Data Modeling and
the Entity-Relationship Model
M. Tamer Özsu
David R. Cheriton School of Computer Science
University of Waterloo
CS 348
Introduction to Database Management
Fall 2012
CS 348 E-R Model Fall 2012 1 / 35
Notes
Outline
1 Basic E-R Modeling
Entities
Attributes
Relationships
Roles
2 Constraints in E-R Models
Primary Keys
Relationship Types
Existence Dependencies
General Cardinality Constraints
3 Extensions to E-R Modeling
Structured Attributes
Aggregation
Specialization
Generalization
Disjointness
4 Design Considerations
CS 348 E-R Model Fall 2012 2 / 35
Notes
Overview of E-R Model
Used for (and designed for) database (conceptual schema) design
) Proposed by Peter Chen in 1976
World/enterprise described in terms of
• entities
• attributes
• relationships
Visualization: E-R diagram
N.B. Many variant notations are in common use
CS 348 E-R Model Fall 2012 3 / 35
Notes
Basic E-R Modeling
Entity: a distinguishable object
Entity set: set of entities of same type
Examples:
• students currently at University of Waterloo
• flights offered by Air Canada
• burglaries in Ontario during 1994
Graphical representation of entity sets:
Student Flight Burglary
CS 348 E-R Model Fall 2012 4 / 35
Notes
Basic E-R Modeling (cont’d)
Attributes: describe properties of entities
Examples (for Student-entities): StudentNum,
StudentName, Major, : : :
Domain: set of permitted values for an attribute
Graphical representation of attributes:
Student Major
StudentNum StudentName
CS 348 E-R Model Fall 2012 5 / 35
Notes
Basic E-R Modeling (cont’d)
Relationship: representation of the fact that certain entities are related
to each other
Relationship set: set of relationships of a given type
Examples:
• students registered in courses
• passengers booked on flights
• parents and their children
• bank branches, customers and their accounts
In order for a relationship to exist, the participating entities must exist.
CS 348 E-R Model Fall 2012 6 / 35
Notes
Graphical Representation
StudentNum
Student
StudentName
RegisteredIn
Course CourseNum
CS 348 E-R Model Fall 2012 7 / 35
Notes
Graphical Representation (cont’d)
BranchName AccountNum
Branch Account
Balance
CAB
StreetAddr Customer SIN
CustomerName CustomerCity
CS 348 E-R Model Fall 2012 8 / 35
Notes
Multiple Relationships and Role Names
Role: the function of an entity set in a relationship set
Role name: an explicit indication of a role
Example:
Address
HomeTeam
Team Match Location
Visitor
TeamName LocName
Role labels are needed whenever an entity set has multiple functions in
a relationship set.
CS 348 E-R Model Fall 2012 9 / 35
Notes
Relationships and Attributes
Relationships may also have attributes
Example:
Score Address
HomeTeam
Team Match Location
Visitor
TeamName LocName
CS 348 E-R Model Fall 2012 10 / 35
Notes
Constraints in E-R Models
• Primary keys
• Relationship types
• Existence dependencies
• General cardinality constraints
CS 348 E-R Model Fall 2012 11 / 35
Notes
Primary Keys
Each entity must be distinguishable from any other entity in an entity
set by its attributes
Primary key: selection of attributes chosen by designer values of which
determines the particular entity.
Example 1:
Dnum Department ManagerName
Dname Budget
Example 2:
FirstName Employee Salary
Initial LastName
CS 348 E-R Model Fall 2012 12 / 35
Notes
Relationship Types
• many-to-many (N:N): an entity in one set can be related to
many entities in the other set, and vice versa
(This is the interpretation we have used so far.)
• many-to-one (N:1): each entity in one set can be related to at
most one entity in the other, but an entity in the second set may
be related to many entities in the first
Employee WorksIn Department
• one-to-many (1:N): similar
• one-to-one (1:1): each entity in one set can be related to at most
one entity in the other, and vise versa
Employee Manages Department
CS 348 E-R Model Fall 2012 13 / 35
Notes
Existence Dependencies
Sometimes the existence of an entity depends on the existence of
another entity
If x is existence dependent on y, then
• y is a dominant entity
• x is a subordinate entity
Example: “Transactions are existence dependent on accounts.”
Balance Account AccNum
Log
Transaction TransNum
Date Amount
CS 348 E-R Model Fall 2012 14 / 35
Notes
Identifying Subordinate Entities
Weak entity set: an entity set containing subordinate entities
Strong entity set: an entity set containing no subordinate entities
Attributes of weak entity sets only form key relative to a given
dominant entity
Example: “All transactions for a given account have a unique
transaction number.”
Balance Account AccNum
Log
Transaction TransNum
Date Amount
CS 348 E-R Model Fall 2012 15 / 35
Notes
Identifying Subordinate Entities (cont’d)
A weak entity set must have a many-to-one relationship to a distinct
entity set
Visualization: (distinguishing an identifying relationship)
Identifying Other
E
Relationship Relationship
Discriminator of a weak entity set: set of attributes that distinguish
subordinate entities of the set, for a particular dominant entity
Primary key for a weak entity set: discriminator + primary key of
entity set for dominating entities
CS 348 E-R Model Fall 2012 16 / 35
Notes
General Cardinality Constraints
General cardinality constraints determine lower and upper bounds on
the number of relationships of a given relationship set in which a
component entity may participate
Visualization:
.
E R .
(lower,upper) .
Example:
Student Takes Course
(3,5) (6,100)
CS 348 E-R Model Fall 2012 17 / 35
Notes
Extensions to E-R Modeling
• Structured attributes
• Aggregation
• Specialization
• Generalization
• Disjointness
CS 348 E-R Model Fall 2012 18 / 35
Notes
Structured Attributes
Composite attributes: composed of fixed number of other attributes
Multi-valued attributes: attributes that are set-valued
Example:
Street
City
Employee Address
Province
Hobbies
PostalCode
CS 348 E-R Model Fall 2012 19 / 35
Notes
Aggregation
Relationships can be viewed as higher-level entities
Example: “Accounts are assigned to a given student enrollment.”
CourseNum
Student EnrolledIn Course
StudentNum
CourseAccount ExpirationDate
Account UserId
CS 348 E-R Model Fall 2012 20 / 35
Notes
Specialization
A specialized kind of entity set may be derived from a given entity set
Example: “Graduate students are students who have a supervisor and
a number of degrees.”
StudentNumber
Student
StudentName
Graduate SupervisedBy Professor
(1, 1) (0, N)
Degrees ProfessorName
CS 348 E-R Model Fall 2012 21 / 35
Notes
Generalization
Several entity sets can be abstracted by a more general entity set
Example: “A vehicle abstracts the notion of a car and a truck.”
PassengerCount
LicenceNum Car MaxSpeed
MakeAndModel
)
Price MakeAndModel
LicenceNum Vehicle Price
COVERS
AxelCount
LicenceNum Truck Tonnage Tonnage Truck Car MaxSpeed
Price MakeAndModel AxelCount PassengerCount
CS 348 E-R Model Fall 2012 22 / 35
Notes
Disjointness
Specialized entity sets are usually disjoint but can be declared to have
entities in common
• By default, specialized entity sets are disjoint.
Example: We may decide that nothing is both a car and a truck.
• However, we can declare them to overlap (to accommodate utility
vehicles, perhaps).
MakeAndModel
LicenceNum Vehicle Price
OVERLAPS
Tonnage Truck Car MaxSpeed
AxelCount PassengerCount
CS 348 E-R Model Fall 2012 23 / 35
Notes
Designing An E-R Schema
Usually many ways to design an E-R schema
Points to consider
• use attribute or entity set?
• use entity set or relationship set?
• degrees of relationships?
• extended features?
CS 348 E-R Model Fall 2012 24 / 35
Notes
Attributes or Entity Sets?
Example: Should one model employees’ phones by a PhoneNumber
attribute, or by a Phone entity set related to the Employee entity set?
Rules of thumb:
• Is it a separate object?
• Do we maintain information about it?
• Can several of its kind belong to a single entity?
• Does it make sense to delete such an object?
• Can it be missing from some of the entity set’s entities?
• Can it be shared by different entities?
An affirmative answer to any of the above suggests a new entity set.
CS 348 E-R Model Fall 2012 25 / 35
Notes
Entity Sets or Relationships?
Instead of representing accounts as entities, we could represent them as
relationships
BranchName
Branch
Balance
Account
AccountNum
StreetAddr Customer SIN
CustomerName CustomerCity
CS 348 E-R Model Fall 2012 26 / 35
Notes
Binary vs. N-ary Relationships?
BranchName AccountNum
Branch Account
Balance
CAB
StreetAddr Customer SIN
CustomerName CustomerCity
CS 348 E-R Model Fall 2012 27 / 35
Notes
Binary vs. N-ary Relationships (cont’d)
We can always represent a relationship on n entity sets with n binary
relationships
BranchName AccountNum
Branch Account
Balance
CABBranch CAB CABAccount
CABCustomer
StreetAddr Customer SIN
CustomerName CustomerCity
CS 348 E-R Model Fall 2012 28 / 35
Notes
A Simple Methodology
1 Recognize entity sets
2 Recognize relationship sets and participating entity sets
3 Recognize attributes of entity and relationship sets
4 Define relationship types and existence dependencies
5 Define general cardinality constraints, keys and discriminators
6 Draw diagram
For each step, maintain a log of assumptions motivating the choices,
and of restrictions imposed by the choices
CS 348 E-R Model Fall 2012 29 / 35
Notes
Example: A Registrar’s Database
• Zero or more sections of a course are offered each term. Courses
have names and numbers. In each term, the sections of each
course are numbered starting with 1.
• Most course sections are taught on-site, but a few are taught at
off-site locations.
• Students have student numbers and names.
• Each course section is taught by a professor. A professor may
teach more than one section in a term, but if a professor teaches
more than one section in a term, they are always sections of the
same course. Some professors do not teach every term.
• Up to 50 students may be registered for a course section. Sections
with 5 or fewer students are cancelled.
• A student receives a mark for each course in which they are
enrolled. Each student has a cumulative grade point average
(GPA) which is calculated from all course marks the student has
received.
CS 348 E-R Model Fall 2012 30 / 35
Notes
Example: A Registrar’s Database (cont’d)
Course
Section
Off Site
Section
Professor Student
CS 348 E-R Model Fall 2012 31 / 35
Notes
Example: A Registrar’s Database (cont’d)
Course
SectionOf
Section
TaughtBy EnrolledIn
Off Site
Section
Professor Student
CS 348 E-R Model Fall 2012 32 / 35
Notes
Example: A Registrar’s Database (cont’d)
CourseNum Course CourseName
SectionOf
Term
SectionNum
Section
TaughtBy EnrolledIn Mark
Off Site
Section GPA
Professor Student
ProfName Location
StudentName
ProfNum StudentNum
CS 348 E-R Model Fall 2012 33 / 35
Notes
Example: A Registrar’s Database (cont’d)
CourseNum Course CourseName
SectionOf
Term
SectionNum
Section
TaughtBy EnrolledIn Mark
Off Site
Section GPA
Professor Student
ProfName Location
StudentName
ProfNum StudentNum
CS 348 E-R Model Fall 2012 34 / 35
Notes
Example: A Registrar’s Database (cont.’d)
CourseNum Course CourseName
(0, N)
SectionOf
Term
(1, 1) SectionNum
(1, 1) Section (6, 50)
TaughtBy EnrolledIn Mark
Off Site
Section GPA
Professor Student
ProfName Location
StudentName
ProfNum StudentNum
CS 348 E-R Model Fall 2012 35 / 35
Notes
