School Database with Relational Lookups (Students/Classes)

A school database links students, classes, and teachers in related tables and answers questions with lookups. You build it in Excel and use Copilot and ChatGPT for the schema design and lookup formulas.

Table of Contents

• Project Overview
• Features
• Prerequisites
• Folder Structure
• Step-by-Step Development
• Complete Source Code
• Running the Project
• Testing
• Common Errors
• Improvements
• Challenge Exercises

Project Overview

• What it does: It stores students, classes, and teachers, then looks up a student's class and teacher from IDs.
• Where it is used: Schools that need a simple relational records system without a database server.
• What you learn: table design, XLOOKUP across tables, COUNTIF, and AI schema prompts.

Features

• Students, classes, and teachers tables
• Look up class and teacher by student ID
• Class size counts
• Validation on IDs
• AI-designed schema suggestion

Prerequisites

• Microsoft Excel (Microsoft 365)
• ChatGPT account
• Basic understanding of tables

Folder Structure

school-database.xlsx
│
├── Sheet: Students  (StudentID, Name, ClassID)
├── Sheet: Classes   (ClassID, ClassName, TeacherID)
├── Sheet: Teachers  (TeacherID, TeacherName)
└── Module: SchMacros (optional VBA)

• Students: who is enrolled.
• Classes: which class, taught by whom.
• Teachers: staff list.
• SchMacros: optional helpers.

Step-by-Step Development

Step 1 – Design the three tables

What are we building?

Three related tables sharing ClassID and TeacherID.

Why do we need it?

Relations let one teacher serve many classes without repeating names.

How do we build it?

Create each as a table (Stu, Cls, Tch).

No formula; design the columns first.

• What it does: sets the data model.
• Why needed: lookups depend on shared keys.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Design three related Excel tables for a school: students, classes, teachers, with the keys that link them."

Step 2 – Look up class and teacher

What are we building?

Formulas that return a student's class name and teacher name.

Why do we need it?

One ID should reveal the full path.

How do we build it?

=XLOOKUP(XLOOKUP(B1, Stu[StudentID], Stu[ClassID]), Cls[ClassID], Cls[ClassName])
=XLOOKUP(XLOOKUP(B1, Stu[StudentID], Stu[ClassID]), Cls[ClassID], Cls[TeacherID])

• What it does: first finds the student's ClassID, then looks up that class's name and teacher.
• Important line: nested XLOOKUP chains the two tables through ClassID.
• Why needed: answers "what class and who teaches this student?"

Use AI (Copilot & ChatGPT)

Ask Copilot:

"Write a nested XLOOKUP: from StudentID get ClassID, then from ClassID get ClassName."

Step 3 – Class size

What are we building?

A count of students per class.

Why do we need it?

Shows how full each class is.

How do we build it?

=COUNTIF(Stu[ClassID], A2)

• What it does: counts students where ClassID matches A2.
• Why needed: each class gets a size.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write COUNTIF that counts students where ClassID equals A2."

Complete Source Code

Lookup formulas

=XLOOKUP(XLOOKUP(B1, Stu[StudentID], Stu[ClassID]), Cls[ClassID], Cls[ClassName])
=XLOOKUP(XLOOKUP(B1, Stu[StudentID], Stu[ClassID]), Cls[ClassID], Cls[TeacherID])
=COUNTIF(Stu[ClassID], A2)

AI prompts

ChatGPT: "Design students, classes, teachers tables with keys."
Copilot: "Nested XLOOKUP StudentID to ClassID to ClassName."
ChatGPT: "COUNTIF students where ClassID equals A2."

Running the Project

1. Open school-database.xlsx.
2. Fill Students, Classes, Teachers.
3. On a search cell enter a StudentID; class and teacher appear.
4. Expected output: full student path from one ID.

Testing

Test Case 1

Input: Student S003 in Class C2; C2 taught by T1. Expected Output: ClassName for C2 and TeacherName for T1. Explanation: Nested XLOOKUP chains the keys.

Test Case 2

Input: Class C2 has 25 students. Expected Output: Size 25. Explanation: COUNTIF tallies the class.

Test Case 3

Input: Student ID not in list. Expected Output: #N/A (or "Not found" if wrapped). Explanation: XLOOKUP finds no match.

Common Errors

#N/A on lookup

Problem: Lookup fails. Reason: ClassID types differ (text vs number). Solution: Make both ID columns the same format.

Wrong teacher

Problem: Teacher mismatch. Reason: TeacherID typo in Classes. Solution: Fix the Classes table key.

Count too low

Problem: Fewer students than real. Reason: Blank ClassID cells. Solution: Ensure every student has a ClassID.

Improvements

• Add a subjects table
• Use Power Query to merge tables
• Validate IDs with dropdowns
• Ask ChatGPT for class-balance suggestions
• Export directory to PDF

Challenge Exercises

1. Add a formula that lists all students in a chosen class.
2. Write a lookup that returns the teacher's name directly from StudentID.
3. Use ChatGPT to write VBA that adds a student with class validation.
4. Build a chart of class sizes.
5. Let ChatGPT find classes over a set size limit.