Database Languages are the set of statements, that are used to define and manipulate a database. A Database language has Data Definition Language (DDL), which is used to construct a database & it has Data Manipulation Language (DML), which is used to access a database.
DDL implements database schema at the physical, logical and external level. While, the DML provides the statements to retrieve, modify, insert and delete the data from the database. In this section, we will discuss the database language in detail.
Database Language in DBMS
Database languages provide the tools to implement and manipulate a database. A database language is comprised of two languages:
DDL and DML are not two distinct languages but they together form a database language. The examples of database languages are SQL, My Access, Oracle, etc.
The figure below displays the detailed classification of database languages.
Data Definition Language (DDL)
DDL defines the statements to implement the database schema. If a clear separation between a logical (conceptual) and a physical (internal) level is not there, then DDL defines both the logical and physical schema and it also defines the mapping between logical and physical schema.
If there is a clear separation between the logical and physical schema, then the storage definition language (SDL) is used to define the physical schema. But today, most of the relational DBMS do not use SDL for specifying the physical schema. Instead, the physical schema is defined using the combination of functions and parameters which allows DBA to map data to the storage.
After implementing logical and physical schema, it’s time to specify the view (external) schema. For that view definition language (VDL) is used, which also maps the view schema to the logical schema. But today in most of the DBMSs, DDL performs the role of VDL.
In all today, DDL specifies all the schemas, i.e. physical, Logical and view schema. The set of statements in DDL used to implement database schema are as follow:
CREATE: This command is used to construct a relation (table) in the database.
ALTER: This command is used to reconstruct the data in the database.
DROP: This command is used to delete a relation in the database or an entire database.
TRUNCATE: This command deletes all the entries from the relation but keeps the relation structure secured in the database.
RENAME: This command renames the relation in a database.
Well, it doesn’t end here, DDL also defines some consistency constraints on the data, stored in the database. Below is the list of constraints specified by the DDL:
(i) Domain Constraints:
Whenever we define any attribute in the database, we must specify its domain. The domain of any attribute defines the constraints on the values that an attribute can take. For example, if we specify an attribute “Name” for the “Student” relation then by specifying “Name char (25)”, would restrict the attribute to take only character values.
(ii) Referential Integrity Constraints:
A value for a given set of attributes in one relation must also appear for the same set of attributes in another relation. For example, we have a record of a student, and his department name is mentioned in the record. Now, we must have that department name in the department relation.
Note: Referential integrity may get violated while modifying the database. To resolve this, the action that causes violation must be rejected.
(iii) Assertion Constraint:
A constraint that must always be satisfied in a database is assertion constraint. Like, domain constraint and referential integrity constraints are also an assertion constraint. For example, a student record must have a roll number in it; its roll number can’t be Null. These type of constraints are assertion constraints. If the assertion constraint is violated the modification is rejected.
(iv) Authorization Constraint:
We cannot allow every user to access and modify the database. So, certain authorization constraints are introduced those are, read authorization, insert authorization, update authorization and delete authorization, which allows the user to read, add new data, modify the database and delete the data in database respectively.
Data Manipulation Language (DML)
Data Manipulation Language has a set of statements that allows users to access and manipulate the data in the database. Using DML statements user can retrieve, insert, delete or modify the information in the database.
The Data Manipulation Languages are further of two types, procedural and non-procedural languages:
(i) Procedural DMLs:
Procedural DMLs are considered to be low-level languages, and they define what data is needed and how to obtain that data. The procedural DMLs are also called one-at-a-time DMLs as it retrieves and processes each record separately.
(ii) Non-Procedural DMLs:
Non-Procedural DMLs are high-level languages, and they precisely define what data is required without specifying the way to access it. The non-procedural DMLs are also called set-a-time DMLs; this is because a non-procedural DMLs can retrieve several records using a single DML command.
Non-procedural DMLs are also called declarative languages. As it only declares what data is required instead of specifying how it should be obtained. Generally, the end-users use the high-level (non-procedural) DMLs for specifying their requirement.
Let us discuss some statements of DML:
SELECT: This command reads and pulls out therecords from the database.
INSERT: This command adds new records to the database.
UPDATE: This command modifies the data in the database.
DELETE: This command deletes the records in the database.
So this is all about the database languages which help in implementing the database schema and then install the data in the database.