How can we help?

You can also find more resources in our Help Center.

30 terms

Database Management: Chapter 5

Physical Database Design and Performance
STUDY
PLAY
Requirements for Physical DB Design
Normalized relations, including estimates for the range of the number of rows in each table
Definitions of each attribute, along with physical specifications such as maximum possible length
Descriptions of where and when data are used in various ways
Expectations or requirements for response time and data security, backup, recovery, retention, and integrity
Description of technologies used for implementation
Field
Smallest unit of application data recognized by system software
Data Type
Detailed coding scheme recognized by system software, such as a DBMS, for representing organizational data
Objectives in Selecting a Data Type
Represent all possible values
Improve data integrity
Support all data manipulations
Minimize storage space
VARCHAR2
Variable-length character data with a maximum length of 4,000 characters
CHAR
Fixed-length character data with a maximum length of 2,000 characters; default length is one
CLOB
Character large object, capable of storing up to 4 gigs of one variable-length character data field
NUMBER
Positive or negative number in the range 10^130 and 10^126; can specify the precision and scale
INTEGER
Positive or negative integer with up to 38 digits (same as SMALL INT)
DATE
Any date from January 1, 4712 BC to December 31, 9999 AD; date stores century, year, month, day, hour, minute, and second
BLOB
Binary large object, capable of storing up to 4 gigs ob binary data
Common Data Integrity Controls
Default value
Range control
Null value control
Referential integrity
Methods of Handling Missing Data
Substitute estimated values
Track missing data (trigger)
Perform sensitivity testing so missing data is ignored
Denormalization
Process of transforming normalized relations into non-normalized physical record specfications
Horizontal Partitioning
Distribution of rows of a logical relation into several separate tables
Vertical Partitioning
Distribution of columns of a logical relation into several separate physical tables
Physical File
Named portion of secondary memory (such as hard disk) allocated for the purpose of storing physical records
Tablespace
Named logical storage unit in which data from one or more database tables, views, or other database objects may be stored
Division of Tablespace
Segments: logical units consisting of one table, index, or partition
Extents: contiguous section of disk storage space
Data Blocks: smallest unit of storage
File Organization
Technique for physically arranging the records of a file on secondary storage devices
Important Factors of File Organization
Fast data retrieval
High throughput for processing data input and maintenance transactions
Efficient use of storage space
Protection from failures or data loss
Minimizing need for reorganization
Accommodating growth
Security from unauthorized use
Sequential File Organization
Storage of records in a file in sequence according to a primary key value
Indexed File Organization
Storage of records either sequentially or non-sequentially with an index that allows software to locate individual records
Index
Table or other data structure used to determine in a file the location of records that satisfy some condition
Secondary Key
One field or combination of fields for which more than one record may have the same combination of values (non-unique key)
Join Index
Index on columns from two or more tables that come from the same domain of values
Hashed File Organization
Storage system in which the address for each record is determined using a hashing algorithm
Hashing Alogirthm
Routine that converts a primary key value into a relative record number or relative file address
Hash Index Table
File organization that uses hashing to map a key into a location in an index, where there is a pointer to the actual data record matching the hash key
Pointer
Field of data indicating a target address that can be used to locate a related field or record of data