Re: Identifying an Identifying Relationship

My confusion might stem on my reluctance to depend on tools such as ERWIN for modeling and design purposes, so any clarification would be appreciated.

Concatenation of primary keys? Concatenation of any type of candidate key for identifying relationships, whether user-defined or system generated, is a new one on me. Does this mean that in what is termed a "parent-child" relationship, or even in an association relation for that matter, system-generated keys are concatenated together in a single attribute for the child relation?

From a pure relational standpoint, this doesn't seem to make a lot of sense since 1) domain integrity is probably violated (i.e. the domain of a single system-generated unique value is doubled in width and therefore probably violated), 2) repeating values are inherent in the single attribute that represents the new "concatenated" key leading to an automatic denormalized state and a likelihood that the ERWIN generated table doesn't even meet the definition of a relation at all, and 3) some sort of automated decoupling mechanism will have to exist to support referential integrity and allow data access languages to traverse across joins, etc.

TIA, Daniel Guntermann

"Jan Hidders" <hidders_at_REMOVE.THIS.win.tue.nl> wrote in message news:9mjg11$7a7$1_at_news.tue.nl...
> John Dilley wrote:
> > In my database designs, I almost exclusively use surrogate keys. Over
> > the years I have found NO real-world data that never changes.
>
> Ok. But don't forget that next to the surrogate key there should at
> least be one other real key.
>
> > Using Erwin to design a database, I am seing these surrogate keys
> > being connected together with other surrogates to make unbelievably
> > long primary keys. Research showed me that this was being caused
> > because I was using identifying relationships. I then went on a search
> > for a definition of what an identifying relationship was, and when to
> > use them.
> >
> > The common definition was that the relationship was an identifying
> > relationship if the child record could not exist independant of the
> > parent record. An example was given of a vehicle/owners relationship,
> > where a vehicle could not be entered unless there was an owner.
>
> That is actually not a very good example. A relationship between A and
> B is only an identifying relationship if two (!) things hold:
> 1. Bs cannot exist without an associated A
> 2. Bs can only be identified through their relationship with As
>
> The second requirement means that the key of a B contains the key of A.
> Note that this is something that should hold in the world you are
> modeling. If it does not hold there, then the relationship is not an
> identifying relationship. So if you can identify cars without refering
> to their owner, then the relationship is simply not an identifying
> relationship. A better example is the kidneys you mentioned; usually
> you can only identify them as "that person's left kidney". :-) Another
> example would be a paragraph in a text that you can only identify by
> saying something like "the first paragraph of the second chapter of
> this book".
>
> > Does the concatenation of keys still make sense when surrogate keys
> > are being exclusively used (except for many/many join tables)?
>
> No, never, because then every entity can be identified by its own
> surrogate key, so the second requirement is never fulfilled.
>
> > But I would like to understand.
>
> The reason that "identifying relationships" have to be indicated is
> that sometimes the own attributes of an entity are simply not enough to
> identify the entity. In that case the "key" in some sense consists of
> some attributes plus a relationship, so you should indicate which
> relationship that is. And that is why you sometimes need to indicate if
> a relationship is an identyfying relationship.
>
> So, if you use surrogate keys then an entity can always be
> identified by its own attributes, so there is then no need for
> identifying relationships.
>
> --
> Jan Hidders
Received on Thu Aug 30 2001 - 06:25:33 CEST