What is the key difference between BCNF and 3NF

I must respectfully disagree strongly with arbert and the materials he has linked to.

BCNF is distinctly defined from 3NF for very good reasons.

A relation in 3rd Normal Form has no non-key Functional Dependencies.
Boyce-Codd Normal Form only becomes applicable to a relation if it has overlapping composite keys - if the keys are separated out to different relations, the design is in BCNF.

A relation with no overlapping composite keys is in BCNF and automatically in 3NF.
A relation with overlapping composite keys is NOT in BCNF, but may still be in 3NF.

Example:

Hotel room booking system, with from and to dates:

BOOKING (ROOM_NUMBER, ARRIVAL_DATE, DEPARTURE_DATE)

Candidate keys are (ROOM_NUMBER, ARRIVAL_DATE) and (ROOM_NUMBER, DEPARTURE_DATE). To normalize, the relation would be split into two relations:

BOOKING_ARRIVAL (ROOM_NUMBER, ARRIVAL_DATE)
BOOKING_DEPARTURE (ROOM_NUMBER, DEPARTURE_DATE)

In most instances, people tend to avoid this by using a surrogate key, e.g.:

BOOKING (BOOKING_ID [pk], ROOM_NUMBER, ARRIVAL_DATE, DEPARTURE_DATE)

But, the problem with this design is that it is then necessary to write complicated trigger code to avoid data integrity problems (e.g. to restrict overlapping bookings for a single room). It is arguably easier to write this constraint with the earlier correct design.

Jeff

I respectfully read your answer and would also like a link that demonstrates what you're talking about....

I've demonstrated the difference in my post.

For more information regarding this topic I recommend "The Database Relational Model: A Retrospective Review and Analysis" by C.J. Date, 2001, Addison Wesley Longman, ISBN  0201612941.

In it he refers to Carlo Zaniolo's paper "A New Normal Form for the Design of Relational Database Schemata", (ACM Transactions on Database Systems 7, No. 3 (Sept 1982).

He does explain how 3NF as it was initially described by Ian Heath in 1971 was equivalent to BCNF; and how the currently accepted definition of 3NF discludes BCNF. It is understandable that confusion has resulted in the literature.

Jeff

Thanx for recomending the book but i am not goin too far i am just learning to normalize basic tables.

For instance
if i hv these functional dependencies

Invoiceno                  -> invoiceDate, invoiceAmount, Custmerno

customerno               ->customerName, customerAddress, PaymentsReceived

mobileno, date, time ->numberDialed,Duration

mobileno                   ->serviceUser, serviceType, customerno

serviceType               ->planCost, includedCallAmount, callRate

If i want to change to BCNF do i need to change it to 1,2,3 NF first or i can do directly to BCNF
Can anyone explain me this with explanation comment to understand

 

julia3,

A design is either unnormalized, in 1NF, 2NF, 3NF, BNCF or higher. Each normal form builds on the previous one, so if your design is in BCNF, it is automatically (by definition) already in 1NF, 2NF and 3NF.

In your design, where is the overlapping composite candidate keys? If there are none, then BCNF does not apply.

Disclaimer: it is impossible to correctly normalize a table design without knowing all the business rules.

Jeff

So jpkemp ,
I f i want to normalise it into BCNF i hv to follow from 1 NF right.
On above example- what r the possible primary keys and foreign keys, i need to know that right.
so if i want to change it into 1NF i hv to put under invoiceno (determining by invoiceno)

I am really tottaly confused...

I've made a few assumptions here:

INVOICE (InvoiceNo [pk], InvoiceDate, InvoiceAmount, CustomerNo)
CUSTOMER (CustomerNo [pk], CustomerName, CustomerAddress, PaymentsReceived)
MOBILE (MobileNo [pk], ServiceUser, ServiceType, CustomerNo)
CALL (MobileNo, CallDate, CallTime, NumberDialed, Duration) pk=(MobileNo, CallDate, CallTime)
SERVICE (ServiceType [pk], PlanCost, IncludedCallAmount, CallRate)

