Re: Teach SELECT DISTINCT first!

"Mikito Harakiri" <mikharakiri_at_iahu.com> wrote in message news:<1tVjc.30$WN3.212_at_news.oracle.com>...
> > "robert" <gnuoytr_at_rcn.com> wrote in message
> > news:da3c2186.0404271241.a427ea5_at_posting.google.com...
> >
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=c0d87ec0
> .0202240850.428d7b88%40posting.google.com&rnum=12&prev=/groups%3Fq%3D%252B%2
> 522select%2Bdistinct%2522%2B%252Bdate%2B%2Bgroup:comp.databases.theory%26sta
> rt%3D10%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26group%3Dcomp.databases
> .theory%26selm%3Dc0d87ec0.0202240850.428d7b88%2540posting.google.com%26rnum%
> 3D12
>
> <quote>
> Consider a table for a school schedule:
>
> CREATE TABLE Schedule
> (period INTEGER NOT NULL,
> teacher CHAR(15) NOT NULL,
> room INTEGER NOT NULL,
> CONSTRAINT tr UNIQUE (teacher, room), -- candidate keys
> CONSTRAINT pr UNIQUE (period, room),
> CONSTRAINT pt UNIQUE (period, teacher),
> CONSTRAINT ptr UNIQUE (period, teacher, room));
>
> Yes, the rules imposed by the UNIQUE constraints are a bit weird, but
> bear with me. The following is one possible solution set that does
> not violate any of the four constraints:
>
> Schedule
> period teacher room
> ======================
> 1 'Curly' 101
> 1 'Larry' 102
> 1 'Moe' 103
> 2 'Curly' 102
> 2 'Larry' 101
> 3 'Curly' 103
> 3 'Moe' 101
>
> I constructed this table by attempting to insert all 27 possible rows
> (3 teachers, 3 rooms, and 3 periods) into the table. This is a handy,
> if inelegant, testing trick for a table with multiple constraints.
>
> Which UNIQUE constraint should be made into the PRIMARY KEY? And how
> did you decide? The relational model does not have to worry about
> performance, but you do. At first glance, it looks like the ptr
> constraint implies the other three constraints; but it does not. The
> ptr constraint by itself would allow all 27 possible rows to appear in
> the table.
> </quote>
>
> Doesn't
>
> CONSTRAINT pt UNIQUE (period, teacher)
>
> (or 'tr', or 'pr' for that matter) imply
>
> CONSTRAINT ptr UNIQUE (period, teacher, room));
>
> so that constraint 'ptr' is simply redundant?

that's fine until your database has to support a school where reading teachers cover two classes a period. not unusual.

robert Received on Tue May 04 2004 - 00:06:04 CEST