Show understanding that the DBMS carries out all queries and maintenance of data using its DML

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

Learning Objective

Understand that the Database Management System (DBMS) executes all queries and maintains data exclusively through its Data Manipulation Language (DML). Recognise the complementary role of Data Definition Language (DDL) in defining the structure on which DML operates.

1. What is DDL?

DDL consists of SQL statements that define, modify, and remove database objects such as tables, indexes, views and schemas. These statements are interpreted by the DBMS to change the metadata (the data dictionary).

• CREATE – defines a new object.
• ALTER – changes the definition of an existing object.
• DROP – removes an object permanently.
• TRUNCATE – quickly removes all rows from a table while preserving its definition.
• RENAME – changes the name of an object.

2. What is DML?

DML comprises the statements that actually manipulate the data stored in the structures created by DDL. The DBMS processes these statements, applying transaction control, integrity checking and concurrency management.

• SELECT – retrieves data (queries).
• INSERT – adds new rows.
• UPDATE – modifies existing rows.
• DELETE – removes rows.
• MERGE – combines INSERT and UPDATE operations.

3. DDL vs DML – A Comparison

4. How the DBMS Executes DML

The DBMS follows a well‑defined pipeline for every DML statement:

1. Parsing – syntax and semantic checks.
2. Optimization – the query optimizer selects the most efficient execution plan.
3. Execution – the execution engine reads/writes data pages, applying locks as needed.
4. Transaction Management – changes are recorded in the transaction log; COMMIT makes them permanent, ROLLBACK undoes them.
5. Integrity Enforcement – constraints (PRIMARY KEY, FOREIGN KEY, CHECK) are verified.

5. Example DDL and DML Statements

Below is a simple scenario for a university database.

CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name \cdot ARCHAR(100) NOT NULL,
DOB DATE,
Programme \cdot ARCHAR(50)
);
INSERT INTO Student (StudentID, Name, DOB, Programme)
VALUES (101, 'Alice Brown', '2002-04-15', 'Computer Science');
SELECT Name, Programme
FROM Student
WHERE Programme = 'Computer Science';
UPDATE Student
SET Programme = 'Software Engineering'
WHERE StudentID = 101;
DELETE FROM Student
WHERE StudentID = 101;

6. Transaction Control – The Bridge Between DML and Data Integrity

All DML operations are part of a transaction. The following SQL demonstrates explicit transaction control:

BEGIN TRANSACTION;
INSERT INTO Student (StudentID, Name, DOB, Programme)
VALUES (102, 'Bob Carter', '2001-09-30', 'Mathematics');
UPDATE Student
SET Programme = 'Data Science'
WHERE StudentID = 102;
-- If any statement fails, undo all changes
IF @@ERROR <> 0
ROLLBACK TRANSACTION;
ELSE
COMMIT TRANSACTION;

7. Relational Algebra Perspective

From a theoretical point of view, DML corresponds to relational algebra operations. For example, the SELECT query above can be expressed as:

\$\sigma_{\text{Programme} = \text{'Computer Science'}}(\text{Student})\$

where \$\sigma\$ denotes the selection operator.

8. Summary

• DDL defines the schema; DML works with the data.
• The DBMS is responsible for parsing, optimizing, and executing every DML statement.
• Transaction control ensures that DML operations are atomic, consistent, isolated, and durable (ACID).
• Understanding the separation of concerns helps in designing robust databases and writing efficient SQL.