# 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.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://codexpress.gitbook.io/welcome-to-datagenesis/normalisation/boyce-codd-normal-form-bcnf.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.