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

Assignment Help:

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.


Related Discussions:- Boyce-codd normal form (bcnf)

List the drawbacks of relational database system, List the drawbacks of rel...

List the drawbacks of relational database system A) Repetition of data B) Inability to s certain information.

Database problem in wordpress, Project Description: I am having a random...

Project Description: I am having a random problem with my Wordpress site. If I click on "Publish" and "Add new", it takes me to a 404 page not found of /wp-admin/post.php (th

Design a large scale system, Question: (a) Why is it important to know...

Question: (a) Why is it important to know the architecture rather than to start requirement capture when designing a large scale system? (b) Who are the different people in

Relationship between two tables-strategic decision making, In a job intervi...

In a job interview with a branch manager of a bank that operates in multiple Canadian provinces and the Yukon, you learn that all of the information is captured in real time in a s

Implementation of integrity rules, Question 1 Describe the following- ...

Question 1 Describe the following- Clustering Indexing Question 2 Explain the following with suitable real time examples- Implementation of Integrity rules

Query processor in database management system, Query Processor The qu...

Query Processor The query language processor is responsible for taking query language statements or shifting them from the English-like syntax of the query language to a form

Why as clause is used in sql, Why 'AS' clause is used in SQL ? 'As' cla...

Why 'AS' clause is used in SQL ? 'As' clause is used for Rename operation. ‘AS' clause is used in SQL for rename operation. (e.g., SELECT ENO AS EMPLOYEE_NO FROM EMP)

Sorting field groups we cannot use more than one key field, While sorting f...

While sorting field groups we cannot use more than one key field False.

Employee elation with information, Consider a database application that dea...

Consider a database application that deals with an Employee relation with information such as SIN, name, data of birth (DOB), and DID (the ID of the department at which the employe

Need point of sale software for pizza restaurant, Project Description: N...

Project Description: Need Point of Sale software for pizza restaurant. Delivery and take out service, client's database, inventory control, order history, last order, order from

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd