Show understanding that the DBMS carries out all creation/modification of the database structure using its Data Definition Language (DDL)

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

Learning Objective

Show that the Database Management System (DBMS) carries out all creation and modification of the database structure using its Data Definition Language (DDL).

1. What are DDL and DML?

• DDL (Data Definition Language) – SQL statements that define, alter, or remove database objects such as tables, indexes, views, schemas, and security privileges.
• DML (Data Manipulation Language) – SQL statements that retrieve or modify the data stored in those objects.

2. Typical DDL Statements

Note: DDL statements are implicitly committed by the DBMS; in most systems they cannot be rolled back with ROLLBACK.

3. Typical DML Statements

4. How the DBMS Processes a DDL Command

1. Parsing – The SQL text is tokenised and checked for correct syntax.
2. Privilege check – The DBMS verifies that the user holds the required rights (e.g., CREATE TABLE or GRANT privilege).
3. Data‑dictionary update – Metadata describing the new or altered object is stored in the system catalog.
4. Storage allocation / de‑allocation – Physical pages or extents are reserved or released as required.
5. Auto‑commit – The change is committed automatically; most DBMSs do not allow a DDL statement to be undone with ROLLBACK.

5. DDL Impact on the Data Dictionary

The data dictionary (or system catalog) holds a description of every database object. When a DDL command runs:

• New entries are added (e.g., a row for each column of a newly created table).
• Existing entries are modified (e.g., adding a column updates the column‑list for that table).
• Deleted entries are removed (e.g., DROP TABLE erases the table’s metadata).

Subsequent DML statements are validated against this metadata – an INSERT will be rejected if it tries to store a value in a non‑existent column or violates a declared constraint.

6. Linking DDL to Integrity, Security & Transaction Control

• Integrity constraints (PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, NOT NULL) are defined in DDL. The DBMS enforces them automatically for all DML operations.
• Security – DDL commands such as GRANT and REVOKE are subject to privilege checks; only authorised users can create, alter, drop, or change access to objects.
• Transaction control for DML – Unlike DDL, DML statements are not auto‑committed unless the DBMS is in autocommit mode. They can be wrapped in an explicit transaction:

  BEGIN TRANSACTION;
  INSERT INTO …
  UPDATE …
  COMMIT;          -- or ROLLBACK;
  

7. Common Data Types & Constraints (SQL‑92 subset used in the syllabus)

• Character types: CHAR(n) (fixed length), VARCHAR(n) (variable length).
• Numeric types: INTEGER, DECIMAL(p,s), NUMERIC(p,s).
• Date/Time types: DATE, TIME, TIMESTAMP.
• Constraints:

  • PRIMARY KEY (col1, …)
  • UNIQUE (col)
  • FOREIGN KEY (col) REFERENCES other_table(col)
  • CHECK (condition)
  • NOT NULL

8. BNF Fragment for a CREATE TABLE Statement

<create‑table‑stmt> ::= CREATE TABLE <table‑name> '(' <column‑def> { ',' <column‑def> } [ ',' <table‑constraint> ] ')' ';'
<column‑def>       ::= <column‑name> <data‑type> [ <column‑constraint> ]
<data‑type>        ::= CHAR '(' <int> ')' |
VARCHAR '(' <int> ')' |
INTEGER |
DECIMAL '(' <int> ',' <int> ')' |
DATE
<column‑constraint> ::= NOT NULL |
UNIQUE |
PRIMARY KEY |
CHECK '(' <condition> ')'
<table‑constraint> ::= PRIMARY KEY '(' <column‑list> ')' |
FOREIGN KEY '(' <column‑list> ')' REFERENCES <table‑name> '(' <column‑list> ')' |
CHECK '(' <condition> ')'

9. Illustrative Example (DDL + DML)

-- 1️⃣ DDL: create a schema (optional)
CREATE SCHEMA University AUTHORIZATION dbo;
-- 2️⃣ DDL: create a table for student records
CREATE TABLE University.Student (
StudentID   INTEGER PRIMARY KEY,
Name        VARCHAR(50) NOT NULL,
DOB         DATE,
GPA         DECIMAL(3,2),
Email       VARCHAR(100) UNIQUE,
CONSTRAINT chk_GPA CHECK (GPA BETWEEN 0.00 AND 4.00)
);
-- 3️⃣ DML: insert a new student
INSERT INTO University.Student (StudentID, Name, DOB, GPA, Email)
VALUES (101, 'Alice Smith', '2002-04-15', 3.85, 'alice.smith@example.com');
-- 4️⃣ DDL: add a column for the date of enrolment
ALTER TABLE University.Student
ADD EnrolDate DATE NOT NULL DEFAULT CURRENT_DATE;
-- 5️⃣ DML: update the newly added column for the existing row
UPDATE University.Student
SET EnrolDate = '2020-09-01'
WHERE StudentID = 101;
-- 6️⃣ DML: retrieve the student record
SELECT * FROM University.Student WHERE StudentID = 101;

10. Suggested Diagram (conceptual)

11. Key Take‑aways

• DDL is the only mechanism for creating, altering, or removing the logical and physical structure of a database.
• When a DDL command is executed, the DBMS updates its data dictionary, allocates or frees storage, and commits the change automatically; it cannot be rolled back.
• Constraints and security privileges are defined in DDL; they are enforced automatically for all subsequent DML operations.
• DML works *within* the structures defined by DDL and can be controlled with explicit COMMIT / ROLLBACK statements.
• Understanding the distinction and interaction between DDL and DML is essential for designing, implementing, and maintaining robust database systems in line with the Cambridge AS & A‑Level Computer Science syllabus.