ERD CHECKLIST

ERD CHECKLIST

Making a good ERD

1

12/06/2021 2

Correctness

 Validation

◦ Building the right model?

◦ All the requirements modeled?

◦ Nothing missing or added?

 Verification

◦ Building the model right?

◦ Completed with correct

notation?

3

ERD Diagram Checklist

 Completeness

◦ Only enduring and necessary real-world concepts

 Ask: Is this important to remember?

◦ Every thing is defined once (and

only once)

4

ERD Diagram Checklist



Entities – Nouns

◦ People,

Departments,

Roles, Products,

Inputs, Outputs

5

ERD Diagram Checklist



You might be an entity…

◦ If you have more than one attribute

◦ If you have more than one instance

System Admin

6

ERD Diagram Checklist



You might be an entity…

◦ If you are not a report

 (Next Day’s Appointments)

◦ If you are not a transaction

 (Balance Inquiry)

7

ERD Diagram Checklist



Weak Entity only

◦ If one identifying relationship exists

◦ If no primary key exists

◦ Ternary

relationship

ERD Diagram Checklist



Attributes

◦ Look at all input forms and reports

◦ Fields and Column headings should be considered

◦ Eliminate calculated values

 Might be a derived attribute

◦ Eliminate transaction details that don’t need to be saved

 Checksum, CAPTCHA

8

9

ERD Diagram Checklist



Multivalued Attribute only

◦ If attribute is simple

◦ If cardinality/participation is not important

phon

e numb

er Area

code phone

person

dog

How many persons per dog?

10

ERD Diagram Checklist



Relationships – Verbs

◦ Actions,

associations, connections,

specializations, generalizations, aggregations

◦ Is-a, has-a

EXTREME(LY EFFICIENT)

EXTERMINATORS

11

Extreme(ly Efficient) Exterminators

 Staff need reports on all material used in service calls by week and month

 They also need to know all service calls by employee

 When employees go on a service call, they need the customer’s service history,

address and time of the appointment

 After the appointment, employees update the service record

 All customers must be unique

 Customers change address, so service history must be linked to both.

 There can be multiple services per

appointment ¹²

CUSTOME

R ADDRESS

SERVICE APPT

MAT’L/

SUPPLY EMPLOYEE

mak e

for hav

e

get

provid use

e ema

il

nam e

date

time

stree t

city

state

zip

id

amt id

nam e

type

amt

n

n n

m m

n m

n

1

n

n 1

hav e

n m

APPT mak

e

for hav

e

14

ERD modeling errors



Showing cardinality but not participation

◦ Or vice versa



Mixed notation with min/max

constraints

15

Other ERD modeling errors



Entities without

◦ Attributes

◦ Primary key



Unnecessary relationships



Ternary relationships



Duplicated Attributes

◦ address

◦ “foreign keys”

16

ERD Diagram Checklist



What if an entity has the same attributes as another entity?

 Same Entity, Different Name?

 Generalization?

Customer

Client

Employee

Technician

U

17

ERD Diagram … what if?



If attributes or relationships are common

◦ Generalizations



If attribute or relationship is unique

◦ Specializations

Technician

U

Tgrad e

name ^dephas

t

fixe s

General

Special

THE ENHANCED ENTITY- RELATIONSHIP (EER)

MODEL

Chapter 8

Chapter 8 Outline



Subclasses, Superclasses, and Inheritance



Specialization and Generalization



Constraints and Characteristics of Specialization and Generalization Hierarchies



Modeling of UNION Types Using

Categories

20

Subclasses and Superclasses

EMPLOYEE

SECRETARY

ENGINEER TECHNICIAN

SALARIED_EMPLOYEE HOURLY_EMPLOYEE

Every entity that is a member of one of these subgroupings is also an employee

Superclasses: EMPLOYEE Subclasses: SECRETARY, ENGINEER,

TECHNICIAN,

SALARIED_EMPLOYEE,

HOURLY_EMPLOYEE An entity type may have additional meaningful subgroupings of its entities

21

Why class/subclass relationships and

specializations

◦ A subclass is defined in order to group the entities to which these attributes apply.

◦ The members of the subclass may still share the

majority of their attributes with the other members of the superclass.

