Database Modeling: From Entity to SQL Schema
Database Modeling is an intermediate-level course of 5 lessons (about 3 h 4 min), with 18 exercises and 15 quiz questions. Prerequisites: Intermediate SQL (joins, subqueries). Exercises run in the browser, with nothing to install.
- SQL
- Intermediate
- 5 lessons
- 3 h 4 min
- 18 exercises
- Exercises run in the browser
The course in brief
This course teaches you to design a database before querying it. You will translate a business need into tables and constraints, model relationships with foreign keys, normalize a spreadsheet export up to 3NF, then build a star schema for analysis. You will finish with the physical schema: indexes, views and migrations.
What you will be able to do
- Translate business entities into typed tables with primary keys and integrity constraints
- Model 1-N and N-N relationships with foreign keys and choose the behavior on delete
- Spot anomalies and normalize a table up to third normal form
- Design a star schema with a defined grain and suitable dimensions
- Write a complete DDL script with indexes and views
- Evolve an existing schema through safe migrations
Syllabus: 5 lessons
- Translate a business entity into a table with correctly typed columns
- Choose a primary key (natural key or surrogate key)
- Protect data with NOT NULL, UNIQUE, CHECK and DEFAULT
- Check existing data before adding a constraint to it
CREATE TABLEData typesPrimary keyNOT NULL, UNIQUE, CHECK, DEFAULT
- 02
Relationships and Foreign Keys
- Identify 1-1, 1-N and N-N cardinalities between entities
- Implement a 1-N relationship with a foreign key
- Implement an N-N relationship with a junction table
- Choose the appropriate ON DELETE behavior
CardinalitiesFOREIGN KEY / REFERENCESJunction tableON DELETE CASCADE / RESTRICT / SET NULL
- 03
Normalization: 1NF, 2NF, 3NF and Anomalies
- Recognize insertion, update and deletion anomalies
- Identify functional dependencies in a table
- Apply the first three normal forms
- Decide when denormalization is acceptable
Update anomaliesFunctional dependencies1NF, 2NF, 3NFDenormalization
- 04
Dimensional Modeling: The Star Schema
- Distinguish a transactional database (OLTP) from an analytical one (OLAP)
- Identify facts, measures and dimensions in an analysis requirement
- Define and verify the grain of a fact table
- Query a star schema
- 05
From Model to Physical Schema: Views, Indexes and Migrations
- Go from a logical model to a complete, consistent DDL script
- Apply naming conventions
- Create views to simplify data access
- Evolve a schema through versioned migrations
DDL: CREATE, ALTER, DROPViewsForeign key indexesSchema migrations
Final project
Design the database of a booking platform
A nonprofit offers workshops (cooking, pottery, coding) run by instructors, in several venues. Participants book seats, pay, and can cancel. Design the complete transactional database, then a small star schema to track workshop occupancy rate and revenue.
Deliverables
- An entity-relationship diagram (image or text) with the cardinalities of each relationship
- An executable DDL script (PostgreSQL or SQLite): tables, primary and foreign keys, constraints, indexes
- A written justification of the normalization (main functional dependencies, normal form reached)
- A sample dataset and five business queries (full workshops, participants of a workshop, revenue per instructor...)
- A star schema (booking facts, date, workshop and venue dimensions) with its grain stated
- A migration that adds a business rule after the fact (waiting list or promo code) without data loss
Assessment criteria
- Cardinalities are correct and the N-N participants-workshops relationship goes through a junction table
- Constraints reflect the business rules (positive available seats, restricted booking status, uniqueness)
- The transactional model is in 3NF, and any denormalization is justified
- The fact table grain is explicit and verified by a query
- The script is readable, consistent in naming and runs without errors
Frequently asked questions
What is database normalization?
It is a design method that splits tables to remove redundancy and avoid update anomalies. For a transactional database, you generally aim for third normal form (3NF).
What is the difference between a normalized model and a star schema?
The normalized model serves applications that record data without inconsistencies. The star schema, with a fact table and denormalized dimensions, serves analysis and tools like Power BI.
Do I need to know SQL before this course?
Yes: the course assumes you are comfortable with SELECT queries, joins and subqueries (SQL fundamentals and intermediate SQL courses).
Is this course useful for a web developer?
Yes. Any application that stores accounts, orders or content relies on a database schema; designing it well avoids bugs and painful migrations.
This course, told for your job
Say what you want to be able to do: “Database Modeling” takes its place in your path, with examples from your field. The concepts stay those of the course.