Dbms Concepts - Learning Module

Loading content...

0/241

Database Languages

The Languages of Data Interaction

Every interaction with a database requires communication—you must tell the DBMS what you want to do and how to do it. But not all database operations are alike. Creating a table is fundamentally different from querying data, which differs again from granting access permissions or managing transactions.

To handle this diversity, database systems use specialized Database Languages, each designed for a specific category of operation. While most developers think of SQL as a single language, it's actually a collection of sub-languages, each with distinct purposes and syntax.

In this page, we'll examine the four major categories of database languages and understand when and how each is used.

What You Will Learn

By the end of this page, you'll understand the four categories of database languages (DDL, DML, DCL, TCL), know when to use each, and be able to write and interpret statements in each category. You'll also see how these languages work together to enable complete database management.

Overview of Database Languages

SQL (Structured Query Language) is not a single, monolithic language. It's composed of several sub-languages, each addressing a different aspect of database management. Understanding these categories clarifies what each type of statement does and why it exists.

The Four Categories of Database Languages
Category	Full Name	Purpose	Key Commands
DDL	Data Definition Language	Define and modify database structure	CREATE, ALTER, DROP, TRUNCATE
DML	Data Manipulation Language	Query and modify data	SELECT, INSERT, UPDATE, DELETE
DCL	Data Control Language	Manage access permissions	GRANT, REVOKE
TCL	Transaction Control Language	Manage transactions	COMMIT, ROLLBACK, SAVEPOINT

Converting Mermaid diagram...

Why Separate Languages?

The separation of database languages into categories reflects fundamental differences in:

What's affected — DDL modifies schema (structure); DML modifies data (content); DCL modifies permissions; TCL manages operation scope.
Who typically uses them — DDL is often used by DBAs and during deployments. DML is used by applications and ad-hoc queries. DCL is used by security administrators. TCL is embedded in application logic.
Transaction behavior — DDL often auto-commits in many DBMS. DML can be grouped into transactions. TCL controls transaction boundaries.
Reversibility — DML changes can be rolled back (within a transaction). DDL changes are often not rollable (implementation-dependent).

Beyond the Four Categories

Some classifications include additional categories like DQL (Data Query Language, for SELECT specifically) or SDL (Storage Definition Language, for physical storage). The four-category model (DDL, DML, DCL, TCL) is the most common and covers the essential distinctions.

Data Definition Language (DDL)

Data Definition Language (DDL) is used to define and modify the structure of database objects—tables, indexes, views, schemas, and constraints. DDL statements describe the shape of the database, not its contents.

Key Characteristic: DDL operates on metadata (the schema), not on data. When you execute a DDL statement, you're changing how the database is organized, not the stored values.

CREATE Statement

Creates new database objects. The most fundamental DDL operation—bringing new structures into existence.

CREATE Examples
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
-- CREATE DATABASE: Create a new database
CREATE DATABASE company_db
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8';
 
-- CREATE SCHEMA: Logical namespace within database
CREATE SCHEMA hr
    AUTHORIZATION hr_admin;
 
-- CREATE TABLE: The fundamental data structure
CREATE TABLE hr.employees (
    employee_id     SERIAL PRIMARY KEY,
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    email           VARCHAR(100) NOT NULL UNIQUE,
    hire_date       DATE NOT NULL DEFAULT CURRENT_DATE,
    salary          DECIMAL(10,2) CHECK (salary > 0),
    department_id   INTEGER,
    manager_id      INTEGER,
    
    -- Table-level constraints
    CONSTRAINT fk_department 
        FOREIGN KEY (department_id) 
        REFERENCES hr.departments(department_id)
        ON DELETE SET NULL,
    CONSTRAINT fk_manager 
        FOREIGN KEY (manager_id) 
        REFERENCES hr.employees(employee_id),
    CONSTRAINT no_self_management 
        CHECK (manager_id <> employee_id)
);
 
-- CREATE INDEX: Improve query performance
CREATE INDEX idx_emp_dept 
    ON hr.employees(department_id);
 
CREATE UNIQUE INDEX idx_emp_email 
    ON hr.employees(LOWER(email));  -- Functional index
 
-- CREATE VIEW: Virtual table
CREATE VIEW hr.employee_directory AS
SELECT 
    employee_id,
    first_name || ' ' || last_name AS full_name,
    email,
    d.name AS department
FROM hr.employees e
LEFT JOIN hr.departments d 
    ON e.department_id = d.department_id;
 
