Database Management: Chapter 5

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

CLOB

Character large object, capable of storing up to 4 gigs of one variable-length character data field

Common Data Integrity Controls

Default value Range control Null value control Referential integrity

Data Type

Detailed coding scheme recognized by system software, such as a DBMS, for representing organizational data

Vertical Partitioning

Distribution of columns of a logical relation into several separate physical tables

Horizontal Partitioning

Distribution of rows of a logical relation into several separate tables

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

Pointer

Field of data indicating a target address that can be used to locate a related field or record of data

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

CHAR

Fixed-length character data with a maximum length of 2,000 characters; default length is one

Join Index

Index on columns from two or more tables that come from the same domain of values

Tablespace

Named logical storage unit in which data from one or more database tables, views, or other database objects may be stored

Physical File

Named portion of secondary memory (such as hard disk) allocated for the purpose of storing physical records

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

Secondary Key

One field or combination of fields for which more than one record may have the same combination of values (non-unique key)

INTEGER

Positive or negative integer with up to 38 digits (same as SMALL INT)

NUMBER

Positive or negative number in the range 10^130 and 10^126; can specify the precision and scale

Denormalization

Process of transforming normalized relations into non-normalized physical record specfications

Objectives in Selecting a Data Type

Represent all possible values Improve data integrity Support all data manipulations Minimize storage space

Hashing Alogirthm

Routine that converts a primary key value into a relative record number or relative file address

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

Field

Smallest unit of application data recognized by system software

Indexed File Organization

Storage of records either sequentially or non-sequentially with an index that allows software to locate individual records

Sequential File Organization

Storage of records in a file in sequence according to a primary key value

Hashed File Organization

Storage system in which the address for each record is determined using a hashing algorithm

Methods of Handling Missing Data

Substitute estimated values Track missing data (trigger) Perform sensitivity testing so missing data is ignored

Index

Table or other data structure used to determine in a file the location of records that satisfy some condition

File Organization

Technique for physically arranging the records of a file on secondary storage devices

VARCHAR2

Variable-length character data with a maximum length of 4,000 characters