Re: Help needed on Boyce-Codd Normal Form.

From: TroyK <cs_troyk_at_juno.com>
Date: Fri, 14 Dec 2007 14:08:02 -0800 (PST)
Message-ID: <a7268a08-a4d0-44e8-aedc-c40301ac57ce_at_e6g2000prf.googlegroups.com>


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.

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.

HTH,
TroyK Received on Fri Dec 14 2007 - 23:08:02 CET

Original text of this message