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 objectives (Cambridge AO1‑AO3)

• AO1 – Knowledge: Identify the purpose of DDL and DML and list the core SQL statements.
• AO2 – Application: Analyse a simple database requirement, design an appropriate schema (using DDL) and write DML statements to create, query, update and delete data.
• AO3 – Evaluation: Critically evaluate how the DBMS processes DML, the role of transactions, and the impact of constraints on data integrity and security.

1. Data Definition Language (DDL)

DDL statements define, modify or remove database objects – the structures that hold data. They affect the metadata stored in the system catalogue and are usually auto‑committed (most DBMS cannot roll back DDL).

1.1 Core DDL statements and their purpose

1.2 Essential DDL elements for data integrity

• PRIMARY KEY – uniquely identifies each row; enforces entity integrity.
• FOREIGN KEY – maintains referential integrity between related tables.
• UNIQUE – prevents duplicate values in a column.
• CHECK – restricts values to a defined range or pattern (e.g., CHECK (Age >= 0)).
• NOT NULL – forces a column to contain a value, supporting data completeness.

1.3 Example DDL statements (including constraints)

-- 1. Create a database (optional in many DBMS)
CREATE DATABASE UniversityDB;

-- 2. Create a table with integrity constraints
CREATE TABLE Student (
    StudentID   INT PRIMARY KEY,
    Name        VARCHAR(100) NOT NULL,
    DOB         DATE,
    Programme   VARCHAR(50) NOT NULL,
    Email       VARCHAR(120) UNIQUE,
    CONSTRAINT chk_DOB CHECK (DOB <= CURRENT_DATE)
);

-- 3. Add a new column (ALTER)
ALTER TABLE Student
ADD Phone VARCHAR(15);

-- 4. Rename the table (RENAME)
RENAME TABLE Student TO Learner;

-- 5. Remove a column (ALTER)
ALTER TABLE Learner
DROP COLUMN Phone;

-- 6. Delete the table (DROP)
DROP TABLE Learner;

-- 7. Remove all rows but keep the definition (TRUNCATE)
TRUNCATE TABLE Student;

2. Data Manipulation Language (DML)

DML statements operate on the actual data stored in the structures created by DDL. Every DML command is executed inside a transaction, allowing the DBMS to guarantee the ACID properties (Atomicity, Consistency, Isolation, Durability).

2.1 Core DML statements

• SELECT – retrieves data (queries).
• INSERT – adds new rows.
• UPDATE – modifies existing rows.
• DELETE – removes rows.
• MERGE – “up‑sert”: inserts rows that do not exist and updates those that do.

2.2 Transaction control (AO3 – evaluation of reliability)

Explicit transaction boundaries give the programmer control over when changes become permanent.

BEGIN TRANSACTION;

INSERT INTO Student (StudentID, Name, DOB, Programme, Email)
VALUES (101, 'Alice Brown', '2002-04-15', 'Computer Science', 'alice@example.com');

UPDATE Student
SET Programme = 'Software Engineering'
WHERE StudentID = 101;

-- If any statement fails, undo everything
IF @@ERROR <> 0
    ROLLBACK TRANSACTION;
ELSE
    COMMIT TRANSACTION;

Note:

• Most DBMS treat DDL as an implicit commit; therefore DDL cannot be rolled back in the same way as DML.
• Locking, logging and recovery mechanisms are invoked only for DML statements.

2.3 Example DML statements (including MERGE)

-- INSERT a new student
INSERT INTO Student (StudentID, Name, DOB, Programme, Email)
VALUES (102, 'Bob Carter', '2001-09-30', 'Mathematics', 'bob@example.com');

-- SELECT students enrolled in Computer Science
SELECT Name, Programme
FROM Student
WHERE Programme = 'Computer Science';

-- UPDATE a student's programme
UPDATE Student
SET Programme = 'Data Science'
WHERE StudentID = 102;

-- DELETE a student record
DELETE FROM Student
WHERE StudentID = 102;

-- MERGE (up‑sert) – useful for synchronising with external data sources
MERGE INTO Student AS tgt
USING (SELECT 103 AS StudentID, 'Clara Lee' AS Name,
              '2000-12-05' AS DOB, 'Physics' AS Programme,
              'clara@example.com' AS Email) AS src
ON tgt.StudentID = src.StudentID
WHEN MATCHED THEN
    UPDATE SET Programme = src.Programme, Email = src.Email
WHEN NOT MATCHED THEN
    INSERT (StudentID, Name, DOB, Programme, Email)
    VALUES (src.StudentID, src.Name, src.DOB, src.Programme, src.Email);

2.4 Relational‑algebra view of DML (AO2 – application)

Every DML command has a direct counterpart in relational algebra, which helps students understand the logical operations behind SQL.

• SELECT → σ (selection) and π (projection).
  Example: SELECT Name, Programme FROM Student WHERE Programme='Computer Science' ↔ π_{Name,Programme}(σ_{Programme='Computer Science'}(Student))
• INSERT → ρ (extend) followed by ∪ (union) with the existing relation.
• UPDATE → δ (rename) + σ + ∪ to replace affected tuples.
• DELETE → σ (selection) with the negated predicate, i.e., a set difference.
• MERGE → combination of join, σ, and ∪ to achieve “up‑sert”.

3. How the DBMS Executes a DML Statement (Processing Pipeline)

1. Parsing – Syntax check and semantic validation (e.g., column existence, data‑type compatibility).
2. Query optimisation – The optimiser builds alternative execution plans and selects the cheapest (considering indexes, join order, statistics).
3. Execution – The execution engine reads/writes data pages, acquiring the appropriate locks (shared for reads, exclusive for writes).
4. Transaction management – Changes are recorded in the transaction log; COMMIT makes them durable, ROLLBACK restores the previous state.
5. Integrity enforcement – All defined constraints (PK, FK, UNIQUE, CHECK, NOT NULL) are verified before the transaction is committed.

Diagram suggestion

4. DDL vs DML – Quick comparison

5. Practical activities (linking to Cambridge Paper 4)

1. Design task (AO2): Given a brief scenario (e.g., a university enrolment system), produce an ER diagram, then translate it into SQL DDL statements with appropriate constraints.
2. Implementation task (AO3): Write a short Java/Python program that connects to the database, performs a series of DML operations inside a transaction, and demonstrates error handling with ROLLBACK.
3. Evaluation task (AO3): Compare the behaviour of TRUNCATE vs. DELETE in terms of speed, logging and recoverability. Discuss which is more appropriate for a production environment.

6. Summary

• DDL defines the schema; DML manipulates the data stored in that schema.
• The DBMS parses, optimises and executes **all DML statements**, applying locks, logging, and integrity checks to guarantee ACID properties.
• Transaction control (BEGIN, COMMIT, ROLLBACK) ensures reliable data handling and supports the evaluation of reliability and security (AO3).
• Understanding the separation of DDL/DML, the role of constraints, and the internal processing pipeline equips you to design robust databases and to write efficient, safe SQL – essential skills for the Cambridge IGCSE/A‑Level Computer Science examinations.