What is physical database design?

What is physical database designe?

Physical database design is the last step of database design process. Physical database design can be defined as the conversion of logical data model into actual database structures like files, stored procedures, indexes etc.

Major inputs to physical database design

There are three major inputs to physical database design:

Logical database structures: These are developed during logical database design such as normalized relations.

User processing requirements: It includes size and frequency of use of the database, response time, security, backup, recovery etc.

Characteristics of the DBMS: It includes characteristics of the DBMS and other components of the computer operating environment.

Components of physical database Design

The physical database design has the following components:

Data volume and its usage

Data distribution strategy

File organization

Indexes

Integrity constraints

Data volume and its usage

In physical database design, the first important issue is the estimation of size or column and usage patterns of database

The estimation of database size is used to select physical storage devices. It is also used to estimate the costs of storage.

The estimation of usage patterns are used to select the file organization and access methods. It is also used to plan for the use of indexes and to plan a strategy for data distribution.

Data distribution strategy

Many organizations today have distributed computer networks. These organizations face data distribution problem in physical data base design. They have to decide nodes in the network at which data will be located physically.

The basic data distribution strategies are:

Centralized In this strategy, all data is located at a single site. It is easy to implement but it has three disadvantages:

Data is not readily accessible at remote sites.

Data communication costs may be very high.

The database system fails totally when the central system fails.

Partitioned In this strategy, the database is divided into partitions each partition is assigned to a particular site. Major advantage of this strategy is that data is moved closer to local users. Data becomes more easily accessible.

Replicated In this strategy, full copy of database is assigned to more than one site on the network. This approach maximizes local access but it creates update problems.

Hybrid In this strategy, the database is partitioned into critical and non-critical fragments. Non-critical fragments are stored at only one site. The critical fragments are stored at multiple sites.

File organization

File organization is a technique for storing data on secondary storage devices so that it can be retrieved easily when needed. The system designer must recognize several constraints for selecting a file organization. These constraints include:

Physical characteristics of the secondary storage devices.

Available operating systemsand file management software

User requirements for storing and accessing data.

Criteria for selecting file organization:

Follow is the criteria for selecting file organization:

Fast access for data retrieval.

Efficient use of storage space

Protection from failure or data loss.

Minimizing need for re-organization.

Accommodating growth.

Security from un-authorized use.

Indexes

An index is a table that is used to fine the location of record in a table. The indexes are created to speed up the searching process. An index ma be created on primary key, secondary key, foreign key etc.

Integrity Constraints

Data integrity refers to the correctness and consistency of data. It is another form of database protection or security. Security involves protecting the data from unauthorized operations. Data integrity is concerned with the quality of data itself. It is maintained with the help of integrity constraints the integrity constraints are the rules that can be applied to database so that the correct data can be entered into database.