Transcript Database Languages - Vrije Universiteit Brussel

Chapter 6
Physical Database Design
Physical Design and implementation
1. Translate global logical data model for target DBMS
 1.1 Design base relations for target DBMS
 1.2 Design integrity rules for target DBMS
2. Design and implement physical representation
 2.1
 2.2
 2.3
 2.4
 2.5
Analyze transactions
Choose file organizations
Chose secondary indexes
Consider introduction of controlled redundancy
Estimate disk space
3. Design and implement security mechanisms
 3.1 Design and implement user views
 3.2 Design and implement access rules
4. Monitor and tune the operational system
1. Translate global logical data
model for target DBMS

Objective
 To produce a working relational database schema from the
global logical data model

Examine whether the system supports:
 definition of primary keys, foreign keys, alternate keys ;
 definition of required fields ( option NOT NULL ) ;
 definition of domains ;
 definition of enterprise constraints
• 1993 ISO SQL standard (SQL2)
• Triggers, database procedures
• Unique indexes

Verify how to create base tables
Design base relations for target DBMS
2. Design and Implement
Physical Representation

Objective
 To determine the file organizations and access methods that
will be used to store the base relations : that is, the way in
which relations and tuples will be held on secondary storage.

Factors used to measure efficiency:
 Transaction throughput
 Response time (elapse time for completion of a single
transaction)
 Disk storage

This leads to a Query Execution Plan (QEP)
 related to query optimizer and supported by some DBMS.
2.1 Analyze Transactions

Objective
 To understand the functionality of the transactions that will
run on the database and to analyze the important transactions

For each transaction type we should determine:
 the expected frequency;
 relations and attributes accessed by the transactions and the
type of access (insert, delete, update);
 attributes used as predicates in SQL (these are candidates for
access structures);
 attributes involved in a join of two or more relations
 time constraints on the transactions.
2.2 Choose file organization

Objective
 Determine an efficient file organization for each base table.

Heap when:
 data is bulk-loaded into the relation
 relation is only a few pages long
 every tuple in a relation is to be retrieved

Hash when:
 tuples are only to be retrieved based on a hash field

B-tree when:
 retrieval based on exact key match or ranges of values

Inverted file when:
 multiple search criteria and few updates
2.3 Choose secondary indexes

Objective
 Determine whether adding secondary indexes will improve
the performance of the system.


The overhead involved should be balanced against the
performance improvement .
Guidelines:
 index the primary key if it is not the key of the file organization
 no indexes on small relations
 add secondary indexes on heavily used attributes
 add secondary indexes to a foreign key
 avoid keys on attributes that are frequently updated
 avoid indexes on long attributes
2.4 Consider introduction of
controlled redundancy

Objective
 Determine whether introducing redundancy in a controlled
manner by relaxing the normalization rules will improve the
system performance.
 Denormalization makes implementation more complex;
 Denormalization often sacrifices flexibility;
 Denormalization speeds up retrieval but slows down updates;



Derived data.
Duplicating attributes or joining relations together.
Introduction of redundancy should be fully
documented
2.5 Estimate disk space

We estimate the amount of disk space that the
database requires.

Calculation is important to ensure sufficient
disk space for the database when it goes live,
and during the lifetime of the system.
3. Document design of security
measures and user views

We document the design of the individual user
views and security mechanisms of the
database.
4.

Monitor and Tune the Operational
System
Example new requirements
Ability to hold pictures of the properties for rent,
together with comments that describe the main
features of the property.
Ability to publish a report describing properties
available for rent on the World-Wide Web (WWW).
Microsoft Access Startup window and
Microsoft Access dialog box
Microsoft Access Database window for
DreamHome database
New Table dialog box
Design View of Property_for_Rent table Field Properties for Property_No field
Design View of Property_for_Rent
table -Field Properties for Rooms field
Possible values for Type Field of
Property_for_Rent table
Using Lookup list for Type field of
Property_for_Rent table
Property_for_Rent tables joined on
Staff_No
Relationships dialog box for the Staff
and Property_for_Rent tables
Design Enterprise Constraints for Target
DBMS - Example enterprise constraint
Error message indicating that an
enterprise constraint has been violated