Session 31 - SQL DDL Commands

TL;DR

Set up a local MySQL environment and use phpMyAdmin to manage databases, tables, and rows through a relational hierarchy (server → database → table → rows/columns).

Briefing Cornell Notes

Briefing

SQL DDL commands take center stage, with a practical walkthrough of how to set up a local MySQL environment (via phpMyAdmin) and then build, modify, and remove database objects—databases, tables, and columns—using Structured Query Language. The core message is that DDL is the foundation for shaping the database schema before any data manipulation happens, and that getting schema design right matters because it directly affects data integrity, reliability, and future maintainability.

After a quick setup guide, the session demonstrates the relational model: a database server hosts multiple databases; each database contains tables; and each table stores rows and columns. With phpMyAdmin running at a local URL, the instructor shows how databases like “CampusX” can be created and deleted, and how tables appear inside a selected database. From there, the class introduces SQL as a programming-style language for managing relational data—retrieving, inserting, updating, and deleting—while emphasizing that the current focus is DDL (Data Definition Language).

DDL is broken into four major operations: CREATE (create databases/objects like tables and columns), ALTER (modify existing objects), DROP (delete objects), and—by contrast with later sections—DML (Data Manipulation Language) is reserved for CRUD-style actions like INSERT, SELECT, UPDATE, and DELETE. The roadmap is explicit: DDL first, then DML in subsequent classes, followed by Data Control Language (GRANT/REVOKE) and Transaction Control Language (COMMIT/ROLLBACK and related ideas).

The session then pivots from syntax to correctness, introducing data integrity as the practical goal behind constraints. Integrity is framed as accuracy and consistency: values should be correct for their type (e.g., text in string fields), and stable over time (e.g., a name shouldn’t unexpectedly change). To enforce integrity, the class highlights three mechanisms—constraints, transactions, and normalization—then dives into constraints in MySQL terms.

Seven constraint types are taught: NOT NULL (no missing values), UNIQUE (no duplicates), PRIMARY KEY (unique row identifier; can be composite), AUTO_INCREMENT (automatic numeric IDs), CHECK (value ranges/conditions), DEFAULT (fallback values when input is omitted), and FOREIGN KEY (relationships between tables). The walkthrough includes examples: creating a USERS table with NOT NULL and UNIQUE behavior, demonstrating how constraints trigger warnings/errors when NULLs or duplicates are attempted, and showing how composite primary keys can be defined.

Foreign keys become the session’s most applied concept. Two tables—CUSTOMER and ORDER—are linked so that ORDER rows reference CUSTOMER via a customer ID. This prevents “orphan” orders when a customer is deleted, and it enables navigation from an order to its customer. The class also explains referential actions on delete/update: RESTRICT (default—block changes), CASCADE (propagate deletes/updates), and SET NULL (replace the foreign key with NULL or a default-like outcome). Finally, ALTER TABLE is used to add columns, delete columns, and modify constraints, with an important operational note: some constraint changes require dropping and recreating rather than direct modification.

By the end, the session leaves learners with a clear schema-first workflow: set up the database environment, use DDL to define structure, enforce integrity with constraints (especially foreign keys), and use ALTER TABLE to evolve the schema safely—before moving on to DML, normalization, and more advanced database topics later in the course.

Cornell Notes

The session focuses on SQL DDL (Data Definition Language) in MySQL, showing how to create and manage the database schema using CREATE, ALTER, and DROP. It starts with a local setup using phpMyAdmin and explains the relational model: database → tables → rows/columns. The class then connects schema design to data integrity by teaching constraints—NOT NULL, UNIQUE, PRIMARY KEY, AUTO_INCREMENT, CHECK, DEFAULT, and FOREIGN KEY. Foreign keys are demonstrated through CUSTOMER and ORDER tables, including referential actions like RESTRICT (default), CASCADE, and SET NULL to prevent inconsistent “orphan” data. This matters because correct schema and constraints reduce errors and make future updates safer.

Why does the session treat DDL as the starting point before inserting or querying data?

DDL defines the structure that later DML relies on. CREATE sets up databases/tables/columns, ALTER evolves that structure, and DROP removes objects. Without a schema (columns, types, keys, constraints), INSERT/UPDATE/DELETE can’t be made reliably because the database wouldn’t know what fields exist or what rules (like NOT NULL or UNIQUE) must be enforced.

