Transcript Chapter 12

Chapter 12
Physical Database Design – Step 3
(Translate Logical Design)
Transparencies
© Pearson Education Limited,
2004
1
Chapter 12 - Objectives





Purpose of physical database design.
How to map the logical database
design to a physical database design.
How to design base tables for target
DBMS.
How to design representation of
derived data.
How to design business rules for
target DBMS. © Pearson Education Limited,
2004
2
Logical vs Physical Database Design



Logical
db
design
independent
of
implementation
details,
such
as
functionality of target DBMS.
Logical db design concerned with the what,
physical database design is concerned with
the how.
Sources of information for physical design
includes logical data model and data
dictionary.
© Pearson Education Limited,
2004
3
Physical Database Design
Process of producing a description of
implementation of the database on
secondary storage.
It describes base tables, file organizations,
and indexes used to achieve efficient
access to the data, and any associated
integrity
constraints
and
security
restrictions.
© Pearson Education Limited,
2004
4
Overview of Physical Database
Design Methodology






Step 3 Translate logical database design for
target DBMS
Step 4 Choose file organizations and indexes
Step 5 Design user views
Step 6 Design security mechanisms
Step 7 Consider introduction of controlled
redundancy
Step 8 Monitor and tune operational system
© Pearson Education Limited,
2004
5
Step 3 Translate logical database
design for target DBMS
To produce a basic working relational
database from the logical data model.

Consists of the following steps:
 Step 3.1 Design base tables
 Step 3.2 Design representation of
derived data
 Step 3.3 Design remaining business rules
© Pearson Education Limited,
2004
6
Step 3 Translate logical database
design for target DBMS

Need to know functionality of target DBMS
such as how to create base tables and
whether DBMS supports the definition of:





PKs, FKs, and AKs;
required data – i.e. whether system supports
NOT NULL;
domains;
relational integrity rules;
business rules.
© Pearson Education Limited,
2004
7
Step 3.1 Design base tables
To decide how to represent base
tables identified in logical model in
target DBMS.

Need to collate and assimilate the
information about tables produced during
logical database design (from data
dictionary and tables defined in DBDL).
© Pearson Education Limited,
2004
8
Step 3.1 Design base tables

For each table, need to define:
name of the table;
 list of simple columns in brackets;
 PK and, where appropriate, AKs and FKs.
 referential integrity constraints for any FKs
identified.


For each column, need to define:
its domain, consisting of a data type, length,
and any constraints on the domain;
 an optional default value for the column;
 whether the column can hold nulls.

© Pearson Education Limited,
2004
9
DBDL for the Branch table
© Pearson Education Limited,
2004
10
Step 3.2 Design representation of
derived data
To design the representation
derived data in the database.


of
Produce list of all derived columns from
logical data model and data dictionary.
Derived column can be stored in database
or calculated every time it is needed.
© Pearson Education Limited,
2004
11
Step 3.2 Design representation of
derived data

Option selected is based on:



additional cost to store the derived data and
keep it consistent with data from which it is
derived;
cost to calculate it each time it’s required.
Less expensive option is chosen subject to
performance constraints.
© Pearson Education Limited,
2004
12
RentalAgreement and Member with
derived column noOfRentals
© Pearson Education Limited,
2004
13
Step 3.3 Design remaining
business rules
To design the remaining business rules
for the target DBMS.

Some DBMS provide more facilities than
others for defining business rules. Example:
CONSTRAINT member_not_renting_too_many
CHECK (NOT EXISTS (SELECT memberNo
FROM RentalAgreement
GROUP BY memberNo
© Pearson
Education Limited,
HAVING
COUNT(*) > 10))
2004
14