Chapter 6 Physical Database Design and Performance.pptVIP

Chapter 6:Physical Database Design and Performance Objectives Definition of terms Describe the physical database design process Choose storage formats for attributes Select appropriate file organizations Describe three types of file organization Describe indexes and their appropriate use Translate a database model into efficient structures Know when and how to use denormalization Physical Database Design Purpose–translate the logical description of data into the technical specifications for storing and retrieving data Goal–create a design for storing data that will provide adequate performance and insure database integrity, security, and recoverability Does not include implementing files and databases Physical Design Process Designing Fields Field: smallest unit of named application data in database Corresponds to a simple attribute from logical data model Field design Choosing data type Coding, compression, encryption Controlling data integrity Handling missing data Choosing Data Types Data type: A detailed coding scheme recognized by system software, such as a DBMS, for representing organizational data. Objectives for selecting data types Minimize storage space Represent all possible values Improve data integrity Support all data manipulations Choosing Data Types CHAR–fixed-length character VARCHAR2–variable-length character (memo) LONG–large number NUMBER–positive/negative number INEGER–positive/negative whole number DATE–actual date BLOB–binary large object (good for graphics, sound clips, etc.) Field Data Integrity Default value–assumed value if no explicit value Range control–allowable value limitations (constraints or validation rules) Null value control–allowing or prohibiting empty fields Referential integrity–range control (and null value allowances) for foreign-key to primary-key match-ups Handling Missing Data Substitute an estimate of the missing value (e.g., using a formula) Construct a report listing missing values and resolve unknown values In pro