Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
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.
47K+ Views
