Re: Database design, Keys and some other things

vldm10 wrote:
> dawn wrote:
> > vldm10 wrote:
> > > Here is a simple example about two entities and one relationship - Car,
> > > Person and Owner. It tries to describe more realistically Real World
> > > situations. A car had its color changed twice. In the Real World
> > > attributes are often changed. In another entity, the person Mary
> > > changed her last name because she got married. In the relationship
> > > Owner it can happen that one person buys the same car twice during a
> > > period of time. It can also happen that two entities can be in the same
> > > relationship many times. So,I believe that this new approach is more
> > > appropriate.
> > >
> > > Given the table Car :
> > > CarKey CarID Make Color ...
> > > ______________________________________________________
> > > ...
> > > 23 vin1 Buick silver ...
> > > 24 vin1 Buick blue ...
> > > 25 vin1 Buick red ...
> > > 26 vin2 Honda silver ...
> > > 27 vin3 Ford black ...
> > > ...
> >
> > You named this table "Car" which might suggest that a row in this table
> > is-a car and that no two rows are the same car. That is not the case
> > with your table here. If you are going to model Cars, then model Cars.
> >
> > > In the Car table VIN = Vehicle Identification Number
> >
> > That sounds like a candidate key for a Car table.
> >
> > > Now, let Person be the following table:
> > > PersonKey PersonID PersonName ...
> > > _______________________________________________________
> > > ...
> > > 208 ssn1 Mary Jones ...
> > > 209 ssn1 Mary Adams ...
> > > 210 ssn2 John Stewart ...
> > > ...
> > >
> > > In the Person table SSN = Social Security Number
> >
> > That sounds like a candidate key for a table of USA people. Yet, you
> > have multiple rows for the same person. If you are going to model a
> > Person, then model a Person.

>

If you are using the RM as typically taught rather than the RM that includes nested relations, then along with a PaintColor table, you would have a relationship table between PaintColor and Car, perhaps named CarColor. That table could be seen as an event table with paintDate and colorReplacedDate or paintDate and paintStatus (a date range or a combination of date and status).

So the Car table includes facts about Cars and the relationship table CarColor includes facts about the car colors. The relationship table has a candidate key that consists at least of the Car ID and the Color ID. If you want it to be more activity/event based about when the car was painted, then it would also include a starting date. Otherwise a compound candidate key of car id and color id might work. Make sense?

> 2. Let SSN be the primary key for the table Person. How we can enter
> information in the
> table Person that Mary Adams changed her name again to Mary Jones
> - she got divorced.
> These two questions show one limitation in the RM.

This is pretty much the same question, so you can use a similar approach. This does not show a limitation in the RM, although we might way that it shows a human-computer-interaction issue. --dawn Received on Sun Sep 25 2005 - 18:01:15 CEST