-- CREATE SEQUENCE: Number generator
CREATE SEQUENCE hr.invoice_number_seq
    START WITH 1000
    INCREMENT BY 1
    NO CYCLE;
 
-- CREATE TYPE: Custom data types
CREATE TYPE hr.employment_status AS ENUM (
    'active', 
    'on_leave', 
    'terminated'
);

DDL Key Concepts

•Schema Definitions — DDL creates the blueprint that data must conform to
•Constraint Enforcement — DDL establishes rules the DBMS enforces automatically
•Index Creation — DDL enables performance optimization structures
•View Definitions — DDL creates virtual tables for abstraction and security

Data Manipulation Language (DML)

Data Manipulation Language (DML) is used to query, insert, update, and delete data within database tables. While DDL defines structure, DML operates on the actual data values.

Key Characteristic: DML is the language of everyday database operations. Applications spend the vast majority of their database interactions executing DML statements.

SELECT Statement

The most frequently used SQL statement. Retrieves data from one or more tables according to specified criteria. SELECT is the foundation of database querying—arguably the most important SQL skill.

SELECT Examples
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
-- Basic SELECT: All columns
SELECT * FROM employees;
 
-- Projection: Specific columns
SELECT first_name, last_name, salary 
FROM employees;
 
-- Selection: Filtering with WHERE
SELECT * FROM employees
WHERE department_id = 5
  AND salary > 50000;
 
-- Aliases: Readable output
SELECT 
    first_name || ' ' || last_name AS full_name,
    salary * 12 AS annual_salary
FROM employees;
 
-- DISTINCT: Remove duplicates
SELECT DISTINCT department_id 
FROM employees;
 
-- Ordering results
SELECT * FROM employees
ORDER BY salary DESC, last_name ASC;
 
-- Limiting results
SELECT * FROM employees
ORDER BY hire_date DESC
LIMIT 10;  -- First 10 rows
 
-- Aggregation functions
SELECT 
    department_id,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(hire_date) AS first_hire
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5     -- Filter after grouping
ORDER BY avg_salary DESC;
 
-- JOIN: Combining tables
SELECT 
    e.first_name,
    e.last_name,
    d.name AS department,
    m.first_name AS manager_first_name
FROM employees e
INNER JOIN departments d 
    ON e.department_id = d.department_id
LEFT JOIN employees m 
    ON e.manager_id = m.employee_id
WHERE d.location = 'New York';
 
-- Subquery: Query within a query
SELECT * FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
);
 
-- Common Table Expression (CTE)
WITH ranked_employees AS (
    SELECT 
        *,
        RANK() OVER (
            PARTITION BY department_id 
            ORDER BY salary DESC
        ) AS salary_rank
    FROM employees
)
SELECT * FROM ranked_employees
WHERE salary_rank <= 3;  -- Top 3 per department

DML Best Practices

Always use explicit column names in INSERT. Always include WHERE in UPDATE and DELETE. Use transactions for multi-statement operations. Test with SELECT before running UPDATE/DELETE. Use RETURNING to verify what was affected.

Data Control Language (DCL)

Data Control Language (DCL) manages access permissions—who can do what to which database objects. Security is fundamental to database systems, and DCL provides the mechanism for implementing access control.

Key Characteristic: DCL implements the 'authorization' aspect of database security. It determines which users can perform which operations on which objects.

DCL Security Concepts

•Privilege — A right to perform a specific operation (SELECT, INSERT, UPDATE, DELETE, EXECUTE, etc.)
•Role — A named collection of privileges that can be granted to users or other roles
•User — An account that can connect to the database and perform operations
•Object — A database entity (table, view, procedure) that privileges apply to
•Grantor — The user granting the privilege (must own the object or have GRANT OPTION)
•Grantee — The user or role receiving the privilege

DCL Examples
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
-- ================================================
-- GRANT: Giving permissions
-- ================================================
 
-- Grant specific privileges on a table
GRANT SELECT, INSERT, UPDATE 
ON employees 
TO analyst_user;
 
-- Grant all privileges on a table
GRANT ALL PRIVILEGES 
ON employees 
TO admin_user;
 
-- Grant with ability to re-grant (GRANT OPTION)
GRANT SELECT ON employees 
TO team_lead 
WITH GRANT OPTION;
-- team_lead can now GRANT SELECT to others
 
-- Grant on specific columns only
GRANT SELECT (employee_id, first_name, last_name) 
ON employees 
TO public_viewer;
-- Cannot see salary, email, etc.
 
