Question

Ensure that the design is normalised to the Third Normal Form. Please show the steps (10 marks)

Please help normalize that design!

Assigned Staff start_ld - int(5) PK name-varchar(20) date of birth-date Patient patient_id - int(5) PK name-varchar(50) address - varchar(50) gender-varchar(50) health condition (100) unit_id - int(5) FK staff_id - int(5) FK salary - decimal(5,2) 1 M Manager manager_id-int(5) PK name - varchar(20) salary-decimal(5.2) Manages date of birth-date 1 position - varchar(20) qualification - varchar(20) retirement_date-date re-employment_date-date unit_id - int(5) FK manager_id(5) FK M M Manages Admitted 1 Works Unit unit_id - int(5) PK name-varchar(20) unit_type - varchar(20) center_name-varchar-unique manager_id - int(5) FK

Answer 1

The given design has been redesigned to ensure that it is in 3rd normal form (3NF) A table is said to be in 3NF if it is in 2NF (no partial dependencies) and should not have any transitive dependencies. So the given tables have been normalized to 3NF in my revised ER diagram below which does not have transitive dependencies and does not have any insertion, update and deletion anomalies How normalized 1. Staff is denormalized in the given diagram which is wrong and unnecessary. So the Staff table has been normalized to have the manager_id as a recursive foreign key ,which means that the primary key staff_id will be the manager_id of other staffs as foreign key in the same table So staff_ld will be the manager_id. 2. Unit is an independent parent/super entity which is dependent by the child/sub entities patient and the staff and the manager. So the primary key unit_id of the unit is being referred as the foreing key in the staff table and the patient table 3. An unit has one to many patients as one to one (1,1) and one to many relationships of cardinalities 4. An unit has one to many staff as one to one (1,1) and one to many(1,M) relationships of cardinalities 5. An unit may have one manager as one to one (1,1) while many unit have many managers many to many(1,M) relationships of cardinalities

Unit 1.1 O PK 1,1 admitted unit_id : int(5) name : varchar(20) unit_type: varchar(20) center_name : varchar(20) (U) Patient 1.M 04 PK patient_id int(5) name : varchar(50) address : varchar(50) gender : varchar(50) health_condition : varchar(100) FK1 unit_id: int(5) FK2 staff_id: int(5) 1.M assigned H11 works Staff 1,1 HPK staff_id: int(5) FK1 name : varchar(20) Date_of_Birth date salary date manages position varchar(20) qualtification varchar(20) retirement_date date re_employment_date date 1,M 04 FK1 manager_id int(5) 0 FK2 unit_id int(5) MN