A spreadsheet was used to keep data for the booking system of the ABC Clinic, with sample data shown in Table 1. Columns 1-4 contain information for doctors, Columns 5-9 contain information for patients and the last 3 columns contain information for patients appointments with doctors.

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)

1.2. (6 marks) Propose your own design for the database. Your design must be able to keep all information shown in Table 1. Explain your design. Give the schema for each relation in your database and specify their data integrity constraints (underline primary keys and put asterisk for foreign keys)

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