DB2 Implementation - ERD Problems

[Kurt Stein]

Hi,

my team has the following problem.

There are 3 entities(A,B,C). A 'is_in' B. And A 'owns' C. (owns and
is_in are relations). C needs either A or B to be owned.
This means. Normaly C is owned by a member of A, but if this case is
not true, then C has to be owned by a member of B.
How can we realize this as a E/R diagram and in a DB2 SQL
Implementation?
We thought of making 2 Relations (r,s): A r C and B s C
But we cannot make sure that either r or s are true(existing).
Thanks a lot for your help.
Kurt Stein

[Serge Rielau]

Kurt,

I see two options:
a) Use a typed table hierarchy with A under B
There are some limitatiosn of what DB2 does with typed tables.
typed tables are not exactly mainstream.
b) Extend B to truly comprise A. If A has more columns than B you can
either add it as a dimension table (also called vertical partitioning)
or you can simply add NULLable columns to B and some typeid telling you
which rows in A are also B. (thsi is how typed tables work under teh
covers).

Either way, once you have chosen one of the solutions you can declare RI
between C and B.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

Kurt,

I see two options:
a) Use a typed table hierarchy with A under B
There are some limitatiosn of what DB2 does with typed tables.
typed tables are not exactly mainstream.
b) Extend B to truly comprise A. If A has more columns than B you can
either add it as a dimension table (also called vertical partitioning)
or you can simply add NULLable columns to B and some typeid telling you
which rows in A are also B. (thsi is how typed tables work under teh
covers).

Either way, once you have chosen one of the solutions you can declare RI
between C and B.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Knut Stolze]

Serge Rielau wrote:

Kurt,

I see two options:
a) Use a typed table hierarchy with A under B
There are some limitatiosn of what DB2 does with typed tables.
typed tables are not exactly mainstream.
b) Extend B to truly comprise A. If A has more columns than B you can
either add it as a dimension table (also called vertical partitioning)
or you can simply add NULLable columns to B and some typeid telling you
which rows in A are also B. (thsi is how typed tables work under teh
covers).

But that would model a "is_a" relationship and not the required "is_in".

I the relationship between A and B is truly "is_in", then I don't see how
you could represent the condition that C has to be owned by either A or B
in the E/R diagram. That's just one of the restrictions of E/R.

In SQL, you can implement the 2 tables A, B, and C with C having two columns
(C1, C2), one referencing A and the other references B. Then define RI
from C1 to A and C2 to B, plus a constraint on C that says that either C1
or C2 must be NULL.

CREATE TABLE a ( a INT NOT NULL PRIMARY KEY );
CREATE TABLE b ( b INT NOT NULL PRIMARY KEY );
CREATE TABLE c (
c1 INT,
c2 INT,
FOREIGN KEY (c1) REFERENCES a,
FOREIGN KEY (c2) REFERENCES b,
CHECK ((c1 IS NOT NULL AND c2 IS NULL) OR
(c2 IS NULL AND c2 IS NOT NULL))
);

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Knut Stolze]

Serge Rielau wrote:

Kurt,

I see two options:
a) Use a typed table hierarchy with A under B
There are some limitatiosn of what DB2 does with typed tables.
typed tables are not exactly mainstream.
b) Extend B to truly comprise A. If A has more columns than B you can
either add it as a dimension table (also called vertical partitioning)
or you can simply add NULLable columns to B and some typeid telling you
which rows in A are also B. (thsi is how typed tables work under teh
covers).

But that would model a "is_a" relationship and not the required "is_in".

I the relationship between A and B is truly "is_in", then I don't see how
you could represent the condition that C has to be owned by either A or B
in the E/R diagram. That's just one of the restrictions of E/R.

In SQL, you can implement the 2 tables A, B, and C with C having two columns
(C1, C2), one referencing A and the other references B. Then define RI
from C1 to A and C2 to B, plus a constraint on C that says that either C1
or C2 must be NULL.

CREATE TABLE a ( a INT NOT NULL PRIMARY KEY );
CREATE TABLE b ( b INT NOT NULL PRIMARY KEY );
CREATE TABLE c (
c1 INT,
c2 INT,
FOREIGN KEY (c1) REFERENCES a,
FOREIGN KEY (c2) REFERENCES b,
CHECK ((c1 IS NOT NULL AND c2 IS NULL) OR
(c2 IS NULL AND c2 IS NOT NULL))
);

--
Knut Stolze
Information Integration
IBM Germany / University of Jena