All employees have names, SSN, Birthdates, Addresses

Secretaries also have Typing Speed Engineers also have EngineerType Technicians also have Tgrade

22

Attribute Inheritance

EMPLOYEE

SECRETARY

TECHNICIAN

 

d Fnam

e

Lnam

e SSN

TypingSpeed

TGrade

ENGINEER

EngType



Addr

SECRETARY

Fname, Lname, SSN, Addr TypingSpeed TECHNICIAN

Fname, Lname, SSN, Addr, TGrade ENGINEER

Fname, Lname, SSN, Addr, EngType EMPLOYEE

Fname, Lname, SSN, Addr

23

Why class/subclass relationships and specializations



Some relationship types may be participated in only by entities that are members of the

subclass.

All Employees work for Departments Managers are assigned to Projects Hourly Employees belong to Trade

Unions

24

Relationship Inheritance

EMPLOYEE: WORKS SECRETARY: WORKS TECHNICIAN: WORKS ENGINEER: WORKS

MANAGER: WORKS, MANAGES SALARIED_EMP: WORKS

HOURLY_EMP: WORKS, BELONGS_TO

SECRETARY

d

TECHNICIAN ENGINEER

d

SALARIED_EMP

HOURLY_EMP MANAGER

 EMPLOYEE

PROJECT TRADE_UNION

DEPARTMENT

MANAGES BELONGS_TO

WORKS

TypingSpeed

TGrade EngType Fname

Lname SSN

Addr

25

EER Notation

Esuper

…

E1

…

E2

…

  Specific

attribute s

Generalizati on

Specializatio n

26

Specialized Instances



A superclass/subclass relationship may resemble a 1:1 relationship at the instance level.



The main difference is that

◦ In a 1:1 relationship two distinct entities are related,

◦ In a superclass/subclass relationship

the entity in the subclass is the same

real-world entity as the entity in the

superclass but playing a specialized

role.

28

Subclasses vs. Superclasses



The set of entities in each subclasses is a subset of the entities that belong to

EMPLOYEE

◦ Each is called a subclass of EMPLOYEE

◦ EMPLOYEE is the superclass for each of these subclasses



The relationship between a superclass and any one of its subclass is called a

superclass/subclass or class/subclass relationship.

◦ EMPLOYEE/SECRETARY and

EMPLOYEE/TECHNICIAN are two class/subclass relationships.

Type inheritance



The type of an entity is defined by the attributes it possesses and the relationship types which it

participates.



An entity that is a member of a subclass inherits

◦ all the attributes of the entity as a

member of the superclass, as well as

◦ all the relationships in which the

superclass participates.

30

Generalization vs Specialization



Top down or bottom up?



Decision as to which process,

generalization or specialization, is

often subjective.

Specialization Process



Specialization

◦ Process of defining a set of subclasses of an entity type

◦ Defined on the basis of some

distinguishing characteristic of the entities in the superclass



Subclass can define:

◦ Specific attributes

◦ Specific relationship types

Generalization



Reverse process of abstraction



Bottom-up conceptual synthesis.

◦ Start with subclasses and then

define superclasses by successive generalization.



Suppresses the difference among several entity types, identifying their common features, and

generalize them into a single

superclass of which the original

types are special subclasses.

33

Generalization (cont.)

CAR

LicensePlateNo

Price MaxSpee

VehicleIDd

NoOfPassenger s

TRUCK

LicensePlateNo

Price Tonnage

VehicleID

NoOfAxles

VEHICLE

LicensePlateNo Price

VehicleID

 

d MaxSpee CAR

d

NoOfPassenger s

TRUCK Tonnage NoOfAxles

34

Generalization (cont.)



We can view {CAR, TRUCK} as a specialization of VEHICLE



Alternatively, we can view

VEHICLE as a generalization of

CAR and TRUCK

35

When one instance is in several classes…

 Deleting an entity from a superclass implies that it is

automatically deleted from all the

subclasses to which it belongs

• Inserting an entity in a superclass implies that the entity is mandatorily inserted in all

applicable subclasses.

• Inserting an entity in a superclass of a total specialization implies that the entity is

mandatorily inserted in at least one of the subclasses of the specialization.