Transcript Physical Data Modelling - itsy.co.uk

44220: Database Design & Implementation
Physical Data Modelling
Ian Perry
Room: C49
Tel Ext.: 7287
E-mail: [email protected]
http://itsy.co.uk/ac/0506/sem2/44220_DDI/
The ‘Data Modelling Stack’
Conceptual Overview of things
Logical
Physical
Ian Perry
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)
44220: Database Design & Implementation: Physical Data Modelling
Slide 2
What is a Physical Data Model?

The Physical Implementation (hardware &
software) of a Logical Data Model:


A Physical Data Model must enable:



it is useless to progress to this stage of database
development if your Logical Data Model is NOT
demonstrably ‘robust’.
data to be stored (& maintained) in structured
manner; i.e. MUST be an ‘accurate’ implementation
of the logical data model.
retrieval of specific groupings of data; i.e. in order
to provide the information REQUIRED by the
original business requirements.
There may be several software constraints:

Ian Perry
depending upon the software application chosen.
44220: Database Design & Implementation: Physical Data Modelling
Slide 3
Our Physical ‘World’?

RDBMS Software:


Microsoft Access
In this physical world we MUST be able to:

Create;


Populate;


this Database with ‘test’ data.
Query;

Ian Perry
i.e. translate our Relational Schema into a
Database.
i.e. ask questions of the Database.
44220: Database Design & Implementation: Physical Data Modelling
Slide 4
Properties of RDBMS Software

Modification:

To Schema:



To Data:



creating & deleting relations (i.e. Tables).
adding attributes to, or removing attributes
from, existing relations.
creating & deleting tuples (i.e. Records)
updating attribute values (i.e. Data in Fields)
Interrogation:


Ian Perry
Relational Algebra – how it SHOULD work.
Relational Calculus – how it DOES work.
44220: Database Design & Implementation: Physical Data Modelling
Slide 5
Logical => Physical

i.e. translate our Relational Schema into
a Database Storage Model:

Schema
Relations
Attributes
Domains

Key Fields =>



Ian Perry
=>
=>
=>
=>
Database
Tables
Field Names
Data Type
Field Size
Input Mask
Validation Rule
etc.
Relationships
44220: Database Design & Implementation: Physical Data Modelling
Slide 6
The SSC Database
ER Diagram
Course
N
M
Staff
1
M
Student
Relations





Staff (StaffID, FirstName, SurName, ScalePoint, DOB)
Student (EnrolNo, FirstName, SurName, OLevelPoints, Tutor)
Course (CourseCode, Name, Duration)
Team (CourseCode, StaffID)
Pay (ScalePoint, RateOfPay)
Ian Perry
44220: Database Design & Implementation: Physical Data Modelling
Slide 7
Physical Implementation
Ian Perry
44220: Database Design & Implementation: Physical Data Modelling
Slide 8
Relational Algebra

With most (all?) Relational DataBase
Management Systems the means of
database interrogation is based upon:


Relational Algebra (E. F. Codd, 1972)
As represented by the 3 primary
functions of:



Ian Perry
SELECT
PROJECT
JOIN
44220: Database Design & Implementation: Physical Data Modelling
Slide 9
Relational Algebra - Example
ER Diagram
Employee
1
M
Assignment
M
1
Job
Relations
Employee (EmpNo, EName, ESalary, Dept)
Assignment (JName, EmpNo, Hours)
Job (JName, Budget)
Ian Perry
44220: Database Design & Implementation: Physical Data Modelling
Slide 10
SELECT

Extracts TUPLES (Rows) from a relation
subject to required conditions on
attributes in that relation. e.g.:
SELECT Employee WHERE ESalary > 13000
Ian Perry
EmpNo
EName
ESalary
Dept
146
468
Harvey
Mendoza
15000
14000
Sales
Planning
44220: Database Design & Implementation: Physical Data Modelling
Slide 11
PROJECT

Extracts COLUMNS from a relation in a
named order by attribute. e.g.:
PROJECT Employee OVER EName, Dept
Ian Perry
EName
Dept
Harvey
Jones
Mendoza
Smith
Sales
Planning
Planning
Sales
44220: Database Design & Implementation: Physical Data Modelling
Slide 12
JOIN

