Query Engine

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.

Buffer Manager

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.

Storage Manager

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.

Transaction Control

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 a table.

Recovery Manager

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 state.

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