Relational Database Course

Welcome! This repository contains a collaborative, hands-on course for learning relational databases with MySQL. Each lesson is paired with a homework assignment, and the table below serves as a roadmap linking every lesson to its corresponding homework. Students submit their solutions as pull requests.

Lesson Plan

Part	Lesson #	Topic	Lesson	Homework
Foundations	1	Intro to Relational Databases	Lesson	HW1
Foundations	2	Database Fundamentals	Lesson	HW2
Foundations	3	Relationships & Cardinality	Lesson	HW3
Foundations	4	Normalization	Lesson	HW4
Working with Data	5	Inserting, Updating, and Deleting Data	Lesson	HW5
Working with Data	6	SELECT Basics	Lesson	HW6
Working with Data	7	Joins	Lesson	HW7
Working with Data	8	Indexing & Performance	Lesson	HW8
Working with Data	9	Expressions & Functions	Lesson	HW9
Working with Data	10	Aggregation & Grouping	Lesson	HW10
Working with Data	11	Set Operations	Lesson	HW11
Working with Data	12	Subqueries	Lesson	HW12
Working with Data	13	Common Table Expressions	Lesson	HW13
Working with Data	14	Window Functions	Lesson	HW14
Working with Data	15	Views & Triggers	Lesson	HW15
Working with Data	16	Stored Procedures	Lesson	HW16
From SQL to APIs	17	REST API Foundations	coming soon	coming soon
From SQL to APIs	18	Validation & Error Handling	coming soon	coming soon
From SQL to APIs	19	Authentication & Authorization	coming soon	coming soon
From SQL to APIs	20	Testing & Documentation	coming soon	coming soon
From SQL to APIs	21	Reports, Pagination & Filtering	coming soon	coming soon
From SQL to APIs	22	Performance & Deployment	coming soon	coming soon
Capstone	23	Final Project	coming soon	coming soon

The API-focused lessons emphasize conceptual understanding. Step-by-step Hono implementations are provided as supplemental practice activities.

How This Works

• Lessons are in the lessons/ directory.
• Homework assignments are in homework/, one per lesson.
• Submit your homework in the submissions/ directory, following the instructions in each assignment.
• Each homework builds on the previous one, so complete them in order.

Prerequisites

• Docker Desktop (with WSL2 integration on Windows)
• Git
• MySQL client (optional, for direct DB access)
• curl (for API testing, included in most systems)
• (Optional) Postman for API testing

Quick Start

1. Clone this repository:

   git clone https://github.com/<your-org>/relational-db-course.git
   cd relational-db-course

2. Start the MySQL Docker container:

   docker compose -f docker/docker-compose.yml up -d

   This will start a MySQL 8 container with a persistent volume at ./docker/mysql-data.

3. Connect to MySQL:

   docker exec -it mysql-db mysql -uroot -psecret

   • Host: localhost
   • Port: 3306
   • User: root
   • Password: secret

4. Run a homework SQL script:

   ./scripts/run_hw.sh 1 <student-github-username>

   This will execute the SQL file for HW1 for the specified student.

5. Run all homework scripts in order:

   ./scripts/run_all.sh <student-github-username>

6. Clean the database (drops all tables):

   ./scripts/clean_db.sh

Submitting Homework

• For each assignment, create a folder under submissions/hwX/<your-github-username>/ and add:
  • solution.sql — your SQL script
  • explanation.md — a brief explanation of your approach and any assumptions
• Submit a pull request to this repository.

Docker & Database Details

• The MySQL data is stored in a Docker volume mapped to ./docker/mysql-data for persistence.
• The database is named course_db by default.
• You can reset the database at any time using the provided scripts.

Questions?

Open an issue or ask in the course chat!