Databases Reference
In-Depth Information
to the DBMS-specific implementation model (e.g., SQL tables). We also note
that many conceptual data models are obtained not from scratch, but from the
process of
reverse engineering
from an existing DBMS-specific schema [Silber-
schatz 2006]. Our definition of the physical model is given below.
3.
Physical database design
. The physical database design step involves the selection
of indexes, partitioning, clustering, and selective materialization of data. Physical
database design (as treated in this topic) begins after the SQL tables have been
defined and normalized. It focuses on the methods of storing and accessing those
tables on disk that enable the database to operate with high efficiency. The goal
of physical design is to maximize the performance of the database across the
entire spectrum of applications written on it. The physical resources that involve
time delays in executing database applications include the CPU, I/O (e.g.,
disks), and computer networks. Performance is measured by the time delays to
answer a query or complete an update for an individual application, and also by
the throughput (in transactions per second) for the entire database system over
the full set of applications in a specified unit of time.
4.
Database implementation, monitoring, and modification.
Once the logical and
physical design is completed, the database can be created through implementa-
tion of the formal schema using the data definition language (DDL) of a
DBMS. Then the data manipulation language (DML) can be used to query and
update the database, as well as to set up indexes and establish constraints such as
referential integrity. The language SQL contains both DDL and DML con-
structs; for example, the “create table” command represents DDL, and the
“select” command represents DML.
As the database begins operation, monitoring indicates whether performance
requirements are being met. If they are not being satisfied, modifications should be
made to improve performance. Other modifications may be necessary when require-
ments change or end-user expectations increase with good performance. Thus, the life
cycle continues with monitoring, redesign, and modifications.
1.3
Elements of Physical Design: Indexing,
Partitioning, and Clustering
The physical design of a database starts with the schema definition of logical records
produced by the logical design phase. A
logical record
(or record) is a named collection of
data items or attributes treated as a unit by an application program. In storage, a record
includes the pointers and record overhead needed for identification and processing by
the database management system. A
file
is typically a set of similarly constructed records
of one type, and relational tables are typically stored as files. A
physical database
is a col-
