Normalization: BCNF, 4NF and 5NF

 

   Boyce - Codd Normal Form (BCNF):

 

A relation is in Boyce Codd normal form if every attribute (for every set of attributes) on which some other attributes is fully functionally dependent is also a candidate key for a primary key of the relation (i.e. Candidate key).

 

Consider the following relation:

The candidate for primary key are:

 

·         Member + Sport

·         Member + Coach

 

 because either of these uniquely identifies the row.

 

The attribute Coach is not a candidate for a primary key even though the attribute Sport is fully functional dependent on it, since a Coach is associated with only one Sport (assumption a coach is an expert in one sport, though he can coach other sports also).

 

Since the relation has an attribute (Coach) on which some other attribute is fully functionally dependent, but it is not a candidate for the primary key, the above relation is not fully normalized. The above relation will result in the following problems:

 

If Mr Rajesh Iyer withdraws from the membership, information about coach Rane is also lost.

 

If coach Kurian’s name is mis-spelt and has to be corrected to Kurien, the update needs to be done in all the rows.

 

A better implementation of the situation can be made through the following relations.

By this process we have reduced the 3NF to BCNF by ensuring that they are in 3NF for every candidate key.

Ex 2:

The relations can be further decomposed to 4NF and 5NF if still there exists multivalued dependencies. However, this decomposition may lead to increased overhead on the system like maintaining the complex relationships between the tables, indexing mechanism, etc.

 Fourth Normal Form (4NF):

 

A relation is in the 4NF, if it is in the BCNF and does not have any independently multi-valued components of the primary key.

 

Assume that the Club keeps records of its members. For each member it keeps a list of sports that the member is most interested in and a list of most striking characteristics of each member which are inherent in the member and are in no way dependent on the sport.

 

Ex 1:

According to the above structure, if another interest for example Cycling  is added for Mr Rao then, 2 more additional rows will be added - one for High Stamina  and another for Poor Speed. Thus, the above relation is normalized further to get 2 relations.

Thus, we have reduced BCNF entity relation into 4NF by splitting into two relations the independently multivalued component of the primary key.

 Ex 2:

Fifth Normal Form (5NF):

 

It is also called as Project Join Normal Form (PJNF).

If any relation has functional, multivalued and join dependencies then it can be decomposed into 5NF. For ex: consider the Loan relation with the following attributes.

 

(loan_no, branch_no, customer_no, amount)

 

This relation can be defined as a set of all tables on the loan relation such that:

 

·         The loan represented by loan_no   is made by the branch represented by the branch_no.

 

·         The loan represented by the loan_no is made to the customer represented by the customer_no.

 

·         The loan represented by loan_no is in the amount represented by amount.

 

Thus, the definition of the above relation is in the conjunction of 3 predicates with join dependencies:

 

·         on loan_no and branch_no.

·         on loan_no and customer_no.

·         on  loan_no and amount.

 

To put the Loan relation into PJNF, we must decompose it into 3 relations specified by the join dependency:

 

·         Loan_Branch (loan_no, branch_no)

·         Loan_Customer (loan_no, customer_no)

·         Loan_Amount (loan_no, amount)