---

Boyce-Codd Normal Form (BCNF) in DBMS

---

Boyce-Codd Normal Form (BCNF) is an advanced step in the normalization process, and it's a stronger version of the Third Normal Form (3NF). In fact, every relation in BCNF is also in 3NF, but the converse isn't necessarily true. BCNF was introduced to handle certain anomalies that 3NF does not deal with.

A relation is in BCNF if:

1. It is already in 3NF.

2. For every non-trivial functional dependency \( X \rightarrow Y \), X is a superkey. This essentially means that the only determinants in the relation are superkeys.

Here, "non-trivial" means that Y is not a subset of X, and a "superkey" is a set of attributes that functionally determines all other attributes in the relation.

Example for Boyce-Codd Normal Form (BCNF)

Consider a university scenario where professors supervise student theses in various topics. Now, let's assume each professor can only supervise one topic, but multiple professors can supervise the same topic.

Initial Table

| Student | Professor | Topic  |
|---------|-----------|--------|
| Alice   | Mr. A     | Math   |
| Bob     | Mr. B     | Math   |
| Charlie | Mr. C     | Physics|

Here:

• Each professor is associated with exactly one topic.
• The primary key is {Student, Professor}, meaning a professor can supervise multiple students, but each student has one thesis and thus one topic.
• There's a functional dependency {Professor} → {Topic} since each professor supervises only one topic.

Now, observe that {Professor} is not a superkey (because the primary key is a combination of Student and Professor), but it determines another attribute in the table (Topic). This violates the definition of BCNF.

To bring this table into BCNF, we can decompose it into two tables:

StudentSupervision Table:

| Student | Professor |
|---------|-----------|
| Alice   | Mr. A     |
| Bob     | Mr. B     |
| Charlie | Mr. C     |

ProfessorTopic Table:

| Professor | Topic  |
|-----------|--------|
| Mr. A     | Math   |
| Mr. B     | Math   |
| Mr. C     | Physics|

This decomposition eliminates the partial dependency and ensures that the only determinants are superkeys, making the structure adhere to BCNF.

In practice, BCNF is a highly normalized form, and while it can minimize redundancy, it can also increase the complexity of the database design. Designers often have to make trade-offs between achieving higher normal forms and maintaining simplicity, depending on the specific use case and requirements of the system.

---

Next Topic :Fourth Normal Form (4NF) in DBMS