Re: erd to db

From: Michael Gruebsch <michael.gruebsch_at_pentaprisma.com>
Date: Mon, 25 Mar 2002 16:59:49 +0100
Message-ID: <a7nhlg$mn178$1@ID-71391.news.dfncis.de>

Thanks Jan, for answering. I'm afraid I was not clear. I meant the following constallation

  [BOOKING] -- (N) -- <ROLE> -- (M) -- [ADDRESS]

where the relationship <ROLE> have an attribute (TYPE). Both entities BOOKING and ADRESS are strong entities having both a primary key consisting of an attribute (IDENT) for the booking number or customer id resp.

Transforming this into relational schema <ROLE> can be modelled by an entity [ROLE] having a (primary) key consisting of the attributes (BOOKING.IDENT, ADDRESS.IDENT) at least.

The question is: How can I model the ERM that the transformation into the RS includes (TYPE) into the primary key of [ROLE]? There should be a notion of a key for a relationship ... iff "multi-valued" relationships are allowed.

Thanks. - Michael

"Jan Hidders" <hidders_at_uia.ua.ac.be> schrieb im Newsbeitrag news:3c9f260d$1_at_news.uia.ac.be...
> "Michael Gruebsch" <michael.gruebsch_at_pentaprisma.com> wrote in message
> news:a7n695$mgkt6$1_at_ID-71391.news.dfncis.de...
> > "Jan Hidders" <hidders_at_uia.ua.ac.be> wrote in
> > news:3c9af4b6$1_at_news.uia.ac.be...
> > > ... and second, there can be no such
> > > thing as the primary key of a relationship other than that which is
> > > implied
> > > by the involved entity types ...
> >
> > What about the following example of a travel agency for disabled
persons.
> >
> > * there is an entity of addresses
> > * there is an entity of bookings belonging to a certain journey
> > * for each booking you can specify up to three addresses playing
different
> > roles:
> > participant, person in charge, contact (e.g.: Mr X [participiant] is
> > accompanied
> > on that journey by Mr Y while the contact adress is that of Mr X's
> > parents)
> >
> > I have modelled this in ERM as the entity BOOKING, the entity ADRESS
> > and a N:M relationship ROLE which has an attribute TYPE defined as
> > either being 'participiant', 'person in charge' or 'contact'. To make
sure
> > that for each TYPE there is only one pair (BOOKING, ADRESS) the
> > TYPE attribute is defined as (part of a week) key for that relationship.

>

> No problem sofar. :-)

>
> > Transforming this into the relational model one gets an entity ROLE
whose
> > primary key consists of the foreign keys of ADRESS and BOOKING and
> > the attribute TYPE. By this the above business rule can be ensured.
>
> But what is the primary key of the table for ADDRESS? Since you said it
was
> a weak entity type it doesn't have enough attributes by itself to form a
> primary key. So you should take the union of the incomplete local key (the
> attributes that almost identify it :-)) and the the primary keys of the
> tables for the enitity types on which the weak entity depends. In this
case
> this means that the primary key for ADDRESS becomes the same as that of
> ROLE.

>
> > Apart from the questions whether this modelling (a weak key on a
relation-
> > ship) is usual or whether it does make sense I see the following point:
> >
> > Having a (weak) key on a relationship implies that for that relationship
> > each pair of the associated entities can occure more than once ... that
> > is actually you have a "multi-valued" relationship what might not be
well
> > defined in the relational model.

>

> I'm not sure I see the problem here as long as you get the primary key of
> ADDRESS right.

>

> Kind regards,

>

> -- Jan Hidders

>
> Received on Mon Mar 25 2002 - 09:59:49 CST