# Re: BCNF

Date: Fri, 8 Aug 2008 07:11:11 -0400

Message-ID: <kRVmk.20889$N87.5947@nlpi068.nbdc.sbc.com>

<aarklon_at_gmail.com> wrote in message

news:57725ea6-edc0-4dd4-a0bd-6c3ec629a1bc_at_r15g2000prh.googlegroups.com...

*> On Aug 2, 1:10 pm, "Brian Selzer" <br..._at_selzer-software.com> wrote:*

*> > <aark..._at_gmail.com> wrote in message*

*> >*

*> > news:3a1e04e9-e65f-469f-8357-f36486009e72_at_b38g2000prf.googlegroups.com...*

*> >*

*> >*

*> >*

*> > > Hi all,*

*> >*

*> > > BCNF*

*> >*

*> > > the following is the definition is the definition of BCNF , which i*

*> > > saw in a schaum series book*

*> >*

*> > > 1) The relation is 1 N.F*

*> >*

*> > > 2) for every functional dependency of the form X -> A , we have*

*> > > either A C X or X is a super key of r. in other words,*

*> > > every functional dependency is either a trivial dependency or in*

*> > > the case that the functional dependency is not trivial then X must*

*> > > be a super key.*

*> >*

*> > > now my questions are as follows*

*> >*

*> > > 1)*

*> >*

*> > > we know that 2-ND normal form is all about separating partial*

*> > > dependencies and full dependencies.third normal form is all about*

*> > > removing transitive dependencies, in these lines can any one give*

*> > > simple/ easy to understand method/explanation for converting a*

*> > > relation in 3rd normal form to BCNF*

*> >*

*> > A relation schema is in 3NF iff for every functional dependency the*

*> > determinant is a superkey or the dependent is prime; a relation schema *

*> > is in*

*> > BCNF iff every determinant is a superkey. A schema that is in 3NF but *

*> > not*

*> > in BCNF will have one or more determinants that are not superkeys. Find*

*> > them and eliminate them.*

*> >*

*> >*

*> >*

*> > > 2) how correct is the following definition of transitive*

*> > > dependencies*

*> >*

*> > > transitive dependencies*

*> >*

*> > > assume that A,B, and C are the set of attributes of a relation(R).*

*> > > further assume that the following*

*> > > functional dependencies are satisfied simultaneously : A -> B , B -/-*

*> > >> A, B -> C , and C -/-> A and A -> C*

*> > > observe that C -> B is neither prohibited nor required. if all these*

*> > > conditions are true, we will say that attribute C is transitively*

*> > > dependent on attribute on A*

*> >*

*> > It is not correct: what if B = C or C is a subset of B?*

*>*

*> this is what the the reply i got from the authors of the book*

*>*

*> The condition for having a transitive dependency is as follows:*

*>*

*> A -> B B-> C from this you will infer that A ->C provided that B*

*> does not determine A and C does not determine A. The reason for*

*> requiring that B does not determine A is because we are assuming that*

*> A is the only key. If B were to determine A then B will also be a key.*

*> Same thing applies to C. Remember that you are going from 1NF to 2NF.*

*> This latter form requires that you do not have any partial*

*> dependencies on any primary or candidate key. That is no subset of A*

*> can determine B if A is a key or candidate key.*

Bunk! Suppose that R has attributes {V, W, X, Y}, that A is the set of attributes {V, W, X, Y}, that B is the set of attributes {W, X, Y} and that C is the set of attributes {X, Y}.

R is in 2NF.

Clearly A --> B, B -/-> A, B --> C, A --> C, and C -/-> A,
but C is not /transitively/ dependent on A: C is /trivially/ dependent on A.

*> If B=C then they are*

*> the same attribute and every attribute determines itself. That is what*

*> the reflexivity axiom is all about. Remember that 2NF requires that*

*> there cannot be partial dependencies on the key. It does not talk*

*> about partial dependencies among the other attributes if these*

*> attribute are not primary key or candidate key. Just make sure that*

*> before you guarantee that a set of relationships are in 2NF you find*

*> ALL keys. From this set you will choose a primary key. There got to be*

*> only one primary key. The other possible keys are candidate. Tell your*

*> friends that they are not correct the definition in the book is the*

*> original and only definition that there is about transitivity.*

Assuming that you copied the definition from the book word for word, the definition in the book is wrong because it does not exclude trivial functional dependencies. Here is a definition that appears in a paper by Carlo Zaniolo, "A New Normal Form for the Design of Relational Database Schemata", ACM Transactions on Database Systems, Vol. 7, No. 3, September 1982:

Let R be a relation with attribute set U, let X be a subset of U (not necessarily proper), and let A be a member of U. A is /transitively/ dependent on X if there exists a Y which is a subset of U (not necessarily proper) such that X --> Y, Y -/-> X, Y --> A, and A is not a member of Y. [Zaniolo's definition used symbols instead of 'be a subset of ... (not necessarily proper)' and 'is/is not a member of'. Sometimes I've found that the symbols for 'is a subset of' and 'is a proper subset of' are not used consistently. This may be one case of that because clearly, in order for there to be a transitive dependency, both X and Y must both be /proper/ subsets of U, since A can't be a member of Y, so if Y were a subset of X which would be the case when X = U, then A couldn't be a member of X.]

Note that the final condition, 'A is not a member of Y,' excludes the trivial case.

*> Just*

*> remember that in the book we are assuming that A is the PK. If as your*

*> friends say B -> A and A is a key then B is a key. As I mentioned*

*> before this applies also to C. If A, B, and C were all keys then the*

*> definition of 2NF and 3NF are moot because there cannot be partial or*

*> transitive dependencies because these definitions require that there*

*> exist other attributes that are not primary or candidate keys. You*

*> need to be careful when you go to 3NF if you have PK that overlap.*

*> Then you have to move to BC normal form.*

*>*

*> Thanks for reading the book and reading it carefully. If you find any*

*> mistakes please let me know, Pauline and I are in the process of*

*> getting ready for the 2nd. Ed.*

*> Ramon A. Mata-Toledo*

Received on Fri Aug 08 2008 - 06:11:11 CDT