Re: Teach SELECT DISTINCT first!

From: Mikito Harakiri <mikharakiri_at_iahu.com>
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