SQL -Structured Query Language - Normalization - Boyce Codd normal form ( BCNF ) Tutorial
BCNF is more advanced in terms of 3 NF. It can also be called 3.5 NF.
- The table must be in 3 NF.
- Still, there is some redundancy based on functional dependency were ( A ->B), which should be handled by making A a primary key
For example – In 3 NF example (Gender -> Salutation). This means Salutation is functionally dependent on Gender.
Hence to achieve BCNF make gender a primary key in other tables.
In this, we can see that table 3 is functionally dependent on table 1.
This means we can obtain the table 3 data i.e contact_no using the user_id of table 1.
This shows that table 3 is functionally dependent on table 1.
So to achieve BCNF
For example, table 2 doesn’t have any primary key till 3NF, while table 1 and table 2 has already primary key user_id and gender respectively.
Hence we added id as a primary key in table 2.