Showing posts with label Normal Forms. Show all posts
Showing posts with label Normal Forms. Show all posts

Steps to decompose a non-2NF relation to a 2NF relation



How to decompose a non-2NF relation into a 2NF relation? / Decomposing a relation that consists partial functional dependencies / Steps in decomposing a table into a 2nf table


2NF properties:


-Relation should be in 1NF.

-No partial functional dependency must present. (All the non-key attributes must depend on the whole key / key attribute)

Decomposition steps:
If any partial functional dependency (partial FD is considered only when the key is a composite key) present in a table/relation that you normalize, then you should decompose (break) that relation into two or more relations depend on the set of functional dependencies. To decompose the relation, you can follow these simple steps;

Step 1: Create a separate relation for each partial dependency


Step 2: Remove the right hand side attribute of the partial dependency from the relation that is being decomposed.

Example 1:
Flight_ID
Flight_Day
Pilot
Boarding_Gate
IC123
Monday
Kesav
2
IC123
Tuesday
Mark
2
IC217
Wednesday
Kesav
3
IA156
Monday
Steve
1

For this Flight_Schedule table, the following is the set of functional dependencies;
F = { Flight_ID Flight_Day Pilot Boarding_Gate, Flight_ID Boarding_Gate}
This table is in 1NF, but not in 2NF because of the FD Flight_ID Boarding_Gate. In our example, the key is (Flight_ID, Flight_Day). These two attributes together can identify the Pilot value uniquely. But for identifying the other attribute Boarding_Gate, the attribute Flight_Day is enough [Flight_Day is part of the composite key of this relation].

Now, let us apply the steps shown above.

Step 1: Create a separate relation for each partial dependency. In our example, Flight_ID Boarding_Gate is the partial dependency. Hence we need to create a separate relation for this FD. Let us name this relation as Boarding.
Boarding ( Flight_ID, Boarding_Gate)

Step 2: Remove the right hand side attribute of the partial dependency from the relation that is being decomposed. In the relation Flight_Schedule (Flight_ID, Flight_Day, Pilot, Boarding_Gate), the attribute Boarding_Gate should be removed as per this condition. The reason is, Boarding_Gate is the right hand side (RHS) attribute of the partial dependency, Flight_ID Boarding_Gate. Hence,
Flight_Schedule (Flight_ID, Flight_Day, Pilot).

Thus, Flight_Schedule (Flight_ID, Flight_Day, Pilot, Boarding_Gate) is decomposed into Flight_Schedule (Flight_ID, Flight_Day, Pilot) and Boarding ( Flight_ID, Boarding_Gate).

Example 2:

Assume a relation R (A, B, C, D, E) with the following set of functional dependencies;
F = {AB C, B D, E D}
The key for this relation is ABE. Then, all three given FDs are partial dependencies, viz., AB C, B D, and E D.
Step 1: separate tables for partial dependencies; hence, R1 (ABC), R2 (BD) and R3 (ED).

Step 2: remove RHS of these two partial FDs from R; hence, R4(A, B, E).

Thus, we have four tables R1 (ABC), R2 (BD), R3 (ED) and R4 (ABE).



Comparison of Normal Forms


Compare Normal Forms / 1NF vs 2NF vs 3NF vs BCNF / Differentiate between normal forms



Properties to hold
All the attributes of the relation are atomic (indivisible into meaningful sub parts),
Every attribute contains single value (per record).
At the first place the table is in 1NF,
All the non-key attributes of the table are fully functionally dependent on the Primary key of the table.

The table is in 2NF,
There is no Functional Dependency such that both Left Hand Side and Right Hand Side attributes of the FD are non-key attributes. In other words, no transitive dependency is allowed
For all the Functional Dependencies (FDs) hold in the relation R, if the FD is non-trivial then the determinant (LHS of FD) of that FD should be a Super key
Achievability
Always achievable
Always achievable
Always achievable
Not always
Lossless Join Decomposition
Always achievable
Always achievable
Always achievable
Sometimes not achievable
Dependency Preserving Decomposition
N/A

Possible
Either lossless join or dependency preserving decomposition is possible. Not both.
Anomalies
May allow some anomalies
May allow some anomalies
May allow some anomalies
Always eliminates anomalies
What is eliminated?
Eliminate repeating groups
Eliminate redundant data
Eliminate columns not dependent on key
Eliminate multiple candidate keys
Identification of Functional Dependencies
Not necessary
Must
Must
Must
Attribute Domain
Should be atomic
Should be atomic
Should be atomic
Should be atomic
Handling of Update Anomalies
Does not handle.
Handles
Handles
Handles
Composite Primary Key
Allowed
Allowed (if no partial dependency exists)
Allowed
Not allowed
Partial key dependencies
(if AB C, and if C can be fully determined by either A or B, then this dependency is partial key dependencies)
Permitted
Not permitted
Not permitted
Not permitted
Transitive dependencies (if A B, and B C then A C)
Can be permitted
Can be permitted
Cannot be permitted
Cannot be permitted
Overview
It is about shape of a record type
It is about the relationship between key and non-key fields
It is about the relationship between key and non-key fields
It is about determinant should be a superkey.

*Please give your valuable input

Popular Posts