Merge the local data models to create a global logical data model of the Wellmeadows Hospital case study. State any assumptions necessary to support your design.

Question

What will be an ideal response?

Answer 1

Once the local data models have been validated, the student should demonstrate the view
integration approach to create a global logical data model. The student should produce an ER
model of the global data model, representing both user views and the supporting documentation
that describes the model. Throughout the process of design, the student should clearly state any
assumptions necessary to support his or her design.
An example of an ER model and the relational schema of the global data model of the Wellmeadows
Hospital case study is shown below. Note that this answer also includes the Personnel Officer’s view.
Ward (wardNo, wardName, location, totalBeds, telExtn, chargeNurseStaffNo)
Primary Key wardNo
Alternate Key telExtn
Foreign Key chargeNurseStaffNo NOT NULL references Staff(staffNo)
ON DELETE SET DEFAULT ON UPDATE CASCADE
Staff (staffNo, fName, lName, address, telNo, DOB, sex, NIN, position, salary, sScale,
weekHrs, contType, typePay)
Primary Key staffNo
Alternate Key NIN
Qualification (staffNo, oType, oDate, institutionName)
Primary Key staffNo, oType
Foreign Key staffNo NOT NULL references Staff(staffNo) ON DELETE CASCADE
ON UPDATE CASCADE
WorkExperience (staffNo, sDate, fDate, position, orgName)
Primary Key staffNo, orgName
Foreign Key staffNo NOT NULL references Staff(staffNo) ON DELETE CASCADE
ON UPDATE CASCADE
StaffRota (shift, weekNo, staffNo, wardNo) (from *:* relationship between Staff/ Ward)
Primary Key staffNo, weekNo
Foreign Key staffNo NOT NULL references Staff(staffNo) ON DELETE CASCADE
ON UPDATE CASCADE
Foreign Key wardNo NOT NULL references Ward(wardNo) ON DELETE CASCADE
ON UPDATE CASCADE)
Patient (patNo, fName, lName, address, telNo, DOB, sex, mStatus, dateReg,
docName, clinicNo, nokName, nokRelationship, nokAddress, nokTelNo)
Primary Key patNo
Foreign Key docName, clinicNo NOT NULL references Doctor(docName, clinicNo)
ON DELETE NO ACTION ON UPDATE CASCADE
Doctor (docName, clinicNo, address, telNo)
Primary Key docName, clinicNo
Appointment (appNo, patNo, consStaffNo, appDate, appTime, roomNo)
Primary Key appNo
Foreign Key patNo NOT NULL references Patient(patNo) ON DELETE NO ACTION
ON UPDATE CASCADE)
Foreign Key consStaffNo NOT NULL references Staff(staffNo) ON DELETE NO ACTION
ON UPDATE CASCADE
OutPatientAppointment (patNo, outPatDate, outPatTime)
Primary Key patNo, outPatDate
Foreign Key patNo NOT NULL references Patient(patNo) ON DELETE CASCADE
ON UPDATE CASCADE
Bed (wardNo, bedNo)
Primary Key wardNo, bedNo
Foreign Key wardNo NOT NULL references Ward(wardNo) ON DELETE NO ACTION
ON UPDATE CASCADE
InPatientAllocation (patNo, listDate, wardReq, duration, placedDate, exLeaveDate,
actLeaveDate, bedNo)
Primary Key patNo, ListDate
Foreign Key patNo NOT NULL references Patient(patNo) ON DELETE CASCADE
ON UPDATE CASCADE
Foreign Key wardReq, bedNo NOT NULL references Bed(wardNo, bedNo)
ON DELETE NO ACTION ON UPDATE CASCADE
Medication (patNo, drugNo, unitsDay, aMethod, sDate, fDate)
Primary Key patNo, drugNo, sDate
Foreign Key patNo NOT NULL references Patient(patNo) ON DELETE CASCADE
ON UPDATE CASCADE
Foreign Key drugNo NOT NULL references Pharmaceutical(drugNo)
ON DELETE NO ACTION ON UPDATE CASCADE
Pharmaceutical (drugNo, dName, description, dosage, methodAdmin, quantityStock,
reorderLevel, unitCost, supplierNo)
Primary Key drugNo
Foreign Key supplierNo NOT NULL references Supplier(supplierNo)
ON DELETE NO ACTION ON UPDATE CASCADE
Non-Surgical/Surgical (itemNo, iName, iDescription, quantityStock, reorderLevel,
unitCost, supplierNo)
Primary Key itemNo
Foreign Key supplierNo NOT NULL references Supplier(supplierNo)
ON DELETE NO ACTION ON UPDATE CASCADE
Requisition (reqNo, chargeNurseStaffNo, wardNo, itemdrugNo, quantReq, dateOrder,
dateReceive)
Primary Key reqNo
Foreign Key chargeNurseStaffNo NOT NULL references Staff(staffNo)
ON DELETE NO ACTION ON UPDATE CASCADE
Foreign Key wardNo NOT NULL references Ward(wardNo) ON DELETE NO ACTION
ON UPDATE CASCADE
Foreign Key itemdrugNo NOT NULL references NonSurgical/Surgical(itemNo) and
Pharmaceutical(drugNo) ON DELETE NO ACTION ON UPDATE CASCADE
Supplier (supplierNo, sName, sAddress, telNo, faxNo)
Primary Key supplierNo
Alternative Key telNo
Alternative Key faxNo