Transcript Conceptual Data Modelling

44220: Database Design & Implementation
Conceptual Data Modelling
Ian Perry
Room: C49
Tel Ext.: 7287
E-mail: [email protected]
http://itsy.co.uk/ac/0506/sem2/44220_DDI/
Remember the ‘Stack’
Conceptual Overview of things
Logical
Physical

that are perceived to
be of ‘interest’ in the
‘real’ world.
Data elements & the
relationships between
those elements in a
tabular form.
Actual data held in a
database & the
means to manipulate
that data.
Model of the
Business System.
(ER Model)
Model of Data
Storage Theory
(Db Schema)
Physical
Implementation
(RDBMS)
We MUST begin by developing a
Conceptual Data Model.
Ian Perry
44220: Database Design & Implementation: Conceptual Data Modelling
Slide 2
Conceptual Data Model?

A Conceptual Data Model is an abstraction
which reflects the requirements of a ‘realworld’ system, by definition of:




Need a ‘language’ to explore & explain our view
of a ‘real-world’ situation:


Objects of Interest
Semantics
Constraints
Ideally this ‘language’ should be compromise free
and will ‘work’ using any Software & Hardware.
Useful choice is that of ER Modelling:

Ian Perry
Need to identify Entities, Attributes &
Relationships.
44220: Database Design & Implementation: Conceptual Data Modelling
Slide 3
‘Facts’ about the ‘Real-World’




Customers place orders.
Patients take medication.
Lecturers teach students.
Students attend lectures:



Ian Perry
Some students attend all lectures.
Some students attend some lectures.
Some students attend no lectures!
44220: Database Design & Implementation: Conceptual Data Modelling
Slide 4
‘Facts’ need to be Expressed

An ER Model lets us do so in way that:





Encourages thorough Analysis.
Can be applied to ALL Database Theories.
Is independent of Software & Hardware.
Provides an effective means of
Communication.
For an ER Model we must determine:



Ian Perry
the Objects of Interest.
their Characteristics.
their Associations.
44220: Database Design & Implementation: Conceptual Data Modelling
Slide 5
Entities/Attributes/Relationships

Entities - Objects of Interest (Nouns):


Attributes - Characteristics
(Adjectives):



Customer, Supplier, Order, Employee,
Stock, etc.
Customer - Name, Address, etc.
Stock - Description, Price, Quantity, etc.
Relationships - Associations (Verbs):


Ian Perry
Customer ‘places’ Order.
Supplier ‘supplies’ Stock.
44220: Database Design & Implementation: Conceptual Data Modelling
Slide 6
Entities = Objects of Interest

Must play a necessary role in the
business system:


Each Entity MUST have name that is:


so, we must make decisions about what to
include and what to exclude.
a noun; is singular; is succinct; and is
meaningful.
Each Entity MUST be described by;

Ian Perry
one-or-more Attributes.
44220: Database Design & Implementation: Conceptual Data Modelling
Slide 7
Attributes = Characteristics


Attributes describe an Entity.
Must have Meaningful Names:


Should be Atomic:


NOT; Field 1, Field 2, etc.
NOT; Address, Invoice, etc.
For example:


Ian Perry
House No., Street, Town, County, Post
Code, etc.
Invoice No., Customer ID, Invoice Date,
etc.
44220: Database Design & Implementation: Conceptual Data Modelling
Slide 8
Entities & Attributes

Each Entity requires an Attribute
Identifier, which can be defined as:



the minimum number of Attributes that,
when given value(s), uniquely identify one
Entity Occurrence from another.
these are often called ‘key’ Attributes.
Consequently:

Ian Perry
it is mandatory that values exist for all of
these ‘key’ Attributes.
44220: Database Design & Implementation: Conceptual Data Modelling
Slide 9
Entities & Attributes (Example 1)
Entity
Attributes
Invoice
InvoiceNo
InvoiceDate
PaymentDate CustomerID
2005_0001
4 Jan 2005
11 Jan 2005
2005_0002
4 Jan 2005
2005_0003
7 Jan 2005
10002
10034
14 Jan 2005
10002
Key Attribute
Ian Perry
44220: Database Design & Implementation: Conceptual Data Modelling
Slide 10
Entities & Attributes (Example 2)
Entity
Attributes
Module
ModuleName
Level
CourseCode
StaffNo
Database Design
1
ITB
234
Marketing
1
ITB
346
Marketing
2
ITB
Research Methods
2
IS Planning
M
254
Key Attributes
Ian Perry
44220: Database Design & Implementation: Conceptual Data Modelling
Slide 11
Relationships = Associations

