Re: erd to db

"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