Databases Reference
In-Depth Information
of business intelligence computing has led to a dramatic increase in the complexity of
the processing that is required.
These factors motivate the need for complex and sophisticated approaches to
physical database design. Why? By exploiting design techniques a practitioner can
reduce the processing time for operations in some cases by several orders of magni-
tude. Improving computational efficiency by a thousand times is real, and valuable;
and when you're waiting at the bank machine to get your money, or waiting for an
analysis of business trading that will influence a multimillion dollar investment deci-
sion, it's downright necessary.
1.2
Database Life Cycle
The database life cycle incorporates the basic steps involved in designing a logical data-
base from conceptual modeling of user requirements through database management
system (DBMS) specific table definitions, and a physical database that is indexed, parti-
tioned, clustered, and selectively materialized to maximize real performance. For a dis-
tributed database, physical design also involves allocating data across a computer net-
work. Once the design is completed, the life cycle continues with database implementa-
tion and maintenance. The database life cycle is shown in Figure 1.2. Physical database
design (step 3 below) is defined in the context of the entire database life cycle to show
its relationship to the other design steps.
1.
Requirements analysis.
The database requirements are determined by interviewing
both the producers and users of data and producing a formal requirements speci-
fication. That specification includes the data required for processing, the natural
data relationships, and the software platform for the database implementation.
2.
Logical database design.
Logical database design develops a conceptual model of
the database from a set of user requirements and refines that model into normal-
ized SQL tables. The goal of logical design is to capture the reality of the user's
world in terms of data elements and their relationships so that queries and
updates to that data can be programmed easily. The
global schema
, a conceptual
data model diagram that shows all the data and their relationships, is developed
using techniques such as entity-relationship (ER) modeling or the Unified Model-
ing Language (UML). The data model constructs must ultimately be integrated
into a single global schema and then transformed into normalized SQL tables.
Normalized tables (particularly third normal form or 3NF) are tables that are
decomposed or split into smaller tables to eliminate loss of data integrity due to
certain delete commands.
We note here that some database tool vendors use the term
logical model
to
refer to the conceptual data model, and they use the term
physical model
to refer
