Re: erd to db

I would like to reply to the statement that an relationship may not have a primary key.

"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.

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.

Apart from the questions whether this modelling (a weak key on a relationship)  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.

However a "clean" approach would model three N:1 relations between BOOKING and ADRESS which is (IMHO) not such straightforward. Moreover what about the case if the domain of TYPE is not an enumeration  as above: you would have an unlimited number of relationships which can not be modelled.

Another approach would be to design ROLE as an entity from the beginning ... however, it remains to be an relation ...

What do you think?

Thank you. - Michael Received on Mon Mar 25 2002 - 13:45:30 CET