Computer Sciene of Udayana State University

April 26, 2009

Boyce–Codd Normal Form (BCNF)

Filed under: Computer Science — ignaga @ 1:51 AM

· Based on functional dependencies that take into account all candidate keys in a relation, however BCNF also has additional constraints compared with general definition of 3NF.

· BCNF – A relation is in BCNF if and only if every determinant is a candidate key.

· Difference between 3NF and BCNF is that for a functional dependency A –> B, 3NF allows this dependency in a relation if B is a primary-key attribute and A is not a candidate key.

· Whereas, BCNF insists that for this dependency to remain in a relation, A must be a candidate key.

· Every relation in BCNF is also in 3NF. However, relation in 3NF may not be in BCNF.

teach

  • Two FDs exist in the relation TEACH:

fd1: { student, course} à instructor

fd2: instructor à course

  • {student, course} is a candidate key for this relation and that the dependencies shown follow the pattern in Figure 10.12 (b).

So this relation is in 3NF but not in BCNF

  • A relation NOT in BCNF should be decomposed so as to meet this property, while possibly forgoing the preservation of all functional dependencies in the decomposed relations.
  • Main steps of algorithm 11.3:

Find the FD XàY in Q that violates BCNF

Replace Q by two relations with schemas (Q-Y) and (X,Y).

TEACH relation is decomposed into:

  • T1(instructor, course)
  • T2(instructor, student)

FD fd1 is lost after decomposition.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: