Re: Relation problem

From: <MSherrill_at_compuserve.com>
Date: Fri, 26 Jan 2001 19:41:34 GMT
Message-ID: <3a71ad7f.8334584_at_news.compuserve.com>


On 26 Jan 2001 13:03:09 GMT, hidders_at_REMOVE.THIS.win.tue.nl (Jan Hidders) wrote:

>Sure, but then you are dropping the constraint the the Organization
>table is the union of the two "subtables". That's fine as long as you
>are clear about what every tuple in every table means.

The Organization table isn't the union of anything. Do you *know* what a supertype is? Judging by your contribution to this thread, I'd have to say you don't. It looks like you're just throwing words around.

Maybe these bite-sized pieces of SQL will help.

CREATE TABLE Parties (
  PartyID INTEGER NOT NULL PRIMARY KEY,
  PartyName CHAR(30) NOT NULL,
  PartyType CHAR(3) NOT NULL
);

ALTER TABLE Parties
ADD CONSTRAINT TypeIsValid
CHECK (PartyType IN ('Ind', 'Org'));

INSERT INTO Parties VALUES (1, 'Savealot, Eva', 'Ind'); INSERT INTO Parties VALUES (2, 'Wibble, Inc.', 'Org');

CREATE TABLE Persons (
  PersonID INTEGER NOT NULL PRIMARY KEY,   HeightInInches INTEGER NOT NULL
);

ALTER TABLE Persons
ADD CONSTRAINT FKPID
FOREIGN KEY (PersonID) REFERENCES Parties (PartyID);

ALTER TABLE Persons
ADD CONSTRAINT IDRefersToIndividual
CHECK (EXISTS
  (SELECT PartyID FROM Parties
   WHERE (
    (Parties.PartyID = Persons.PersonID) AND     (Parties.PartyType = 'Ind')
   )
  )
);

INSERT INTO Persons VALUES (1, 72);

CREATE TABLE Orgs (
  OrgID INTEGER NOT NULL PRIMARY KEY,
  TaxID CHAR(9) NOT NULL
);

ALTER TABLE Orgs
ADD CONSTRAINT FKOID
FOREIGN KEY (OrgID) REFERENCES Parties (PartyID);

ALTER TABLE Orgs
ADD CONSTRAINT OrgIsOrg
CHECK (
  EXISTS (
    SELECT PartyID FROM Parties
    WHERE (

      (Parties.PartyID = Orgs.OrgID) AND 
      (Parties.PartyType = 'Org')

    )
  )
);

INSERT INTO Orgs VALUES (2, '23-4432');

-- 
Mike Sherrill
Information Management Systems
Received on Fri Jan 26 2001 - 20:41:34 CET

Original text of this message