Project 1: Student Information System Database0%

Project 2: Retail Store Inventory Management System

Project 1: Student Information System Database

Beginner15 min read•Updated: 2026-09-12

Project 1: Student Information System Database Architecture

Welcome to your first practical capstone project! In this project, you will step into the shoes of a Lead Database Architect tasked with designing, implementing, and querying a production-ready Student Information System (SIS) for MSK Tech Academy.

This project integrates every concept you have learned across Level 1: DDL database and table creation, constraints, foreign keys, DML data population, multi-column sorting, scalar functions, and aggregation with GROUP BY and HAVING.

1. Project Requirements & Architecture

Our educational institute requires tracking:

1
Departments / Branches (e.g., Computer Science, Data Science, Cyber Security).
2
Students (Personal details, enrollment dates, active statuses).
3
Courses & Enrollments (Course fees, grades, student-course mappings).

Visual Architecture Blueprint

+--------------------+          +--------------------+
   |    departments     |          |      students      |
   +--------------------+          +--------------------+
   | dept_id (PK)       | <------- | student_id (PK)    |
   | dept_name          |    1:N   | full_name          |
   +--------------------+          | email (UQ)         |
                                   | dept_id (FK)       |
                                   | fee_balance        |
                                   +--------------------+

2. Step 1: Schema Creation (DDL)

Copy and execute this script in MySQL Workbench or CLI:

1-- Create and switch to project database
2CREATE DATABASE IF NOT EXISTS msk_student_system
3    CHARACTER SET utf8mb4
4    COLLATE utf8mb4_0900_ai_ci;
5 
6USE msk_student_system;
7 
8-- Table 1: Academic Departments
9CREATE TABLE IF NOT EXISTS departments (
10    dept_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
11    dept_name VARCHAR(100) NOT NULL UNIQUE,
12    head_of_dept VARCHAR(100) NOT NULL
13) ENGINE = InnoDB;
14 
15-- Table 2: Student Directory
16CREATE TABLE IF NOT EXISTS students (
17    student_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
18    first_name VARCHAR(50) NOT NULL,
19    last_name VARCHAR(50) NOT NULL,
20    email VARCHAR(150) NOT NULL,
21    date_of_birth DATE NOT NULL,
22    dept_id INT UNSIGNED NOT NULL,
23    fee_balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
24    academic_status ENUM('Active', 'Graduated', 'Suspended') NOT NULL DEFAULT 'Active',
25    enrolled_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
26    
27    CONSTRAINT uq_student_email UNIQUE (email),
28    CONSTRAINT chk_positive_balance CHECK (fee_balance >= 0.00),
29    CONSTRAINT fk_students_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
30        ON DELETE RESTRICT
31        ON UPDATE CASCADE
32) ENGINE = InnoDB;

3. Step 2: Data Population (DML)

1-- Populate Departments:
2INSERT INTO departments (dept_name, head_of_dept) VALUES
3    ('Computer Science', 'Dr. Ramesh Kulkarni'),
4    ('Data Science & AI', 'Dr. Sunita Sharma'),
5    ('Full Stack Engineering', 'Prof. Amit Roy');
6 
7-- Batch Insert Student Records:
8INSERT INTO students (first_name, last_name, email, date_of_birth, dept_id, fee_balance, academic_status, enrolled_at) VALUES
9    ('Aarav', 'Sharma', 'aarav.s@msk.in', '2004-05-12', 1, 0.00, 'Active', '2025-08-01 10:00:00'),
10    ('Diya', 'Patel', 'diya.p@msk.in', '2005-02-18', 2, 12500.00, 'Active', '2025-08-05 11:30:00'),
11    ('Rohan', 'Verma', 'rohan.v@msk.in', '2003-11-25', 1, 4500.00, 'Active', '2025-08-01 14:00:00'),
12    ('Ananya', 'Gupta', 'ananya.g@msk.in', '2004-09-03', 2, 0.00, 'Active', '2025-08-10 09:15:00'),
13    ('Kabir', 'Mehta', 'kabir.m@msk.in', '2002-07-14', 3, 22000.00, 'Active', '2024-07-15 10:00:00'),
14    ('Sneha', 'Deshmukh', 'sneha.d@msk.in', '2004-12-30', 1, 8000.00, 'Suspended', '2025-08-02 12:00:00'),
15    ('Ishaan', 'Nair', 'ishaan.n@msk.in', '2005-04-05', 3, 0.00, 'Graduated', '2024-01-10 10:00:00');

