Understand given SQL (DDL) statements and be able to write simple SQL (DDL) statements using a sub-set of statements

Cambridge A‑Level Computer Science (9618) – Databases: Data Definition Language (DDL)

Learning Objectives

• Identify the purpose of DDL statements and distinguish them from DML statements.
• Interpret given DDL statements – understand what tables, columns, constraints and indexes are created, altered or removed.
• Write correct DDL statements using the defined sub‑set of commands:

  CREATE TABLE, ALTER TABLE, DROP TABLE,

  CREATE INDEX, DROP INDEX.

• Apply appropriate data types and constraints (primary key, foreign key, NOT NULL, UNIQUE, DEFAULT, CHECK).
• Explain how DDL statements affect the database schema and why they are executed only once (or rarely) in a project.

1. Relational Database Basics (Brief Recap)

• Relation (table): a set of rows (records) and columns (attributes).
• Primary key (PK): uniquely identifies each row.
• Foreign key (FK): links a column to the PK of another table, enforcing referential integrity.
• Candidate key: any column or set of columns that could serve as a PK.
• Unique constraint: guarantees all values in a column are different.
• Check constraint: restricts the range or pattern of values.

Why DDL Matters

DDL statements define the structure of the database – the tables, columns, data types and constraints. Once the schema is in place, DML statements (INSERT, UPDATE, DELETE, SELECT) manipulate the actual data.

2. DDL vs DML – Quick Comparison

3. Sub‑set of DDL Commands Covered

3.1 CREATE TABLE

Defines a new table, its columns, data types and any column‑level constraints.

CREATE TABLE table_name (
column1   datatype [constraint],
column2   datatype [constraint],
…
[table_constraint]
);

Common Data Types (Cambridge A‑Level)

Column‑level constraints (examples)

• PRIMARY KEY – unique, not null.
• NOT NULL – column must contain a value.
• UNIQUE – all values must be different.
• DEFAULT value – value used when none is supplied.
• CHECK (condition) – e.g., CHECK (Credits BETWEEN 1 AND 6).

Table‑level constraints (examples)

• PRIMARY KEY (col1, col2) – composite key.
• FOREIGN KEY (col) REFERENCES OtherTable(PK).

3.2 ALTER TABLE

Modifies an existing table – add, drop or change columns and constraints.

-- Add a new column
ALTER TABLE tablename ADD columnname datatype [constraint];
-- Drop an existing column
ALTER TABLE tablename DROP COLUMN columnname;
-- Add a table‑level constraint (e.g., foreign key)
ALTER TABLE tablename ADD CONSTRAINT fkname
FOREIGN KEY (col) REFERENCES OtherTable(PK);

3.3 DROP TABLE

Deletes a table and all its data permanently.

DROP TABLE table_name;

3.4 CREATE INDEX

Creates a secondary structure to speed up searches on one or more columns.

CREATE INDEX indexname ON tablename(column1 [, column2 …]);

3.5 DROP INDEX

Removes an index that is no longer needed.

DROP INDEX index_name;

4. Worked Example – Building a Simple University Database

Step 1 – Create the Student table

CREATE TABLE Student (
StudentID   INT PRIMARY KEY,
FirstName   VARCHAR(30) NOT NULL,
LastName    VARCHAR(30) NOT NULL,
DOB         DATE,
EnrolDate   DATE DEFAULT CURRENT_DATE,
Email       VARCHAR(50) UNIQUE
);

• StudentID is the primary key – automatically NOT NULL and unique.
• Email has a UNIQUE constraint to prevent duplicates.
• EnrolDate gets the current date if no value is supplied.

Step 2 – Create the Course table

CREATE TABLE Course (
CourseID   INT PRIMARY KEY,
CourseName VARCHAR(100) NOT NULL,
Credits    INT DEFAULT 3 CHECK (Credits BETWEEN 1 AND 6)
);

Step 3 – Create the junction table Enrollment (many‑to‑many)

CREATE TABLE Enrollment (
StudentID  INT,
CourseID   INT,
EnrolDate  DATE DEFAULT CURRENT_DATE,
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID)  REFERENCES Course(CourseID)
);

Step 4 – Add a new column to Course

ALTER TABLE Course ADD RoomNumber VARCHAR(10);

Step 5 – Create an index to speed up look‑ups by course name

CREATE INDEX idxcoursename ON Course(CourseName);

Step 6 – Remove the index (if the design changes)

DROP INDEX idxcoursename;

Step 7 – Delete a temporary table (example only)

DROP TABLE TempResults;

5. Common Pitfalls & How to Avoid Them

• Missing PRIMARY KEY: Every table must have at least one PK. Forgetting it leads to duplicate rows and makes foreign‑key relationships impossible.
• Incorrect data type for dates: Use DATE (or DATETIME if time is needed). Storing dates as VARCHAR prevents date arithmetic.
• Duplicate column names in CREATE TABLE: The parser will raise an error – double‑check spelling.
• Dropping a table that other tables reference: Most DBMS will refuse or cascade deletions. Use DROP TABLE … CASCADE only when you are sure you want to lose dependent data.
• Creating an index on a column that already has a PRIMARY KEY or UNIQUE constraint: Redundant – the DBMS already maintains an index for those constraints.

6. Practice Exercises

Exercise 1 – Create a table Supplier

Columns: SupplierID (INT, PK), Name (VARCHAR 50, NOT NULL), Phone (VARCHAR 15, may be NULL), Country (VARCHAR 30, default ‘UK’).

Exercise 2 – Extend Supplier with a Website column (VARCHAR 100) that must be unique.

Exercise 3 – Add an index idxsuppliercountry on the Country column.

Exercise 4 – A temporary table TmpStock is no longer needed – write the statement to remove it.

Answers (for teacher reference)

-- Exercise 1
CREATE TABLE Supplier (
SupplierID INT PRIMARY KEY,
Name       VARCHAR(50) NOT NULL,
Phone      VARCHAR(15),
Country    VARCHAR(30) DEFAULT 'UK'
);
-- Exercise 2
ALTER TABLE Supplier ADD Website VARCHAR(100) UNIQUE;
-- Exercise 3
CREATE INDEX idxsuppliercountry ON Supplier(Country);
-- Exercise 4
DROP TABLE TmpStock;

7. Quick Reference – DDL Cheat Sheet

8. Summary

• DDL statements shape the schema – they are written once (or when the design changes) and are not part of routine data processing.
• Understanding the syntax for CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX and DROP INDEX enables you to model real‑world entities accurately.
• Choosing appropriate data types and constraints ensures data integrity and simplifies later DML operations.
• Practice writing DDL statements from plain English specifications – this is a core skill assessed in the Cambridge A‑Level examinations.