8.3 Data Definition Language (DDL) and Data Manipulation Language (DML)

Introduction

A Database Management System (DBMS) is a software system designed to create, manage, and provide controlled access to a collection of data. A core function of a DBMS is to allow users to interact with the data stored within it. This interaction is achieved through the use of specific languages: Data Definition Language (DDL) and Data Manipulation Language (DML). This section will explore these languages and demonstrate how the DBMS utilizes DML to perform data queries and maintenance.

Data Definition Language (DDL)

DDL is used to define the structure of the database. It deals with the creation, modification, and deletion of database objects such as tables, indexes, and views.

• CREATE: Used to create new database objects (e.g., a table). Example: `CREATE TABLE Students (StudentID INT, Name VARCHAR(255), Major VARCHAR(255));`
• ALTER: Used to modify the structure of existing database objects (e.g., adding a column to a table). Example: `ALTER TABLE Students ADD COLUMN Email VARCHAR(255);`
• DROP: Used to delete database objects. Example: `DROP TABLE Students;`
• TRUNCATE: Used to remove all rows from a table, but the table structure remains. Example: `TRUNCATE TABLE Students;`

Data Manipulation Language (DML)

DML is used to manipulate the data stored within the database. It allows users to insert, update, delete, and retrieve data.

• SELECT: Used to retrieve data from the database. Example: `SELECT * FROM Students WHERE Major = 'Computer Science';`
• INSERT: Used to add new data into a table. Example: `INSERT INTO Students (StudentID, Name, Major) VALUES (1, 'Alice Smith', 'Computer Science');`
• UPDATE: Used to modify existing data in a table. Example: `UPDATE Students SET Major = 'Software Engineering' WHERE StudentID = 1;`
• DELETE: Used to remove data from a table. Example: `DELETE FROM Students WHERE StudentID = 1;`

The DBMS and DML Operations

The DBMS is responsible for executing all queries and maintenance operations on the data. This includes both DDL and DML commands. When a user submits a query (e.g., a `SELECT` statement), the DBMS parses the query, optimizes it, and then executes it against the database. The DBMS then returns the results to the user.

Consider the following example:

1. A user submits the DML query: `SELECT Name, Major FROM Students WHERE Major = 'Computer Science';`
2. The DBMS receives the query.
3. The DBMS parses the query to understand its meaning.
4. The DBMS optimizes the query to find the most efficient way to retrieve the data. This might involve using indexes.
5. The DBMS accesses the database and retrieves the relevant data from the `Students` table.
6. The DBMS formats the retrieved data and returns it to the user.

Relationship between DDL and DML

DDL and DML are complementary languages. DDL defines the structure, while DML manipulates the data within that structure. The DBMS uses DDL to create and maintain the database schema, and then uses DML to perform operations on the data stored within the schema.

Language	Purpose	Examples
DDL	Defines the database schema (structure).	CREATE TABLE, ALTER TABLE, DROP TABLE
DML	Manipulates the data within the database.	SELECT, INSERT, UPDATE, DELETE

The DBMS ensures data integrity and consistency by enforcing constraints defined through DDL and by properly executing DML operations.