Oracle-How to Understand 2NF

How to remember 2nf or Second Normal Form?

Look at this table:

 

STUDENT	SKILLS	BIRTHDATE
Raja	Driving	15-12-1980
Roja	Swimming	10-10-1985
Raja	Swimming	15-12-1980

In this table, how many columns are there?

Three

+++++++++++++++++++++++++++++++++++

In this table, how many attributes are there?

Three. Because, fields,attributes,columns are all same meaning

++++++++++++++++++++++++++++++++

Which is the prime attribute?

If you say STUDENT, it is wrong. prime attribute is primary key. If student is primary key, then 'Raja' should NOT repeat two times.

Actually, STUDENT + SKILLS is the prime attributes. Let us call this STUDENT +SKILLS as the candidate key from now onwards. As this candidate key is having more than one attribute, it is a COMPOSITE CANDIDATE KEY.

++++++++++++++++++++++++++++++

Which are the non prime attributes?

BIRTHDATE

+++++++++++++++++++++++++++++++++++++

Whether birthdate is fully dependent on the candidate key?

No. it is dependent on Student which is part of the candidate key.

++++++++++++++++++++++++++++++==

If a non-prime attribute is PARTLY dependent on candidate key, then it is not statisfying 2NF.

You should remove this Partly dependent things and put it in a separate table, as shown here.

 

STUDENT	BIRTHDATE
Raja	15-12-1980
Roja	10-10-1985

 

Now let us see the formal definition of 2nF

A 1NF table is in 2NF if and only if NONE of its non-prime attributes are functionally dependent on a PART of a candidate key.

 

 

++++++++++++++++++++++++++++++++++++

 

*********************

How to remember 3nf or Third Normal Form?

The easy way to remember 3nf: Imagine you are in the court room and taking the oath:

"Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key."

 

Please refer back to 2NF. In case of 2NF,every non-key attribute must depend on the WHOLE KEY. But "NOTHING BUT THE KEY" talks about 3NF

Look at this table:

TOURNAMENT	YEAR	WINNER	WINNER BIRTHDATE
Wimbledon	2009	Federer	1-1-1980
Wimbledon	2008	Nadal	10-10-1981
Wimbledon	2007	Federer	1-1-1980

In the above example the key is TOURNAMENT + YEAR, The non key WINNER is depend on Key. But the other non-key WINNER BRITHDATE does not depend on the key. It depends on another non-key. so it is NOT satisfying 3NF.

 

+++++++++++++++++++++++++++++++++++++

 

The formal defnition for 3NF:

Atable is in 3NF if and only if both of the following conditions hold:

The relation R (table) is in second normal form (2NF)
Every non-prime attribute of R is non-transitively dependent (i.e. directly dependent) on every key of R.