Consider the following relations:

• Patients(pid, name, address, telephone, care_centre_id)
• Care_centres(cid, name, location, nurse_charge_id)
• Treatments(tid, patient_id, physician_id, treatment_name, date)
• Nurses(nid, name, care_centre_id, certificate_type, telephone, salary)
• Physicians(phid, name, pager_number, specialization, salary).

Use PostgreSQL or the DBMS selected for your Labs to complete the following tasks.

1. For some strategic decisions, the president of the hospital needs summary data about the care centres. For each care centre, s/he needs to know the number of nurses holding an RN certificate, as well as their total and average salaries. Does the following view answer the presidents request? If not, write the correct view that will satisfy the presidents request. (7 marks)

CREATE VIEW NURSE_SUMMARY (D, C, TOTAL_S, AVERAGE_S)

AS SELECT cid, COUNT (*), SUM (salary), AVG (salary)

FROM Care_centres, Nurses

WHERE nurse_charge_id = nid and certificate_type like RN

GROUP BY cid;

5. State which of the following queries and updates would be allowed in this view. If a particular query or update would be allowed, show what the corresponding query or update on the base relations would look like, and give its result when applied to the database. (5 marks)

Q1. SELECT *

FROM NURSE_SUMMARY;

Q2. SELECT D, C

FROM NURSE_SUMMARY

WHERE TOTAL_S > 100000;

Q3. SELECT D, AVERAGE_S

FROM NURSE_SUMMARY

WHERE C > (SELECT C FROM NURSE_SUMMARY WHERE D=4);

Q4. UPDATE NURSE_SUMMARY

SET D=3

WHERE D=4;

Q5. DELETE FROM NURSE_SUMMARY

WHERE C > 4;

6. Use PostgreSQL or the database system you used for the Labs to create a view that displays the following information for each Patient: (10 marks)

Patient Number

Patient Name

Care Center Name

Name of Nurse-in-charge

Treatment ID

Treatment Name

Physician ID

Date

(Only the part f Please)