Question 1 1.1
Candidate keys for Patient
{MedicareNo, PName}
Because MedicareNo identifies the family and PName further identifies the member in the family.
MedicareNo is same for patient in the family, different patient may have the same PName. So {MedicareNo, PName} is unique among all tuples, but no proper subset of it is unique.
{PhNo}
Under the assumption that no two patients share the same phone number
{Email}
Under the assumption that no two patients share the same Email
Candidate keys for Appointment
{MedicareNo, ApptDate, ApptTime}
Because from 1. A patient can only have one appointment on a given day at a given time.
So {MedicareNo, PName, ApptDate, ApptTime} is unique. And from 6, two members of the same family cannot have appointments at the same time on the same day. So {MedicareNo, ApptDate, ApptTime} is unique. Obviously no proper subset of it is unique.
{RoomNo, ApptDate, ApptTime}
From 5 Two appointments cannot take place in the same room at the same time on the same day,
{RoomNo, ApptDate, ApptTime} is unique. Obviously no proper subset of it is unique under the assumption that at the same time in the same day, multiple rooms can be used for appointment, a room can be used for appointment at the same time in different day or at the different time in the same day.
1.2
{MedicareNo, MPName}
Because {MedicareNo , MPName} in Appointment identifies rows in the Patient.
{MPRN}
Because MPRN identifies the rows in the MedicalPractitioner, it uniquely identifies a medical practitioner.
Question 2
Licencense_number
License_date
Check_up_date
Description Employee_id
First_name
Last_name
EMPLOY
EE d
Birth_date
Address
Salary
Phone_no
Date
Departure_time Arrival_time
Cost
ADMINISTRATION_ST AFF
PILOT
1 Type 2
SERVICE_ENGIN Date EER
Operates
Flight_type 1 N
Time
Detail
Manufacturer
Mechanical_service
HELICOPTER
M
Flight
N
CUSTOM
Customer_id Name
Model_code
Comments
Model_name Engine_strength
Registration_num ber
Number_of_seats Mileage
Phone_no
Address
2.1 entities: CUSTOMER, EMPLOYEE, PILOT, SERVICE_ENGINEER, ADMINISTRATION_STAFF, HELICOPTER,
2.2 PILOT, SERVICE_ENGINEER, ADMINISTRATION_STAFF are subclass of EMPLOYEE. Customer(N) and HELICOPTE(M) have Flight relation. SERVICE_ENGINEER(2) and HELICOPTER(N) have mechanical service relationship. PILOT(1) and HELICOPTER(1) have operation relationship. I have the assumption that a pilot can only operate 1 helicopter.
2.3
CUSTOMER: Customer_id Name Phone_no Address Customer_id is the primary key EMPLOYEE: Employee_id First_name Last_name Birth_date Salary Phone_no Address
Employee_id is the primary key
PILOT: License_number License_date Check_up_date License_number is the primary key HELICOPTER: Registration_number Model_code Model_name Number_of_seats Mileage
Engine_strength Manufacturer Registration_number is the primary key MECHANICAL_SERVICE Service_type Service_date Service_time Service_detail
Comments
FLIGHT Registration_number Flight_type Customer_id
Date Departure_time Arrival_time Cost Description
2.4 No such integrity constraints or information.
Question 3
3.1
STUDENT(EmailID, StudentID, Name, PhNo) with primary key StudentID.
ORDER(Date, Description, StudentID, ISBN, Discount) with primary key {Date, StudentID, ISBN} , with foreign key {StudentID} and {ISBN}.
BOOK(ISBN, Publisher, Edition, Title, Authors, Price) with primary key {ISBN}.
3.2
CREATE TABLE STUDENT (
EmailID varchar(40), StudentID integer,
Name varchar(40),
PhNo integer,
PRIMARY KEY(StudentID)
);
CREATE TABLE BOOK ( ISBN varchar(100), Publisher varchar(100), Edition varchar(100), Title varchar(100), Authors varchar(40), Price real,
PRIMARY KEY(ISBN) );
CREATE TABLE “ORDER” ( Date date,
Description varchar(300),
StudentID integer,
ISBN varchar(100),
Discount real,
PRIMARY KEY(Date, StudentID, ISBN),
FOREIGN KEY(StudentID) REFERENCES STUDENT, FOREIGN KEY(ISBN) REFERENCES BOOK
);