Database Concepts
This was my Database Concepts course at Purdue (IT234), a ten-week walk through the full database development lifecycle in order, from a rough idea of the data all the way to a secured, queryable database. The running example was a movies dataset, a flat file of films with their directors, lead stars, genres, and producers, which got carried through every stage so each unit built on the last. All of the hands-on work was in Microsoft SQL Server using SQL Server Management Studio.
What the course covered
The early units were design on paper before any database existed. 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. A second design exercise did the same for a medical intake form, comparing a normalized design from Claude against one from Gemini. Each got something right the other missed, and one contained a factual error that only checking against the source form caught. The merged design was stronger than either one taken wholesale.
From there it turned 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, and why ALTER is almost always preferable to dropping and rebuilding an object that already holds data.
The middle units were 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 on the movies database and the Northwind sample database, followed by INSERT, UPDATE, and DELETE with identity columns and foreign keys in play, and then joins: inner and outer joins, correlated and non-correlated subqueries, and UNION.
The last technical unit was the one that felt most like real administration. I wrapped queries in views, wrote parameterized stored procedures, used transactions with TRY/CATCH and explicit ROLLBACK, and set up role-based access with GRANT and REVOKE. The demonstration that made it concrete was running the identical DELETE statement as two different users and watching it succeed for one and fail for the other, purely on role membership.
The final unit stepped outside the relational model. The assignment was to recommend non-relational options for Northwind, and I proposed a phased plan: a dimensional data warehouse for reporting, a document store for external feeds, columnstore indexes once volume justified them, and a graph store for supply chain analysis. The conclusion I argued for was that the relational system stays the system of record. Most NoSQL options make you rebuild constraint enforcement, transactional safety, and authorization in application code, and those are exactly the things the relational engine already does well.
What was useful
The direct payoff is that I run a SQL Server container in my home lab, so this never stayed abstract. Partway through the course I started building a recipe database on it from scratch, with the schema in versioned migration scripts and the application allowed to touch data only through stored procedures. That is this course's last units applied for real. 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.
Several assignments had me use AI tools to generate SQL and then verify it, and the pattern held across all of them. Most of what came back ran unchanged, the failures were real, and the fix was to read the error, understand the rule it broke, and correct it. The tools sped up the typing. They did not replace knowing why the schema is shaped the way it is.