Database Languages and Data Models

Structured Query Language (SQL) is the language understood by DBMS for defining and manipulating data in a database.  The statements used in SQL are non procedural, i.e., SQL statements require a user to specify what to be done on data without specifying how to get the work done on the data.

The statements provided in SQL can be categorized into four major groups, namely:

1. Data Definition Language (DDL)
2. Data Manipulation Language (DML) and
3. Data Control Language (DCL)
4. Transaction Control Language (TCL)

The first set of statements defined in SQL are for specifying the database schema (structure) and is called Data Definition Language (DDL).  Using DDL, we can specify a database schema by defining tables, integrity constratins, assertions etc.  For instance, the following statement defines a table named account:

create table account(account_number char(10), balance integer);

Execution of the above DDL statement will create a table structure named account in the database for storing the details about bank accounts such as account_number and balance.  In addition, it updates the data dictionary (also called system catalogue) with the metadata of the account table.  

 

The data dictionary is considered to be a special type of database, which can only be accessed and updated by the database system itself before reading or modifying the actual data stored in the database.

 

Here is another example for DDL Statement that defines the department table:

 

create table department(dept_name char(20), building char(15), budget numeric(12,2));

The above DDL statement is able to create the department table with three columns: dept_name, building, and budget, each of which has a specific data type associated with it.

The DDL is also used to specify additional properties of data stored in the database.  For example, suppose the balance on an account should not fall below Rs. 500, it can be specified on the database by imposing constrains called consistency constraints.

 

The data values stored in the database must satisfy the consistency constrains imposed on the field in which it is stored.  The database system checks these constraints every time the database is updated.

Data Manipulation Language (DML):

DML refers to the set of SQL Statements that enable users to access or manipulate data organized in the database.  There are two types of DML Statements namely Procedural DML and Declarative DML:

• Procedural DMLs require a user to specify what data are needed and how to get those data.
• Declarative DMLs (also referred to as non procedural DMLs) require a user to specify what data are needed without specifying how to get those data.
  

Some of the operations that can be done using DML include the following:

• Insertion of new information into the database
• Retrieval of information stored in the database
• Modification of information stored in the database
  
• Deletion of information from the database

A query is one of the DML statements used for requesting the retrieval of information from a database.  A query takes as input several tables (possibly only one) and always returns a single table.

Here is an example of an SQL query that finds the names of all instructors in the History department:

select instructor.name from instructor where instructor.dept_name = "History";

The query specifies that those rows from the table instructor where the dept_name is History must be retrieved, and the name attribute of these rows must be displayed.  More specifically, the result of executing this query is a table with a single column labeled name, and a set of rows, each of which contains the name of an instructor whose dept_name, is History.

Queries may involve information from more than one table.  For instance, the following query finds the instructor ID and department name of all instructors associated with a department with budget of greater than Rs. 95,000:

select instructor.ID, department.dept_name from instructor, department where instructor.dept_name = department.dept_name and department.budget > 95000;

Data Models:

Underlying the structure of a database is the data model: a collection of conceptual tools for describing data, data relationships, data semantics, and consistency constrains.  A data model provides a way to describe the design of a database at the physical, logical and view levels.  The following are some of the data models used in database systems for describing and organizing data:

1. Network Data Model
2. Hierarchical Data Model
3. Relational Data Model
4. Object Data Model

Historically, the network data model and hierarchical data model preceded the relational data model.  These models were tied closely to the underlying implementation, and complicated the task of modeling data.  The relational data model is the most widely used data model, and a vast majority of current database systems are based on the relational model.

Relational Data Model uses a collection of tables to represent both data and the relationships among those data.  Each table has multiple columns, and each column has a unique name.  Tables are also known as relations.

The relational model is an example of a record-based model.  Each table in the relational database contains records of a particular type.  Each record type defines a fixed number of fields, or attributes.  The columns of the table correspond to the attributes of the record type.

Object Oriented Programming (OOP), especially programming using C++ and Java has become the dominant software-development methodology.  This led to the development of an object-oriented data model that allows data to be stored as objects in the database.