Provided the following design for a database : Doctor ( DoctorName , Gender, Reg

Question

Provided the following design for a database :

Doctor (DoctorName, Gender, RegistrationNumber, Qualification);

Patient (PatientNumber, Gender, DOB, Address, PhoneNumber);

Appointments (DoctorName*, PatientNumber*, AppointmentDate, AppointmentTime, Type);

NOTE : IN ITALICS ARE PRIMARY KEYS

complete below tasks to populate your database with the data shown in Table 1.

Table 1.

2.1. (5 marks) Give the CREATE TABLE statement for each relation, including primary key and any foreign key constraints.

2.2. (5 marks) Give the INSERT INTO statements for each relation in your design. All data shown in Table 1 must be inserted into your database.

Appoint Doctor Name Gender Patient Name Gender DOB Address Appoint date Type number Bachelor of Medicine and Surgery Deakin University, 1980 Bachelor of Medicine and Surgery Deakin University, 1930 Bacbelor of Medicine and Surgery Steven Aow M Will Smith M 15/121965 3 Farmer Street, Melboume, 3001 30001234 15/04/2017 1000 long 1001/1970 15 Happy Lane, Prahan, 3015 15121965 Storey Drive, Preston, 3147 15/12/1965 3Farmer Street, Melboume, 3001 30001234 20105/2017 15:00 hort /04/1980 109 Cardigan Street, Carlton, 3003 10901980 1105/2017 1030 long 12/05/2001 23 Tmart Street, Malvem, 3145 23002001 26/06/2017 9:30 short /03/2007 3Farmer Street, Melboume, 3001 30001234 30/06/2017 14:00 long 19/10/1980 45 Water Lane, Fitzroy, 3078 20/04/1998 26 May Street, Kew, 3125 15/12/1965 7 Storey Drive, Preston, 3147 Steven Aw M . Jade Liew F ABC001 15001234 15/04/2017 1030 shor and Surgery. Deakin University Alex Lade Steven Aow M Stanley Bill M Stanley Bill M Stanley Bill Kate Cox ABC001 ABCOOL 70001965 28/05/2017 14:00 ong , 1980 Bachelor of Medical Science, The University of Melbourme, 1982 Bachelor of Medical Science, The University of Melbourne, 1982 Emly Moo Will Smith F M AXG0 Bachelor of Medical Science. Tbe Bob Jane M Unuversiry of Melboume, 1982 BITO01 Master of Clinical Medicine BITO BM 345 Will Smith M Monash Uruversity, 2000 Master of Clinical Medicine Monash University, 2000 Master of Medicine, The University of Melbourne, 2000 Master of Medicine, The University of Melbourne, 2000 Kate Cox Danuel Water M 5001980 30/06/2017 15:30 short 26001998 28/04/2017 16:00 short 70001965 30/04/2017 13:00 short Max Fat M Arran May M Max Fat Alex Lade MM

Explanation / Answer

Answer)

Table design:
Doctor (DoctorName, Gender, RegistrationNumber, Qualification);

Patient (PatientNumber, Gender, DOB, Address, PhoneNumber);

Appointments (DoctorName*, PatientNumber*, AppointmentDate, AppointmentTime, Type);

2.1. (5 marks) Give the CREATE TABLE statement for each relation, including primary key and any foreign key constraints.

Create Table Scripts for the above tables including primary key and any foreign key constraints are:

CREATE TABLE Doctor(DoctorName varchar primary key, Gender varchar, RegistrationNumber varchar, Qualification varchar);

CREATE TABLE Patient(PatientNumber integer primary key, Gender varchar, DOB varchar, Address varchar, PhoneNumber varchar );

CREATE TABLE Appointments (DoctorName varchar, PatientNumber integer, AppointmentDate varchar, AppointmentTime varchar, type varchar, PRIMARY KEY (DoctorName,PatientNumber), FOREIGN KEY (DoctorName) REFERENCES Doctor(DoctorName), FOREIGN KEY (PatientNumber) REFERENCES Patient(PatientNumber));

