Physical database design phase begins once the logical database models have been merged into a finalized enterprise model. The physical database design phase is the last phase in the creation of a relational database. Unlike the conceptual and logical design phases, the physical design phase is dependent on a particular database management system (DBMS). Since the physical design phase targets a specific DBMS, the designer must ensure that the target DBMS offers functionalities that befit the finalized enterprise model. Effective implementation of the physical design phase takes several steps as discussed below.
To kick start the physical design process, the design collects and organizes all information that is the product of the logical design phase. Using relations derived from logical data model and the data dictionary, the designer creates relations in the target DBMS. Each relation from the logical data model should provide the following information;
1. Relation name
2. Relation attributes
3. Primary key and any foreign keys
4. Integrity constraints for any foreign keys
Key information obtained from the data dictionary should include
1. Domain of each attribute
2. Default values for each attribute where applicable
3. The attribute’s value
Using the information from the data dictionary and the logical relations, the designer can use a database definition language (DBDL) to organize the data into a format that is identical to one used by data definition language (DDL).
The designer creates constraints that restrict the data that an attribute can receive. These constraints are in most cases derived from the business rules created for the enterprise in relation to the database. Constraints are DBMS specific.
Defining a secondary index creates a set of keys that enhance efficient data retrieval from a relation while meeting certain conditions.
It maybe inefficient at times to implement a fully normalized database especially where data is updated infrequently or where full normalization contributes significantly to poor database performance. At this point the designer decides whether to denormalize the relations. Denormalization is achieved by duplication of attributes in relations or merger of relations to reduce the number of join operations in data fetching.
Since essentially a database has many different users, the designer provides a way for all kinds of users to access the database using user views that abstract database design details and prohibit direct access.
Stakeholders in any organization have access to different levels of information regarding the organization. Similarly, a database system is meant to serve multiple users or groups of users while limiting what kind of data each user has access to in the database. This is achieved by creating access rules that grant users certain privileges that limit their level of access.
The physical database is implemented using database definition language (DDL). DDL is a set of commands that are used in creating the physical database. These commands are used to achieve the following tasks
a. Creating a relation using CREATE TABLE command
b. Creating a schema using CREATE SCHEMA command
c. Altering relations and views e.g. when adding a column to a relation using the ALTER command
d. Dropping relations and views using the DROP command
Primary and foreign keys are essential components of a relational database. Primary keys are used to ensure that data in a specific column is unique. This helps in uniquely identifying any record in a relation. Primary keys solely or in combination with other candidate keys form the points of linkage between relations. Normalization often results in putting related data in different relations. Foreign keys are used to link this data between different relations.