BCNF

Normalization

Posted on

NORMALIZATION

Fifth Normal Form (5NF)

These relations still have a problem. While defining the 4NF we mentioned that all the attributes depend upon each other. While creating the two tables in the 4NF, although we have preserved the dependencies between Vendor Code and Item code in the first table and Vendor Code and Item code in the second table, we have lost the relationship between Item Code and Project No. If there were a primary key then this loss of dependency would not have occurred. In order to revive this relationship we must add a new table like the following. Please note that during the entire process of normalization, this is the only step where a new table is created by joining two attributes, rather than splitting them into separate tables.

Project No. Item Code
P1 11
P1 12
P2 11
P3 11
P3 13

Let us finally summarize the normalization steps we have discussed so far.

Input Relation Transformation Output Relation
All Relations Eliminate variable length record. Remove multi-attribute lines in table. 1NF
1NF Relation Remove dependency of non-key attributes on part of a multi-attribute key. 2NF
2NF Remove dependency of non-key attributes on other non-key attributes. 3NF
3NF Remove dependency of an attribute of a multi attribute key on an attribute of another (overlapping) multi-attribute key. BCNF
BCNF Remove more than one independent multi-valued dependency from relation by splitting relation. 4NF
4NF Add one relation relating attributes with multi-valued dependency. 5NF

Posted By-: Vissicomp Technology Pvt. Ltd.

Website -: http://www.vissicomp.com

Normalization

Posted on Updated on

Boyce-Code Normal Form (BCNF)

  • A relationship is said to be in BCNF if it is already in 3NF and the left hand side of every dependency is a candidate key.
  • A relation which is in 3NF is almost always in BCNF. These could be same situation when a 3NF relation may not be in BCNF the following conditions are found true.
  1. The candidate keys are composite.
  2. There are more than one candidate keys in the relation.
  3. There are some common attributes in the relation
Professor Code Department Head of Dept. Percent Time
P1 Physics Ghosh 50
P1 Mathematics Krishnan 50
P2 Chemistry Rao 25
P2 Physics Ghosh 75
P3 Mathematics Krishnan 100

Consider, as an example, the above relation. It is assumed that:

  1. A professor can work in more than one department
  2. The percentage of the time he spends in each department is given.
  3. Each department has only one Head of Department.
  4. The relation diagram for the above relation is given as the following:

 

Untitled

The given relation is in 3NF. Observe, however, that the names of Dept. and Head of Dept. are duplicated. Further, if Professor P2 resigns, rows 3 and 4 are deleted. We lose the information that Rao is the Head of Department of Chemistry.

The normalization of the relation is done by creating a new relation for Dept. and Head of Dept. and deleting Head of Dept. form the given relation. The normalized relations are shown in the following.

Professor Code Department Percent Time
P1 Physics 50
P1 Mathematics 50
P2 Chemistry 25
P2 Physics 75
P3 Mathematics 100

 

Department Head of Dept.
Physics Ghosh
Mathematics Krishnan
Chemistry Rao

See the dependency diagrams for these new relations.

y1

 

 

 

 

 

Posted By-: Vissicomp Technology Pvt. Ltd.

Website -: http://www.vissicomp.com