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:
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:
Set up a transaction that will handle this scenario successfully.
Due Date: December 05, 2014