Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

bcnf

What is BCNF?

Zeerak Babar

Overview

BCNF stands for Boyce-Codd normal form, which is a special case of 3NF and is also known as 3.5 NF. BNCF is a normal form used in the normalization of databases and has more strict rules as compared to 3NF.

BCNF rules

To check if the table satisfies the conditions of BCNF, the following two conditions should exist:

  • The table must be in 3NF form.
  • For any dependency X → Y, X must be a candidate key or super key. In other words, for dependency X → Y, if Y is a prime attribute, X cannot be a non-prime attribute.

3NF states that the transitive dependency must not exist. Transitive dependency is that the LHS (left-hand side) of the functional dependency must consist of a super key/candidate key or the RHS (right-hand side) must have a prime attribute. BCNF adds more restrictions by stating that LHS of functional dependency must have a super key and removes the RHS condition.

Example of BCNF

Assume there is a hospital where an employee works in more than one department.

Employee table

Emp_ID

Nationality

Emp_Dept

Dept_Type

Dept_No

#088

Pakistan

Surgery

X12

301

#088

Pakistan

Dental

X12

482

#112

Canada

General Medicine

X97

212

#112

Canada

Radiology

X97

356

Functional dependencies

  • Emp_ID → Nationality
  • Emp_Dept → {Dept_Type, Dept_No}

Candidate key

  • {Emp_ID, Emp_Dept}

In this example, the table is not in BCNF form as both the Emp_ID and Emp_Dept alone are not keys. To convert the table into BCNF form, decompose the table into three tables based on the functional dependency.

Nationality table

Emp_ID

Nationality

#088

Pakistan

#112

Canada

Dept table

Emp_Dept

Dept_Type

Dept_No

Surgery

X12

301

Dental

X12

482

General Medicine

X97

212

Radiology

X97

356

Dept Mapping table

Emp_ID

Emp_Dept

#088

Surgery

#088

Dental

#112

General Medicine

#112

Radiology

Functional dependencies

  • Emp_ID → Nationality
  • Emp_Dept → {Dept_Type, Dept_No}

Candidate key

  • Nationality Table: Emp_ID
  • Dept Table: Emp_Dept
  • Dept Mapping Table: {Emp_ID, Emp_Dept}

The relation is now in BCNF form because it satisfies both conditions which are that the table is already in 3NF form and on the LHS of the functional dependency there is a candidate key.

RELATED TAGS

bcnf
RELATED COURSES

View all Courses

Keep Exploring