The Entity-Relationship Model

1
The Entity-Relationship Model

• Chapter 2

2
Objectives

• Overview of database design
• Requirement analysis, conceptual design, logical
  design, normalization, and physical design
• The entity-relationship (ER) model
• Basic constructs
• Entities
• Relationships
• Attributes (of entities and relationships)
• Advanced constructs
• Constraints
• Weak entities
• ISA hierarchies
• Aggregation
• Conceptual design using the ER model

3
Overview of Database Design

• Requirements analysis Find out what users want
  to do with the database
• What data should be stored in the database?
• What applications should be built on top of it?
• What operations have critical performance
  requirements?
• Conceptual design Use the output of RA to
  develop a high-level description of the data to
  be stored, along with their constraints. Output
  of CD usually is an ER-diagram.
• What are entities and relationships of the
  domain?
• What are the integrity constraints ( business
  rules) that hold?
• Logical design Choose a DBMS and map the
  conceptual schema (ER-diagram) into the data
  model of the DBMS. Output of this step is the
  logical schema of the data.

4
Overview of Database Design (Contd)

• Schema refinement Analyze the logical schema,
  identify potential problems in it, and fix these
  by refining the logical schema using known normal
  forms.
• Physical design Consider expected workloads that
  the database will support to further refine the
  design in order to meet desired performance
  criteria. Output here is the physical schema.
• Application and security design Consider aspects
  of the design that go beyond the database itself.
  Complete design methodologies such as UML are
  useful here
• What are the users and processes involved in the
  database application?
• What are the roles of the involved users?
• What parts of the database are accessible to each
  role and what must not?

5
Conceptual Database Design

• Conceptual design (ER Model is used at this
  stage.)
• What are the entities and relationships in the
  enterprise?
• What information about these entities and
  relationships should we store in the database?
• Which integrity constraints (business rules)
  hold?
• How to represent a database schema in the ER
  model ? pictorially (as ER diagrams) !
• How to map an ER diagram into a relational schema?

6
ER Model Basics

• Entity Real-world object distinguishable from
  other objects. E.g., one employee.
• Attribute Construct describing an entity. E.g.,
  the name of an employee.
• Entity Set A collection of similar entities.
  E.g., all employees.
• All entities in an entity set have the same set
  of attributes. (Exception ISA hierarchies)
• Each entity set has a key, i.e. a minimal set of
  attributes whose values uniquely identify an
  entity in the entity set. Underlined !
• Each attribute has a domain, i.e. a set of
  possible values

• Entity set rectangle attribute oval.

7
ER Model Basics (Contd.)
since
name
dname
ssn
lot
budget
did
Works_In
Departments
Employees

• Relationship Association among 2 or more
  entities. E.g., John works in the Pharmacy
  department.
• Relationship Set Collection of similar
  relationships.
• An n-ary relationship set R relates n entity
  sets E1 ... En each relationship in R involves
  entities e1 of E1, ..., en of En.
• Relationship Set diamond.

8
ER Model Basics (Contd.)
name
ssn
lot
Employees
since
name
dname
super-visor
subor-dinate
budget
ssn
lot
did
Reports_To
Works_In
Departments
Employees

• Descriptive attributes Attributes of
  relationships.
• A relationship set may relate non-distinct entity
  sets
• Related entities play different roles e.g., emp1
  reports to a managing employee emp2. Role
  indicators emphasize this difference in roles.
• Unique names for attributes of the relationship
  set are obtained by concatenating the indicator
  with the attributes of the entity set.

9
Key Constraints
budget
did

• Consider Works_In An employee can work in many
  departments a dept can have many employees.
• In contrast, each dept has at most one manager
  this is an example of a key constraint on
  Manages, also called 1-to-many relationship set.
  (See arrow in figure)

Departments
1-to-1
1-to Many
Many-to-1
Many-to-Many
10
Participation Constraints

• Does every department have a manager?
• If so, this is a participation constraint the
  participation of Departments in Manages is said
  to be total (vs. partial).
• (Every did value in Departments table must appear
  in a row of the Manages table with a non-null ssn
  value!)
• Below thick (thin) lines mean total (partial)
  participation.

since
since
name
dname
name
dname
ssn
lot
budget
did
budget
did
Departments
Employees
Manages
Works_In
since
11
Weak Entities

• A weak entity can be identified uniquely only by
  considering the primary key of another
  (identifying owner) entity.
• Owner entity set and weak entity set must
  participate in a one-to-many relationship set
  (one owner, many weak entities).
• Weak entity set must have total participation in
  this identifying relationship set.
• Example below employees holding insurance
  policies for their dependents. The weak entity
  set and its identifying relationship are
  indicated by drawing them with dark lines.
• A weak entity set has a partial key (indicated
  with dashed line).

name
cost
pname
age
ssn
lot
Dependents
Policy
Employees
12
ISA (is a) Hierarchies
name
ssn
lot
Employees
hours_worked
hourly_wages
As in C, or other OO PLs, attributes are
inherited. If we declare A ISA B, then every A
entity is also a B entity.
ISA
contractid
Contract_Emps
Hourly_Emps

• Overlap constraints Are two classes allowed to
  overlap? E.g., can Joe be both an Hourly_Emps and
  a Contract_Emps entity? (Allowed/disallowed)
• Covering constraints Do entities of subclasses
  contain all those in the superclass? E.g., does
  every Employees entity also have to be an
  Hourly_Emps or a Contract_Emps entity? (Yes/no)
