DataBase

Entity-Relationship
(ER) Data Model
DataBase part 04 - Entity
Relationship Model

The entity-relationship (ER) data model

allows us to describe the data involved
in a real-world enterprise in terms of
objects and their relationships and is
widely used to develop an initial
database design.

DataBase part 04 - Entity

Relationship Model

The ER model

important primarily for its role in database

design
provides useful concepts that allow us to
move from an informal description of what
users want from their database to a more
detailed, and precise description that can
be implemented in a DBMS.
DataBase part 04 - Entity
Relationship Model

Within the larger context of the overall

design process, the ER model is used in
a phase called conceptual database

design
Many variations of ER diagrams are in

use, and no widely accepted standards

prevail
DataBase part 04 - Entity
Relationship Model

Database design

1.
2.
3.
4.

5.
6.

ER model is most relevant to first 3 steps:

Requirements Analysis
Conceptual Database Design
Logical Database Design
Schema Refinement
Physical Database Design
Security Design
DataBase part 04 - Entity
Relationship Model

Requirements Analysis

understand what data is to be stored

what operations are most frequent and

subject to performance requirements

must find out what the users want from

the database
informal process, study of the current
operating environment and how it is
expected to change
DataBase part 04 - Entity
Relationship Model

Conceptual Database Design

develop a high-level description of data

to be stored in database

along with constraints that are known to

hold over this data

often carried out using ER model

DataBase part 04 - Entity

Relationship Model

Logical Database Design

choose a DBMS to implement database

design

convert conceptual database design into database

schema in the data model of the chosen DBMS

convert an ER schema into a relational

database schema
result is a conceptual schema or logical
schema
DataBase part 04 - Entity
Relationship Model

Schema Refinement

in contrast to the requirements analysis and

conceptual design steps, which are essentially
subjective, schema refinement can be guided
by some elegant and powerful theory
theory of normalizing relations - restructuring
them to ensure some desirable properties
database consistency, regardless the
operations performed insert, update, delete
DataBase part 04 - Entity
Relationship Model

Physical Database Design

consider workloads that database must

support and further refine database
design => it meets the desired
performance criteria
building indexes on some tables,
clustering some tables, or it may
involve a substantial redesign of parts
of the database schema
DataBase part 04 - Entity
Relationship Model

Security Design

identify different user groups and

different roles played by various users
identify parts of database that they
must be able to access and parts of
database that they should not be
allowed to access

DataBase part 04 - Entity

Relationship Model

Tuning

subsequent phase in which all six kinds

of design steps are interleaved and
repeated until the design is satisfactory
implementing the database design
designing and implementing the
application layers that run on top of
database
DataBase part 04 - Entity
Relationship Model

Entity, Entity set

An entity is an object in the real world

that is distinguishable from other
objects
An entity set is a collection of similar
entities

DataBase part 04 - Entity

Relationship Model

Attributes

An entity is described using a set of

attributes. All entities in a given entity
set have the same attributes; this is
essentially what we mean by similar
choice of attributes reflects the level of
detail at which we wish to represent
information about entities
DataBase part 04 - Entity
Relationship Model

Domain

For each attribute associated with an

entity set, we must identify a domain of

possible values

DataBase part 04 - Entity

Relationship Model

Keys

each entity set contains at least one set of

attributes that uniquely identifies an entity in
the entity set <=> key
A key is a minimal set of attributes whose
values uniquely identify an entity in the set

could be more than one candidate key

designate one of them as the primary key

The primary key has unique values;
NULL not allowed
DataBase part 04 - Entity
Relationship Model

Representation

An entity set is represented by a

rectangle, and an attribute is
represented by an oval
Each attribute in the primary key is
underlined
domain information could be listed
along with the attribute name, but we
omit this, to keep figures compact
DataBase part 04 - Entity
Relationship Model

Employee entity set

Employees entity set, attributes ssn,

name, and lot, key is ssn

Relationship

set of n-tuples:
{(e1, ... ,en) | ei E}
each n-tuple denotes a relationship
involving n entities e1 through en ,
where entity ei is in entity set E

