Database languages and the people who use them

Builders, stockers, and shoppers

Think of a grocery store. Someone builds the shelves and decides the store layout (a builder). Someone stocks and rearranges items on those shelves (a stocker). A customer walks in and picks items off the shelf (a shopper). Each of these people speaks a different "language" of instructions, even though they're all working with the exact same shelves.

Database languages split along the exact same lines — some define structure, some manipulate data within that structure, some control who's allowed to do what.

DDL: Data Definition Language

DDL is used to define and modify the structure of the database — creating tables, specifying columns and their types, adding constraints, dropping tables. This is "building and reshaping the shelves."

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50)
);

ALTER TABLE students ADD COLUMN enrollment_year INT;

DROP TABLE old_records;

DDL statements change the schema (structure), not the data inside it. The DBMS stores the result of DDL statements in a special internal set of tables usually called the data dictionary or system catalog — the DBMS's own database about your database.

DML: Data Manipulation Language

DML is used to work with the actual data stored inside that structure — inserting, reading, updating, deleting rows. This is "stocking and picking items off the shelf."

INSERT INTO students (student_id, name, department) VALUES (1, 'Asha', 'CSE');

SELECT name, department FROM students WHERE department = 'CSE';

UPDATE students SET department = 'ECE' WHERE student_id = 1;

DELETE FROM students WHERE student_id = 1;

DML is split further into two styles worth knowing by name:

• Procedural DML — you specify what data you want and exactly how to get it, step by step (like relational algebra, covered in Module 3).
• Declarative (non-procedural) DML — you specify what you want, and let the DBMS work out how (like SQL and relational calculus). Almost every modern database is queried declaratively, because it lets the DBMS choose the fastest method internally.

DCL: Data Control Language

DCL controls permissions — who is allowed to do what.

GRANT SELECT ON students TO teaching_assistant;
REVOKE DELETE ON students FROM teaching_assistant;

TCL: Transaction Control Language

TCL manages transactions — the atomic, all-or-nothing groups of operations introduced earlier in this module.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

The people around a database

• Database Administrators (DBAs) define the conceptual and internal schemas, manage security, performance, and backups — the closest role to "owning" the database itself.
• Application programmers write the software that issues DML statements on behalf of end users, usually through an external schema tailored to their app.
• Sophisticated (power) end users write their own queries directly, without going through a pre-built application — a data analyst running ad-hoc SQL, for example.
• Naive end users interact with the database only through a polished application interface (a bank app, a food delivery app) and never see a query at all.

The math behind why declarative wins

If a database has $q$ distinct queries applications need over its lifetime, and the DBMS later gets a better internal search strategy (say, a new type of index from Module 6), a procedural approach requires every one of those $q$ queries to be manually rewritten to take advantage of it:

$\text{queries needing manual rewrite} = q$

A declarative approach requires zero rewrites, because the DBMS's query optimizer automatically picks the better strategy underneath the same declared query:

$\text{queries needing manual rewrite} = 0$

This is one of the biggest practical reasons SQL displaced older procedural query approaches historically.

Common mistakes

1. Mixing up DDL and DML. A quick test: does it change the shape of stored data (DDL) or the values inside that shape (DML)?
2. Forgetting TCL exists as its own category. COMMIT/ROLLBACK are often lumped in with DML, but they control transaction boundaries, not data itself.
3. Assuming all users write SQL. Most real-world database interaction happens through applications built for naive end users, who never see a query — the SQL is hidden behind buttons and forms.

Summary

DDL defines structure, DML manipulates data (procedurally or declaratively), DCL controls permissions, and TCL manages transaction boundaries. DBAs, application programmers, power users, and naive end users all interact with the same underlying database through different languages and interfaces suited to their role. With the foundations module complete, Module 2 begins the real design work: the Entity-Relationship model, the tool used to plan a database's structure before a single table is created.