Translate the logical description of data into the technical specifications of storing and retrieving data.
Create a design for storing data that will provide adequate performance and ensure database integrity, security and recoverability. Basically, the primary goal of physical database design is data processing efficiency.
* Physical Design Process
Inputs such as normalized relations and estimation of their volume; definitions of each attributes; expectations and requirements for response time, data security, back-up, retention and recovery; and integrity; and description of the DBMS technology used to implement the database leads to critical decisions that will affect the integrity and performance of the system such as: * 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 maximize data integrity. * Grouping attributes from the logical data model into physical records. * Arranging similarly structured records in secondary memory (primary hard disks) so that individual and groups of records (called file organizations) can be stored, retrieved and updated rapidly. Consideration may also be given to protect data and recovering data after errors are found. * Selecting structures (called indexes and database architectures) for storing and connecting files to make retrieval of related data more efficient. * Preparing strategies for handling queries against the database that will optimize performance and take advantage of the file organizations and indexes that the users have specified. Efficient database structures will be of benefit only if queries and the management systems that handle those queries are tuned intelligently to use those structures.
Data Volume and Usage Analysis
The physical design of data base requires certain information that should have been collected and produced during initial phases of system development. These include: * Normalized relations, including volume estimates,
* Definition of each attribute
* Description of where and when data are used entered, retrieved, deleted and updated. * Requirement for response time and data security, back up, recovery and integrity etc. The above information can be generated by data volume and usage analysis. Thus, data volume and ‘frequency-of-use’ statistics are critical inputs. The first step one needs to take in physical design of the data base is to estimate the size and usage pattern of the database. The volume and frequency statistics are generated during the system analysis phase of the system development process when system analysts are studying current and proposed data processing activities. The data volume statistics represent the size of the business and should be calculated assuming business growth over a considerable period of time. The access frequencies are from the timing of events, transactions volumes and reporting and querying activities. Ad-hoc query may increase frequency of usage. The data volume and usage analysis helps in identifying the key areas where the greatest attention needs to be given in order to achieve the best possible performance. Data volume and usage analysis are critical inputs to the physical database design process. The volume and frequency statistics are generated during the systems analysis phase of the systems development process when systems analysts are studying current and proposed data processing and business activities. The data-volume statistics represent the size of the business, and should be calculated assuming business growth over at least a several-year period. The access frequencies are estimated from the timing of events, transaction volumes, and reporting and querying activities. Since many databases support ad hoc accesses, and such accesses may change significantly over time, the access frequencies tend to be less certain than the...