Map the EER diagram to a relational schema, and transform the relation into 3NF.
ID: 3880430 • Letter: M
Question
Map the EER diagram to a relational schema, and transform the relation into 3NF.
PERSON ITEM Address Birthdate Phone No Person Type RESCRIPTION CONSUMPTION Date Time Desc Unit Cost Volunteer? = "Y" VOLUNTEER Person Type Total Cost 0 Skill Patient? = "Y" Physician: "Ye. NT SERVICE-RECORDS Employee? "Y" PATIENT PHYSICIAN EMPLOYEE Begin Date End Date Receives Responsible Patient Type Contact Date Pager No Specialyy Employee Type Date Hired Refers Administers Has Patient Type: Employee Type 0 CARE CENTRE RECORDS Hours Logged Outpatient? "Y" Resident? "V" NURSE Certificate Nurse Type OUTPATIENT TECHNICIAN RESIDENT Date Admitted Date Discharged STAFF Job Class Skill Works Assigned Provided Employed Nurse Type: Assigned VISIT Visit No (Partial ld) Comments BED Bed No (Partial ID) Room No WORK UNIT OTHER NURSES RN Location In Charge Work Type Directs ORDER Work Type: Contains TREATMENT Treatment Time Results Treatment Date CARE CENTRE LABORATORY Performed InExplanation / Answer
ER diagram into relational schema-
Below is the relational model of the given ER diagram. It consist of 20 relations. Each relation has a primary key and associated foreign keys.
Primary key are mentioned as underlined attributes and foreign key as bold attributes.
Person (PersonID, Name, Address, Birthdate, PhoneNo)
Patient (PersonID, PatientID,Contact Date)
OutPatient (PatientID, VisitNo., Comments)
Resident (PatientID, DateAdmitted, DateDischarged, BedNo.,, RoomNo., CareCenterID)
Physician (PersonID, PhysicianID, PagerNo, Speciality, AssignedToWorkUnit)
Employee (PersonID, EmployeeID, DateHired)
Nurse (EmployeeID, Certificate, Type, DirectBy)
Staff (EmployeeID, JobClass)
Technician (EmployeeID, Skill)
Volunteer (PersonID, VolunteerID, Skill)
Volunteer-Service-Record (VolunteerID, BeginDate, EndDate, Hours, SupervisedByEmployee)
Item (ItemNo)
Prescription (PhysicianID, PatientID, Desc, UnitCost)
Item-Consumption (ItemNo, PatientID, Date, Time, Quantity, TotalCost)
CareCenterRecords (PhysicianID, HoursLogged, CareCenterID)
Treatment (TreatmentID)
TreatmentOrder (TreatmentID, PatientID, TreatmentTime, Result, TreatmentDate)
WorkUnit (Name, Location)
CareCenter (CareCenterID, WorkUnitName)
Laboratory (LabID, WorkUnitName)