Database Design and PostgreSQL

Relational Modeling: Entities, Attributes, and Relationships

Turning a real-world domain into entities, attributes, and relationships — and the cardinality/optionality questions that decide how those relationships are actually implemented.

Primary Keys, Foreign Keys, and Constraints

The identity guarantee a primary key provides, the referential-integrity guarantee a foreign key enforces, and the natural-vs-surrogate key decision every table forces you to make.

Normalization: First and Second Normal Form

Functional dependencies as the underlying idea behind normalization, and the first two normal forms — eliminating repeating groups, then eliminating partial dependency on a composite key.

Third Normal Form and Denormalization Tradeoffs

Eliminating dependency on a non-key column, and the honest, deliberate cases where denormalizing a schema is the right engineering call, not a mistake.

PostgreSQL Data Types and DDL

Choosing the right PostgreSQL column type for a value — including the types SQLite doesn't distinguish at all — and the DDL that defines a table.

Implementing a Normalized Schema in PostgreSQL

Turning a relational model into real DDL with correctly-ordered CREATE TABLE statements, relationships, constraints, and seed data — and doing it for real, on your own machine.

Joins and Aggregation, Beyond the Basics

Going past the SELECT/JOIN/GROUP BY fundamentals from the SQL module: multi-table joins, HAVING vs. WHERE, and aggregate functions that hold up in both SQLite and PostgreSQL.

Subqueries and Common Table Expressions

Nesting a query inside another to answer a question a single flat query can't, and CTEs — the readable, nameable alternative for anything beyond a trivial subquery.

Window Functions: Calculations Across Rows Without Collapsing Them

The one capability GROUP BY fundamentally can't offer — a per-row calculation that sees other rows in its group without collapsing the result down to one row per group.

Transactions and ACID Guarantees

Grouping several statements into one all-or-nothing unit, and the four ACID properties that make a transaction a genuine guarantee rather than a convention.

Concurrent-Update Problems and Isolation Levels

The specific anomalies that happen when two transactions overlap in time, and how PostgreSQL's isolation levels trade off which of those anomalies each one still permits.

Indexes, Composite Indexes, and Reading EXPLAIN

How an index turns an O(n) table scan into an O(log n) lookup, when adding one is actually the wrong call, and how to read EXPLAIN to find out instead of guessing.

Views, Roles, and the Principle of Least Privilege

Naming a reusable query as a view, and the security discipline of granting every role exactly the access it needs — never more.

Migrations, Schema Evolution, and Operational Safety

Changing a live schema safely with ordered, reversible migrations, testing database behavior deliberately, and the backup/recovery discipline that makes every other guarantee in this course matter.