Re: Generalizations

From: David Cressey <>
Date: Sun, 23 Mar 2008 10:45:00 GMT
Message-ID: <MwqFj.1476$vD1.493_at_trndny09>

"shane" <> wrote in message news:fs4hrj$7tc$
> I hope it is ok to ask this here.
> Im having trouble with ERD's.
> I have been given a definition on which to draw an ERD. I did so, and
> transforming the entities and relationships to relations using the
> steps
> Step 1) Transform entities
> Step 2) Transform weak entities
> Step 3) Transform Relationships
> Step 4) Transform Generalizations
> Step 5) Simplify.
> When I came upon step 4 I realised I had two opportunities to generalize.
> Doctor and a Patient (Both with SSN as their primary keys) could both be
> specializations of a 'People' entity.
> Likewise Pharmacy and Pharmaceutical company are both specializations of
> a 'company' entity.
> I dont understand why we have the generalizations. It seems to me that we
> just creating a relation for the sake of it.
> Could someone put me onto the right track?
> --

The following might be a little too vague for what you want.

The "generalization/specialization" pattern, like many tools and techniques in information science, is not strictly necessary. It is possible to model Doctors and patients without ever making explicit the fact that they are both "people". Just about everything you need to do with the data can still be done.

In such a model, if a curcumstance were to arise where a given person were both a doctor and a patient in the same database, the fact that a givven SSN might identify a doctor in one context, and a patient in another context, would be "obvious" to the people working with the data with an understanding of the subject matter. It would be pretty opaque to the people working with the data, but with no understanding of the subject matter. That might or might not be ok.

As to whether generalizations are useful or not, that's another story. There are many situations where realizing, for instance, that autos and trucks (lorries?) are both "vehicles" can help the database designer, the programmer, the interactive user, and the user of reports or other extracts. It makes things easier, in a lot of situations. Having said that, I'm quick to add that it's not the silver bullet of data modeling.

The way I learned modeling (which might not be the most usual way) gen-spec is used for modeling at the ERD level. That is, it forms part of the conceptual data model. The question might arise about what happens when one designs a relational schema to conform to the conceptual model, when the conceptual model contans a gen-spec pattern.

As you stated, the most mechanical transformation of ERD into relational schema will result in an extra relation for the generalization. The question might be asked, "is this good design or bad design?" I think this is what you are really asking. My answer is, "it depends".

And I would suggest that the purely mechanical transformation from ERD to relational schema masks the fact that some real design work is going on when this transformation is done. The fact that you can do it without thinking doesn't mean that you should do it without thinking. It seems to me that in some situations, the "extra" relation is worth its cost. In others, it isn't. You will undoubtedly get other insights from other people regarding this.

Not that, if someone is exposed only to the relational schema, it might take a little deduction on that person's part to discover that the relations were intended to mimic a gen-spec pattern. Deducing design intent from implementation is another whole discipline in the general field of information systems. Received on Sun Mar 23 2008 - 11:45:00 CET

Original text of this message