Forums » .NET » SQL Server »

3NF and BCNF


Posted Date: 23 Jul 2008      Posted By:: juhee     Member Level: Gold    Member Rank: 0     Points: 1   Responses: 6



hi
plz explain main difference b/w 3NF and BCNF wid example
thanks in advance




Responses

#268756    Author: Ultimaterengan      Member Level: Gold      Member Rank: 11     Date: 23/Jul/2008   Rating: 2 out of 52 out of 5     Points: 1

hi,
http://www.cs.sjsu.edu/~lee/cs157b/The_Normal_Forms2.ppt
http://forums.oracle.com/forums/thread.jspa?threadID=375006

i hope this may help u



Thanks & Regards
G.Renganathan
Nothing is mine ,Everything is yours!!!



 
#268757    Author: shanmukha kumari      Member Level: Gold      Member Rank: 0     Date: 23/Jul/2008   Rating: 2 out of 52 out of 5     Points: 6

3rd NF:

The third normal form (3NF) is a normal form used in database normalization. 3NF was originally defined by E.F. Codd[1] in 1971. Codd's definition states that a table is in 3NF if and only if both of the following conditions hold:

The relation R (table) is in second normal form (2NF)
Every non-prime attribute of R is non-transitively dependent (i.e. directly dependent) on every key of R.
A non-prime attribute of R is an attribute that does not belong to any candidate key of R.[2] A transitive dependency is a functional dependency in which X ? Z (X determines Z) indirectly, by virtue of X ? Y and Y ? Z (where it is not the case that Y ? X).[3]

A 3NF definition that is equivalent to Codd's, but expressed differently, was given by Carlo Zaniolo in 1982. This definition states that a table is in 3NF if and only if, for each of its functional dependencies X ? A, at least one of the following conditions holds:

X contains A (that is, X ? A is trivial functional dependency), or
X is a superkey, or
A is a prime attribute (i.e., A is contained within a candidate key)[4]
Zaniolo's definition gives a clear sense of the difference between 3NF and the more stringent Boyce-Codd normal form (BCNF). BCNF simply eliminates the third alternative ("A is a prime attribute").

An example of a 2NF table that fails to meet the requirements of 3NF is:

Tournament Winners Tournament Year Winner Winner Date of Birth
Indiana Invitational 1998 Al Fredrickson 21 July 1975
Cleveland Open 1999 Bob Albertson 28 September 1968
Des Moines Masters 1999 Al Fredrickson 21 July 1975
Indiana Invitational 1999 Chip Masterson 14 March 1977

The only candidate key is {Tournament, Year}.

The breach of 3NF occurs because the non-prime attribute Winner Date of Birth is transitively dependent on {Tournament, Year} via the non-prime attribute Winner. The fact that Winner Date of Birth is functionally dependent on Winner makes the table vulnerable to logical inconsistencies, as there is nothing to stop the same person from being shown with different dates of birth on different records.

In order to express the same facts without violating 3NF, it is necessary to split the table into two:


Tournament Winners Tournament Year Winner
Indiana Invitational 1998 Al Fredrickson
Cleveland Open 1999 Bob Albertson
Des Moines Masters 1999 Al Fredrickson
Indiana Invitational 1999 Chip Masterson
Player Dates of Birth Player Date of Birth
Chip Masterson 14 March 1977
Al Fredrickson 21 July 1975
Bob Albertson 28 September 1968


Update anomalies cannot occur in these tables, which are both in 3NF.

BCNF:


Boyce-Codd normal form (or BCNF) is a normal form used in database normalization. It is a slightly stronger version of the third normal form (3NF). A table is in Boyce-Codd normal form if and only if, for every one of its non-trivial functional dependencies X ? Y, X is a superkey—that is, X is either a candidate key or a superset thereof.

Only in rare cases does a 3NF table not meet the requirements of BCNF. A 3NF table which does not have multiple overlapping candidate keys is guaranteed to be in BCNF.[4] Depending on what its functional dependencies are, a 3NF table with two or more overlapping candidate keys may or may not be in BCNF.

An example of a 3NF table that does not meet BCNF is:

Today's Court Bookings Court Start Time End Time Rate Type
1 09:30 10:30 SAVER
1 11:00 12:00 SAVER
1 14:00 15:30 STANDARD
2 10:00 11:30 PREMIUM-B
2 11:30 13:30 PREMIUM-B
2 15:00 16:30 PREMIUM-A

Each row in the table represents a court booking at a tennis club that has one hard court (Court 1) and one grass court (Court 2)
A booking is defined by its Court and the period for which the Court is reserved
Additionally, each booking has a Rate Type associated with it. There are four distinct rate types:
SAVER, for Court 1 bookings made by members
STANDARD, for Court 1 bookings made by non-members
PREMIUM-A, for Court 2 bookings made by members
PREMIUM-B, for Court 2 bookings made by non-members
The table's candidate keys are:

{Court, Start Time}
{Court, End Time}
{Rate Type, Start Time}
{Rate Type, End Time}
Recall that 2NF prohibits partial functional dependencies of non-prime attributes on candidate keys, and that 3NF prohibits transitive functional dependencies of non-prime attributes on candidate keys. In the Today's Court Bookings table, there are no non-prime attributes: that is, all attributes belong to candidate keys. Therefore the table adheres to both 2NF and 3NF.

