(Solved) : Consider Following Relations Patients Pid Name Address Telephone Carecentreid Carecentres Q30693776 . . . .

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 Oracle to complete the following tasks.

d. For some strategic decisions, the president ofthe hospital needs summary data about the care centres. For eachcare centre, s/he needs to know the number of nurses holding an RNcertificate, as well as their total and average salaries. Does thefollowing view answer the president’s request? If not, write thecorrect 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