FOR FREE CONTENT

3NF:

Before seeing this chapter follow previous chapters: 

Concept of Normalization, 2NF, Find Super and Candidate Key from Attribute Closer.

Definition: A relation schema R is in 3NF if it is already in 2NF and should not contain any transitive dependency. 

Transitive Dependency: X → Y is transitive dependency when  X is not Super Key  and  Y is Non-Prime Attribute. 

Functional Dependency: X → Y is functional dependency when  X is Super Key  or  Y is Non-Prime Attribute. 

Or

(R is in 3NF) ⇔ [(should not contain any Transitive Dependency) + (R is in 2NF)]

Example:

Let R (ABCDEF), CK = {AB, CD}, PA = {A, B, C, D}, NPA = {E, F}

ABE → F is in 3NF because ‘ABE’ is Super Key.

A → E is not in 3NF because ‘A’ is not Super Key and ‘E’ is NPA.

A → BE then checks A → B , A → E.

A → B is in 3NF because ‘A’ is not Super Key but ‘B’ is PA.

A → E is not in 3NF because ‘A’ is not Super Key and ‘E’ is NPA.

So, because A → E is Transitive Dependency then A → BE is not in 3NF.

From 2NF (See 2NF) we got:

Here AB → C – Functional Dependency because AB is Super Key but C → D Transitive Dependency because C is not Super Key, and D is Non-Prime Attribute.

Here B → E – Functional Dependency because B is Super Key.

So, this redundancy is coming from Transitive Dependency C → D.

So, we can decompose ‘CD’ which creates the problem and keep the rest of the attribute on one side.

If a relation where all attribute is the prime attribute and there is not multi-valued attribute then it is already in 3NF.

Here we decompose R in their table ${\mathbf{R}}_{\mathbf{11}}\mathbf{,}\mathbf{ }{\mathbf{R}}_{\mathbf{12}}\mathbf{,}\mathbf{ }{\mathbf{R}}_{\mathbf{2}}$ and we reduce redundancy created by Functional Dependencies at 3NF level but here maintenance cost of table and query processing time (at the time of join) will be increased.