3NF

[azizrasul]

I have the following table:-

tblPatients

PatientID
Forename
Surname
DateofBirth

What kind of field (other than a calculated field) could I add, such that the table would not be in 3nf? The purpose of the question is to make me understand 3nf. Thanks in advance.

 

[anvil19]

Hi azizrasul

A table is said to be in Third Normal Form if it's in Second Normal Form and if all nonkey columns are mutually independant.

If you chose to add the day (a Monday, a Tuesday...etc.) on which the patients birthday fell then the table would be said not to be in Third Normal Form because the data would depend on the value in DateOfBirth.

Tables normally do not qualify for 3NF because data depends in an ID to describe a value - a product Id describes a product in the same table - and that's not 3NF valid.

any help??

anvil19

 

[azizrasul]

I have the following table:-

tblPatients

PatientID PK
Forename
Surname
NextofKin
NextofKinDetails

I know that it's possible to have more than one patient who can share the details of the SAME next of kin. Also the next of kin can be a patient themselves. However if we ignore these sceanrios for the moment, would the following changes: -

tblPatients
========
PatientID PK
NOKID FK
Forename
Surname

tblNOK
=====
NOKID PK
Forename
Surname
TelNo


What I am interested in and the difficulty I am having is, does the creation of tblNOK constitute 2nf OR 3nf? Or does the the fact that if the 2 points that I asked u to ignore above would mean 2nf or 3nf.

Any help would be appreciated.

 

[anvil19]

Hi Aziz

tblNOK is in 3NF (and therefore 2NF too) because none of the values are dependant of any other values for their data.

2NF is valid where all values are fully dependant on the entire primary key field. So tblNOK is valid to both 2NF and 3NF.

Having both tables, the second tblPatients and tblNOK, is excellent use of the database normalisation rules. The first tblPatients has data (details for the next-of-kin for example) that is not fully dependant to the patientID key, but is peripheral information. The NOKID field takes care of that. It's fully dependant on the primary key as a field(2NF) but not for it's value(3NF). That's why the second scenario is the right one.

anvil19

 

[Aladin Akyurek]

I have the following table:-

tblPatients

PatientID PK
Forename
Surname
NextofKin
NextofKinDetails

I know that it's possible to have more than one patient who can share the details of the SAME next of kin. Also the next of kin can be a patient themselves. However if we ignore these sceanrios for the moment, would the following changes: -

tblPatients
========
PatientID PK
NOKID FK
Forename
Surname

tblNOK
=====
NOKID PK
Forename
Surname
TelNo


What I am interested in and the difficulty I am having is, does the creation of tblNOK constitute 2nf OR 3nf? Or does the the fact that if the 2 points that I asked u to ignore above would mean 2nf or 3nf.

Any help would be appreciated.

What kind of entity does tblNOK house?

 

[azizrasul]

The tblNOK houses the details of the next of kin of the patient in tblPatients.

 

[Abulafia]

The second attempt is better - go with it.

If you are ever allowed or need multiple options for next of kin then I'd suggest a junction table:

tblPatientsToNOK
PatientID
NOKID

where both fields consititute the primar key but both are foreign keys too as they link to the relevant Patient or NOK table.

 

[azizrasul]

Many thanks.

 

[Aladin Akyurek]

The second attempt is better - go with it.

If you are ever allowed or need multiple options for next of kin then I'd suggest a junction table:

tblPatientsToNOK
PatientID
NOKID

where both fields consititute the primar key but both are foreign keys too as they link to the relevant Patient or NOK table.

Two disconnected tables in a single database look strange (to me). Although the OP asked to disregard "I know that it's possible to have more than one patient who can share the details of the SAME next of kin. Also the next of kin can be a patient themselves.", the issue is far too real in practice. A table like tblPatientsToNOK as you suggested makes the issue unavoidable.

P1 is in tblPatients.
N1 is in tblNOK

N1 becomes a patient after a while. Now, how to avoid the redundancy, that is, having N1 in both tblPatients and tblNOK?