Tables in Third Normal Form (3NF)

C.J. Date defines 3NF as…

“A table is in 3NF if and only if it is in 2NF and every non-key attribute is nontransitively dependent the primary key.”1

This too is a difficult definition to understand. Stated in other terms, if a non-key attribute has a dependency on another non-key attribute then the table is not in third normal form (3NF). Let’s look a few examples of tables that are not in third normal form (3NF) so we can discuss this further.

In the example below the “Winner Date if Birth” clearly has a dependency on the “Winner” column. It is definitely an inappropriate violation of third normal form.

(Note: this example is borrowed from Wikipedia)

Tournament

Year

Winner

Winner Date of Birth

Des Moines Masters

1998

Chip Masterson

14-Mar-77

Indiana Invitational

1998

Al Fredrickson

21-Jul-75

Cleveland Open

1999

Bob Albertson

28-Sep-68

Des Moines Masters

1999

Al Fredrickson

21-Jul-75

Indiana Invitational

1999

Chip Masterson

14-Mar-77


We can however find more subtle violations that can be appropriate. The following is an example that violates 3NF. Below is a partial table to identify medical findings for a patient. The finding_subtype_code has dependency on the service_type_cde column. This is an example of a table that is not in 3NF, but the violation is more than appropriate.

patient_fk

finding_type_cde

 data_create_timestamp

service_type_cde

finding_subtype_cde

11

FINDNG

1/6/2006  9:37:00 AM

ASSESS

COMA

11

FINDNG

1/6/2006  9:37:00 AM

ASSESS

COMA

12

FINDNG

10/30/2006  3:56:50 PM

MISC

NOTE

12

FINDNG

10/30/2006  4:03:24 PM

NURSNG

NOTE

12

FINDNG

10/30/2006  4:05:17 PM

NURSNG

NOTE

20

FINDNG

3/18/2003  11:29:40 AM

ASSESS

VSRCL


Whether a table is in third normal form or not really is predicted on the specific business context of the table itself.

1 C.J. Date. An Introduction To Database Systems 6th Edition. Addison-Wesley. p 302

 

I like to get feedback but do not have time to combat all the spammers out there. The best means to contact me is by sending me a message through LinkedIn (I think you need to be a member to do so). Once you signup, just request to add me to your network and I will accept. I will do my best to respond to your message.

View Eric Matthews's profile on LinkedIn



Back to sqlinfo.net homepage.

Homepage
By Eric Matthews on sqlinfo.net