Normalize the following entities to 3NF: PATIENT (Patient_Id Patient_lastname, P

Question

Normalize the following entities to 3NF:

PATIENT (Patient_Id Patient_lastname, Patient_firstname, Patient_socsec, Patient_street, Patient_city, Patient_zip, Patient_diagnosis, Patient_Phone, Patient_gender)

BED (Bed_Id, Room_no, Room_type, Ward_id, Bed_charge)

EMPLOYEE (Employee_Id Employee_lastname, Employee_firstname, Employee_socsec, Employee_gender, Employee_street, Employee_city, Employee_zip, date_hired, Employee_phone, Qualification)

DEPARTMENT (Department_Id, Department_Name, Department_location, Department_phone)

TREATMENT (Treatment_code, Treatment_description, Treatment_result, Treatment_date)

TEST (Test_Id, Test_type, Test_duration, Test_charge)

Explanation / Answer

Normalizing to 3NF:

Prerequisites for 3NF:

1. Contains only Atomic value (1NF)

2. All the non key attributes are functionally dependant to primary key. (2NF)

3. No transitive function dependancy (3NF)

Regarding the given relation.  

PATIENT (Patient_Id Patient_lastname, Patient_firstname, Patient_socsec, Patient_street, Patient_city, Patient_zip, Patient_diagnosis, Patient_Phone, Patient_gender)

BED (Bed_Id, Room_no, Room_type, Ward_id, Bed_charge)

EMPLOYEE (Employee_Id Employee_lastname, Employee_firstname, Employee_socsec, Employee_gender, Employee_street, Employee_city, Employee_zip, date_hired, Employee_phone, Qualification)

DEPARTMENT (Department_Id, Department_Name, Department_location, Department_phone)

TREATMENT (Treatment_code, Treatment_description, Treatment_result, Treatment_date)

TEST (Test_Id, Test_type, Test_duration, Test_charge)

Since there is no atomic values in the above give relation it already satisfies the 1NF.

2nd Normal Form:

Actually in 2nd normal form there should be only one Primary key in all the relations. However in the relation Bed,

BED (Bed_Id, Room_no, Room_type, Ward_id, Bed_charge), here it exits 2 Pk hence it was further divided into

BED(Bid_id, Bed_charge, Room_type) Bed_id - PK

Room( Room_no,Ward_id, Room_type) Room_no -PK

3rd Normal form

There should be no transitive dependencies among the entitites in above relation, to satisfy the 3NF.

While looking at the above attributes, there were no transitive dependencies among the relation, Hence below is the 3NF of the give relation.

PATIENT (Patient_Id Patient_lastname, Patient_firstname, Patient_socsec, Patient_street, Patient_city, Patient_zip, Patient_diagnosis, Patient_Phone, Patient_gender)

BED(Bid_id, Bed_charge, Room_type) Bed_id - PK

Room( Room_no,Ward_id, Room_type) Room_no -PK

EMPLOYEE (Employee_Id Employee_lastname, Employee_firstname, Employee_socsec, Employee_gender, Employee_street, Employee_city, Employee_zip, date_hired, Employee_phone, Qualification)

DEPARTMENT (Department_Id, Department_Name, Department_location, Department_phone)

TREATMENT (Treatment_code, Treatment_description, Treatment_result, Treatment_date)

TEST (Test_Id, Test_type, Test_duration, Test_charge)

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

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