Introduction to Oracle: Lesson 8

1
DATA MODELING AND DATABASE DESIGN Part 1
2
Objectives

• Learn the stages of system development.
• Define basic types of data relationships.
• Recognize the optionality and degree of a
  relationship
• Read an entity relationship diagram.
• Translate an entity relationship diagram into set
  of table instance charts.
• Complete translation from logical to
• physical database design.

3
Development Stages

• Creating CONCEPTUAL database model
• Making LOGICAL database design
• Performing PHYSICAL database design

4
Interview notes Existing documentation Current
System Specs
Business Narrative
Course Code Name o Start Date o Duration
Instructor id last name o first name phone
taught by
leads a
5
Benefits of Entity Relationship Diagrams

• Quickly present concepts in people's minds
• Provide an easily understood graphical map of the
  system
• May be easily refined and upgraded
• Separate the information required by a business
  from the activities performed by the business

6
Entity Relationship Modeling
Terms

• Entity
• A thing of significance about which information
  needs to be known
• Examples department, employee, order
• Attribute
• Something that describes or qualifies an entity
• Examples dept_id, address, customer_id
• Relationship
• An association between two entities
• Examples region and department, customer and
  order

7
Entity Relationship Model

• Create an entity relationship diagram from
  business specifications or narratives.
• Scenario (from Departments side)
• ". . . Assign one or more employees to a certain
  department . . ."
• ". . . Some departments do not yet have assigned
  employees . . ."

EMPLOYEE id last name o username
DEPARTMENT id name o region_id
Belongs to
Contains
8
Entity Relationship Modeling Conventions
ENTITY Soft box Singular, unique name Uppercase
attribute Singular name Lowercase Mandatory
marked with "" Optional marked with "o"
CUSTOMER id name o phone
EMPLOYEE id last name o first name ()
username
assigned to
the sales rep to
Unique Identifier (UID) Primary marked with
"" Secondary marked with "()"
9
Entity Relationship Syntax

• Syntax
• Each source entity may be must be
  relationship name one and only one or more
  destination entity.
• Example
• Each ORDER must be for one and only one CUSTOMER.
• Each CUSTOMER may be the client for one or more
  ORDERs.

Mandatory - Must be
Optionality - May be
ORDER id ord_date o ship_date
CUSTOMER id name o phone
for
the client for
Degree - One or more
Degree - One and only one
10
Relationships
OPTIONALITY
Solid line represents a mandatory relationship
often called a MUST BE relation.
Dashed Line represents an optional relationship
often called a MAY BE relation.
DEGREE
One and only One --- ONE-TO-ONE
One or More (Many) --- ONE-TO-MANY
11
Degree Types

• One-to-one
• Have a degree of one and only one in both
  directions.
• Are rare.
• Example Computer and Motherboard
• One-to-many
• Have a degree of one or more in one direction and
  a degree of one and only one in the other
  direction.
• Are very common.
• Example Customer and Order.
• Many-to-many
• Have a degree of one or more in both directions.
• Are resolved with an intersection entity.
• Example Reader and Magazine

12
UID Bar Example
ITEM id price o quantity
ORDER id ord_date o ship_date
in
made up of
taken by
UID bar - relationship is part of the entitys
unique identifier.
the sales rep for
EMPLOYEE id last name o first name
13
UID Bars
A Unique Identifier bar indicates that the
relationship participates in an entities UID.
In other words the UID of one entity becomes
part of the composite UID of the other entity as
well as a foreign key.
14
UID Bars

• Rules
• 1) The UID Bar is always at the many end of a one
  to many relationship.
• 2) The entity at the many end always receives the
  UID of the other entity.

15
Reading ERDs
COURSE code duration o fee
INSTRUCTOR id last name first name o hire
date
is taught by
is the teacher of
included in
includes
PROGRAM code start date