Re: Help needed on Boyce-Codd Normal Form.
From: Kira Yamato <kirakun_at_earthlink.net>
Date: Fri, 14 Dec 2007 20:16:09 -0500
Message-ID: <2007121420160975249-kirakun_at_earthlinknet>
>> On 2007-12-14 06:24:06 -0500, Kira Yamato <kira..._at_earthlink.net> said:
>>
>>
>>
>>
>> Ok. Here is my attempt:
>>
>> One obvious relation is
>> T --> J.
>> This relation is obviously BCNF and it captures 2).
>>
>> But I'm having trouble identifying the other relation(s) for 1).
>>
>> First, I tried the relation
>> SJ --> T,
>> but it is not BCNF because of the functional dependency T --> J.
>>
>> Next, I tried the relation
>> ST --> {},
>> but this relation does not enforce 1). For example, a student can take
>> two teachers teaching the same subject --- a scenario not allowed.
>>
>> So, I'm in search of some hints or suggestions.
>>
>> --
>>
>> -kira
Date: Fri, 14 Dec 2007 20:16:09 -0500
Message-ID: <2007121420160975249-kirakun_at_earthlinknet>
On 2007-12-14 17:08:02 -0500, TroyK <cs_troyk_at_juno.com> said:
> On Dec 14, 4:42 am, Kira Yamato <kira..._at_earthlink.net> wrote:
>> On 2007-12-14 06:24:06 -0500, Kira Yamato <kira..._at_earthlink.net> said:
>>
>>> Suppose I want to create a schema for >>> S = student, >>> J = subject, >>> T = teacher, >>> that enforces
>>
>>> 1) For each subject, each student taking that subject is taught by >>> exactly one teacher. But a student can take multiple subjects. >>> 2) Each teacher teaches exactly one subject. But a subject can be >>> taught by multiple teachers.
>>
>>> How do you create a schema that is at least BCNF and enforces 1) and 2)?
>>
>> Ok. Here is my attempt:
>>
>> One obvious relation is
>> T --> J.
>> This relation is obviously BCNF and it captures 2).
>>
>> But I'm having trouble identifying the other relation(s) for 1).
>>
>> First, I tried the relation
>> SJ --> T,
>> but it is not BCNF because of the functional dependency T --> J.
>>
>> Next, I tried the relation
>> ST --> {},
>> but this relation does not enforce 1). For example, a student can take
>> two teachers teaching the same subject --- a scenario not allowed.
>>
>> So, I'm in search of some hints or suggestions.
>>
>> --
>>
>> -kira
> > This looks like the example Date gives in An Introduction To Database > Systems (or am I thinking of another of his writings?). > > Basically, your choices are to decompose the relvar into 2 BCNF > relvars, in which case you will have a multi-relvar constraint to deal > with in order to preserve one of the FDs, or forgo BCNF and leave the > relvar as is.
Hmm... Interesting. I had thought that you can always decompose a scheme into BCNF while keeping the FD independent from each other.
> > Here are the FDs I see given the rules... they may give you a hint how > to proceed: > > { S, J } -> { T } > Loosely, if you know a student and the subject they're taking, you > know the teacher, and > > { T } -> { J } > if you know the teacher, you know the subject being taught.
Yes, these two FD's are exactly the two constraints I was trying to achieve with BCNF. But, I suppose this cannot always be done then.
Is there a way to identify when a set of FD's cannot be decomposed into BCNF?
-- -kiraReceived on Sat Dec 15 2007 - 02:16:09 CET