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

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

Key Concepts

• DDL (Data Definition Language) – statements that define, alter, or remove database objects such as tables, indexes, views, and schemas.
• DML (Data Manipulation Language) – statements that retrieve or modify the data stored within those objects.
• The DBMS interprets DDL statements, updates the data dictionary (metadata), and enforces integrity constraints.

Typical DDL Statements

1. CREATE – creates a new database object (e.g., CREATE TABLE).
2. ALTER – modifies the definition of an existing object (e.g., adding a column).
3. DROP – removes an object permanently.
4. TRUNCATE – deletes all rows from a table while preserving its structure.

Typical DML Statements

1. SELECT – retrieves data from one or more tables.
2. INSERT – adds new rows to a table.
3. UPDATE – modifies existing rows.
4. DELETE – removes rows from a table.

How the DBMS Handles DDL

When a DDL command is issued, the DBMS performs the following steps:

• Parses the statement and checks syntax.
• Validates that the user has the required privileges.
• Updates the data dictionary (metadata) to reflect the new structure.
• Executes any associated storage allocation or deallocation.
• Commits the change automatically (DDL is typically auto‑committed).

Comparison of DDL and DML

Illustrative Example

Consider a simple student database. The following sequence shows how DDL and DML interact.

-- DDL: Create a table for student records
CREATE TABLE Student (
StudentID   INT PRIMARY KEY,
Name \cdot ARCHAR(50) NOT NULL,
DOB         DATE,
GPA         DECIMAL(3,2)
);
-- DML: Insert a new student record
INSERT INTO Student (StudentID, Name, DOB, GPA)
VALUES (101, 'Alice Smith', '2002-04-15', 3.85);
-- DDL: Add a new column for email address
ALTER TABLE Student
ADD Email \cdot ARCHAR(100);
-- DML: Update the newly added column
UPDATE Student
SET Email = 'alice.smith@example.com'
WHERE StudentID = 101;

Why DDL Is Central to DBMS Functionality

The DBMS must maintain a consistent and accurate description of the database structure. This description, stored in the data dictionary, enables the DBMS to:

• Validate DML statements against the current schema.
• Allocate storage space efficiently.
• Enforce integrity constraints (e.g., primary keys, foreign keys).
• Generate execution plans for queries.

Suggested Diagram

Key Take‑aways

• DDL statements are the only way to create or modify the physical and logical structure of a database.
• The DBMS processes DDL by updating its internal metadata, which in turn governs how DML operates.
• Understanding the distinction between DDL and DML is essential for designing, implementing, and maintaining robust database systems.