Structured Query Language (SQL)

As we had seen in the previous article, DBMS acts as the interface between the users or the application programs and the database. The users or the application programs would talk to the DBMS using a language called "Structured Query Language". It is commonly referred to as "SQL".


The history of SQL dates back to the 1970s when Dr. E.F. Codd published the paper entitled "A Relational Model of Data for Large Shared Data Banks" in June 1970 in the Association of Computer Machinery (ACM) journal "Communications of the ACM". Codd's model is now accepted as the definitive model for Relational DataBase Management Systems (RDBMS). The language "Structured English Query Language" was developed by IBM Corporation Inc., to use Codd's model. SEQUEL later became SQL. In 1979, Relational Software Inc. (now Oracle) introduced the first commercially available implementation of SQL.


Today, many flavors of SQLs are available in market. Almost each DBMS vendor in the market has got their own flavor of SQL. For example, Oracle has got PL/SQL for it's Oracle RDBMS, Microsoft and Sybase have got T-SQL for their SQL Servers, IBM has got SQL PL for it's DB2 RDBMS and so on. There is a standard of SQL accepted by the ANSI. This standard has gone through many revisions. The first version of ANSI standard (shortly called ANSI SQL) came out in 1986 and it was called "SQL-86" or "SQL-87". After that, many versions have come out namely SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2006, SQL:2008, SQL:2011.


In our articles, we are going to discuss about the T-SQL for Microsoft SQL Server. More on it in the forthcoming articles.


I hope that this article was informative. Please post your comment if you see something wrong, need correction or to appreciate :)

Database Management System (DBMS)

A DataBase Management System, most commonly referred to as "DBMS" is a software that acts as an interface between the user or the application programs and the database. The users or the application programs talk with the DBMS using language called Structured Query Language (SQL) which will be dealt with in detail in forthcoming posts. The DBMS in turn communicates with the database to fetch data, update data per the user's request. 

The DBMS manages the following:

1. Data - collection of information
2. Database engine - the underlying software that the DBMS uses to create, read, update and delete data
3. Database schema - the skeleton that represents the logical view of the entire database

By managing the above three components, the DBMS provides the following:

1. Concurrency - the ability of the data being accessed by more than one user at the same time
2. Security - protection for the data
3. Data integrity - data being intact
4. Uniform administration procedures

DBMS can offer both physical and logical data independence.

1. Physical independence - users and applications need not know the physical structure of the data
2. Logical independence - users and applications need not know where the data is stored

There are many types of DBMSes. Some of them are briefed below. Please note that just a very brief introduction is given here on each of the DBMSes. The in-depth study of each type is not in the scope of this article.

1. Relational DBMS (RDBMS)
2. NoSQL DBMS
3. In-memory DBMS
4. Columnar DBMS (CDBMS)

Relational DBMS:
A relational database management system (RDBMS) is a database engine/system based on the relational model specified by Edgar F. Codd. The most important relational database features include the ability to use tables for data storage while maintaining and enforcing certain data relationships.
To make it simple, relational databases store data in multiple tables that are related to each other.

NoSQL DBMS:
NoSQL stands for Not Only SQL. There are few constraints that the RDBMS presents. For example, the amount of data that can be stored. NoSQL, in a way suits to almost all the needs. There are many NoSQL products available out there in the market each with unique feature. The situation where the NoSQL RSBMS would be of utmost use would be to design a software that needs more than one storage mechanism that keeps on changing based on the needs. NoSQL does not have a prescriptive definition but we can make a set of common observations such as:

 -> Not using the relational model
 -> Running well on clusters
 -> Mostly open-source
 -> Schema-less

In-memory DBMS:
An in-memory DBMS is the one in which the data are stored in the main memory instead of hard disk. This eliminates the latency and overhead of moving the data back and forth between the
cache memory and the disk and vice-versa. It also reduces the instructional set that's required to access data. To enable more efficient data storage and access, data may be stored in a compressed format.

Columnar DBMS:
A columnar DBMS is one in which data dare stored in columns instead of rows. This increases efficiency of writing and reading data to and from hard disk storage. It speeds up the query
processing. One of the main benefits of the columnar DBMS is that data can be highly compressed. The compression permits columnar operations like MIN, MAX, SUM, COUNT, AVG to be performed very rapidly.

I hope that this article was useful. Please post your comment if you see something wrong, need correction or to appreciate :)