Re: Help needed on Boyce-Codd Normal Form.

And this intersection table has a real-world interpretation: the "class" (or "section" at some universities).

On Dec 14, 12:06 pm, "Knowledgy" <knowle..._at_knowledgy.org> wrote:
> 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, LLCwww.knowledgy.org
> Atlanta's Business Intelligence and Knowledge Management Experts
>
> "Kira Yamato" <kira..._at_earthlink.net> wrote in message
>
> news:2007121406424775249-kirakun_at_earthlinknet...
>
> > 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
Received on Fri Dec 14 2007 - 18:34:27 CET