Boyce-codd normal form (bcnf), Database Management System

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







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
Related Questions
Prepare Heart Failure Registry database How are you today? Please I would like you to design a Heart Failure registry where doctors will log in to enter information about their

For this application, we will consider IBM DB2 catalog data (i.e., metadata) for IBM's enterprise-class servers running the z/OS (formerly OS/390 or MVS) operating system.  One of

Increases efficiency : Data is stored close to most often used site, therefore retrieval would be faster. Also, data that is not required by local applications is not stored, there

What is ODBC ? ODBC - Open DataBase Connectivity which is also denoted by ODBC enable the integration of SQL along with a common-purpose programming language. ODBC expose data

Explain Interfaces for parametric Users? Interfaces for Parametric Users - Parametric users, such as bank tellers, frequent have a little set of operations in which they must p

Explain the following functions of Oracle with suitable examples: (i) To_Char( ) (ii) Count( ) (iii) Trim( ) (iv) Length( ) Ans: (i) To_char( ): The TO_CHAR function conver

Define Query by Example QBE is a query language relies upon domain calculus and has two dimensional syntax. The queries are written in the horizontal and vertical dimensions of

Define DBA database administrator? DBA : A database administrator (DBA) is a person who is responsible for the environmental aspects of a database. In common, these involves:

What is Distributed system ? Distributed System - A distributed system consists of a number of processing parts in which are interconnected through a computer network and in

Define the terms  1) physical schema 2) logical schema.  Physical schema:  The physical schema explains the database design at the physical level, which  is  the  lowest  lev