Re: Help needed on Boyce-Codd Normal Form.

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