Skip to main content
  • Pricing
Tutorials

Boyce-Codd Normal Form With a Worked Example

By Oakgen TeamPublished 1 min read

Share
Boyce-Codd Normal Form With a Worked Example

A relation is in Boyce-Codd normal form when the determinant of every nontrivial functional dependency is a superkey. To check the rule, test what each determinant identifies. A determinant can identify one attribute reliably while failing to identify a complete row.

Try a scheduling example

Suppose a relation records Student, Course, and Instructor. Assume each student takes a course with one instructor, and every instructor teaches one course. Then Student and Course together determine Instructor, while Instructor determines Course. Student and Course can identify the row. Instructor alone cannot, because multiple students can have the same instructor.

Instructor determines Course, so that dependency violates BCNF. Store Instructor and Course in one relation, then Student and Instructor in another. Instructor links the two and identifies a row in the first relation. The course name now needs to be recorded only once per instructor.

Check what the decomposition changes

A lossless join and dependency preservation are separate questions. In this example, enforcing that a given student takes a course with only one instructor may require checking the relations together. Verify both properties when you choose a design. A decomposition can remove repeated facts while making some original rules harder to enforce through individual table constraints alone.

Continue with Oakgen Agent

Open in Oakgen Agent

The prompt is filled in for you. Review it before sending.

Open in Oakgen Agent

Sources

  1. Simon Fraser University, Boyce-Codd Normal Form
  2. BCcampus, Database Design: Normalization
Share