Entity-Relationship Model

• Design Process
• Modeling
• Constraints
• E-R Diagram
• Weak Entity Sets
• Extended E-R Features
• Design of the Bank Database
• Reduction to Relation Schemas
 Design Phases

• The initial phase of database design is to characterize

fully the data needs of the prospective database users.
• Next, the designer chooses a data model and, by
applying the concepts of the chosen data model,
translates these requirements into a conceptual
schema of the database.
• A fully developed conceptual schema also indicates
the functional requirements of the enterprise. In a
“specification of functional requirements”, users
describe the kinds of operations (or transactions) that
will be performed on the data.
 Design Phases (Cont.)
The process of moving from an abstract data model to the
implementation of the database proceeds in two final
design phases.

• Logical Design – Deciding on the database schema.

Database design requires that we find a “good”
collection of relation schemas.
– Business decision – What attributes should we record in
the database?
– Computer Science decision – What relation schemas
should we have and how should the attributes be
distributed among the various relation schemas?
• Physical Design – Deciding on the physical layout of the
database
 Design Approaches
• Entity Relationship Model (covered in this
chapter)
– Models an enterprise as a collection of entities
and relationships
• Entity: a “thing” or “object” in the enterprise that is
distinguishable from other objects
– Described by a set of attributes
• Relationship: an association among several entities
– Represented diagrammatically by an entity-
relationship diagram:
• Normalization Theory
– Formalize what designs are bad, and test for them
Outline of the ER Model
ER model -- Database Modeling
• The ER data mode was developed to facilitate database
design by allowing specification of an enterprise schema
that represents the overall logical structure of a database.
• The ER model is very useful in mapping the meanings and
interactions of real-world enterprises onto a conceptual
schema. Because of this usefulness, many database-design
tools draw on concepts from the ER model.
• The ER data model employs three basic concepts:
– entity sets,
– relationship sets,
– attributes.
• The ER model also has an associated diagrammatic
representation, the ER diagram, which can express the
overall logical structure of a database graphically.
 Entity Sets
• An entity is an object that exists and is distinguishable
from other objects.
– Example: specific person, company, event, plant
• An entity set is a set of entities of the same type that
share the same properties.
– Example: set of all persons, companies, trees, holidays
• An entity is represented by a set of attributes; i.e.,
descriptive properties possessed by all members of an
entity set.
– Example:
instructor = (ID, name, street, city, salary )
course= (course_id, title, credits)
• A subset of the attributes form a primary key of the
entity set; i.e., uniquely identifiying each member of
the set.
 Entity Sets -- instructor and student
instructor_ID instructor_name student-ID student_name
 Relationship Sets
• A relationship is an association among several entities
Example:
44553 (Peltier) advisor 22222
(Einstein)
student entity relationship set
instructor entity
• A relationship set is a mathematical relation among n ≥
2 entities, each taken from entity sets
{(e1, e2, … en) | e1 ∈ E1, e2 ∈ E2, …, en ∈ En}

where (e1, e2, …, en) is a relationship

– Example:
(44553,22222) ∈ advisor
Relationship Set advisor
•
Relationship Sets (Cont.)
An attribute can also be associated with a relationship set.
• For instance, the advisor relationship set between entity sets
instructor and student may have the attribute date which tracks
when the student started being associated with the advisor
 Degree of a Relationship Set
• binary relationship
– involve two entity sets (or degree two).
– most relationship sets in a database system are
binary.
• Relationships between more than two entity
sets are rare. Most relationships are binary.
(More on this later.)
4 Example: students work on research projects
under the guidance of an instructor.
4 relationship proj_guide is a ternary relationship
between instructor, student, and project
Mapping Cardinality Constraints
• Express the number of entities to which another
entity can be associated via a relationship set.
• Most useful in describing binary relationship sets.
• For a binary relationship set the mapping
cardinality must be one of the following types:
– One to one
– One to many
– Many to one
– Many to many
 Mapping Cardinalities

One to One to
one many
Note: Some elements in A and B may not be mapped
to any
elements in the other set
 Mapping Cardinalities

Many to Many to
one many
Note: Some elements in A and B may not be mapped
to any
elements in the other set
 Complex Attributes
• Attribute types:
– Simple and composite attributes.
– Single-valued and multivalued attributes
• Example: multivalued attribute: phone_numbers
– Derived attributes
• Can be computed from other attributes
• Example: age, given date_of_birth
• Domain – the set of permitted values for
each attribute
Composite Attributes
 Redundant Attributes
