Exam Topics: IS481 Database Management and Organization
Fall 2007

Chapter 6: Physical Design

Terminology and definitions: define and give example of:
A relation: A named two dimensional table of data.
A field: The smallest unit of named application data recognized by system software.
A data type: A detailed coding scheme recognized by system software, such as a database management system, for representing organizational data.
Index: A table or other data structure used to determine the location of rows in a file that satisfy some condition.
Compute Usage analysis (fig 6-1)
VARCHAR2: Variable-length character data with a maximum length of 4000 characters; you must enter a maximum field length (e.g., VARCHAR2 (30) for a field with a maximum length of 30 characters). A value less than 30 characters will consume only the required space.
CHAR: Fixed length character data with a maximum length of 2000 character; default length is 1 character (e.g. CHAR 5) for a field with a fixed length of 5 characters, capable of holding a value from 0 to 5 characters long.
LONG: Capable of storing up to 2 gigabytes of one variable length character data field (e.g., to hold a medical instruction or a customer comment).
NUMBER: Positive and negative numbers in the range 10^-130 to 10^126; can specify the precision (total number of digits to the left and right of the decimal point) (e.g., NUMBERS (5) specifies an integer field with a maximum of 5 digits and NUMBERs (5,2) specifies a field with no more than 5 digits and exactly, 2 digits to the right of the decimal point.)
INTEGER: Positive and negative integers with up to 38 digits (same as SMALL INT)
DATE Any date from January 1, 4712 B.C to December 31, 4712 A.D; date stores the century, year, month, day, hour minute, and second.
BLOB: Binary large object, capable of storing up to 4 gigabytes of binary data (e.g., a photograph or a sound clip).
Denormalization? Example: The process of transforming normalized relations into unnormalized physical record specifications.
Data Partitioning (vertical, horizontal): is a division of a logical database or its constituting elements into distinct independent parts. Database partitioning is normally done for manageability, performance or availability reasons.
Horizontal Partitioning: Distributing the rows of a table into several separate files.
Vertical Partitioning: Distributing the columns of a table into several separate physical records.
File organization: (sequential, indexed) A technique for physically arranging the records of a file on secondary storage devices.
Sequential file organization: The storage of records in a file in sequence according to a primary key value.
Indexed file organization: The storage of records either sequentially or nonsequentially with an index that allows software to locate individual records.
• Convert an ER diagram into a relational database design, clearly indicating all the primary keys, foreign keys, and ...

Show full text: 13,302 characters