(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 certificate_type like ‘RN’
GROUP BY cid;
e. State which of the following queries andupdates would be allowed in this view. If a particular query orupdate would be allowed, show what the corresponding query orupdate on the base relations would look like, and give its resultwhen applied to the database.
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;
Expert Answer
Answer to Consider Following Relations Patients Pid Name Address Telephone Carecentreid Carecentres Q30693776 . . .
OR