Transcript ER Diagrams - Joe Meehean

ENTITY RELATIONSHIP
DIAGRAMS
1
Joe Meehean
ENTITY RELATIONSHIP MODEL

Entity type
an object (noun)
 e.g., student
 attributes (e.g., last name)


Student
Last name
First name
Student ID
Relationship
Verb
association between entity types
 bidirectional relationship between entities
 e.g., students take course offerings

2
COLLEGE EXAMPLE
Course
Name
Number
Credits
Offering
Has
Section #
Room
Time
Teach
Enroll
Student
Last name
First name
Student ID
Faculty
Last name
First name
Faculty ID
3
RELATIONSHIP CARDINALITY
Limits the number of objects in a relationship
 Options



0

1

Many (M)
Example: Course offering
a course offering can have only one instructor
 an instructor can teach many course offerings
 a course may not be offered at all (0 offerings)

4
RELATIONSHIP CARDINALITY
Relationships have both a minimum and
maximum cardinality
 Minimum cardinality

least possible number of entities involved
 e.g., course may not be offered at all (0)


Maximum cardinality
most possible number of entities involved
 e.g., Course may be offered many times (M)

5
COLLEGE EXAMPLE
Max
Min
0
Course
Many
Offering
Name
Number
Credits
Has
Section #
Room
Time
1
Enroll
Teaches
Student
Last name
First name
Student ID
Faculty
Last name
First name
Faculty ID
6
GROUP
BREAK!!!
Course
Name
Number
Credits
Offering
Has
Enroll
Section #
Room
Time
Teaches
Student
Last name
First name
Student ID
Faculty
Last name
First name
Faculty ID
7
GROUP
BREAK!!!
Course
Name
Number
Credits
Offering
Has
Enroll
Section #
Room
Time
Teaches
Student
Last name
First name
Student ID
Faculty
Last name
First name
Faculty ID
8
CARDINALITY CLASSIFICATIONS

Mandatory


Min cardinality >= 1
Example
An offering must have a course
 An offering must have a faculty instructor

Course
Name
Number
Credits
Offering
Has
Section #
Room
Time
9
CARDINALITY CLASSIFICATIONS

Optional


Min cardinality = 0
Example
A faculty member does not need to teach
any offerings
 A student does not need to take any offerings

Faculty
Last name
First name
Faculty ID
Offering
Teaches
Section #
Room
Time
10
CARDINALITY CLASSIFICATIONS

Single-valued (functional)


Max cardinality of 1
Example
An offering can only have 1 course
 An offering can only have 1 instructor

Course
Name
Number
Credits
Offering
Has
Section #
Room
Time
11
CARDINALITY CLASSIFICATIONS

One to Many (1-M)
max cardinality = 1 in one direction
 max cardinality > 1 in the other


Example
Faculty can teach many offerings
 Each offering must have only one teacher

Faculty
Last name
First name
Faculty ID
Offering
Teaches
Section #
Room
Time
12
CARDINALITY CLASSIFICATIONS

Many to Many (M - N)
max cardinality > 1 in one direction
 max cardinality > 1 in the other


Example
Students can enroll in many offerings
 Offering can have many students enrolled

Student
Last name
First name
Student ID
Offering
Enroll
Section #
Room
Time
CARDINALITY CLASSIFICATIONS

One to One (1 – 1)
max cardinality = 1 in one direction
 max cardinality = 1 in the other


Example

Faculty can be married to at most one other
faculty member
Married
Faculty
Last name
First name
Faculty ID
QUESTIONS
15
PRIMARY KEYS

Unique value that identifies an entity
Student -> Student ID
 Autopart -> Part #
 Employee -> SSN


Underlined in ER Diagrams
Faculty
Faculty ID
Last name
First name
16
WEAK ENTITIES

Borrows all or part of its primary key from
another entity


identification dependency
Identifying Relationship

relationship that provides part of a primary key
Diagonal Lines
Solid Line
Building
Building ID
Name
Location
Room
Contains
Room Nr
Capacity
17
ASSOCIATIVE ENTITY TYPE

Weak entity that depends on two or more entities
for its primary key
Student
Student ID
Last name
First name
Earned
Offering
Enroll
Transcript Entry
Grade
Credits Earned
Offering #
Room
Time
Records
18
1-M AND M-N RELATIONSHIPS

Can convert a M-N relationship to
two 1-M relationships
Add a associative entity type
 Replace M-N relationship with 2
identifying relationships

Student
Student ID
Last name
First name
Enroll
Offering
Offering #
Room
Time
19
1-M AND M-N RELATIONSHIPS

Can convert a M-N relationship to
two 1-M relationships
Add a associative entity type
 Replace M-N relationship with 2
identifying relationships

Student
Offering
Student ID
Last name
First name
Earned
Offering #
Room
Time
Transcript Item
Grade
Credits Earned
Records
20
RELATIONSHIPS WITH ATTRIBUTES
Relationships can have attributes too
 Most likely to occur with M-N relationships

