Re: Join constraints

>> How can one set external constraints over join path using visio orm
source diagram?

For example, let's say we have three objects: Owner (ownID)
Building (buildID)
Room (roomID)

And fact types:
Owner is owner of zero or more Building / Building is owned by zero or one Owner.
Owner is owner of zero or more Room / Room is owned by zero or one Owner.
Room belongs to exactly one Building.

I would like to set the following constraint: Building b is owned by Owner xor Room r is owned by Owner, Room r belongs to Building b.

Is there a way to describe this constraint? <<

I think you are looking for the model which will "seamlessly" support the given constraint. One idea is to use CREATE ASSERTION, but I don't know which is your target DBMS, so I'm not gona use it here. The second idea is to store directly only membership over rooms, and use a view to find membership over buildings (an Owner has a building B if he has all rooms in the building B). Something like this:

CREATE TABLE owner (

    ownerID INTEGER NOT NULL PRIMARY KEY,     ...);

CREATE TABLE building (

    buildingID INTEGER NOT NULL PRIMARY KEY,     ...);

CREATE TABLE room (

    roomID INTEGER NOT NULL PRIMARY KEY,     ...);

CREATE TABLE has_a_room (

    ownerID INTEGER NOT NULL REFERENCES owner(ownerID),     roomID INTEGER NOT NULL REFERENCES room(roomID),     PRIMARY KEY (ownerID, roomID)
);

CREATE VIEW has_a_building(ownerID, buildingID) AS SELECT O.ownerID as ownerID, B.buildingID as buildingID FROM Owner O, Building B
WHERE (SELECT COUNT(*)

       FROM room R
       WHERE R.buildingID = B.BuildingID) =
      (SELECT COUNT(*)
       FROM has_a H, room R
       WHERE H.ownerID = O.ownerID
       AND H.roomID = R.roomID
       AND O.ownerID = H.ownerID)

Right now I don't see any other way to support such a constraint without using some advanced features such as triggers, stored procedures or CREATE ASSERTION statement.

regards,
Damjan S. Vujnovic

University of Belgrade
School of Electrical Engineering
Department of Computer Engineering & Informatics Belgrade, Yugoslavia

http://galeb.etf.bg.ac.yu/~damjan/ Received on Tue Jan 21 2003 - 11:33:11 CET