You are given the following data.
Patient No, Patient First Name, Patient Last Name, Patient Address, Gender, Tel No, DOB, Marital Status, Date Reg., Next of Kin Name, Relationship, Next of Kin Address, Next of Kin Tel No, Local's Doctor's Name, Clinic No, Doctor's Address, Doctor's Tel No.
Normalize the given data into 1st then 2nd and 3rd normal form. Give justification of conversion into 1st then 2nd and 3rd normal form and the anomalies because of which you transformed it into another normal form.
Â
Answered By
James20
5 points
N/A
#95852
Data Base Related Question Required Answer
Solution:
1st NF
The table is in 1st NF as all the attributes have an atomic value.
Patient
|
Patient No
|
Patient First Name
|
Patient Last Name
|
Patient Address
|
Gender
|
Telephone No
|
DOB
|
Marital Status
|
DateReg
|
Local Doctor's Name
|
N-O-K Name
|
Relationship
|
Address
|
N-O-K Telephone No
|
|
Local Doctor's Name
|
Clinic No
|
Doctor’s Address
|
Doctor’s Telephone No
|
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 2nd NF
In second NF we have divided the patient relation into three relations that are:
1. Patient
2. Nextofkin
3. Doctor
The reason was that some of the attributes were not fully dependent on the primary key which is Patient No. Thus we divided into three relations in order to remove the partial dependency.
Patient
|
Patient No
|
Patient First Name
|
Patient Last Name
|
Patient Address
|
Gender
|
Telephone No
|
DOB
|
Marital Status
|
Date Reg
|
Local Doctor's Name
|
Nextofkin
|
Patient No
|
N-O-K Name
|
Address
|
N-O-K Telephone No
|
Relationship
|
Doctor
|
Local Doctor's Name
|
Clinic No
|
Address
|
Doctor’s Telephone No
|
3NF
In 3rd NF we have further divided the Nextofkin tables into two relations that are:
Nextofkin
Pat-NOK
We divided it because there was a transitive dependency among some of the attributes of the Nextofkin relation.
Patient
|
Patient No
|
Patient First Name
|
Patient Last Name
|
Patient Address
|
Gender
|
Telephone No
|
DOB
|
Marital Status
|
Date Reg
|
Local Doctor's Name
|
Nextofkin
|
N-O-K Name
|
Address
|
N-O-K Telephone No
|
Relationship
|
Pat-NOK
|
Patient No,
|
N O K Name
|
Relationship
|
Doctor
|
Local Doctor's Name
|
Clinic No
|
Address
|
Doctor’s Telephone No
Â
|
Â
