Database Management Systems II - Prof. Holowczak

Zicklin School of Business - Baruch College
City University of New York

Data Warehousing and Analytics

Entity Relationship Modeling and Normalization

What you'll learn in this set of notes

Introduction to the course

Background Material

You may wish to briefly review Chapters 1,2,3 and 9 through 14 in the Connolly/Begg Database Systems 5th Edition textbook (Chapters 1 through 5 in the Elmasri/Navathe book) to refresh your memory of basic database concepts. You are responsible for this material.

Our goal is to provide information for the purposes of decision making in business. The database is one way to provide such information.

However, we must organize and develop a database in a structured fashion to meet the needs of an organization and reflect the way it operates.

Recall the database design process we follow:

  1. Gather user/business requirements.
  2. Develop the Entity Relationship (E-R) Model (shown as an E-R Diagram) based on the user/business requirements. The E-R model is the conceptual model of the database.
  3. Convert the E-R Model to a set of relations in the relational model. We call this the Logical model
  4. Normalize the relations to remove any anomalies.
  5. Implement the database schema by creating a table for each normalized relation. We call this the Physical model
  6. Develop applications (forms, reports, queries, scripts, procedures) that work with these tables.

Note that in the second step, we may also develop additional models such as data flow and functional models.

Review of Entity Relationship Model

Elmasri/Navathe 3rd ed. Connolly/Begg 5th ed.
Chapters 3 and 4 2, 9, 10, 11, 12

E-R Modeling Constructs

E-R Diagrams

Chen notation as used in the Elmasri/Navathe Textbook

  • Relationship Name: Displayed just inside the relationship diamond.
  • Degree: Shown by line segments between the relationship diamond and 2 or more entities.
  • Cardinality: Displayed between the participating entity and the relationship diamond next to the relationship line. Split up the cardinality.
  • Optionality: Mandatory participation indicated by double relationship line
    Optional participation indicated by a single relationship line.

This is also called the "Chen notation" after the author who first proposed it.

Oracle Designer CASE Notation

[Oracle CASE Example ER Diagram]

  • Also called the "Information Engineering" or "Crow's Foot" notataion.
  • In Oracle Corporation's Designer, relationships are expressed in a rigid sentence format. For example: An ORDER must be placed by one and only one CUSTOMER.
    The "be" is mandatory making the verb difficult to get right.
  • Relationship diamonds are not used.
  • Relationship Names: Are expressed as a verb phrase starting with "be".
    There are two phrases, one for each direction of the relationship.
    This phrase is then written along the line segments for the relationship.
  • Degree: Shown by line segments between any two entities. As such, ternary (3 way) relationships as described in the Kronke and Elmasri/Navathe textbooks (Chen notation) can not exist.

  • Cardinality: Single participation ("1" in the previous example) is indicated by a single line segment.
    Multiple participation ("N") is indicated by crow's feet

  • Optionality: Mandatory participation is indicated by a solid relationship line segment.
    Optional participation is indicated by a dotted line segment.
  • One ORDER must be placed by one and only one CUSTOMER.
  • One CUSTOMER may be placing zero or more ORDERS.
  • One ORDER may be made up of zero or more ITEMS.
  • One ITEM must be an item on one and only one ORDER.

There are a set of tools within the Oracle CASE environment that can print these "relationship sentences".

Visible Analyst Notation

  • Visible Analyst Workbench (VAW) uses the rounded box to show an Attributive Entity - one that depends on the existence of a fundamental entity (noted by just the rectangle).
  • The relationships use the following symbols:
    • For cardinality, the crow's feet are used to show a "Many" side of a relationship.
    • A single line indicates a "One" side of the relationship.
    • Optional participation is shown with an open circle. Thus in the above diagram, a Customer May place one or more Orders.
    • Mandatory participation is shown with two hash marks. Thus in the above diagram, an Order Must be placed by one and only one Customer.

Popkin System Architect - Crow's Feet Notation

  • Two hash marks indicates mandatory participation
  • Hollow circle indicates optional participation
  • Crows-foot indicates Many while single line indicates One
  • Dashed line indicates non-associative relationship
  • Solid line indicates associative relationship (e.g., that the Identifier of OrderHeader should be part of the composite identifier of Order Items)

Popkin System Architect - UML Notation

  • The Unified Modeling Language (UML) has gained in popularity
  • UML draws upon several different Object Oriented modeling techniques developed in the 1980's and 1990's.
  • Learn more at

  • In UML notation, entites are refered to as classes or object types and are represented as rectangles.
  • Note the naming convension used: Customer is a member of the class Customers
  • Associations (relationships) can be binary, ternary or higher (n-ary
  • Associations are shown by connecting lines with a single term as a label
  • Cardinalities can be displayed (as above) e.g., 1 indicates one and only one
    0..* indicates a minimum of 0 and a maximum of Many

  • Note that only relationships of degree 1 and degree 2 are supported in UML (no ternary relationships)

  • The Connolly/Begg textbook 5th edition now uses UML throughout.

Displaying Attributes

Weak Entities and ID Dependent Entities

Subtype Entities

Subtype Entity Notations

Review of the Relational Model

Entity to Relation Conversion

Representing Relationships

Recursive Relationships

Ternary Relationships

IS-A Relationships

File: er_index.html Date: 12:06 PM 8/28/2013
All materials Copyright, 1997-2013 Richard Holowczak