• Suppose we have entity sets:
– instructor, with attributes: ID, name, dept_name,
salary
– department, with attributes: dept_name, building,
budget
• We model the fact that each instructor has an associated
department using a relationship set inst_dept
• The attribute dept_name appears in both entity sets. Since
it is the primary key for the entity set department, it
replicates information present in the relationship and is
therefore redundant in the entity set instructor and needs
to be removed.
• BUT: when converting back to tables, in some cases the
attribute gets reintroduced, as we will see later.
 Weak Entity Sets
• Consider a section entity, which is uniquely identified by
a course_id, semester, year, and sec_id.
• Clearly, section entities are related to course entities.
Suppose we create a relationship set sec_course
between entity sets section and course.
• Note that the information in sec_course is redundant,
since section already has an attribute course_id, which
identifies the course with which the section is related.
• One option to deal with this redundancy is to get rid of
the relationship sec_course; however, by doing so the
relationship between section and course becomes
implicit in an attribute, which is not desirable.
 Weak Entity Sets (Cont.)
• An alternative way to deal with this redundancy is to not store the
attribute course_id in the section entity and to only store the
remaining attributes section_id, year, and semester. However, the
entity set section then does not have enough attributes to identify
a particular section entity uniquely; although each section entity is
distinct, sections for different courses may share the same
section_id, year, and semester.
• To deal with this problem, we treat the relationship sec_course as a
special relationship that provides extra information, in this case,
the course_id, required to identify section entities uniquely.
• The notion of weak entity set formalizes the above intuition. A
weak entity set is one whose existence is dependent on another
entity, called its identifying entity; instead of associating a primary
key with a weak entity, we use the identifying entity, along with
extra attributes called discriminator to uniquely identify a weak
entity. An entity set that is not a weak entity set is termed a strong
entity set.
 Weak Entity Sets (Cont.)
• Every weak entity must be associated with an
identifying entity; that is, the weak entity set is
said to be existence dependent on the identifying
entity set. The identifying entity set is said to own
the weak entity set that it identifies. The
relationship associating the weak entity set with
the identifying entity set is called the identifying
relationship.
• Note that the relational schema we eventually
create from the entity set section does have the
attribute course_id, for reasons that will become
clear later, even though we have dropped the
attribute course_id from the entity set section.
E-R Diagrams
 Entity Sets
● Entities can be represented graphically as follows:
• Rectangles represent entity sets.
• Attributes listed inside entity rectangle
• Underline indicates primary key attributes
 Relationship Sets
● Diamonds represent relationship sets.
Relationship Sets with Attributes
 Roles
• Entity sets of a relationship need not be
distinct
– Each occurrence of an entity set plays a “role”
in the relationship
• The labels “course_id” and “prereq_id” are
called roles.
 Cardinality Constraints
• We express cardinality constraints by drawing either a
directed line (→), signifying “one,” or an undirected line
(—), signifying “many,” between the relationship set
and the entity set.
• One-to-one relationship between an instructor and a
student :
– A student is associated with at most one instructor via the
relationship advisor
– A student is associated with at most one department via
stud_dept
 One-to-Many Relationship
• one-to-many relationship between an
instructor and a student
– an instructor is associated with several
(including 0) students via advisor
– a student is associated with at most one
instructor via advisor,
 Many-to-One Relationships
• In a many-to-one relationship between
an instructor and a student,
– an instructor is associated with at most one
student via advisor,
– and a student is associated with several
(including 0) instructors via advisor
 Many-to-Many Relationship
• An instructor is associated with several
(possibly 0) students via advisor
• A student is associated with several
(possibly 0) instructors via advisor
 Total and Partial Participation
● Total participation (indicated by double line): every entity
in the entity set participates in at least one relationship in
the relationship set

participation of student in advisor relation is total

4 every student must have an associated instructor
● Partial participation: some entities may not participate in
any relationship in the relationship set
● Example: participation of instructor in advisor is partial
 Notation for Expressing More Complex Constraints

● A line may have an associated minimum and maximum

cardinality, shown in the form l..h, where l is the minimum
and h the maximum cardinality
● A minimum value of 1 indicates total participation.
● A maximum value of 1 indicates that the entity
participates in at most one relationship
● A maximum value of * indicates no limit.

Instructor can advise 0 or more students. A student

must have 1 advisor; cannot have multiple advisors
Notation to Express Entity with Complex Attributes
 Expressing Weak Entity Sets

• In E-R diagrams, a weak entity set is depicted via a double

rectangle.
• We underline the discriminator of a weak entity set with a dashed
line.
• The relationship set connecting the weak entity set to the
identifying strong entity set is depicted by a double diamond.
• Primary key for section – (course_id, sec_id, semester, year)
Reduction to Relation Schemas
 Reduction to Relation Schemas
