Physical Database Design-Purpose of the physical database design

- Process of producing a description of the implementation of the database on secondary storage; it describes the base relations, file organizations, and indexes used to achieve efficient access to the data, and any associated integrity constraints and security measures.
- Does not include implementing files and databases ( i.e. creating them and loading data into them)
- It produce technical specifications use during the implementation phase
- The database can be single centralized or multiple distributed sites (here single centralized only)
- Physical database design is critical. Decisions made during this stage have a major impact on data accessibility, response times, securing and user friendliness
- Database administration plays a major role in physical database design.
- In most situations, many physical database design decisions are implicit or eliminated when you choose the database management technologies.
- Since many organizations have standards for OS, DBMS, and data access languages, you must deal only with those choices.
- The primary goal of physical database design is data processing efficiency.
- With ever-decreasing costs for computer technology (both speed and space), it is important for you to design the physical database to minimize the time required by users to interact with the IS
- Designing physical files and databases requires certain information:
- Normalized relations, including volume estimates
- Definitions of each attribute
- Descriptions of where and when data are used: entered, retrieved, deleted, and updated (including frequencies)
- expectations or requirements for response time and data security, backup, recovery, retention, and integrity
- descriptions of the technologies (DBMS) used for implementing the database Physical database design requires several critical decisions that will affect the integrity and performance. These key decisions include:
- Choosing the storage format (called data type) for each attribute from the logical data model. The format is chosen to minimize storage space and to minimize data integrity
- Grouping attributes from the logical data model into physical records
- Arranging similarly structured records in secondary memory (hard disk or any other means) so that individual or group of records (file organizations) can be stored, retrieved, and updated rapidly.
Consideration must be given to protecting and recovering data after errors are found
- Selecting structures (called indexes and database architectures) for storing and connecting files to make retrieving related data more efficient
- Preparing strategies for handling queries against the database that will optimize performance and take advantage of the file organization and indexes