This physical methodology is the third and final phase of the database design methodology. Here, the designer must decide how to translate the logical database design (i.e. the entities, attributes, relationships, and constraints) into a physical database design which can ultimately be implemented using the target DBMS. As the various parts of physical database design are highly reliant on the target DBMS there may be more than one method of implementing any given portion of the database. Consequently to do this work appropriately, the designers must be fully aware of the functionality of the target DBMS and must recognize the advantages and disadvantages of each alternative approach for a particular accomplishment. For some systems the designer may also need to select a suitable storage space / strategy that can take account of intended database usage.
What is Physical Database Design?
It is the process of making a description of the execution of the database on secondary storage which describes the base relations, file organizations as well as indexes used to gain efficient access to the data and any associated integrity constraints and security measures.
Comparison of Logical and Physical Database Design
In designing and presenting a database design methodology, you have to divide the design process into 3 main stages or steps also known as Database development life cycle. These steps or stages are:
- Logical and
- Physical database design (as studied in the earlier chapter)
The phase prior to physical design is the logical database design which is largely independent of implementation details; such as the specific functionality of the target DBMS and application programs but is reliant on the target data model. The outcome of this process is a logical data model which consists of an ER / relation diagram, relational schema and supporting documents that depicts this model, such as a data dictionary.
Logical database designs are concerned with the “what” and in contrast physical database design is concerned with the “how”. It requires diverse skills that are often found in different people. In particular, the physical database designer must know how the computer system hosts the DBMS and how it operates and must be fully conscious of the working of the target DBMS.
Steps Required for Implementing Physical Methodology
The steps of the physical database design methodology are as follows:
- Transform the logical data model for target DBMS
- Design base relations
- Design representation of derived data
- Design general constraints
- Design file organizations and indexes
- Analyze transactions
- Choose file organizations
- Choose indexes
- Estimate disk space requirements
- Design user views
- Design security mechanisms
- Consider the introduction of controlled redundancy
- Monitor and tune the operational system
Common Characteristics of a Physical Data Model
- It typically illustrates data requirements for a single project or application. Sometimes even a part of an application
- May be incorporated with other physical data models by means of a repository of shared entities
- It typically includes 10-1000 tables; although these numbers are highly variable, depending on the scope of the data model
- It has the relationships between tables that address cardinality and nullability (optionality) of the relationships
- Designed and developed to be reliant on a specific version of a DBMS, storage location of data or on technology
- Database columns will have data types with accurate precisions and lengths assigned to them. Columns will have nullability (optional) assigned
- Tables and columns will have specific definitions