Re: What is that "more" that makes E-R model truly independent ?

From: David Cressey <>
Date: Tue, 24 Jul 2007 11:22:22 GMT
Message-ID: <Ohlpi.2684$0v4.934_at_trndny01>

"beginner16" <> wrote in message
> hello
> 1)
> Before I ask the question I must point out that I understand the
> difference between logical and conceptual level. Thus, conceptual
> model represents DB design independently of the underlying logical and
> physical structure. I also realise that hierarchical and relational
> models are at logical level!
> I just started learning a little about E-R model. I realize the E-R
> model was created so that we can represent DB at the conceptual level,
> and as such DB is presented independently of the underlying logical
> DB design.
> Say we are trying to create DB for particular organization. At
> conceptual level E-R model for this DB would be the same no matter if
> logical implementation of this DB will be hierarchical or relational
> ( I realize that this is probably not always true )
> We could implement same graphical symbols ( let us called this set of
> symbols with "S" ) to represent objects, relationships etc in both
> relational and hierarchical models, but when trying to represent DB at
> conceptual level using these symbols, ( I assume ) the two diagrams
> ( hierarchical and relational ) would still be very different, even if
> both relational and hierarchical models would use same graphic
> symbols?!
> But as I stated before, E-R model diagram would be the same no matter
> if logical DB implementation is hierarchical or relational. For that
> reason I assume that ER modeling is more than just using symbols for
> entity, attributes, relationships ( since relational and hierarchical
> models use same "fictional" graphical symbols and still their diagram
> representations of DB at conceptual level differs ). But what is that
> "more" that makes E-R model truly independent of logical DB
> implementation ?
> 2)
> Does relational model also have its own graphical symbols defined
> ( for graphical representation ) or do we always need "outside"
> diagrams ( E-R model, Bachman's model etc ) in order represent it
> graphically?
> thank you
> cheers
My insight into what makes E-R valuable comes, in part, from some experiences dating back to the 1990s. In one case, I was working with the local data modeller, and he had taken a huge enterprise wide database built for VAX DBMS and abstracted it out to an E-R model (including, but not limited to, a diagram). when the time came to design and build a reporting database in Oracle that used some data from an Rdb database, but which described the same underlying enterprise as the old database, the data analysis phase of the project was essentially completed in an afternoon, by taking the suubset of the E-R model relevant to the subject at hand.

For your background, VAX DBMS was (and still is) a CODASYL database product. CODASYL (or network) databases are like hierarchical except that a record can belong to more than one set.

Anyway, the advantage that E-R offers over some alternatives is that it is less biased toward one data model or another. It's easy to derive a relational model from an E-R model. It's also fairly easy to derive a hierarchical model, a network model, or even an object oriented model from the same starting place. This lack of bias is, from my experience, very important. It helps in the necessary discipline of keeping design distinct from analysis.

As to whether relational has its own diagramming convention, the answer is decidedly yes. There are even some tools out there that will manage an E-R model and an equivalent relational model in parallel. One such tool is Data Architect from Sybase.

The two biggest differences between an E-R diagram and a relational diagram have to do with many-to-many relationships and with foreign keys.

An E-R model does not have foreign keys installed. Relationships are diagrammed by lines between boxes, but they are not implemented via foreign keys. This help keep the diagram unbiased, because there are other ways, besides foreign keys of implementing relationships. In a relational model, foreign keys are used to implement relationships. (some modellers deviate from what I'm saying here, but this is the formal description)

An E-R diagram can connect two boxes (entities) with a single line (relationship) and indicate by symbols at either end of the line that the relationship is many to many. An example would be an enrollment system, where many students can be enrolled in one course, and each student is enrolled in several courses. In a relational model, an intermediate box (sometimes called a "junction box") will be placed in the middle of the line joining two boxes in a many to many relationship. Two foreign keys will be placed inside the box. (Actually, I've described only binary relationships. The concept can be extended to ternary relationships, etc.) Again, some modellers follow the relational conventions when building an E-R model but that is not formally correct.

Here are two places you can go to pick up some diagrams, and some description of what this subject is all about: Received on Tue Jul 24 2007 - 13:22:22 CEST

Original text of this message