Boyce-Codd Normal Form (BCNF)

The relation NEWSTUDENT (Enrolmentno, Sno, Sname, Cname, Cno,) has every attributes participating in candidate keys as all the attributes are assumed to be unique. We thus had the following candidate keys.

(Enrolmentno, Cno)

(Enrolmentno, Cname)

(Sname, Cno)

(Sname, Cname)

As the relation has no non-key attributes, the relation is in 2NF and as well in 3NF. Though, the relation suffers from the anomalies (please check it yourself by making the relational instance of the NEWSTUDENT relation).

The complexity in this relation is being caused by dependence within the candidate keys.

Definition: A relation is in BCNF, if it is in 3NF and if each determinant is a candidate key.

• A determinant is the left side of an FD
• Most relations that are in 3NF are also in BCNF. A 3NF relation is not in

BCNF if all the following conditions apply.

(a)     The candidate keys in the relation are composite keys.

(b)     There is more than one overlapping candidate keys in the relation and a number of attributes in the keys are overlapping and some are not overlapping.

(c)      There is a FD from the non-overlapping attribute(s) of single candidate key to non-overlapping attribute(s) of other candidate key.

Let us recall the NEWSTUDENT relation:

NEWSTUDENT (Enrolmentno, Sname, Sno,  Cno, Cname) Set of FDs:

Enrolmentno     →           Sname              (1)

Sname  →                       Enrolmentno     (2)

Cno       →                      Cname              (3)

Cname  →                      Cno                   (4)

The relation even though in 3NF, but is not in BCNF and can be decomposed on any one of the FDs in (1) & (2); and any one of the FDs in (3) & (4) as:

STUD1 (Enrolmentno, Sname) COUR1 (Cno, Cname)

The third relation that will join the two relation will be:

ST_CO(Enrolmentno, Cno)

Since this is a slightly complex form, let us give one more example, for BCNF. Consider for example, the relation:

ENROL(Enrolmentno, Sname, Cno, Cname, Dateenrolled)

Let us suppose that the relation has the following candidate keys:

(Enrolmentno, Cno)

(Enrolmentno, Cname)

(Sname, Cno)

(Sname, Cname)

(We have supposed Cname and Sname are unique identifiers).

The relation has the following set of dependencies:

Enrolmentno     →    Sname

Sname             →     Enrolmentno

Cno                  →     Cname

Cname             →     Cno

Enrolmentno, Cno     →  Dateenrolled

The relation is in 3NF but not in BCNF as there are dependencies. The relation suffers from all anomalies. Please draw the relational instance and checks these troubles. The BCNF decomposition of the relation would be:

STUD1 (Enrolment no, Sname)

COU1 (Cno, Cname)

ENROL1 (Enrolmentno, Cno, Dateenrolled)

We now have a relation that only has information about students, another only about subjects and the third only about relationship enrolls.

Posted Date: 3/7/2013 2:08:57 AM | Location : United States

Ask an Expert

Related Discussions:- Boyce-codd normal form (bcnf), Assignment Help, Ask Question on Boyce-codd normal form (bcnf), Get Answer, Expert's Help, Boyce-codd normal form (bcnf) Discussions

Write discussion on Boyce-codd normal form (bcnf)
Your posts are moderated

Write your message here..

Related Questions

What is the initial step for object-oriented designing, What is the initial... What is the initial step for object-oriented designing The initial step for object-oriented designing is object modeling. Before we go into deep about object modeling first of

What is a hashing function, What is a hashing function?  What are the prope... What is a hashing function?  What are the properties of a good hashing function?  Describe the folding technique for hashing functions.      Ans:  Hashing function is a method

Differance between join and outer join, Differentiate between join and oute... Differentiate between join and outer join? Outer joins return all rows from at least one of the tables or views mentioned within the FROM clause, as long as those rows get toge

Hospital managment sysytem, sy bcs project of hospital managment system sy bcs project of hospital managment system

Define single value and multivalued attribute, Define Single Value and Mult... Define Single Value and Multivalued attribute Single value attribute comprise a single atomic value for an entity and multivalued attribute may have much more than one value f

One to one relationship, how map binary relation one to one how map binary relation one to one

The internal or physical level in dbms, The Internal or Physical Level ... The Internal or Physical Level The collection of files permanently stored on secondary storage devices is called as the physical database. The internal or physical level is t

What is the candidate key for a table with columns, For problems 1-4, give ... For problems 1-4, give all candidate keys for the tables with FDs specified.  Composite candidate keys must be put in parentheses, and candidate keys must be separated with commas

Draw enhanced er-diagram for hierarchy of a student database, Draw an EER d... Draw an EER diagram that captures the "is-a" hierarchy of students in another simple database. The database captures both the "basic" information (say, student ID and student name)

OLAP CUBES, 1) Define a job scheduling strategy that will meet business req... 1) Define a job scheduling strategy that will meet business requirement of reporting availability by 6am CST for the following cubes? Show the job scheduling dependencies in a pict