Re: Help needed on Boyce-Codd Normal Form.

You must resolve the many-to-many relationships. You'll need to create an additional table (known as an intersection table) to handle the constraints you mention. The intersection table will control constraints 1 and 2 in your post

-- 
Sincerely,
John K
Knowledgy Consulting, LLC
www.knowledgy.org
Atlanta's Business Intelligence and Knowledge Management Experts


"Kira Yamato" <kirakun_at_earthlink.net> wrote in message 
news:2007121406424775249-kirakun_at_earthlinknet...


> On 2007-12-14 06:24:06 -0500, Kira Yamato <kirakun_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

>