EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Data Structures Tutorial BCNF
Secondary Sidebar
Data Structures Tutorial
  • Basics
    • Linked List Advantages
    • What is Data Structure
    • Heap Data Structure
    • Types of Trees in Data Structure
    • AVL Tree in Data Structure
    • B Tree in Data Structure
    • B+ Tree in Data Structure
    • DFS Algorithm
    • BFS Algorithm
    • Arrays in Data Structure
    • Graph in Data Structure
    • Graph Representation
    • Breadth First Search
    • Depth Limited Search
    • Hashing in Data Structure
    • Searching in Data Structure
    • Linear Search in Data Structure
    • Linked List in Data Structure
    • Doubly linked list in Data Structure
    • Circular Linked List in Data Structure
    • Pointers in Data Structure
    • Types of Graph in Data Structure
    • Bubble Sort in Data Structure
    • Quick Sort in Data Structure
    • Bitonic Sort
    • Merge Sort in Data Structure
    • Selection Sort in Data Structure
    • Insertion Sort in Data Structure
    • Radix Sort in Data Structure
    • Stack in Data Structure
    • Queue in Data Structure
    • Priority Queue in Data Structure
    • Asymptotic Analysis
    • Tree Traversal in Data Structure
    • Tree Traversal Techniques
    • Trie Data Structure
    • Splay Tree in Data Structure
    • Spanning Tree Algorithm
    • Sparse Matrix in Data Structure
    • Radix Sort Algorithm
    • Counting Sort Algorithm
    • Skip List Data Structure
    • Linked List Algorithm
    • Linked List Types
    • Inorder Traversal of Binary Tree
    • Kruskals Algorithm
    • Prims Algorithm
    • BFS VS DFS
    • BCNF
    • Skip List
    • Hash Table?in Data Structure
    • Data Structure Interview Questions
    • Data Structures & Algorithms Interview
    • AVL Tree Deletion
    • B+ Tree Deletion
    • Decision Tree Advantages and Disadvantages
    • Data Architect Skills
    • Data Architecture Principles
    • Data Engineer Jobs
    • Data Engineer Roadmap
    • Fundamentals of Data Structure
    • Circular queue in Data Structure
    • Spanning Tree in Data Structure
    • Tree traversal types
    • Deque in Data structure
    • Shell Sort in Data Structure
    • Heap sort in data structure
    • Heap data structure C++
    • Heap data structure in Java
    • Binary Search Tree Types
    • Binary Tree in Data Structure
    • Binary Tree Types
    • Binary search tree in data structure
    • Binary Search Tree Advantages
    • Binary Search Tree Properties
    • Binary Search in Data Structure
    • Binary Tree Deletion
    • Sparse Matrix Multiplication
    • Preorder Traversal of Binary Tree
    • Postorder traversal
    • Decision Tree Hyperparameters
    • PostOrder Traversal without Recursion
    • AVL Tree Rotation
    • Avro File Format
    • Decision Tree Types
    • Binomial heap
    • Confluence Jira Integration
    • Timm Sort
    • Depth First Search
    • Stock Span Problem

BCNF

By Priya PedamkarPriya Pedamkar

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.

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

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

  • 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
Popular Course in this category
All in One Data Science Bundle (360+ Courses, 50+ projects)
  360+ Online Courses |  1500+ Hours |  Verifiable Certificates |  Lifetime Access
4.7
Price

View Course

Related Courses

Oracle DBA Database Management System Training (2 Courses)4.9
SQL Training Program (10 Courses, 8+ Projects)4.8
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

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

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*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?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more