Database Concepts
This is my Database Concepts course at Purdue (IT234), and it is still in progress. It walks through the full database development lifecycle in order, from a rough idea of the data all the way to running queries against a loaded database. The running example is a movies dataset, a flat file of films with their directors, lead stars, genres, and producers, which gets carried through every stage so each unit builds on the last. All of the hands-on work is in Microsoft SQL Server using SQL Server Management Studio.
What the course covers
The early units are design on paper before any database exists. I started with conceptual modeling, picking out entities and the relationships between them and reasoning about cardinality, then moved to logical design and normalization to third normal form. That normalization work is the part that clicked most: taking one wide flat file and splitting it into Director, Star, Genre, Producer, and Movies tables so each fact is stored once, no repeating groups, no transitive dependencies, with foreign keys tying it back together.
From there it turns physical. I built the same schema two ways, once by hand in the SSMS table designer and once from a DDL script, defining data types, character limits, nullability, primary keys, and named foreign key constraints. A later unit covered schema modification with ALTER TABLE, adding and dropping columns, changing data types, and adding a NOT NULL constraint, along with why ALTER is almost always preferable to dropping and rebuilding an object that already holds data.
The most recent units are about getting data in and back out. I contrasted manual row entry against a repeatable migration script that used SELECT DISTINCT and NOT IN guards to deduplicate the source and correlated subqueries to resolve foreign keys. Then came SELECT practice: filtering, LIKE, BETWEEN, IS NULL, DISTINCT, aggregates with GROUP BY and HAVING, and calculated columns, first on the movies database and now against the Northwind sample database.
What is useful
The direct payoff is that I run a SQL Server container in my home lab, so this stops being abstract. I can create a database, design its tables properly, load it, and query it, and I understand why the schema is shaped the way it is. Debugging a double-encoded UTF-8 issue in one deployment script was a good reminder that data problems often live in the source, not the query. More units are still to come.