Boyce Codd normal form (BCNF)

by anupmaurya
690 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_type sub_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

1 comment

hdTl November 30, 2020 - 9:45 am

I was recommended this blog via my cousin. I’m not positive whether this post is written by means of him as no one else know such certain about
my trouble. You’re wonderful! Thank you!

Comments are closed.