Building a Full Database App
Capstone lesson: combine everything you've learned — schema design, SQL queries, transactions, and PHP — to architect a complete student grade tracker application from scratch.
Grade Tracker Simulator
Interact with a simulated grade tracker. Add students, enroll them in courses, record grades, and generate reports — all in-browser.
The Capstone Project: Grade Tracker
A grade tracker app is the perfect capstone because it requires every concept from this course: a normalized schema with multiple related tables, SELECT with JOINs for reports, aggregate functions for averages, INSERT/UPDATE/DELETE for CRUD operations, transactions for safe grade recording, and PHP PDO for the web interface.
Browser → HTML form → Apache/PHP → PDO → MySQL → PHP builds HTML → Browser. Each layer has a clear responsibility: MySQL stores data, PHP contains business logic, HTML/CSS presents the interface.
Step 1: Normalized Schema
Design the schema before writing a single line of PHP. The grade tracker needs: students (id, name, email, grade_level), courses (id, title, credits, instructor_id), instructors (id, name, department), enrollments (student_id, course_id, grade, enrolled_at). This is 3NF — every fact is stored exactly once.
Step 2: CRUD Operations
Each table needs four operations: Create (INSERT), Read (SELECT), Update (UPDATE), Delete (DELETE). Build a separate PHP function for each — e.g., getStudentById($pdo, $id), createStudent($pdo, $data). Keep SQL inside functions; controllers call functions, not raw SQL.
Step 3: JOIN Queries for Reports
The most valuable queries in a grade tracker involve JOINs. A class roster needs students JOIN enrollments JOIN courses. A transcript needs all a student's courses and grades. An instructor report needs AVG(grade) GROUP BY course. Write these as named functions returning arrays.
Step 4: Transactions for Grade Recording
Recording a grade might involve inserting an enrollment row and immediately updating a student's GPA in a summary table. Wrap these in a transaction so they either both succeed or both roll back. Never leave data half-updated.
Where to Go Next
- ORM frameworks — Laravel (PHP), Django ORM (Python), ActiveRecord (Ruby) abstract raw SQL
- NoSQL databases — MongoDB, Redis for non-relational data
- Database performance — Indexing, query EXPLAIN, connection pooling
- Cloud databases — Amazon RDS, Google Cloud SQL, PlanetScale
- REST API design — Build a JSON API on top of your PHP + MySQL backend
Check Your Understanding
1. In a grade tracker, the enrollments table has columns student_id, course_id, and grade. What type of table is enrollments?
2. You need to simultaneously INSERT a new enrollment row AND UPDATE a student's GPA. What SQL feature ensures both operations succeed or both are undone?
3. To get a class roster showing each student's name alongside their course title and grade, which SQL operation do you need?
4. Which PHP practice is MOST important for preventing SQL injection in a web application?