I assume then that a customer cannot call more than one NumberDialed at the same time.

If (NumberDialed, CallDate, CallTime) is another candidate key for CALL, then BCNF does apply as you have two candidate keys that overlap.

Jeff

To get into 1NF you remove all multi-valued columns.
To get into 2NF you remove all functional dependencies on part of a composite key.
To get into 3NF you remove all functional dependencies on non-key columns.
To get into BCNF you remove all functional dependencies on overlapping composite candidate keys.
To get into 4NF you remove all multi-valued dependencies.
To get into 5NF you remove all join dependencies.

P.S. There is a 6th Normal Form recently described, but I haven't read the book on this one yet.

6th Normal Form: C.J. Date, “Temporal Data and the Relational Model”, 2003

Thanx jpkemp,
I am not worried about 4,5,6Nf
When we change into 3NF if we hv transitive dependecies does it violates 3NF
 
so is that the one u hv mentioned above is in 1,2,3 and BCNF orderly or we hv to normalised it
can u explain me frm that assumption
regards

working...

To explain BCNF a bit more in the context of your example:

CALL (MobileNo, CallDate, CallTime, NumberDialed, Duration) pk=(MobileNo, CallDate, CallTime)

Here, a problem exists if a user tries to insert the following rows:

('0123000001','01/01/2003','06:00','0123000002','00:30')
('0123000002','01/01/2003','06:00','0123000001','00:30')

Now, having a primary key on (MobileNo, CallDate, CallTime) does not restrict the insertion of rows like the above which are obviously invalid - two people can't call each other at the same time (one is the caller, the other is the numberdialed).

You could resolve this with a table-level trigger that checks for invalid rows before they are inserted. But, the relational model does not require triggers and so it must provide a way of avoiding this problem through the use of proper relational design.

BCNF resolves this problem but I'm not sure how to apply it here. I'm still looking.

Jeff

Suppose it is invoice (bill) to customer.
so here just think as invoice not the relationship of like u hv mentioned "two people can't call each other at the same time"

Sorry I don't understand your last post. What dependency are you trying to resolve?

i mean i hv taken these FD from printed Invoice, in example i hv printed invoice with these FD

I have to go to a physio appt now so I'll have to get back to you.

See you later
Jeff

Ok thanx a lot hope u will back and try to explain me
see u

I've found a good reference in ch5 of Practical Issues in Database Management by Fabian Pascal (2000, Addison-Wesley). It recommends decomposition into BCNF using a surrogate key.

CALL (MobileNo, CallDate, CallTime, NumberDialed, Duration) pk=(MobileNo, CallDate, CallTime)

decomposition into BCNF could be:

CALL (CallNo [pk], MobileNo, CallDate, CallTime, Duration)
DIALLED (CallNo [pk], NumberDialed) fk CallNo references CALL(CallNo)

Here, a constraint would be added to CALL that validates that no other CALL exists where (MobileNo, CallDate, CallTime) = (NumberDialed, CallDate, CallTime).

If you need further help with the invoice relation, please explain what question you have.

Jeff

So what are my possible all relation while changing that above FDs into BCNF

i am little confuse with ur last comment

*Its getting long long so i hv increased the points for u thank u.

My last comment was basically that I don't understand your requirements. What do you want? Do you want me to normalize your design for you?

Remember that your initial question was not "please normalize this for me" but "what is the difference between 3ND and BCNF". If I haven't explained this clearly enough let me know and I'll try to explain it better.

If you believe your design is not in BCNF, please explain in what way - because I don't know your business requirements I have insufficient information to normalize your design.

Jeff

Yha the question hv asked me to normalize the data structure to meet but i am familiar with definition of 1nf and 2 nf
confused with 3nf, and bcnf.
It says that to normalise into 1st, 2nd ,3rd, and through to bcnf
and i hav to indicate the primary, foreign key from that Dependecies.

