🗄️Course Curriculum

Database Fundamentals with SQL

Database & Backend Systems· Intermediate· Ages 14–17· 30 Hours

Course At a Glance

Program Outcomes

By the end of this course, students will be able to:

1
Design relational databases using structured tables and relationships.
2
Write SQL queries to retrieve, modify, and analyse data.
3
Apply joins and aggregation to generate meaningful insights from multi-table databases.
4
Develop a complete database project demonstrating backend system design skills.

Module 1

Introduction to Databases & Relational Concepts

Students learn what databases are, how data is organised in tables with primary and foreign keys, ER diagram design, normalisation (1NF–3NF), and SQL constraints.

Approx. 7–8 hrs

#	Lesson Title	What Students Learn	Activity / Project	Key SQL / Concepts
1.1	What is a Database?	Define databases vs. spreadsheets (scale, relationships, concurrent access). Explore DBMS types like SQLite, MySQL, PostgreSQL.	Database vs Spreadsheet: Discuss why a school spreadsheet fails at scale. List problems and sketch tables.	`DBMS, relational database, table, row, column, SQLite, MySQL, PostgreSQL`
1.2	Tables, Rows & Columns	Understand the relational model and atomic values. Learn basic SQL data types (INTEGER, TEXT, DATE).	First Table: Open DB Browser for SQLite. Create a 'students' table and manually insert records.	`CREATE TABLE, INTEGER, TEXT, DATE, REAL, NULL, atomic values, DB Browser`
1.3	Primary Keys & Uniqueness	Define primary keys to uniquely identify rows. Distinguish natural vs. surrogate keys and AUTOINCREMENT.	Key Design Lab: Decide PKs for 6 different tables. Add a PRIMARY KEY constraint and test duplicate errors.	`PRIMARY KEY, AUTOINCREMENT, UNIQUE, NOT NULL, natural vs surrogate key`
1.4	Foreign Keys & Relationships	Link tables using foreign keys. Learn 1:1, 1:N, and M:N relationships and ON DELETE behaviours (CASCADE).	Relationship Builder: Link 'classes' and 'teachers' tables. Attempt invalid insertions to test foreign key constraints.	`FOREIGN KEY, REFERENCES, ON DELETE CASCADE/RESTRICT, junction table, 1:1, 1:N, M:N`
1.5	ER Diagrams	Draw ER diagrams using Crow's Foot notation to visually blueprint entities, attributes, and relationships.	ER Design Challenge: Draw an ER diagram for a school management system from a paragraph spec.	`ER diagram, entity, attribute, relationship, cardinality, Crow's Foot notation`
1.6	Normalisation: Removing Redundancy	Eliminate update/deletion anomalies by normalising data to 1NF, 2NF, and 3NF.	Normalise This: Redesign a denormalised, comma-separated table into a proper 3NF schema.	`1NF (atomic), 2NF (no partial dependency), 3NF (no transitive dependency), update anomaly`
1.7	Constraints & Data Integrity	Apply database-level rules: NOT NULL, UNIQUE, CHECK, and DEFAULT constraints.	Constraint Enforcement: Alter the students table to add CHECK(age BETWEEN 11 AND 18). Trigger violation errors.	`NOT NULL, UNIQUE, CHECK(), DEFAULT, ALTER TABLE ADD CONSTRAINT`
1.8	Module 1 Project: Schema Design	Apply all concepts to design, normalise, and create a complete database schema with populated sample data.	Project: 'School Database Schema' — create tables, PK/FK constraints, and ER diagrams for a full school DB.	`Full Module 1 — CREATE TABLE, PRIMARY KEY, FOREIGN KEY, constraints, ER diagram`

Module 2

SQL Basics

Students master core SQL commands: SELECT, WHERE, ORDER BY, LIMIT, INSERT, UPDATE, DELETE, and built-in SQL functions for strings and dates.

Approx. 7–8 hrs

#	Lesson Title	What Students Learn	Activity / Project	Key SQL / Concepts
2.1	SELECT: Reading Data	Retrieve specific columns using SELECT, aliasing with AS, and eliminating duplicates with DISTINCT.	Query Practice: Write 8 queries to pull specific student data, use concatenation, and apply aliases.	`SELECT col, *, DISTINCT, AS 'alias', \|\| concatenation, FROM tablename`
2.2	WHERE: Filtering Data	Filter rows using operators (=, <, BETWEEN, IN, LIKE, IS NULL) and boolean logic (AND, OR, NOT).	Filter Challenge: Write 10 queries extracting specific demographics, wildcards, and NULL email checks.	`WHERE, =, !=, <, >, BETWEEN, IN(), IS NULL, LIKE '%pattern%', AND, OR, NOT`
2.3	ORDER BY & LIMIT	Sort results (ASC/DESC) across multiple columns and restrict result counts using LIMIT/OFFSET.	Ranking Queries: Find top scorers, oldest students, and page 2 results using ORDER BY and LIMIT.	`ORDER BY col ASC/DESC, ORDER BY col1, col2, LIMIT n, OFFSET n`
2.4	INSERT: Adding Data	Add new rows safely. Use multi-row INSERTs and INSERT INTO ... SELECT for copying tables.	Data Population: Insert new students, copy grade 12s to an 'alumni' table, and handle primary key conflicts.	`INSERT INTO table (cols) VALUES (vals), multi-row INSERT, INSERT INTO ... SELECT`
2.5	UPDATE: Modifying Data	Safely modify rows using UPDATE...SET...WHERE. Understand the danger of omitting WHERE.	Safe Update Practice: Promote grades and add bonus points. Demonstrate an accidental full-table update and ROLLBACK.	`UPDATE table SET col = val WHERE condition, SET col = col + n, ROLLBACK`
2.6	DELETE: Removing Data	Remove rows safely. Understand the difference between DELETE, TRUNCATE, and DROP TABLE.	Controlled Deletion: Delete specific students. Observe CASCADE DELETE triggering across foreign keys.	`DELETE FROM table WHERE condition, CASCADE DELETE, TRUNCATE, DROP TABLE`
2.7	SQL Functions: String, Date & Math	Transform data on-the-fly using UPPER(), LENGTH(), ROUND(), and date formatting functions.	Function Lab: Normalise names, extract area codes, and calculate age in days using SQL functions.	`UPPER(), LOWER(), LENGTH(), ROUND(), SUBSTR(), DATE(), STRFTIME(), ABS()`
2.8	Module 2 Project: Student Database Queries	Query and maintain databases using the complete CRUD (Create, Read, Update, Delete) lifecycle.	Query Set: Write 15 realistic school admin queries spanning SELECT, UPDATE, DELETE, and functions.	`Full Module 2 — SELECT, WHERE, ORDER BY, LIMIT, INSERT, UPDATE, DELETE, functions`

Module 3

Advanced Queries & Relationships

Students write professional SQL: INNER/LEFT JOINs, aggregate functions, GROUP BY/HAVING, subqueries, and views/indexes for performance.

Approx. 7–8 hrs

#	Lesson Title	What Students Learn	Activity / Project	Key SQL / Concepts
3.1	INNER JOIN: Combining Tables	Combine data across tables using INNER JOIN ... ON. Utilize table aliases to keep queries readable.	First Joins: Write 6 queries connecting students to classes and subjects to teachers.	`INNER JOIN ... ON a.id = b.a_id, table aliases (t1, t2), join condition`
3.2	LEFT JOIN, RIGHT JOIN & NULL Handling	Use LEFT JOIN to find mismatched or missing related data (anti-joins) using IS NULL checks.	Anti-Join Queries: Find students with NO enrolments, and teachers who teach NO classes.	`LEFT JOIN, IS NULL anti-join, RIGHT JOIN, FULL OUTER JOIN (concept), NULL handling`
3.3	Aggregate Functions: COUNT, SUM, AVG, MIN, MAX	Compute summary statistics (totals, means, min/max) across datasets.	Statistics Queries: Calculate total student count, average scores, and maximum scores.	`COUNT(*), COUNT(col), SUM(), AVG(), MIN(), MAX(), ROUND(AVG(), 2)`
3.4	GROUP BY & HAVING	Group rows to apply aggregates per category. Use HAVING to filter results post-aggregation.	Grouped Reports: Count students per grade. Find subjects where the average score > 70 using HAVING.	`GROUP BY col, HAVING COUNT(*) > n, query execution order, non-agg rule`
3.5	Multi-Table Joins & Complex Queries	Chain 3+ JOINs together. Combine WHERE, JOIN, GROUP BY, HAVING, and ORDER BY sequentially.	Complex Query Challenge: Write queries linking students, subjects, scores, and teachers simultaneously.	`3-table JOIN chain, aliasing, WHERE + JOIN + GROUP BY + HAVING + ORDER BY`
3.6	Subqueries	Nest SELECT queries to pass scalar values or lists (IN) to outer queries. Understand EXISTS.	Subquery Exercises: Find students who scored above the class average (scalar nested query).	`SELECT ... WHERE col = (SELECT ...), IN (SELECT ...), EXISTS (SELECT ...), correlated`
3.7	Views, Indexes & Performance	Create virtual tables with VIEWs. Speed up searches with INDEXes while understanding write-speed tradeoffs.	Optimisation Lab: Build a 'student_report' VIEW. Analyze 'SCAN TABLE' speeds via EXPLAIN QUERY PLAN.	`CREATE VIEW AS SELECT, DROP VIEW, CREATE INDEX, EXPLAIN QUERY PLAN, B-tree index`
3.8	Module 3 Project: Sales Data Analysis	Analyze a complex multi-table sales database to extract business insights using advanced SQL.	Sales Analysis Report: Answer 12 queries: total revenue per product, top 5 customers, monthly sales trends.	`Full Module 3 — JOINs, GROUP BY, HAVING, subqueries, views, indexes`

Module 4

Database Project

Students design and implement a complete database system from scratch: ER diagram, normalised schema, sample data, and a suite of 16 queries.

Approx. 7–8 hrs

#	Lesson Title	What Students Learn	Activity / Project	Key SQL / Concepts
4.1	Project Briefing & System Selection	Define the scope for a custom database system (≥5 tables, 10 query requirements).	Project Scope: Write a scope document defining entities and the core questions the DB must answer.	`System scoping, entity identification, query requirements, min 5 tables`
4.2	ER Diagram & Schema Design	Map the scope document to an ER diagram in 3NF, establishing keys and cardinality.	Design Deliverable: Produce the ER diagram and define data types, PK/FKs, and ON DELETE rules.	`ER diagram, Crow's Foot, 3NF, FK placement, ON DELETE strategy`
4.3	Database Creation & Population	Execute the schema creation using CREATE TABLE constraints and insert realistic sample data.	Build Sprint: Populate tables ensuring all FK constraints and CHECKs function properly. Teacher review.	`CREATE TABLE with all constraints, INSERT data, PRAGMA foreign_keys = ON, verification`
4.4	Core Queries: SELECT & Filtering	Write the foundational CRUD operations to answer the business questions from the scope document.	Build Sprint: Write ≥8 basic queries incorporating sorting, limits, wildcards, and clean aliases.	`SELECT with aliases, WHERE conditions, ORDER BY, LIMIT, SQL functions, -- comments`
4.5	Advanced Queries: Joins, Groups & Subqueries	Execute complex logic to answer deep analytical questions requiring JOINs and groupings.	Build Sprint: Write ≥8 advanced queries combining 3-table JOINs, GROUP BY + HAVING, and anti-joins.	`INNER/LEFT JOIN (3+ tables), GROUP BY + HAVING, subqueries, anti-join pattern`
4.6	Views, Indexes & Documentation	Optimize query execution and document the schema via a Data Dictionary and Query Reference Guide.	Documentation Sprint: Create 2 VIEWs, 1 INDEX, and produce a full Data Dictionary for the system.	`CREATE VIEW, CREATE INDEX, EXPLAIN QUERY PLAN, data dictionary, query documentation`
4.7	Testing, Validation & Peer Review	Test the database resilience to bad data and edge cases. Conduct peer reviews.	Testing Sprint: Force constraint violations, test NULL behaviours, and swap databases for peer review checks.	`Constraint testing, query verification, edge cases, peer review checklist`
4.8	Final Presentation Day	Present the designed database, ER schemas, and live query execution to demonstrate competency.	Final Demo: 5-minute presentation walking through the ER blueprint and executing 3 complex queries live.	`Full course — schema design, SQL CRUD, JOINs, GROUP BY, subqueries, views, indexes`

