Stored Procedures & Triggers

Database Scheme

Doctor(did, dname, noOfpatient, deid) // did = doctor id --- dname= doctor name -- deid= department id

Patient(pid, pname, noOfexamination) // pid = patient id --- pname= patient name --- noOfexamination = no of examination

Examination(did, pid, datetime, diagnosis, fee)

Department(deid, dename, noOfdoctor, totalFee)

  Create the above database with CREATE TABLE in PostgreSQL and INSERT enough data into each table to answer the following questions.  Write and test the following triggers and procedures. Before and after testing, Make sure that the trigger/procedures are working correctly by querying the states/ conditions of tables records. 

Share trigger/procedure source code and test data(i.e The insert/update/delete command used for testing, the data of the records before and after the command) for each question.

Questions:

1)Write the trigger(s) that update the Total Fee field in the department table as needed. Clue:

It should work, When a record is added, deleted, or the fee field in a record is updated in the examination table.

2) Write the trigger(s) that update the doctorNumber field in the department table as needed.

3) Consider a table defined such as : examinations(did, start Date Time, end Date Time) returns table, Write a stored function that returns all examination records of a given doctor (whose did (doctor id) is given) between a startDateTime and endDateTime.

[Note: In PostgreSQL the SELECT command can be directly used without using RETURN TABLE or RETURN QUERY.]. In order to show that this function works, use it in a SQL command.

4) Consider a patient who is defined as such TotalPaid(hid INT) returns INT and his id is given, Write a stored function that returns the total of the inspection fees he paid. In order to show that this function works, use it in a SQL command.