-- Grant EXECUTE on functions/procedures
GRANT EXECUTE 
ON FUNCTION calculate_bonus(INTEGER) 
TO payroll_system;
 
-- Grant usage on schema
GRANT USAGE ON SCHEMA hr 
TO reporting_app;
 
-- Grant connect to database
GRANT CONNECT ON DATABASE company_db 
TO new_employee;
 
-- ================================================
-- Role Management
-- ================================================
 
-- Create a role (group of privileges)
CREATE ROLE hr_analyst;
 
-- Grant privileges to the role
GRANT SELECT, INSERT ON employees TO hr_analyst;
GRANT SELECT ON departments TO hr_analyst;
GRANT USAGE ON SCHEMA hr TO hr_analyst;
 
-- Grant role to users
GRANT hr_analyst TO alice;
GRANT hr_analyst TO bob;
GRANT hr_analyst TO carol;
-- All three users now have hr_analyst privileges
 
-- Create role hierarchy
CREATE ROLE hr_manager;
GRANT hr_analyst TO hr_manager;  -- Inherits analyst privs
GRANT UPDATE, DELETE ON employees TO hr_manager;
 
-- ================================================
-- REVOKE: Removing permissions
-- ================================================
 
-- Revoke specific privilege
REVOKE UPDATE 
ON employees 
FROM analyst_user;
 
-- Revoke all privileges
REVOKE ALL PRIVILEGES 
ON employees 
FROM temp_user;
 
-- Revoke from role
REVOKE hr_analyst FROM carol;
 
-- Revoke CASCADE (also revoke from users who received 
-- the privilege via GRANT OPTION)
REVOKE SELECT ON employees 
FROM team_lead 
CASCADE;
 
-- ================================================
-- Row-Level Security (PostgreSQL)
-- ================================================
 
-- Enable row-level security on table
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;
 
-- Create policy: Users see only their department
CREATE POLICY department_visibility ON employees
    FOR SELECT
    USING (department_id = current_user_department());
 
-- Create policy: Users can update only their own records
CREATE POLICY self_update ON employees
    FOR UPDATE
    USING (employee_id = current_employee_id())
    WITH CHECK (employee_id = current_employee_id());

Principle of Least Privilege

Grant only the minimum permissions necessary for a user or application to perform its function. Avoid granting ALL PRIVILEGES or using the admin/superuser account for application connections. More privileges = more risk if the account is compromised.

Transaction Control Language (TCL)

Transaction Control Language (TCL) manages database transactions—logical units of work that must be processed completely or not at all. TCL ensures the 'A' (Atomicity) and 'D' (Durability) of ACID properties.

Key Characteristic: TCL defines the boundaries around groups of operations, ensuring they succeed together or fail together.

Transaction Concepts

•Transaction — A sequence of operations performed as a single logical unit of work
•COMMIT — Makes all changes in the current transaction permanent and visible
•ROLLBACK — Undoes all changes in the current transaction
•SAVEPOINT — Creates a named point within a transaction to which you can rollback
•Auto-commit — Mode where each statement is its own transaction (common default)

TCL Examples
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
-- ================================================
-- Basic Transaction Control
-- ================================================
 
-- Start a transaction explicitly
BEGIN TRANSACTION;
-- or just: BEGIN; (PostgreSQL)
-- or: START TRANSACTION; (standard SQL)
 
-- Perform operations
UPDATE accounts SET balance = balance - 500 WHERE id = 1001;
UPDATE accounts SET balance = balance + 500 WHERE id = 2002;
INSERT INTO transfers (from_acc, to_acc, amount, transfer_date)
VALUES (1001, 2002, 500, CURRENT_TIMESTAMP);
 
-- Make permanent
COMMIT;
-- All three operations are now permanent
-- If any failed, we could have done ROLLBACK instead
 
-- ================================================
-- ROLLBACK: Undo changes
-- ================================================
 
BEGIN TRANSACTION;
 
DELETE FROM orders WHERE order_date < '2020-01-01';
-- Realized this was wrong!
 
ROLLBACK;
-- All deletes are undone, as if they never happened
 
-- ================================================
-- SAVEPOINT: Partial rollback
-- ================================================
 
BEGIN TRANSACTION;
 
-- First operation
INSERT INTO orders (customer_id, order_date, total)
VALUES (100, CURRENT_DATE, 250.00);
 
SAVEPOINT after_order;  -- Mark this point
 
-- Second operation (might fail)
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (LASTVAL(), 42, 1);
 
