FOR FREE MATERIALS

Normalization Examples

Before seeing the examples please follow previous chapters: 

Find Super and Candidate Key from Attribute Closer,  2NF, 3NF, BCNF.

For more details about partial dependency and transitive dependency follow: 

2NF, 3NF

Example 1:

Q. R (ABCD) 

{AB → CD, D → A} 

Assume that it does not contain any multi-valued attribute. 

Find the highest normal form of this relation. 

Solution:

So, initially, we think, it is in 1NF

R (ABCD) 

{AB → CD, D → A}

To find the highest normal form of the above relation we first calculate the Candidate key, Prime attribute, and Non-prime attribute from this given functional dependencies. 

To find the candidate key we have done closer of all attributes:

For more details please follow: Find Super and Candidate Key from Attribute Closer

So, ‘AB’ is Super Key and Candidate Key also.

So, attribute ‘D’ is not a Super Key and Candidate key also.

So, attribute ‘C’ is not a Super Key and Candidate key also.

So, Candidate Key (CK) = {AB}, Prime Attribute (PA) = {A, B}, NPA (Non-prime attribute) = {C, D}

Check for 2NF:

Here AB is not a proper subset of Candidate Key. AB → AB, so, it is not Partial Dependency. It is in 2NF.

Here D → AB, so it is not Partial Dependency because D is not a subset of the candidate key and A and B is prime attribute so, it is in 2NF.

So, R (ABCD) – is in 2NF.

Check for 3NF:

Here ‘AB’ is Super Key, so, it is in 3NF, it is not Transitive Dependency.

Here ‘D’ is not Super Key but ‘A’ is Prime Attribute. So, it is not Transitive Dependency. It is also in 3NF.

So,  R(ABCD) is in 3NF also.

But it is not in BCNF why because D → A is a Functional Dependency but D is not Super Key.

So, redundancy can exist due to D → A.

We can try to decompose it:

But the problem is it is not dependency preserving because we can’t derive Functional Dependency AB → CD. 

Important note: Every 3NF we can’t convert to BCNF. Maybe it is possible or maybe not possible. So, it is not mandatory that every 3NF must be converting to BCNF.

Example 2:

Q. R (ABCDEF) 

{A → BCDEF, BC → ADEF, B → F, D → E} 

Assume it is in 1NF.

Find the highest normal form of this relation. 

Solution:

To find the highest normal form of the above relation we first calculate the Candidate key, Prime attribute, and Non-prime attribute from this given functional dependencies.

To find the candidate key we have done closer of all attributes:

For more details please follow: Find Super and Candidate Key from Attribute Closer

So, Candidate Keys = {A, BC}, Prime Attributes = {A, B, C}, Non-Prime Attribute = {D, E, F}.

Check for 2NF:

A → BCDEF:

Functional Dependency but not Partial dependency. It is in 2NF because A ⊄ Candidate Key (attribute ‘A’ itself a Candidate Key).

BC → ADEF:

It is a functional dependency, it is in 2NF because of BC ⊄ CK. So, it is not Partial Dependency (‘BC’ itself a Candidate Key).

B → F:

It is Partial Dependency (PD), it is a partial dependency because of B ⊂ CK and ‘F’ is Non-Prime Attribute (NPA). So, it is not in 2NF.

D → E:

It is in 2NF because of D ⊄ CK.

So, R(ABCDEF) not in 2NF because B → F is Partial Dependency.

So, we should decompose the table where it creates the problem, B → E, so, decompose BE as one table and the rest of the attribute as another table.

Check for lossless decomposition: 

Also, dependency preserving because we derive all Functional Dependencies as main Functional Dependencies.

Check for 3NF:

B → F where ‘B’ is Super Key so, it is in 3NF also.

A → BCDE: It is functional dependency because ‘A’ is Super Key, it is in 3NF.

BC → ADE - is in 3NF because BC is Super Key. 

D → E: It is transitive dependency, so it is not in 3NF because ‘D’ is not Super Key and ‘E’ is Non-Prime Attribute.

So,  decompose table into two parts-

It is lossless and Dependency Preserving.

Because A → BCD, BC → AD, D → E, B → F all are Super Key on the left-hand side.

When we check the highest normal form of a relation, we can check it both ways.

Example 3:

Now we check the reverse way:

R (ABCDEFGH)

{ABC → DE, E → FG, H → G, G → H, ABC → EF}

Now we can drive candidate key, prime attribute, and non-prime attribute from attribute closer.

Candidate Key (CK) = ABC, Prime Attribute (PA) = {A, B, C}, Non-prime-attribute (NPA) = {D,E,F,G,H}

First, we check for BCNF:

ABC → DE is in BCNF because ‘ABC’ is Super Key.

E → FG is not in BCNF because ‘E’ is not Super Key.

H → G is not in BCNF, ‘H’ is not Super Key.

G → H is not in BCNF, ‘G’ is not Super Key.

ABC → EF is BCNF, ABC is Super Key.

So, the highest normal form of relation.

R (ABCDEFGH) is 2NF but not in 3NF.