Texts:
Instruction:
1. Write the SQL statements/queries for the tasks provided below.
2. Some SQL statements (for creating some tables/populating some tables) are provided in the separate text file attached with this specification document. Run the statements to get the created and populated tables.
Task 1:
Create the following tables based on the specification tables given below. You need to decide the right order to create these tables. Note that the description of each table is for your understanding and not to be used in SQL table creation.
Create table Patient (
PatientID char(4),
PatientName varchar(20),
PatientSuburb varchar(20),
PatientAge int,
PatientOccupation varchar(20),
constraint Patient_PK primary key (PatientID)
);
Create table Nurse (
NurseID char(4),
NurseName varchar(25),
NursePhoneNumber numeric,
NurseExperience int,
constraint Nurse_PK primary key (NurseID)
);
Create table Appointment (
AppointmentID char(5),
AppointmentTime time,
AppointmentDate date,
PatientID char(4),
constraint Appointment_PK primary key (AppointmentID),
constraint Appointment_FK foreign key (PatientID) references Patient (PatientID)
);
Create table Vaccine (
VaccineID char(4),
VaccineName varchar(25),
VaccineExpiry date,
constraint Vaccine_PK primary key (VaccineID)
);
Create table Shot (
ShotTime time,
DoseNumber int,
PatientID char(4),
NurseID char(4),
VaccineID char(4),
constraint Shot_PK primary key (DoseNumber, PatientID),
constraint Shot_FK1 foreign key (PatientID) references Patient (PatientID),
constraint Shot_FK2 foreign key (NurseID) references Nurse (NurseID),
constraint Shot_FK3 foreign key (VaccineID) references Vaccine (VaccineID)
);
Insert into Patient values('P001','RICKY PONTING','BURWOOD',46,'HEALTH');
Insert into Patient(PatientID, PatientName, PatientSuburb, PatientAge, PatientOccupation) values('P002','MATHEW HYDEN','BURWOOD',48,'EDUCATION'),
('P003','BRIAN LARA','MARSFIELD',52,'EDUCATION'),
('P004','YOUNAS KHAN','EPPING',45,'AGED CARE'),
('P005','RAHULL DRAVID','TOP RYDE',49,'HOSPITALITY'),
('P006','KANE WILLIAMSON','AUBURN',39,'HEALTH'),
('P007','BRIAN LARA','LIVERPOOL',55,'EDUCATION'),
('P008','EOIN MORGAN','EPPING',37,'AGED CARE'),
('P009','KUMAR SANGAKARA','PENRITH',42,'HOSPITALITY'),
('P010','ANDY FLOWER','EPPING',61,'HEALTH');
Insert into Vaccine(VaccineID, VaccineName, VaccineExpiry) values ('V001','PFIZER','2021-11-01'),
('V002','MODERNA','2022-01-01'),
('V003','SPUTNICK','2022-03-01'),
('V004','SINOVAC','2022-01-01'),
('V005','PAKVAC','2022-12-01');
Insert into Shot(ShotTime, DoseNumber, PatientID, NurseID, VaccineID) values ('11:00',1,'P001','N001','V001'),
('12:00',1,'P003','N002','V002'),
('11:30',1,'P002','N003','V003'),
('13:00',1,'P004','N002','V001'),
('14:15',1,'P008','N001','V004'),
('12:00',2,'P003','N005','V002'),
('10:00',2,'P001','N001','V001'),
('15:00',2,'P008','N001','V004'),
('16:00',2,'P004','N005','V001'),
('12:30',1,'P007','N006','V005'),
('11:00',1,'P006','N006','V004'),
('10:30',1,'P005','N004','V005');
Task 1 (10 marks):
Create the following tables based on the specification tables given below. You need to decide the right order to create these tables. Note that the description of each table is for your understanding and not to be used in SQL table creation.
Table Name: Patient (SQL code is given)
Column Name Data Type
PatientID (PK) char(4)
PatientName varchar(20)
PatientSuburb varchar(20)
PatientAge int
PatientOccupation varchar(20)
Description:
A unique identifier for each Patient
Name of the Patient
Suburb of the Patient
Age of the Patient
Occupation of the Patient
Table Name: Nurse (SQL code is given)
Column Name Data Type
NurseID (PK) char(4)
NurseName varchar(25)
NursePhoneNumber numeric
NurseExperience int
Description:
A unique identifier for each Nurse
Name of the Nurse
Phone number of the Nurse
Nurse years of Experience
Table Name: Appointment
Column Name Data Type
AppointmentID (PK) char(5)
AppointmentTime time
AppointmentDate date
PatientID (FK) char(4)
Description:
A unique identifier for each Appointment
Appointment time
Appointment date
A unique identifier for each Patient
Table Name: Vaccine
Column Name Data Type
VaccineID (PK) char(4)
VaccineName varchar(25)
VaccineExpiry date
Description:
A unique identifier for each Vaccine
Name of the vaccine
Expiry date of the vaccine
Table Name: Shot (SQL code is given)
Column Name Data Type
ShotTime time
DoseNumber (PK) int
PatientID (PK)(FK) char(4)
NurseID (FK) char(4)
VaccineID (FK) char(4)
Description:
Time of the injection
Dose number
A unique identifier for each Patient
A unique identifier for each Nurse
A unique identifier for each Vaccine
