Boyce Codd normal form (BCNF)

484 views

Boyce–Codd normal form is a normal form used in database normalization. It is a slightly stronger version of the third normal form. BCNF was developed in 1974 by Raymond F. Boyce and Edgar F. Codd to address certain types of anomalies not dealt with by 3NF as originally defined.

  • A relation will be in Boyce Codd normal form ,if has no multi-valued dependency.
  • For a dependency A → B, if for a single value of A, multiple values of B exists, then the relation will be a multi-valued dependency.

Example: Suppose there is an university wherein student opted in more than one subject . They store the data like this:

stud_idstud_nationalitystud_subjectdept_typesub_code
1001IndianECESCIENCE200
1002AmericanGEOGRAPHYENVIRONMENTAL250
1002AmericanCSESCIENCE280
1001IndianANTHROPOLOGYENVIRONMENTAL300

Functional dependencies in the table above:
stud_id -> stud_nationality
stud_subject -> {dept_type, sub_code}

Candidate key: {stud_id, stud_subject}

The table is not in BCNF as neither stud_id nor stud_subject alone are keys.

To convert the given table into BCNF, we decompose it into three tables:
stud_nationality table:

stud_idstud_nationality
1001Indian
1002American

stud_subject table:

stud_subjectstud_typesub_code
ECESCIENCE200
GEOGRAPHYENVIRONMENTAL250
CSE
SCIENCE
280

ANTHROPOLOGY
ENVIRONMENTAL300

stud_subject_mapping table:

stud_idstud_subject
1001GEOGRAPHY
1002stores
1002CSE
1002ANTHROPOLOGY

Functional dependencies:
stud_id -> stud_nationality
stud_code -> {dept_type, sub_code}

Candidate keys:
For first table: stud_id
For second table: stud_subject
For third table: {stud_id, stud_subject}

This is now in BCNF as in both the functional dependencies left side part is a key.

You may also like

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More

Enable Notifications    OK No thanks