BCNF form in DBMS
About BCNF Form in DBMS
In this article, we will learn about BCNF Form in DBMS.
Normalization is a process of breaking a table into you still optimized ones so that they are free from all problems faced due to insertion updation and deletion of data in the table
BCNF form in DBMS
In this article, we will learn about the Boyce Codd Normal Form i.e. BCNF form in DBMS.
- Normalization is a process where the table is decomposed into further tables so that newly formed tables are free from all problems of insertion, deletion, and updation of data
- BCNF: Advanced version of 3nf and stricter then 3nf
Definition of Boyce Codd Normal Form:
The table is said to be in BCNF if it satisfies the following properties
- It should be in 3nf
- For every functional dependency, A-> B, A must be a superkey i.e in any functional dependency LHS attribute must be a Superkey.
Problem Table
EMPLOYEE table:
| EID | EMP_LOCATION | EMP_DEPT | DEPT_NO | EMP_DEPT_NO |
|---|---|---|---|---|
| 66 | India | HR | D1 | 101 |
| 66 | India | Marketing | D1 | 102 |
| 73 | CANADA | Exports | D2 | 103 |
| 73 | CANADA | Finance | D2 | 104 |
- Consider a table employee when an employee can work in more than one department, these dependencies cause insertion, updation and deletion problems
- It is in First Normal Form because of no multiple attributes and It is in second normal form and third Normal Form also because it is free from all kinds of partial and transitive dependencies.
Procedure to convert a table into BCNF
In the above table Functional dependencies are as follows:
- EMP → EMP_COUNTRY
EMP_DEPT → {DEPT_NO, EMP_DEPT_NO} - Candidate key: {EID, EMP_DEPT}
- From the table, we can see that we must need a candidate key combination {EID, EMP-DEPT} because single employee working in more than two departments hence his ID is duplicated so that only employee ID cannot be served as a primary key
- Create three tables from the original employee table
- Create a table called EMP_COUNTRYwhich contains employees country and employee ID and here each record can be uniquely identified from this table.
EMP_COUNTRY table:
| EID | EMP_LOCATION |
|---|---|
| 66 | India |
| 73 | UK |
- Create another table which will consist of employee department type number and the employee Department number.
EMP_DEPT
| EMP_DEPT | DEPT_NO | EMP_DEPT_NO |
|---|---|---|
| HR | D1 | 101 |
| Marketing | D1 | 102 |
| Exports | D2 | 103 |
| Finance | D2 | 104 |
- Create one more table EMP_DEPT_MAPPING which consists of employee ID and departmentEMP_DEPT_MAPPING table:
| EMP_ID | EMP_DEPT_NO |
|---|---|
| D1 | 101 |
| D2 | 102 |
| D3 | 103 |
| D4 | 104 |
Observed functional dependencies
- EID → EMP_COUNTRY
- EMP_DEPT → {DEPT_NO, EMP_DEPT_NO}
Conclusion: The table is in BCNF because the left side of each dependency is a superkey in all the three tables
Prime Course Trailer
Related Banners
Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription
Get over 200+ course One Subscription
Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others
Login/Signup to comment
hii