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

The tables in the 3NF will meet the 1NF and 2NF requirements and ,in addition, will also contain only foreign key references to the columns that are part of the referenced table. In other words, no data from the referenced table other than the foreign key would be present in the referencing table.

The 3NF representation of the given tables will look as shown below.

I have introduced two new tables "ADDRESS" and "ROOM" inorder to make the given tables into 3NF.

The "Address" table will store the address information such as street, city and zipcode with a primary key "Address_Id". This will help in replacing the address related columns in "Patient" table - Patient_street, Patient_city, Patient_zip with just the column "Address_Id" which is the primary key in the referenced table "Address". The same applies while replacing the address information from the "EMPLOYEE" table.

The "ROOM" table will separate the ROOM information to a separate table with information such as Room_no, Room_type,Ward_id. This will help in replacing the columns Room_no, Room_type,Ward_id from the "BED" table with the column "Bed_No" which is the primary key of the referenced table "BED".

Tables in the 3NF:

PATIENT (Patient_Id Patient_lastname, Patient_firstname, Patient_socsec, Address_ID, Patient_diagnosis, Patient_Phone, Patient_gender)

BED (Bed_Id, Room_no, Bed_charge)

EMPLOYEE (Employee_Id Employee_lastname, Employee_firstname, Employee_socsec, Employee_gender, Address_ID, 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)

ADDRESS (Address_ID, Address_Street, Address_City, Address_Zip)

ROOM(Room_no, Room_type,Ward_id)

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

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