Does entity integrity imply entity identity?

#51

On Aug 7, 6:03*am, "Walter Mitty" <wami... (AT) verizon (DOT) net> wrote:

Quote:

"Nilone" <rea... (AT) gmail (DOT) com> wrote in message

news:d218288c-7a72-4b77-900e-8fa39fcdae6a (AT) l34g2000vba (DOT) googlegroups.com...
On Aug 6, 10:13 pm, "Mr. Scott" <do_not_re... (AT) noone (DOT) com> wrote:

snip

How, exactly? If you have columns K, A, B, and C such that for a
given
K
it is permissible for the values for A, B or C to be at present
unknown,
but not both A and B, how would you lay out the tables and
referential
constraints without using nulls?

Here's a more concrete example. Software can be delivered to a
customer
over the internet or through the mail or both, but in order to deliver
over
the internet there must be an e-mail address, and in order to deliver
through the mail there must be a snail-mail address. There may alsobe
a
delivery contact phone number. So for a given order K, there must be
at
least one of an e-mail address A or a snail-mail address B, and there
may be
a delivery phone number C.

Three separate relations: {K -> A}, {K -> B}, {K -> C}. It doesn't
enforce the requirement for at least A or B, but neither does {K -> A,
B, C} where A, B and C are nullable.

CREATE TABLE T
(
K INT NOT NULL PRIMARY KEY,
A VARCHAR(40) NULL,
B VARCHAR(160) NULL,
C VARCHAR(20) NULL,
CHECK (A IS NOT NULL OR B IS NOT NULL)
)

enforces the requirement for either A or B.

I see your point. *While searching for information on this topic, I
came across the following paper:

Incorporating record subtyping into a relational data modelhttp://dbis.eprints.uni-ulm.de/377/1/KaDa94.pdf

If anyone has any opinions or further information on record subtyping,
I'd like to hear about it.

Do a web search on "generalization specialization relational modeling".
You'll find several interesting articles.

Thanks! Among other things I discovered RM/T and got a copy of Codd's
original paper. Interesting stuff.

#52

Mr. Scott wrote:

Quote:

,,,...
I probably should have included D and E columns that don't allow nulls.

(X,D,E) "X is an order placed on date D by customer E."
(X,A) "A is the e-mail address for order X."
(X,B) "B is the snail-mail address for order X."
(X,C) "C is the delivery phone number for order X."

Then there would be no question as to whether an order table would be needed
in addition to the order_e-mail table and the order_snail-mail table.

Okay, that's clearer. Now I get the impression that SQL doesn't support
all the FK constraints desired, Is that so? If so, does that mean that
SQL can't test relations for equality?

#53

"paul c" <toledobythesea (AT) oohay (DOT) ac> wrote

Quote:

Mr. Scott wrote:
,,,...
I probably should have included D and E columns that don't allow nulls.

(X,D,E) "X is an order placed on date D by customer E."
(X,A) "A is the e-mail address for order X."
(X,B) "B is the snail-mail address for order X."
(X,C) "C is the delivery phone number for order X."

Then there would be no question as to whether an order table would be
needed in addition to the order_e-mail table and the order_snail-mail
table.

Okay, that's clearer. Now I get the impression that SQL doesn't support
all the FK constraints desired, Is that so? If so, does that mean that
SQL can't test relations for equality?

SQL doesn't support foreign keys that reference views, but I'm not so sure
if that is a bad thing. I don't see what it has to do with whether SQL can
test relations for equality or not.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Does entity integrity imply entity identity?

comp.databases.theory comp.databases.theory

Re: Does entity integrity imply entity identity? - 08-07-2009 , 02:09 AM

Re: Does entity integrity imply entity identity? - 08-08-2009 , 08:27 PM

Re: Does entity integrity imply entity identity? - 08-09-2009 , 08:18 PM