Q1
(a)
(b)
Relationship: Has
Because ¡°Patients can appear on our system before they have a medical record.¡±, so a patient may have no record. From specification, we also know that patient can have many records. So the cardinality for record is (0,*).
Because a record must belong to one and only one patient, so the cardinality for patient is (1,1).
Relationship: Carry
Because ¡°An operation is carried out by one or several doctors on a single patient¡±, so the cardinality for doctor is (1,*), and the cardinality for patient is (1,1). From common sense, a doctor may have no operation or many operations on a patient, so the cardinality for operation is (0,*).
Relationship: Made
Because ¡°Such a record must have been made by exactly one doctor.¡±, so the cardinality for doctor is (1,1), from ¡°Some doctors (e.g. in training) may not be allowed to diagnose patients¡±, we know that a doctor may make no medical records, so the cardinality for record is (0,*).
Relationship: Mentor
Because ¡°Some doctors may have one mentor who is also a doctor. ¡± indicates that a doctor can have no mentor or 1 mentor, so the cardinality for mentor is (0,1). ¡°Only a few senior doctors are
mentors and each usually has four or five tutees. ¡° indicates that a doctor may have no menteree, can also have many tutees, so the
Q2
PhoneNumbers(phoneNumber, nis_no)
primary key (phoneNumber, nis_no)
foreign key nis_no references Employee (nis_no)
Employee(nis_no, street_name, postcode, house_no) primary key nis_no
Supplier(supplier_no, supplier_name) primary key supplier_no
Project(proj_no, estimated_cost, supplier_no, city_name, state) primary key proj_no
foreign key supplier_no references Supplier(supplier_no) supplier_no not null
foreign key (city_name, state) references City(name, state)
Participates(contract_no, nis_no, proj_no) primary key contract_no
foreign key nis_no references Employee(nis_no) foreign key proj_no references Project(proj_no)
is_Married_to(person, married_to, m_date) primary key person
foreign key person references Employee(nis_no) person not null
foreign key married_to references Employee(married_to) married_to not null
City(name, state, population) primary key (name, state)
Q3
1. A person can’t be married to himself/herself. 2. The law may not allow same sex marriage.