DATA MODELING USING

THE ENTITY-RELATIONSHIP
MODEL

1
DATABASE DESIGN PROCESS

2
ER MODEL CONCEPTS
 Entities
 specific objects in the mini-world.
 E.g. EMPLOYEE John , Research DEPARTMENT

 Attributes
 They are properties used to describe an entity.
 Each attribute has a data type
 E.g. integer, string, subrange, enumerated
type, …

3
TYPES OF ATTRIBUTES
 Simple
 Each entity has a single atomic value for the
attribute. For example, SSN or Sex.
 Composite
 The attribute is composed of several components.
For example:
 Address(House#, Street, City, State, ZipCode, Country),
 Name(FirstName, MiddleName, LastName).

 Multi-valued
 An entity may have multiple values for that attribute.
For example:
 PreviousDegrees of a STUDENT.
 Stored and Derived
 Null Values
4
TYPES OF ATTRIBUTES
 Complex attribute - The composite and multi-
valued attributes may be nested arbitrarily to
any number of levels,
 Forexample, PreviousDegrees of a STUDENT is a
composite multi-valued attribute denoted by
{PreviousDegrees (College, Year, Degree, Field)}
 Multiple PreviousDegrees values can exist
 Each has four subcomponent attributes:
 College, Year, Degree, Field

5
EXAMPLE OF A COMPOSITE
ATTRIBUTE

6
ENTITY TYPES AND KEY ATTRIBUTES
 Entities with the same basic attributes are
grouped into an Entity type.
 For example, the entity type EMPLOYEE
and PROJECT.

 Key attribute
 For example, SSN of EMPLOYEE.

7
KEY ATTRIBUTES
 A key attribute may be composite.
 VehicleTagNumber is a key of the CAR
entity type with components (Number,
State).
 An entity type may have more than one key.

 The CAR entity type may have two keys:

 VehicleId (popularly called VIN)
 VehicleTagNumber (Number, State), aka license plate

number.
 Each key is underlined

8
ENTITY TYPE CAR WITH TWO KEYS
AND A CORRESPONDING ENTITY
SET

Entity set is the

current state of
the entities that
is stored in the
database

9
INITIAL DESIGN OF ENTITY TYPES:
EMPLOYEE, DEPARTMENT, PROJECT, DEPENDENT

Entity types in the

COMPANY database:
DEPARTMENT
PROJECT
EMPLOYEE
DEPENDENT

10
REFINING THE INITIAL DESIGN BY
INTRODUCING RELATIONSHIPS
 Some aspects in the requirements will be
represented as relationships

 ER model has three main concepts:

 Entities(and their entity types and entity sets)
 Attributes (simple, composite, multivalued)
 Relationships (and their relationship types and
relationship sets)

11
RELATIONSHIPS
 A relationship relates two or more distinct
entities with a specific meaning.
 For example, EMPLOYEE John works on the
ProductX PROJECT,
 EMPLOYEE Franklin manages the Research
DEPARTMENT.
 Relationships of the same type are grouped
into a relationship type.
 Degree of a relationship type is the no of
participating entity types.
 Both MANAGES and WORKS_ON are binary 12
relationships.
RELATIONSHIP INSTANCES OF THE WORKS_FOR N:1
RELATIONSHIP BETWEEN EMPLOYEE AND DEPARTMENT

13
RELATIONSHIP INSTANCES OF THE M:N WORKS_ON
RELATIONSHIP BETWEEN EMPLOYEE AND PROJECT

14
RELATIONSHIP TYPE VS.
RELATIONSHIP SET
 Relationship Type:
 Isthe schema description of a relationship
 Identifies the relationship name and the
participating entity types
 Also identifies certain relationship constraints
 Relationship Set:
 The current state of a relationship type

15
COMPANY DATABASE: RELATIONSHIPS
 Six relationship types are identified
 All are binary relationships( degree 2)
 WORKS_FOR (between EMPLOYEE, DEPARTMENT)
 MANAGES ( between EMPLOYEE, DEPARTMENT)
 CONTROLS (between DEPARTMENT, PROJECT)
 WORKS_ON (between EMPLOYEE, PROJECT)
 SUPERVISION (between EMPLOYEE (as subordinate),
EMPLOYEE (as supervisor))
 DEPENDENTS_OF (between EMPLOYEE, DEPENDENT)

16
ER DIAGRAM – RELATIONSHIP TYPES ARE:
WORKS_FOR, MANAGES, WORKS_ON, CONTROLS, SUPERVISION, DEPENDENTS_OF

17
WEAK ENTITY TYPES
 An entity that does not have a key attribute
 A weak entity must participate in an identifying
relationship type with an owner or identifying entity
type
 Entities are identified by the combination of:
 Partial key of the weak entity type
 The particular entity they are related to in the
identifying entity type

 Example:
 A DEPENDENT entity is identified by
 the dependent’s first name (partial key), and
 the specific EMPLOYEE with whom the dependent is
related
18
CONSTRAINTS ON RELATIONSHIPS
 Constraints on Relationship Types (ratio
constraints)
 Cardinality Ratio (specifies maximum participation)
 One-to-one (1:1)
 One-to-many (1:N) or Many-to-one (N:1)

 Many-to-many (M:N)

 Existence Dependency Constraint (specifies

minimum participation) (also called participation
constraint)
 zero (optional participation, not existence-dependent)
 one or more (mandatory participation, existence-

dependent)
19
MANY-TO-ONE (N:1) RELATIONSHIP

20
MANY-TO-MANY (M:N)
RELATIONSHIP

21
RECURSIVE RELATIONSHIP TYPE
 EMPLOYEE participates twice in two distinct
roles:
 supervisor(or boss) role
 supervisee (or subordinate) role
 Each relationship instance relates two
distinct EMPLOYEE entities:
 One employee in supervisor role
 One employee in supervisee role

22
A RECURSIVE RELATIONSHIP
SUPERVISION`

23
RECURSIVE RELATIONSHIP TYPE IS: SUPERVISION
(PARTICIPATION ROLE NAMES ARE SHOWN)

24
ATTRIBUTES OF RELATIONSHIP TYPES
 A relationship type can have attributes:
 Forexample, HoursPerWeek of WORKS_ON
 A value of HoursPerWeek depends on a particular
(employee, project) combination

 Most relationship attributes are used with

M:N relationships
 In 1:N relationships, they can be transferred to the
entity type on the N-side of the relationship

25
EXAMPLE ATTRIBUTE OF A RELATIONSHIP
TYPE:

Participation
Constraint : Total vs
partial.

Cardinality Ratio:
1:N, 1:1, N:M

26
STEPS TO DRAW AN ER DIAGRAM
 Get problem description
 Define Entities

 Add Attributes

 Specify Key, multiple, composite attributes

 Add Relations

 Specify Cardinality, total/partial relations

 Iterate
STEPS TO DRAW AN ER DIAGRAM
 Essential to further design, but often given
little care:
 Is an entity a weak entity (key?)

 Multivalued attributes

 Derived attribute

 Total/partial participation

 Cardinality ratio
EXAMPLES
 An employee may have several skills; and
 A particular skill may be held by several

employees
EXAMPLE
 An employee may have no more than one
locker; and
 A locker may only be accessible by one

employee
EXAMPLE
 An employee may only work for one
department; and
 A department has several employees
EXAMPLE
 An employee can have many colleagues
 A building has many apartments
 An apartment exists in only one building
 PROBLEM 1
Draw E\R model for the university database with the following
requirements
a) The university keeps track of each student's name, rollno, SSN, current
address and phone, permanent address and phone, birthdate, sex, class
(freshman, sophomore, ..., graduate), major department, minor
department (if any), and degree program (B.A., B.S., ..., Ph.D.). Some
user applications need to refer to the city, state, and zip of the
student's permanent address, and to the student's last name. Both SSN
and Rollno have unique values for each student.
b) Each department is described by a name, code, office number, office
phone, and college. Both name and code have unique values for each
department.
c) Each course has a course name, description, course number, number of
semester hours, level, and offering department. The value of course
number is unique for each course.
d) Each section has an instructor, semester, year, course, and section
number. The section number distinguishes different sections of the
same course that are taught during the same semester/year; its values
are 1, 2, 3, ...; up to the number of sections taught during each
semester.
e) A grade report has a student, section, letter grade, and numeric grade
ALTERNATIVE (MIN, MAX) NOTATION FOR
RELATIONSHIP STRUCTURAL CONSTRAINTS:
 Specifies that each entity e in E participates in at least
min and at most max relationship instances in R
 Default(no constraint): min=0, max=n
 Must have min  max, min  0, max  1
 Derived from the knowledge of mini-world constraints

35
(MIN , MAX) NOTATION FOR
RELATIONSHIP CONSTRAINTS

A department has exactly one manager and an employee can manage at

most one department.

Specify (0,1) for participation of EMPLOYEE in MANAGES

Specify (1,1) for participation of DEPARTMENT in MANAGES

Read the min,max numbers next to the entity type and looking away
from the entity type

36
THE (MIN,MAX) NOTATION FOR
RELATIONSHIP CONSTRAINTS
An employee can work for exactly one department but a department can
have any number of employees.

Specify (1,1) for participation of EMPLOYEE in WORKS_FOR

Specify (1,n) for participation of DEPARTMENT in WORKS_FOR

37
MIN, MAX - NOTATION

38
NOTATION FOR ER DIAGRAMS

39
RELATIONSHIPS OF HIGHER DEGREE
 Relationship types of degree 2 are called
binary
 Relationship types of degree 3 are called

ternary and of degree n are called n-ary

 Constraints are harder to specify for higher-

degree relationships (n > 2) than for binary

relationships

40
EXAMPLE OF A TERNARY RELATIONSHIP
EXAMPLE OF A TERNARY
RELATIONSHIP
EXAMPLE OF A TERNARY
RELATIONSHIP
EXAMPLE 2 OF A TERNARY
RELATIONSHIP

44
N-ARY RELATIONSHIPS (N > 2)
 Three binary relationships represents
different information than a single ternary
relationship
 In some cases, a ternary relationship can be

represented as a weak

45
EXAMPLE 3 OF A TERNARY
RELATIONSHIP

46
N-ARY RELATIONSHIPS (N > 2)
 If a particular binary relationship can be
derived from a higher-degree relationship at
all times, then it is redundant

 For example, the TAUGHT_DURING binary

relationship can be derived from the ternary
relationship OFFERS (based on the meaning
of the relationships)

47
DISPLAYING CONSTRAINTS ON
HIGHER-DEGREE RELATIONSHIPS
 Displaying a 1, M, or N indicates
1 indicates that an entity can participate in at
most one relationship instance that has a
particular combination of the other participating
entities
 M or N indicates no constraint

 (min, max) constraints can be displayed on the

edges – however, they do not fully describe the
constraints

48
 In general, both (min, max) and 1, M, or N are
needed to describe fully the constraints
 CARDINALITY FOR TERNARY
RELATIONSHIP

Constraint: For a particular project-part combination, only one

supplier will be used (only one supplier supplies a particular part
to a particular project).

49
DISPLAYING CONSTRAINTS ON HIGHER-
DEGREE RELATIONSHIPS
PROBLEM 2: ER FOR NOTOWN RECORDS
 Each musician has an SSN, name, address, phone. Poor
musicians often share the same address, and no address has
more than one phone.
 Each instrument that is used in songs recorded at Notown
has a name (e.g., guitar, flute) and a musical key (e.g., C, B-
flat).
 Each album has a title, a copyright date, a format (e.g., CD
or MC), and an album identifier.
 Each song recorded at Notown has a title and an author.
 Each musician may play several instruments, and an
instrument may be played by several musicians.
 Each album has a number of songs on it, but no song may
appear on more than one album.
 Each song is performed by one or more musicians, and a
musician may perform a number of songs.
 Each album has exactly one musician who acts as its
producer. A musician may produce several albums, of
ALTERNATIVE DIAGRAMMATIC
NOTATION
 ER diagrams is one popular example for
displaying database schemas
 UML class diagrams is representative of

another way of displaying ER concepts

52
UML CLASS DIAGRAMS
 Represent classes (similar to entity
types) as large boxes with three
sections:
 Top section includes entity type (class)
name
 Second section includes attributes
 Third section includes class operations
(operations are not specified in ER model)
 Composite attribute is modeled as a
structured domain
 Name of EMPLOYEE
 Multivalued attribute is modeled as a
separate class
 53
LOCATION class
UML CLASS DIAGRAMS
 Relationship types are called associations in
UML
 Relationship instances are called links
 Relationships are represented as lines
connecting the classes
 The (min, max) notation is used to specify
relationship constraints (multiplicities)
 multiplicities are placed on the opposite
ends of the relationship
 A recursive relationship is called a reflexive
association in UML
 Weak entities can be modeled using the
construct called qualified association
 UML CLASS DIAGRAM FOR
COMPANY DATABASE SCHEMA

Qualified
association
for weal
entity

55
OTHER ALTERNATIVE
DIAGRAMMATIC NOTATIONS

56
CHAPTER SUMMARY
 ER Model Concepts: Entities, attributes,
relationships
 Constraints in the ER model

 Using ER in step-by-step conceptual schema

design for the COMPANY database

 ER Diagrams - Notation

 Alternative Notations – UML class diagrams,

others

57