Welcome, Guest User :: Click here to login

Logo 67311

67-311 :: Phase 2: Building the PATS database

This phase we will actually build the database that was designed in phase 1. The solution to phase 1 that everyone needs to use this phase can be found online on github at https://github.com/profh/67311_phase_2. Even if you like your solution better, you still need to conform to this solution so your project can be properly graded.

In addition the phase 1 solutions, the github repository has a number of other files to be used this phase, including a set of test data. Add the required code to the corresponding file, add a pg_dump of the final, complete database and zip these files up (the zip file should be called [student_1_andrew_id]_[student_1_andrew_id]_p2.zip) and submit them to the Head TA no later than December 5, 2014 at 5pm. More details on this process will be given later in class, but follow the templates we provide closely to complete the project.

In this phase students will create the table structure for the PATS database, add constraints, add indexes, add views, add privileges, and add functions and triggers. All this will be done as follows:

  • SQL to create all 13 tables in PATS and setting primary keys is to be put in pats_structure.sql
  • SQL to add foreign key constraints and other appropriate constraints is to be put in pats_constraints.sql. (Even though you could add your FKs during the create table process, for grading purposes we want all FKs set up in this file.)
  • SQL to indexes needed to facilitate speedier queries is to be put in pats_indexes.sql
  • SQL to create the functions and triggers specified below is to be put in the pats_functions.sql
  • SQL to create the two views specified below is to be put in the pats_views.sql
  • SQL to control access privileges to certain tables in the system
  • SQL to create a transaction (details listed below)

In terms of views, one view called 'owners_view' is to be created that joins owners, pets, and visits together. In addition, the pet's animal_id is to be replaced with the animal name (which is more meaningful). The second view is to be called 'medicine_views' and connects information from the medicine, animal and cost tables together. This view should also replace animal_id with the animal name. In terms of costs, the only costs that need to appear are the current cost_per_unit for the medicine (column should be called 'current cost') as well as the date the medicine's cost last changed.

In terms of functions and triggers, the phase 1 solution indicates that we need triggers to automatically set the end_date of either procedure_costs or medicine_costs to the current date before a new record is added. We also need a trigger to automatically reduce the medicine stock levels after a new visit_medicine application is recorded in the system. Finally we need functions to calculate whether the overnight_stay flag in the visits table needs to be set to true as well as calculate the total costs for a particular visit. (See the functions template for an exact list of functions, triggers and the exact names each should be called.)

The last two functions can be used as part of a transaction that captures all the business issues regarding a pet's visit to PATS. In the pats_transaction.sql file we need to create a transaction to describe the following:

  • Zaphod (pet id: 173; owned by Horacio Mayert) the dog comes in today for a visit and weighs 39 pounds.
  • Zaphod gets an examination because he's lost some weight unexpectedly. (Last two visits he weighed 48 and 50 pounds, so 39 pounds is low for Zaphod.)
  • Assuming the required 500 units are in stock and the medicine is appropriate for a dog, Zaphod is given Ivermectin (medicine id: 3) to treat a nasty parasite which is responsible for the weight loss.
  • Assuming the required 200 units are in stock and the medicine is appropriate for a dog, Zaphod is given Mirtazapine (medicine id: 5) to stimulate his appetite and help get his weight back up to the 50 pound range.
  • Assuming no problems, then total costs for these services and medicines are calculated (no discounts for Horacio), the overnight_stay flag is evaluated and all the data is committed to the database. If there are any problems at any step, then none of this data is saved to the system.

Set up a transaction that will handle this scenario successfully.

Required Documents
Phase 2 Materials - a repository of materials stored on github. Do NOT push your changes back to github -- severe grade penalties will be enforced for any team that does so.


Due Date: December 05, 2014

Weight: 20.0