A Relationship MAY OCCCUR;


Each Relationship REPRESENTS;


between any TWO Entities.
the possible existence of an Association
between TWO Entities.
Every Relationship SHOULD be
described :


Ian Perry
by Degree (a quantitative association)
by Type (a qualitative association)
44220: Database Design & Implementation: Conceptual Data Modelling
Slide 12
Quantitative = Degree


Identifies the number of Entity Occurrences
that might be on each side of a Relationship.
May be simple:



May be complex:


one_to_one (1:1), e.g. Wife - Husband
one_to_many (1:M), e.g. Lecturer - Student
many_to_many (M:M), e.g. Product – Customer
MOST ‘real-world’ Relationships are M:M,
however:

Ian Perry
Logical & Physical Data Models CAN NOT handle
such complex Relationships.
44220: Database Design & Implementation: Conceptual Data Modelling
Slide 13
Qualitative = Type

MUST be a succinct & meaningful verb
(or verb-phrase), e.g.:


Wife is married to Husband
MOST Relationships are bi-directional,
e.g.:




Ian Perry
Lecturer bores Student
Student bored by Lecturer
Product bought by Customer
Customer buys Product
44220: Database Design & Implementation: Conceptual Data Modelling
Slide 14
Example Relationships (by Degree & Type)
One-to-One
Husband
1
is married to
1
bores =>
M
Wife
One-to-Many
Lecturer
1
Student
<= bored by
Many-to-Many
Product
M
bought by =>
M
Customer
<= buys
Ian Perry
44220: Database Design & Implementation: Conceptual Data Modelling
Slide 15
Decomposing Complexity

Try to simplify any complex ‘real-world’ M:M
Relationships that you discover into 2 x 1:M
Relationships:


This results in the creation of an new ‘artificial’
linking Entity composed of the identifiers (i.e. the
‘key’ Attributes) from either side of the original
Many-to-Many relationship.
ALWAYS attempt to simplify any complex (i.e.
M:M) Relationships at the Conceptual Data
Modelling stage:

Ian Perry
you will have to ‘solve’ these ‘problems’ when you
move on to the Logical Data Model.
44220: Database Design & Implementation: Conceptual Data Modelling
Slide 16
An Example of Decomposition
1 x Many-to-Many
M
Product
bought by =>
M
Customer
<= buys
2 x One-to-Many
1
Product
ProductKey
M
bought by =>
M
Prod/Cust
CustomerKey
<= buys
1
Customer
CustomerKey
ProductKey
NB. The Relationship is still between the two ‘real-world’ Entities;
the ‘artificial’ Entity is just there to solve a problem.
Ian Perry
44220: Database Design & Implementation: Conceptual Data Modelling
Slide 17
Conceptual Data Modelling Process

Identify ALL of the relevant Entities:


Identify those Attributes that adequately
describe each Entity:


must play a necessary role in the business
system.
remember to choose ‘key’ attribute(s).
Identify the Relationships between Entities:



Ian Perry
determine the Degree of each Relationship.
determine the Type of each Relationship.
attempt to decompose any many-to-many
Relationships that you have identified.
44220: Database Design & Implementation: Conceptual Data Modelling
Slide 18
This Week’s Workshop


In this Workshop we will continue to explore
some of the design decisions you must make in
order to begin to construct a Conceptual Data
Model of the ‘real world’.
Working alone, or in a small team, consider the
following ‘things’ that exist in the ‘real’ world.


For each of the above;




University, Bank, Sports Activity.
list the Entities,
list likely Attributes that ‘describe’ each Entity,
and identify ‘Key’ Attributes.
DON’T arrive at the workshop un-prepared!
Ian Perry
44220: Database Design & Implementation: Conceptual Data Modelling
Slide 19