Transcript Logical Data Modelling

44220: Database Design & Implementation
Logical Data Modelling
Ian Perry
Room: C48
Tel Ext.: 7287
E-mail: [email protected]
http://itsy.co.uk/ac/0607/sem2/44220_DDI/
Remember the ‘Stack’
Conceptual Overview of things
Model of the
Business System
(ER Model)
Logical
Model of Data
Storage Theory
(Db Schema)
Physical
Ian Perry
that are perceived to
be of ‘interest’ in the
‘real’ world.
Data elements & the
relationships between
those elements in
tabular form.
Actual data held in a
database & the
means to manipulate
that data.
Physical
implementation
(RDBMS)
44220:Database Design & Implementation: Logical Data Modelling
Slide 2
What is a Logical Data Model?

A ‘robust’ representation of the initial
decisions made when building our
Conceptual Data Model, which was
composed of:




Entities
Attributes
Relationships
When I say ‘robust’ I mean that this
model MUST ‘perform’ well with respect
to a specific style/type of software.
Ian Perry
44220:Database Design & Implementation: Logical Data Modelling
Slide 3
Database Theories & Software

Logical Modelling is hardware independent;
the match to ‘type’ of software is our only
concern; e.g.:




hierarchy
relations
objects
Hierarchical DBMS
Relational DBMS
Object-based DBMS
Whichever Database Theory is ‘chosen’ they
generally separate the following three things:



Ian Perry
Data Structure

Defining how the data will be stored.

Defining how data is to be added/updated/deleted.

Defining how ‘questions’ can be asked of the stored data.
Data Manipulation
Data Query
44220:Database Design & Implementation: Logical Data Modelling
Slide 4
Database Theory = Relational Model

First proposed by Dr. E. F. Codd in June 1970.



Codd's model is the definitive model for ALL
Relational Database Management Systems (RDBMS).
Structured English Query Language ("SEQUEL") was
developed by IBM Corporation, Inc., to use Codd's
model.


Codd E F, (1970), A Relational Model of Data for Large
Shared Data Banks, Communications of the ACM, Vol. 13, No.
6, Pgs 377 – 387.
SEQUEL later became SQL.
In 1979, Relational Software, Inc. (now Oracle
Corporation) introduced the first commercial
implementation of SQL.

Ian Perry
SQL is still (i.e. after more than 27 Years) the most widely
used RDBMS manipulation & query language.
44220:Database Design & Implementation: Logical Data Modelling
Slide 5
Relational Modelling Language
Schema
set of relations
Relation
tablular framework
Tuple
horizontal division of relation (row)
Attribute
vertical division of relation (column)
Data Cell
junction of horizonal & vertical
Cardinality Number of tuples in relation
Degree
Number of attributes in relation
Don’t get too hung up on Codd’s strange language, but that is what you
often read in Relational Database Design books.
Ian Perry
44220:Database Design & Implementation: Logical Data Modelling
Slide 6
Relations look very like Entities

Staff (SCode, Name, Address, DoB, DoE)
SCode Name Address DoB
DoE
9491 Smith 6 Shaw St 130265 031098
7416 Day
2 Sale St 140157 031192
 Contract (CCode, Site, Begin, End, Super)
CCode Site Begin End
Super
279
Hull 270699 030702 9491
665
York 140901 020203 7416

NB. You should only ‘use’ the above tabular form in
order to check logic; i.e. Tables are NOT Relations.
Ian Perry
44220:Database Design & Implementation: Logical Data Modelling
Slide 7
Attributes

Attribute Values are held in Data Cells:


Set of Attribute Values eligible for
entry in a Data Cell:


must be atomic (i.e. simple) values only.
is known as an Attribute’s Domain.
The Relational Model is weak at
explicitly modelling relationships:

Ian Perry
Relations MUST share an Attribute Domain
if a relationship is present/possible.
44220:Database Design & Implementation: Logical Data Modelling
Slide 8
Data Access


Data is retrieved by reference to name
(of an Attribute) & value (held in a Data
Cell) only.
Most important Attribute(s) is know as
the ‘Key’, of which there are two types.

Primary Key:


Foreign Key:

