A DBMS must provide appropriate languages and interfaces for different categories of users to express database queries and updates.
Database Languages:
1.Data Definition Language (DDL): Used to specify the database schema
2.Data Manipulation Language (DML): Used to express database queries and updates.
In practice, DDL and DML are not separate languages; instead, they are parts of a single database language, such as the widely used SQL (Structured Query Language).
DDL is used for specifying the database schema. It is used by database administrators (DBA) and database designers to define the conceptual schema of a database.
DDL is used for creating tables, schemas, indexes, constraints, etc., in the database.
DDL is also used to store metadata such as the number of tables, schemas, their names, indexes, columns in each table, constraints, etc.
• The DDL compiler generates a set of table templates stored in a data dictionary.
• The data dictionary contains metadata (i.e., data about data) such as: Database schema,Table names,Column names and types, Integrity constraints, Primary key, Authorization (Who can access what)
• CREATE: Used to create database objects like tables, views, etc.
• ALTER: Used to modify the structure of existing database objects.
• DROP: Used to delete database objects.
• TRUNCATE: Used to remove all records from a table, but keep the structure.
• RENAME: Used to rename a database object.
DML is used for accessing and manipulating data in a database. It allows users to insert, update, delete, and retrieve data from the database.
DML is also known as Query Language.
A query is a statement that requests the retrieval of information.
⇒Users specify what data is required and how to get that data.
⇒Procedural DMLs are often embedded into high-level programming languages like Java.
⇒Example: PL/SQL.
⇒Users specify what data is required without specifying how to retrieve it.
⇒Declarative DMLs are easier to learn and use than procedural DMLs.
⇒Example: SQL.
• SELECT: Used to retrieve data from a database.
• INSERT: Used to insert data into a table.
• UPDATE: Used to update existing data in a table.
• DELETE: Used to delete data from a table.
Data Control Language (DCL)Data Control Language (DCL) is used to control privileges in databases. DCL is used for granting and revoking user access to a database (authorization).
To perform any operation in the database, such as creating tables or views, privileges are required.
• GRANT: Used to give user access privileges to the database.
• REVOKE: Used to remove access privileges from the user.
The operations for which privileges may be granted or revoked apply to both DDL and DML.
Transaction Control Language (TCL) commands are used to manage transactions in the database. TCL is used to control the changes made by DML statements.
The changes in the database made by DML commands are either committed or rolled back using TCL.
• COMMIT: Saves the transaction in the database.
• ROLLBACK: Restores the database to its previous state since the last commit.
• SAVEPOINT: Temporarily saves a transaction so that it can be rolled back to that point if necessary.
