122 BCNF

Informally, Boyce-Codd Normal Form, or BCNF, says that whenever a set of attributes determines some other attribute, that determinant should be a superkey.

Example which violates BCNF

The following relation records the lecturer for each student's course enrolment:

Assume the following functional dependencies hold:

• {student, course} → {lecturer} because a student's enrolment in a course has one lecturer
• {lecturer} → {course} because each lecturer teaches exactly one course

The second dependency is visible in the repeated lecturer-course pairs. It violates BCNF because lecturer is not a superkey, yet {lecturer} → {course} holds. It can still satisfy 3NF because course is part of a candidate key: {student, lecturer} also determines all attributes.

entity CourseEnrolment
{
    String student;
    String course;
    String lecturer;

    key {student, course};
    key {student, lecturer};
}

Here course is a fact about the lecturer, but it is stored on CourseEnrolment.

Writing the dependency as a constraint

If the model is left in this shape, the dependency from lecturer to course has to be imposed over the containing collection of enrolments. One way to write it is to say that there is no pair of enrolments with the same lecturer and different courses:

entity University
{
    CourseEnrolment enrolment*;

    constraints
    {
        let university = this in
            enrolment.(let e = this in
                university.enrolment.[
                    lecturer = e.lecturer
                    ∧ course ≠ e.course
                ].Σempty);
    }
}

This constraint is the Predica form of {lecturer} → {course}. It is non-local because the course taught by a lecturer is repeated on enrolment records instead of being a local fact about a lecturer.

Fixing the violation

The fix is to separate the fact about the lecturer's course from the fact that a student is enrolled with a lecturer. In relational form, this decomposes the original relation into two relations:

In Predica, the same repair is to model Lecturer explicitly:

entity Lecturer
{
    String name;
    String course;

    key {name};
}

entity CourseEnrolment
{
    String student;
    Lecturer lecturer;

    key {student, lecturer};
}

entity University
{
    Lecturer lecturer*;
    CourseEnrolment enrolment*;
}

After this repair, the cross-enrolment FD constraint is no longer needed. The course is a local attribute of Lecturer, and each enrolment points to the lecturer responsible for that enrolment.