Final Project: Build a Complete SQL Database System

# CHAPTER 30

Final Project: Build a Complete SQL Database System #

1. Introduction #

2. Project Requirements #

1. 1. DDL Architecture: Create 3 normalized tables with perfect Data Types.

1. 2. Data Integrity: Implement Primary Keys, Foreign Keys, UNIQUE, and NOT NULL constraints.

1. 3. Performance Optimization: Create an explicit B-Tree Index.

1. 4. DML Population: Insert dummy data into all tables.

1. 5. Advanced Querying: Write a complex JOIN query utilizing Aggregate Functions (GROUP BY).

1. 6. Security (Optional): Encapsulate a complex query into a VIEW.

3. Step 1: DDL Architecture & Constraints (Chapters 4, 5, 14, 15) #

CREATE DATABASE university_capstone;
USE university_capstone;

-- Table 1: Students
CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    registration_date DATE DEFAULT CURRENT_DATE
);

-- Table 2: Courses
CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    credits INT NOT NULL CHECK (credits > 0)
);

-- Table 3: Enrollments (The N:M Pivot Table)
CREATE TABLE enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    course_id INT,
    grade DECIMAL(3,2), -- e.g., 3.85 GPA
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE
);

4. Step 2: Performance Optimization (Chapter 20) #

-- Create a B-Tree Index to accelerate WHERE last_name = ? searches
CREATE INDEX idx_student_lastname ON students(last_name);

5. Step 3: DML Data Population (Chapter 6) #

-- Insert Students
INSERT INTO students (first_name, last_name, email) VALUES 
('Alice', 'Smith', 'alice@uni.edu'),
('Bob', 'Johnson', 'bob@uni.edu'),
('Charlie', 'Brown', 'charlie@uni.edu');

-- Insert Courses
INSERT INTO courses (course_name, credits) VALUES 
('Database Architecture', 4),
('Web Development', 3);

-- Insert Enrollments (Alice takes both, Bob takes Web Dev, Charlie takes DB)
INSERT INTO enrollments (student_id, course_id, grade) VALUES 
(1, 1, 4.00),
(1, 2, 3.80),
(2, 2, 3.20),
(3, 1, 3.90);

6. Step 4: Advanced Analytical Query (Chapters 12, 13, 16) #

SELECT 
    c.course_name, 
    COUNT(e.student_id) AS 'Total Students Enrolled',
    ROUND(AVG(e.grade), 2) AS 'Average Course Grade'
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.course_id, c.course_name
ORDER BY AVG(e.grade) DESC;

7. Step 5: Encapsulation (Chapter 19) #

CREATE VIEW vw_course_performance AS
SELECT 
    c.course_name, 
    COUNT(e.student_id) AS 'Total Students Enrolled',
    ROUND(AVG(e.grade), 2) AS 'Average Course Grade'
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.course_id, c.course_name;

-- Now the Dean simply runs:
SELECT * FROM vw_course_performance;

8. Final Review #

• You defined strict, unbreakable architectural boundaries.

• You linked disparate entities together flawlessly.

• You optimized for massive hardware scale.

• You mathematically aggregated distinct data streams into unified, executive-level business intelligence.

You are no longer a beginner. You are a highly capable Database Developer.

9. Conclusion and Next Steps #

1. 1. Master a Backend Language: Pair your SQL skills with PHP, Node.js, Python, or Java to build the APIs that interface with your databases.

1. 2. Explore ORMs: Learn how modern frameworks (like Laravel Eloquent or Prisma) interact with SQL databases using Object-Relational Mapping.

1. 3. Learn Advanced Engine Features: Dive deep into the specific, advanced features of PostgreSQL (like JSONB processing) or Microsoft SQL Server (T-SQL programming).

Thank you for choosing this platform for your database journey. Keep building, keep optimizing, and welcome to the world of Relational Data.