Database Languages: DDL & DML Explained | GATE & Competitive Exams
Database Languages: An Essential Guide
Introduction
A Database Management System (DBMS) provides tools to define, manipulate, and manage data efficiently. These tools are broadly categorized into two database languages:
- Data-Definition Language (DDL): Used to define the structure and schema of the database.
- Data-Manipulation Language (DML): Used to interact with and manipulate the data stored in the database.
1. Data-Manipulation Language (DML)
A Data-Manipulation Language (DML) allows users to interact with the database to retrieve, insert, delete, or update data. DML is crucial for working with the data stored in a DBMS.
Types of DML:
- Procedural DML: Requires users to specify what data is needed and how to retrieve it.
- Declarative DML: Requires users to specify only what data is needed, leaving the database system to determine how to retrieve it. (e.g., SQL)
To retrieve all students in the “Computer Science” department:
SELECT * FROM Students WHERE Department = 'Computer Science';
Key Operations in DML:
- Retrieval: Querying data from the database.
- Insertion: Adding new data to the database.
- Deletion: Removing data from the database.
- Modification: Updating existing data in the database.
2. Data-Definition Language (DDL)
A Data-Definition Language (DDL) defines the structure and organization of the database. It specifies schemas, constraints, and access permissions.
Key Functions of DDL:
- Defining Schemas: Create tables, define data types, and specify relationships.
- Specifying Constraints: Ensure data integrity through rules like domain constraints and referential integrity.
- Authorization: Control user access to specific data and operations.
To create a Students table:
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Department VARCHAR(30),
Age INT CHECK (Age > 18)
);
Types of Constraints in DDL:
- Domain Constraints: Ensure values belong to a defined domain (e.g., integer, date).
- Referential Integrity: Ensure relationships between tables are valid (e.g., foreign key constraints).
- Assertions: Enforce complex rules like “Every department must offer at least five courses.”
- Authorization: Specify user access rights like read, insert, update, or delete.
3. Role of the Data Dictionary
The data dictionary is a special table within the database that stores metadata, or “data about data.” It is used to maintain information about the database schema, constraints, and user permissions.
- Table names and column details
- Primary and foreign key definitions
- User access permissions
Conclusion
Understanding database languages, including DDL and DML, is crucial for managing and interacting with data in a DBMS. While DDL defines the structure of the database, DML allows users to perform operations on the data. These concepts are vital for students preparing for competitive exams like GATE, UGC NET, and ISRO, and for professionals working in database management and development.