2.2. (5 marks) Give the INSERT INTO statements for each relation in your design. All data shown in Table 1 must be inserted into your database.

Insertion into Table Doctor:
insert into Doctor values('Steven Arrow','M','ABC001','Bachelor of Medicine and Surgery, Deakin University, 1980');
insert into Doctor values('Stanley Bill','M','AXG809','Bachelor of Medical Science, The University of Melbourne, 1982');
insert into Doctor values('Kate Cox','F','BIT001','Master of Clinical Medicine, Monash University, 2000');
insert into Doctor values('Max Fat','M','BMJ345','Master of Medicine, The University of Melbourne, 2000');

Insertion into Table patient:
insert into patient values(1,'Will Smith','M','15/12/1965','3 Farmer Street, Melbourne, 3001');
insert into patient values(2,'Jade Liew','F','10/01/1970','15, Happy Lane, Prahan, 3015');
insert into patient values(3,'Alex Lade','M','15/12/1965','7 Storey Drive, Prestion, 3147');
insert into patient values(4,'Emily Moo','F','4/04/1980','109 Cardigan Street, Carlton, 3003');
insert into patient values(5,'Bob Jane','M','12/05/2001','23 Tmar Street, Malvern, 3145');
insert into patient values(6,'Daniel Water','M','19/10/1980','45 Water Lane, Fitzroy, 3078');
insert into patient values(7,'Arran May','M','20/04/1998','26 May Street, Kew, 3125');

Insertion into table Appointments:
insert into Appointments values ('Steven Arrow',1,'15/04/2017','10:00','long');
insert into Appointments values ('Steven Arrow',2,'15/04/2017','10:30','short');
insert into Appointments values ('Steven Arrow',3,'28/05/2017','14:00','long');
insert into Appointments values ('Stanley Bill',1,'20/05/2017','15:00','short');
insert into Appointments values ('Stanley Bill',4,'11/05/2017','10:30','long');
insert into Appointments values ('Stanley Bill',5,'26/06/2017','9:30','short');
insert into Appointments values ('Kate Cox',1,'30/06/2017','14:00','long');
insert into Appointments values ('Kate Cox',6,'30/06/2017','15:30','short');
insert into Appointments values ('Max Fat',7,'28/04/2017','16:00','short');
insert into Appointments values ('Max Fat',3,'30/04/2017','13:00','short');

select * from Doctor;

Output:
Steven Arrow|M|ABC001|Bachelor of Medicine and Surgery, Deakin University, 1980
Stanley Bill|M|AXG809|Bachelor of Medical Science, The University of Melbourne, 1982
Kate Cox|F|BIT001|Master of Clinical Medicine, Monash University, 2000
Max Fat|M|BMJ345|Master of Medicine, The University of Melbourne, 2000

select * from patient;

Output:
1|Will Smith|M|15/12/1965|3 Farmer Street, Melbourne, 3001
2|Jade Liew|F|10/01/1970|15, Happy Lane, Prahan, 3015
3|Alex Lade|M|15/12/1965|7 Storey Drive, Prestion, 3147
4|Emily Moo|F|4/04/1980|109 Cardigan Street, Carlton, 3003
5|Bob Jane|M|12/05/2001|23 Tmar Street, Malvern, 3145
6|Daniel Water|M|19/10/1980|45 Water Lane, Fitzroy, 3078
7|Arran May|M|20/04/1998|26 May Street, Kew, 3125

select * from Appointments;

Output:
Steven Arrow|1|15/04/2017|10:00|long
Steven Arrow|2|15/04/2017|10:30|short
Steven Arrow|3|28/05/2017|14:00|long
Stanley Bill|1|20/05/2017|15:00|short
Stanley Bill|4|11/05/2017|10:30|long
Stanley Bill|5|26/06/2017|9:30|short
Kate Cox|1|30/06/2017|14:00|long
Kate Cox|6|30/06/2017|15:30|short
Max Fat|7|28/04/2017|16:00|short
Max Fat|3|30/04/2017|13:00|short

/*Please Hit Like if you like my answer*/

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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