Database Design and Basic SQL in PostgreSQL Course

Lesson 1 • Introduction to Entity-Relationship Diagrams

Teaches ER diagram notation to visualise data models before implementation. Diagrams created here are refined throughout the course.

Lesson 2 • Relationships Between Tables

Covers one-to-one, one-to-many, and many-to-many relationships. Provides the vocabulary needed for entity-relationship modelling.

Lesson 3 • What Is a Relational Database

Defines relational databases, contrasts them with flat files and NoSQL. Establishes the conceptual baseline for all subsequent design work.

Lesson 4 • Primary Keys and Foreign Keys

Introduces key constraints that enforce entity and referential integrity. Students apply these concepts when linking tables in later chapters.

Lesson 1 • Creating Your First Database

Walks through creating a database and schema with proper ownership settings. This practice database is used in all remaining chapters.

Lesson 2 • Installing PostgreSQL Locally

Guides installation on major operating systems and verifies a successful setup. A working local instance is required for all lab exercises.

Lesson 3 • Using psql Command-Line Tool

Introduces the psql interface for executing SQL and meta-commands. Command-line fluency accelerates all subsequent query exercises.

Lesson 4 • Using pgAdmin Graphical Interface

Covers pgAdmin navigation, query tool, and object browser. Provides a visual alternative for students less comfortable with the command line.

Lesson 1 • Text and Character Data Types

Explains CHAR, VARCHAR, and TEXT with performance implications. Choosing the right text type affects storage efficiency and indexing.

Lesson 2 • CREATE TABLE and Column Constraints

Teaches DDL syntax for table creation with NOT NULL, UNIQUE, and CHECK constraints. Constraints enforce data quality at the database level.

Lesson 3 • Date, Time, and Timestamp Types

Introduces temporal types including timezone-aware variants. Proper temporal storage is essential for accurate reporting and scheduling.

Lesson 4 • Numeric and Boolean Data Types

Covers integer, decimal, and boolean types with storage and precision trade-offs. Correct type selection prevents data loss and query errors.

Lesson 1 • Third Normal Form

Explains transitive dependencies and how 3NF eliminates them. Most production schemas target 3NF as the practical design standard.

Lesson 2 • Designing a Multi-Table Schema

Applies normalisation to a realistic business scenario end-to-end. Students produce a complete ER diagram and matching DDL script.

Lesson 3 • First and Second Normal Forms

Defines 1NF atomicity and 2NF full functional dependency. Students practise decomposing tables to satisfy each form.

Lesson 4 • Problems with Unnormalised Data

Demonstrates insertion, update, and deletion anomalies in flat tables. Motivates normalisation as the solution to structural data problems.

Lesson 1 • Sorting Results with ORDER BY

Teaches ascending and descending sort on single and multiple columns. Ordered output is required for reports and pagination.

Lesson 2 • Aggregate Functions and GROUP BY

Introduces COUNT, SUM, AVG, MIN, and MAX with GROUP BY grouping. Aggregation enables summary reporting across categories.

Lesson 3 • Filtering Rows with WHERE

Covers comparison, logical, and pattern operators in WHERE clauses. Filtering is the most frequently used skill in day-to-day SQL work.

Lesson 4 • SELECT and FROM Fundamentals

Introduces SELECT syntax, column aliases, and the FROM clause. These are the building blocks of every query written in the course.

Lesson 1 • INNER JOIN Fundamentals

Explains how INNER JOIN matches rows on a join condition. This is the most common join type used in relational query work.

Lesson 2 • Subqueries in SELECT and WHERE

Teaches scalar and correlated subqueries embedded in SELECT and WHERE. Subqueries enable complex filtering without temporary tables.

Lesson 3 • CROSS JOIN and Self-Join

Introduces Cartesian products and self-referencing table joins. These patterns solve hierarchical and combinatorial data problems.

Lesson 4 • Common Table Expressions

Introduces WITH clauses to name and reuse intermediate result sets. CTEs improve readability and enable recursive query patterns.

Lesson 1 • Inserting Data with INSERT

Covers single-row and multi-row INSERT syntax including returning values. Correct insert patterns prevent constraint violations.

Lesson 2 • Updating Rows with UPDATE

Teaches targeted UPDATE statements with WHERE conditions and joins. Unguarded updates are a leading cause of data corruption.

Lesson 3 • Upsert with INSERT ON CONFLICT

Covers the ON CONFLICT clause for idempotent insert-or-update operations. Upsert patterns are critical for data pipeline and sync workflows.

Lesson 4 • Deleting Rows with DELETE

Explains DELETE with WHERE filters and cascading foreign key behaviour. Safe deletion practices protect referential integrity.

Lesson 1 • Specialised Index Types

Introduces Hash, GIN, GiST, and partial indexes for specific workloads. Choosing the right index type avoids unnecessary overhead.

Lesson 2 • How PostgreSQL Executes Queries

Explains the query planner, statistics, and cost estimation. Understanding execution flow is prerequisite to meaningful optimisation.

Lesson 3 • Query Optimisation Techniques

Applies rewriting strategies to reduce plan cost and improve throughput. Students optimise real queries using EXPLAIN feedback iteratively.

Lesson 4 • Reading EXPLAIN and EXPLAIN ANALYZE

Teaches how to interpret EXPLAIN output nodes and actual timing. Plan reading is the primary diagnostic skill for slow queries.