Chapter 5
Physical Database Design
Physical Database Design … Cont’d
• The Logical database design is concerned with the what;
• The Physical database design is concerned with the how.
• Physical database design is the process of producing a description of the
implementation of the database on secondary storage.
• It describes the base relations, file organization, and indexes used to
achieve effective access to the data along with any associated integrity
constraints and security measures.
• Sources of information for the physical design process include global
logical data model and documentation that describes model.
• Knowledge of the DBMS that is selected to host the database systems,
with all its functionalities, is required since functionalities of current
DBMS vary widely.
Steps in physical database design
1. Translate logical data model for target DBMS
• To determine the file organizations and access methods that will be
used to store the base relations; i.e. the way in which relations and
tuples will be held on secondary storage
• Design enterprise constraints for target DBMS
• This phase is the translation of the global logical data model to produce
a relational database schema in the target DBMS. This includes creating
the data dictionary based on the logical model and information gathered.
• After the creation of the data dictionary, the next activity is to
understand the functionality of the target DBMS so that all necessary
requirements are fulfilled for the database intended to be developed.
Steps in physical database design … Cont’d
• Knowledge of the DBMS includes:
• how to create base relations
• whether the system supports:
• definition of Primary key
• definition of Foreign key
• definition of Alternate key(replace by others)
• definition of Domains
• Referential integrity constraints
• definition of enterprise level constraints
• Some tasks to be done:
• 1.1. Design base relation
• 1.2. Design representation of derived data
• 1.3. Design enterprise constraint
Steps in physical database design … Cont’d
1.1. Design base relation
• Designing base relation involves identification of all necessary requirements
about a relation starting from the name up to the referential integrity constraints.
• The implementation of the physical model is dependent on the target DBMS
since some has more facilities than the other in defining database definitions.
• The base relation design along with every justifiable reason should be fully
documented.
1.2. Design representation of derived data
• While analyzing the requirement of users, we may encounter that there are some
attributes holding data that will be derived from existing or other attributes. A
decision on how to represent such data should be devised.
• Most of the time derived attributes are not expressed in the logical model but
will be included in the data dictionary. Whether to store stored attributes in a
base relation or calculate them when required is a decision to be made by the
designer considering the performance impact.
Steps in physical database design … Cont’d
1.3. Design enterprise constraint(based on enterprise need)
• Data in the database is not only subjected to constraints on the
database and the data model used but also with some enterprise
dependent constraints.
• This constraint definition is also dependent on the DBMS selected
and enterprise level requirements.
• All the enterprise level constraints and the definition method in the
target DBMS should be fully documented.
Steps in physical database design … Cont’d
2. Design physical representation
This phase is the level for determining the optimal file organizations to store the
base relations and indexes that are required to achieve acceptable performance,
that is, the way in which relations and tuples will be held on the secondary
storage.
• 2.1. Analyze transactions
To understand the functionality of the transactions that will run on the
database and to analyze the important transactions
• 2.2. Choose file organization
To determine an efficient file organization for each base relation
• 2.3. Choose indexes
Used for quick access
• 2.4. Estimate disk space and system requirement
To estimate the amount of disk space that will be required by the database.
Steps in physical database design … Cont’d
3. Design user view
To design the user views that were identified in the conceptual database design
methodology
4. Design security mechanisms
5. Consider controlled redundancy
• To determine whether introducing redundancy in a controlled manner by relaxing the
normalization rules will improve the performance of the system.
6. Monitor and tune the operational system
• To design the access rules to the base relations and user views