Re: Teach SELECT DISTINCT first!
Date: Wed, 28 Apr 2004 14:25:02 -0700
Message-ID: <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? Received on Wed Apr 28 2004 - 23:25:02 CEST