Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: Multiplicity, Change and MV

Re: Multiplicity, Change and MV

From: Jay Dee <>
Date: Thu, 13 Apr 2006 01:16:14 GMT
Message-ID: <yDh%f.92808$>

Neo wrote:

>>This difficulty has been long recognized as a consequence of the
>>EAR approach to database design.  Since Chen's first paper, it's
>>been apparent that neither "entity" nor "relationship" were
>>fundamental concepts.

> I have been referring to that fundamental concept as a "thing" in the
> most general sense (which has provided c.d.t. members with an endless
> sources of ridicule for years).
>>The most resilient designs are those which accurately represent
>>data, not relationships.  In that vein, I've found Halpern's ORM
>>approach to be helpful -- although his goal of discovering objects
>>is, I feel, slightly off the mark.

> Would you (or someone) be willing to demonstrate this resiliency
> starting with the OP's simple example and then extending a bit further?

I should probably make clear that "entity" as quoted above is not a fundamental concept in EAR. Real-world entities are the "things" we want to keep track of, presumably because there is some value in doing so. (Why else would the effort be worthwhile?) Acknowledging that databases can, at best, record only representations of some of the characteristics of real things, we must be extremely careful that our designs don't allow us to record misrepresentations of those "things" as they exist in the real world. (In other words, it's not necessary that we make or enforce any sort of real-world rule like, for example, "there can never be fewer than zero widgets stored in the warehouse" because, in the real world, such a circumstance could never occur.)

This, I feel, is the EAR's greatest shortcoming: it's impossible to capture all the constraints necessary to ensure that a design is a satisfactory representation of the real world as relationships.

I've found that studying the significant events which affect "things" is invaluable in understanding what must be recorded and how recording those events may transform a "thing" into some different "thing." This is key: the ubiquitous UPDATE is not sufficient to handle many realworld  transactions. The succession of events involving "things" require DELETEs and INSERTs much more often than most designers realize. Failure to recognize that leads to adding attributes to entities so that some of the entities are recognizable as different from other entities stored in the same table.

 From glancing at some of your other posts, I think you're on the right track. But the question about the values in a domain ("you are now told by the customer that attrib1 can have any number of values") seems to be type related; I'm not sure that "the proper method of allowing an attribute to have multiple values is to have a separate table" is true -- although I acknowledge that many 'data-driven' designs adhere to that approach and litter the design with all sorts of 'reference tables' or 'authority tables' that result in consequent 'relationships' which are 'enforced with foreign key constraints.' So many, in fact, that such 'relationships' are frequently omitted from graphical depictions of EAR designs because they result in too much clutter.

DBMS support for arbitrary types is miserable - and SQL products that toss NULLs into the mix don't help in the least. The failure to properly handle attribute types propelled much of the move to other database models. Too bad. Because those shortcomings have nothing to do with the relational model. In fact, I suspect that what you think are shortcomings in the relational model are, in fact, defects in various SQLDBMS products that the vendor claims are relational.

The fault isn't yours: the vendors call their products relational and cite features like support for "primary keys" and "foreign keys" to prove their claims. But they're not relational database products, they're SQL database products.

Regarding the OP's question: he seems to be asking, "Why must I change my design when one of the constraints which resulted in a 5NF design is removed?" Obviously, and not tritely, it's because the rules changed. (Remember what I said about careful design and avoiding misrepresentations?) Granted, the example stepped into the realm where functional dependency won't yield a satisfactory design, but careful and complete analysis of the real world entities would not have resulted in a design that relies upon the presumption that Tom will never teach French because he teaches English. Received on Wed Apr 12 2006 - 20:16:14 CDT

Original text of this message