Teaching Notes & Tips

Pacing Guidance

Each module is ~7-8 hours. Normalisation (1.6) and LEFT JOIN anti-joins (3.2) are conceptually dense and require multiple examples. Checkpoints in Mod 4 (schema review, queries) are mandatory before proceeding.

Differentiation

Advanced students can explore window functions (ROW_NUMBER(), RANK()), CTEs (WITH clause), DB transactions (BEGIN/COMMIT), or Python sqlite3. Core students can use DB Browser for SQLite or sqliteonline.com to skip server setup.

Assessment Criteria

Final project assessed on: (1) ER schema & 3NF. (2) SQL CRUD query correctness. (3) Query complexity (JOINs/GROUP BYs). (4) Index & View implementation. (5) Complete Data Dictionary.

Tools & Environment

Primary tool: DB Browser for SQLite (portable, no server setup). Alternatives: sqliteonline.com, sqlfiddle.com. For ER mapping: draw.io or Lucidchart. SQL taught is compatible across SQLite and MySQL.

Project System Options

School Management System (students, subjects, grades), Library System (books, loans, members), Online Store (customers, products, orders), Inventory System, Hospital Database.

Prior Knowledge Expected

Students should be comfortable with basic computer logic and spreadsheet paradigms (rows/cols). No prior SQL experience needed. Prior programming experience helps, but declarative SQL logic differs from procedural logic.

Enroll Your Child Now