Consider the following subset of relations from the RVH database:

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.

Create the tables that correspond to these relations in your PostgreSQL database.

If not automatically created by the DBMS$,$ create indexes corresponding to the primary and foreign keys.

Populate these tables with some sample data and write SQL queries that show the content of each table after entering the data.

For some strategic decisions, the president of the hospital needs summary data about the care centres. For each care centre, the president 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 president$$s request? If not, write the correct view that will satisfy the president$$s request.

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;

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.

Q$1.$ SELECT $*$

FROM NURSE$\_$SUMMARY;

Q$2.$ SELECT D$,$ C

FROM NURSE$\_$SUMMARY

WHERE TOTAL$\_$S $>100000$;

Q$3.$ SELECT D$,$ AVERAGE$\_$S

FROM NURSE$\_$SUMMARY

WHERE C $>($SELECT C FROM NURSE$\_$SUMMARY WHERE D$=4)$;

Q$4.$ UPDATE NURSE$\_$SUMMARY

SET D$=3$

WHERE D$=4$;

Q$5.$ DELETE FROM NURSE$\_$SUMMARY

WHERE C $>4$;

Create a view that displays the following information for each patient.

Patient Number

Patient Name

Care Centre Name

Name of Nurse$-$in$-$Charge

Treatment ID

Treatment Name

Physician ID

Date