Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I understand most of BCNF and have been able to complete numerous problems with it, I'm just having trouble applying it to the following situation:

R(A,B,C,D,E) FDs: { A->D; BE->A; CD->E }

Here's what I have so far:

Relationship1: (A,D)
Relationship2: (A,B,E)
Relationship3: (B,C,E)

However with this setup the CD->E functional dependency isn't considered. Should that be taken into account in a new relationship, or should D (or maybe even A?) be added to Relationship 3?

For what it's worth, I determined that:

BCE+ = ABCDE
BE+ = ABDE
AC+ = ACDE

I'm just not sure where to go from here. Thanks in advance for the help.

share|improve this question

1 Answer

up vote 0 down vote accepted

Look up "dependency preservation".

It is a known phenomenon that when decomposing relation schemas (along some given FD), certain other FD's can become inexpressible.

In a complete logical design, they have to be reinstated as a constraint on the database. However, normalization theory does not cover constraints. Normalization theory deals only with relation schemas, not with constraints between them.

If you first decompose over CD->E (leaving ABCD / CDE), you can still further decompose over A->D (leaving AD / ABC / CDE), but you will now have "lost" the BE->A dependency.

share|improve this answer
That was the other thing I ended up doing after posting (starting from CD->E). So you're saying my original answer would be correct? Would I be just as correct saying R1(A,D), R2(A,B,E), R3(B,C,E) as I would be saying R1(C,D,E), R2(A,D), R3(A,B,C) since neither of them have any FDs that are not keys? – Cabloo Feb 19 at 23:11
As far as normal forms theory is concerned, yes. As far as logical database design is concerned, remember that xNF is not where the story ends. – Erwin Smout Feb 20 at 20:16
Thanks for the clarification, Erwin. This was just a practice exam for my database exam asking to convert to BCNF, so I don't have to worry about anything past that for this question. – Cabloo Feb 20 at 22:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.