CS457 Syllabus & Progress

### Boyce-Codd Normal Form (BCNF)

• Introduction to BCNF:

• Consider the following Teacher-Subject-Student database:

Teacher Subject Student
Mr. Abbot Math John Smith
Mr. Abbot Math Jane Doe
Mr. Abbot Math Jake Brown
Mr. Costello Math Peter Pan
Mr. Costello Math Capt. Hook
Mr. Johnson Chem Peter Pan
Mr. Johnson Chem John Smith

Data description:

 Each teacher teaches one subject A subject can be taught by different teachers , i.e., there are multiple teachers per subject Students takes classes in subjects A (student, subject) pair identifies a teacher uniquely

• The resulting relation and functional dependencies are:

 ``` TJS(Teacher, Subject, Student) Functional Dependencies: Teacher → Subject (each teacher teaches 1 subject) Student, Subject → Teacher ( (student,subject) identifies teacher) ```

The keys of this relation are:

 ``` Keys: (Student, Subject) (Student, Teacher) ```

• Question:

 Is this relation in the 3NF ???

• Check the functional dependency Student, Subject Teacher:

• Is Student, Subject a superkey ?

 Yes ! ===> no problem, move on....

• Check the functional dependency Teacher Subject:

• Is Teacher a superkey ?

 No ! ===> continue check....

• Is Subject a key attribute (= part of a key) ?

 Yes ! ===> no problem !!!

Conclusion:

 This relation is in the 3NF !!!

• Consider a sample content of the TJS relation:

Teacher Subject Student
Mr. Abbot Math John Smith
Mr. Abbot Math Jane Doe
Mr. Abbot Math Jake Brown
Mr. Costello Math Peter Pan
Mr. Costello Math Capt. Hook
Mr. Johnson Chem Peter Pan
Mr. Johnson Chem John Smith

Observation:

 There are duplication of information stored in the relation !!!

• Historical background:

• Boyce and Codd discovered this problem after Codd published the classic 1972 paper.

 This "problem" can arise when there are 2 or more composite keys in a relation.

• Boyce and Codd proposed a new 3NF which is known as the:

 Boyce-Codd Normal Form or BCNF

• Definition: BCNF

• BCNF defintion:

• A relation R is in Boyce-Codd Normal Form (BCND) iff

• For every functional dependency X → A in R:

 X   must be a super key of R

• The Teacher-Subject-Student relation revisited

• Recall:

 ``` TJS(Teacher, Subject, Student) Keys: (Student, Subject) (Student, Teacher) Functional Dependencies: Student, Subject → Teacher Teacher → Subject ```

• Question:

 Is this relation in the BCNF ???

• Check the functional dependency Student, Subject Teacher:

• Is Student, Subject a superkey ?

 Yes ! ===> no problem, move on....

• Check the functional dependency Teacher Subject:

• Is Teacher a superkey ?

 No ! ===> violation !!!

Conclusion:

 The relation is not in the BCNF !!!

• Decompose:

 ``` Violating FD: Teacher → Subject Compute Teacher+: Teacher+ = Teacher = Teacher, Subject Decompose: TSJ( Teacher, Student, Subject ) / \ / \ Violating FD: Teacher → Subject / \ T1(Teacher, Subject) R2(Teacher, Student) ```

• Original relation:

Teacher Subject Student
Mr. Abbot Math John Smith
Mr. Abbot Math Jane Doe
Mr. Abbot Math Jake Brown
Mr. Costello Math Peter Pan
Mr. Costello Math Capt. Hook
Mr. Johnson Chem Peter Pan
Mr. Johnson Chem John Smith

Decomposed relations:

Teacher Subject
Mr. Abbot Math
Mr. Costello Math
Mr. Johnson Chem

Teacher Student
Mr. Abbot John Smith
Mr. Abbot Jane Doe
Mr. Abbot Jake Brown
Mr. Costello Peter Pan
Mr. Costello Capt. Hook
Mr. Johnson Peter Pan
Mr. Johnson John Smith

No duplication of information !!!