• Reasons for using ISA (by specialization/generaliz
  ation)
• To add descriptive attributes specific to a
  subclass.
• To identify entities that participate in a
  relationship (See slide 17).

13
Aggregation
name
lot
ssn

• Used to model a relationship between entity sets
  and relationship sets.
• Aggregation Allows to treat a relationship set
  as an entity set for purposes of participation
  in (other) relationships.
• Notation dashed box indicating that a
  relationship set participates in another
  relationship set.

Monitors
until
since
started_on
dname
pid
pbudget
did
budget
Sponsors
Departments
Projects

• Aggregation vs. ternary relationship
• -- Monitors is a distinct relationship,
• with a descriptive attribute.
• -- Also, can say that each sponsorship
• is monitored by at most one employee.

14
Conceptual Design Using the ER Model

• Design choices
• Should a concept be modeled as an entity or an
  attribute?
• Should a concept be modeled as an entity or a
  relationship?
• Identifying relationships Binary or ternary?
  Aggregation?
• Constraints in the ER Model
• A lot of data semantics can (and should) be
  captured.
• But some constraints cannot be captured in ER
  diagrams.

15
Entity vs. Attribute

• Should address be an attribute of Employees or an
  entity (connected to Employees by a
  relationship)?
• Depends upon the use we want to make of address
  information, and the semantics of the data
• If we have several addresses per employee,
  address must be an entity (since attributes
  cannot be set-valued).
• If the structure (city, street, etc.) is
  important, e.g., we want to retrieve employees in
  a given city, address must be modeled as an
  entity (since attribute values are atomic).

16
Entity vs. Attribute (Contd.)
to
from

• Works_In4 does not allow an employee to
  work in a department for two or more
  periods.
• Similar to the problem of wanting to record
  several addresses for an employee We want to
  record several values of the descriptive
  attributes for each instance of this
  relationship.
• Accomplish this by introducing new entity set
  Duration.

budget
Departments
Works_In4
name
ssn
lot
Works_In4
Departments
Employees
17
Entity vs. Relationship

• Should an attribute be descriptive or not?
• First ER diagram depicts the situation where a
  manager gets a separate discretionary budget for
  each dept.
• What if a manager gets a discretionary budget
  that covers all managed depts?
• Redundancy dbudget stored for each dept managed
  by manager.
• Misleading Suggests dbudget associated with
  department-mgr combination.

since
dbudget
name
dname
ssn
did
lot
budget
Employees
Departments
Manages2
name
ssn
lot
dname
since
did
budget
Employees
Departments
Manages2
ISA
This fixes the problem!
Managers
dbudget
18
Binary vs. Ternary Relationships
pname
age
Dependents
Covers
Bad design for the additional requirements

• This ER diagram expresses the following
  requirements
• An employee can own several policies.
• Each policy can be owned by several employees.
• Each dependent can be covered by several
  policies.
• The ER diagram doesnt express the following
  requirements
• A policy cannot be covered jointly by 2 or more
  employees.
• Every policy must be covered by some employee.
• Dependents is a weak entity set with partial key
  pname and identified by the primary key of
  Policies.
• Which constraints would capture the additional
  requirements?

19
Binary vs. Ternary Relationships (Contd)

• Solution instead of Covers, introduce two binary
  relationships, Purchaser and Beneficiary.
• Then add the following constraints
• Key constraint on Policies with respect to
  Purchaser
• Total participation constraint on Policies with
  respect to Purchaser
• Appropriate constraints on the weak entity set
  Dependents
• What if we only add a key constraint on Policies
  wrt to Covers?

pname
age
Dependents
Purchaser
Better design
20
Binary vs. Ternary Relationships (Contd.)

• Previous example illustrated a case when two
  binary relationships were better than one ternary
  relationship.
• An example in the other direction a ternary
  relation Contracts relates entity sets Parts,
  Departments and Suppliers, and has descriptive
  attribute qty. No combination of binary
  relationships is an adequate substitute
• S can-supply P, D needs P, and D
  deals-with S does not imply that D has agreed
  to buy P from S.
• How do we record qty? Impossible to represent it
  cleanly with these binary relations.

21
Summary of Conceptual Design

• Conceptual design follows requirements analysis,
• Yields a high-level description of data to be
  stored
• ER model popular for conceptual design
• Constructs are expressive, close to the way
  people think about their applications.
• It is a semantic model !
• Basic constructs are entities, relationships,
  and attributes (of entities and relationships).
• Some additional constructs are weak entities,
  ISA hierarchies, and aggregation.
• There are many variations of ER model in the
  literature.

22
Summary of ER (Contd.)

• Several kinds of integrity constraints can be
  expressed in the ER model key constraints,
  participation constraints, and overlap/covering
  constraints for ISA hierarchies. Some foreign
  key constraints are also implicit in the
  definition of a relationship set.
• Some constraints (notably, functional
  dependencies) cannot be expressed in the ER
  model.
• Constraints play an important role in determining
  the best database design for an enterprise.

23
Summary of ER (Contd.)

• ER design is subjective. There are often many
  ways to model a given scenario! Analyzing
  alternatives can be tricky, especially for a
  large enterprise. Common choices include
• Entity vs. attribute, entity vs. relationship,
  binary or n-ary relationship, whether or not to
  use ISA hierarchies, and whether or not to use
  aggregation.