Student
Student ID
Last name
First name
Offering
Enroll
Offering #
Room
Time
Grade Earned
21
SELF-REFERENCING RELATIONSHIPS

Relationship between members of the same
entity
Married
Faculty
Last name
First name
Faculty ID
PrereqTo
Course
Name
Number
Credits
22
QUESTIONS
23
GENERALIZATION HIERARCHIES

Entity Inheritance
sharing of attributes between supertype and subtype
 subtype inherits attributes from supertype

College People
Solid Line
Student
Major
Grad Date
College ID
Last name
First name
With Arrow
Faculty
Department
Office
24
GENERALIZATION CONSTRAINTS

Disjointness
an entity can be one and only one subtype
 College People is not disjoint

Course
Number
Name
Credits
Intership
Company
Supervisor
D
“D” for Disjoint
Lab Course
Lab Room
Lab Credits
25
GENERALIZATION CONSTRAINTS

Completeness
every entity of a supertype must also be a subtype
 the Course generalization is not Complete


some courses are neither a lab or an internship
College People
College ID
Last name
First name
Student
Major
Grad Date
C
“C” for Complete
Faculty
Department
Office
26
MORE GENERALIZATION
Multiple levels of inheritance is OK
 Generalizations can be

Disjoint
 Complete
 Both
 Neither
College People
College ID
Last name
First name

C
Student
Faculty
Major
Grad Date
International
Visa
Expiration Date
D,C
Department
Office
Domestic
SSN
Home State
27
PREGUNTAS O INQUIETUDES?
28
?
PROBLEMS WITH ER MODELS

Fan Trap
pathway between certain entity rows is ambiguous
 may exist where two or more 1-M relationships fan
out from the same entity
 e.g., Which employees work in sales?

Employees
Oversees
Managers
Heads
Departments
29
PROBLEMS WITH ER MODELS

Fan Trap
fix by restructure of diagram to
better represent reality
 e.g., Which employees work in sales?

Managers
Heads
Departments
30
Has
Employees
PROBLEMS WITH ER MODELS

Chasm Trap
relationship implied but pathway
does not always exist
 e.g., Which retailers offer T-Mobile?

Retail
Store
Has
Location
Has
31
Phone Plans
PROBLEMS WITH ER MODELS

Chasm Trap
add in the missing relationship
 e.g., Which retailers offer T-Mobile?

Offers
Retail
Store
Has
Location
Has
32
Phone Plans
ER DIAGRAM RULES

Names
All entity types, relationships and attributes
are named
 Entity type names are unique
 Attribute names are unique within entity types
and relationships
 Attribute names in a subtype do not match inherited
attribute names


Content


All entity types have a primary key
Cardinality is given for both entity types in
a relationship
33
ER DIAGRAM RULES

Connection

All entity types participate in a relationship


except those in a generalization hierarchy
Each generalization hierarchy participates in at least
one relationship

with an entity type not in the generalization hierarchy
All relationships connect two entity types
 Relationships are not connected to
other relationships
 Redundant foreign keys are not used


foreign keys are primary keys from other entities
34
ER DIAGRAM RULES

Identification Dependency
Weak entities have at least one
identifying relationship
 Identifying relationships must have least one
weak entity
 A weak entity’s min-max cardinality must be 1 for its
identifying relationship

35
QUIZ BREAK!!!

Find the errors in the following diagram
36
Motorcycle
Quantity
Style
Quantity
Order
Order
In
In
Part#
Quantity
Has
In
Distributor
Parts
Part#
Description
Quantity
Sales Rep
Supplies
Order#
Date
$Total
Dist#
Name
Address
Supplier
Employee HasJobTitle
Supplier#
Name
Address
Employee#
Name
Years
Has
Years
Has- JobTitle
Empl
Position#
Name 37
BaseSalary
Motorcycle
Quantity
Style
Quantity
Order
Order
In
In
Part#
Quantity
Has
In
Distributor
Parts
Part#
Description
Quantity
Sales Rep
Supplies
Order#
Date
$Total
Dist#
Name
Address
Supplier
Employee HasJobTitle
Supplier#
Name
Address
Employee#
Name
Years
Has
Years
Has- JobTitle
Empl
Position#
Name 38
BaseSalary
QUIZ BREAK!!!

Fix the errors in the previous diagram
39
Motorcycle
ID#
Quantity
Style
In
Order
OrderItem
In
In
Quantity
Order#
Date
$Total
Has
In
Distributor
Part#
Description
Quantity
Supplies
Parts
Dist#
Name
Address
Supplier
Employee HasJobTitle
Supplier#
Name
Address
Employee#
Name
Years
Sales Rep
Has
Years
Has- JobTitle
Empl
Position#
Name 40
BaseSalary
PREGUNTAS O INQUIETUDES?
41
?