Entity Relationship Diagrams

1
Entity Relationship Diagrams
2
Database Development Process
Business Information Requirements
Business view
Systems view
Logical Database Design
Physical Database Build
Operational Database
3
Terminology
LOGICAL
CONCEPTUAL
(Business view)
(Systems view)
DESIGN
ANALYSIS
ENTITY
TABLE
RELATIONSHIP
FOREIGN KEY
ATTRIBUTE
COLUMN
PRIMARY KEY
UNIQUE IDENTIFIER
UNIQUE KEY
4
Information Models

• Organize thought processes
• Accurately model business data
• Communicate with stakeholders
• Analyze the scope
• Provide sound basis for system design

5
CS 450 ERD Conventions
attribute
Cardinality (degree)
empnum
key
M
1
Works For
EMPLOYEE
DEPARTMENT
entity
relationship
Participation Requirements (optionality)
6
Main Elements of an Oracle ERD
Relationship
Unique Identifier
CUSTOMER customer number first name
last name o other initials
held by
Entity
MEMBERSHIP
Attributes
the holder of
7
Hardware and Software Independence
ENTITY RELATIONSHIP MODEL
Agreement Term
Product
NETWORK DATABASE
HIERARCHICAL DATABASE
Agreement
Agreement
Product x
Product y
Item 1
Item 2
RELATIONAL DATABASE
Code
Date
Customer
Code
Description
Agreement
Quantity
Number
Product
8
Relationship Definitions

• The way one entity 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

9
Relationship Diagramming Conventions

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

Mandatory - must be
Optional - may be

• Degree (Maximum cardinality)

One or more
One and only one
10
Relationship Diagramming Conventions
many (crows foot)
optional
one
mandatory
11
Relationship Syntax
Entity 2
Entity 1
must be or may be
one or more or one and only one
relationship name
Each
entity 1
entity 2
Object entity
Name
Subject entity
Optionality
Degree
12
How do you read this?
assigned to
EMPLOYEE
DEPARTMENT
13
How do you read this?
assigned to
EMPLOYEE
DEPARTMENT
Each EMPLOYEE must be assigned to one and only
one DEPARTMENT
14
How do you read this?
EMPLOYEE
DEPARTMENT
responsible for
15
How do you read this?
EMPLOYEE
DEPARTMENT
responsible for
Each DEPARTMENT may be responsible for one or
more EMPLOYEES
16
Full Reading of Relationship
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
17
How do you read this?
enrolled in
COURSE
STUDENT
taken by
18
How do you read this?
Each STUDENT may be enrolled in one or more
COURSES
Each COURSE may be taken by one or more STUDENTS
19
Analyzing and Modeling Relationships

• Determine the existence of a relationship
• Name each direction of the relationship
• Determine the degree of each direction of the
  relationship
• Determine the optionality of each direction of
  the relationship
• Read the relationship aloud to validate it

20
Oracles Layout Guidelines
Parent Entity (1)
Child Entity
Dead Crows Fly East !
Parent Entity (2)
21
Attributes
22
Finding Attributes
Is this attribute really needed ?
Beware of obsolete requirements from previous
systems
Beware of derived data
23
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
24
Meaningful Components
PERSON
PERSON
last name first name
name
Break down aggregate attributes
25
Verify for Single Value
Can an attribute have more than one value for
one instance of the entity?
Yes, more than one item may be rented at a time.
An entity is missing.
26
Attributes Which have Attributes
TITLE
Does information need to be stored about any of
the attributes?
product code title description review details
Yes, review details. An entity is missing.
27
Finding Common or Derived Data

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

Derived attributes are redundant and can lead to
inconsistent values
28
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

29
Attribute Optionality
EMPLOYEE

badge num

first name

last name
o
title
o
weight
30
Attribute Details and Volumes
Attribute - Engine Size
Format Type Number Maximum
length 4 Average length 4 Decimal
place 1 Unit of measure cc Allowable
values 900,1000,1500,1800,2000 Volume Initial
100
31
Using a Domain
Movie
Mono
AUDIO
Stereo
MON STE SUR
Audio
Game
Surround
Sound
32
Creating an Entity
For DB Planning
33
Adding Details
34
Domains
35
Creating Relationships
36
Diagrammer Behavior

• Definition changes are stored in Repository even
  if the diagram is not saved.
• Saving a diagram saves the layout.
• Diagrams are not synchronized automatically.
• Requerying is your responsibility.
• Diagrams can be exported to, for example, Word
  documents.

37
Editing in the RON