Transcript PowerPoint Presentation - Entity/Relationship Modeling (9/6/00)

Entity-Relationship Design
Information Level Design
TOP DOWN DATA ANALYSIS
Computer systems are extremely
complicated and cannot be developed
without careful planning. The most
common MIS is the Systems Development
Life Cycle. This approach is to build a
model of the information system based on
the objectives and goals it must meet.
This is called top down modeling.
The Data Life Cycle
Stage
Current
Situation
Approach
Tool
Function-Entity
Decomposition
Enterprise Model
Objective
General Scope &
Business Impact
Business Entities
General
Analysis
Detailed
Design
Coding
Conceptual E-R
Diagram
Relational
Schema
SQL
Logical Entities
Installation
Population Plan
Initialize Tables
Physical Design
Specify DBMS
Data Modeling Stages
Stage
Goal
Outcome
Business
Scope
Business
Model
IT Model
Business justification
for system
Data requirements in
business terms
High level technical
description
Database specific
design
Data Definition
specifications
Functional Database
Data Plan
CRUD Analysis
Project Organization
Enterprise Model
Conceptual Design
E-R Diagrams
Physical Design
Normalized Tables
Table Schema
SQL requirements
Populated Database
Management plan
Technology
Model
Technology
Definition
Database
System
Babysitter Service
The AITP Service Club wants to run a
babysitting service. Customers call to
request a sitter and the Club
Coordinator assigns an employee to sit
for the customer from a list of
employees available for the particular
day requested.
CRUD Analysis:
Functional Decomposition

Assign Employee






Take Call
Check Availability
Assign to Job
Contact Employee
Confirm Appointment
Determine Availability
Function-Entity Decomposition
Employee
Assign Employee
Take Call
Check Availability
R
Assign to Job
R
Contact Employee
Confirm Appointment
Determine
Availability
R
Customer
Job
U
U
R
U
R
R
U
Entity-Relationship Model
•
A logical representation of the data of
an organization or business area in
graphical form
Enterprise E-R Diagram
Employee
Customer
Job
Data Flow Diagram
Context Diagram
Request
Customer
Confirmation
Babysitter
Information
System
Availability
Assignment
Employee
Data Flow Diagram
Level 1
Request
Assignment
Confirmation
New
1.
Assign
Employee
Current
D1 | Customer
Availability
Avail
Times
Job
Assign
2.
Record
Availability
Avail
Times
D2 | Employee
D3 | Jobs
Communications Model
•
A representation of the location at
which data is stored and processed and
the communications links that connect
them.
Entity Relationship Models
A good E-R model has
One table for every entity in the business
system
 Correctly drawn relationships indicating 1-1
or 1-m cardinalities
 Optionality indicators to support needed
referential integrity

ENTITY:
A person, place, object, event, or
concept about which the
organization wishes to maintain
data.
•
•
•
Must need to store data
Must have at least two attributes
Must have at least two records
ENTITY TYPES
classes of people, objects or concepts
about which we wish to store data.



become tables in a new computer
system.
Instances are rows
Attributes are columns
ATTRIBUTE:
A description or property of a given entity
type.
•
•
•
Must depend on the entity key alone
Must contain information that we
explicitly need
Must have the same data type for all
entity occurrences
RELATIONSHIP:
.
A connection between entity instances in
different entity classes
•
•
Must specify what row connects with
what row in associated tables
Must not describe processing
LOGICAL AND PHYSICAL
COMPONENTS
Logical
Physical
Entity Class
Table
Instance
Row
Attribute
Column
Relationship
Junction Table
or Foreign Key
Primary Key
Identifier
Narrative Description

The conceptual modeling process starts
with a narrative description of the
process. This is a direct, active
depiction of what the system should do.
This is the basis of the initial data and
process models.
Discovering Entities
•
•
•
•
Entities are normally described by NOUNS
and ADJECTIVES
Entities do not change anything.
Entity occurrences are records, entity types
are files.
Reports are derived output and not entities.
Discovering Entities
•
•
•
Entities with only one attribute are usually
modeled as attributes of another entity.
Entities that have only one record are usually
modeled as a set of parameters and not as
files.
Include only files (entity types) that are
needed by a system. Extra entities require
maintenance and space that can add
considerably to the cost of a system.
Converting a text description
into an E-R model:
1. Review the conceptual description of
the business area for nouns that
describe the system.
2. Each entity type should have more
than one potential instance.
3. Each entity type should have more
than one attribute.
4.Each entity type should be relevant..
DEVELOPING E-R KEYS
Attributes are properties that describe
features of entity types. Attributes are
usually nouns that describe properties
of entity instances (like address for a
customer). Attributes become fields in
a database.
DEVELOPING E-R KEYS


