Entity-Relationship (E-R) Modeling
Peter Chen introduced the E-R model in 1976
CSC3064
Database Engineering
Has many variations
Lecture 2 - ERD
Entity-Relationship (E-R) Modeling
Depicting Entities and Attributes
Entity-Relationship (E-R) Diagram
A detailed, logical representation of the entities,
associations and data elements for an organization
or business
Notation uses three main constructs
Data entities
Relationships
Attributes
Entity
Entity
A person, place, object, event or concept in the user
environment about which the organization wishes to
maintain data
Represented by a rectangle in E-R diagrams
Naming Conventions should follow an established
standard
E.g. capitalize the names of entities
Attribute
Attribute
also termed as Properties
they are characteristics of an entity
Can be included as named bubbles
attached to the entity
multi-valued attributes are often shown in
a diagram by the use of a double circle
multi-valued attributes may have multiple
values
7
Primary Key Attribute
Multi-valued Attributes
denoted by underlining an attribute
usually a single attribute
may be a comprised of a concatenation of attributes
primary key selection criteria
During database modeling, it is often desirable to
decompose this situation into two separate entities
since a multi-valued attribute often results when we
capture only a single data attribute of what would
otherwise be modeled as an entity.
Key should not change its value over the life of each entity
instance.
Key should be guaranteed to not be NULL.
Avoid intelligent-keys (stored data as part of a key such as a
code).
Substitute single-attribute surrogate keys for large composite
keys.
Multi-valued Attributes
10
Relationship
An association between the instances of
one or more entity types that is of interest
to the organization
Association indicates that an event has
occurred or that there is a natural link
between entity types
Relationships are always labeled with verb
phrases
11
12
Naming and Defining Relationships
Naming and Defining Relationships
Guidelines for defining relationships
Explain any restrictions on participation in the
relationship
Explain extent of the history that is kept in the
relationship
Explain whether an entity instance involved in a
relationship instance can transfer participation to
another relationship instance
Relationship name is a verb phrase
Avoid vague names
Guidelines for defining relationships
Definition explains what action is being taken and why
it is important
Give examples to clarify the action
Optional participation should be explained
Explain reasons for any explicit maximum cardinality
10.14
13
14
Example relationships of different degrees
Degree of Relationship
Degree
Number of entity types that participate in a relationship
Three cases
Unary
A relationship between two instances of one entity type
Binary
A relationship between the instances of two entity
types
Ternary
A simultaneous relationship among the instances of
three entity types
Not the same as three binary relationships
15
16
Binary Relationship
The two models
Chen Model
1 to represent one.
M to represent many
involves two entities.
degree is equal to 2 because two entities
participate in the relationship
Crows Foot
One
Mandatory one , means (1,1)
many
One or many
17
18
Ternary Relationships
Unary Relationships
association between occurrences of three entities at
the same time
represents an association between occurrences of a
single entity.
19
20
Cardinality
Weak Entity
an occurrence of one entity cannot exist unless
Maximum cardinality
there is an occurrence of a related entity.
expresses the number of occurrences of one entity related
to another entity.
E.g. 1:1, 1:M, M:N
A weak entity set is indicated by a doubly-outlined
box.
Minimum cardinality
expresses the number of occurrences of one entity related
to another entity.
expresses how many occurrences of one entity must, at a
minimum, be associated with occurrences of another
entity
21
Examples
22
Examples cont
23
24
Example COMPANY Database
Example COMPANY Database (Contd.)
We store each EMPLOYEEs social security
We need to create a database schema design
based on the following (simplified)
requirements of the COMPANY Database:
number, address, salary, sex, and birthdate.
Each employee works for one department but
may work on several projects.
We keep track of the number of hours per week
that an employee currently works on each
project.
We also keep track of the direct supervisor of
each employee.
Each employee may have a number of
DEPENDENTs.
For each dependent, we keep track of their
name, sex, birthdate, and relationship to the
employee.
The company is organized into DEPARTMENTs. Each
department has a name, number and an employee
who manages the department. We keep track of the
start date of the department manager. A department
may have several locations.
Each department controls a number of PROJECTs.
Each project has a unique name, unique number and
is located at a single location.
25
Initial Design of Entity Types for the COMPANY
Database Schema
26
Initial Design of Entity Types:
EMPLOYEE, DEPARTMENT, PROJECT, DEPENDENT
Based on the requirements, we can identify four
initial entity types in the COMPANY database:
DEPARTMENT
PROJECT
EMPLOYEE
DEPENDENT
Their initial design is shown on the following slide
The initial attributes shown are derived from the
requirements description
Slide 3- 27
28
