Normal Forms in Database

A relation is in first normal form if the domain of each attributes contains only atomic values and value of each attribute contains only a single value from that hand.
#Eliminate duplicative columns from the same table
#Create separate table for each group of related data and identify each row with unique column(primary key)
A table is in 2NF if and only if it is in 1NF and no non prime attribute is dependent on any proper subset of any candidate key of the table
for example
R(A,B,C,D) is relation with AB as the candidate key and B->C as the only functional dependency then the relation is not in 2NF as C is dependent on B (the proper subset of candidate key AB)
A table is in 3NF if and only if it is in 2NF and Every non prime attribute is non transitively dependent (i.e. directly dependent on every superkey of R)
In other words 
For every non trivial Functional Dependency (X-> A) satisfied by R at least one of the following condition is true
a. X is a superkey for R or
b. A is a key atrribute of R
Boyce-Codd Normal Form (BCNF)
A relation schema R is in BCNF if and only if for every one of its dependencies X->Y at least one of the following condition hold
a. X -> Y is trivial Functiona Dependency (Y is subset of X) or
b X is a superkey for schema R.
Few points
a. All relations with only two attributes are in BCNF.
b. For any relation schema, there is a dependency preserving decomposition in 3NF
c. BCNF, 3NF, 4NF – all normal forms produces lossless join decomposition for any relation schema.
d. If there are no functional dependencies present then the relational will automatically in BCNF.
Normal Forms in Database
Rate this post

Leave a Reply