-- If second operation has issues:
ROLLBACK TO SAVEPOINT after_order;
-- First INSERT is kept, second is undone
 
-- Fix and retry
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (LASTVAL(), 43, 2);  -- Different product
 
COMMIT;  -- Now both operations are permanent
 
-- ================================================
-- Transaction Isolation Levels
-- ================================================
 
-- Set isolation level for a transaction
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Options: READ UNCOMMITTED, READ COMMITTED, 
--          REPEATABLE READ, SERIALIZABLE
 
SELECT balance FROM accounts WHERE id = 1001;
-- With SERIALIZABLE, this account is protected from 
-- changes by other transactions until we COMMIT
 
UPDATE accounts SET balance = balance - 100 WHERE id = 1001;
 
COMMIT;
 
-- ================================================
-- Practical Pattern: Error Handling
-- ================================================
 
-- PostgreSQL procedural example
DO $$
BEGIN
    -- Start transaction (implicit with DO block)
    
    UPDATE accounts SET balance = balance - 500 WHERE id = 1001;
    UPDATE accounts SET balance = balance + 500 WHERE id = 2002;
    
    -- Check business rule
    IF (SELECT balance FROM accounts WHERE id = 1001) < 0 THEN
        RAISE EXCEPTION 'Insufficient funds';
    END IF;
    
    -- If we get here, commit happens automatically
    
EXCEPTION
    WHEN OTHERS THEN
        -- Any error causes rollback
        RAISE NOTICE 'Transaction failed: %', SQLERRM;
        -- Rollback is automatic in exception block
END $$;

Transaction Best Practices

Keep transactions short—long transactions hold locks and block other users. Include all related operations in one transaction. Always handle errors and rollback on failure. Avoid user interaction within transactions. Use savepoints for complex operations where partial recovery is useful.

How Database Languages Work Together

In practice, the four database language categories work together in coordinated ways. Understanding their interactions is crucial for effective database management.

Complete Database Lifecycle Example
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
-- ================================================
-- DDL: Define the structure (typically done once)
-- ================================================
 
