(Solved) : Consider Following Relations Patient Pid Integer Lname String Fname String Primarydid Inte Q36160975 . . .
Consider the following relations:
Patient (pid: integer, lname: string,fname: string, primary_did: integer,age: integer)
Doctor (did: integer, lname: string,fname: string, deptid: integer, age:integer, salary: integer)
Nurse (nid: integer, lname: string,fname: string, deptid: integer, rank:string, age: integer)
Department (deptid: integer, name: string,budget: integer)
Menu (menuid: integer, pid: integer,caloriecount: integer, saltlevel: integer,vegetarian: bit, diabetic: bit,nauseaSafe: bit, notes: string)
Vaccination (vaccinationid: integer, pid:integer, vaccinationname: string, dategiven:date, dateexpires: date)
Prescription (presid: integer, pid:integer,medname: string, quantity: integer,timesPerDay: integer, withfood: bit,writtenby: integer)
MedsGiven(presid: integer, timegiven: timestamp,givenbynurse: integer, notes: string)
The relations capture some basic information about ahospital.
Patients have primary physicians (primary_did).
Doctors work in departments (deptid) (oncology, pediatrics,etc).
Each patient staying for a while in the hospital has a specificmenu ordered for them; pid in Menu is a foreign key referencingPatient.
Patients also have vaccinations (pid in Vaccination referencesPatient).
Patients may have prescriptions and, of course, each prescriptionmust be written by a doctor. writtenby in Prescription referencesdid in the Doctor relation.
Nurses give medicines based on prescriptions, and the databaserecords the time each medicine is given (timegiven) and which nursegave it (givenbynurse).
(did = doctor ID. lname = last name. pid = patient ID. nid =nurse ID. Etc.)
- Find the prescription IDs, nurse IDs and doctor IDs ofprescriptions, where the doctor who wrote the prescription has thelast name “Salk”. Also, the nurse ID is for a nurse who has giventhe prescription to the patient. Results should be sorted by themedicine name of the prescription.4
- For each doctor that is a primary doctor for patients, and whohas more than five patients for whom that doctor is primary doctor,list the doctor ID and the total number of patients that have thatdoctor as their primary doctor.
Expert Answer
Answer to Consider Following Relations Patient Pid Integer Lname String Fname String Primarydid Inte Q36160975 . . .
OR