Transcript Introduction to Data Modelling: Entity Relationship Modelling IMS1002 /CSE1205

IMS1002 /CSE1205
Systems Analysis and Design
Introduction to Data
Modelling: Entity
Relationship Modelling
www.monash.edu.au
Data Modelling
• Focus on the information aspects of the
organisation
• In a database environment many
applications share the same data
• The database is a common asset and
corporate resource
• Corporate and application level data
modelling
www.infotech.monash.edu.au
2
Conceptual Data Modelling
• A conceptual data model is a representation of
organisational data
• Captures the structure, meaning and
interrelationships amongst the data
• Independent of any data storage and access
method, DBMS, platform issues
• Occurs in parallel with other systems analysis
activities
www.infotech.monash.edu.au
3
Conceptual Data Modelling
• Identification of information
requirements
• Allows integration of data across the
organisation and across applications
• Helps eliminate problems of data
inconsistency and duplication across
the organisation
www.infotech.monash.edu.au
4
Conceptual Data Modelling
• Techniques;
– Entity Relationship (ER) modelling
– Normalisation
– Data Structure Diagrams (DSD)
• Good modelling techniques are supported
by rigorous standards and conventions to
remove ambiguity and aid understanding
www.infotech.monash.edu.au
5
Entity Relationship Modelling
• Used for conceptual data modelling
• Diagrammatic technique used to represent:
– things of importance in an organisation - entities
– the properties of those things - attributes
– how they are related to each other relationships
www.infotech.monash.edu.au
6
Entity Relationship Modelling
• Entity relationship (ER) models can be readily
transformed into a variety of technical
architectures
• All information about the system’s data
identified during conceptual data modelling
must be entered into the data dictionary or
repository
• This assists in checking the consistency of data
and process models
www.infotech.monash.edu.au
7
Entity Relationship Modelling
• Data “objects” or entities are things
about which we wish to store
information
• ER models show the major data objects
and the associations between them
• ER models are useful in the initiation,
analysis and design phases
www.infotech.monash.edu.au
8
Entity
• Something of interest about which we store
information
eg.
EMPLOYEE
SALES ORDER
SUPPLIER
• Often identified from nouns used within the
business application
• Should be LOGICAL (not physical)
www.infotech.monash.edu.au
9
Identifying Entities
• Entities are subjective (i.e. they reflect the
viewpoint of the system) and can be:
Real
eg
VEHICLE
Abstract
eg
QUOTA
Event remembered
eg
LOAN
Role played
eg
CUSTOMER
Organisation
eg
DEPARTMENT
Geographical
eg
LOCATION
www.infotech.monash.edu.au
10
Representing Entities
• We represent an entity by a named rectangle
• Use a singular noun, or adjective + noun
• Refer to one instance in naming convention
CUSTOMER
PART-TIME
EMPLOYEE
www.infotech.monash.edu.au
11
Entity Types and Instances
• An entity type is a classification of entity
instances
eg
BN Holdings
ABC Engineering
Acme Corp. Ltd.
SUPPLIER
www.infotech.monash.edu.au
12
Entity Types are Logical
• E.g. in a sales and inventory system there
might be 3 physical forms of data:
– a stock file
– product brochures sent to customers enquiring
about products
– a product range book used by salespeople
when calling on customers to take orders
which could be represented by one logical entity
PRODUCT
www.infotech.monash.edu.au
13
Entity Types are Logical
• E.g. in a Student Records System there might be an
entity type STUDENT which represents some of the
data used in several physical forms of data:
> Student re-enrolment forms
> Subject class lists
> Student results file
The ER model identifies the minimum set of data
objects necessary to construct the data used within
the system in its various physical forms.
www.infotech.monash.edu.au
14
Relationship
• Is an association between two entities
• We may wish to store information about the
association
• Often recognised by a verb or
"entity” + verb + “entity"
eg CUSTOMER places ORDER
• Relationships capture the "business rules" of
the system
www.infotech.monash.edu.au
15
Representing Relationships
• We represent a relationship as a line between
two entities
• The relationship is named by a meaningful verb
phrase which should indicate the meaning of the
association
• Relationships are bi-directional so naming each
end of the relationship conveys more meaning
SUPPLIER
supplies
Supplied by
ITEM
www.infotech.monash.edu.au
16
Relationship Types and Instances
• A relationship type is a classification of
relationship instances
Marketing
Finance
MIS
DEPT
employs
employs
employs
employs
Sue Black
Bill Brown
John Smith
EMPLOYEE
www.infotech.monash.edu.au
17
Cardinalities in Relationships
• The cardinality of a relationship is the
number of instances of one entity type that
may be associated with each instance of the
other entity type
eg a CUSTOMER may place many ORDERs
an ORDER is placed by one CUSTOMER
an ITEM can appear on many ORDERs
www.infotech.monash.edu.au
18
Examples of Cardinalities
One to One
One to Many
Many to Many
EMPLOYEE
CUSTOMER
SUPPLIER
led by
placed
by
leads
PROJECT
places
SALES
ORDER
supplied
by
supplies
ITEM
www.infotech.monash.edu.au
19
Nature of Relationships
We can indicate whether relationships are
optional or mandatory:
• A customer MAY place many sales orders
• Each sales order MUST be placed by one
customer
CUSTOMER
places
placed by
SALES
ORDER
www.infotech.monash.edu.au
20
Notations
EMPLOYEE
EMPLOYEE
Is attended
by
attends
attends
COURSE
Notation used in Hoffer et al (1999)
COURSE
Notation used in Whitten et al (2001)
www.infotech.monash.edu.au
21
Notations
EMPLOYEE
or
EMPLOYEE
Exactly one
One and only one
EMPLOYEE
Zero or one
EMPLOYEE
One or more
EMPLOYEE
Zero, one or more
EMPLOYEE
More than one
www.infotech.monash.edu.au
22
Relationship Degree
• The degree of a relationship is the number
of entity types that participate in the
relationship.
• The most common relationships in ER
modelling in practice are:
unary (degree one)
binary (degree two)
ternary (degree three)
www.infotech.monash.edu.au
23
Unary relationships
• A unary relationship is a relationship
between instances of one entity type (also
called a recursive relationship)
manages
Has component
ITEM
EMPLOYEE
Reports to
Is a component
of
www.infotech.monash.edu.au
24
Binary relationships
• A binary relationship is a relationship
between instances of two entity types and is
the most common type of relationship
encountered in practice.
has copy
MOVIE
VIDEO TAPE
Is a copy of
www.infotech.monash.edu.au
25
Ternary relationships
• A ternary relationship is a simultaneous
relationship between instances of three
entity types.
• A ternary relationship is NOT the same as
three binary relationships between the
same three entity types.
www.infotech.monash.edu.au
26
Ternary relationships
PROJECT
PROJECT
PROGRAMMER
PROGRAMMER
LANGUAGE
Triplets e.g. Mary uses COBOL on HR
Project
LANGUAGE
3 independent sets of pairs e.g. Mary
uses COBOL; Mary works on HR Project;
COBOL is used in the HR Project
www.infotech.monash.edu.au
27
Example ER model
employs
DEPARTMENT
EMPLOYEE
employed by
made by
makes
places
CUSTOMER
placed by
SALES
ORDER
is on
is for
ITEM
www.infotech.monash.edu.au
28
Associative Entities (Gerunds)
• An associative entity (or gerund) is a relationship that a data
modeller decides to model as an entity type
• As both entities and relationships can have attributes, this is
possible
CUSTOMER
CUSTOMER
Is made by
makes
Is ordered by
SALES
ORDER
orders
PRODUCT
PRODUCT
Is on
has
www.infotech.monash.edu.au
29
Multiple Relationships
• It is common to have two or more relationships
between the same entities.
• They represent different business rules.
Has working
EMPLOYEE
Has eligible
Is working on
PROJECT
Is eligible for
www.infotech.monash.edu.au
30
Modelling Time-dependent Data
• Some data values vary over time and it may be
important to store a history of data values to
understand trends and for forecasting.
E.g. for accounting purposes we are likely to need a history
of costs of material and labour costs and the time period over
which each cost was in effect.
• Modelling time-dependent data can result in
changes to entities, attributes and
relationships.
www.infotech.monash.edu.au
31
Modelling Time-dependent Data
• One technique is to store a series of time
stamped data values. These values can either
be represented as repeating data or as an
additional entity called PRICE HISTORY.
PRODUCT
has
PRODUCT
Price
Effective date
belongs
to
PRICE
HISTORY
www.infotech.monash.edu.au
32
Modelling Time-dependent Data
• Relationship cardinality can change.
Has working
DEPT
EMPLOYEE
Works for
Had working
DEPT
EMPLOYEE
Has worked for
www.infotech.monash.edu.au
33
Entity subtypes and supertypes
• Some entities can be generalised (or specialised)
to form other entities
• An entity subtype is made up from some of the
instances of the entity
E.g. the entity types
motor car
truck
train
can be grouped together to form the entity supertype
transport vehicle
www.infotech.monash.edu.au
34
Entity Subtypes
• Entity subtypes are included in the ER model only when
they are of use - they may participate in relationships and
have additional attributes
DEPARTMENT
employed
employs
EMPLOYEE
SALESPERSON
services
served by
CUSTOMER
www.infotech.monash.edu.au
35
Multiple entity subtypes
•Entity types may have
multiple subtypes
• Entity subtypes may be nested
PROPERTY
COMMERCIAL
EMPLOYEE
PART-TIME
FULL-TIME
RESIDENTIAL
METROPOLITAN
COUNTRY
www.infotech.monash.edu.au
36
Entity Subtypes
Multiple entity subtypes should be
• Non-overlapping (disjoint)
• Collectively exhaustive
This enables easier translation to a relational design
PROPERTY
EMPLOYEE
PART-TIME
SALARIED
METROPOLITAN
?
?
RESIDENTIAL
COMMERCIAL
www.infotech.monash.edu.au
37
Building a Basic ER Model
• Identify and list the major entities in the system
• Represent the entities by named rectangles
• Identify, draw, name, and quantify relationships
• Indicate mandatory/optional nature of
relationships
• Revise for entity subtypes where appropriate
www.infotech.monash.edu.au
38
Example ER model
• Airline ticketing model
for
FLIGHT
scheduled
as
arrival
AIRLINE
ROUTE
AIRPORT
departure
COUPON
made up
of
TICKET
shown on
operated by
AIRLINE
PASSENGER
See Barker (1989), chap 2.4
www.infotech.monash.edu.au
39
Eliciting Information for an ER Model
• Fact-finding and information gathering techniques
are used to determine the entities and relationships
• Identify both existing and new information
• Existing documents are particularly useful
e.g. forms, paper-based and computer files, reports, listings, data
manuals, data dictionary
• Existing and new business rules for information
are often difficult to elicit from documents ...
it is essential to speak directly to the client
www.infotech.monash.edu.au
40
ER Modelling Difficulties
• Is a given object an entity or relationship ?
• Are two similar objects one entity or two ?
• Is a given object an entity or an attribute of
(data item about) an entity?
e.g. EMPLOYEE and EMPLOYEE SPOUSE
• Do we need to store data about the object?
• What is the 'best' data model ?
www.infotech.monash.edu.au
41
Quality dimensions
•
•
•
•
•
Correctness
Completeness
Understandability
Simplicity
Flexibility
www.infotech.monash.edu.au
42
ER models and DFDs
• Do not to confuse entities with sources/sinks or
relationships with data flows
TREASURER
ACCOUNT
EXPENSE
EXPENSE
REPORT
• TREASURER is the person entering data; there is only
one person and hence it is not an entity type
• ACCOUNT has many account balance instances
• EXPENSE has many expense transactions
• EXPENSE REPORT contents are already in ACCOUNT and
EXPENSE - it is not an entity type
www.infotech.monash.edu.au
43
Integration of ER Models with
DFDs
• All data elements represented in data flow diagrams for a
system (data flows and data stores) MUST correspond to
entities and their attributes in the ER model
2
Check
sales
order
ORDER
Sales orders
placed by
CUSTOMER
made up of
3
Produce
weekly
sales
totals
ORDER
LINE
for
PRODUCT
www.infotech.monash.edu.au
44
References
Barker, R. (1989) CASE*METHOD Entity Relationship
Modelling, Addison-Wesley, Wokingham UK. Chapters
4,5
Hoffer, J.A., George, J.F. and Valacich, J.S., (1999).,
Modern Systems Analysis and Design, (2nd ed),
Benjamin/Cummings, Massachusetts. Chapter 10
Whitten, J.L. & Bentley, L.D. and Dittman, K.C., (2001),
Systems Analysis and Design Methods, (5th edn.),
McGraw Hill Irwin, Boston MA USA. Chapter 7
www.infotech.monash.edu.au
45