Lesson 1, Topic 1

In Progress

Lesson 1.4 – Unit 1 Project – SchoolHub ERD and T-SQL Schema

Lesson 1.4 – Unit Capstone: SchoolHub ERD & T-SQL Schema

Capstone Objectives

This lesson is the synthesis of Unit 1. By the end you will have:

Designed and drawn an Entity-Relationship Diagram (ERD) for SchoolHub with four entities and the relationships between them.
Built the SchoolHub schema in T-SQL with four tables, IDENTITY primary keys, and correct foreign key constraints.
Populated the database with enough sample data to support every later unit.
Written a 4-table INNER JOIN query that returns one combined row per student-class enrollment.
Submitted a working, normalized schema you can use as a portfolio piece and as the practice database for Units 2-10.

🏗️ What You're Building

This is the project that ties Unit 1 together: SchoolHub, a working four-table school database in SQL Server. Everything you've learned — flat vs. hierarchical vs. relational (Lesson 1.1), tables and keys (1.2), normalization to 3NF (1.3) — comes together here.

The deliverable is a single .sql file that, when run from top to bottom in the Knowledge Pillars Coding Lab, creates and populates the entire SchoolHub database from scratch. It should include:

An ER diagram sketch (paper or digital — submit a photo or screenshot).
Four CREATE TABLE statements with appropriate data types, IDENTITY PKs, and FOREIGN KEY constraints.
At least 8 students, 4 teachers, 6 classes, and 15 enrollments.
A 4-table INNER JOIN query that demonstrates everything connects.
Comments throughout so a reviewer can follow your thinking.

🎓 Why this matters: The schema you build today is the practice database for the entire rest of the course. Units 2 and 3 query it. Unit 4 uses subqueries on it. Unit 5 ranks within it. Units 6 and 7 extend it. Build it carefully now and your future self will thank you for the next 50+ hours.

📐 Step 1 — Sketch the ERD

Before writing a single line of T-SQL, sketch the ER diagram. ERDs are how database designers think on paper. They make the structure obvious before any syntax can get in the way.

The SchoolHub entities

You have four entities to model:

Students — the people learning.
Teachers — the people teaching.
Classes — the courses offered.
Enrollments — which student is in which class (the link).

The relationships

A teacher can teach many classes, but each class has exactly one teacher. → One-to-many from Teachers to Classes.
A student can be enrolled in many classes, and a class can have many students. → Many-to-many, resolved by the Enrollments junction table.

Your ERD should look something like this:

                ┌─────────────┐
                │  Teachers   │
                ├─────────────┤
                │ TeacherID PK│
                │ FirstName   │
                │ LastName    │
                │ Subject     │
                └──────┬──────┘
                       │ 1
                       │
                       │ ∞ (teaches many)
                ┌──────▼──────┐                 ┌───────────────┐
                │   Classes   │                 │   Students    │
                ├─────────────┤                 ├───────────────┤
                │ ClassID PK  │                 │ StudentID PK  │
                │ ClassName   │                 │ FirstName     │
                │ TeacherID FK│                 │ LastName      │
                └──────┬──────┘                 │ GradeLevel    │
                       │ 1                      └───────┬───────┘
                       │                                │ 1
                       │ ∞                              │
                       │                                │ ∞
                       └────────┬───────────────────────┘
                                │
                         ┌──────▼──────────┐
                         │  Enrollments    │
                         ├─────────────────┤
                         │ StudentID PK,FK │   ← composite PK
                         │ ClassID   PK,FK │
                         │ Grade           │
                         └─────────────────┘

📝 Submission: draw this diagram by hand on paper or in any digital tool (Lucidchart, Excalidraw, even PowerPoint — Microsoft tools work great for SQL Server projects). Save it as a PNG or PDF and submit it alongside your .sql file.

🛠️ Step 2 — Build the T-SQL Schema

Now translate the ERD into actual T-SQL. Open the Coding Lab and create lesson1_4.sql. Type out each CREATE TABLE statement carefully — don't copy-paste.

lesson1_4.sql — header comments

/* ============================================
   SchoolHub Database — Unit 1 Capstone
   Author: (Your Name Here)
   Course: T-SQL Coding Specialist
   Date:   (Today's Date)
 
   This script creates and populates the SchoolHub
   database. It contains four normalized tables in
   3NF: Students, Teachers, Classes, Enrollments.
   ============================================ */
GO

Table order matters

Because foreign keys point from child tables to parent tables, you must create the parents first. The build order is:

Students and Teachers (no foreign keys — pure parent tables).
Classes (foreign key to Teachers).
Enrollments (foreign keys to both Students and Classes).

lesson1_4.sql — the four tables