• Entity sets and relationship sets can be expressed
uniformly as relation schemas that represent the
contents of the database.
• A database which conforms to an E-R diagram
can be represented by a collection of schemas.
• For each entity set and relationship set there is a
unique schema that is assigned the name of the
corresponding entity set or relationship set.
• Each schema has a number of columns (generally
corresponding to attributes), which have unique
names.
 Representing Entity Sets
• A strong entity set reduces to a schema with the same
attributes
student(ID, name, tot_cred)

• A weak entity set becomes a table that includes a

column for the primary key of the identifying strong
entity set
section ( course_id, sec_id, sem, year )
 Representing Relationship Sets

• A many-to-many relationship set is represented as a

schema with attributes for the primary keys of the two
participating entity sets, and any descriptive attributes
of the relationship set.
• Example: schema for relationship set advisor

advisor = (s_id, i_id)

Representation of Entity Sets with Composite Attributes
• Composite attributes are flattened out by creating a
separate attribute for each component attribute
– Example: given entity set instructor with composite
attribute name with component attributes first_name
and last_name the schema corresponding to the entity
set has two attributes name_first_name and
name_last_name
• Prefix omitted if there is no ambiguity
(name_first_name could be first_name)
• Ignoring multivalued attributes, extended instructor
schema is
– instructor(ID,
first_name, middle_initial, last_name,
street_number, street_name,
apt_number, city, state, zip_code,
date_of_birth)
Representation of Entity Sets with Multivalued Attributes
• A multivalued attribute M of an entity E is represented
by a separate schema EM
• Schema EM has attributes corresponding to the
primary key of E and an attribute corresponding to
multivalued attribute M
• Example: Multivalued attribute phone_number of
instructor is represented by a schema:
inst_phone= ( ID, phone_number)
• Each value of the multivalued attribute maps to a
separate tuple of the relation on schema EM
– For example, an instructor entity with primary key
22222 and phone numbers 456-7890 and 123-4567
maps to two tuples:
(22222, 456-7890) and (22222, 123-4567)
●
Redundancy of Schemas
Many-to-one and one-to-many relationship sets that are total
on the many-side can be represented by adding an extra
attribute to the “many” side, containing the primary key of the
“one” side
● Example: Instead of creating a schema for relationship set
inst_dept, add an attribute dept_name to the schema arising
from entity set instructor
 Redundancy of Schemas (Cont.)
• For one-to-one relationship sets,
either side can be chosen to act as
the “many” side
– That is, an extra attribute can be added
to either of the tables corresponding to
the two entity sets
• If participation is partial on the
“many” side, replacing a schema by
an extra attribute in the schema
corresponding to the “many” side
could result in null values
 Redundancy of Schemas (Cont.)
• The schema corresponding to a relationship set
linking a weak entity set to its identifying
strong entity set is redundant.

• Example: The section schema already contains

the attributes that would appear in the
sec_course schema
 Specialization
• Top-down design process; we designate sub-
groupings within an entity set that are distinctive
from other entities in the set.
• These sub-groupings become lower-level entity sets
that have attributes or participate in relationships
that do not apply to the higher-level entity set.
• Depicted by a triangle component labeled ISA (e.g.,
instructor “is a” person).
• Attribute inheritance – a lower-level entity set
inherits all the attributes and relationship
participation of the higher-level entity set to which it
is linked.
 Specialization Example
• Overlapping – employee and student
• Disjoint – instructor and secretary
• Total and partial
 Generalization
• A bottom-up design process – combine a
number of entity sets that share the same
features into a higher-level entity set.
• Specialization and generalization are simple
inversions of each other; they are
represented in an E-R diagram in the same
way.
• The terms specialization and generalization
are used interchangeably.
 Aggregation
● Consider the ternary relationship proj_guide, which we saw
earlier
● Suppose we want to record evaluations of a student by a
guide on a project
 Aggregation (Cont.)
• Relationship sets eval_for and proj_guide
represent overlapping information
– Every eval_for relationship corresponds to a
proj_guide relationship
– However, some proj_guide relationships may not
correspond to any eval_for relationships
• So we can’t discard the proj_guide relationship
• Eliminate this redundancy via aggregation
– Treat relationship as an abstract entity
– Allows relationships between relationships
– Abstraction of relationship into new entity
 Aggregation (Cont.)
• Eliminate this redundancy
via aggregation without
introducing redundancy,
the following diagram
represents:
– A student is guided by a
particular instructor on a
particular project
– A student, instructor,
project combination may
have an associated
evaluation