Data Modeling - Entity Relationship Diagram (ERD)

Chapter 5

Entity Relationship (ER) Model

• forms the basis of an ER diagram

• ERD represents conceptual database as viewed by end user

• ERD shows the information that is created, stored, and used by a business system

• ERDs depict database’s main components:

  • Entities

  • Attributes

  • Relationships

Entities

• the basic building block for a data model.

• It is a person, place, event, or thing about which data is collected—for example, an employee, an order, or a product.

• Refers to entity set and not to single entity occurrence

• Corresponds to table and not to row in relational environment

• In both Chen and Crow’s Foot models, entity is represented by rectangle containing entity’s name

• Entity name, a noun, is usually written in capital letters

Attributes

• Some type of information that is captured about an entity. 

• It is also referred to as characteristics of entities

• In Chen model, attributes are represented by oval and are connected to entity rectangle with a line

• Each oval contains the name of attributes it represents

• In Crow’s Foot model, attributes are written in attribute box below entity rectangle on the data model

Identifiers (Primary Keys)

One or more attributes can serve as the identifier—the attribute(s) that can uniquely identify one instance of an entity—and the attributes that serve as the identifier are noted by an asterisk or has an underlined next to the attribute name.

Key attributes are also underlined in frequently used table structure shorthand

Composite Primary Keys

• Primary keys ideally composed of only single attribute

• Possible to use a composite key

  • Primary key composed of more than one attribute

Relationships

• Association between entities they are shown by lines that connect the entities together.

• Every relationship has a parent entity and a child entity, the parent being the first entity in the relationship, and the child being the second.

• Participants are entities that participate in a relationship

• Relationships between entities always operate in both directions

• Relationship can be classified as 1:M

• Relationship classification is difficult to establish if know only one side of the relationship

• Relationships should be clearly labeled with active verbs so that the connections between entities can be understood.

• If one verb is given to each relationship, it is read in two directions.

Connectivity

Used to describe the relationship classification

Cardinality

It is the ratio of parent instances to child instances

Expresses minimum and maximum number of entity occurrences associated with one occurrence of related entity

Guide Question: “How many instances of one entity are associated with an instance of the other?”

Relationship Participation (continued)

Relationship Degree

Indicates number of entities or participants associated with a relationship

Unary Relationship

Association is maintained within single entity

Binary Relationship

Two entities are associated

Ternary Relationship

Three entities are associated

Three Types of Relationship degree Illustrated

Iterative process

Based on repetition of processes and procedures

Data Dictionary

is a main method for analyzing the data flows and data stores of data-oriented systems.

is a reference work of data about data (metadata).

lt collects, coordinates, and confirms what a specific data term means to different people in the organization

Reasons for Data Dictionary

• The data dictionary may be used for the following reasons:

  • Provide documentation.

  • Eliminate redundancy.

  • Validate the data flow diagram.

  • Provide a starting point for developing screens and reports.

  • To develop the logic for DFD processes.

Data Dictionary Contents

• Data flow.

• Data structures.

• Elements.

• Data stores.

Defining Data Flow

Each data flow should be defined with descriptive information and its composite structure or elements.

• Include the following information:

  • ID - identification number.

  • Label, the text that should appear on the diagram.

  • A general description of the data flow.

  • The source of the data flow

  • This could be an external entity, a process, or a data flow coming from a data store.

  • The destination of the data flow

  • Type of data flow, either:

  • A record entering or leaving a file.

  • Containing a report, form, or screen.

  • Internal - used between processes.

  • The name of the data structure or elements

  • The volume per unit time

    • This could be records per day or any other unit of time.

  • An area for further comments and notations about the data flow

Data Structures

Are a group of smaller structures and elements.

An algebraic notation is used to represent these.

• The symbols used are:

  • Equal sign, meaning “consists of”.

  • Plus sign, meaning “and”.

  • Braces {} meaning repetitive elements, a repeating element or group of elements.

  • Brackets [] for an either/or situation.

  • The elements listed inside are mutually exclusive.

  • Parentheses () for an optional element.

Structural Records

•  consist of elements or smaller structural records.

• These are a group of fields, such as:

  • Customer Name.

  • Address.

  • Telephone.

• Each of these must be further defined until only elements remain.

Data Elements

• should be defined with descriptive information, length and type of data information, validation criteria, and default values.

• Input and Output formats should be included, using coding symbols:

  • Z — Zero Suppress

  • 9 — Number

  • X — Character

  • X(8) — 8 Characters

  • . , — Comma, Decimal Point, Hyphen

  • These may translate into masks used to define database fields