EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials Data Structures Tutorial BCNF
 

BCNF

Priya Pedamkar
Article byPriya Pedamkar

Updated March 24, 2023

BCNF

 

 

What is BCNF?

BCNF can be expanded as Boyce Codd Normal Form, the fourth form of normalization on a database. It is necessary to normalize the Multidimensional database systems up to the last level of normalization until there is no more space for normalization to be carried out anymore. Normalization reduces or removes redundancy in the data, in turn, to maintain the database without any duplicate values and bring inconsistency to the data in the database. It can be applied to a database that obeys two conditions; namely, it should be in 3NF stage and when at least one of the reference tables consists of a primary key.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

A relation or a table which is in Boyce Codd Normal Form is by default considered to an in all the below forms.

  • 1NF
  • 2NF
  • 3NF

There are other normal forms such as 4NF and 5NF, but they are rarely used.

How does BCNF Work?

A table is said to be BCNF when it satisfies the below two concepts-

  1. It is in 3NF.
  2. A->B, A should be a super key or a candidate key for any functional dependency. In other words, if B is a prime attribute, A cannot be a non-prime attribute.

To understand more, few concepts need to be discussed, such as keys and attributes.

  • Attributes: Attributes that are a part of the candidate key are called prime attributes, and the rest of the attributes are known as Non-prime attributes.
  • Super Key: This is the combination of columns that will uniquely identify the rows in a table. A candidate key is selected from the given super keys based on the minimum number of attributes. And the primary key is one among the candidate keys.

Consider a Students table with the attributes- StudentID, Roll_no, Name.

Super Key:

{StudentID}
{Roll_no}
{StudentID, Roll_no}
{StudentID, Name}
{Roll_no, Name}
{StudentID, Roll_no, Name}

Candidate Key:

{StudentID}
{Roll_no}

Primary Key:

{StudentID} or {Roll_no}

  • Functional Dependency: It is the relationship between two attributes- one known as the determinant and the dependent. An FD has the given representation X->Y, which states that X determines Y.

Decomposition into BCNF

When a table is in 3NF, it may or may not be in the Boyce Codd Normal Form. Each table/relation will have a set of functional dependencies. If the FD does not satisfy the second condition of BCNF, the table is decomposed (breaking into smaller tables) recursively until all the functional dependency meets the super key criteria.

The algorithm to be followed for decomposition is,

  1. Determine the functional dependency that violates the BCNF.
  2. For every functional dependency X->Y which violates, decompose the relation into R-Y and XY. Here R is a relation.
  3. Repeat until all the relations satisfy BCNF.

Examples to Implement BCNF

Below are the examples:

Example #1

Let’s consider a Relation R with five attributes.

R=ABCDE

The functional dependencies are

FD = {A -> BC, C -> DE)

Candidate keys are {A}

Algorithm:

Inspect each of the FD to check whether it satisfies the second condition of BCNF as it is in 3NF.

  • The first FD A -> BC since A is a key for R this FD does not violate BCNF.
  • Second FD C -> DE, C is not a key of R. We decompose R into (CDE) (ABC).

The two schemas are created with the FD attributes, which violates and the other with original attributes minus the right-hand side of the violating FD. Now we will check both the newly created relations to check whether they are in BCNF or not. A is the key in (ABC), and C is the key (CDE) they do not violate BCNF. Thus the relation is in BCNF.

Example #2

Let’s consider a Relation R with five attributes.

R = (WXYZ)

The functional dependencies are

F = {WX -> Y, X -> Z; Y -> W}

Candidate keys are {WX, XY}

Algorithm:

  • For the first FD WX -> Y, since WX is a key for R, it does not violate BCNF.
  • Second FD, X -> Z violates BCNF as X is not a key. Thus we create two relations (XZ) and (WXY).

Now inspect the given two relations for BCNF,

For (XZ), the candidate key is X. The only FD that applies here is X -> Z, so it is in BCNF.

For (WXY), the candidate keys are WX and XY.

  1. The first FD applies, WX -> Y, and WX is a key, so it is in BCNF.
  2. The second FD doesn’t apply (there is any Z in it).
  3. The third FD, Y->W, Y, is not a super key; thus, we need to decompose by creating a new relation.

(XZ)(YW)(XY)

  • XZ: The XZ is still in BCNF as before.
  • YW: The YW has Y as the candidate key, and the only FD that applies is Y-> W. It is in BCNF.
  • XY: The XY has XY as the candidate key, and no FDs apply, so it is in BCNF.

Thus our final decomposition is:

(XZ)(YW)(XY)

Advantages

  • It is a more restricted form of normalization so that the database does not end in anomalies.
  • The business rules expressed in functional dependencies are enforced using keys, and BCNF ensures that it is correctly followed.

Conclusion

The fourth and restricted form of normalization is BCNF, and it makes sure that the table is in 3NF and for each functional dependency, the determinant is a candidate or super key. If a relation is in BCNF, it will satisfy 1NF, 2NF, and 3NF by default. It ensures that for every functional dependency X->Y, X is a super key of the table, making BCNF a stronger variation of 3NF.

Recommended Articles

This is a guide to BCNF. Here we discuss BCNF and how it works, decomposition, advantages, and examples. You may also look at the following articles to learn more-

  1. Top 7 Types of DBMS Keys
  2. What is Hashing in DBMS?
  3. RDBMS Interview Questions
  4. Normal Forms in DBMS

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW