6.25- BCNF Boyce Codd normal Form in dbms with example| Functional Dependency And Normalization

6.25- BCNF Boyce Codd normal Form in dbms | Functional Dependency And Normalization | dbms course

PLAYLIST:
DBMS - Data Base Management System Tutorials In Hindi | GATE- NET- DBMS Lectures in Hindi
   • DBMS Complete Syllabus- All University exa...  

   • DBMS- Relational Database Design & Functio...  
Relational Database Design- Functional Dependencies And Normalization


dbms tutorials,
database management system tutorial,
database tutorials,
relational database design,
dbms lectures,
normalization in dbms,

what is normalization in dbms,
dbms gate lectures in hindi,

dbms gate lectures,
dbms gate lectures in hindi,
dbms ugc net lectures,

#gatedbmslecturesinhindi
#ugcnetdbmslecturesinhindi
#gatedbmslectures
#ugcnetdbmslectures
#dbmsLectures
#dbmsTutorials
#NormalizationInDbms

Boyce Codd Normal Form (BCNF)
BCNF is a strict format of 3NF. A relation is in BCNF if and only if all determinants
are candidate keys. BCNF deals with multiple candidate keys.
Relations in 3NF also contains anomalies. Consider the relationStudent relation.
Assumptions:
— Student can have more than 1 subject.
— A Teacher can teach only 1 subject.
— A subject can be taught by more than 1 teacher
Functional Dependency and Normalisation 223
There are two candidate keys (RollNo., Subject) and (RollNo., Teacher)
Relation Student is in 3NF but still contain anomalies.
1. Deletion anomaly : If you delete student whose RollNo. is 7. You will also loose
information that Teacher T4 is teaching the subject VB.
2. Insertion anomaly : If you want to add a new Subject VC++, you cannot do that
until a student chooses subject VC++ and a teacher teaches subject VC++.
3. Updation anomaly : Suppose you want to change Teacher for Subject C. You have
to search all the students having subject C and update each record individually
otherwise it causes inconsistency.
In relation Student, candidate key is overloaded. You can find Teacher by RollNo. and
Subject. You can also find Subject by RollNo. and Teacher. Here RollNo. is overloaded. You
can also find Subject by Teacher.
RollNo., Subject
RollNo., Teacher
Determinants Teacher
Teacher
Subject
Subject
FIGURE 6.16. Determinants in relation student.
The solution of this problem is to divide relation Student in two relations Stu-Teac and
Teac-Sub Stu-Teac Teac-Sub
RollNo. Teacher Teacher Subject
1 T1 T1 C
2 T2 T2 C++
3 T1 T3 Java
4 T3 T4 VB
5 T3 T5 Oracle
1 T5 T6 Oracle
6 T5 Teac-Sub
3 T2 Candidate Key (Teacher)
7 T4
8 T6
Stu - Teac (RollNo., Teacher)
Candidate Key (RollNo., Teacher)
. Relations in BCNF.
In this solution all determinants are candidate keys.
6.10.2.4.1 Decomposition Algorithm for BCNF with Lossless Join
It is always possible to find a decomposition of a relation that is Boyce-Codd Normal Form
224 Introduction to Database Management System
and that has the lossless join property. The process involves finding each violation of BCNF and removing it by decomposing the relation containing it into two relations. The process is repeated until all such violations are removed. The algorithm is
Given a universal relation R and a set of functional dependencies on the attributes of R:
1. D ← R;
2. while there is some relation schema S in D that is not already BCNF
{
a. Find a functional dependency X→Y in S that violates BCNF
b. Replace S by two relation schemas (S–Y) and (X,Y)

6.21- Lossless Join Property Of Normalization | Lossless Decomposition | Normalization | dbms    • 6.21- Lossless Join Property Of Normalizat...  

6.22- Conditions to Check Lossless Join Property in dbms | Functional Dependency & Normalization    • 6.22- Conditions to Check Lossless Join Pr...  

6.23- Numerical On Lossless Join Property In DBMS | Functional Dependency And Normalization    • 6.23- Numerical On Lossless Join Property ...  

6.24- Dependency Preservation Property | Functional Dependency And Normalization | Normalization    • 6.24- Dependency Preservation Property | F...  

6.25- BCNF Boyce Codd normal Form in dbms | Functional Dependency And Normalization | dbms course    • 6.25- BCNF Boyce Codd normal Form in dbms ...   Notes Link:
http://www.tutorialsspace.com/Downloa...