Boyce-Codd Normal Form (BCNF)

1. Boyce-Codd Normal Form (BCNF)

2. 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 3NF and the BCNF are equivalent. • BCNF is a special case of 3NF.

3. 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 3NF. 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 ()

4. 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 emp1 & emp2 in BCNF

5. emp1 emp2 Emp1 has one determinant (empid,deptid),emp2 has one determinant (emp_id) so, emp1 and emp2 are in BCNF.

6. 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 R1 = { branch_name, branch_city, branch_assets) R2 = { branch_name, Custome_name, Loan_number, loan_amount) R3 = { branch_name, Loan_number, loan_amount) R4 = {Custome_name, Loan_number, loan_amount) Final decomposition : R1, R3, R4

7. Sample Data for a BCNF Conversion

8. Decomposition into BCNF