FrançaisGet started

Boyce-Codd normal form

Boyce-Codd normal form: every determinant is a superkey; the case of a 3NF relation that does not meet it, the decomposition, and its price: a dependency that no table enforces any more.

  • 20 cards
  • 5 topics
  • Free
Study this deck

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

  1. Concepts
  2. Questions
  3. Sort and link
  4. Scenarios
  5. Understand

A few cards from the deck

  • Boyce-Codd normal form (BCNF): what is it?

    Show the answer

    The property of a relation in which every non-trivial dependency X → Y has a superkey as its left-hand side X.

    It is stricter than 3NF: every BCNF relation is in 3NF, the converse is false. It removes all redundancy caused by functional dependencies.

  • Cours(etudiant, matiere, enseignant): a teacher teaches a single subject, so enseignant → matiere. The candidate keys are (etudiant, matiere) and (etudiant, enseignant). Which form is it in?

    • 3NF, but not BCNF
    • BCNF, since every dependency starts from a key
    • 2NF only, since matiere depends on enseignant
    • It is not in 2NF, since the key is composite
    Show the answer

    3NF, but not BCNF

    enseignant is not a superkey: BCNF is broken. But matiere belongs to a candidate key, which saves 3NF. Every attribute is in some key: no partial or transitive dependency in the strict sense.

  • A relation is in BCNF when, for every non-trivial dependency X → Y, X is a ….

    Show the answer

    A relation is in BCNF when, for every non-trivial dependency X → Y, X is a superkey.

    3NF adds a loophole: it also accepts Y when it is an attribute belonging to a candidate key. BCNF removes it.

  • Determinant: what is it?

    Show the answer

    The left-hand side of a functional dependency: the set of attributes whose value fixes that of the attributes on the right.

    BCNF boils down to this: a determinant that does not identify the whole row is a sign of redundancy.

  • After a BCNF decomposition, the database accepts without error these two enrolments of Léa in Réseaux with two different teachers. What is the cause, and what to choose?

    ```sql
    -- R1(enseignant, matiere) R2(etudiant, enseignant)
    INSERT INTO r1 VALUES ('Aubry', 'Réseaux'), ('Moreau', 'Réseaux');
    INSERT INTO r2 VALUES ('Léa', 'Aubry'), ('Léa', 'Moreau');
    `

    • No table carries (etudiant, matiere) → enseignant any more; check it with a trigger, or stay in 3NF
    • The decomposition is lossy: joining R1 and R2 invents Léa's second enrolment
    • The foreign keys between R1 and R2 are missing; declaring them is enough to reject the row
    • This behaviour is normal in BCNF: a student may have several teachers per subject
    Show the answer

    No table carries (etudiant, matiere) → enseignant any more; check it with a trigger, or stay in 3NF

    The split is lossless, and foreign keys change nothing: each table stays valid. It is the dependency spanning two tables that is no longer guarded, although the domain rule has not changed. That is BCNF's price, weighed against the redundancy it avoids.

Study this deck