The table does not adhere to BCNF. This is because of the dependency Rate Type ? Court, in which the determining attribute (Rate Type) is neither a candidate key nor a superset of a candidate key.

Any table that falls short of BCNF will be vulnerable to logical inconsistencies. In this example, enforcing the candidate keys will not ensure that the dependency Rate Type ? Court is respected. There is, for instance, nothing to stop us from assigning a PREMIUM A Rate Type to a Court 1 booking as well as a Court 2 booking—a clear contradiction, as a Rate Type should only ever apply to a single Court.

The problem can be corrected by decomposing the original table into two tables:


Rate Types Rate Type Court
SAVER 1
STANDARD 1
PREMIUM-A 2
PREMIUM-B 2
Today's Bookings Rate Type Start Time End Time
SAVER 09:30 10:30
SAVER 11:00 12:00
STANDARD 14:00 15:30
PREMIUM-B 10:00 11:30
PREMIUM-B 11:30 13:30
PREMIUM-A 15:00 16:30


The sole candidate key for the Rate Types table is {Rate Type}; the candidate keys for the Today's Bookings table are {Rate Type, Start Time} and {Rate Type, End Time}. Both tables are in BCNF. Bookings no longer explicitly include a Court identifier, but the Court may be determined by joining the Today's Bookings table to the Rate Types table on the Rate Type attribute. Showing one Rate Type against two Courts is now impossible, so the anomaly affecting the original table has been eliminated.


 
#268767    Author: Deepa      Member Level: Gold      Member Rank: 174     Date: 23/Jul/2008   Rating: 2 out of 52 out of 5     Points: 6

According to this link:
http://www.cs.jcu.edu.au/Subjects/cp1500/1998/Lecture_Notes/normalisation/bcnf.html
consider that:
It should be noted that most relations that are in 3NF are also in BCNF. Infrequently, a 3NF relation is not in BCNF and this happens only if
(a) the candidate keys in the relation are composite keys (that is, they are not single attributes),
(b) there is more than one candidate key in the relation, and
(c) the keys are not disjoint, that is, some attributes in the keys are common.





 
#268780    Author: chandramohan      Member Level: Gold      Member Rank: 214     Date: 23/Jul/2008   Rating: 2 out of 52 out of 5     Points: 1

It should be noted that most relations that are in 3NF are also in BCNF. Infrequently, a 3NF relation is not in BCNF and this happens only if
(a) the candidate keys in the relation are composite keys (that is, they are not single attributes),
(b) there is more than one candidate key in the relation, and
(c) the keys are not disjoint, that is, some attributes in the keys are common.

--------------


 
#268982    Author: Ashok Khanna      Member Level: Gold      Member Rank: 265     Date: 23/Jul/2008   Rating: 2 out of 52 out of 5     Points: 1

Comparison of BCNF and 3NF

1. We have seen BCNF and 3NF.
* It is always possible to obtain a 3NF design without sacrificing lossless-join or dependency-preservation.
* If we do not eliminate all transitive dependencies, we may need to use null values to represent some of the meaningful relationships.
* Repetition of information occurs.
2. These problems can be illustrated with Banker-schema.
* As banker-name tex2html_wrap_inline1526 bname , we may want to express relationships between a banker and his or her branch.

figure523
Figure 7.4: An instance of Banker-schema.

* Figure 7.4 shows how we must either have a corresponding value for customer name, or include a null.
* Repetition of information also occurs.
* Every occurrence of the banker's name must be accompanied by the branch name.
3. If we must choose between BCNF and dependency preservation, it is generally better to opt for 3NF.
* If we cannot check for dependency preservation efficiently, we either pay a high price in system performance or risk the integrity of the data.
* The limited amount of redundancy in 3NF is then a lesser evil.
4. To summarize, our goal for a relational database design is
* BCNF.
* Lossless-join.
* Dependency-preservation.
5. If we cannot achieve this, we accept
* 3NF
* Lossless-join.
* Dependency-preservation.
6. A final point: there is a price to pay for decomposition. When we decompose a relation, we have to use natural joins or Cartesian products to put the pieces back together. This takes computational time.

refer http://www.cs.sfu.ca/CC/354/zaiane/material/notes/Chapter7/node12.html

Please rate this post, if it is useful for you.

Thanks & Regards
Ashok


 
#268983    Author: Ashok Khanna      Member Level: Gold      Member Rank: 265     Date: 23/Jul/2008   Rating: 2 out of 52 out of 5     Points: 1

Boyce-Codd Normal Form (BCNF)

* When a relation has more than one candidate key, anomalies may result even though the relation is in 3NF.
* 3NF does not deal satisfactorily with the case of a relation with overlapping candidate keys
* i.e. composite candidate keys with at least one attribute in common.
* BCNF is based on the concept of a determinant.
* A determinant is any attribute (simple or composite) on which some other attribute is fully functionally dependent.
* A relation is in BCNF is, and only if, every determinant is a candidate key.

Please rate this post, if it is useful for you.

Thanks & Regards
Ashok


 
Post Reply

 This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post.



Return to Discussion Forum
Start new thread

Subscribe to Subscribers
Awards & Gifts
Talk to Webmaster Tony John
Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India