Database Schema Design: Normalization Forms and Anomaly Resolution

This chapter covers core relational database management concepts, focusing on Structured Query Language (SQL) execution flows, table manipulation commands, query optimizations, advanced database objects, and data integrity constraints.

1. DDL vs. DML vs. DQL: Structural and Data Commands

In SQL, statements are categorized based on their purpose—managing structures, handling data within rows, or querying database instances.

• DDL (Data Definition Language): Manages database structures and schemas (e.g., CREATE, DROP, ALTER).
• DML (Data Manipulation Language): Modifies data stored inside tables without altering the underlying schema (e.g., INSERT, UPDATE, DELETE).
• DQL (Data Query Language): Retrieves data from tables without changing state (e.g., SELECT).

2. Table Erasure and Modification: DROP vs. TRUNCATE vs. DELETE

Choosing the correct command to remove data or structures depends on requirements around speed, rollback capabilities, and conditional filtering.

Real-Life Analogy

• DROP (Demolishing the Building): Rips down the entire building (table schema) and hauls away all the furniture (data). Nothing remains, and you cannot target individual rooms (WHERE clause is invalid).
• TRUNCATE (Mass House-Clearing): Keeps the building structure intact, but completely empties all rooms at once instantly. Cannot target specific items (WHERE clause is invalid).
• DELETE (Targeted Room Cleaning): Goes into a specific room and removes only the items matching your exact criteria using a conditional filter (WHERE clause).

3. Database Architecture and Basic CRUD Operations

A relational database management system (RDBMS) organizes data hierarchically, starting from isolated database instances down to structured tables and individual row values.

Hierarchical Layout

Core SQL Operations (CRUD)

-- 1. Create a Database & Table
CREATE DATABASE university;
USE university;

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT
);

-- 2. Insert Data
INSERT INTO students (student_id, name, email, age) 
VALUES (1001, 'Alice', 'alice@uni.edu', 20);

-- 3. Update Data with Condition
UPDATE students 
SET email = 'new_alice@uni.edu' 
WHERE student_id = 1001;

-- 4. Delete Specific Record
DELETE FROM students 
WHERE student_id = 1004;

4. Advanced Data Query Language (DQL) and Pattern Matching

The SELECT statement allows complex filtering, sorting, pagination, and pattern matching using wildcards.

Pattern Matching (LIKE)

• %: Represents zero, one, or multiple characters.
• _: Represents exactly a single character.

-- Retrieve students whose names start with 'R'
SELECT * FROM students WHERE name LIKE 'R%';

-- Retrieve students whose second letter is 'R'
SELECT * FROM students WHERE name LIKE '_R%';

Pagination and Sorting

Pagination uses sorting, limits, and offsets to manage large datasets in chunks.

-- Sort ascending/descending and paginate
SELECT * FROM students 
ORDER BY age DESC 
LIMIT 10 OFFSET 5;

Note: An OFFSET 15 skips the first 15 records and fetches the next 5 records.

5. Aggregation, Grouping, and Execution Filtering (WHERE vs. HAVING)

Aggregation functions calculate metrics across multiple rows (e.g., AVG(), MAX(), MIN(), COUNT()). Filtering grouped data requires understanding the distinct execution phases of WHERE and HAVING.

-- Group by department and filter groups having more than 5 members
SELECT department_id, COUNT(*) as total_students 
FROM students 
GROUP BY department_id 
HAVING COUNT(*) > 5;

Difference Between WHERE and HAVING

• WHERE: Filters individual raw rows before any grouping takes place. It is lightweight and fast.
• HAVING: Filters aggregated groups after the GROUP BY operation has completed. It is a heavier operation for the database engine.

6. SQL Query Execution Order

When a query runs, the database engine does not execute clauses in the order they are written. Understanding this order is vital to avoid bugs (such as using column aliases incorrectly).

Strict Execution Pipeline

Warning: You cannot use a column alias defined in the SELECT clause inside a WHERE clause, because WHERE executes before SELECT is evaluated.

7. Database Joins

Joins combine columns from two or more tables based on a related column between them (such as a foreign key).

Types of Joins

• INNER JOIN: Returns only the rows where there is a match in both tables. No NULL padding.
• LEFT JOIN (Left Outer Join): Returns all rows from the left table, and the matched rows from the right table. Unmatched right-side values appear as NULL.
• RIGHT JOIN (Right Outer Join): Returns all rows from the right table, and the matched rows from the left table. Unmatched left-side values appear as NULL.
• FULL JOIN: Returns all rows when there is a match in either left or right table, filling missing matches with NULL on either side.
• CROSS JOIN: Produces a Cartesian product (every row in table A multiplied by every row in table B).

-- Inner Join Example
SELECT e.name, d.department_name 
FROM employees e
INNER JOIN departments d 
ON e.department_id = d.department_id;

8. Subqueries, Views, and Virtual Abstractions

Subqueries

A subquery is a query nested inside a larger query.

-- Find students older than the average age
SELECT * FROM students 
WHERE age > (SELECT AVG(age) FROM students);

Views

A view is a saved, virtual table based on the result-set of an SQL statement. It provides data security and simplifies complex joins.

-- Creating a View
CREATE VIEW computer_science_students AS 
SELECT student_id, name, email 
FROM students 
WHERE department_id = 101;

-- Querying the View
SELECT * FROM computer_science_students;

9. Stored Procedures and Triggers

Stored Procedures

A stored procedure is a precompiled collection of SQL statements stored inside the database catalog, executed by name to reduce network traffic and promote code reuse.

-- Creating a Stored Procedure (Conceptual Syntax)
DELIMITER //
CREATE PROCEDURE GetEmployeeById(IN emp_id INT)
BEGIN
    SELECT * FROM employees WHERE id = emp_id;
END //
DELIMITER ;

-- Executing the Procedure
CALL GetEmployeeById(101);

Triggers

A trigger is a specialized stored procedure that executes automatically in response to specific database events (such as INSERT, UPDATE, or DELETE) on a table. Often used for audit logging.

-- Creating a Trigger for Audit Logging
CREATE TRIGGER before_student_delete
BEFORE DELETE ON students
FOR EACH ROW
BEGIN
    INSERT INTO student_logs(student_id, deleted_at) 
    VALUES (OLD.student_id, NOW());
END;

10. Summary and Key Takeaways

• DROP vs TRUNCATE vs DELETE: DROP removes structure and data unconditionally; TRUNCATE clears all data instantly keeping schema; DELETE removes specific rows conditionally via WHERE and supports rollbacks.
• Execution Pipeline: SQL statements execute in a strict sequence starting from FROM/JOIN, passing through WHERE, GROUP BY, HAVING, and ending with SELECT and LIMIT.
• Alias Restriction: Column aliases created in SELECT cannot be referenced inside WHERE clauses because WHERE evaluates earlier in the pipeline.
• WHERE vs HAVING: WHERE filters raw rows prior to aggregation, whereas HAVING filters grouped results after aggregation.
• Inner vs Outer Joins: INNER JOIN returns strict matches with zero NULL values, while LEFT/RIGHT joins preserve unmatching records padded with NULL.
• Cartesian Product: A CROSS JOIN multiplies every row of table A by every row of table B, yielding $N \times M$ rows.
• Views for Abstraction: Views encapsulate complex multi-table join logic into reusable virtual tables, enhancing security and readability.
• Stored Procedures: Precompiled SQL routines that execute server-side to minimize network round-trips and optimize performance.
• Triggers for Automation: Automated routines linked to table events (INSERT, UPDATE, DELETE) used primarily for maintenance, security auditing, and logging.