4. Step 3: Analytical Business Queries (DQL)

Now, let us write the queries that the academy's executive directors and administrative staff use daily:

1-- Query 1: Outstanding Fee Collection Report
2-- Find all active students who owe fees, ordered from highest balance to lowest:
3SELECT 
4    student_id,
5    CONCAT(first_name, ' ', last_name) AS student_name,
6    email,
7    fee_balance
8FROM students
9WHERE academic_status = 'Active' 
10  AND fee_balance > 0.00
11ORDER BY fee_balance DESC;
12 
13-- Query 2: Department Enrollment & Financial Breakdown
14-- Compute student count and total pending fee balance per department:
15SELECT 
16    dept_id,
17    COUNT(*) AS total_students,
18    SUM(fee_balance) AS total_pending_fees,
19    AVG(fee_balance) AS average_pending_fee,
20    MAX(fee_balance) AS highest_individual_debt
21FROM students
22GROUP BY dept_id
23HAVING COUNT(*) >= 2
24ORDER BY total_pending_fees DESC;
25 
26-- Query 3: Student Age Calculation & Verification
27-- Calculate the current age of each student in years:
28SELECT 
29    student_id,
30    CONCAT(first_name, ' ', last_name) AS full_name,
31    date_of_birth,
32    TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS calculated_age
33FROM students
34ORDER BY calculated_age ASC;

5. Capstone Review & Takeaways

You have designed a normalized database from scratch! It features:

Referential integrity linking students to departments.
Automatic default values and check constraints enforcing positive fee balances.
Optimized queries that calculate summaries and filter aggregates cleanly.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In our student system schema, why is ON DELETE RESTRICT specified on the foreign key linking students to departments?

Question 2

Which constraint guarantees that no two students can register with the exact same email address?

Question 3

What does TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) calculate?

Question 4

What will happen if an administrative script attempts to insert a student with a negative fee_balance = -500.00?

Question 5

In Query 2, what does HAVING COUNT(*) >= 2 accomplish?

Next Lesson

Project 2: Retail Store Inventory Management System

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Exporting & Importing CSV Data	Project 2: Retail Store Inventory Management System

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Project 2: Retail Store Inventory Management System

Project 1: Student Information System Database

Beginner15 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Project 1: Student Information System Database Architecture

1. Project Requirements & Architecture

Our educational institute requires tracking:

1
Departments / Branches (e.g., Computer Science, Data Science, Cyber Security).
2
Students (Personal details, enrollment dates, active statuses).
3
Courses & Enrollments (Course fees, grades, student-course mappings).

Visual Architecture Blueprint

+--------------------+          +--------------------+
   |    departments     |          |      students      |
   +--------------------+          +--------------------+
   | dept_id (PK)       | <------- | student_id (PK)    |
   | dept_name          |    1:N   | full_name          |
   +--------------------+          | email (UQ)         |
                                   | dept_id (FK)       |
                                   | fee_balance        |
                                   +--------------------+

2. Step 1: Schema Creation (DDL)

Copy and execute this script in MySQL Workbench or CLI:

1-- Create and switch to project database
2CREATE DATABASE IF NOT EXISTS msk_student_system
3    CHARACTER SET utf8mb4
4    COLLATE utf8mb4_0900_ai_ci;
5 
6USE msk_student_system;
7 
8-- Table 1: Academic Departments
9CREATE TABLE IF NOT EXISTS departments (
10    dept_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
11    dept_name VARCHAR(100) NOT NULL UNIQUE,
12    head_of_dept VARCHAR(100) NOT NULL
13) ENGINE = InnoDB;
14 
15-- Table 2: Student Directory
16CREATE TABLE IF NOT EXISTS students (
17    student_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
18    first_name VARCHAR(50) NOT NULL,
19    last_name VARCHAR(50) NOT NULL,
20    email VARCHAR(150) NOT NULL,
21    date_of_birth DATE NOT NULL,
22    dept_id INT UNSIGNED NOT NULL,
23    fee_balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
24    academic_status ENUM('Active', 'Graduated', 'Suspended') NOT NULL DEFAULT 'Active',
25    enrolled_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
26    
27    CONSTRAINT uq_student_email UNIQUE (email),
28    CONSTRAINT chk_positive_balance CHECK (fee_balance >= 0.00),
29    CONSTRAINT fk_students_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
30        ON DELETE RESTRICT
31        ON UPDATE CASCADE
32) ENGINE = InnoDB;

