Re: Does entity integrity imply entity identity?

"Mr. Scott" <do_not_reply_at_noone.com> wrote in message news:jrydnWYowLOIIurXnZ2dnUVZ_vmdnZ2d_at_giganews.com...
>
> "Walter Mitty" <wamitty_at_verizon.net> wrote in message
> news:JTadm.209$nh2.42_at_nwrddc02.gnilink.net...
>>
>> "Mr. Scott" <do_not_reply_at_noone.com> wrote in message
>> news:IaKdnaZV7ZxZyOnXnZ2dnUVZ_uednZ2d_at_giganews.com...
>>> Since the entity integrity rule ensures that a relational table cannot
>>> have any duplicate rows, does that imply that each row in a table maps
>>> to a distinct entity?
>>>
>>>
>>>
>> Here's the way I learned it, back in 1984.
>>
>> Each row in a table represents either an instance of an entity or an
>> instance of a relationship between or among entities. I'm not sure
>> whether or not "represents" in my wording is equivalent to "maps to" in
>> your wording.
>
> Isn't an instance of a relationship between or among entities also an
> entity?
>
That's a real good question. My answer is, not necessarily. Some practicioners of ER modeling claim that relationships cannot have attributes, so if there's an attribute that belongs to a relationship, we redefine the relationship to be an entity. (Example: the enrollment status of a student enrolled in a course, where enrollment status could be "wait listed").

In some cases, the subject matter experts treat the relationship as a "thing" in and of itself. (Example: a confirmation for a rental car. It has a "confirmation number" and is therefore identified as a "thing").

In both cases this is somewhat subjective. There is a fancy word for treating a relationship as an entity. It's called "reification".

At a lower level, many database designers (particularly ones who deal with SQL server) create an autogenerated first column called "ID", and make that the primary key for every last table. I think that's a mistake, but that's another discussion.

The word "entity" suffers from the "everything is a thing" syndrome. Everything is a thing. Everything is an object. Everything is an entity. Well, duh.

While I'm on this subject, after I read your account of the origin of the term "entity integrity" (for which thanks, by the way) it occurred to me that perhaps my reading of the word "entity" in that phrase has been wrong all these years. When I hear "entity" I always think "subject matter entity", unless the context makes me think otherwise. But maybe the entity wohse integrity is being protected by the entity integrity rule is a table, and not a subject matter entity.

This would sort of make sense, because it would put entity integrity on the same level of abstraction as referential integrity. A table with no primary key might be a bag of rows, and not a set of rows. If a row has a NULL in a primary key column, the test for whether rows are unique might return "unknown", meaning that we don't know whether we're dealing with a set or a bag. If we're trying to build a database that conforms to the relational model, it's important to rule out bags of rows, where sets of rows are called for.

Note that for people who model in terms of relations, and not tables, this becomes a moot point. Perhaps that is why your original post didn't get a better response here in c.d.t. Received on Tue Aug 04 2009 - 10:52:28 CEST