What is Physical Database Design
- 717
Physical Database Design
Physical design is the last stage of the database design process. The major objective of physical database design is to implement the database as a set of records, files, indexes, and other data structures.
Major Inputs to Database Design
Three major inputs to physical database design are as follows:
1. Logical Database Structure
These are developed during logical database design such as normalized relations.
2. User Processing Requirements
It includes the size and frequency of database usage, response time, security, backup, and recovery, etc.
3. Characteristics of DBMS X
It includes the characteristics of DBMS and other components of the computer operating environment.
Components of Physical Database Design
Different components of physical database design are as follows:
1. Data Volume and Usage Analysis
It is used to estimate the size or volume and usage patterns of the database. The estimate
of database size is used to select the physical storage devices. It is also used to determine the
costs of storage. The estimate of usage patterns is used to select file organization and access
methods. It is also used to plan for the use of indexes and a strategy for data distribution.
2. Data Distribution Strategy
Many organizations are using distributed computer networks nowadays. These
organizations face a significant problem in physical database design. The problem is that they
have to decide and select nodes or sites in-network at which data will be located physically. The basic data distribution strategies are as follows:
i. Centralized
In this strategy, all data is located at a single site. It is simple and easy to conduct. This strategy has three disadvantages:
- Data stored at remote sites are not accessible readily.
- Data communication costs may be very high.
- The database system fails totally when the central system fails.
ii. Partitioned
In this strategy, the database is divided into partitions or fragments. Each partition is
assigned to a particular site. The major advantage of this strategy is that data is moved closer
to the local users. Data becomes more easily accessible.
iii. Replicated
In this strategy, the full copy of the database is assigned to more than one site in the
network. This strategy maximizes local access. But it creates update probelms because each
database change must be reliably processed and synchronized at all sites.
iv. Hybrid
In this strategy, the database is divided into critical and non-critical fragments. The
critical fragments are stored at multiple sites. The non-critical fragments are stored at one site
only.
3. File Organization X
File organization is a technique for physically arranging the records of a file on secondary devices. The system designer must recognize several constraints for selecting a file organization. These constraints include the following:
- Physical characteristics of secondary storage devices
- Available operating systems and file management software
- User requirements for storing and accessing data
Criteria to Select File Organization
The criteria for selecting a file organization are as follows:
- Fast access for data retrieval
- High throughput for processing transactions
- Efficient use of storage space
- Protection from failure or data loss
- Minimizing need for data re-organization
- Security from unauthorized use
File Organization Methods
The files are organized on storage media in the following methods:
a. Sequential Files
The records in sequential file organization are stored in sequence. A sequence means the records are stored one after the other. The records can be retrieved only in the sequence in which they were stored. The principal storage media for sequential files is magnetic tape.
The major disadvantage of sequential access is that it is very slow. If the the last record is to be retrieved, all preceding record are read before reaching the last record.
b. Direct or Random Files
The records in direct file organization are not stored in a particular sequence. A key value of a record is used to determine the location to store the record. Each record is accessed directly without going through the preceding records. This file organization is suitable for storing data on disk. Direct file organization is much faster than sequential file organization for finding a specific record. A problem may occur in this type of files known as synonym. The problem occurs if the same address is calculated to store two or more records.
c. Indexed Sequential Files
In indexed sequential file organization, records are stored in ascending or descending order. The order is based on a value called key. Additionally, indexed file organization maintains an index in a file. An index consists of key values and the corresponding disk address for each record in the file. Index refers to the place on a disk where a record is stored. The index file is updated whenever a record is added or deleted from the file. The records in indexed file organization can be accessed in sequential access as well as random access or direct access. The records in this file type require more space on storage media. This method is slower than direct file organization as it requires to perform an index search.
4. Indexes
An index is a table that is used to determine the location of rows in a table. Indexes are used to speed up the sorting and searching process. The performance of database is improved with these indexes. The index may be created on primary key, secondary key and foreign key etc.
5. Integrity Constraints
Database integrity means the correctness and consistency of data. It is another form of database protection. Integrity is related to the quality of data. Integrity is maintained with the help of integrity constraints. These constraints are the rules that are designed to keep data consistent and correct. They act like a check on the incoming data. It is very important that a database maintains the quality of the data stored in it. DBMS provides several mechanisms to
enforce integrity of the data.
