What is BCNF Boyce Codd Normal Form. Explain it.
Database Management System
Computer Science Engineering
470
Seema
Boyce-Codd Normal Form (BCNF) is a database normalization technique used to eliminate data redundancy and improve data integrity. BCNF is an extension of Third Normal Form (3NF) and applies to tables that have more than one candidate key.
A relation is in BCNF, if and only if, every determinant is a Form (BCNF) candidate key.
In BCNF, a table is said to be in BCNF if for every functional dependency (X → Y), X is a superkey. This means that all non-key attributes in a table should be functionally dependent on the candidate keys, and not on any other non-key attributes.
To explain BCNF with an example, consider a table called "Students" with the following columns:
In this table, both "Student ID" and "Course ID" are candidate keys. However, the non-key attribute "Department Name" is functionally dependent on "Department ID" instead of being dependent on either "Student ID" or "Course ID."
To normalize this table to BCNF, we need to split it into three tables: "Students," "Courses," and "Departments."
The "Students" table will have the following columns:
The "Courses" table will have the following columns:
The "Departments" table will have the following columns:
By doing this, we have eliminated data redundancy and ensured that each table contains data that is relevant and fully dependent on the candidate keys. This allows for better data integrity and easier management of the database.
Let's discuss the example of Boyce-Codd Normal Form (BCNF). Assume there is a company where employees work in more than one department.
EMPLOYEE table:
EMP_ID | EMP_COUNTRY | EMP_DEPT | DEPT_TYPE | EMP_DEPT_NO |
---|---|---|---|---|
264 | India | Designing | D394 | 283 |
264 | India | Testing | D394 | 300 |
364 | UK | Stores | D283 | 232 |
364 | UK | Developing | D283 | 549 |
In the above table Functional dependencies are as follows:
EMP_ID → EMP_COUNTRY
EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}
Candidate key: {EMP-ID, EMP-DEPT}
The table is not in BCNF because neither EMP_DEPT nor EMP_ID alone are keys. To convert the given table into BCNF, we decompose it into three tables:
EMP_COUNTRY table:
EMP_ID | EMP_COUNTRY |
---|---|
264 | India |
264 | India |
EMP_DEPT table:
EMP_DEPT | DEPT_TYPE | EMP_DEPT_NO |
---|---|---|
Designing | D394 | 283 |
Testing | D394 | 300 |
Stores | D283 | 232 |
Developing | D283 | 549 |
EMP_DEPT_MAPPING table:
EMP_ID | EMP_DEPT_NO |
---|---|
264 | 283 |
264 | 300 |
364 | 232 |
364 | 549 |
Functional dependencies:
EMP_ID → EMP_COUNTRY
EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}
Candidate keys:
Now, this is in BCNF because left side part of both the functional dependencies is a key.