Candidate keys are any attribute or
combination of attributes that uniquely
identify a record. The entire record is a
candidate key.
A Primary Key is one candidate key. A
good primary key is short and does not
change over the life of the database.
Keys
Names are normally poor primary keys. They
have multiple valid representations. Primary
keys:
1. Should not change values over the life of the
instance.
2. Should not have null values.
3. Should not be "intelligent keys". These are
keys that also describe properties of the
entity.
4. Should not be large composite keys.
Not null vs nulls allowed
Null values represent
•
•
•
inapplicable,
applicable but not known,
and applicable but not present values.
Primary keys cannot have null values.
Null values are different from zeros or
blanks
TYPES OF ATTRIBUTES:
•
•
Composite or Simple (atomic)
Single valued or Multivalued (repeating
group)
Relational database models cannot represent
multivalued attributes but objects and
structured databases can. Repeating groups
(sets of related multivalued attributes) usually
represent entities or subclasses.
Diagrams: Attributes
Entity
Key
Attribute
......
Attribute
Diagrams:
Repeating Groups
Course
SectionNum
E-R MODEL:
•
•
•
MULTI-VALUED ATTRIBUTES can be indicated
on an E-R graph by using a double line
around the bubble..
REPEATING GROUPS are stored differently in
structured models (hierarchical or network)
than in relational models.
DERIVED VALUES: cause data consistency
problems and are not normally included in a
database.
PREMIERE PRODUCTS
EXAMPLE
The Premier Products Company is a wholesale
hardware company that provides products to
customers. Each customer is served by a
salesman who processes orders. The
salesmen is paid from commissions earned on
each customer order. A customer places an
order by calling the company and contacting
the salesman. The salesman records the
ordering person, products and quantity
ordered.
PREMIERE PRODUCTS:
REPEATING GROUPS
In the Premier Products Company each
salesmen is paid from commissions earned
on each customer order. A customer places
an order by calling the company and
contacting the salesman. The salesman
records the ordering person, products and
quantity ordered. The order consists of
Customer data, Salesman data and a list of
products, price, and quantity for the products
that the customer wants delivered. The
attributes {PRODUCT, PRICE, QUANTITY}
constitute a repeating group.
Example: Order
ORDER
5103
PRODUCT
IRON
SKILLET
PRICE
17.95
19.95
QUANTITY
11
6
5110
TOASTER
IRON
57.95
17.95
4
3
Each instance of an order has several order lines. Order lines
{Description, Price, Quantity} are examples of repeating
groups.
Relationships
A relationship is a connection between
records in one table and those in
another.


Instructor assigned to class (section)
Student enrolled in class (section)
RELATIONSHIP.
Does not describe processing or change any
data. Relationship names should be passive
(ordered by).
•
•
CARDINALITY Refers to the number of records
that a relationship connects to a given child
record in a relationship.
PARTICIPATION (Optionality) Refers to whether
a record must exist in one table before a related
one is inserted into another.
Diagrams:
1:m Relationships
InstructorID
Section
CourseSection
Instructor
InstructorID
Diagrams:
m:n Relationships
CourseSection
Section
Student-Section
CourseSection
StudentID
StudentID
Student
Optionality
(Referential Integrity)
Records in a table that have a relationship
with another table may be restricted by
optionality requirements.


Relationship Optional
Relationship Mandatory (referential
integrity enforced)
Optionality
Optional (0 allowed)
0
Mandatory (1 or more required)
1
Optionality
A constraint should be mandatory only if
the relationship must be known
whenever a record is first entered.
Most relationships are optional.
Maintaining Integrity
If a parent record is deleted then an
optionality relationships can be
maintained in several ways



Cascade delete
Cascade update
Cascade null
Data