BoyceCodd Normal Form BCNF Database Normalization BoyceCodd Normal

Boyce-Codd Normal Form (BCNF)

Database Normalization • Boyce-Codd Normal Form (BCNF) – A relation is in Boyce-Codd normal form (BCNF) if every determinant in the table is a candidate key. (A determinant is any attribute whose value determines other values with a row. ) – If a table contains only one candidate key, the 3 NF and the BCNF are equivalent. – BCNF is a special case of 3 NF.

Boyce-Codd Normal Form • A relation schema R is in BCNF if whenever a non trivial functional dependency X A holds in R then X is a super key of R. • BCNF is more rigorous from of 3 NF. It deals with relational tables which has multiple candidate keys and composite candidate keys. BCNF is based on the concept of determinant. A determinant refers to the attribute or group of attributes on the left hand side of the arrow of the functional dependency ( )

Example • Unnormalized employee table. • The employee table has 3 determinates : emp_id, deptid, qualification. But (emp_id, dept_id) is a candidate key. So this relation is not in BCNF. For a relation to be in BCNF each determinant must be a candidate key. • Normalized employee table – employee table in BCNF. • The employee table is split into emp 1 & emp 2 in BCNF

emp 1 emp 2 Emp 1 has one determinant (empid, deptid), emp 2 has one determinant (emp_id) so, emp 1 and emp 2 are in BCNF.

Example R = { branch_name, branch_city, branch_assets, Custome_name, Loan_number, loan_amount) F = { branch_name branch_assets, branch_city. Loan_number loan_amount, branch_name} Key = {loan_number, customer_name} Decomposed to R 1 = { branch_name, branch_city, branch_assets) R 2 = { branch_name, Custome_name, Loan_number, loan_amount) R 3 = { branch_name, Loan_number, loan_amount) R 4 = {Custome_name, Loan_number, loan_amount) Final decomposition : R 1, R 3, R 4

Sample Data for a BCNF Conversion

Decomposition into BCNF