THE ENTITYRELATIONSHIP (ER)
MODEL
CHAPTER 7 (6/E)
CHAPTER 3 (5/E)
LECTURE OUTLINE
Using High-Level, Conceptual Data Models for Database Design
Entity-Relationship (ER) model
Popular high-level conceptual data model
ER diagrams
Diagrammatic notation associated with the ER model
STEPS IN DATABASE DESIGN
Requirements collection and analysis
DB designers interview prospective DB users to understand and
document data requirements
Data requirements
Functional requirements of the principal applications
Conceptual or logical DB design
Description of data requirements
Detailed descriptions of components and constraints
Transformed into implementation data model
Result: DB schema in implementation data model of DBMS
Physical DB design
Internal storage structures, file organizations, indexes, access
paths, and physical design parameters for the DB files
External or view design
A SAMPLE DATABASE APPLICATION
Requirements gathered for COMPANY
Employees, departments, and projects
Company is organized into departments
Department controls several projects
Employee: require each employees name, Social Security number,
address, salary, sex (gender), and birth date
Keep track of the dependents of each employee
ER MODEL OVERVIEW
ER model describes data in terms of:
Entities and entity sets
Objects
Relationships and relationship sets
Connections between objects
Attributes
Properties that characterize or describe entities or relationships
ENTITIES AND ATTRIBUTES EXAMPLE
ENTITY SETS
Entity type or set
Collection (or set) of similar entities that have the same attributes
ER model defines entity sets, not individual entities
But entity sets described in terms of their attributes
CATEGORIES OF ATTRIBUTES
Simple (atomic) vs. composite attributes
Single-valued vs. multivalued attributes
Stored vs. derived attributes
Key or unique attributes
Attribute values constrained to be distinct for individual entities in
entity set
INITIAL ER DIAGRAM FOR COMPANY
Four entity types
Most attributes are simple, single-valued, and stored
Works_on and Locations are multivalued
Employees Name is composite
Employee has one key, department and project have two keys,
dependent has none
WEAK ENTITY TYPES
Entity types that do not have key attributes of their own
Identified by their relationship to specific entities from another entity
type
Dependent is meaningless in
COMPANY DB independently
of Employee
Identified by relationship to
Employee
Dependent_name distinguishes
one dependent from other
dependents for the same
employee: partial key
11
Identifying relationship
Relates a weak entity type to the identifying entity, which has the
rest of the key
RELATIONSHIPS IN GENERAL
Relationship
Interaction between entities
Indicator: an attribute of one entity refers to another entity
12
Represent such references as relationships not attributes
RELATIONSHIPS
Relationship
Interaction between entities
Indicator: an attribute of one entity refers to another entity
Represent such references as relationships not attributes
Relationship type R among n entity types E1, E2, ..., En
Defines a set of associations among entities from these entity types
Relationship instance ri
Each ri associates n individual entities (e1, e2, ..., en)
Each entity ej in ri is a member of entity set Ej
Relationships uniquely identified by keys of participating entities
Degree of a relationship type
13
Number of participating entity types
e.g., binary, ternary
14
RELATIONSHIPS & RELATIONSHIP SETS
DIAGRAMMING RELATIONSHIP TYPE
Diamond for relationship type
Connected to each participating entity type
Could be binary, ternary, or higher degree
Remember:
15
Represents a set of entities of each type,
some of which are related to entities of the
other type(s)
Some entities might participate in several
relationships
Some entities might not participate in the
relationship at all
RELATIONSHIPS WITH
REPEATED ENTITY SETS
Some relationships involve multiple entities from the same entity set
e.g., spouse (two persons), games (two teams)
e.g., recursive relationships, such as supervises (two employees)
Role name
16
Signifies role that participating entity plays in relationship instance
Required when entity type participates multiple times in a
relationship
17
USING ROLE NAMES
RELATIONSHIP CONSTRAINTS
Cardinality ratio
Specifies maximum number of relationship instances in which each
entity can participate
Types 1:1, 1:N, or M:N
Participation constraint
Specifies whether existence of entity depends on its being related to
another entity
Types: total and partial
Thus minimum number of relationship instances in which entities can
participate: thus1 for total participation, 0 for partial
Diagrammatically, use a double line from relationship type to entity
type
Alternative: Structural constraint
Generalization: specifying any min and max participation
Replaces cardinality ratio numerals and single/double line notation
18
Associate a pair of integer numbers (min, max) with each participation
of an entity type E in a relationship type R, where 0 min max and
max 1
max=N finite, but unbounded
RELATIONSHIP ATTRIBUTES
Relationship types can also have attributes
Property that depends on both/all participating entities
Example: Percentage of control that department has on a project
CONTROLS
Percent
Attributes of 1:1 or 1:N relationship types can be
migrated to one of the participating entity types
19
For a 1:N relationship type, relationship attribute can be migrated
only to entity type on N-side of relationship
Attributes on M:N relationship types must be specified as
relationship attributes
SUMMARY OF ER DIAGRAM SYMBOLS
20
1 E1 entity can be related to N E2 entities
REFINING EXAMPLE ER DESIGN
Recall preliminary ER design
Change attributes that reference entity types into relationship types
Weak entities use identifying relationship
Weak entity type always has structural constraint of (1,1)
participation in identifying relationship
21
Determine cardinality ratio and participation constraints for each
relationship type
22
23
APPROPRIATE ER MODEL DESIGN
Choose names that convey meanings attached to various
constructs.
Nouns give rise to entity type names
Verbs indicate names of relationship types
Choose binary relationship names to make ER diagram readable
from left to right and from top to bottom
Review all attributes
Refine into a relationship if attribute references an entity type
Attribute that exists in several entity types may be better modelled
as an independent entity type
25
Entities that must participate in a relationship with another entity
type and with cardinality constraint of 1 might be better modelled as
weak entity
26
REVIEW HIGH-DEGREE RELATIONSHIPS
LECTURE SUMMARY
Components of the Entity-Relationship Model
Entity Types, Entity Sets
Weak Entity Types
Relationship Types, Relationship Sets, Roles
Attributes, Attribute Classification, Keys
Structural Constraints
ER diagrams represent ER models
28
Appropriate ER design