Transcript Introduction to Oracle: Lesson 8

DATA MODELING AND
DATABASE DESIGN
Part 1
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.
Development Stages
• Creating CONCEPTUAL database model
• Making LOGICAL database design
• Performing PHYSICAL database design
Interview notes
Existing documentation
Business
Narrative
Current System Specs
Course
# Code
* Name
o Start Date
o Duration
Instructor
taught by
leads a
# id
* last name
o first name
* phone
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
Entity Relationship Modeling
• Entity
Terms
– 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
Entity Relationship Model
• Create an entity relationship diagram from
business specifications or narratives.
EMPLOYEE
#* id
*
last name
o username
Belongs to
Contains
DEPARTMENT
#* id
*
name
o region_id
• Scenario (from Department’s side)
– ". . . Assign one or more employees to a
certain department . . ."
– ". . . Some departments do not yet have
assigned employees . . ."
Entity Relationship Modeling
Conventions
ENTITY
Soft box
Singular, unique name
Uppercase
CUSTOMER
#* id
*
name
o phone
attribute
Singular name
Lowercase
Mandatory marked with
"*"
Optional marked with "o"
EMPLOYEE
assigned to
#* id
*
last name
the sales o first name
rep to
(#)* username
Unique Identifier (UID)
Primary marked with "#"
Secondary marked with "(#)"
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
ORDER
#* id
* ord_date
o ship_date
for
Degree - One or more
Optionality - May be
CUSTOMER
#* id
*
name
o
phone
the client
for
Degree - One and only one
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
• One-to-one
Degree Types
– 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
UID Bar: Example
ITEM
#* id
* price
o quantity
in
UID bar relationship is part
of the entity’s unique
identifier.
ORDER
#* id
made up * ord_date
o ship_date
of
taken by
the sales
rep for
EMPLOYEE
#* id
* last name
o first name
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.
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.
Reading ERD’s
COURSE
# code
* duration
o fee
included
in
includes
PROGRAM
# code
# start date
is taught by
is the
teacher of
INSTRUCTOR
# id
# last name
# first name
o hire date