# Re: Help needed on Boyce-Codd Normal Form.

Date: Fri, 14 Dec 2007 09:34:27 -0800 (PST)

Message-ID: <161ff0c8-8f45-4edc-b41b-98072f2b925e_at_e6g2000prf.googlegroups.com>

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