- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Explain BCNF with an example in DBMS
BCNF (Boyce Codd Normal Form) is the advanced version of 3NF. A table is in BCNF if every functional dependency X->Y, X is the super key of the table. For BCNF, the table should be in 3NF, and for every FD. LHS is super key.
Example
Consider a relation R with attributes (student, subject, teacher).
Student | Teacher | Subject |
---|---|---|
Jhansi | P.Naresh | Database |
jhansi | K.Das | C |
subbu | P.Naresh | Database |
subbu | R.Prasad | C |
F: { (student, Teacher) -> subject (student, subject) -> Teacher Teacher -> subject}
Candidate keys are (student, teacher) and (student, subject).
The above relation is in 3NF [since there is no transitive dependency]. A relation R is in BCNF if for every non-trivial FD X->Y, X must be a key.
The above relation is not in BCNF, because in the FD (teacher->subject), teacher is not a key. This relation suffers with anomalies −
For example, if we try to delete the student Subbu, we will lose the information that R. Prasad teaches C. These difficulties are caused by the fact the teacher is determinant but not a candidate key.
Decomposition for BCNF
Teacher-> subject violates BCNF [since teacher is not a candidate key].
If X->Y violates BCNF then divide R into R1(X, Y) and R2(R-Y).
So R is divided into two relations R1(Teacher, subject) and R2(student, Teacher).
R1
Teacher | Subject |
---|---|
P.Naresh | database |
K.DAS | C |
R.Prasad | C |
R2
Student | Teacher |
---|---|
Jhansi | P.Naresh |
Jhansi | K.Das |
Subbu | P.Naresh |
Subbu | R.Prasad |
All the anomalies which were present in R, now removed in the above two relations.
Note
BCNF decomposition does not always satisfy dependency preserving property. After BCNF decomposition if dependency is not preserved then we have to decide whether we want to remain in BCNF or rollback to 3NF. This process of rollback is called denormalization.
- Related Articles
- Explain 3NF with an example in DBMS
- Explain about 2NF with an example in DBMS
- Explain serial execution or transaction with an example(DBMS)
- Explain the concept of key attributes with an example in DBMS?
- Explain join operations with the help of an example in DBMS
- Explain the concept of primary key with an example (DBMS)?
- Explain trial balance with an example
- Explain asynchronous functions in JavaScript with an example
- Explain hard reset with an example in Git
- Explain mixed reset with an example in Git
- Explain soft reset with an example in Git
- Explain 5NF with examples in DBMS
- Is RowSet Scrollable? Explain with an example?
- What is Symbiosis? Explain with an example.
- Explain simultaneous linear equations with an example.
