Transcript Six Blind Men and the Elephant

Data Modeling Supplement
Six Blind Men and the Elephant
Basic ERD Symbology
Entity
Relationship
Attribute
Multivalued
Attribute
Associative
Entity
Typical Business Relationships
are purchased by
PRODUCTS
CUSTOMERS
supply
SUPPLIERS
PRODUCTS
contain
ORDERS
PRODUCTS
are placed by
ORDERS
CUSTOMERS
are assigned to
EMPLOYEES
OFFICES
are sold in
PRODUCTS
STORES
Relationship Complexities
(a)
EMPLOYEE
Each EMPLOYEE must be assigned to one
and only one OFFICE.
assigned to
OFFICE
assigned to
Each OFFICE may be assigned to one and
only one EMPLOYEE.
(b)
EMPLOYEE
Each EMPLOYEE must be assigned to one
and only one DEPARTMENT.
assigned to
DEPARTMENT
in charge of
Each DEPARTMENT may be in charge of
zero or more EMPLOYEEs.
(c)
STUDENT
Each STUDENT may be registered for
zero, one, or many COURSEs.
registered for
COURSE
taken by
Each COURSE may be taken by zero, one,
or many STUDENTs.
Unary Relationship
PERSON
COURSE
married to
is a
prerequisite for
has as
a prerequisite
Each PERSON may be married to one and only one PERSON.
Each COURSE may have zero, one, or many prerequisite COURSEs.
Each COURSE may be a prerequisite for zero, one, or many COURSEs.
Each EMPLOYEE must be managed by one and only one EMPLOYEE.
Each EMPLOYEE may manage zero, one, or many EMPLOYEEs.
EMPLOYEE
manage
managed by
Binary Relationship
EMPLOYEE
assigned to
OFFICE
assigned to
STUDENT
registered for
COURSE
taken by
CUSTOMER
place
ORDER
placed by
Each EMPLOYEE must be assigned to one and only one OFFICE.
Each OFFICE may be assigned to one and only one EMPLOYEE.
Each STUDENT may be registered for zero, one, or many COURSEs.
Each COURSE may be taken by for zero, one, or many STUDENTs.
Each CUSTOMER may place zero, one, or many ORDERs.
Each ORDER must be placed by one and only one CUSTOMER.
Ternary Relationship
STUDENT
ADVISOR
MAJOR
Each STUDENT must have declared one or more MAJORs and be assigned to one or more ADVISORs.
Each ADVISOR must be assigned to one or more STUDENTs and be responsible for one or more
MAJORS.
Each MAJOR must be declared by one or more STUDENTs and be assigned to one or more ADVISORs.
Associative Entity
(a)
PASSENGER
make reservation
reserved by
SCHEDULED
FLIGHTS
(b)
PASSENGER
RESERVATION
SCHEDULED
FLIGHTS
Reading Relationships
Each…
entity1
must be
or
may be
{
}
relationship
one or more
or
entity2
one and only one
{
}
Each…
CUSTOMER may be placing one or more ORDERS.
ORDER must be placed by one and only one CUSTOMER
Characteristics of a Good Data Model
Characteristic
Pictorial
Rigorous and Specific
Explanation
A good data model should be an accurate graphical depiction of the entities and their
relationships
A good data model should be specific with regard to the identification of all entities and their
relationships and rigorous in the identification and specification of the attributes associated with
each entity.
Top-down Decomposable
A good data model should be decomposable in the sense that the level of detail for each entity
and its associated attributes can be investigated at various levels of detail or aggregation.
Provide Focus
A good data model should be focused on the data associated with a single system and contained
within a single system boundary.
Minimally Redundant
A good data model will display minimal redundancy with regard to repeated entity types, data
redundancy, and many-to-many relationships.
Transparent
The actual data and the physical structure of the database should be discernable from looking at
the graphical data model.
Easily Navigated
A good data model should be laid out in an organized fashion to allow for the relationships among
the entities to be easily followed.
Predicts the Final System
A good data model should be an accurate prediction of the physical implementation of the
system.
Identifying Entities and Relationships
Question Category
Description
Determine System Entities
Find out what types of people, business units, things, places, events, materials, or
other organizations are associated with, or interact with, the system and about
which data must be maintained.
Identify Entity Attributes
Identify the characteristics by which each entity is associated or identified with.
Determine Entity Keys
Identify the most appropriate characteristic for each entity that uniquely
distinguishes an instance of that entity from all other instances of the same entity.
Determine Relationships and Degrees
Identify the various events, transactions, or other business activities that infer an
association between entities.
Determine Cardinalities and Optionalities
Identify the circumstances under which each of the relationships can occur. This
requires an investigation into the various business rules under which the
organization operates and the constraints imposed on the events which occur
within the business environment.