Database Management: Chapter 5

30 terms by Lauren_Alena

Create a new folder

Advertisement Upgrade to remove ads

Physical Database Design and Performance

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

Please allow access to your computer’s microphone to use Voice Recording.

Having trouble? Click here for help.

We can’t access your microphone!

Click the icon above to update your browser permissions above and try again

Example:

Reload the page to try again!

Reload

Press Cmd-0 to reset your zoom

Press Ctrl-0 to reset your zoom

It looks like your browser might be zoomed in or out. Your browser needs to be zoomed to a normal size to record audio.

Please upgrade Flash or install Chrome
to use Voice Recording.

For more help, see our troubleshooting page.

Your microphone is muted

For help fixing this issue, see this FAQ.

Star this term

You can study starred terms together

NEW! Voice Recording

Create Set