Menu
Can someone please explain the difference between 3NF and BCNF to me? It would be great if you could also provide some examples. Thanks.
511
BCNF is a stronger form of normalization than 3NF because it eliminates the second condition for 3NF, which allowed the right side of the FD to be a prime attribute. Thus, every left side of an FD in a table must be a superkey. Every table that is BCNF is also 3NF, 2NF, and 1NF, by the previous definitions.
Following are the various types of Normal forms:A relation will be in 3NF if it is in 2NF and no transition dependency exists. BCNF. A stronger definition of 3NF is known as Boyce Codd's normal form. 4NF. A relation will be in 4NF if it is in Boyce Codd's normal form and has no multi-valued dependency.
The difference between 3NF and BCNF is subtle.
A relation is in 3NF if it is in 2NF and no non-prime attribute transitively depends on the primary key. In other words, a relation R is in 3NF if for each functional dependency X ⟶ A in R, at least one of the following conditions are met:
Given the following relation:
EMP_DEPT(firstName, employeeNumber, dateOfBirth, address, departmentNumber, departmentName)
An employee can only work in one department and each department has many employees.
The candidate key is employeeNumber.
Consider the following functional dependencies:
Given the definition above, it is possible to conclude that the relation EMP_DEPT is not in 3NF because the second functional dependency does not meet any of the 2 conditions of the 3NF:
A relation R is in BCNF if it is in 3NF and for each functional dependency X ⟶ A in R, X is a key or superkey in R. In other words, the only difference between 3NF and BCNF is that in BCNF it is not present the second condition of the 3NF. This makes BCNF stricter than 3NF as any relation that is in BCNF will be in 3NF but not necessarily every relation that is in 3NF will be in BCNF.
Given the following relation:
STUDENT_COURSE(studentNumber, socialSecurityNumber, courseNumber)
A student can assist to many courses and in a course there can be many students.
The candidate keys are:
Consider the following functional dependencies:
Given the definition above, it is possible to conclude that STUDENT_COURSE is not in BCNF as at least studentNumber is not a key or superkey in STUDENT_COURSE.
157
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
