> For the complete documentation index, see [llms.txt](https://codexpress.gitbook.io/welcome-to-datagenesis/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://codexpress.gitbook.io/welcome-to-datagenesis/normalisation/boyce-codd-normal-form-bcnf.md).

# Boyce-Codd Normal Form (BCNF)

Boyce-Codd Normal Form (BCNF) is a higher level of database normalization than the Third Normal Form (3NF). BCNF ensures that a relational database table is free from certain types of anomalies and redundancy by eliminating partial and transitive functional dependencies. A table is in BCNF if, for every non-trivial functional dependency X -> Y, X is a superkey.

To define BCNF more formally:

1. A table must be in 1NF, which means it should have atomic values in each cell.
2. A table must be in 2NF, which means it should not have partial dependencies.
3. A table must be in 3NF, which means it should not have transitive dependencies.
4. For any non-trivial functional dependency X -> Y in the table, X must be a superkey.

Here's an example to illustrate BCNF:

Consider a table called `Courses` with the following attributes:

| CourseID | Instructor  | InstructorOffice | Department |
| -------- | ----------- | ---------------- | ---------- |
| 101      | Dr. Smith   | Office A         | Math       |
| 102      | Dr. Johnson | Office B         | Physics    |
| 103      | Dr. White   | Office C         | Chemistry  |

In this table:

* `CourseID` is the primary key.
* `Instructor` is functionally dependent on `CourseID`.
* `InstructorOffice` is functionally dependent on `Instructor`.
* `Department` is functionally dependent on `CourseID`.

Now, let's analyze whether this table is in BCNF.

1. It is in 1NF because it contains only atomic values.
2. It is in 2NF because it doesn't have partial dependencies.
3. It is in 3NF because it doesn't have transitive dependencies.

However, it is not in BCNF because of the functional dependency `Instructor -> InstructorOffice`. In BCNF, every non-trivial functional dependency X -> Y should have X as a superkey. In this case, `Instructor` is not a superkey because two courses can have the same instructor. Therefore, we need to decompose the table into two tables to achieve BCNF:

**Table: Courses**

| CourseID | Instructor  | Department |
| -------- | ----------- | ---------- |
| 101      | Dr. Smith   | Math       |
| 102      | Dr. Johnson | Physics    |
| 103      | Dr. White   | Chemistry  |

**Table: Instructors**

| Instructor  | InstructorOffice |
| ----------- | ---------------- |
| Dr. Smith   | Office A         |
| Dr. Johnson | Office B         |
| Dr. White   | Office C         |

Now, each table is in BCNF, and the functional dependency `Instructor -> InstructorOffice` holds because `Instructor` is the primary key in the `Instructors` table. This decomposition ensures that BCNF is satisfied and eliminates redundancy and potential anomalies.