Ian Perry
special Attribute(s) used to control access to
Tuples (i.e. rows) of data.
used, as logical pointers (i.e. instead of lines) to
represent associations between Relations.
44220:Database Design & Implementation: Logical Data Modelling
Slide 9
Relations are NOT Tables

Tables are ‘possible’ data values
presented in rows & columns.


and are very useful in order to check logic.
Relations must obey rules (Codd’s 12
Rules); e.g. we cannot have:

Duplicates:


Row or Column dependencies


Ian Perry
each Tuple (row) must be unique.
there is no hidden meaning from
location/sequence of Tuples (rows) and/or
Attributes (columns).
etc.
44220:Database Design & Implementation: Logical Data Modelling
Slide 10
Keys to Data Integrity

Rule:


Need a way to discriminate between
Tuples:


each Relation MUST have a Primary Key
May be many candidates for the job of
Primary Key, so select on basis of:


each Tuple (i.e. row) must be unique.
uniqueness AND/OR minimality
Primary Keys with multiple Attributes:

Ian Perry
are know as composite keys.
44220:Database Design & Implementation: Logical Data Modelling
Slide 11
Other things about Attributes

Attributes that are part of the Primary
Key:


Candidates not selected as part of the
Primary Key:


are known as Prime Attributes.
are known as Secondary Keys.
Secondary Attributes which serve as
the Primary Key in another Relation:

Ian Perry
are known as Foreign Keys.
44220:Database Design & Implementation: Logical Data Modelling
Slide 12
Rules for Integrity

Entity Integrity:


No Attribute that is a member of a Primary
Key can assume a ‘null’ (i.e. empty) value.
 Else, how could we discriminate between
Tuples?
Referential Integrity:

Ian Perry
A Foreign Key Attribute must take values
that are either ‘null’, or from same Domain
as the Relation in which this Attribute acts
as the Primary Key.
 Else, we will lose linkages between
Relations.
44220:Database Design & Implementation: Logical Data Modelling
Slide 13
It can be difficult to check all of Codd’s Rules
if you don’t express each Relation as a Table.

Staff (SCode, Name, Address, DoB, DoE)
SCode Name Address DoB
DoE
9491 Smith 6 Shaw St 130265 031098
7416 Day
2 Sale St 140157 031192
 Contract (CCode, Site, Begin, End, Super)
CCode Site Begin End
Super
279
Hull 270699 030702 9491
665
York 140901 020203
BUT always remember that Tables are NOT Relations.
Ian Perry
44220:Database Design & Implementation: Logical Data Modelling
Slide 14
In Summary

Logical Data Modelling:


Relational Database Theory, Codd (1970):


is all about translating our Conceptual Data Model
so that it might be implemented using software
that matches a specific Database Theory.
allows us to develop mathematically rigorous
abstract data models, composed of a number of
distinct Relations.
Tables are NOT Relations:

Ian Perry
simply the way we choose to mentally give flesh to
our Logical Data Model.
44220:Database Design & Implementation: Logical Data Modelling
Slide 15
And

Relations?



Attributes?




are defined by a list of Attributes.
relationships via Primary/Foreign Key mechanism.
must be distinctly named.
ordering is irrelevant.
data entries should be atomic, and of the same
type, and from the same domain.
Tuples?


Ian Perry
must be unique (so need a Key).
ordering is irrelevant.
44220:Database Design & Implementation: Logical Data Modelling
Slide 16
And Finally!

I know what you are thinking:


However:


the Relation Definitions of a Logical Data
Model looks to be exactly the same as the
Entity Definitions of a Conceptual Data
Model.
it is highly unlikely that your Logical Data
Model will be exactly the same as your
Conceptual Data Model.
You will have made some logical errors!

Ian Perry
Which we will attempt to ‘fix’ in next week’s
lecture & workshop sessions.
44220:Database Design & Implementation: Logical Data Modelling
Slide 17
This Week’s Workshop


The purpose of this week’s Workshop is
to explore the formality of a developing
a logical data model, that conforms to
the ‘rules’ of Codd’s Relational Model.
Five Questions, exploring:


the ‘language’ and ‘rules’ of Relational
Modelling, and the ‘definition’ of Relations.
Make sure that you ‘have a go’ at all 5
questions in advance of the Workshop.
Ian Perry
44220:Database Design & Implementation: Logical Data Modelling
Slide 18