Database Languages in DBMS

Database Languages

A database system provides the languages to specify the database schema and manipulating the data in the database. Database language can be mainly categorized into two types:

• Data Definition Language (DDL)
• Data Manipulation Language (DML)

Data Definition Language (DDL)

Data definition language is the specification notation for defining the database schema.

• Used by the Database Administrator (DBA) and database designers to specify the conceptual schema of a database.
• In many DBMSs, the DDL is also used to define internal and external schema (views).
• In some DBMSs, separate storage definition language (SDL) and view definition language (VDL) are used to define internal and external schema.
• SDL is typically realized via DBMS commands provided to the DBA and database designer

• Example:CREATE TABLE account(account-number CHAR(10), balance INTEGER)

• Execution of the above DDL statement creates the account table.
• It updates a special set of tables called the data dictionary.

Data dictionary: DDL compiler generates a set of tables stored in a data dictionary. Simply, Data dictionary is a special set of tables that contain the information about tables. Data dictionary contains metadata (i.e., data about data)

Metadata: Data that describes the database or one of its parts is called metadata. The schema of a table is an example of metadata

Data storage and definition language is a special type of DDL that is used to specify the storage structure and access methods used by the database system

The DDL provides the facilities to define 

v  Database scheme

v  Database tables

v  Integrity constraints

•  Domain constraints
•  Referential integrity (references constraint in SQL)
•  Assertions
•  Triggers
•  Views

v  Security and Authorization

v  Modify the Scheme

v  The common DDL Commands are: CREATE, ALTER, DROP

Data Manipulation Language (DML)

A Data-manipulation language (DML) is a language that enables users to access or manipulate data organized by the appropriate data model. DML also known as query language. There are basically two classes of DML:

v  Procedural DMLs ( or Low-level DML ): In procedural DMLs, a user specifies what data are required and how to get those data 

v  Declarative (or nonprocedural or high-level ) DMLs: In declarative DMLs a  user specifies what data are needed without specifying how to get those data

v  The data manipulation is:

• The retrieval of information stored in the database
• The insertion of new information into the database
• The deletion of information from the database
• The modification of information stored in the database

v  The SELECT, INSERT, UPDATE, DELETE statements are common DML commands

v  Query: A query is a statement requesting the retrieval of information. SQL is the most widely used query language Select, insert, update, delete etc are the SQL DML statement