CREATE TABLE bank_accounts (
    account_id      SERIAL PRIMARY KEY,
    account_holder  VARCHAR(100) NOT NULL,
    balance         DECIMAL(15,2) NOT NULL CHECK (balance >= 0),
    account_type    VARCHAR(20) DEFAULT 'checking',
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE transactions (
    transaction_id  SERIAL PRIMARY KEY,
    from_account    INTEGER REFERENCES bank_accounts(account_id),
    to_account      INTEGER REFERENCES bank_accounts(account_id),
    amount          DECIMAL(15,2) NOT NULL CHECK (amount > 0),
    transaction_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status          VARCHAR(20) DEFAULT 'completed'
);
 
CREATE INDEX idx_trans_accounts ON transactions(from_account, to_account);
 
-- ================================================
-- DCL: Set up access control
-- ================================================
 
CREATE ROLE bank_teller;
CREATE ROLE bank_manager;
CREATE ROLE audit_team;
 
-- Tellers can view and perform transactions
GRANT SELECT ON bank_accounts TO bank_teller;
GRANT INSERT ON transactions TO bank_teller;
GRANT EXECUTE ON FUNCTION perform_transfer TO bank_teller;
 
-- Managers can do everything tellers can, plus more
GRANT bank_teller TO bank_manager;
GRANT UPDATE ON bank_accounts TO bank_manager;
 
-- Auditors can only view
GRANT SELECT ON bank_accounts TO audit_team;
GRANT SELECT ON transactions TO audit_team;
 
-- ================================================
-- DML + TCL: Daily operations (done constantly)
-- ================================================
 
-- A bank transfer operation using TCL + DML
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
-- Check balance (DML SELECT)
SELECT balance INTO @current_balance 
FROM bank_accounts 
WHERE account_id = 1001
FOR UPDATE;  -- Lock the row
 
IF @current_balance < 500 THEN
    ROLLBACK;  -- TCL
    -- Signal error
END IF;
 
-- Perform transfer (DML UPDATE)
UPDATE bank_accounts 
SET balance = balance - 500 
WHERE account_id = 1001;
 
UPDATE bank_accounts 
SET balance = balance + 500 
WHERE account_id = 2002;
 
-- Record transaction (DML INSERT)
INSERT INTO transactions (from_account, to_account, amount)
VALUES (1001, 2002, 500);
 
COMMIT;  -- TCL - make permanent
 
-- ================================================
-- DDL: Evolution (done during maintenance)
-- ================================================
 
-- Add audit column
ALTER TABLE transactions 
ADD COLUMN processed_by VARCHAR(50) DEFAULT CURRENT_USER;
 
-- Update permissions for new column
GRANT UPDATE (processed_by) ON transactions TO bank_manager;

Language Category Summary
Aspect	DDL	DML	DCL	TCL
Purpose	Schema definition	Data operations	Access control	Transaction control
Frequency	Rare (setup/migration)	Constant (every query)	Periodic (user mgmt)	Around DML operations
Who Uses	DBAs, DevOps	Applications, Users	Security Admins	Applications
Auto-Commit	Often yes (varies)	No (transactional)	Yes	N/A (controls transactions)
Rollback?	Usually no	Yes (in transaction)	Usually no	That's its purpose!

The Complete Picture

DDL creates the stage. DCL decides who can access it. DML performs the action. TCL ensures the action is atomic. Together, they enable complete database management—from creation through operation to access control.

Beyond SQL: Other Database Languages

While SQL dominates relational databases, other query languages exist for different data models and use cases. Understanding the landscape helps when working with diverse database systems.

Non-SQL Database Languages
Language	Database Type	Example Database	Key Characteristics
Cypher	Graph	Neo4j	Pattern matching for nodes and relationships; ASCII art syntax
Gremlin	Graph	JanusGraph, Neptune	Traversal-based; imperative style; part of Apache TinkerPop
MQL	Document	MongoDB	JSON-like query syntax; aggregation pipelines
AQL	Multi-model	ArangoDB	Combines graph traversal with SQL-like syntax
CQL	Wide-column	Cassandra	SQL-like but no JOINs; partition-aware
N1QL	Document	Couchbase	SQL for JSON documents; full JOIN support
PartiQL	Multi-model	AWS (DynamoDB)	SQL-compatible for semi-structured data

Non-SQL Examples

Cypher

// Cypher (Neo4j) - Find friends of friends
MATCH (user:Person {name: 'Alice'})-[:FRIENDS_WITH*2]->(fof:Person)
WHERE NOT (user)-[:FRIENDS_WITH]->(fof)
RETURN DISTINCT fof.name AS recommendation
 
// MongoDB Query Language
db.employees.find({
    department: "Engineering",
    salary: { $gte: 100000 },
    skills: { $in: ["Python", "Go"] }
}).sort({ hireDate: -1 }).limit(10)
 
// Cassandra CQL
SELECT employee_name, department 
FROM employees 
WHERE company_id = 'ACME' 
  AND employee_id = 12345;
-- Note: Must include partition key (company_id)

SQL Remains Dominant

Despite the variety of database languages, SQL skills remain the most valuable. Many NoSQL databases are adding SQL-like interfaces (MongoDB: SQL API, Cassandra: CQL), and the concepts (DDL, DML, etc.) transfer across languages. Invest in SQL mastery first.

Summary: Database Languages

We've explored the four categories of database languages that together enable complete database management. Let's consolidate the key insights:

Key Takeaways

•SQL is composed of sub-languages — DDL for structure, DML for data, DCL for access, TCL for transactions. Each serves a distinct purpose.
•DDL defines database structure — CREATE, ALTER, DROP, TRUNCATE operate on schema, not data. Often auto-commits.
•DML manipulates data — SELECT, INSERT, UPDATE, DELETE are the daily operations. Fully transactional.
•DCL controls access — GRANT and REVOKE manage permissions. Roles group privileges for easier management.
•TCL manages transactions — BEGIN, COMMIT, ROLLBACK, SAVEPOINT ensure atomicity and enable recovery from errors.
•Languages work together — DDL creates schema, DCL sets permissions, DML operates within TCL-defined transaction boundaries.
•Principle of least privilege — Grant minimum necessary permissions. Use roles to simplify management.
•Transactions should be short — Long transactions cause contention. Always handle errors with rollback.

Module Complete:

With this page, you've completed Module 3: DBMS Concepts. You now understand what a DBMS is, how its components work together, how data abstraction enables independence, what data models are available, and how database languages enable complete database management.

This conceptual foundation prepares you for the next chapters where we'll dive deeper into specific aspects: database architecture, data models, and the query languages that bring databases to life.

Module Complete

Congratulations! You've mastered the fundamental concepts of Database Management Systems. You understand DBMS definition, architecture, abstraction levels, data models, and the complete range of database languages. This knowledge forms the foundation for all advanced database topics that follow.