Review Questions
1. Define each of the following terms:
a. File organization A technique for physically arranging the records of a file on secondary storage devices.
b. Sequential file organization Records in the file are stored in sequence according to a primary key value.
c. Indexed file organization Records are either stored sequentially or non-sequentially, and an index is created that allows software to locate individual records.
d. Hashing file organization The address for each record is determined using a hashing algorithm.
e. Denormalization The process of transforming normalized relations into unnormalized physical record specifications.
f. Composite key A key made up of more than one column.
g. Secondary key One or a combination of fields for which more than one record may have the same combination of values.
h. Data type Each unit of a detailed coding scheme recognized by system software, such as a DBMS, for representing organizational data.
i. Bitmap index A table of bits in which each row represents the distinct values of a key and each column is a bit, which when on indicates that the record for that bit column position has the associated field value.
j. Redundant Arrays of Inexpensive Disks (RAID) A set, or array, of physical disk drives that appear to the database user (and programs) as if they form one large logical storage unit.
k. Join index An index on columns from two or more tables that come from the same domain of values.
l. Stripe The set of pages on all disks in a RAID that are the same relative distance from the beginning of the disk drive.
m. Explain plan A command available in most database management systems which displays how the query optimizer will access indexes, use parallel servers and join tables to prepare query results.
2. Match the following terms to the appropriate definitions: d bitmap index f hashing algorithm a page g physical record e pointer b blocking factor c physical file
3. Contrast the following terms:
a. Horizontal partitioning; vertical partitioning Horizontal partitioning is very similar to creating a supertype/subtype relationship because different types of the entity (where the subtype discriminator is the field used for segregating rows) are involved in different relationships, hence different processing. Neither horizontal nor vertical partitioning prohibits the ability to treat the original relation as a whole.
b. Physical file; tablespace A physical file is a named portion of secondary memory (magnetic tape, hard disk) allocated for the purpose of storing records; a tablespace is a named set of disk storage elements in which physical files for database tables may be stored.
c. Physical record; physical file A physical file is a named portion of secondary memory (a magnetic tape or hard disk) allocated for the purpose of storing physical records. Physical records of several types can be clustered together into one physical file in order to place records frequently used together close to one another in secondary memory.
d. Page; physical record Often a page will store multiple physical records. Depending on the computer system, a lengthy physical record may or may not be allowed to span two pages.
e. Secondary key; primary key A secondary key is one or a combination of fields for which more than one record may have the same combination of values; the primary key is one or a combination of fields for which every record has a unique value. Hence, the primary key is a unique identifier for a row.
4. Three major inputs to physical design:
a. Logical database structures developed during logical design
b. User processing requirements identified during requirements definition
c. Characteristics for the DBMS and other components of the computer operating environment
5. Key decisions in physical database design:
a. Choosing the storage format
