Entity Relationship Modelling

1
Entity Relationship Modelling

• Rosemary Rock-Evans'
• Diagramming Technique
• Relational Database Design

2
Database Development Process
Business Requirements
Information
Process
Cross-checking
ER model, entity definition
Function hierarchy, function definition
Module (screen, report, menu, PL/SQL utility)
Table, index, view
Cross-checking
Application
Database
Operational System
3
Database Development Process
Business Information Requirements
Business view
Systems view
Logical Database Design
Physical Database Build
Operational Database
4
Terminology
LOGICAL
CONCEPTUAL
(Business view)
(Systems view)
5
Goals of Conceptual Data Modeling

• Definition of scope
• Identification and definition of concepts
• Ease communication between participants
• Ease of development
• Establish robust foundation for further
  development
• Integration of multiple applications

6
Main Elements of an ER Diagram
Relationship
Unique Identifier
CUSTOMER customer number first name
last name o other initials
held by
EntityType
MEMBERSHIP
Attributes
the holder of
COMPANY
Super-type
held by
SUPPLIER
Sub-type
the holder of
OTHER COMPANY
composed of
Recursive Relationship
part of
Exclusive Arc
7
Entity and Entity Type Identification

• An Entity is something of significance to the
  business and about which some information is
  needed
• Physical object product, employee, thing, etc.
• Logical object inquiry, order, schedule, etc.
• An entity has a noun
• An entity has a set of attributes
• An Entity Class A collection of entities
  described with the same set of attributes
• An Entity Type is a "template" for entities
• Entities are instances of entity types

8
Attribute Identification

• Specific pieces of information which need to be
  known
• Attributes have nouns
• Attributes have data types

9
Diagramming Entities

• Soft box
• Singular, unique name in uppercase
• Optional synonym name
• Attribute names in lower case

EMPLOYEE
name
date of birth
COMPANY (CLIENT)
DEPARTMENT
MEMBERSHIP
10
Entity Instances
Head office
Personnel
Finance
Sales
EMPLOYEE
DEPARTMENT
11
Identifying a Unique Instance
12
Relationship Definitions

• The way one entity type relates to another
• The business rules that link together business
  information needs
• What one thing has to do with another
• A named association between entities

13
Bi-directional Relationships
SMT COURSE
SMT COURSE
DMDD COURSE
INSTRUCTOR
COURSE
14
Diagramming Conventions

• A line between two entities
• Lower case relationship name
• Optionality (minimum cardinality)

Mandatory - must be
Optional - may be

• Cardinality (maximum)

One or more
One and only one
15
Diagramming Conventions
many (crows foot)
optional
one
mandatory
16
Relationship Syntax
must be or may be
one or more or one and only one
relationship role
Each
entity 1
entity 2
Object entity
Subject entity
Optionality
Cardinality
Name
17
Validation - in class practice
assigned to
EMPLOYEE
DEPARTMENT
18
Validation - in class solution
assigned to
EMPLOYEE
DEPARTMENT
Each EMPLOYEE must be assigned to one and only
one DEPARTMENT
19
Validation - in class practice
EMPLOYEE
DEPARTMENT
responsible for
20
Validation - in class solution
EMPLOYEE
DEPARTMENT
responsible for
Each DEPARTMENT may be responsible for one or
more EMPLOYEES
21
Validation - in class solution
assigned to
EMPLOYEE
DEPARTMENT
EMPLOYEE
DEPARTMENT
responsible for
Each EMPLOYEE must be assigned to one and only
one DEPARTMENT
Each DEPARTMENT may be responsible for one or
more EMPLOYEES
22
Relationship Types
Many-to-One
Many-to-Many
One-to-One
23
Attributes
24
Finding Attributes
Is this attribute really needed ?
Beware of obsolete requirements from previous
systems
Beware of derived data
25
Attribute Diagramming Conventions

• Inside the entity's soft box
• Singular
• Lowercase

EMPLOYEE
badge num
first name
last name
payroll num
date of birth
employment status
26
Attributes Which Have Attributes
TITLE
product code title description review details
Does information need to be stored about any of
the attributes?
Yes, review details. An separate entity must be
added.
27
Attributes Which have Attributes
TITLE
product code title description review details
Does information need to be stored about any of
the attributes?
Yes, review details. An separate entity must be
added.
REVIEW
TITLE
product code title description review details
author comment date recorded
28
Finding Common or Derived Data

• Count
• Total
• Maximum, Minimum, Average
• Calculation

Derived attributes are redundant and can lead to
inconsistent values
29
Attribute Optionality
Mandatory Attributes

• A value must be stored for each entity instance
• Tagged with

Optional Attributes

• A value may be stored for each entity instance
• Tagged with o

30
Attribute Optionality
EMPLOYEE

badge num

first name

last name
o
title
o
weight
31
Unique Identifier Definition
Each entity instance must be able to be uniquely
identified
A combination of attributes or relationships that
serve to identify a specific instance of an
entity.
32
Simple Unique Identifier
CUSTOMER
customer num
Single attribute

Tag the UID with
33
Compound UID - Composite
ACCOUNT
BANK
bank num acc num
bank num
Use to indicate that the attribute is part of
the entitys UID
34
Compound UID - Composite
ACCOUNT
BANK
bank num
acc num
Use a UID bar to indicate that a relationship is
part of the entitys UID
35
Resolving Many to Many Relationships
From this diagram, can you tell which supplier
instance provides item Casablanca?
supplier of
TITLE
SUPPLIER
prod code name
supplier no name
supplied by
In which entity would you store the attribute
purchase price?
36
Intersection Entities
CATALOG ITEM
purchase price
for
for
available as
supplier of
SUPPLIER
TITLE
TITLE
prod code name
supplier no name
37
Modeling Recursive Relationships
...but Im HIS manager!
...and mine
...hes my manager
manager of
EMPLOYEE
managed by
38
Modeling Hierarchical Data
Company
Division
Department
Team
39
Hierarchies as Recursive Relationships
TEAM
name
made up of
DEPARTMENT
name
ORGANIZATIONELEMENT
name type
DIVISION
within
name
COMPANY
name
40
Network Structures
a part of
COMPONENT
identifier
made up of
41
Network Structures
a part of
COMPONENT
identifier
made up of
made up of
COMPONENT
COMPONENT
identifier
identifier
a part of
42
Subtypes of Entities
COMPANY
COPY
acquired from

id name telephone num
inventory num o condition


SUPPLIER

supplier num sales contact
the source of

held by

the holder of
OTHER
o
43
Moving into Database Design
Business Information Requirements
Business view
Logical Database Design
Systems view
Physical Database Build
Operational Database
44
Creating the Database Design

• Map simple entities to tables
• Map attributes to columns, and document sample
  data
• Map unique identifiers to primary keys
• Map relationships to foreign keys

45
Summary
CUSTOMER customer num first name
last name o other initials
0NF
1NF
2NF
3NF
held by
MEMBERSHIP
the holder of
cus num cus fname cus lname cus initial mem
num mem st date mem exp date
num start date expire date
COMPANY
held by
SUPPLIER
the holder of
OTHER COMPANY
composed of
part of
NORMALIZATION
ENTITY RELATIONSHIP MODELING
MEMBERSHIPS
mem_num mem_st_date
mem_expire_date mem_cus_num FK
DATABASE DESIGN