Databse design Draw Entity Relationship Diagram

Questions: 1.Use the symbols as prescribed in your unit-textbook to draw the ER diagra? 2.Provide assumptions and business rules relevant to your ERD? 3.Map your Entity Relationship diagram (ERD) into relations and make sure that all the relations are in 3NF? Anwers: Identification of Entities Medical specialists Consultations Procedures Patients Appointments General medical practitioner (GPs) Medical history Tests Prescription Charges Invoice Identifying Attributes of the Entities 1.Medical specialists Specialist ID Specialist First name Specialist Last name DOB Contacts Address 2.Consultation Consultation ID Referral letter Specialist ID Specialist remarks 3.Patient Patient ID Patient Name Gender DOB Phone number Telephone Address Comments 4.Medical history Patient ID Past surgeries Current medicines Current dosage Medication allergies 5.Booking Booking ID Patient ID Date of booking Appointment ID 6.General practitioner Medical Provider Number Name Contact number Address 7.Referral Letter Letter ID Notes Medical Provider Number 8.Test Test ID Test description 9.Procedure Procedure ID Procedure description 10.Prescription Prescription id Description 11.Appointment Appointment ID Patient ID Specialist ID Phone call Personal visit 12.Invoice Invoice number Appointment ID Total charges 2: Business Rules and assumptions The general practitioner may issue one or more referral letters. Many referral letters may be issued by one general practitioner. One patient may have many referral letters while many referral letters may belong to one patient. One patient may have one or more medical history reports. Many history reports may belong to one patient. One patient may make one or more bookings for appointment while many bookings may be made by one patient One booking makes only one appointment while one appoints has only one booking One appointment may result to many invoices while many invoice which each invoice must be attached to an appointment Each appointment must have one or more consultation while each consultation must have an appointment Each appointment may have one or more tests while each test must have an appointment Each appointment may have one or more prescriptions while each prescription must have an appointment Each appointment may have one or more procedure while each procedure must have an appointment 3: Logical design in 3NF Medical specialist (SpecialistID, Specialistname, DOB, Contacts, Address) Consultation (ConsultationID, ReferralletterID, SpecialistID, TestID, ProcedureID, PrescriptionID, AppointmentID, Specialistremarks) Foreign key (ReferralletterID) references Referral Letter (ReferralletterID) Foreign key (SpecialistID) references Medical specialists (SpecialistID) Foreign key (TestID) references Test (TestID) Foreign key (ProcedureID) references Medical specialists (ProcedureID) Foreign key (PrescriptionID) references Prescription (PrescriptionID) Foreign key (Appointment) references Appointment (AppointmentID) Patient (PatientID, PatientName, Gender, DOB, Phonenumber, Telephone, Address, Comments) Medical history (PatientID, Pastsurgeries, Currentmedicines, Currentdosage, Medicationallergies) Foreign key (PatientID) references patient (PatientID) Booking (BookingID, PatientID, Date of booking, AppointmentID, SpecialistID) Foreign key (PatientID) references patient (PatientID) Foreign key (AppointmentID) references Appointment (AppointmentID) Foreign key (SpecialistID) references SpecialistID (SpecialistID) General practitioner (MedicalProviderNumber, Name, contactnumber, address) Referral Letter (ReferralletterID, Notes, MedicalProviderNumber) Foreign key (MedicalProviderNumber) references general practitioner (MedicalProviderNumber) Test (TestID, Testdescription) Procedure (ProcedureID, Proceduredescription) Prescription (PrescriptionID, Description) Appointment (AppointmentID, PatientID, SpecialistID, time, duration) Invoice (Invoicenumber, Appointment ID, Totalcharges) Foreign key (Invoicenumber) references invoice (Invoicenumber) References Buxton, S. (2009).Database design. 1st ed. Amsterdam: Morgan Kaufmann Publishers/Elsevier. Demba, M. (2013). Algorithm for Relational Database Normalization Up to 3NF.International Journal of Database Management Systems, 5(3), pp.39-51. Garmany, J., Walker, J. and Clark, T. (2005).Logical database design principles. 1st ed. Boca Raton, Fla.: Auerbach Publications. Howe, D. (2003).Data analysis for database design. 1st ed. Oxford [u.a.]: Butterworth Heinemann. Shoval, P. and Even-Chaime, M. (2012). Database schema design: an experimental comparison between normalization and information analysis.ACM SIGMIS Database, 18(3), pp.30-39. Watt, A. and Eng, N. (2014).Database design. 1st ed. [Place of publication not identified]: [publisher not identified].