* The information needed for physical file and DB design include these requirements: 1) Normalized relations, including estimates for the range of the number of rows in each table 2) Definitions of each attribute, along with physical specifications such as maximum possible length 3) Descriptions of where and when data are used in various ways (entered, retrieved, deleted, and updated, including typical frequencies of these events) 4) Expectations or requirements for response time and data security, backup, recovery, retention, and integrity 5) Descriptions of the technology (DBMS) used for implementing the DB * Physical DB design requires several critical decisions that will affect integrity and performance of the application system: * Data Type: choosing the storage format for each attribute from the logical data model * Giving the DBMS guidance regarding how to group attributes from the logical data model into physical records * Giving the DBMS guidance regarding how to arrange similarly structured records in secondary memory (primarily hard disks), using a structure called a file organization, so that individual and groups of records can be stored, retrieved, and updated rapidly * Selecting structures, including indexes and the overall database architecture, for storing and connecting files to make retrieving related data more efficient * Preparing strategies for handling queries against the DB that will optimize performance and take advantage of the file organizations and indexes that you have specified * Field: is the smallest unit of application data recognized by system software, such as a programming language or database management system a field corresponds to a simple attribute in the logical data model * Data Type: is a detailed coding scheme recognized by system software, such as DBMS, for representing organizational data * Selecting data type involves 4 objectives:
1) Represent all possible values
2) Improve data integrity
3) Support all data manipulations
4) Minimize storage space
* Data Integrity Controls:
* Default Value: the value a field will assume unless a user enters an explicit value for an instance of that field * Range Control: limits the set of permissible values a field may assume * Null Value Control: each PK must have an integrity control that prohibits a null value * Referential Integrity: a form of range control in which the value of that field must exist as the value in some field in another row of the same or a different table the range of legitimate values comes from the dynamic contents of a field in a DB table, not from some pre-specified set of values * Denormalization: the process of transforming normalized relations into non-normalized physical record specifications may partition a relation into several physical records, may combine attributes from several relations together into one physical record, or may do a combination * Horizontal Partitioning: implements a logical relation as multiple physical tables by placing different rows into different tables, based on common column values * Advantages: Efficiency, Local Optimization, Security, Recovery and Uptime, Load Balancing * Disadvantages: Inconsistent access speed, Complexity, Extra space and update time * Veritcal Partitioning: distributes the columns of a logical relation into separate tables, repeating the PK in each of the tables * Tablespace: is a named logical storage unit in which data from one or more database tables, views, or other database objects may be stored * Segments: each tablespace consists of logical units consisting of one table, index, or partition, which, in turn, are divided into extents (a contiguous section of disk storage space) these, finally, consist of a number of contiguous data blocks, which are the smallest unit of storage * File organization: is a technique...