Is this a coursework question?

not exactly,
but its our exam sample question
y does it matter

There are lots of other questions i wanna ask .....more

I'm happy to help you as much as possible. However, it matters because we aren't supposed to do homework here on EE - it is primarily a technical forum for experts to exchange information and advice. I'm very happy to help you understand the principles better, and point out areas of improvement/correction in your answers to exam questions, but I won't do the work for you - this would not be good service to you. You have to do the work, otherwise you will never learn.

Now, you have given your dependencies as follows:

Invoiceno                  -> invoiceDate, invoiceAmount, Custmerno
customerno               ->customerName, customerAddress, PaymentsReceived
mobileno, date, time ->numberDialed,Duration
mobileno                   ->serviceUser, serviceType, customerno
serviceType               ->planCost, includedCallAmount, callRate

I can't help you get this into BCNF unless you also provide all relevant business requirements. Alternatively, give your suggested design and I'll try to work out where the redundancies are. However, you must understand that it is simply impossible to accurately diagnose a problem without complete information.

Jeff

julia3,

I had a look at your previous question which you asked, which obviously prompted this one.
http://www.experts-exchange.com/Databases/Q_20621735.html

The experts who helped you gave you lots of information which should be sufficient to answer your question, yet you still awarded a C (i.e. you felt they hadn't helped you enough).

I think you need to seek training in database fundamentals - this will be of great advantage to your learning, especially if you want to get into database design work in the future. I really recommend you attend a course in database design - you will get a lot out of it.

Jeff

Okay i c,
but if i got question like this-

realation R(A,B,C,D,E,F) IS IN 3NF . As (A,B,C) is primary key
FD given is-
E -> C

If i like to convert it into 2-3 relations, R1, R2 and R3 all in BCNF, do need to ask all relevant requirements.
Can i solve it just looking at relations and FD provided.
coz its diff as compare to previous question. and more confusion i hv understood some concept but in this case its diff.

Yha in prevous question i didn't get sufficient answer and some of their material only talks about 1,2 and 3nf
not bcnf, so i want to learn more.

R(A,B,C,D,E,F) with (A,B,C) as PK cannot be in 3NF if the functional dependency E->C (FD on partial key) still exists. This would indicate the relation is only in 1NF.

Yes, you can solve it by just looking at relations and FD provided - the FD's represent the business rules as pertains to normalization.

Jeff

RE your previous question:

microIT gave quite a good explanation of BCNF. For 30 points he was unwilling to help you further. Remember, we're all unpaid volunteers here, normally we charge people a lot of money to give advice and expertise.

Jeff

So here

relation R(a,b,c,d,e,f)  which in 2nf
a,b is p.k
fd here is
c,d -> e

so i hv here tried myself just correct w'r i am wrong if.
i a, converting it into 3nf

solution-
R1(c,d,e) c,d pk

R2(a,b,c,d,f) c,d COMPOSITE FK
is this correct.

Yes, well done.

Jeff

But here in this case

R(A,B,C,D,E,F,G,H) G,H IS pk  WHICH IS IN 2NF

and i hv 2 FD
AB -> F
C  -> D

While changing it into 3nf

is this way

R1(A,B,F) ---------------(A,B) P.K
R2(C,D)   ---------------(C) P.K
R3(A,B, C , E, G,H) ----(A,B) F.K and C also f.k
is that the way of doing?

when there will be 2 FD i guess we have three relation rite?
or we can do it in 2 relation like previous.

Thanx for helping me.

In this case

Relation R(A,B,C,D) has the following Functional Dependencies:

AB      ->      CD
C      ->      B

Convert R into two-or three relations, R1, R2 and R3 all in the highest possible normal form

do i hv to follow same method i bit confuse coz of word highest nf.

guide me here