-- 1) Students -----------------------------------------
CREATE TABLE Students (
    StudentID  INT          IDENTITY(1,1) PRIMARY KEY,
    FirstName  NVARCHAR(50) NOT NULL,
    LastName   NVARCHAR(50) NOT NULL,
    GradeLevel INT          NOT NULL
);
GO
 
-- 2) Teachers -----------------------------------------
CREATE TABLE Teachers (
    TeacherID INT          IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName  NVARCHAR(50) NOT NULL,
    Subject   NVARCHAR(50) NULL
);
GO
 
-- 3) Classes (FK to Teachers) -------------------------
CREATE TABLE Classes (
    ClassID   INT           IDENTITY(1,1) PRIMARY KEY,
    ClassName NVARCHAR(100) NOT NULL,
    TeacherID INT           NOT NULL,
    FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID)
);
GO
 
-- 4) Enrollments (junction; composite PK) -------------
CREATE TABLE Enrollments (
    StudentID INT         NOT NULL,
    ClassID   INT         NOT NULL,
    Grade     NVARCHAR(2) NULL,
    PRIMARY KEY (StudentID, ClassID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (ClassID)   REFERENCES Classes(ClassID)
);
GO

📥 Step 3 — Insert Sample Data

Add at least 8 students, 4 teachers, 6 classes, and 15 enrollments. Make the data plausible — give some students more classes than others, and assign each teacher to a couple of classes. You're free to use the data below or invent your own.

lesson1_4.sql — sample data (feel free to customize)

-- Teachers (4 rows) — TeacherID 1..4 will be auto-assigned
INSERT INTO Teachers (FirstName, LastName, Subject) VALUES
    ('John',   'Smith',   'Math'),
    ('Maria',  'Lee',     'History'),
    ('David',  'Brown',   'Science'),
    ('Sarah',  'Khan',    'English');
GO
 
-- Students (8 rows)
INSERT INTO Students (FirstName, LastName, GradeLevel) VALUES
    ('Alice',   'Wong',    10),
    ('Bob',     'Garcia',  10),
    ('Carol',   'Patel',   11),
    ('Daniel',  'Nguyen',  11),
    ('Eva',     'Martin',  9),
    ('Frank',   'Ahmed',   9),
    ('Grace',   'Cohen',   12),
    ('Henry',   'Diaz',    12);
GO
 
-- Classes (6 rows) — each links to a TeacherID 1..4
INSERT INTO Classes (ClassName, TeacherID) VALUES
    ('Algebra I',     1),  -- ClassID 1, Smith
    ('Algebra II',    1),  -- ClassID 2, Smith
    ('World History', 2),  -- ClassID 3, Lee
    ('Biology',       3),  -- ClassID 4, Brown
    ('Chemistry',     3),  -- ClassID 5, Brown
    ('English Lit',   4);  -- ClassID 6, Khan
GO
 
-- Enrollments (15+ rows) — mix grades and missing grades (NULL allowed)
INSERT INTO Enrollments (StudentID, ClassID, Grade) VALUES
    (1, 1, 'A'),  (1, 3, 'B'),  (1, 6, 'A'),
    (2, 1, 'C'),  (2, 4, 'B'),
    (3, 2, 'A'),  (3, 3, 'A'),  (3, 5, 'B'),
    (4, 2, 'B'),  (4, 5, NULL),
    (5, 1, 'B'),  (5, 4, 'A'),
    (6, 1, 'C'),  (6, 6, 'B'),
    (7, 2, 'A'),  (7, 3, 'A'),  (7, 5, 'A'),
    (8, 3, 'B'),  (8, 6, 'A');
GO

⚠️ Insert order matters: Teachers and Students must exist before you insert into Classes and Enrollments, or SQL Server will reject the inserts with a foreign key violation. The order shown above is correct — follow it.

🔍 Step 4 — Prove It All Connects

The capstone test: a single query that joins all four tables and returns one row per enrollment with the student's name, the class name, the teacher's name, and the grade. If this works, your schema is correctly designed and populated.

lesson1_4.sql — the 4-table JOIN

-- The full SchoolHub enrollment roster
SELECT
    s.FirstName + ' ' + s.LastName   AS Student,
    s.GradeLevel,
    c.ClassName,
    t.FirstName + ' ' + t.LastName   AS Teacher,
    e.Grade
FROM Enrollments e
INNER JOIN Students s ON e.StudentID = s.StudentID
INNER JOIN Classes  c ON e.ClassID   = c.ClassID
INNER JOIN Teachers t ON c.TeacherID = t.TeacherID
ORDER BY s.GradeLevel, Student, c.ClassName;

Expected Output (first several rows)

🎉 If you see 19 rows like this — congratulations. You've successfully built SchoolHub. This is the database every future unit will use as its practice playground.

⭐ Optional Stretch Goals

If you finish early and want to push your skills further, try one or more of these. They're not required for full credit, but they're great practice:

Add a DateOfBirth column of type DATE to Students and populate it. Then write a query that returns student name and age.
Add a Semester column to Classes (e.g., NVARCHAR(20) like 'Fall 2024'). Populate it. Then write a query showing how many classes run each semester.
Add a CHECK constraint to Students.GradeLevel so it only accepts values 9 through 12. Try inserting an invalid grade — what error does SQL Server give?
Build a query that lists each teacher and how many students they teach (use COUNT(DISTINCT e.StudentID)). This is a preview of what's coming in Unit 3.

📦 Submission Instructions

To submit your Unit 1 capstone, include all three of the following:

Your ERD sketch (PNG, PDF, or photo) showing all four tables and their relationships, with PK and FK labels.
Your .sql file, named lesson1_4_(FirstName_LastName).sql. It must run cleanly from top to bottom in the Coding Lab.
A short reflection (3-5 sentences in a text file or at the bottom of your SQL file as a comment): What was easiest? What tripped you up? What would you do differently next time?

⚠️ Common reasons capstones fail:

Tables created in the wrong order, so FK constraints fail.
Forgetting NOT NULL on columns that need it.
Using VARCHAR instead of NVARCHAR for names (international students!).
Forgetting the composite PK on Enrollments, allowing a student to be "enrolled" in the same class twice.
No comments in the SQL file — the script works but a reviewer can't tell what's going on.

📋 Grading Rubric (100 pts)

Criterion	Excellent (full)	Proficient (partial)	Needs Work (low)	Pts
ERD Diagram	All 4 entities, all relationships, all PKs/FKs labeled clearly.	Missing 1 relationship or some labels.	Significant pieces missing.	15
Tables Built	All 4 tables with correct columns, types, `IDENTITY` PKs.	Tables exist with minor type errors.	Missing tables or major type mistakes.	25
Constraints	All FKs declared; `NOT NULL` used appropriately; composite PK on `Enrollments`.	Some FKs missing or composite PK omitted.	FK constraints largely absent.	20
Sample Data	8+ students, 4+ teachers, 6+ classes, 15+ enrollments — all valid.	Met minimums but some data feels arbitrary.	Below minimum counts or invalid FK refs.	15
4-Table JOIN	Query runs and returns correct combined rows ordered sensibly.	JOIN works but with minor issues (missing column, wrong order).	Query doesn't run or returns wrong data.	15
Code Quality	Comments throughout; consistent style; clear naming; `GO` separators.	Some comments missing or inconsistent style.	No comments; messy formatting.	10

✅ Self-Check Before Submitting

My ERD shows all 4 entities, their PKs, FKs, and relationship cardinalities.

My script creates Teachers and Students first, then Classes, then Enrollments.

Every table has an IDENTITY primary key (or composite PK for the junction).

Every foreign key is declared with FOREIGN KEY ... REFERENCES.

All text columns use NVARCHAR with sensible lengths.

I have at least 8 students, 4 teachers, 6 classes, and 15 enrollments.

My 4-table INNER JOIN query runs and returns the expected combined data.

My script has comments at the top and at each major section.

The whole script runs from top to bottom in the Coding Lab with no errors.

I saved the file as lesson1_4_(FirstName_LastName).sql and have my ERD ready to attach.

0 of 10 complete

🎤 Reflection Questions

Reflect on what you built — answer at least three:

If your school wanted to add attendance tracking, what new table(s) would you add to SchoolHub? What would the keys look like?
Which part of designing the schema was hardest — the ERD, the data types, or the foreign keys? Why?
If you had to explain the difference between a one-to-many and many-to-many relationship to a friend in one sentence, what would you say?
How does the composite primary key on Enrollments prevent duplicate enrollments? What would happen without it?
What's one thing you'd change about the SchoolHub schema if you were designing it for a real school district?

➡️ What's Next

You've finished Unit 1 — the foundation. From here, every unit gets more interesting and more powerful because you have a real schema to work with.

Unit 2: Filtering, Patterns & Conditional Logic will teach you how to actually ask questions of SchoolHub. You'll write WHERE clauses to find specific students, use LIKE for pattern matching, and use CASE to categorize data.

Keep your lesson1_4.sql file safe — you'll re-run it at the start of every future unit to reset SchoolHub to a known state.

🎓 Well done. You designed a database, built it in SQL Server, populated it, and proved it works. That's exactly what a working database developer does every day. The rest of the course is about doing more with this foundation.