Computer Science / Beginner

Database Management Systems

Design reliable relational data and query it with SQL. Move from tables and keys to normalization, joins, transactions, and indexes.

▤ 5 modules✓ 5 PDFs✦ 1 researched video lesson

What you’ll learn

Learning objectives

Model entities and relationships in relational tables

Write queries that filter and summarize data

Use keys and normalization to reduce inconsistency

Explain how transactions and indexes support reliable access

Video lesson

Learn with freeCodeCamp.org

Open on YouTube ↗

▶

Relational Database Design – Full Course

freeCodeCamp.org · About 6 hours

Covers: SQL, relational design, keys, ER models, normalization through BCNF, indexing, and query optimization

Course roadmap

Learn it in 5 modules

0 of 5 completed

MODULE 01Ready to learn

Relational tables and SQL

Give each table a clear purpose. Use primary keys to identify rows and foreign keys to represent references. Start queries with `SELECT` and `FROM`, then filter with `WHERE`; be explicit about columns rather than relying on every column.

View key idea and practice

Key idea

A relational database stores related data in tables of rows and columns. SQL is a language for defining, querying, and changing that data.

Worked example

An `Orders` table stores order ID, customer ID, order date, and status; a query selects open orders after a specified date.

Try it

Design a minimal table for courses and identify a primary key and two useful columns.

Suggested check

Use a stable course ID as the primary key and include a course name and category. Keep one course per row and make the ID unique and non-null.

MODULE 02Ready to learn

ER diagrams and relationships

Identify entities from the domain, then determine whether relationships are one-to-one, one-to-many, or many-to-many. A junction table represents a many-to-many relation while preserving each side's identity.

View key idea and practice

Key idea

An entity-relationship model names entities, attributes, and relationships before implementation. Cardinality describes how many records may participate in a relationship.

Worked example

Students and courses are many-to-many: one student joins many courses and one course includes many students, so an Enrollment table joins their IDs.

Try it

Model authors and books where a book may have multiple authors. Name the tables and key fields.

Suggested check

Use Authors and Books plus an AuthorBook junction table with foreign keys to each. Add a composite or surrogate primary key according to the product's requirements.

MODULE 03Ready to learn

Keys and normalization

First normal form requires atomic values rather than repeating groups. Second and third normal forms remove dependencies on part of a composite key and on non-key attributes, respectively. Normalization improves consistency; it is not a goal to split tables without reason.

View key idea and practice

Key idea

A functional dependency says one attribute determines another. Normalization organizes data to reduce update anomalies while maintaining meaningful dependencies.

Worked example

If customer address is copied into every order row, an address update can leave old copies behind; store it once in a customer table when that matches the business rule.

Try it

A table stores comma-separated course IDs in one student row. Identify the design issue and suggest a relational repair.

Suggested check

The repeated values are not atomic. Create an Enrollment table with one student-course pair per row, referencing Student and Course.

MODULE 04Ready to learn

Queries, joins, and aggregation

Use explicit join conditions to avoid accidental row multiplication. Filter rows with `WHERE` before grouping and filter groups with `HAVING`. Understand whether a left join should preserve unmatched rows.

View key idea and practice

Key idea

A join combines rows related by a key. An aggregate computes a summary such as count, sum, or average over a group of rows.

Worked example

A sales report joins orders to customers, groups by customer, and sums order totals to show each customer's spending.

Try it

Write the steps for listing each course and its enrollment count, including courses with zero students.

Suggested check

Start from Course, left join Enrollment on course ID, group by the course ID and name, and count enrollment identifiers so unmatched courses show zero.

MODULE 05Ready to learn

Transactions and indexes

Use transactions when multiple changes must succeed or fail together. Add indexes for frequent filters, joins, or ordering after measuring the workload; unnecessary indexes consume storage and slow writes.

View key idea and practice

Key idea

A transaction groups operations into a unit that should satisfy ACID properties: atomicity, consistency, isolation, and durability. An index is an auxiliary structure that speeds selected lookups at storage and write cost.

Worked example

A bank transfer debits one account and credits another in one transaction so a failure cannot leave only one side updated.

Try it

Choose a likely index for a frequent query filtering enrollments by course ID and explain a trade-off.

Suggested check

An index beginning with course ID can help that filter. It consumes space and adds work on insert, update, and delete, so confirm it matches real query patterns.

Take it further