Re: Database design, Keys and some other things

vldm10 wrote:
> >
> > 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 one car changed color (or any attribute) and had same color
> (attribute value) in two different periods this key doesn't work.

I said the key has AT LEAST these two.

> You
> need to add date or some status or anithing else.

I indicated that above with the date range or combination of date & status.

> >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
>
>
> This is the way how people usually practically work, but there are some
> things which are not defined in the theory. In the RM and the ER
> Conceptual Model there are no events.

I agree it is useful to identify what prompts changes to the database "facts" and when. In the RM nothing is identified as a "master file" or a "code file" or a "history file" or a "transaction file" as was common when designing indexed sequential files in years gone by. That is not so much a gap in the theory as something a relational theorist might consider "orthogonal" to the RM.

However, the extensions that Date & company are developing (of which I am ignorant) to address time in a different way suggest (again, I'm guessing) that they have identified a reason to think not only in terms of a set of facts as they stand, but facts as they change over time. So I will agree with you that the identification of events and change over time is a good thing. I haven't found your solution to be enticing, however.

> You mentioned Event table, but
> what is event. We know that event is one of the most general concepts
> in the Real World.
> The second think, that we use often is the date or the time in the
> construction of an entity and a relationship. Can a date be an
> attribute of an entity? Can for example a date be an attribute of some
> person?

A date can be an attribute of a "transaction" or event. I agree this is important and not handled well today. Time & date "stamping" of data (e.g. startingDate, endingDate, deceasedDate) have handled the most basic needs, but these are often properties of attribute values (ColorStatusChangeDate) or weak entities, not properties of strong entities.

> I don't believe. Not to mention that time or date for some
> attribute's value can has three different values in the Real World,
> the Conceptual Model and the Logical Model respectively. I defined a
> date as a part of a knowledge related to some data, so it is not an
> attribute.

OK, so you have a means of getting at the properties of attributes? You have a hierarchy of entity > attribute > property? What does a query look like? XML has entities & attributes with nesting that often causes us to look at RM entities as container entities and attributes as child entities, then use the xml attributes of an xml entity for these properties. That can be useful, but it often results in a confusion between sub-entities & attributes and makes for a language like XQuery (not exactly lean & mean).

> Now I have a problem with the assumption that date is not
> attribute, I can't use date as key (or as a part of the key) because
> in the key definition (for RM) states that the key is an attribute or
> set of the attributes. It is similarly with things like a status, an
> indicator, etc. I think that these are limitation in the RM.

OK, I'll buy that -- it is a limitation of the RM that you cannot model data with entities, properties of entities, properties of values of properties, properties of properties of values of properties, ... using such a hierarchy of values.

But simplifying this complexity into entities & attributes is also part of the charm, allowing for simplicity in querying, sort-a, kind-a. We could simply further so there is only one "thing" (entity, for example) and relationships between this thing and other things. Some products do this. Both too much simplification and too little simplification cause problems.

> I also
> think that the Relational Model is the best database model,

I don't, but I do think it is the best laid out and best taught.

> but there
> are things which are not cavered with the theory. (Of course this is
> just one opinion)

I'm trying to get us to move beyond the RM myself, so I want to agree with your questions & answers, but it isn't resonating yet. In particular, I really don't like the confusion between modeling an entity and modeling the relationship between that entity and some property of that entity. Keep trying and maybe it will click. Cheers!  --dawn

> Vladimir Odrljin
Received on Mon Sep 26 2005 - 16:06:19 CEST