Boyce-Codd Normal Form (3.5NF)
This normal form is also referred as 3.5 normal forms. This normal form
- Meets all the requirement of 3NF
- Any table is said to be in BCNF, if its candidate keys do not have any partial dependency on the other attributes. i.e.; in any table with (x, y, z) columns, if (x, y)->z and z->x then it’s a violation of BCNF. If (x, y) are composite keys and (x, y)->z, then there should not be any reverse dependency, directly or partially.
In the above 3NF example, STUDENT_ID is the Primary key in STUDENT table and ZIP is the primary key in the ZIPCODE table. There is no other key column in each of the tables which determines the functional dependency. Hence it’s in BCNF form. That is, with STUDENT_ID, we can retrieve STUDENT_NAME and ZIP from STUDENT table. Similarly, with ZIP value, we can retrieve STREET and CITY from ZIPCODE table
Let us consider another example – consider each student who has taken major subjects has different advisory lecturers. Each student will have different advisory lecturers for same Subjects. There exists following relationship, which is violation of BCNF.
(STUDENT_ID, MAJOR_SUBJECT) -> ADVISORY_LECTURER
ADVISORY_LECTURER -> MAJOR_SUBJECT
i.e. Major_Subject which is a part of composite candidate key is determined non-key attribute of the same table, which is against the rule.
Below table will have all the anomalies too. If we delete any student from below table, it deletes lecturer’s information too. If we add any new lecturer/student to the database, it needs other related information also. Also, if we update subject for any student, his lecturer info also needs to be changed, else it will lead to inconsistency.
Hence we need to decompose the table so that eliminates so that it eliminates such relationship. Now in the new tables below, there are no inter-dependent composite keys (moreover, there is no composite key in both the tables). If we need to add/update/delete any lecturer, we can directly insert record into STUDENT_ADVISOR table, without affecting STUDENT_MAJOR table. If we need to insert/update/delete any subject for a student, then we can directly do it on STUDENT_MAJOR table, without affecting STUDENT_ADVISOR table. When we have both advisor as well as major subject information, then we can directly add/update both the tables. Hence we have eliminated all the anamolies in the database.