COMBINES RELATIONS which have a
common attribute to generate a temporary
relation containing all of the attributes from
both relations. e.g:
JOIN Employee AND Assignment OVER EmpNo
EmpNo
EName
ESalary
134
146
468
Smith
12000
Harvey
15000
Mendoza 14000
Dept
JName
Sales
ProjB
Sales
ProjA
Planning ProjB
Hours
9.0
3.4
5.2
NB. This temporary relation contains only one instance
of the common ‘EmpNo’ Attribute.
Ian Perry
44220: Database Design & Implementation: Physical Data Modelling
Slide 13
Asking ‘Complex’ Questions

What are the Names, Jobs and Hours worked
by those in the Sales Dept?
PROJECT (
SELECT (
JOIN Employee AND Assignment OVER EmpNo)
WHERE Dept = 'Sales')
OVER EName, JName, Hours
EName
JName
Hours
Smith
Harvey
ProjB
ProjA
9.0
3.4
Ian Perry
NB. The final relation is
constructed by working
from the innermost
nesting outwards.
44220: Database Design & Implementation: Physical Data Modelling
Slide 14
Structured Query Language

SQL is the most often used method for
accessing relational databases.


Remember:




A ‘software interpretation’ of Codd's Relational
Algebra.
SELECT - extracts TUPLES from a relation subject to
required conditions on attributes in the relation.
PROJECT - extracts COLUMNS from a relation in a named
order by attribute.
JOIN - COMBINES RELATIONS which have a common
attribute to generate a temporary relation containing all of
the attributes from both relations.
SQL ‘works’ for all RDBMS applications:

Ian Perry
e.g. Access, Oracle, etc.
44220: Database Design & Implementation: Physical Data Modelling
Slide 15
The SQL ‘SELECT’ Statement

SQL Syntax
SELECT {column_name [, column_name, ... ] }
FROM table_name [ table_alias ]
[ WHERE condition
[ AND/OR condition [, AND/OR condition, ... ] ] ]
[ GOUP BY column_name [, column_name, ... ]
[ HAVING condition ] ]
[ ORDER BY {column_name/column_number [, ... ] } ]

Don’t worry, it is not a frightening as it
looks!
Ian Perry
44220: Database Design & Implementation: Physical Data Modelling
Slide 16
The ‘simplest’ SELECT
SELECT *
FROM staff ;

A SELECT of all Tuples (rows) from a
Table called staff.
Ian Perry
44220: Database Design & Implementation: Physical Data Modelling
Slide 17
A ‘more useful’ SELECT
SELECT name, surname, age, position
FROM staff
WHERE age > 35
OR position = 'CLERK'
GROUP by age ;

A PROJECT of specific columns of the
staff Table (in a named order), with
some SELECTion of conditions.
Ian Perry
44220: Database Design & Implementation: Physical Data Modelling
Slide 18
Previous SQL Statement Produces:
Ian Perry
44220: Database Design & Implementation: Physical Data Modelling
Slide 19
SELECTing from 2 Tables
SELECT S.name, S.surname, S.age, C.courseno
FROM staff S, course C
WHERE S.surname = C.surname
AND age > 50 ;

JOINs the Tables staff and course to
create a temporary table and PROJECTs
columns from this new table based on
the SELECTion criteria.
Ian Perry
44220: Database Design & Implementation: Physical Data Modelling
Slide 20
Previous SQL Statement Produces:
Ian Perry
44220: Database Design & Implementation: Physical Data Modelling
Slide 21
Query-By-Example (QBE)

SQL can be difficult to learn, however, most
RDBMS software has a QBE interface:


Using this 'point-&-click' QBE interface, we
don’t have to know (much) about:


Field Names, Logical Operators, etc.
can easily set up relationships between tables:


which presents the user with ‘lists’ of things to
choose from.
Staff.SURNAME = Course.SURNAME
and apply criteria for selection:

Ian Perry
Staff.AGE > 50
44220: Database Design & Implementation: Physical Data Modelling
Slide 22
This Week’s Workshop

Provides a ‘gentle’ Introduction to
Microsoft Access


Showing you how to build, and then ask
questions of, relatively simple Relational
Databases.
i.e.:

Ian Perry
Databases consisting of two, or three,
Tables.
44220: Database Design & Implementation: Physical Data Modelling
Slide 23