Data Base Related Question Required Answer

Asked By petersulliva 0 points N/A Posted on - 07/30/2011

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.

 

• Status: Open
• Question Views: 857
• Answer Count: 1
• Vote Up 0 Vote Down

• Answer Accepted: No
• Question Category: MS SQL Server

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

 

About James20

Questions
0

Answers
104

Best Answers
69

• Vote Up 0 Vote Down

• Posted on - 07/30/2011
• Question Category: MS SQL Server