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@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?

-- 

-kira

Received on Fri Dec 14 2007 - 19:16:09 CST