3. Step 2: Data Population (DML)

1-- Populate Departments:
2INSERT INTO departments (dept_name, head_of_dept) VALUES
3    ('Computer Science', 'Dr. Ramesh Kulkarni'),
4    ('Data Science & AI', 'Dr. Sunita Sharma'),
5    ('Full Stack Engineering', 'Prof. Amit Roy');
6 
7-- Batch Insert Student Records:
8INSERT INTO students (first_name, last_name, email, date_of_birth, dept_id, fee_balance, academic_status, enrolled_at) VALUES
9    ('Aarav', 'Sharma', 'aarav.s@msk.in', '2004-05-12', 1, 0.00, 'Active', '2025-08-01 10:00:00'),
10    ('Diya', 'Patel', 'diya.p@msk.in', '2005-02-18', 2, 12500.00, 'Active', '2025-08-05 11:30:00'),
11    ('Rohan', 'Verma', 'rohan.v@msk.in', '2003-11-25', 1, 4500.00, 'Active', '2025-08-01 14:00:00'),
12    ('Ananya', 'Gupta', 'ananya.g@msk.in', '2004-09-03', 2, 0.00, 'Active', '2025-08-10 09:15:00'),
13    ('Kabir', 'Mehta', 'kabir.m@msk.in', '2002-07-14', 3, 22000.00, 'Active', '2024-07-15 10:00:00'),
14    ('Sneha', 'Deshmukh', 'sneha.d@msk.in', '2004-12-30', 1, 8000.00, 'Suspended', '2025-08-02 12:00:00'),
15    ('Ishaan', 'Nair', 'ishaan.n@msk.in', '2005-04-05', 3, 0.00, 'Graduated', '2024-01-10 10:00:00');

4. Step 3: Analytical Business Queries (DQL)

Now, let us write the queries that the academy's executive directors and administrative staff use daily:

1-- Query 1: Outstanding Fee Collection Report
2-- Find all active students who owe fees, ordered from highest balance to lowest:
3SELECT 
4    student_id,
5    CONCAT(first_name, ' ', last_name) AS student_name,
6    email,
7    fee_balance
8FROM students
9WHERE academic_status = 'Active' 
10  AND fee_balance > 0.00
11ORDER BY fee_balance DESC;
12 
13-- Query 2: Department Enrollment & Financial Breakdown
14-- Compute student count and total pending fee balance per department:
15SELECT 
16    dept_id,
17    COUNT(*) AS total_students,
18    SUM(fee_balance) AS total_pending_fees,
19    AVG(fee_balance) AS average_pending_fee,
20    MAX(fee_balance) AS highest_individual_debt
21FROM students
22GROUP BY dept_id
23HAVING COUNT(*) >= 2
24ORDER BY total_pending_fees DESC;
25 
26-- Query 3: Student Age Calculation & Verification
27-- Calculate the current age of each student in years:
28SELECT 
29    student_id,
30    CONCAT(first_name, ' ', last_name) AS full_name,
31    date_of_birth,
32    TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS calculated_age
33FROM students
34ORDER BY calculated_age ASC;

5. Capstone Review & Takeaways

You have designed a normalized database from scratch! It features:

Referential integrity linking students to departments.
Automatic default values and check constraints enforcing positive fee balances.
Optimized queries that calculate summaries and filter aggregates cleanly.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In our student system schema, why is ON DELETE RESTRICT specified on the foreign key linking students to departments?

Question 2

Which constraint guarantees that no two students can register with the exact same email address?

Question 3

What does TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) calculate?

Question 4

What will happen if an administrative script attempts to insert a student with a negative fee_balance = -500.00?

Question 5

In Query 2, what does HAVING COUNT(*) >= 2 accomplish?

Next Lesson

Project 2: Retail Store Inventory Management System

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Exporting & Importing CSV Data	Project 2: Retail Store Inventory Management System

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next