Database Management Systems & SQL Masterclass

Welcome to the CSL303 / MAL505 Database Management Systems comprehensive documentation and study hub. This course covers everything from the theoretical foundations of the relational model and relational algebra to practical, high-performance SQL querying, transaction management, and database application development.

Course Structure & Modules

The course is divided into five core conceptual modules and a practical lab series:

Module 1: Relational Foundations

ANSI/SPARC 3-schema architecture, data independence, relations, tuples, candidate/primary keys, and formal Relational Algebra.

Module 2: Core SQL Querying

Schema design (DDL), constraints, single-table filtering, join family (inner, outer, anti, self), and aggregate summaries.

Module 3: Advanced SQL & Analytics

Scalar & correlated subqueries, WITH CTEs, EXISTS, the NOT IN NULL trap, and analytical Window Functions (OVER, RANK, LAG/LEAD).

Module 4: Programming & Transactions

Safe DML updates, UPSERT, Triggers, Python DB-API, SQL injection prevention, Updatable/Materialized Views, and ACID transactions.

Module 5: Practice & Interview Questions

Top LeetCode SQL 50 problems (Easy, Medium, Hard), Core DBMS Interview Questions (Normalization, B+ Trees, Concurrency), and Topic Question Bank.

Course Source PDFs

Direct access and downloads for all 17 original lecture slides and lab handouts (Lectures 01–12, Labs 01–02).

Standard Schemas Used Across the Course

Throughout the lectures, two primary relational schemas are utilized for examples, queries, and lab exercises:

1. The University Schema

Used in Lectures 2–6a and Lab 02:

department(dept_name, building, budget)
course(course_id, title, dept_name, credits)
instructor(id, name, dept_name, salary)
student(id, name, dept_name, tot_cred)
section(course_id, sec_id, semester, year, building, room_no)
teaches(id, course_id, sec_id, semester, year)
takes(id, course_id, sec_id, semester, year, grade)
prereq(course_id, prereq_id)
advisor(s_id, i_id)
classroom(building, room_number, capacity)

2. The Retail Store Schema

Used in Lectures 7–9b for aggregation, subqueries, and window analytics:

customers(id, name, city, signup_date)
categories(cat_id, cat_name)
products(prod_id, prod_name, cat_id, price, stock)
orders(order_id, customer_id, order_date, total_amount)
order_items(order_id, prod_id, quantity, unit_price)

Recommended Toolchain

For working through the exercises and labs:

SQLite 3 CLI (sqlite3): Fast, lightweight, zero-configuration local experimentation.
DB Browser for SQLite: Visual GUI for inspecting tables, schemas, and query execution plans.
Python 3 sqlite3: Scripting queries, handling parameterized execution, and preventing injection.
PostgreSQL: Production-grade client/server RDBMS used for advanced concurrency, triggers, and materialized views.

Course Overview & Syllabus