---

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 : Teacher → Subject A subject can be taught by different teachers , i.e., there are multiple teachers per subject Students takes classes in subjects and each student only take a subject once This property result in the following functional dependency: Student, Subject → Teacher I.e.: 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) // because Teacher → Subject

    ---

    ---

  • Question:

    Is this relation in the 3NF ???

    Answer:

    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          Read more on Boyce here (who developed SQL): click here Read more on Codd here (who developed the Relational Model): click here

  ---

  ---

  ---

  ---

• 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 ???

    Answer:

    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 !!!