With more and more patients and doctors, the ABC Clinic decides to go for databa

Question

With more and more patients and doctors, the ABC Clinic decides to go for database system to manage data. You are asked to design a relational database to keep data to answer clinic operation queries such as:

- List the patient appointments for each doctor for a given date.

- When a patient rings to make an appointment, give the available time slots for a given date.

- Retrieve the address of patients to send notices via mail services.

Answer question 1:

1.1. (4 marks) A database schema of one relation as shown below is proposed, where semantics of attributes are self-explanatory. Discuss at least two weaknesses of this design.

ABC(doc-name, doc-gender, registration_num, qualification, pat-name, pat-gender, DOB, address, phone-num, appoint-date, appoint-time, type)

Appoint date Appoint Doctor Name Gender Steven AwM Steven Aow M Patient Name Gender DOB Address Type number nuber Bachelor of Medicine and Surgery Deakin University, 1980 Bachelor of Medicine and Surgery Deakin University, 1980 Bachelor of Medicine and Surgery Deakin University, 1980 Bachelor of Medical Science, The University of Melbourme, 1982 Bachelor of Medical Science, The Universiry of Melboume, 1982 Bachelor of Medical Science, The Unuversity of Melbourme, 1982 - Will Smih . Jade Liew Ales Lade ABCOO D 15/12/1965 3Farmer Street, Melboume, 3001 30001234 15/04/2017 10:00 long 1001/1970 15 Happy Lane, Prahan, 3015 15/12/1965 7 Storey Drive, Preston, 3147 15/12/1965 3 Farmer Street, Melboume, 3001 30001234 20VO5/2017 15:00 short 4/04/1980 109 Cardigan Street, Carltom, 3003 10901980 1105/2017 1030 long 12/05/2001 23 Tmart Street, Malvem, 3145 23002001 26/06/2017 9:30 hort 4/03/2007 3 Farmer Street, Melboume, 3001 30001234 30/06/2017 14:00 ong 19/10/1980 45 Water Lane, Fitzroy, 3078 20/04/1998 26 May Street, Kew, 3125 15/12/1965 7 Storey Drive, Preston, 3147 MABC0o Bachlor of ABC001 15001234 15/04/2017 10.30 short Steven Arrow Stanley Bill Stanley Bill Stanley Bill Kate Co 70001965 28/05/2017 14:00 long MAXG809 M AXGS09 M Will Smith M Emily Moo F Bob Jane M Will Smith M BIT001 Master of Clinical Medicine Monash Uriversity, 2000 Master of Clinical Medicine BITO Monash University Kate Co Max Fat Max Fat Daniel Water M 45001980 30/06/2017 15:30 short 26001998 28/04/2017 16:00 short 70001965 30/04/2017 13:00 short , 2000 Master of Medicine, The University of Melbourne, 2000 Master of Medicine, The University of Melbourne, 2000 Arran May M Alex Lade M

Explanation / Answer

Anwer: 1.1

Proposed Relation:

ABC(doc-name, doc-gender, registration_num, qualification, pat-name, pat-gender, DOB, address, phone-num, appoint-date, appoint-time, type)

Weeknessess of this design:

1. Data Redundancy: The above design leads to data redundancy. The same piece of data shall not be stored in more than one place. This is because duplicate data not only waste storage spaces but also easily lead to inconsistencies. In the above design, for multiple patients for a doctor, the doctor details such as doc-name, doc-gender, qualification etc. will be stored in multiple rows. The same goes with multiple doctors with one patient which causes data redundancy.

2. Data Integrity and Accuracy: For one patient, different addressess might have been entered. If we try to retrieve mailing address to send notices, multiple mails have to be sent for the same patient. The data may not be accurate if we update information of doctor in one row and forgot to update the details in other rows of the same doctor. The same goes with the patient.

3. Anomilies: If we delete a patient from the database, the doctor information will also be deleted; and if there is only one entry for the doctor, the doctors information will be lost from the database.

=======================================================================================

The Relational Database design:

The doc-name, doc-gender, registration_num, qualification are dependent on doctor entity and they are not dependent on patients info (entity) and appointment details.

The same goes with patient also. So, we can create sepearate tables for doctor, patient.

The appoint-time, appoint-date, type are dependent on both doctor and patient. So, a sepeate relation should be created with doctor id and patient id.

The proposed database schema is :

Doctor_Table:

Patient_Table:

Appointment_Table:

Here, doc-id and pat-id are primary keys, as they are used to uniquely identify a row. In appointment table, both doc-id and pat-id are used to uniquely identify the rows. They are foreign keys to the primary keys of the doctor and patient table.

We can use doctor's register number as primary key to uniqely identify the rows, but if we use numerical values for the key performance of database will be improved (also auto-increment the id).

==================================================================================

doc-id doc-name doc-gender registration_num qualification

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.