modeling either/or relationship...
Volker Hetzer
I've got to model electrical constraints on a net of a pcb.
So, a net can be constrained either by layer,net class (and constraint name)
or constraint class (and constraint name).
The problem I have is that a constraint (with a name and a bunch of attributes)
can be either
- a "free constraint, adressed by layer, net class and name or
- a "bound constraint", adressed by the constraint class name.
A constraint cannot be both.
I can enforce this easily with a table constraint (id set1 xor id set2 is true), but
can I put this into an ER diagram too?
Lots of Greetings!
Volker
David Cressey
"Volker Hetzer" <volker...@ieee.org> wrote in message
news:drd0q3$idc$1...@nntp.fujitsu-siemens.com...
Look up "generalization specialization relational model" on the web.
Volker Hetzer
What I saw was that triangle thingy. Can I assume that when some tools
have a "subcategory relationship" looking like "0||", that they mean the
same? If yes,
then, change of problem description:
I'm looking for a database design tool that, when doing that subcategory
relationship, creates code that enforces the constraint that an object
can belong to only one subcategory.
What I'm currently doing is to have three tables (root + 2 subs),
root has a type field and each sub has a type field too and a constraint
forcing the sub type to be of a particular value. Sub name and type
have to exist in root and there is only one row per name in root.
My current database tool (dezign) is useless with subcategories, so
I have to fiddle with two non identifying 1:n relationships instead
of a subcategory relationship.
Lots of Greetings and thanks for bothering!
Volker
David Cressey
Joe Celko has presented table designs, including constraints for the
gen-spec case several times in this newsgroup. If you look up the history
of this newsgroup (c.d.t.) in web archives of the messages, you may be able
to see one of his ealrier resonses.
-CELKO-
attributes and then specialized sub-classes under it. As an example,
let's take the class of Vehicles and find an industry standard
identifier (VIN), and add two mutually exclusive sub-classes, Sport
utility vehicles and sedans ('SUV', 'SED').
CREATE TABLE Vehicles
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) NOT NULL
CHECK(vehicle_type IN ('SUV', 'SED')),
UNIQUE (vin, vehicle_type),
..);
Notice the overlapping candidate keys. I then use a compound candidate
key (vin, vehicle_type) and a constraint in each sub-class table to
assure that the vehicle_type is locked and agrees with the Vehicles
table. Add some DRI actions and you are done:
CREATE TABLE SUV
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SUV' NOT NULL
CHECK(vehicle_type = 'SUV'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);
CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
CHECK(vehicle_type = 'SED'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);
I can continue to build a hierarchy like this. For example, if I had a
Sedans table that broke down into two-door and four-door sedans, I
could a schema like this:
CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
CHECK(vehicle_type IN ('2DR', '4DR', 'SED')),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);
CREATE TABLE TwoDoor
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT '2DR' NOT NULL
CHECK(vehicle_type = '2DR'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Sedans(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);
CREATE TABLE FourDoor
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT '4DR' NOT NULL
CHECK(vehicle_type = '4DR'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Sedans (vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);
The idea is to build a chain of identifiers and types in a UNIQUE()
constraint that go up the tree when you use a REFERENCES constraint.
Obviously, you can do variants of this trick to get different class
structures.
If an entity doesn't have to be exclusively one subtype, you play with
the root of the class hierarchy:
CREATE TABLE Vehicles
(vin CHAR(17) NOT NULL,
vehicle_type CHAR(3) NOT NULL
CHECK(vehicle_type IN ('SUV', 'SED')),
PRIMARY KEY (vin, vehicle_type),
..);
Now start hiding all this stuff in VIEWs immediately and add an INSTEAD
OF trigger to those VIEWs.
Volker Hetzer
that it doesn't model it like this and I have to use stuff that's not
visible on the printed out EERD.
Also, since my root table doesn't contain any common attributes, just the
name (primary key) and the single permitted subtype for that particular type
I haven't defined the unique constraint.
Ditto for the subtype table since the check constraint ensures uniqueness
by constraining to a single value and primary key ensures uniqueness of the
primary key anyway.
Am I missing anything there?
Lots of Greetings and Thanks!
Volker
Murdoc
All this seems slightly over-complicated for what appears to be a simple issue. Why create 7
tables, when 1 will do?
Vehicle -> [#vin, vehicle_type, door_count]
In you example, how would you structure the query to find a listing of all 4DR cars currently in
your database (assuming that both Sedans and SUVs can be 2-door and 4-door vehicles)?
--
Ernst-Udo Wallenborn
> -CELKO- wrote:
>
>>
>> Now start hiding all this stuff in VIEWs immediately and add an INSTEAD
>> OF trigger to those VIEWs.
>
> All this seems slightly over-complicated for what appears to be a simple
> issue. Why create 7 tables, when 1 will do?
>
> Vehicle -> [#vin, vehicle_type, door_count]
This will work if and only if all there is to a car is a unique vin, a
vehicle_type and a door count, and vin is a primary key for every car.
Both assumptions hold in this example, but both are usually violated in the
wild.
What if you need to model a class of cars without vin (a variant of this
shows up often in employee databases that take SSN as a primary key and
suddenly have to deal with foreign employees)? What if there are duplicate
vins in different contexts? Two license plate numbers from different
countries for example may be identical for two different cars, maybe there
is a similar thing with your vins. Maybe you need to model vehicles from a
country where the laws use two numbers to identify chassis and engine, and
one single vin is meaningless? Then your domain may still consider a
vehicle an entity, but different vehicle_types may have different
requirements for primary keys. That is a tricky yet frequent case in
real-life databases.
The other assumption is weak, too. Your cars will have more than one
attribute. The set of attributes for a SED and a SUV are almost guaranteed
to differ. You may get away with one broad table that contains a superset
of all possible attributes of all vehicle types, but then again, Murphy's
Law says you won't.
> In you example, how would you structure the query to find a listing of all
> 4DR cars currently in your database (assuming that both Sedans and SUVs
> can be 2-door and 4-door vehicles)?
As Joe wrote, you wouldn't. You would hide all this complexity in VIEWs.
--
Ernst-Udo Wallenborn
Murdoc
> Murdoc wrote:
>
> > -CELKO- wrote:
> >
> >>
> >> Now start hiding all this stuff in VIEWs immediately and add an INSTEAD
> >> OF trigger to those VIEWs.
> >
> > All this seems slightly over-complicated for what appears to be a simple
> > issue. Why create 7 tables, when 1 will do?
> >
> > Vehicle -> [#vin, vehicle_type, door_count]
>
>
> This will work if and only if all there is to a car is a unique vin, a
> vehicle_type and a door count, and vin is a primary key for every car.
Given that the solution illustrated had only those fields, then I only included those in my
suggestion. Given that every vehicle has both a vehicle type and a door count, the only assumption
that needs work in the VIN being a primary key.
> Both assumptions hold in this example, but both are usually violated in the
> wild.
>
> What if you need to model a class of cars without vin (a variant of this
> shows up often in employee databases that take SSN as a primary key and
> suddenly have to deal with foreign employees)?
I am unfamiliar with SSNs.
> What if there are duplicate
> vins in different contexts? Two license plate numbers from different
> countries for example may be identical for two different cars, maybe there
> is a similar thing with your vins.
Solution: Make the license_plate & country combination unique.
>Maybe you need to model vehicles from a
> country where the laws use two numbers to identify chassis and engine, and
> one single vin is meaningless?
Then use those two numbers? Or create an internal unique index as the primary key, and remove the
database-level uniqueness constraint. Use Triggers and Stored Procedures to enforce integrity.
> Then your domain may still consider a
> vehicle an entity, but different vehicle_types may have different
> requirements for primary keys. That is a tricky yet frequent case in
> real-life databases.
Maybe. Another potential solution could be to define multiple unique indexes on the same table. Can
you provide an example of a real-life situation, though? I would be intrigued.
> The other assumption is weak, too. Your cars will have more than one
> attribute. The set of attributes for a SED and a SUV are almost guaranteed
> to differ.
Maybe. But my question would be: What attributes are valid only for a sedan and not an SUV? Or,
more specifically, what attributes are valid for a 4DR SUV as opposed to 2DR SUV? Or Sedan?
> You may get away with one broad table that contains a superset
> of all possible attributes of all vehicle types, but then again, Murphy's
> Law says you won't.
>
>
> > In you example, how would you structure the query to find a listing of all
> > 4DR cars currently in your database (assuming that both Sedans and SUVs
> > can be 2-door and 4-door vehicles)?
>
>
> As Joe wrote, you wouldn't. You would hide all this complexity in VIEWs.
My question still stands. In order to list all 4 door vehicles in the system (using the
hierarchical design suggested), the query would have to visit EVERY specific car type in the system.
Essentially, the end result for the proposed 'hierarchical' design is a nightmare:
Vehicle
SUV
4-Door
2-Door
SED
4-Door
2-Door
STW (Station Wagon)
HTC (Hatchback)
TRK (Truck)
SMI (Semi-Trailer)
RDT (Road Train)
UTE
The list would be endless.
--
Jason.G...@gmail.com
> Essentially, the end result for the proposed 'hierarchical' design is a nightmare:
>
> Vehicle
> SUV
> 4-Door
> 2-Door
> SED
> 4-Door
> 2-Door
> STW (Station Wagon)
> HTC (Hatchback)
> TRK (Truck)
> SMI (Semi-Trailer)
> RDT (Road Train)
> UTE
>
> The list would be endless.
Could you expand on why would this be a nightmare?
The only thing that strikes me with the hierarchical class structure as
posted by Celko is that I am not clear about how I would go about
querying it (but perhaps this is due to limitations in my SQL skills).
Perhaps someone in the now could put me on the right track - If I did
want to know (for example) how many 4-door cars there were in the
system my impression is that I would not be able to extract this
information this in a single SQ: query (I would insted use some sort of
external script processing to process query results before firing off
new ones), as somehow I will have to be matching entries in the Vehicle
table (e.g. the Char(3) SUV) to other actual table names, before I
query those tables to count their rows?
David Portas
>
> The only thing that strikes me with the hierarchical class structure as
> posted by Celko is that I am not clear about how I would go about
> querying it (but perhaps this is due to limitations in my SQL skills).
>
> Perhaps someone in the now could put me on the right track - If I did
> want to know (for example) how many 4-door cars there were in the
> system my impression is that I would not be able to extract this
> information this in a single SQ: query (I would insted use some sort of
> external script processing to process query results before firing off
> new ones), as somehow I will have to be matching entries in the Vehicle
> table (e.g. the Char(3) SUV) to other actual table names, before I
> query those tables to count their rows?
I think the different tables for 2- and 4-door vehicles was a poorly
chosen example - useful only to illustrate how to extend the design.
The number of doors is an attribute that all vehicles have in common so
it more sensibly belongs in one place only - the Vehicles table.
More generally, a union and joins should allow you to interrogate the
data across all tables without resorting to procedural code in an
external script.
--
David Portas
Jason.G...@gmail.com
> I think the different tables for 2- and 4-door vehicles was a poorly
> chosen example - useful only to illustrate how to extend the design.
> The number of doors is an attribute that all vehicles have in common so
> it more sensibly belongs in one place only - the Vehicles table.
>
> More generally, a union and joins should allow you to interrogate the
> data across all tables without resorting to procedural code in an
> external script.
>
> --
> David Portas
Thanks for your response. Simplifying it slightly, if I wanted to
determine the total number of cars stored, the logical process might
be:
1) Obtain the table names for individual car types (SUV, SED, etc) from
the master table (so I can determine where the cars are stored).
2) Iterate through each of these type names counting the no. of rows in
each of their corresponding sub table.
3) Sum these counts.
But how to link these steps together into one statement is still
eluding me, specifically the link between steps 1 and 2? Much
appreciated if anyone can illuminate me. Many thanks, Jason.
David Portas
jason.g...@gmail.com wrote:
How about:
SELECT COUNT(*) FROM Vehicles;
Or maybe:
SELECT COUNT(*)
FROM Vehicles
WHERE vehicle_type IN
(... list of types);
--
David Portas
Jason.G...@gmail.com
My bad. By simplifying and not actually thinking, I trivialised the
example to a simple select statement, and lost my real question - I
think I'll quit while I'm behind ;) Thanks for attempting to answer the
question anyhow.
paul c
> The classic scenario calls for a root class with all the common
> attributes and then specialized sub-classes under it. As an example,
> let's take the class of Vehicles and find an industry standard
> identifier (VIN), and add two mutually exclusive sub-classes, Sport
> utility vehicles and sedans ('SUV', 'SED').
>
> CREATE TABLE Vehicles
> (vin CHAR(17) NOT NULL PRIMARY KEY,
> vehicle_type CHAR(3) NOT NULL
> CHECK(vehicle_type IN ('SUV', 'SED')),
> UNIQUE (vin, vehicle_type),
> ..);
>
> Notice the overlapping candidate keys. I then use a compound candidate
> key (vin, vehicle_type) and a constraint in each sub-class table to
> assure that the vehicle_type is locked and agrees with the Vehicles
> table. Add some DRI actions and you are done:
>
> CREATE TABLE SUV
> (vin CHAR(17) NOT NULL PRIMARY KEY,
> vehicle_type CHAR(3) DEFAULT 'SUV' NOT NULL
> CHECK(vehicle_type = 'SUV'),
> UNIQUE (vin, vehicle_type),
> FOREIGN KEY (vin, vehicle_type)
> REFERENCES Vehicles(vin, vehicle_type)
No comment about the design but not knowing much about SQL, I'm
wondering if the "overlapping candidate keys" (ignoring whether the term
as used here is wrong) is a way of "trick"ing some products into
allowing a reference to different columns than those of a primary key?
Would interchanging the columns of the PRIMARY KEY and UNIQUE clauses
behave the same? Also, do all/most of the SQL products require a
foreign key to reference a primary key? (If so, I don't understand why.)
cheers,
p
David Portas
AFAIK in standard SQL the PRIMARY KEY / UNIQUE constraints are
interchangeable except that UNIQUE may include nullable columns whereas
PRIMARY KEY may not and is the default for a foreign key reference if
no other column(s) are specified using REFERENCES.
SQL's idea of designating a PRIMARY key from among the candidate keys
is apparently a legacy of Codd's use of that term. If it has a useful
purpose at all I guess it's to act as a hint to the DBMS on how to
implement that key physically.
--
David Portas
len...@kommunicera.umea.se
David Portas wrote:
[...]
>
> AFAIK in standard SQL the PRIMARY KEY / UNIQUE constraints are
> interchangeable except that UNIQUE may include nullable columns
Are you sure about that (unique may contain null)? I dont see how that
should work (and I know that it does not in DB2). AFAIK the rule is
that a foreign key constraint is satisfied as long as it does not
evaluate to FALSE. Assuming that a unique constraint may contain null
we can construct:
CREATE TABLE PARENT (
A int not null,
B int,
constraint AK UNIQUE (A, B)
)
CREATE TABLE CHILD (
A int not null,
B int,
constraint FK (A, B) references PARENT (A,B)
)
insert into PARENT (A) values 1
insert into CHILD (A) values 1 -- this satisfies FK because it does
evaluate to NULL (i.e. it does not evaluate to FALSE)
insert into CHILD (A) values 2 -- this also satisfies FK because it
does evaluate to NULL (i.e. it does not evaluate to FALSE)
Either I am missing something here, or null in unique does not make
sense. Can anyone with a better insight in the sqlstandard shed some
light on this?
/Lennart
[...]
David Portas
Since SQL92 NULLs are permitted in UNIQUE constraints (SQL89 didn't
allow them). Rows with nulls aren't compared in the unique constraint
so you can add multiple rows with nulls in the key columns even though
the other values are duplicated.
Here is ISO/IEC 9075-2:2003 section 8.10:
<quote>
<unique predicate> ::= UNIQUE <table subquery>
1) Let T be the result of the <table subquery>.
2) If there are no two rows in T such that the value of each column
in one row is non-null and is not distinct from the value of the
corresponding column in the other row, then the result of the
<unique predicate> is True; otherwise, the result of the <unique
predicate> is False.
</quote>
The constraint is based on this predicate.
As far as foreign keys go, a foreign key containing nulls does not
violate the REFERENCES constraint anyway so your example is valid
whether or not nulls are allowed by the UNIQUE constraint.
> Either I am missing something here, or null in unique does not make
> sense. Can anyone with a better insight in the sqlstandard shed some
> light on this?
Nulls are a disappointment if you like sense ;-)
--
David Portas
len...@kommunicera.umea.se
David Portas wrote:
> len...@kommunicera.umea.se wrote:
>
[...]
>
> Since SQL92 NULLs are permitted in UNIQUE constraints (SQL89 didn't
> allow them). Rows with nulls aren't compared in the unique constraint
> so you can add multiple rows with nulls in the key columns even though
> the other values are duplicated.
>
> Here is ISO/IEC 9075-2:2003 section 8.10:
>
> <quote>
>
> <unique predicate> ::= UNIQUE <table subquery>
>
> 1) Let T be the result of the <table subquery>.
>
> 2) If there are no two rows in T such that the value of each column
> in one row is non-null and is not distinct from the value of the
> corresponding column in the other row, then the result of the
> <unique predicate> is True; otherwise, the result of the <unique
> predicate> is False.
>
> </quote>
>
> The constraint is based on this predicate.
>
Thanx
> As far as foreign keys go, a foreign key containing nulls does not
> violate the REFERENCES constraint anyway so your example is valid
> whether or not nulls are allowed by the UNIQUE constraint.
>
Yes, I realised that the example was even sillier than I thought as
soon as I had hit the sendbutton. Still, I get this strange feeling
thinking about nulls in unique. Cant put my finger on it though.
[...]
> Nulls are a disappointment if you like sense ;-)
>
;-)
/Lennart
-CELKO-
with the same structure. Vehicles are an easy to understand example,
and the VIN is an ISO standard.
-CELKO-
aggregate up the hierarchy, I would have a UNION-ed VIEW on the common
attributes and use it.
-CELKO-
We did, too. I can only vaguely remember the arguements in X3H2 now.
Basically, we said that there is equality and there is grouping, as in
GROUP BY. NULLs fail equality, but get grouped together. The
REFERENCES clause uses grouping and not equality.
As we used to say about OS/JCL, it makes perfect sense after you work
with it for decade :)
JOG
"All roads lead to rome"...
do "all threads on comp.theory.database lead to nulls?"