SQL – Structured Query Language

IBM has developed the SQL in the 1970’s as a part of SystemR Project . It is purely called SEQUEL. ANSI and ISO has standardize the SQL – Structured Query Language.

The standard SQL has the following components:

  • DDL (Data Definition Language)
  • Interactive DML (Data Manipulation Language)
  • Integrity
  • View Definitions
  • Transaction Control (ACID Properties)
  • Embedded SQL
  • Dynamic SQL
  • Authorization

SQL DDL allows specification of not only a set of relations, but also gives following information:

  • Schema for each relation
  • Associated Domain Values with each attribute
  • Integrity Constraints
  • Indexes to be maintain for each relation
  • Security and Authorization
  • Physical Storage Structure

Query Evaluation

SQL queries are translated into extended form of relational algebra. The query evaluation plans are represented as trees of relational operators. The Query Evaluation plan also identifies which algorithm are must be use at each node of the tree.

The relational operator subs as Building Blocks for evaluating building query. The implementation of the relational operators is carefully optimized for better performance. RDBMS maintain information about every table, view and index in a structure called as Catalog, Data Dictionary and System Catalog file. A Catalog can contain information about Page Size, Buffer Pool Size, Table, Index, Views.

For each Table the catalog maintains the following information:

  • Table Names
  • File Names
  • File Structure
  • Attribute Types
  • Index Names
  • Integrity Constraints on that table

For each Index the catalog maintain following information:

  • Index Name
  • Index Structure
  • Search Key Attribute

For each View the Catalogue will maintain following information:

  • View Name
  • View Structure
  • Table Name (Definition)

The Catalog also maintains statics about updated tables, indexes periodically. The catalogue also store following additional information:

  • Cardinality – Number of touples for each table
  • Size – Number of Pages for each Table
  • Index_Size – Number of Pages
  • Index_range – Minimum, Maximum and Present index Key value

The catalogue will also contain information about users and related accounting and authorisation information. The system catalogue is stored itself as a table and can be queried. DBMS venders may vary in their catalogue schema designs, but the catalogue is implemented as collection of tables and essentially describes all the data stored in the database.

” Bless for You, Peace for the World ” – Purvik

Leave a Reply

Your email address will not be published. Required fields are marked *