How do constraints protect data integrity in practice?

Data integrity is framed as accuracy and consistency. Constraints enforce rules at the database level so invalid or inconsistent data can’t be stored. Examples from the session include NOT NULL preventing missing values, UNIQUE blocking duplicate entries (commonly used for emails), CHECK enforcing ranges (like age between 6 and 25), and DEFAULT filling in values when an insert omits a field.

What’s the difference between UNIQUE and PRIMARY KEY, and how does composite PRIMARY KEY work?

UNIQUE ensures a column (or a set of columns) doesn’t repeat across rows. PRIMARY KEY uniquely identifies each row and is typically used for row identity. The session also shows composite PRIMARY KEY—when two columns together (e.g., USER_ID and NAME) form the unique identifier—useful when a single column alone isn’t sufficient.

What does AUTO_INCREMENT solve, and when is it typically used?

AUTO_INCREMENT removes the need to manually generate sequential numeric IDs. As new rows are inserted, the database automatically assigns the next value (e.g., USER_ID or ORDER_ID). The session notes it’s commonly applied to ID columns and avoids extra work to find the last used value before inserting the next record.

How do FOREIGN KEY relationships prevent inconsistent data between tables?

A FOREIGN KEY ties a child table to a parent table. In the CUSTOMER/ORDER example, ORDER.customer_id must reference a valid CUSTOMER.customer_id. This prevents deleting a customer while orders still reference them (under RESTRICT), or it can propagate changes depending on the referential action (CASCADE or SET NULL). The result is fewer “orphan” records and more reliable joins.

What are the referential actions RESTRICT, CASCADE, and SET NULL, and how do they change delete/update behavior?

RESTRICT (default) blocks deletion/update of a parent row if child rows depend on it. CASCADE propagates the change—deleting a customer also deletes their orders, and updating a customer_id updates the referenced foreign keys. SET NULL replaces the foreign key in the child row with NULL (or a null-like outcome) when the parent row is removed/changed, keeping the child row but breaking the reference safely.

Review Questions

What are the four major DDL operations mentioned, and what kinds of database changes does each enable?
Give an example of how NOT NULL and UNIQUE would behave differently during an INSERT.
In the CUSTOMER/ORDER relationship, what problem does a FOREIGN KEY solve, and how does RESTRICT differ from CASCADE?

Key Points

1
Set up a local MySQL environment and use phpMyAdmin to manage databases, tables, and rows through a relational hierarchy (server → database → table → rows/columns).
2
Use DDL to define schema first: CREATE for new objects, ALTER for structural changes, and DROP for removing objects.
3
Enforce data integrity with constraints: NOT NULL, UNIQUE, PRIMARY KEY, AUTO_INCREMENT, CHECK, DEFAULT, and FOREIGN KEY.
4
Model relationships with FOREIGN KEY so child rows (e.g., ORDER) can only reference valid parent rows (e.g., CUSTOMER), preventing orphan data.
5
Choose referential actions deliberately: RESTRICT blocks dependent changes, CASCADE propagates deletes/updates, and SET NULL removes the reference safely.
6
When evolving schemas with ALTER TABLE, remember that some constraint modifications may require dropping and recreating rather than direct edits.
7
Treat schema design as the foundation for later DML work (INSERT/SELECT/UPDATE/DELETE), because constraints affect what data operations are allowed.

Highlights

DDL is presented as the schema-first workflow: CREATE, ALTER, and DROP shape databases and tables before any CRUD operations.

Constraints turn integrity into enforceable rules—NOT NULL blocks missing values, UNIQUE prevents duplicates, and CHECK restricts valid ranges.

Foreign keys link tables (CUSTOMER ↔ ORDER) and keep data consistent, with RESTRICT/CASCADE/SET NULL controlling what happens on delete or update.

ALTER TABLE can add or remove columns and adjust constraints, but some changes may require drop-and-recreate to avoid errors.

Topics

SQL DDL
MySQL phpMyAdmin Setup
Data Integrity
Constraints
Foreign Keys
ALTER TABLE

Mentioned

SQL
DDL
DML
DBMS
CRUD
TC L
TCL

Session 31 - SQL DDL Commands | DSMP 2023