Kindly use the hospital relational schema image provided below
to solve these
questions.
1. List EID, Dnumber and Lname of each doctor attending a
patient in hospital H02 at the ward
W18.
a) We want RESULT(DoctorEID, Dnumber, DoctorLname)
although column headers can be different.
b) Need to join DOCTOR, EMPLOYEE to get all the required
attributes.
c) Need a selection for "H02" and "W18"
2. List Registration numbers of all patients in H02, ward W18
who have been diagnosed with
"Flu" but for whom "Chest X-ray" test has not been ordered.
a) First get a table with only one column RegistrationNumber, which
lists all patients
from W18 of H02 who have been diagnosed with flu.
b) Second, get a table with only one column RegistrationNumber,
which lists all patients
from W18 of H02 who have had a test with Tname="Chest
X-ray".
c) Finally, take the set difference.
- Kindly Use The Hospital Relational Schema Image Provided Below To Solve These Questions 1 List Eid Dnumber And Lname 1 (60.54 KiB) Viewed 41 times
HN - Hospital Network Relational Schema HOSPITAL(Hid Hname, Street, City, ZIP, State, Phone) Hname - candidate key Street, City, ZIP-candidate key Phone - candidate key WARD(Hid, Wid. Wname, Beds) #Unlike in the ER diagram that was given earlier, # this assumes that wid does not identify a ward unless we know the hospital. Hid, Wname-candiate key Hid - foreign key of HOSPITAL EMPLOYEE(Eid, Fname, MI, Lname, Duty, Shift, Hid, Wid) Hid, Wid - foreign key of WARD DOCTOR(Eid, Dnumber, Spacialization) Eid - foreign key of EMPLOYEE PATIENT(Registr Number, Fname, MI, Lname, DOB, Street, City, ZIP, State, Sex, Allergies, Hid, Wid, BedNumber) Street, City, ZIP - candidate key Hid, Wid - foreign key of WARD DIAGNOSIS(Did, Dname) Dname - candidate key TREATMENT(Registration Number, DoctorEid, Did, DiagStateent, Complications, Treatment) Registration Number - foreign key of PATIENT DoctorEid - foreign key of EMPLOYEE Did - foreign key of DIAGNOSIS TEST(Tid, Type, Tname) Tname - candidate key ORDERED_TESTS(PatientRegistrationNumber, Tid, date, time, specimenNumber, status, results) PatientRegistration Number - foreign key of PATIENT Tid - foreign key of TEST LAB(Lid. Lname, Street, City, ZIP, State, Phone) Street, City, ZIP - candidate key Phone - candidate key LAB_TEST(Lid. Tid) Lid - foreign key of LAB Tid - foreign key of TEST CONTRACTS(Lid, Hid) Lid - foreign key of LAB Hid - foreign key of HOSPITAL