Works_in
relationship set

Relationship set Works_In indicates

a department in which an employee
works

Relationships
descriptive attributes

to record information about the

relationship, rather than about any one
of the participating entities
relationship must be uniquely identified
by the participating entities, without
reference to the descriptive attributes

Instance

of an entity / relationship set is a set of

relationships
snapshot of relationship set at some
instant in time

Instance of Works_In
Relationship Set

N-nary relationship

as another example suppose that each

department has offices in several
locations and we want to record the
locations at which each employee works
ternary relationship because we must
record an association between an
employee, a department, and a location

Ternary Relationship Set

Auto-Referring relationship

entity sets that participate in

relationship set need not be distinct
sometimes a relationship might involve
two entities in the same entity set
for example, consider the Reports_To
relationship set

Roles

Since employees report to other employees,

every relationship is of form (emp1, emp2),
where both emp1, emp2 are entities in
Employees
however, they play different roles

emp1 reports to the managing employee emp2

subordinate emp1
supervisor emp2

Reports_To Relationship

Manages relationship

between the Employees and

Departments
each department has at most one
manager

although a single employee is allowed to

manage more than one department

Key Constraint on Manages

Key constraint

restriction that each department has at

most one manager
restriction indicated in ER diagram
using an arrow from Departments to
Manages

Instance of the Manages relationship

set is also a potential instance for the
Works_In relationship set,
but violates the key constraint on
Manages
Manages One to Many relationship

Instance of Manages Relationship

Set

Binary relationship types

One employee can be associated with many

departments, in capacity of manager,

whereas each department can be associated
with at most one employee as its manager
In contrast, Works In relationship set, in
which an employee is allowed to work in
several departments and a department is
allowed to have several employees, is said to
be many-to-many

Binary relationship types

If we add the restriction that each

employee can manage at most one
department to the Manages relationship
set we have a one-to-one relationship
set

Participation constraint

Key constraint tells us that a department has at most

one manager
Every department has a manager?
let say that every department is required to have a
manager
Participation of entity set Departments in
relationship set Manages is said to be total
Participation that is not total is said to be partial
as an example, participation of entity set Employees
in Manages is partial, since not every employee gets
to manage a department

Class Hierarchies

Natural to classify the entities into

subclasses
for example, we might want to talk
about an Hourly_Emps entity set and
a Contract_Emps entity set

to distinguish basis on which they are paid

Class Hierarchies

Class Hierarchies

Employees is specialized into subclasses

Specialization is the process of identifying
subsets of an entity set (the superclass) that
share some distinguishing characteristic
Hourly_Emps and Contract_Emps are
generalized by Employees
Generalization consists of identifying some
common characteristics of a collection of
entity sets and creating a new entity set that
contains entities possessing these common
characteristics (the subclass)

Conceptual database design with

EntityRelationship model

Should a concept be modeled as an

entity or an attribute?
Should a concept be modeled as an
entity or a relationship?
What are the relationship sets and their
participating entity sets? Should we use
binary or ternary relationships?

Entity versus Attribute

for example, consider adding address information to

the Employees entity set
option is to use an attribute address
appropriate if we need to record only one address
per employee, and it suffices to think of an
address as a string
alternative is to create an entity set called Addresses
record associations between employees and
addresses using a relationship
more complex alternative is necessary in two
situations:

1.

2.

We have to record more than one

address for an employee
We want to capture the structure of an
address in our ER diagram. For
example, we might break down an
address into city, state, country, and
Zip code, in addition to a string for
street information

Entity versus Relationship

suppose that each department manager is

given a discretionary budget
there is at most one employee managing a
department, but a given employee could
manage several departments
store starting date and discretionary budget
for each manager-department pair
approach is natural if we assume that a
manager receives a separate discretionary
budget for each department that he or she
manages

But what if discretionary budget is a

sum that covers all departments
managed by that employee

in this case each Manages relationship that

involves a given employee will have the
same value in the dbudget field
such redundancy could be significant and
could cause a variety of problems