What is difference between 3nf and bcnf?

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 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

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

Third Normal form (3NF) A table design is said to be in 3NF if both the following conditions hold: Table must be in 2NF Transitive functional dependency of non-prime attribute on any super key should be removed. An attribute that is not part of any candidate key is known as non-prime attribute. In other words 3NF can be explained like this: A table is in 3NF if it is in 2NF and for each functional dependency X-> Y at least one of the following conditions hold: X is a super key of table Y is a prime attribute of table An attribute that is a part of one of the candidate keys is known as prime attribute. Example: Suppose a company wants to store the complete address of each employee, they create a table named employee_details that looks like this: emp_id emp_name emp_zip emp_state emp_city emp_district 1001 John 282005 UP Agra Dayal Bagh 1002 Ajeet 222008 TN Chennai M-City 1006 Lora 282007 TN Chennai Urrapakkam 1101 Lilly 292008 UK Pauri Bhagwan 1201 Steve 222999 MP Gwalior Ratan Super keys: {emp_id}, {emp_id, emp_name}, {emp_id, emp_name, emp_zip}…so on Candidate Keys: {emp_id} Non-prime attributes: all attributes except emp_id are non-prime as they are not part of any candidate keys. Here, emp_state, emp_city & emp_district dependent on emp_zip. And, emp_zip is dependent on emp_id that makes non-prime attributes (emp_state, emp_city & emp_district) transitively dependent on super key (emp_id). This violates the rule of 3NF. To make this table complies with 3NF we have to break the table into two tables to remove the transitive dependency: employee table: emp_id emp_name emp_zip 1001 John 282005 1002 Ajeet 222008 1006 Lora 282007 1101 Lilly 292008 1201 Steve 222999 employee_zip table: emp_zip emp_state emp_city emp_district 282005 UP Agra Dayal Bagh 222008 TN Chennai M-City 282007 TN Chennai Urrapakkam 292008 UK Pauri Bhagwan 222999 MP Gwalior Ratan Boyce Codd normal form (BCNF) It is an advance version of 3NF that’s why it is also referred as 3.5NF. BCNF is stricter than 3NF. A table complies with BCNF if it is in 3NF and for every functional dependency X->Y, X should be the super key of the table. Example: Suppose there is a company wherein employees work in more than one department. They store the data like this: emp_id emp_nationality emp_dept dept_type dept_no_of_emp 1001 Austrian Production and planning D001 200 1001 Austrian stores D001 250 1002 American design and technical support D134 100 1002 American Purchasing department D134 600 Functional dependencies in the table above: emp_id -> emp_nationality emp_dept -> {dept_type, dept_no_of_emp} Candidate key: {emp_id, emp_dept} The table is not in BCNF as neither emp_id nor emp_dept alone are keys. To make the table comply with BCNF we can break the table in three tables like this: emp_nationality table: emp_id emp_nationality 1001 Austrian 1002 American emp_dept table: emp_dept dept_type dept_no_of_emp Production and planning D001 200 stores D001 250 design and technical support D134 100 Purchasing department D134 600 emp_dept_mapping table: emp_id emp_dept 1001 Production and planning 1001 stores 1002 design and technical support 1002 Purchasing department Functional dependencies: emp_id -> emp_nationality emp_dept -> {dept_type, dept_no_of_emp} Candidate keys: For first table: emp_id For second table: emp_dept For third table: {emp_id, emp_dept} This is now in BCNF as in both the functional dependencies left side part is a key. read less

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

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