What is difference between 3nf and bcnf?

3 NF is regarding transitive dependency. i.e. if in R(A,B,C,D,E) and A->C and C->E then the relation is not in 3NF. u need to remove the transitive dependency by decomposing R. and if R(A,B,C,D,E) and A,B->D,E and A,C->D and B->C then R is not in BCNF. BCNF is to be checked when theres is two composite key possible where one attribute(here A ) is common and other two (here C & D) heve dependency. read less

A relation schema R is in 3NF if it is in 2NF and for every FD X –> A either of the following is true X is a Super-key of R. A is a prime attribute of R. In other words, if every non prime attribute is non-transitively dependent on primary key. A relation schema R is in BCNF if it is in 3NF and satisfies an additional constraint that for every FD X –> A, X must be a candidate key. read less

BCNF: For every functional dependency X->Y in a set F of functional dependencies over relation R, either: Y is a subset of X or, X is a superkey of R 3NF: For every functional dependency X->Y in a set F of functional dependencies over relation R, either: Y is a subset of X or, X is a superkey of R, or Y is a subset of K for some key K of R N.b., no subset of a key is a key read less

Please refer wiki for exact definitions. Here is an explaination to your query 3NF Definition 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: X is a key or superkey in R A is a prime attribute in R Example 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: employeeNumber ? firstName, dateOfBirth, address, departmentNumber departmentNumber ? departmentName 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: departmentNumber is not a key or superkey in EMP_DEPT departmentName is not a prime attribute in EMP_DEPT BCNF Definition 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. Example 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: socialSecurityNumber, courseNumber studentNumber, courseNumber Consider the following functional dependencies: studentNumber ? socialSecurityNumber socialSecurityNumber ? studentNumber Given the definitioin 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.3NF Definition 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: X is a key or superkey in R A is a prime attribute in R Example 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: employeeNumber ? firstName, dateOfBirth, address, departmentNumber departmentNumber ? departmentName 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: departmentNumber is not a key or superkey in EMP_DEPT departmentName is not a prime attribute in EMP_DEPT BCNF Definition 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. Example 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: socialSecurityNumber, courseNumber studentNumber, courseNumber Consider the following functional dependencies: studentNumber ? socialSecurityNumber socialSecurityNumber ? studentNumber Given the definitioin 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. read less

BCNF: For every functional dependency X->Y in a set F of functional dependencies over relation R, either: Y is a subset of X or, X is a superkey of R 3NF: For every functional dependency X->Y in a set F of functional dependencies over relation R, either: Y is a subset of X or, X is a superkey of R, or Y is a subset of K for some key K of R N.b., no subset of a key is a key read less

Assume the following things; A and B are set of attributes. A is non-key attribute and B is the primary key. FD – { A ? B. } The above Functional Dependency is about the dependency of primary key on a non-key attribute. This functional dependency is permitted in Third Normal Form (3NF). 3NF tries to identify and eliminate Non-key ? Non-key dependency. This Functional Dependency is not permitted in Boyce-Codd Normal Form (BCNF), because BCNF expects the determiner should be a candidate key. In our example, A is not a candidate key. This is why BCNF is termed as strict 3NF. 3NF is always achievable. BCNF is not. BCNF may result in Loss-less Join Decomposition and lead to loss of Dependency Preserving Decompositions. Properties 3NF BCNF Achievability Always achievable Not always achievable Quality of the tables Less More Non-key Determinants Can have non-key attributes as determinants Cannot have. Proposed by Edgar F. Codd Raymond F.Boyce and Edgar F.Codd jointly proposed Decomposition Loss-less join decomposition can be achieved Sometimes Loss-less join decomposition cannot be achieved Table 1 – 3NF – BCNF Comparisons read less