Re: extracting information from schemas

"chris" <damoncwk_at_hotmail.com> wrote in message news:90bgct$1c0m11_at_hkunae.hku.hk...
> Dear all,
> If there are 3 relations as shown below:
> PreferredTutor(name varchar(40), instructor varchar(40))
> PreferredInstructor(name varchar(40), tutor varchar(40))
> Assignment(courseTitle varchar(40), instructor varchar(40))
>
> A tuple(x,y) is in PreferredTutor if an instructor x prefers a tutor y to
> work on his course.
> A tuple(x,y) is in PreferredInstructor if a tutor x prefers to work for
 the
> instructor y.
>
> In reality, tutors may not have any preferred instructor to work with.
> tutors who do not indicate his preferred instructor and is not preferred
 by
> any instructor will not have his or her name listed in any of the above 3
> tables. Suppose new tables are not allowed to be built, how can I ensure
> that all tutor names appear somewhere in the tables?
>
> And how can I find the total number of tutors using only the above 3
 tables?
> Thx a lot!
>
>
>
>
>

Obviously the database design is wrong as is doesn't adhere to the third normal form.
Your requirements are simply impossible without a separate instructor table and a separate tutor table. Also, according to your own words, there is no guarantee at all *all* instructors and *all* tutors appear in any of the three tables.

Regards,

Sybrand Bakker, Oracle DBA Received on Sat Dec 02 2000 - 13:16:00 CST