Oracle Database Tips by Donald Burleson

Entity Relation Modeling

Data modeling is a method for developing the structure of the database.  It uses various tools to define the data, relationships, semantics and data consistency requirements.  The Entity Relation Diagram (ERD) is where most designers start when creating and normalizing a database.  An ERD normally starts on paper with a general layout of the database data entities and their relationships. 

The entity/relation model was first introduced by Professor Emeritus Peter Chen from the University of Louisiana, and it is sometimes called a Chen diagram. In the 25 years since the introduction of this model, many permutations have been created, but the basic principles of entity/relations modeling remain intact.

While an E/R model may be free of redundant information, it is impossible to implement the model in a relational database without introducing redundancy to support the data relationships. For example, if a data model was implemented using a pointer-based DBMS, such as IMS, pointers would be used to establish relationships between entities.

At some point, the diagram becomes unwieldy on paper and an ERD tool is used to graphically finish the design.  In a nutshell, the ER diagram shows the data tables, and more importantly, it shows the one-to-many and many-to-many data relationships:

Why start on paper?  Because an ERD is so simple that in the early stages as the design progresses, it is easier to just redraw it by hand.  In the beginning, we are not concerned with details of a relationship just that a relationship exists.  For example, we are not concerned with what the relationship is between an employee and a department, just that there is a relationship and that it is a two-way relationship.  As the model continues to be defined, those relationships become more defined. 

One key point to remember is that a relationship must be defined in both directions.  A department may have one or more employees, but an employee will have zero or one and only one department.  Both rules define a relationship between an employee entity and a department entity, but they differ significantly depending on which direction you are discussing.  Business rules eventually will make their way into the later stages of the ERD.  First, let's quickly define the parts of the ERD.

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.