The Query Engine receives all the SQL
commands from the user. Its sub-system
consists of a Parser, Optimizer and Executor.
The parser checks the syntax of SQL
commands, checks the existence of requested
tables and columns, and checks user privileges
to perform the operation.
An optimizer checks the existence of indexes
to satisfy the query chooses among several
alternate ways to execute the command and
prepares an best possible plan of execution.
The executor takes the actions required to
execute the plan or requests.
The Buffer Manager lies in between the query
engine and storage manager. This sub-system
is responsible for memory management. It keeps
the active part of the data from the data files in
main memory and performs the necessary
replacement of data to and from the storage
manager, and supplies the requested data to
the query engine. Its sub-system consists of a
data buffer for table and index file, a metadata
buffer for data dictionary files and redo buffer
for the log files. In addition the buffer manager
also contains one more component the undo
buffer that keeps copies of data currently being
modified by active transactions.
The Storage Manager is the backend of the
system. It manages different categories of data
files in the file system. This system deals with
efficient storage and retrieval of data to and
from the OS file systems. Its sub-system
consists of the data files containing the relational
tables, data dictionary that is also known as
metadata, contains list of all the tables and
indexes, privileges of the users in the data,
business rules associated with the data etc.,
and log files generated by the recovery manager.
The purpose of Transaction Control is to
provide transactions and concurrency.
Transaction allows the users to manipulate data
atomically and concurrency where multiple
concurrent users can access the data in a
consistent way. The transaction control subsystem consists of Transaction Manager and
Lock Manager components. The transaction
manager takes care of atomic manipulation of
data by temporarily storing copies of data. The
lock manager sets locks in tables or records of
The Recovery Manager is responsible for
keeping copies of data and record in which
changes has been done. So that in case of
primary data files crash, the database can be
restored in a consistent state as just before
the crash occurred.
Its sub-system consists of the Logger that
records any modifications done in the database
in log files, and Backup & Recovery through
which a DBA can save copies of the data files
that can be used later in concurrence with log
files to restore the database in a consistent
Introduction to Basic SQL statements
As we have discussed that, most commercial
RDBMS’s use the Structured Query Language
(SQL) to access the database.
SQL (Structured Query Language) is a standard
language for making interactive queries from
and updating a database such as IBM’s DB2,
Microsoft’s Access, and database products from
Oracle, Sybase, and Computer Associates.
Queries are the backbone of SQL. It is a term
that refers to a widely available set of SQL
commands called clauses. Each clause
(command) performs some sort of function
against the database.
For instance, the create clause creates tables
and databases and the select clause selects
Dec 2007 | Java Jazz Up | 19
|View All Topics
|All Pages of this Issue