Campus360 (University Management System Database)

Description: This project was created using SQL. In this project, I used various tools such as:

SQL: Query, Database Schema, Data Dictionary, CSV Files, SQL Scripts, Tables, Functions, ER Diagram, Relationships, AI-Generated Dataset Tableau: Data cleaning,. Dashboards, Data visualization, Graphs, Calculated filed Power Bi: Dashboards, Data visualization, Measures, Data modeling Python: Code, Library, Functions, Variables, Operations

Title:

Campus360 – University Management System Database

Project Overview:

Summery:

Campus360 is my personal portfolio brand and a comprehensive end-to-end data analytics case study designed to demonstrate how multiple analytical technologies can work together within a single integrated project. The project combines SQL Server, Power BI, Tableau, Python, and AI-assisted analytics, covering the analytical workflow from database design, data modeling, data cleaning, and SQL analysis to KPI development, advanced analytics, interactive dashboards, and data visualization. Rather than demonstrating each tool in isolation, Campus360 is designed to showcase how their capabilities can be connected throughout a real-world analytical workflow. The project applies a broad range of techniques and features commonly used by Data Analysts and Business Intelligence professionals to transform raw data into meaningful insights and support data-driven decision-making. Through this integrated approach, Campus360 demonstrates not only technical proficiency with individual analytical tools, but also the ability to connect data engineering, analytical thinking, business analysis, and data visualization into a complete and scalable analytics solution. Campus360, University Management System Database is a comprehensive end-to-end SQL portfolio project that simulates the operations of a modern Canadian university through a fully normalized relational database. The project has been developed to demonstrate professional skills in database design, data modeling, SQL development, business intelligence, and data analytics within a realistic higher education environment. Campus360 University is a fictional university located in Canada, created as a professional portfolio brand specifically for this project. Instead of relying on publicly available datasets, the entire database has been custom-designed and generated with the assistance of Artificial Intelligence to create a realistic, consistent, and business-oriented academic environment. The university consists of five academic faculties: -Faculty of Engineering -Faculty of Business -Faculty of Science -Faculty of Arts -Faculty of Health Sciences These faculties include academic departments in Computer Science, Data Science, Artificial Intelligence, Electrical Engineering, Mechanical Engineering, Civil Engineering, Accounting, Finance, Marketing, Mathematics, Statistics, and Psychology, reflecting the multidisciplinary structure of a modern Canadian university. The project follows an incremental development approach, where each stage expands the database while preserving normalization principles, referential integrity, and realistic business rules. This methodology mirrors how enterprise database systems are designed and implemented in real-world organizations. Artificial Intelligence plays a key role in generating realistic, interconnected, and business-oriented data. AI has been used to create meaningful entities, consistent relationships, and portfolio-quality datasets while maintaining logical integrity throughout the project. Campus360 is more than a SQL database project it is a complete case study that combines database engineering, analytical thinking, and data visualization into a single professional portfolio designed for GitHub, personal websites, and technical interviews. This project is intended as a complete portfolio demonstrating practical skills in: • Database Design • Data Modeling • SQL Server • Advanced SQL Queries • Relational Database Management • Business Intelligence • Data Analytics • Power BI • Tableau • Python Data Analysis

Database Overview:

The Campus360; University Management System Database is a fully normalized relational database designed to simulate the academic, administrative, and operational processes of a modern Canadian university. The database has been developed as a professional SQL portfolio project using realistic business rules and AI-assisted data generation. The primary objective of this database is to provide a comprehensive environment for practicing database design, SQL development, business intelligence, and data analytics while maintaining a structure similar to enterprise-level information systems.

ER Diagram

The Entity Relationship Diagram (ERD) illustrates the logical structure of the database and the relationships between all entities. It provides a clear visualization of how academic, administrative, and operational data are connected throughout the university management system.

Database Schema

The database follows a relational schema consisting of multiple interconnected entities representing students, academic programs, departments, instructors, courses, enrollments, examinations, financial records, library services, and other university operations. The schema has been designed to ensure scalability, consistency, and efficient querying while supporting future database expansion.

Tables

The Campus360 database contains 12 interconnected relational tables, organized into key university management and analytics areas:

- Academic Structure: Faculties, Departments, Programs
- Faculty Management: Professors
- Student Management: Students
- Course Management: Courses
- Enrollment Management: Enrollments
- Academic Performance: Grades
- Attendance Management: Attendance
- Financial Management: TuitionPayments
- Scholarship Management: Scholarships, StudentScholarships

The tables are connected through Primary Keys (PK) and Foreign Keys (FK) to create a structured relational model representing the core academic, administrative, and financial operations of a fictional Canadian university. The database supports relationships such as Faculty → Department → Program, Student → Enrollment → Course, Enrollment → Grade / Attendance, and Student ↔ Scholarship, enabling integrated analysis across multiple areas of university operations. The relational structure was designed to support data integrity, efficient SQL querying, business intelligence, KPI development, dashboard reporting, and advanced analytics across SQL Server, Power BI, Tableau, and Python.

Relationships

All database entities are connected through well-defined relationships that accurately represent real-world university operations. The database includes:

- One-to-One Relationships
- One-to-Many Relationships
- Many-to-Many Relationships (implemented using bridge tables)

These relationships ensure accurate data representation and eliminate unnecessary redundancy. Primary Keys & Foreign Keys Every table includes an appropriate Primary Key (PK) to uniquely identify each record. Relationships between tables are maintained using Foreign Keys (FK) to guarantee referential integrity and ensure consistent data across the entire database.

Database Normalization

The Campus360 database was designed in accordance with Third Normal Form (3NF) principles, with related business entities separated into logically structured and interconnected tables.

Normalization principles were applied to:

- reduce data redundancy
- minimize insert, update, and delete anomalies
- improve data consistency and integrity
- separate distinct business entities into dedicated tables
- establish structured relationships using Primary Keys and Foreign Keys
- simplify database maintenance and future expansion

For example, academic entities such as Faculties, Departments, Programs, Courses, and Professors are maintained separately rather than repeatedly storing their descriptive information within student or enrollment records. Similarly, the many-to-many relationship between Students and Scholarships is managed through the StudentScholarships junction table.

This relational design provides a structured and scalable foundation for SQL analysis, data modeling, business intelligence, and analytical reporting.

Data Dictionary

A complete Data Dictionary accompanies the database. The documentation includes:

- Table descriptions
- Column names
- Data types
- Primary Keys
- Foreign Keys
- Constraints
- Business definitions
- Validation rules

This documentation provides a clear understanding of every database object and supports future maintenance and development.

Database Statistics
- Database Name: Campus360 – University Management System Database
- Country: Canada (Fictional University)
- Database Type: Relational University Management & Analytics Database
- Database Platform: Microsoft SQL Server
- Core Tables: 12 interconnected relational tables
- Total Fields: Approximately 130+ structured fields
- Total Records: More than 39,000 records
- Students: 1,000 student profiles
- Professors: 60 faculty/instructor profiles
- Courses: 65 academic courses
- Enrollments: 3,000 course enrollment records
- Grades: 3,000 student grade records
- Attendance: 30,000 attendance records
- Tuition Payments: 2,000 financial transaction records
- Scholarships: 12 scholarship programs
- Faculties: 6
- Departments: 13
- Academic Programs: 13
- Database Structure: Fully relational model using Primary Keys, Foreign Keys, and defined table relationships
- Normalization: Designed using relational normalization principles up to Third Normal Form (3NF)
- Data Integrity: Referential integrity, relationship validation, duplicate prevention, and enrollment consistency rules implemented
- Main Analytical Areas: Academic performance, enrollment, attendance, student risk, tuition, scholarships, faculty structure, and predictive analytics
- Core Database Tables

Faculties, Departments, Programs, Professors, Students, Courses, Enrollments, Grades, Attendance, TuitionPayments, Scholarships, StudentScholarships

CSV Files

Each major Campus360 database entity is supported by a structured CSV dataset designed for database import, analytical modeling, data validation, and business intelligence workflows. The project includes CSV datasets for key university entities such as:

- Students
- Professors
- Courses
- Enrollments
- Grades
- Attendance
- Tuition Payments
- Scholarships
- Faculties
- Departments
- Academic Programs
- Student–Scholarship relationships

The datasets preserve the relational structure of Campus360 through identifiers such as: StudentID, ProfessorID, CourseID, EnrollmentID, FacultyID, DepartmentID, ProgramID, and ScholarshipID. This allows the CSV files to be imported individually while maintaining relationships between the different components of the university system.

CSV Dataset Scale
- 1,000 Students
- 60 Professors
- 65 Courses
- 3,000 Enrollments
- 3,000 Grades
- 30,000 Attendance Records
- 2,000 Tuition Payment Records
- 12 Scholarship Programs

Compatible Platforms

The Campus360 CSV datasets are compatible with:

- Microsoft SQL Server
- MySQL
- PostgreSQL
- Oracle Database
- SQLite
- Microsoft Excel
- Power BI
- Tableau
- Python / Pandas
Analytical Use

The CSV files can be used independently or combined through their relational keys for: Data Cleaning, ETL, SQL Analysis, Data Modeling, KPI Development, Dashboard Development , Statistical Analysis, Predictive Analytics, Machine Learning.

SQL Scripts

The project includes a complete collection of SQL scripts required to build and populate the database. These scripts include:

- Create Database
- Create Tables
- Primary Key Constraints
- Foreign Key Constraints
- Insert Data
- Views
- Stored Procedures
- User-Defined Functions
- Triggers
- Advanced SQL Queries

The SQL scripts are designed for Microsoft SQL Server and follow industry best practices for readability, maintainability, and performance.

AI-Generated Dataset

The entire dataset has been generated with the assistance of Artificial Intelligence to create realistic, consistent, and interconnected records. AI has been used to:

- generate realistic academic entities
- create meaningful relationships between records
- preserve referential integrity
- simulate real-world university operations
- produce portfolio-quality data for SQL development and business analytics

The result is a realistic academic database suitable for professional portfolio projects, business intelligence dashboards, SQL practice, and technical interviews.

Business Questions:

The Campus360 database was developed to answer key academic, operational, financial, and management questions using SQL-based analysis.

Student & Enrollment Analysis

How many students are enrolled in each faculty and department? Which faculty has the highest student population? How are students distributed by study mode and enrollment status? How many students are enrolled in each course? How do enrollment patterns vary across faculties and departments?

Academic Performance

What is the average, minimum, and maximum GPA in each department? Which departments have an average GPA above 3.0? Which students have a GPA above the university-wide average? What is the average student grade for each course? Which courses perform above the overall course average? What is the pass rate for each course and department? How do students rank by GPA within their departments? How do faculties rank based on average student GPA?

Attendance Analysis

What is the attendance rate for each course? How do attendance patterns vary across departments? Which students or courses have higher levels of absence? What is the relationship between student attendance and academic performance?

Tuition & Financial Analysis

How much tuition is due and how much has been collected? What is the university’s outstanding tuition balance? What is the tuition collection rate? How does tuition revenue vary by academic term? How does tuition performance vary across faculties? Which students have outstanding tuition balances?

Scholarship Analysis

How many students receive scholarships? How are scholarships distributed by scholarship type and department? What is the total and average value of scholarships awarded? Which departments receive the highest total scholarship funding? Management & KPI Analysis What are the university’s total student count and average GPA? What percentage of students are currently active? What are the university’s total tuition due, tuition collected, and outstanding balance? What is the overall tuition collection rate? Which faculties and departments demonstrate the strongest academic performance? Which areas require management attention based on enrollment, academic performance, attendance, or financial indicators?

Executive Decision-Support Goal

Ultimately, Campus360 is designed to answer one broader management question: How can university administrators use integrated student, academic, attendance, tuition, and scholarship data to monitor institutional performance and make better data-driven decisions?

Steps:

Database Architecture: Creating Database:

CREATE DATABASE Campus360;
GO
USE Campus360;
GO

Creating Students Table:

CREATE TABLE Students (

StudentID INT NOT NULL PRIMARY KEY,

StudentNumber VARCHAR(15) NOT NULL UNIQUE,

FirstName VARCHAR(50) NOT NULL,

LastName VARCHAR(50) NOT NULL,

Gender VARCHAR(15) NOT NULL,

DateOfBirth DATE NOT NULL,

Nationality VARCHAR(50) NOT NULL,

Email VARCHAR(100) NOT NULL UNIQUE,

Phone VARCHAR(20),

Address VARCHAR(150),

City VARCHAR(50) NOT NULL,

Province VARCHAR(50) NOT NULL,

PostalCode VARCHAR(10) NOT NULL,

Country VARCHAR(50) NOT NULL,

AdmissionDate DATE NOT NULL,

ExpectedGraduationDate DATE,

AdmissionType VARCHAR(20) NOT NULL,

EnrollmentStatus VARCHAR(20) NOT NULL,

StudyMode VARCHAR(20) NOT NULL,

FacultyID INT NOT NULL,

DepartmentID INT NOT NULL,

ProgramID INT NOT NULL,

AcademicLevel VARCHAR(20) NOT NULL,

CurrentSemester TINYINT NOT NULL,

CreditsEarned INT NOT NULL,

GPA DECIMAL(3,2) NOT NULL,

ScholarshipStatus VARCHAR(20) NOT NULL,

AdvisorID INT,

EmergencyContactName VARCHAR(100),

EmergencyContactPhone VARCHAR(20),

CreatedAt DATETIME NOT NULL

);

GO

Import Wizard

SELECT *

FROM dbo.Students;

SELECT COUNT(*) AS NumberOfStudents

FROM dbo.Students;

SELECT TOP 10

    StudentID,

    StudentNumber,

    FirstName,

    LastName,

    Nationality,

    AdmissionType,

    EnrollmentStatus,

    StudyMode,

    GPA,

    ScholarshipStatus,

    FacultyID,

    DepartmentID,

    ProgramID,

    AdvisorID

FROM dbo.Students

ORDER BY StudentID;

SELECT

    COUNT(*) AS TotalStudents,

    COUNT(DISTINCT StudentID) AS UniqueStudentIDs,

    COUNT(DISTINCT StudentNumber) AS UniqueStudentNumbers,

    COUNT(DISTINCT Email) AS UniqueEmails,

    MIN(GPA) AS MinGPA,

    MAX(GPA) AS MaxGPA

FROM dbo.Students;

Creating Faculties Table:

USE Campus360;

GO

CREATE TABLE Faculties (

    FacultyID INT NOT NULL PRIMARY KEY,

    FacultyName VARCHAR(100) NOT NULL UNIQUE,

    DeanName VARCHAR(100) NOT NULL,

    EstablishedYear INT NOT NULL,

    Campus VARCHAR(100) NOT NULL,

    OfficeEmail VARCHAR(100) NOT NULL UNIQUE

);

GO

INSERT INTO dbo.Faculties

(

    FacultyID,

    FacultyName,

    DeanName,

    EstablishedYear,

    Campus,

    OfficeEmail

)

VALUES

(1, 'Faculty of Engineering', 'Dr. Robert Anderson', 1978, 'Main Campus', 'engineering@campus360.ca'),

(2, 'Faculty of Business', 'Dr. Sarah Mitchell', 1985, 'Main Campus', 'business@campus360.ca'),

(3, 'Faculty of Science', 'Dr. Michael Thompson', 1972, 'North Campus', 'science@campus360.ca'),

(4, 'Faculty of Arts', 'Dr. Emily Carter', 1969, 'Main Campus', 'arts@campus360.ca'),

(5, 'Faculty of Health Sciences', 'Dr. Daniel Wilson', 1991, 'Health Campus', 'health@campus360.ca'),

(6, 'Faculty of Information Technology', 'Dr. Jennifer Lee', 2003, 'Technology Campus', 'it@campus360.ca');

GO

SELECT *

FROM dbo.Faculties

ORDER BY FacultyID;

Creating Departments Table:

USE Campus360;

GO

CREATE TABLE Departments (

    DepartmentID INT NOT NULL PRIMARY KEY,

    DepartmentName VARCHAR(100) NOT NULL,

    FacultyID INT NOT NULL,

    DepartmentHead VARCHAR(100) NOT NULL,

    OfficeEmail VARCHAR(100) NOT NULL UNIQUE

);

GO

USE Campus360;

GO

INSERT INTO dbo.Departments

(

    DepartmentID,

    DepartmentName,

    FacultyID,

    DepartmentHead,

    OfficeEmail

)

VALUES

(101, 'Electrical Engineering', 1, 'Dr. David Anderson', 'ee@campus360.ca'),

(102, 'Mechanical Engineering', 1, 'Dr. James Wilson', 'me@campus360.ca'),

(103, 'Civil Engineering', 1, 'Dr. Robert Taylor', 'ce@campus360.ca'),

(201, 'Accounting and Finance', 2, 'Dr. Sarah Mitchell', 'accounting@campus360.ca'),

(202, 'Business Administration', 2, 'Dr. William Brown', 'businessadmin@campus360.ca'),

(301, 'Computer Science', 3, 'Dr. Michael Thompson', 'cs@campus360.ca'),

(302, 'Mathematics', 3, 'Dr. Daniel Clark', 'math@campus360.ca'),

(401, 'Psychology', 4, 'Dr. Emily Carter', 'psychology@campus360.ca'),

(402, 'Economics', 4, 'Dr. Christopher Lee', 'economics@campus360.ca'),




(501, 'Nursing', 5, 'Dr. Jennifer Smith', 'nursing@campus360.ca'),

(502, 'Public Health', 5, 'Dr. Laura Martin', 'publichealth@campus360.ca'),

(601, 'Information Technology', 6, 'Dr. Kevin Johnson', 'itdept@campus360.ca'),

(602, 'Data Science', 6, 'Dr. Rachel Davis', 'datascience@campus360.ca');

GO

Creating Programs Table:

USE Campus360;

GO

CREATE TABLE Programs (

    ProgramID INT NOT NULL PRIMARY KEY,

    DepartmentID INT NOT NULL,

    ProgramName VARCHAR(120) NOT NULL UNIQUE,

    DegreeLevel VARCHAR(30) NOT NULL,

    DurationYears INT NOT NULL,

    TotalCredits INT NOT NULL,

    CoordinatorName VARCHAR(100) NOT NULL,

    ProgramEmail VARCHAR(100) NOT NULL UNIQUE

);

GO

USE Campus360;

GO

INSERT INTO dbo.Programs

(

    ProgramID,

    DepartmentID,

    ProgramName,

    DegreeLevel,

    DurationYears,

    TotalCredits,

    CoordinatorName,

    ProgramEmail

)

VALUES

(1001, 101, 'Bachelor of Computer Science', 'Bachelor', 4, 120,

 'Dr. Andrew Wilson', 'bcs@campus360.ca'),

(1002, 102, 'Bachelor of Data Science', 'Bachelor', 4, 120,

 'Dr. Sophia Martin', 'bds@campus360.ca'),

(1003, 103, 'Bachelor of Artificial Intelligence', 'Bachelor', 4, 120,

 'Dr. Daniel Clark', 'bai@campus360.ca'),

(2001, 201, 'Bachelor of Accounting', 'Bachelor', 4, 120,

 'Dr. Sarah Thompson', 'accounting.program@campus360.ca'),

(2002, 202, 'Bachelor of Finance', 'Bachelor', 4, 120,

 'Dr. Michael Brown', 'finance.program@campus360.ca'),

(3001, 301, 'Bachelor of Mathematics', 'Bachelor', 4, 120,

 'Dr. Robert Davis', 'mathematics.program@campus360.ca'),

(3002, 302, 'Bachelor of Statistics', 'Bachelor', 4, 120,

 'Dr. Emily Anderson', 'statistics.program@campus360.ca'),

(4001, 401, 'Bachelor of Psychology', 'Bachelor', 4, 120,

 'Dr. Laura Miller', 'psychology.program@campus360.ca'),

(4002, 402, 'Bachelor of Economics', 'Bachelor', 4, 120,

 'Dr. James Taylor', 'economics.program@campus360.ca'),

(5001, 501, 'Bachelor of Nursing', 'Bachelor', 4, 120,

 'Dr. Jennifer Lee', 'nursing.program@campus360.ca'),

(5002, 502, 'Bachelor of Public Health', 'Bachelor', 4, 120,

 'Dr. Rachel Wilson', 'publichealth.program@campus360.ca'),

(6001, 601, 'Bachelor of Information Technology', 'Bachelor', 4, 120,

 'Dr. Kevin Johnson', 'it.program@campus360.ca'),

(6002, 602, 'Bachelor of Cybersecurity', 'Bachelor', 4, 120,

 'Dr. Amanda Carter', 'cybersecurity.program@campus360.ca');

GO

SELECT *

FROM dbo.Programs

ORDER BY ProgramID;

Creating Professors Table:

USE Campus360;

GO

CREATE TABLE Professors (

    ProfessorID INT NOT NULL PRIMARY KEY,

    DepartmentID INT NOT NULL,

    FirstName VARCHAR(50) NOT NULL,

    LastName VARCHAR(50) NOT NULL,

    AcademicTitle VARCHAR(50) NOT NULL,

    Specialization VARCHAR(100) NOT NULL,

    Email VARCHAR(100) NOT NULL UNIQUE,

    Phone VARCHAR(20),

    HireDate DATE NOT NULL,

    EmploymentStatus VARCHAR(20) NOT NULL

);

GO

USE Campus360;

GO

INSERT INTO dbo.Professors

(

    ProfessorID,

    DepartmentID,

    FirstName,

    LastName,

    AcademicTitle,

    Specialization,

    Email,

    Phone,

    HireDate,

    EmploymentStatus

)

VALUES

(1,101,'Andrew','Wilson','Professor','Software Engineering','andrew.wilson@campus360.ca','416-555-2001','2008-08-15','Active'),

(2,101,'Sophia','Martin','Associate Professor','Database Systems','sophia.martin@campus360.ca','416-555-2002','2012-07-01','Active'),

(3,101,'Daniel','Clark','Assistant Professor','Cloud Computing','daniel.clark@campus360.ca','416-555-2003','2018-09-01','Active'),

(4,101,'Olivia','Brown','Associate Professor','Cybersecurity','olivia.brown@campus360.ca','416-555-2004','2014-08-20','Active'),

(5,101,'James','Taylor','Professor','Computer Networks','james.taylor@campus360.ca','416-555-2005','2006-07-10','Active'),

(6,102,'Emily','Davis','Professor','Data Analytics','emily.davis@campus360.ca','416-555-2006','2009-08-12','Active'),

(7,102,'Michael','Lee','Associate Professor','Machine Learning','michael.lee@campus360.ca','416-555-2007','2013-09-01','Active'),

(8,102,'Emma','Anderson','Assistant Professor','Data Visualization','emma.anderson@campus360.ca','416-555-2008','2019-08-20','Active'),

(9,102,'William','Scott','Associate Professor','Big Data Analytics','william.scott@campus360.ca','416-555-2009','2015-07-15','Active'),

(10,102,'Ava','Mitchell','Assistant Professor','Business Analytics','ava.mitchell@campus360.ca','416-555-2010','2020-09-01','Active'),

(11,103,'Robert','Thompson','Professor','Artificial Intelligence','robert.thompson@campus360.ca','416-555-2011','2007-08-15','Active'),

(12,103,'Charlotte','Moore','Associate Professor','Deep Learning','charlotte.moore@campus360.ca','416-555-2012','2013-08-15','Active'),

(13,103,'Henry','White','Assistant Professor','Natural Language Processing','henry.white@campus360.ca','416-555-2013','2018-09-01','Active'),

(14,103,'Mia','Harris','Associate Professor','Computer Vision','mia.harris@campus360.ca','416-555-2014','2015-07-01','Active'),

(15,103,'Lucas','Lewis','Assistant Professor','Intelligent Systems','lucas.lewis@campus360.ca','416-555-2015','2021-08-15','Active'),

(16,201,'Sarah','Walker','Professor','Financial Accounting','sarah.walker@campus360.ca','416-555-2016','2008-07-01','Active'),

(17,201,'Benjamin','Hall','Associate Professor','Managerial Accounting','benjamin.hall@campus360.ca','416-555-2017','2014-08-15','Active'),

(18,201,'Amelia','Young','Assistant Professor','Auditing','amelia.young@campus360.ca','416-555-2018','2019-09-01','Active'),

(19,201,'Ethan','King','Associate Professor','Corporate Accounting','ethan.king@campus360.ca','416-555-2019','2015-07-10','Active'),

(20,202,'Jennifer','Wright','Professor','Corporate Finance','jennifer.wright@campus360.ca','416-555-2020','2006-08-15','Active'),

(21,202,'Matthew','Green','Associate Professor','Investment Analysis','matthew.green@campus360.ca','416-555-2021','2013-09-01','Active'),

(22,202,'Chloe','Baker','Assistant Professor','Financial Markets','chloe.baker@campus360.ca','416-555-2022','2020-08-15','Active'),

(23,202,'Jacob','Adams','Associate Professor','Risk Management','jacob.adams@campus360.ca','416-555-2023','2016-07-01','Active'),

(24,301,'David','Nelson','Professor','Applied Mathematics','david.nelson@campus360.ca','416-555-2024','2005-08-01','Active'),

(25,301,'Hannah','Carter','Associate Professor','Mathematical Modeling','hannah.carter@campus360.ca','416-555-2025','2012-09-01','Active'),

(26,301,'Samuel','Mitchell','Assistant Professor','Discrete Mathematics','samuel.mitchell@campus360.ca','416-555-2026','2019-08-15','Active'),

(27,301,'Grace','Roberts','Associate Professor','Calculus','grace.roberts@campus360.ca','416-555-2027','2015-08-01','Active'),

(28,302,'Christopher','Turner','Professor','Statistical Modeling','christopher.turner@campus360.ca','416-555-2028','2007-07-01','Active'),

(29,302,'Victoria','Phillips','Associate Professor','Probability','victoria.phillips@campus360.ca','416-555-2029','2013-08-15','Active'),

(30,302,'Nathan','Campbell','Assistant Professor','Predictive Analytics','nathan.campbell@campus360.ca','416-555-2030','2020-09-01','Active'),

(31,302,'Lily','Parker','Associate Professor','Biostatistics','lily.parker@campus360.ca','416-555-2031','2016-08-10','Active'),

(32,401,'Laura','Evans','Professor','Cognitive Psychology','laura.evans@campus360.ca','416-555-2032','2008-07-01','Active'),

(33,401,'Ryan','Edwards','Associate Professor','Social Psychology','ryan.edwards@campus360.ca','416-555-2033','2014-08-01','Active'),

(34,401,'Nora','Collins','Assistant Professor','Developmental Psychology','nora.collins@campus360.ca','416-555-2034','2019-09-01','Active'),

(35,401,'Leo','Stewart','Associate Professor','Organizational Psychology','leo.stewart@campus360.ca','416-555-2035','2016-07-15','Active'),

(36,402,'Rachel','Morris','Professor','Macroeconomics','rachel.morris@campus360.ca','416-555-2036','2007-08-15','Active'),

(37,402,'Kevin','Rogers','Associate Professor','Microeconomics','kevin.rogers@campus360.ca','416-555-2037','2013-07-01','Active'),

(38,402,'Zoe','Reed','Assistant Professor','Economic Policy','zoe.reed@campus360.ca','416-555-2038','2020-08-15','Active'),

(39,402,'Alexander','Cook','Associate Professor','International Economics','alexander.cook@campus360.ca','416-555-2039','2015-09-01','Active'),

(40,501,'Amanda','Morgan','Professor','Clinical Nursing','amanda.morgan@campus360.ca','416-555-2040','2006-07-01','Active'),

(41,501,'Jonathan','Bell','Associate Professor','Community Nursing','jonathan.bell@campus360.ca','416-555-2041','2013-08-15','Active'),

(42,501,'Isabella','Murphy','Assistant Professor','Nursing Education','isabella.murphy@campus360.ca','416-555-2042','2019-09-01','Active'),

(43,501,'Logan','Bailey','Associate Professor','Patient Care','logan.bailey@campus360.ca','416-555-2043','2015-08-10','Active'),

(44,502,'Rebecca','Cooper','Professor','Public Health Policy','rebecca.cooper@campus360.ca','416-555-2044','2008-07-01','Active'),

(45,502,'Thomas','Richardson','Associate Professor','Epidemiology','thomas.richardson@campus360.ca','416-555-2045','2014-08-01','Active'),

(46,502,'Ella','Cox','Assistant Professor','Health Promotion','ella.cox@campus360.ca','416-555-2046','2020-09-01','Active'),

(47,502,'Jack','Howard','Associate Professor','Global Health','jack.howard@campus360.ca','416-555-2047','2016-07-15','Active'),

(48,601,'Steven','Ward','Professor','Information Systems','steven.ward@campus360.ca','416-555-2048','2007-08-15','Active'),

(49,601,'Aria','Peterson','Associate Professor','IT Management','aria.peterson@campus360.ca','416-555-2049','2013-07-01','Active'),

(50,601,'Owen','Gray','Assistant Professor','Cloud Infrastructure','owen.gray@campus360.ca','416-555-2050','2019-08-15','Active'),

(51,601,'Evelyn','Ramirez','Associate Professor','Enterprise Systems','evelyn.ramirez@campus360.ca','416-555-2051','2015-09-01','Active'),

(52,601,'George','James','Assistant Professor','Network Administration','george.james@campus360.ca','416-555-2052','2021-08-20','Active'),

(53,602,'Nicole','Watson','Professor','Cybersecurity','nicole.watson@campus360.ca','416-555-2053','2008-07-01','Active'),

(54,602,'Adam','Brooks','Associate Professor','Network Security','adam.brooks@campus360.ca','416-555-2054','2014-08-15','Active'),

(55,602,'Claire','Kelly','Assistant Professor','Digital Forensics','claire.kelly@campus360.ca','416-555-2055','2020-09-01','Active'),

(56,602,'Jason','Sanders','Associate Professor','Information Security','jason.sanders@campus360.ca','416-555-2056','2016-07-10','Active'),

(57,602,'Alice','Price','Assistant Professor','Cryptography','alice.price@campus360.ca','416-555-2057','2021-08-15','Active'),

(58,602,'Mark','Bennett','Professor','Security Architecture','mark.bennett@campus360.ca','416-555-2058','2009-07-01','Active'),

(59,602,'Julia','Wood','Associate Professor','Cyber Risk Management','julia.wood@campus360.ca','416-555-2059','2015-08-15','Active'),

(60,602,'Eric','Barnes','Assistant Professor','Ethical Hacking','eric.barnes@campus360.ca','416-555-2060','2022-09-01','Active');

GO

USE Campus360;

GO

SELECT COUNT(*) AS NumberOfProfessors

FROM dbo.Professors;

SELECT TOP 10 *

FROM dbo.Professors

ORDER BY ProfessorID;


SELECT

    MIN(AdvisorID) AS MinimumAdvisorID,

    MAX(AdvisorID) AS MaximumAdvisorID,

    COUNT(DISTINCT AdvisorID) AS AdvisorsUsed

FROM dbo.Students;

After this stage, the structure is approximately as follows: Faculties ↓ Departments ↓ Programs Departments ↓ Professors ↓ Students (AdvisorID) Programs ↓ Students (ProgramID)

Verify the Presence of All Tables

USE Campus360;
GO

SELECT

OBJECT_ID('dbo.Faculties')   AS FacultiesTable,
OBJECT_ID('dbo.Departments') AS DepartmentsTable,
OBJECT_ID('dbo.Programs')    AS ProgramsTable,
OBJECT_ID('dbo.Professors')  AS ProfessorsTable,
OBJECT_ID('dbo.Students')    AS StudentsTable;

Check for ID inconsistencies

USE Campus360;
GO

-- Departments without a valid Faculty

SELECT d.DepartmentID, d.FacultyID
FROM dbo.Departments d
LEFT JOIN dbo.Faculties f
ON d.FacultyID = f.FacultyID
WHERE f.FacultyID IS NULL;

-- Programs without a valid Department

SELECT p.ProgramID, p.DepartmentID
FROM dbo.Programs p
LEFT JOIN dbo.Departments d
ON p.DepartmentID = d.DepartmentID
WHERE d.DepartmentID IS NULL;

-- Professors without a valid Department

SELECT p.ProfessorID, p.DepartmentID

FROM dbo.Professors p
LEFT JOIN dbo.Departments d
ON p.DepartmentID = d.DepartmentID
WHERE d.DepartmentID IS NULL;

-- Students without a valid Faculty

SELECT DISTINCT s.FacultyID
FROM dbo.Students s
LEFT JOIN dbo.Faculties f
ON s.FacultyID = f.FacultyID
WHERE f.FacultyID IS NULL;

-- Students without a valid Department

SELECT DISTINCT s.DepartmentID
FROM dbo.Students s
LEFT JOIN dbo.Departments d
ON s.DepartmentID = d.DepartmentID
WHERE d.DepartmentID IS NULL;

-- Students without a valid Program

SELECT DISTINCT s.ProgramID
FROM dbo.Students s
LEFT JOIN dbo.Programs p
ON s.ProgramID = p.ProgramID
WHERE p.ProgramID IS NULL;

-- Students without a valid Advisor

SELECT DISTINCT s.AdvisorID
FROM dbo.Students s
LEFT JOIN dbo.Professors p
ON s.AdvisorID = p.ProfessorID
WHERE s.AdvisorID IS NOT NULL
AND p.ProfessorID IS NULL;

Creating Foreign Keys:

USE Campus360;
GO

-- Faculties → Departments

ALTER TABLE dbo.Departments
ADD CONSTRAINT FK_Departments_Faculties
FOREIGN KEY (FacultyID)
REFERENCES dbo.Faculties(FacultyID);

GO

-- Departments → Programs

ALTER TABLE dbo.Programs
ADD CONSTRAINT FK_Programs_Departments
FOREIGN KEY (DepartmentID)
REFERENCES dbo.Departments(DepartmentID);

GO

-- Departments → Professors

ALTER TABLE dbo.Professors
ADD CONSTRAINT FK_Professors_Departments
FOREIGN KEY (DepartmentID)
REFERENCES dbo.Departments(DepartmentID);

GO

-- Faculties → Students

ALTER TABLE dbo.Students
ADD CONSTRAINT FK_Students_Faculties
FOREIGN KEY (FacultyID)
REFERENCES dbo.Faculties(FacultyID);

GO

-- Departments → Students

ALTER TABLE dbo.Students
ADD CONSTRAINT FK_Students_Departments
FOREIGN KEY (DepartmentID)
REFERENCES dbo.Departments(DepartmentID);

GO


-- Programs → Students

ALTER TABLE dbo.Students
ADD CONSTRAINT FK_Students_Programs
FOREIGN KEY (ProgramID)
REFERENCES dbo.Programs(ProgramID);

GO

-- Professors → Students (Advisor)

ALTER TABLE dbo.Students
ADD CONSTRAINT FK_Students_Professors
FOREIGN KEY (AdvisorID)
REFERENCES dbo.Professors(ProfessorID);

GO

Relationship Structure:

Faculties │ └──── FacultyID ────► Departments │ ├────► Programs │ └────► Professors Faculties ────────────────► Students Departments ──────────────► Students Programs ─────────────────► Students Professors ── AdvisorID ──► Students

Verify That the Foreign Keys Have Been Successfully Created:

SELECT

fk.name AS ForeignKeyName,
OBJECT_NAME(fk.parent_object_id) AS ChildTable,
COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ChildColumn,
OBJECT_NAME(fk.referenced_object_id) AS ParentTable,
COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ParentColumn

FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc
ON fk.object_id = fkc.constraint_object_id
ORDER BY ChildTable, ForeignKeyName;

Creating CoursesTable:

USE Campus360;

GO

CREATE TABLE Courses (

CourseID INT NOT NULL PRIMARY KEY,
DepartmentID INT NOT NULL,
CourseCode VARCHAR(10) NOT NULL UNIQUE,
CourseName VARCHAR(120) NOT NULL,
Credits INT NOT NULL,
CourseLevel INT NOT NULL,
SemesterOffered VARCHAR(20) NOT NULL,
DeliveryMode VARCHAR(20) NOT NULL,
Capacity INT NOT NULL,
CourseStatus VARCHAR(20) NOT NULL

);

GO

USE Campus360;

GO

INSERT INTO dbo.Courses

(
CourseID,
DepartmentID,
CourseCode,
CourseName,
Credits,
CourseLevel,
SemesterOffered,
DeliveryMode,
Capacity,
CourseStatus

)

VALUES
-- Computer Science - Department 101
(1,101,'CS101','Introduction to Programming',3,100,'Fall','In-Person',120,'Active'),
(2,101,'CS201','Data Structures and Algorithms',3,200,'Winter','In-Person',100,'Active'),
(3,101,'CS301','Database Management Systems',3,300,'Fall','Hybrid',80,'Active'),

-- Data Science - Department 102

(4,102,'DS101','Introduction to Data Science',3,100,'Fall','In-Person',100,'Active'),
(5,102,'DS201','Data Analysis with Python',3,200,'Winter','Hybrid',90,'Active'),
(6,102,'DS301','Big Data Analytics',3,300,'Fall','Hybrid',70,'Active'),

-- Artificial Intelligence - Department 103

(7,103,'AI101','Introduction to Artificial Intelligence',3,100,'Fall','In-Person',100,'Active'),
(8,103,'AI201','Machine Learning',3,200,'Winter','Hybrid',80,'Active'),
(9,103,'AI301','Deep Learning',3,300,'Fall','In-Person',60,'Active'),

-- Accounting - Department 201

(10,201,'ACC101','Financial Accounting',3,100,'Fall','In-Person',120,'Active'),
(11,201,'ACC201','Managerial Accounting',3,200,'Winter','In-Person',100,'Active'),
(12,201,'ACC301','Advanced Accounting',3,300,'Fall','Hybrid',80,'Active'),

-- Finance - Department 202

(13,202,'FIN101','Introduction to Finance',3,100,'Fall','In-Person',120,'Active'),
(14,202,'FIN201','Corporate Finance',3,200,'Winter','Hybrid',100,'Active'),
(15,202,'FIN301','Investment Analysis',3,300,'Fall','In-Person',80,'Active'),

-- Mathematics - Department 301

(16,301,'MAT101','Calculus I',3,100,'Fall','In-Person',150,'Active'),
(17,301,'MAT201','Linear Algebra',3,200,'Winter','In-Person',120,'Active'),
(18,301,'MAT301','Differential Equations',3,300,'Fall','Hybrid',90,'Active'),

-- Statistics - Department 302

(19,302,'STA101','Introduction to Statistics',3,100,'Fall','In-Person',140,'Active'),
(20,302,'STA201','Probability Theory',3,200,'Winter','In-Person',110,'Active'),
(21,302,'STA301','Statistical Modeling',3,300,'Fall','Hybrid',90,'Active'),

-- Psychology - Department 401

(22,401,'PSY101','Introduction to Psychology',3,100,'Fall','In-Person',140,'Active'),
(23,401,'PSY201','Cognitive Psychology',3,200,'Winter','Hybrid',100,'Active'),
(24,401,'PSY301','Social Psychology',3,300,'Fall','In-Person',90,'Active'),

-- Economics - Department 402

(25,402,'ECO101','Principles of Microeconomics',3,100,'Fall','In-Person',130,'Active'),
(26,402,'ECO201','Macroeconomics',3,200,'Winter','Hybrid',110,'Active'),
(27,402,'ECO301','International Economics',3,300,'Fall','In-Person',80,'Active'),

-- Nursing - Department 501

(28,501,'NUR101','Fundamentals of Nursing',4,100,'Fall','In-Person',80,'Active'),
(29,501,'NUR201','Clinical Nursing Practice',4,200,'Winter','In-Person',60,'Active'),
(30,501,'NUR301','Community Health Nursing',4,300,'Fall','Hybrid',60,'Active'),

-- Public Health - Department 502

(31,502,'PH101','Introduction to Public Health',3,100,'Fall','In-Person',100,'Active'),
(32,502,'PH201','Epidemiology',3,200,'Winter','Hybrid',80,'Active'),
(33,502,'PH301','Health Policy and Management',3,300,'Fall','Online',70,'Active'),

-- Information Technology - Department 601

(34,601,'IT101','Introduction to Information Technology',3,100,'Fall','In-Person',120,'Active'),
(35,601,'IT201','Network Administration',3,200,'Winter','Hybrid',90,'Active'),
(36,601,'IT301','Cloud Computing',3,300,'Fall','Online',80,'Active'),

-- Cybersecurity - Department 602

(37,602,'CYB101','Introduction to Cybersecurity',3,100,'Fall','In-Person',100,'Active'),
(38,602,'CYB201','Network Security',3,200,'Winter','Hybrid',80,'Active'),
(39,602,'CYB301','Digital Forensics',3,300,'Fall','In-Person',60,'Active');

GO

Verify That the Courses Have Been Successfully Created:

USE Campus360;

GO


SELECT COUNT(*) AS NumberOfCourses

FROM dbo.Courses;

SELECT *

FROM dbo.Courses
ORDER BY CourseID;

Creating Foreign Key for Courses:

USE Campus360;

GO

ALTER TABLE dbo.Courses
ADD CONSTRAINT FK_Courses_Departments
FOREIGN KEY (DepartmentID)
REFERENCES dbo.Departments(DepartmentID);


GO

Relationship Structure:

Faculties ↓ Departments ├──── Programs ├──── Professors └──── Courses ↓ Enrollments ↓ Grades

Creating Enrollments Table:

The Enrollments table serves as a many-to-many relationship table between the Students and Courses tables. I will create this table and generate three course enrollments for each student. With 1,000 students, the total number of enrollment records will be: 1,000 × 3 = 3,000 enrollment records The advantage of this approach is that each student will be enrolled only in courses that belong to their own DepartmentID.

USE Campus360;

GO

CREATE TABLE Enrollments (
EnrollmentID INT NOT NULL PRIMARY KEY,
StudentID INT NOT NULL,
CourseID INT NOT NULL,
EnrollmentDate DATE NOT NULL,
AcademicYear VARCHAR(9) NOT NULL,
Semester VARCHAR(20) NOT NULL,
EnrollmentStatus VARCHAR(20) NOT NULL

);

GO

Automatically Generate 3,000 Enrollment Records:

USE Campus360;

GO

WITH StudentCourses AS
(

SELECT
s.StudentID,
c.CourseID,
c.SemesterOffered,
ROW_NUMBER() OVER (
ORDER BY s.StudentID, c.CourseID
) AS EnrollmentID

FROM dbo.Students s

INNER JOIN dbo.Courses c
ON s.DepartmentID = c.DepartmentID
)

INSERT INTO dbo.Enrollments

(

EnrollmentID,
StudentID,
CourseID,
EnrollmentDate,
AcademicYear,

Semester,
EnrollmentStatus

)

SELECT
EnrollmentID,
StudentID,
CourseID,
DATEADD(
DAY,
(StudentID % 20),

CAST('2025-08-20' AS DATE)
) AS EnrollmentDate,
'2025-2026' AS AcademicYear,
SemesterOffered,
'Enrolled' AS EnrollmentStatus
FROM StudentCourses;

GO

Verify the Number of Records:

USE Campus360;

GO

SELECT COUNT(*) AS NumberOfEnrollments

FROM dbo.Enrollments;

SELECT TOP 20 *
FROM dbo.Enrollments
ORDER BY EnrollmentID;

A Very Important Logical Validation:

We need to ensure that no student is enrolled in a course that belongs to a different Department.

SELECT

e.EnrollmentID,
e.StudentID,
s.DepartmentID AS StudentDepartment,
e.CourseID,
c.DepartmentID AS CourseDepartment

FROM dbo.Enrollments e
INNER JOIN dbo.Students s
ON e.StudentID = s.StudentID
INNER JOIN dbo.Courses c
ON e.CourseID = c.CourseID
WHERE s.DepartmentID <> c.DepartmentID;

Creating Foreign Keys:

USE Campus360;

GO

ALTER TABLE dbo.Enrollments
ADD CONSTRAINT FK_Enrollments_Students
FOREIGN KEY (StudentID)
REFERENCES dbo.Students(StudentID);

GO

ALTER TABLE dbo.Enrollments
ADD CONSTRAINT FK_Enrollments_Courses
FOREIGN KEY (CourseID)
REFERENCES dbo.Courses(CourseID);

GO

Prevent Duplicate Enrollments:

Since a student cannot be enrolled in the same course more than once during the same academic year, I will write an additional query to identify and prevent duplicate enrollments.

ALTER TABLE dbo.Enrollments
ADD CONSTRAINT UQ_Enrollments_Student_Course_Year
UNIQUE (StudentID, CourseID, AcademicYear);

GO

Relationship Structure:

Students │ │ StudentID ▼ Enrollments ▲ │ CourseID │ Courses Therefore, the relationship between Students and Courses is no longer direct. Instead, it is structured as: Students → Enrollments ← Courses This represents the correct implementation of a many-to-many relationship.

Creating Grade Table:

We connect this table to the Enrollments table so that each course enrollment has a corresponding academic result.

USE Campus360;

GO

CREATE TABLE Grades (
GradeID INT NOT NULL PRIMARY KEY,
EnrollmentID INT NOT NULL UNIQUE,
NumericGrade DECIMAL(5,2) NOT NULL,
LetterGrade VARCHAR(2) NOT NULL,
GradePoint DECIMAL(3,2) NOT NULL,
ResultStatus VARCHAR(10) NOT NULL,
GradedDate DATE NOT NULL


);


GO

This code generates grades ranging approximately from 45 to 100 and uses them to determine the corresponding Letter Grade, Grade Point, and Pass/Fail status.

USE Campus360;

GO

INSERT INTO dbo.Grades

(

GradeID,
EnrollmentID,
NumericGrade,
LetterGrade,
GradePoint,
ResultStatus,
GradedDate


)


SELECT

e.EnrollmentID AS GradeID,
e.EnrollmentID,
g.NumericGrade,

CASE

WHEN g.NumericGrade >= 90 THEN 'A+'
WHEN g.NumericGrade >= 85 THEN 'A'
WHEN g.NumericGrade >= 80 THEN 'A-'
WHEN g.NumericGrade >= 77 THEN 'B+'
WHEN g.NumericGrade >= 73 THEN 'B'
WHEN g.NumericGrade >= 70 THEN 'B-'
WHEN g.NumericGrade >= 67 THEN 'C+'
WHEN g.NumericGrade >= 63 THEN 'C'
WHEN g.NumericGrade >= 60 THEN 'C-'
WHEN g.NumericGrade >= 57 THEN 'D+'
WHEN g.NumericGrade >= 53 THEN 'D'
WHEN g.NumericGrade >= 50 THEN 'D-'

ELSE 'F'

END AS LetterGrade,

CASE

WHEN g.NumericGrade >= 90 THEN 4.00
WHEN g.NumericGrade >= 85 THEN 4.00
WHEN g.NumericGrade >= 80 THEN 3.70
WHEN g.NumericGrade >= 77 THEN 3.30
WHEN g.NumericGrade >= 73 THEN 3.00
WHEN g.NumericGrade >= 70 THEN 2.70
WHEN g.NumericGrade >= 67 THEN 2.30
WHEN g.NumericGrade >= 63 THEN 2.00
WHEN g.NumericGrade >= 60 THEN 1.70
WHEN g.NumericGrade >= 57 THEN 1.30
WHEN g.NumericGrade >= 53 THEN 1.00
WHEN g.NumericGrade >= 50 THEN 0.70

ELSE 0.00

END AS GradePoint,

CASE
WHEN g.NumericGrade >= 50 THEN 'Pass'

ELSE 'Fail'

END AS ResultStatus,

DATEADD(
DAY,

110 + (e.EnrollmentID % 15),
e.EnrollmentDate

) AS GradedDate

FROM dbo.Enrollments e

CROSS APPLY

(

SELECT CAST(

45 + ((e.EnrollmentID * 17) % 56)
AS DECIMAL(5,2)
) AS NumericGrade
) g;

GO

USE Campus360;

GO

SELECT COUNT(*) AS NumberOfGrades
FROM dbo.Grades;

SELECT TOP 20 *

FROM dbo.Grades
ORDER BY GradeID;

SELECT

MIN(NumericGrade) AS MinimumGrade,
MAX(NumericGrade) AS MaximumGrade,
AVG(NumericGrade) AS AverageGrade,
SUM(CASE WHEN ResultStatus = 'Pass' THEN 1 ELSE 0 END) AS PassedStudents,
SUM(CASE WHEN ResultStatus = 'Fail' THEN 1 ELSE 0 END) AS FailedStudents

FROM dbo.Grades;

USE Campus360;

GO

SELECT

fk.name AS ForeignKeyName,
OBJECT_NAME(fk.parent_object_id) AS ChildTable,
COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ChildColumn,
OBJECT_NAME(fk.referenced_object_id) AS ParentTable,
COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ParentColumn

FROM sys.foreign_keys fk

INNER JOIN sys.foreign_key_columns fkc
ON fk.object_id = fkc.constraint_object_id
WHERE OBJECT_NAME(fk.parent_object_id) = 'Grades';

USE Campus360;

GO

ALTER TABLE dbo.Grades
ADD CONSTRAINT FK_Grades_Enrollments
FOREIGN KEY (EnrollmentID)
REFERENCES dbo.Enrollments(EnrollmentID);

GO

Creating Foreign Keys:

USE Campus360;

GO


ALTER TABLE dbo.Grades
ADD CONSTRAINT FK_Grades_Enrollments
FOREIGN KEY (EnrollmentID)
REFERENCES dbo.Enrollments(EnrollmentID);

GO

Relationship Structure:

Students ↓ Enrollments ↓ Grades Courses ↑ Enrollments

Creating Attendance Table:

We connect this table to the Enrollments table because attendance records are associated with a student’s enrollment in a specific course. For each Enrollment, we record multiple class sessions. If each enrollment includes 10 sessions, the total number of attendance records will be: 3,000 Enrollments × 10 Sessions = 30,000 Attendance Records

USE Campus360;

GO


CREATE TABLE Attendance (

AttendanceID INT NOT NULL PRIMARY KEY,
EnrollmentID INT NOT NULL,
SessionNumber INT NOT NULL,
AttendanceDate DATE NOT NULL,
AttendanceStatus VARCHAR(10) NOT NULL

);

GO

Automatically Generate 30,000 Attendance Records:

USE Campus360;

GO

WITH SessionNumbers AS
(

SELECT 1 AS SessionNumber
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10

),

AttendanceData AS
(

SELECT

e.EnrollmentID,
s.SessionNumber,
ROW_NUMBER() OVER (
ORDER BY e.EnrollmentID, s.SessionNumber

) AS AttendanceID,
DATEADD(
DAY,

(s.SessionNumber - 1) * 7,
e.EnrollmentDate
) AS AttendanceDate,

CASE

WHEN ((e.EnrollmentID + s.SessionNumber * 7) % 100) < 82
THEN 'Present'
WHEN ((e.EnrollmentID + s.SessionNumber * 7) % 100) < 94
THEN 'Absent'
ELSE 'Late'

END AS AttendanceStatus
FROM dbo.Enrollments e
CROSS JOIN SessionNumbers s
)
INSERT INTO dbo.Attendance
(

AttendanceID,
EnrollmentID,
SessionNumber,
AttendanceDate,
AttendanceStatus
)

SELECT

AttendanceID,
EnrollmentID,
SessionNumber,
AttendanceDate,
AttendanceStatus

FROM AttendanceData;

GO

USE Campus360;

GO

SELECT COUNT(*) AS NumberOfAttendanceRecords
FROM dbo.Attendance;
SELECT TOP 20 *
FROM dbo.Attendance
ORDER BY AttendanceID;

Check the Attendance Distribution

SELECT
AttendanceStatus,
COUNT(*) AS NumberOfRecords
FROM dbo.Attendance
GROUP BY AttendanceStatus
ORDER BY NumberOfRecords DESC;

Creating Foreign Keys:

USE Campus360;

GO

ALTER TABLE dbo.Attendance
ADD CONSTRAINT FK_Attendance_Enrollments
FOREIGN KEY (EnrollmentID)
REFERENCES dbo.Enrollments(EnrollmentID);

GO

ALTER TABLE dbo.Attendance
ADD CONSTRAINT UQ_Attendance_Enrollment_Session
UNIQUE (EnrollmentID, SessionNumber);

GO

Relationship Structure:

Students ↓ Enrollments ├── Grades └── Attendance ↑ Courses

USE Campus360;


GO


SELECT

kc.name AS ConstraintName,
OBJECT_NAME(kc.parent_object_id) AS TableName
FROM sys.key_constraints kc
WHERE kc.name = 'UQ_Attendance_Enrollment_Session';


SELECT

COUNT(*) AS TotalAttendanceRecords,
COUNT(DISTINCT EnrollmentID) AS TotalEnrollments,
MIN(SessionNumber) AS MinSession,
MAX(SessionNumber) AS MaxSession

FROM dbo.Attendance;

Creating TuitionPayments:

For each student, we create two tuition payment records for the 2025–2026 academic year: one for the Fall term and one for the Winter term. Therefore: 1,000 Students × 2 Terms = 2,000 Tuition Payment Records

USE Campus360;

GO

CREATE TABLE TuitionPayments (
PaymentID INT NOT NULL PRIMARY KEY,
StudentID INT NOT NULL,
AcademicYear VARCHAR(9) NOT NULL,
Term VARCHAR(20) NOT NULL,
AmountDue DECIMAL(10,2) NOT NULL,
AmountPaid DECIMAL(10,2) NOT NULL,
PaymentDate DATE NULL,
PaymentMethod VARCHAR(30) NULL,
PaymentStatus VARCHAR(20) NOT NULL,
TransactionReference VARCHAR(30) NULL

);

GO

USE Campus360;

GO

WITH Terms AS
(


SELECT 1 AS TermNumber, 'Fall' AS Term
UNION ALL
SELECT 2, 'Winter'
),

PaymentBase AS
(


SELECT
s.StudentID,
s.AdmissionType,
s.ScholarshipStatus,
t.TermNumber,
t.Term,

ROW_NUMBER() OVER (

ORDER BY s.StudentID, t.TermNumber


) AS PaymentID,

CASE

WHEN s.AdmissionType = 'International' THEN 15000.00


ELSE 7000.00

END AS BaseTuition
FROM dbo.Students s

CROSS JOIN Terms t
),

PaymentAmounts AS

(


SELECT *,

CASE
WHEN ScholarshipStatus = 'Full'
THEN 0.00
WHEN ScholarshipStatus = 'Partial'
THEN BaseTuition * 0.50

ELSE BaseTuition

END AS AmountDue

FROM PaymentBase
)


INSERT INTO dbo.TuitionPayments
(

PaymentID,
StudentID,
AcademicYear,

Term,

AmountDue,
AmountPaid,
PaymentDate,
PaymentMethod,
PaymentStatus,
TransactionReference

)


SELECT

PaymentID,
StudentID,
'2025-2026',
Term,
AmountDue,

CASE

-- Full scholarship

WHEN AmountDue = 0 THEN 0

-- Paid

WHEN PaymentID % 10 < 7
THEN AmountDue

-- Partial payment

WHEN PaymentID % 10 < 9
THEN AmountDue * 0.50

-- Pending

ELSE 0


END AS AmountPaid,

CASE

WHEN AmountDue = 0
THEN NULL
WHEN PaymentID % 10 < 9
THEN DATEADD(

DAY,

PaymentID % 25,


CASE


WHEN Term = 'Fall'
THEN CAST('2025-08-15' AS DATE)
ELSE CAST('2026-01-05' AS DATE)

END

)


ELSE NULL
END AS PaymentDate,

CASE

WHEN AmountDue = 0 THEN NULL
WHEN PaymentID % 4 = 0 THEN 'Credit Card'
WHEN PaymentID % 4 = 1 THEN 'Bank Transfer'
WHEN PaymentID % 4 = 2 THEN 'Debit Card'

ELSE 'Online Payment'

END AS PaymentMethod,


CASE

WHEN AmountDue = 0 THEN 'Scholarship'
WHEN PaymentID % 10 < 7 THEN 'Paid'
WHEN PaymentID % 10 < 9 THEN 'Partial'

ELSE 'Pending'



END AS PaymentStatus,

CASE


WHEN AmountDue = 0 THEN NULL
WHEN PaymentID % 10 = 9 THEN NULL
ELSE CONCAT('TXN', FORMAT(PaymentID, '000000'))
END AS TransactionReference

FROM PaymentAmounts;

GO

USE Campus360;

GO

SELECT COUNT(*) AS NumberOfTuitionPayments
FROM dbo.TuitionPayments;

SELECT TOP 20 *

FROM dbo.TuitionPayments
ORDER BY PaymentID;

Check the Payment Status:

SELECT


PaymentStatus,
COUNT(*) AS NumberOfPayments,
SUM(AmountDue) AS TotalAmountDue,
SUM(AmountPaid) AS TotalAmountPaid
FROM dbo.TuitionPayments
GROUP BY PaymentStatus
ORDER BY NumberOfPayments DESC;

SELECT

SUM(AmountDue) AS TotalTuitionDue,
SUM(AmountPaid) AS TotalTuitionPaid,
SUM(AmountDue - AmountPaid) AS OutstandingBalance
FROM dbo.TuitionPayments;

Creating Foreign Keys:

USE Campus360;


GO

ALTER TABLE dbo.TuitionPayments
ADD CONSTRAINT FK_TuitionPayments_Students
FOREIGN KEY (StudentID)
REFERENCES dbo.Students(StudentID);

GO

Prevent Duplicate Tuition Payments for the Same Term:

In this model, each Student can have only one tuition payment record for each Term and Academic Year.

ALTER TABLE dbo.TuitionPayments
ADD CONSTRAINT UQ_TuitionPayments_Student_Year_Term
UNIQUE (StudentID, AcademicYear, Term);


GO

Relationship Structure:

Students (1,000) │ ├── Enrollments (3,000) │ ├── Grades (3,000) │ └── Attendance (30,000) │ └── TuitionPayments (2,000) Courses (39) ↑ Enrollments

Creating Scholarships Table:

We need to create two tables: Scholarships, which stores information about the different types of scholarships, and StudentScholarships, which identifies which scholarship has been awarded to each student.

USE Campus360;

GO

CREATE TABLE Scholarships (

ScholarshipID INT NOT NULL PRIMARY KEY,
ScholarshipName VARCHAR(100) NOT NULL UNIQUE,
ScholarshipType VARCHAR(20) NOT NULL,
CoveragePercent DECIMAL(5,2) NOT NULL,
EligibilityCriteria VARCHAR(250),
Sponsor VARCHAR(100),
ScholarshipStatus VARCHAR(20) NOT NULL

);


GO


USE Campus360;

GO

INSERT INTO dbo.Scholarships

(


ScholarshipID,
ScholarshipName,
ScholarshipType,
CoveragePercent,
EligibilityCriteria,
Sponsor,
ScholarshipStatus

)

VALUES
(1, 'Academic Excellence Scholarship',
'Full', 100.00,
'High academic performance and outstanding GPA',
'Campus360 University',
'Active'),
(2, 'International Excellence Scholarship',
'Full', 100.00,
'Outstanding international students',
'Campus360 University',
'Active'),
(3, 'Dean Merit Scholarship',
'Partial', 50.00,
'Strong academic achievement within the faculty',
'Campus360 University',
'Active'),
(4, 'STEM Achievement Award',
'Partial', 50.00,
'Students demonstrating strong performance in STEM programs',
'Campus360 Foundation',
'Active'),
(5, 'Community Leadership Scholarship',
'Partial', 50.00,
'Academic achievement and community involvement',
'Campus360 Alumni Association',
'Active'),
(6, 'Financial Support Scholarship',
'Partial', 50.00,
'Students demonstrating financial need',
'Campus360 Foundation',
'Active');


GO

Creating StudentScholarships:

USE Campus360;


GO


CREATE TABLE StudentScholarships (
StudentScholarshipID INT NOT NULL PRIMARY KEY,
StudentID INT NOT NULL,
ScholarshipID INT NOT NULL,
AcademicYear VARCHAR(9) NOT NULL,
AwardDate DATE NOT NULL,
AwardAmount DECIMAL(10,2) NOT NULL,
AwardStatus VARCHAR(20) NOT NULL

);

GO

In the current Students file, the ScholarshipStatus column contains three values: None, Partial, and Full. Therefore, we use the existing information and create scholarship records only for students whose ScholarshipStatus is either Partial or Full.

USE Campus360;


GO


WITH EligibleStudents AS

(



SELECT

StudentID,
ScholarshipStatus,
ROW_NUMBER() OVER (
ORDER BY StudentID
) AS StudentScholarshipID
FROM dbo.Students
WHERE ScholarshipStatus IN ('Partial', 'Full')

)

INSERT INTO dbo.StudentScholarships

(


StudentScholarshipID,
StudentID,
ScholarshipID,
AcademicYear,
AwardDate,
AwardAmount,
AwardStatus

)


SELECT

StudentScholarshipID,
StudentID,

CASE

WHEN ScholarshipStatus = 'Full'
AND StudentID % 2 = 0 THEN 1
WHEN ScholarshipStatus = 'Full'

THEN 2

WHEN StudentID % 4 = 0 THEN 3
WHEN StudentID % 4 = 1 THEN 4
WHEN StudentID % 4 = 2 THEN 5

ELSE 6

END AS ScholarshipID,
'2025-2026',
DATEADD(
DAY,
StudentID % 30,
CAST('2025-07-15' AS DATE)
),

CASE

WHEN ScholarshipStatus = 'Full' THEN 14000.00
ELSE 7000.00
END AS AwardAmount,
'Awarded'
FROM EligibleStudents;

GO


SELECT COUNT(*) AS NumberOfStudentScholarships
FROM dbo.StudentScholarships;

SELECT

s.ScholarshipStatus,
COUNT(*) AS NumberOfStudents
FROM dbo.Students s
GROUP BY s.ScholarshipStatus;

SELECT TOP 20


ss.StudentScholarshipID,
ss.StudentID,
s.FirstName,
s.LastName,
sc.ScholarshipName,
sc.ScholarshipType,
sc.CoveragePercent,
ss.AcademicYear,
ss.AwardAmount,
ss.AwardStatus

FROM dbo.StudentScholarships ss

INNER JOIN dbo.Students s
ON ss.StudentID = s.StudentID
INNER JOIN dbo.Scholarships sc
ON ss.ScholarshipID = sc.ScholarshipID
ORDER BY ss.StudentScholarshipID;

Creating Foreign Keys:

USE Campus360;

GO

ALTER TABLE dbo.StudentScholarships
ADD CONSTRAINT FK_StudentScholarships_Students
FOREIGN KEY (StudentID)
REFERENCES dbo.Students(StudentID);

GO

ALTER TABLE dbo.StudentScholarships
ADD CONSTRAINT FK_StudentScholarships_Scholarships
FOREIGN KEY (ScholarshipID)
REFERENCES dbo.Scholarships(ScholarshipID);

GO


ALTER TABLE dbo.StudentScholarships
ADD CONSTRAINT UQ_StudentScholarships_Student_Scholarship_Year
UNIQUE (StudentID, ScholarshipID, AcademicYear);

GO

Relationship Structure:

Students │ ├──── Enrollments ├──── TuitionPayments │ └──── StudentScholarships │ ▼ Scholarships

Entity Relationship Diagram (ERD):

USE Campus360;


GO

SELECT

fk.name AS ForeignKeyName,
OBJECT_NAME(fk.parent_object_id) AS ChildTable,
COL_NAME(
fkc.parent_object_id,
fkc.parent_column_id


) AS ChildColumn,

OBJECT_NAME(fk.referenced_object_id) AS ParentTable,


COL_NAME(

fkc.referenced_object_id,
fkc.referenced_column_id

) AS ParentColumn


FROM sys.foreign_keys fk

INNER JOIN sys.foreign_key_columns fkc
ON fk.object_id = fkc.constraint_object_id
ORDER BY
ChildTable,
ForeignKeyName;

Creating Joins:
- 1) Students + Departments

USE Campus360;


GO


SELECT

s.StudentID,
s.StudentNumber,
s.FirstName,
s.LastName,
s.DepartmentID,
d.DepartmentName

FROM dbo.Students s

INNER JOIN dbo.Departments d
ON s.DepartmentID = d.DepartmentID
ORDER BY s.StudentID;

- 2) Students + Programs

SELECT

s.StudentID,
s.FirstName,
s.LastName,
p.ProgramName,
p.DegreeLevel


FROM dbo.Students s


INNER JOIN dbo.Programs p
ON s.ProgramID = p.ProgramID
ORDER BY s.StudentID;

- 3) Students + Faculties

SELECT


s.StudentID,
s.FirstName,
s.LastName,
f.FacultyName

FROM dbo.Students s

INNER JOIN dbo.Faculties f
ON s.FacultyID = f.FacultyID
ORDER BY s.StudentID;

- 4) Students + Professors

SELECT

s.StudentID,
s.FirstName,
s.LastName,
p.ProfessorID AS AdvisorID,
p.FirstName AS AdvisorFirstName,
p.LastName AS AdvisorLastName,
p.AcademicTitle

FROM dbo.Students s

LEFT JOIN dbo.Professors p
ON s.AdvisorID = p.ProfessorID
ORDER BY s.StudentID;

- 5) Students + Faculties + Departments + Programs

SELECT


s.StudentID,
s.StudentNumber,
s.FirstName,
s.LastName,
f.FacultyName,
d.DepartmentName,
p.ProgramName,
s.AcademicLevel,
s.GPA,
s.EnrollmentStatus


FROM dbo.Students s

INNER JOIN dbo.Faculties f
ON s.FacultyID = f.FacultyID
INNER JOIN dbo.Departments d
ON s.DepartmentID = d.DepartmentID
INNER JOIN dbo.Programs p
ON s.ProgramID = p.ProgramID
ORDER BY s.StudentID;

- 6) Students + Enrollments + Courses

SELECT


s.StudentID,
s.FirstName,
s.LastName,
e.EnrollmentID,
c.CourseCode,
c.CourseName,
e.AcademicYear,
e.Semester,
e.EnrollmentStatus

FROM dbo.Students s


INNER JOIN dbo.Enrollments e
ON s.StudentID = e.StudentID
INNER JOIN dbo.Courses c
ON e.CourseID = c.CourseID
ORDER BY s.StudentID, c.CourseID;

- 7) Students + Courses + Grades

SELECT


s.StudentID,
s.FirstName,
s.LastName,
c.CourseCode,
c.CourseName,
g.NumericGrade,
g.LetterGrade,
g.GradePoint,
g.ResultStatus

FROM dbo.Students s

INNER JOIN dbo.Enrollments e
ON s.StudentID = e.StudentID
INNER JOIN dbo.Courses c
ON e.CourseID = c.CourseID
INNER JOIN dbo.Grades g
ON e.EnrollmentID = g.EnrollmentID

ORDER BY s.StudentID, c.CourseID;

- 8) Students + TuitionPayments

SELECT


s.StudentID,
s.FirstName,
s.LastName,
tp.AcademicYear,
tp.Term,
tp.AmountDue,
tp.AmountPaid,
tp.PaymentStatus


FROM dbo.Students s

INNER JOIN dbo.TuitionPayments tp
ON s.StudentID = tp.StudentID
ORDER BY s.StudentID, tp.Term;

- 9) Students + Scholarships

SELECT


s.StudentID,
s.FirstName,
s.LastName,
sc.ScholarshipName,
sc.ScholarshipType,
sc.CoveragePercent,
ss.AwardAmount,
ss.AcademicYear


FROM dbo.Students s


INNER JOIN dbo.StudentScholarships ss
ON s.StudentID = ss.StudentID
INNER JOIN dbo.Scholarships sc
ON ss.ScholarshipID = sc.ScholarshipID


ORDER BY s.StudentID;

Analytic Joins: Which Faculty Has the Highest Number of Students?

USE Campus360;

GO


SELECT

f.FacultyName,
COUNT(s.StudentID) AS NumberOfStudents


FROM dbo.Faculties f

LEFT JOIN dbo.Students s
ON f.FacultyID = s.FacultyID


GROUP BY


f.FacultyName
ORDER BY
NumberOfStudents DESC;

Number of Students in Each Department

SELECT


d.DepartmentName,

COUNT(s.StudentID) AS NumberOfStudents

FROM dbo.Departments d

LEFT JOIN dbo.Students s
ON d.DepartmentID = s.DepartmentID

GROUP BY

d.DepartmentName

ORDER BY

NumberOfStudents DESC;

Average of GPA in each Department

SELECT

d.DepartmentName,
COUNT(s.StudentID) AS NumberOfStudents,
ROUND(AVG(s.GPA), 2) AS AverageGPA
FROM dbo.Departments d
INNER JOIN dbo.Students s
ON d.DepartmentID = s.DepartmentID


GROUP BY

d.DepartmentName

ORDER BY

AverageGPA DESC;

Student Performance by Course

SELECT

c.CourseCode,
c.CourseName,
COUNT(e.EnrollmentID) AS NumberOfEnrollments,
ROUND(AVG(g.NumericGrade), 2) AS AverageGrade,
MIN(g.NumericGrade) AS MinimumGrade,
MAX(g.NumericGrade) AS MaximumGrade

FROM dbo.Courses c

INNER JOIN dbo.Enrollments e
ON c.CourseID = e.CourseID
INNER JOIN dbo.Grades g
ON e.EnrollmentID = g.EnrollmentID


GROUP BY

c.CourseCode,
c.CourseName

ORDER BY

AverageGrade DESC;

Pass Rate each Course

SELECT

c.CourseCode,
c.CourseName,
COUNT(g.GradeID) AS TotalGrades,
SUM(
CASE

WHEN g.ResultStatus = 'Pass' THEN 1

ELSE 0

END
) AS PassedStudents,
SUM(
CASE
WHEN g.ResultStatus = 'Fail' THEN 1

ELSE 0

END

) AS FailedStudents,

ROUND(

100.0 *

SUM(


CASE

WHEN g.ResultStatus = 'Pass' THEN 1

ELSE 0

END

)


/ COUNT(g.GradeID),
2
) AS PassRatePercent

FROM dbo.Courses c

INNER JOIN dbo.Enrollments e
ON c.CourseID = e.CourseID
INNER JOIN dbo.Grades g
ON e.EnrollmentID = g.EnrollmentID

GROUP BY

c.CourseCode,
c.CourseName

ORDER BY

PassRatePercent DESC;

Attendance Rate each Course

SELECT

c.CourseCode,
c.CourseName,
COUNT(a.AttendanceID) AS TotalAttendanceRecords,
SUM(

CASE
WHEN a.AttendanceStatus = 'Present' THEN 1

ELSE 0

END

) AS PresentCount,

ROUND(
100.0 *
SUM(
CASE

WHEN a.AttendanceStatus = 'Present' THEN 1

ELSE 0
END

)
/ COUNT(a.AttendanceID),
2
) AS AttendanceRatePercent

FROM dbo.Courses c
INNER JOIN dbo.Enrollments e
ON c.CourseID = e.CourseID
INNER JOIN dbo.Attendance a
ON e.EnrollmentID = a.EnrollmentID

GROUP BY

c.CourseCode,
c.CourseName

ORDER BY

AttendanceRatePercent DESC;

Relationship of Attendance with Grade

SELECT


s.StudentID,
s.FirstName,
s.LastName,
c.CourseCode,
c.CourseName,
g.NumericGrade,

ROUND(
100.0 *
SUM(
CASE

WHEN a.AttendanceStatus = 'Present' THEN 1
ELSE 0
END
)
/ COUNT(a.AttendanceID),
2

) AS AttendanceRatePercent

FROM dbo.Students s

INNER JOIN dbo.Enrollments e
ON s.StudentID = e.StudentID
INNER JOIN dbo.Courses c
ON e.CourseID = c.CourseID
INNER JOIN dbo.Grades g
ON e.EnrollmentID = g.EnrollmentID
INNER JOIN dbo.Attendance a
ON e.EnrollmentID = a.EnrollmentID

GROUP BY

s.StudentID,
s.FirstName,
s.LastName,
c.CourseCode,
c.CourseName,
g.NumericGrade

ORDER BY

AttendanceRatePercent DESC;

Tuition Analysis base on Faculty

SELECT

f.FacultyName,
COUNT(DISTINCT s.StudentID) AS NumberOfStudents,
SUM(tp.AmountDue) AS TotalTuitionDue,
SUM(tp.AmountPaid) AS TotalTuitionPaid,
SUM(tp.AmountDue - tp.AmountPaid) AS OutstandingBalance

FROM dbo.Faculties f

INNER JOIN dbo.Students s
ON f.FacultyID = s.FacultyID
INNER JOIN dbo.TuitionPayments tp
ON s.StudentID = tp.StudentID

GROUP BY

f.FacultyName

ORDER BY

TotalTuitionPaid DESC;

Scholarship Analysis base on Department

SELECT

d.DepartmentName,
COUNT(DISTINCT ss.StudentID) AS ScholarshipStudents,
SUM(ss.AwardAmount) AS TotalScholarshipAmount,
ROUND(AVG(ss.AwardAmount), 2) AS AverageScholarshipAmount

FROM dbo.Departments d

INNER JOIN dbo.Students s
ON d.DepartmentID = s.DepartmentID
INNER JOIN dbo.StudentScholarships ss
ON s.StudentID = ss.StudentID


GROUP BY

d.DepartmentName

ORDER BY

TotalScholarshipAmount DESC;

Integrated Management Query

SELECT

f.FacultyName,
COUNT(DISTINCT s.StudentID) AS NumberOfStudents,
ROUND(AVG(s.GPA), 2) AS AverageGPA

FROM dbo.Faculties f

INNER JOIN dbo.Students s
ON f.FacultyID = s.FacultyID

GROUP BY

f.FacultyName

ORDER BY
AverageGPA DESC;

Number of Students, Average GPA, Minimum GPA, and Maximum GPA by Department

USE Campus360;

GO

SELECT

d.DepartmentName,
COUNT(s.StudentID) AS NumberOfStudents,
ROUND(AVG(s.GPA), 2) AS AverageGPA,
MIN(s.GPA) AS MinimumGPA,
MAX(s.GPA) AS MaximumGPA

FROM dbo.Departments d

INNER JOIN dbo.Students s
ON d.DepartmentID = s.DepartmentID

GROUP BY
d.DepartmentName
ORDER BY
AverageGPA DESC;

Number of Students by Faculty and Study Mode

SELECT

f.FacultyName,
s.StudyMode,
COUNT(*) AS NumberOfStudents

FROM dbo.Students s

INNER JOIN dbo.Faculties f
ON s.FacultyID = f.FacultyID

GROUP BY

f.FacultyName,
s.StudyMode

ORDER BY

f.FacultyName,
s.StudyMode;

Enrollment Status Analysis by Faculty

SELECT

f.FacultyName,
s.EnrollmentStatus,
COUNT(*) AS NumberOfStudents

FROM dbo.Students s

INNER JOIN dbo.Faculties f
ON s.FacultyID = f.FacultyID


GROUP BY


f.FacultyName,
s.EnrollmentStatus


ORDER BY


f.FacultyName,
NumberOfStudents DESC;

Departments with More Than 70 Students

SELECT


d.DepartmentName,
COUNT(s.StudentID) AS NumberOfStudents

FROM dbo.Departments d

INNER JOIN dbo.Students s
ON d.DepartmentID = s.DepartmentID


GROUP BY

d.DepartmentName
HAVING
COUNT(s.StudentID) > 70

ORDER BY

NumberOfStudents DESC;

Departments with an Average GPA Above 3.0

SELECT

d.DepartmentName,
COUNT(s.StudentID) AS NumberOfStudents,
ROUND(AVG(s.GPA), 2) AS AverageGPA

FROM dbo.Departments d

INNER JOIN dbo.Students s
ON d.DepartmentID = s.DepartmentID


GROUP BY

d.DepartmentName

HAVING
AVG(s.GPA) > 3.00

ORDER BY

AverageGPA DESC;

Tuition Revenue Analysis by Term

SELECT

AcademicYear,
Term,
COUNT(*) AS NumberOfPayments,
SUM(AmountDue) AS TotalAmountDue,
SUM(AmountPaid) AS TotalAmountPaid,
SUM(AmountDue - AmountPaid) AS OutstandingBalance,
ROUND(AVG(AmountPaid), 2) AS AveragePayment

FROM dbo.TuitionPayments

GROUP BY

AcademicYear,
Term

ORDER BY

AcademicYear,

Term;

Tuition Collection Rate

SELECT

Term,

SUM(AmountDue) AS TotalAmountDue,
SUM(AmountPaid) AS TotalAmountPaid,

ROUND(

100.0 * SUM(AmountPaid)
/ NULLIF(SUM(AmountDue), 0),
2
) AS CollectionRatePercent

FROM dbo.TuitionPayments

GROUP BY
Term

ORDER BY
Term;

Pass Rate by Department

SELECT

d.DepartmentName,
COUNT(g.GradeID) AS TotalGrades,
SUM(

CASE

WHEN g.ResultStatus = 'Pass' THEN 1
ELSE 0

END
) AS Passed,
SUM(

CASE

WHEN g.ResultStatus = 'Fail' THEN 1

ELSE 0

END
) AS Failed,
ROUND(
100.0 *
SUM(

CASE

WHEN g.ResultStatus = 'Pass' THEN 1
ELSE 0

END
)

/ COUNT(g.GradeID),
2
) AS PassRatePercent
FROM dbo.Departments d

INNER JOIN dbo.Courses c
ON d.DepartmentID = c.DepartmentID
INNER JOIN dbo.Enrollments e
ON c.CourseID = e.CourseID
INNER JOIN dbo.Grades g
ON e.EnrollmentID = g.EnrollmentID


GROUP BY
d.DepartmentName
ORDER BY
PassRatePercent DESC;

Attendance Summary by Department

SELECT

d.DepartmentName,
COUNT(a.AttendanceID) AS TotalAttendanceRecords,

SUM(
CASE
WHEN a.AttendanceStatus = 'Present' THEN 1

ELSE 0
END
) AS PresentCount,
SUM(

CASE
WHEN a.AttendanceStatus = 'Absent' THEN 1

ELSE 0

END

) AS AbsentCount,

SUM(

CASE
WHEN a.AttendanceStatus = 'Late' THEN 1

ELSE 0

END

) AS LateCount

FROM dbo.Departments d
INNER JOIN dbo.Courses c
ON d.DepartmentID = c.DepartmentID
INNER JOIN dbo.Enrollments e
ON c.CourseID = e.CourseID
INNER JOIN dbo.Attendance a
ON e.EnrollmentID = a.EnrollmentID
GROUP BY
d.DepartmentName
ORDER BY
PresentCount DESC;

Scholarship Summary by Scholarship Type

SELECT

sc.ScholarshipType,
COUNT(ss.StudentScholarshipID) AS NumberOfAwards,
SUM(ss.AwardAmount) AS TotalAwardAmount,
ROUND(AVG(ss.AwardAmount), 2) AS AverageAwardAmount,
MIN(ss.AwardAmount) AS MinimumAward,
MAX(ss.AwardAmount) AS MaximumAward

FROM dbo.Scholarships sc

INNER JOIN dbo.StudentScholarships ss
ON sc.ScholarshipID = ss.ScholarshipID
GROUP BY
sc.ScholarshipType
ORDER BY
TotalAwardAmount DESC;

Subqueries
- Students with a GPA Above the Overall Average

USE Campus360;

GO

SELECT

StudentID,
FirstName,
LastName,

GPA

FROM dbo.Students

WHERE GPA > (
SELECT AVG(GPA)
FROM dbo.Students
)
ORDER BY GPA DESC;

Courses with an Average Grade Above the Overall Course Average

SELECT

c.CourseCode,
c.CourseName,
AVG(g.NumericGrade) AS AverageGrade

FROM dbo.Courses c

INNER JOIN dbo.Enrollments e
ON c.CourseID = e.CourseID
INNER JOIN dbo.Grades g
ON e.EnrollmentID = g.EnrollmentID

GROUP BY

c.CourseCode,
c.CourseName
HAVING AVG(g.NumericGrade) >
(

SELECT AVG(NumericGrade)

FROM dbo.Grades

)

ORDER BY AverageGrade DESC;

Department Performance Summary

WITH DepartmentPerformance AS
(

SELECT

d.DepartmentName,
COUNT(s.StudentID) AS NumberOfStudents,
AVG(s.GPA) AS AverageGPA

FROM dbo.Departments d

INNER JOIN dbo.Students s
ON d.DepartmentID = s.DepartmentID

GROUP BY

d.DepartmentName

)

SELECT

DepartmentName,
NumberOfStudents,
ROUND(AverageGPA, 2) AS AverageGPA


FROM DepartmentPerformance

ORDER BY AverageGPA DESC;

Outstanding Tuition by Student

WITH StudentBalances AS

(

SELECT

StudentID,
SUM(AmountDue) AS TotalDue,
SUM(AmountPaid) AS TotalPaid,
SUM(AmountDue - AmountPaid) AS OutstandingBalance

FROM dbo.TuitionPayments


GROUP BY StudentID

)

SELECT

s.StudentID,
s.FirstName,
s.LastName,
sb.TotalDue,
sb.TotalPaid,
sb.OutstandingBalance

FROM StudentBalances sb

INNER JOIN dbo.Students s
ON sb.StudentID = s.StudentID
WHERE sb.OutstandingBalance > 0
ORDER BY sb.OutstandingBalance DESC;

Window Functions

- Student Ranking by GPA Within Each Department

SELECT

d.DepartmentName,
s.StudentID,
s.FirstName,
s.LastName,
s.GPA,
DENSE_RANK() OVER
(
PARTITION BY s.DepartmentID
ORDER BY s.GPA DESC
) AS DepartmentRank

FROM dbo.Students s


INNER JOIN dbo.Departments d
ON s.DepartmentID = d.DepartmentID

ORDER BY

d.DepartmentName,
DepartmentRank;

Faculty Ranking by Average GPA

WITH FacultyGPA AS
(

SELECT

f.FacultyName,
AVG(s.GPA) AS AverageGPA
FROM dbo.Faculties f
INNER JOIN dbo.Students s
ON f.FacultyID = s.FacultyID


GROUP BY f.FacultyName

)


SELECT

FacultyName,
ROUND(AverageGPA, 2) AS AverageGPA,
RANK() OVER
(

ORDER BY AverageGPA DESC
) AS FacultyRank

FROM FacultyGPA;

Student Academic Overview

CREATE VIEW dbo.vw_StudentAcademicOverview
AS

SELECT

s.StudentID,
s.StudentNumber,
s.FirstName,
s.LastName,
f.FacultyName,
d.DepartmentName,
p.ProgramName,
s.GPA,
s.StudyMode,
s.EnrollmentStatus


FROM dbo.Students s

INNER JOIN dbo.Faculties f
ON s.FacultyID = f.FacultyID
INNER JOIN dbo.Departments d
ON s.DepartmentID = d.DepartmentID
INNER JOIN dbo.Programs p
ON s.ProgramID = p.ProgramID;

GO

SELECT *

FROM dbo.vw_StudentAcademicOverview;

Course Performance

CREATE VIEW dbo.vw_CoursePerformance

AS

SELECT

c.CourseID,
c.CourseCode,
c.CourseName,
COUNT(e.EnrollmentID) AS NumberOfEnrollments,
AVG(g.NumericGrade) AS AverageGrade,


SUM(

CASE

WHEN g.ResultStatus = 'Pass' THEN 1

ELSE 0

END

) AS PassedStudents

FROM dbo.Courses c

INNER JOIN dbo.Enrollments e
ON c.CourseID = e.CourseID
INNER JOIN dbo.Grades g
ON e.EnrollmentID = g.EnrollmentID


GROUP BY

c.CourseID,
c.CourseCode,
c.CourseName;


GO

SELECT *

FROM dbo.vw_CoursePerformance
ORDER BY AverageGrade DESC;

Stored Procedures

- Student Information

CREATE PROCEDURE dbo.usp_GetStudentDetails

@StudentID INT

AS
BEGIN

SELECT

s.StudentID,
s.StudentNumber,
s.FirstName,
s.LastName,
f.FacultyName,
d.DepartmentName,
p.ProgramName,
s.GPA,
s.EnrollmentStatus


FROM dbo.Students s

INNER JOIN dbo.Faculties f
ON s.FacultyID = f.FacultyID
INNER JOIN dbo.Departments d
ON s.DepartmentID = d.DepartmentID
INNER JOIN dbo.Programs p
ON s.ProgramID = p.ProgramID

WHERE s.StudentID = @StudentID;

END;


GO

EXEC dbo.usp_GetStudentDetails @StudentID = 25;

Department Performance Report

CREATE PROCEDURE dbo.usp_GetDepartmentPerformance
@DepartmentID INT
AS

BEGIN

SELECT

d.DepartmentName,
COUNT(DISTINCT s.StudentID) AS NumberOfStudents,
ROUND(AVG(s.GPA), 2) AS AverageGPA,
MIN(s.GPA) AS MinimumGPA,
MAX(s.GPA) AS MaximumGPA

FROM dbo.Departments d

INNER JOIN dbo.Students s
ON d.DepartmentID = s.DepartmentID

WHERE d.DepartmentID = @DepartmentID

GROUP BY d.DepartmentName;

END;

GO


EXEC dbo.usp_GetDepartmentPerformance
@DepartmentID = 101;

KPI / Business Analysis Queries

- Academic Management KPIs

SELECT

COUNT(*) AS TotalStudents,
ROUND(AVG(GPA), 2) AS AverageGPA,
SUM(

CASE

WHEN EnrollmentStatus = 'Active'
THEN 1 ELSE 0

END
) AS ActiveStudents,


ROUND(
100.0 *
SUM(

CASE

WHEN EnrollmentStatus = 'Active'
THEN 1 ELSE 0

END

)

/ COUNT(*),
2
) AS ActiveStudentRatePercent


FROM dbo.Students;

Financial Management KPIs

SELECT

SUM(AmountDue) AS TotalTuitionDue,
SUM(AmountPaid) AS TotalTuitionCollected,
SUM(AmountDue - AmountPaid) AS OutstandingBalance,

ROUND(

100.0 * SUM(AmountPaid)
/ NULLIF(SUM(AmountDue), 0),
2
) AS TuitionCollectionRatePercent


FROM dbo.TuitionPayments;

Analysis:

- Campus360 | Executive Management Power BI Dashboard

Now that the database tables and SQL queries have been completed, I will use Power BI to develop an Executive Management Dashboard. The purpose of this dashboard is to provide university executives and decision-makers with quick access to key insights and essential performance metrics. Through effective data visualization, university management can gain a clear overview of the institution’s performance within seconds, covering key areas such as Students, Academic Performance, Attendance, Tuition, and Scholarships. By bringing these metrics together in a single executive-level dashboard, complex university data can be transformed into clear, actionable insights that support faster and more informed decision-making. First, we review the established relationships and joins in the Power BI data model to ensure that the tables are correctly connected and that the overall model structure is accurate.

The following three tables: fn_diagramobjects sysdiagrams vw_StudentAcademic are related to the SQL Server Database Diagram and are not required for Power BI analysis. Much of their information is already available in the core database tables. As part of the data-cleaning and model optimization process, these three tables can therefore be removed from the Power BI data model to keep it clean, relevant, and efficient.

Data Structure Required for the Dashboard

The main analytical paths for the dashboard should be structured as follows: Faculties │ ▼ Departments │ ├────────► Courses │ │ │ ▼ │ Enrollments │ / \ │ ▼ ▼ │ Grades Attendance │ ├────────► Programs │ └────────► Students │ ├────────► Enrollments ├────────► TuitionPayments └────────► StudentScholarships │ ▼ Scholarships For the Executive Dashboard, the most important analytical relationship paths are: Academic Students → Enrollments → Grades Attendance Students → Enrollments → Attendance Financial Students → TuitionPayments Scholarship Students → StudentScholarships → Scholarships

Power BI Data Modeling – Resolving Relationship Ambiguity

While developing the Campus360 Executive Management Dashboard, I identified a data modeling issue where the Faculty slicer did not correctly filter student-level metrics. As a result, the Total Students KPI displayed the overall student count (1,000) for every faculty instead of the corresponding faculty-level count. The issue was caused by relationship ambiguity in the Power BI data model. Activating the relationship between Departments and Students created two possible filter paths to the Enrollments table: Departments → Students → Enrollments Departments → Courses → Enrollments To resolve the ambiguity, I redesigned the active filter path around the student-centered analytical model. The Courses → Enrollments relationship was kept in the model but set to inactive, while the following relationships were maintained as active, one-to-many relationships with single-direction filtering: Faculties → Departments → Students → Enrollments ↓ Grades / Attendance Students → TuitionPayments Students → StudentScholarships After restructuring the relationships, the Faculty slicer correctly propagated its filter through Departments to Students, and the Total Students KPI dynamically returned the appropriate number of students for each selected faculty. Key Power BI concepts applied: Data Modeling, Relationship Cardinality, Active/Inactive Relationships, Filter Propagation, Single-Direction Filtering, Ambiguous Relationships, and Dimensional Modeling.

DAX

1-Total Students =DISTINCTCOUNT(Students[StudentID]) 2-Average GPA =AVERAGE(Students[GPA]) 3-Pass Rate % =DIVIDE(CALCULATE(COUNTROWS(Grades),Grades[ResultStatus] = “Pass”),COUNTROWS(Grades),0) 4- Attendance Rate % =DIVIDE(CALCULATE(COUNTROWS(Attendance),Attendance[AttendanceStatus] = “Present”),COUNTROWS(Attendance),0) 5- Tuition Collection Rate % =DIVIDE(SUM(TuitionPayments[AmountPaid]),SUM(TuitionPayments[AmountDue]),0) 6-Passed Enrollments =CALCULATE(COUNTROWS(Grades),Grades[ResultStatus] = “Pass”) 7-Failed Enrollments =CALCULATE(COUNTROWS(Grades),Grades[ResultStatus] = “Fail”) 8- Total Tuition Due =SUM(TuitionPayments[AmountDue]) 9- Total Tuition Collected =SUM(TuitionPayments[AmountPaid]) 10- Outstanding Balance =[Total Tuition Due] – [Total Tuition Collected] 11- Total Scholarship Amount =SUM(StudentScholarships[AwardAmount])

Executive Management Dashboard – Analytical Insights

The Executive Management Dashboard provides a consolidated view of Campus360’s key academic, financial, and student performance indicators, enabling management to evaluate institutional performance and compare results across faculties and departments. The university currently serves 1,000 students, with an overall average GPA of 3.06 and a 91.10% pass rate, indicating strong academic performance across the institution. The overall attendance rate of 82.00% suggests generally consistent student participation, although attendance should continue to be monitored alongside academic outcomes to identify departments or student groups that may require additional support. From a financial perspective, the university has achieved a tuition collection rate of 80.05%. While the majority of tuition has been collected, the remaining outstanding balance represents an opportunity to improve payment follow-up and financial planning. Scholarship analysis provides additional visibility into the distribution of full and partial scholarships, allowing management to assess how financial support is allocated across the student population. Faculty-level analysis reveals variations in student population, GPA, attendance, academic performance, and tuition collection. These comparisons enable management to identify high-performing faculties as well as areas where targeted academic, engagement, or financial interventions may be beneficial. Interactive filters for Faculty, Department, Academic Year, and Study Mode allow executives to move from an institution-wide overview to more focused analysis. For example, the Faculty of Engineering includes 213 students, with an average GPA of 3.10, a 91.39% pass rate, an 81.97% attendance rate, and an 81.46% tuition collection rate, demonstrating how the dashboard supports dynamic performance comparison across organizational units. Overall, the dashboard transforms interconnected academic and financial data into actionable management insights, supporting data-driven decision-making, performance monitoring, resource allocation, and early identification of areas requiring management attention.

Campus360 | Academic & Student Performance Tableau Dashboard & Python Analytics

I Analyze student academic performance, attendance behavior, course outcomes, and I identify students potentially at academic risk using Tableau and Python-assisted analytics.

For this dashboard, we primarily need the following tables: Students Departments Faculties Programs Enrollments Courses Grades Attendance The TuitionPayments and Scholarships tables are not required for this dashboard, as the primary focus is on Academic Performance.

DAX

Total Students:  COUNTD([StudentID])
Average GPA: AVG([GPA])
Average Grade: AVG([NumericGrade])

Pass Rate:

SUM(IF [ResultStatus] = "Pass"

THEN 1

ELSE 0

END

)

/
COUNT([GradeID])

Attendance Rate:

SUM(IF [AttendanceStatus] = "Present"

THEN 1

ELSE 0

END

)

/

COUNT([AttendanceID])

Academic & Student Performance – Analytical Insights

The Campus360 Academic & Student Performance Dashboard, developed in Tableau, provides an integrated view of student achievement, attendance, academic outcomes, course performance, and student risk across the university. The dashboard analyzes 1,000 students and enables interactive exploration by Faculty, Department, Course, and Study Mode. Overall academic performance is relatively stable, with an average GPA of 3.06 and an average numeric grade of 72.51. The overall pass rate is 91.10%, indicating that the large majority of assessed academic records resulted in successful outcomes. However, the grade distribution shows meaningful variation across letter-grade categories, demonstrating that the high overall pass rate does not necessarily imply uniformly strong academic performance. Student engagement represents another important dimension of performance. The university-wide attendance rate is 82.00%, meaning approximately 18% of recorded attendance opportunities were missed. When attendance is examined alongside academic results, it provides an additional indicator for identifying students who may require academic support rather than relying solely on GPA or final grades. To provide a more actionable view of student performance, students were classified into three risk categories using student-level GPA and attendance. The analysis identifies 531 students (53.1%) as At Risk, 453 students (45.3%) as Normal, and 16 students (1.6%) as High Risk. Although only a small proportion of students fall into the High Risk category, more than half are classified as At Risk under the defined thresholds, highlighting the importance of early intervention and continuous monitoring of attendance and academic progress. Department-level analysis shows that average GPA is concentrated around the 3.0 range, with relatively modest differences between departments. For example, visible departmental results include approximately 3.00 for Data Science, 3.05 for Public Health, and 3.07 for Business Administration. This suggests that university-wide academic performance is broadly consistent, while the interactive dashboard enables management to investigate smaller departmental variations and identify areas requiring further attention. Overall, the dashboard indicates a university with a strong 91.10% pass rate and a 3.06 average GPA, but also reveals potential performance risks that are less visible in aggregate KPIs. In particular, the combination of an 82.00% attendance rate and 53.1% of students classified as At Risk suggests that monitoring attendance and student-level performance can provide valuable early-warning signals. The dashboard therefore supports academic management in moving from high-level performance reporting toward data-driven identification of students, courses, and departments requiring targeted intervention. Portfolio note: Student risk classification is rule-based and uses student-level GPA and attendance thresholds; it should be interpreted as an early-warning analytical indicator rather than a predictive model.

Python Analytics:

1-Python Attendance-Grade Correlation

SCRIPT_REAL(

"
import numpy as np
x = np.array(_arg1, dtype=float)
y = np.array(_arg2, dtype=float)
mask = (~np.isnan(x)) & (~np.isnan(y))
if mask.sum() > 1:
r = float(np.corrcoef(x[mask], y[mask])[0,1])

else:

r = float('nan')

return [r] * len(_arg1)

",

[Attendance Rate],
[Average Grade]

)

Correlation KPI
IF FIRST() = 0 THEN
[Grade Correlation]

END

Python analytics was integrated with Tableau through TabPy to examine the relationship between student attendance and academic performance. A Pearson correlation analysis was performed using each student’s Attendance Rate and Average Grade. The analysis produced a correlation coefficient of r = 0.0011, indicating virtually no linear relationship between attendance rate and average grade within the Campus360 dataset. In other words, students with higher attendance rates did not consistently achieve higher grades, while lower attendance was not systematically associated with lower academic performance. This result suggests that attendance alone is not a strong predictor of academic achievement in this dataset. Other factors—such as GPA, course difficulty, department, study mode, and individual student characteristics—may provide greater explanatory value and should be considered in further analysis. From a management perspective, the finding demonstrates the importance of using multiple academic indicators rather than attendance alone when identifying students who may require academic support. Technical implementation: Tableau + Python (NumPy) + TabPy | Pearson Correlation | r = 0.0011

2-Predicted vs Actual Grade:

SCRIPT_REAL(

"
import numpy as np
from sklearn.linear_model import LinearRegression
x = np.array(_arg1, dtype=float).reshape(-1,1)
y = np.array(_arg2, dtype=float)
mask = ~(np.isnan(x[:,0]) | np.isnan(y))
result = np.full(len(y), np.nan)
if mask.sum() >= 2:
model = LinearRegression()
model.fit(x[mask], y[mask])
result[mask] = model.predict(x[mask])
return result.tolist()

",
[Student Attendance Rate],
AVG([NumericGrade])

)

The Predicted vs Actual Grade analysis uses a Python-based Linear Regression model integrated with Tableau through TabPy to estimate students’ academic grades based on their attendance rates. The actual average grades range approximately from 62 to 84, while the predicted grades are highly concentrated around 72–73, close to the overall average grade of 72.51. This limited variation in predicted values is consistent with the extremely weak correlation between attendance and academic performance (r ≈ 0.0011). The results indicate that attendance rate alone has very limited predictive power for student grades in this dataset. Although attendance remains an important academic engagement indicator, other factors such as GPA, course characteristics, academic history, and student-level attributes may be required to develop a more accurate predictive model. Key Insight: The model demonstrates an important analytical finding: a variable can be operationally important without necessarily being a strong standalone predictor of academic performance.

3-Student Risk Level vs Student Attendance Rate:

- Student Risk Level:

IF [Student GPA] < 2.0
OR [Student Attendance Rate] < 0.70
THEN "High Risk"
ELSEIF [Student GPA] < 2.5

OR [Student Attendance Rate] < 0.80

THEN "At Risk"

ELSE "Normal"

END

Student Attendance Rate:
{ FIXED [Student ID] :
SUM(
IF [Attendance Status] = "Present" THEN 1

ELSE 0

END

)

}

/
{ FIXED [Student ID] :
COUNT([Attendance ID])

}
Student GPA:
{ FIXED [Student ID] :
AVG([GPA])

}

This analysis segments students by academic risk level and compares their average GPA and attendance behavior. High-risk students show a substantially lower average GPA of approximately 1.90, despite maintaining an average attendance rate of about 82.5%. In comparison, At-Risk and Normal students demonstrate considerably stronger academic performance, with average GPAs of approximately 3.0 and 3.2, respectively. These results suggest that attendance alone may not fully explain academic risk and highlight the importance of combining multiple performance indicators when identifying students who may require early academic intervention.

4-Prediction Error / MAE:

- Prediction Error

ABS([Average Grade] - [Predicted Grade])
MAE
WINDOW_AVG([Prediction Error])
MAE KPI
IF FIRST() = 0 THEN
[MAE]


END

Model performance was evaluated using Mean Absolute Error (MAE), which measures the average absolute difference between predicted and actual student grades. The model achieved an MAE of 4.76 grade points, meaning that its predictions deviate from students’ actual grades by approximately 4.76 points on average. This provides a clear and interpretable measure of prediction accuracy and complements the Predicted vs Actual Grade analysis. By integrating Python-based Linear Regression through TabPy with Tableau, the analysis moves beyond descriptive reporting to predictive analytics while also quantitatively evaluating model performance.

Key Insight:

MAE = 4.76 indicates that the model’s grade predictions are, on average, approximately 4.76 points away from the actual grades

5-Multiple Linear Regression:

- Multiple Predicted Grade

SCRIPT_REAL(
"
import numpy as np
from sklearn.linear_model import LinearRegression
x1 = np.array(_arg1, dtype=float)
x2 = np.array(_arg2, dtype=float)
x3 = np.array(_arg3, dtype=float)
y  = np.array(_arg4, dtype=float)
X = np.column_stack((x1, x2, x3))

mask = (

~np.isnan(x1) &
~np.isnan(x2) &
~np.isnan(x3) &
~np.isnan(y)

)
result = np.full(len(y), np.nan)
if mask.sum() >= 4:
model = LinearRegression()
model.fit(X[mask], y[mask])
result[mask] = model.predict(X[mask])
return result.tolist()

",

AVG([Student Attendance Rate]),
MIN([Credits Earned]),
MIN([Current Semester]),
AVG([Numeric Grade])

)

Multiple Prediction Error

ABS(

[Average Grade] - [Multiple Predicted Grade]

)
Multiple Regression MAE
WINDOW_AVG([Multiple Prediction Error])
Multiple Regression MAE KPI
IF FIRST() = 0 THEN
[Multiple Regression MAE]

END

The Multiple Linear Regression model was developed to predict students’ academic grades using multiple explanatory variables, including Student Attendance Rate, Credits Earned, and Current Semester. The model achieved a Mean Absolute Error (MAE) of 4.756, indicating that its predicted grades differ from the actual grades by approximately 4.76 points on average. Compared with the previous Simple Linear Regression model, which produced an MAE of approximately 4.760, the multiple regression model shows only a marginal improvement of 0.004 points (approximately 0.08%). This suggests that adding Credits Earned and Current Semester provides very limited additional predictive value for this dataset. The result demonstrates an important modeling insight: adding more predictors does not necessarily lead to a meaningful improvement in predictive performance. In this case, the additional academic variables contribute little incremental information beyond the original model, suggesting that other factors may be required to substantially improve grade prediction accuracy. Key Insight: The Multiple Linear Regression model achieves an average prediction error of approximately 4.76 grade points, but its performance is almost identical to the simpler model. Therefore, based on MAE alone, the simpler model may be preferable due to its comparable accuracy and lower complexity.

6- Multiple Regression RMSE/ Multiple Regression R²

- Squared Prediction Error

POWER(
[Average Grade] - [Multiple Predicted Grade],
2

)
Multiple Regression RMSE
SQRT(
WINDOW_AVG([Squared Prediction Error])

)
Multiple Regression RMSE KPI
IF FIRST() = 0 THEN
[Multiple Regression RMSE]

END

The Multiple Linear Regression model achieved an MAE of 4.756 and an RMSE of 5.546. This indicates that predictions deviate from actual student grades by approximately 4.76 points on average, while the RMSE of 5.55 suggests that larger prediction errors remain relatively controlled.

Multiple Regression R²

SCRIPT_REAL(

"
import numpy as np
from sklearn.linear_model import LinearRegression
from sklearn.metrics import r2_score
x1 = np.array(_arg1, dtype=float)
x2 = np.array(_arg2, dtype=float)
x3 = np.array(_arg3, dtype=float)
y  = np.array(_arg4, dtype=float)
X = np.column_stack((x1, x2, x3))

mask = (

~np.isnan(x1) &
~np.isnan(x2) &
~np.isnan(x3) &
~np.isnan(y)

)

if mask.sum() >= 4:
model = LinearRegression()
model.fit(X[mask], y[mask])
predicted = model.predict(X[mask])
r2 = float(r2_score(y[mask], predicted))


else:

r2 = float('nan')
return [r2] * len(_arg1)

",

AVG([Student Attendance Rate]),
MIN([Credits Earned]),
MIN([Current Semester]),
AVG([Numeric Grade])

)

Multiple Regression R² KPI

IF FIRST() = 0 THEN
[Multiple Regression R²]

END

Simple Squared Prediction Error
POWER(
[Average Grade] - [Predicted Grade],
2
)

7-Simple Regression RMSE/ Simple Regression R²

- Simple Regression RMSE

SQRT(
WINDOW_AVG([Simple Squared Prediction Error])

)

- Simple Regression RMSE KPI

IF FIRST() = 0 THEN
[Simple Regression RMSE]

END

- Simple Regression R²

SCRIPT_REAL(

"

import numpy as np
from sklearn.linear_model import LinearRegression
from sklearn.metrics import r2_score
x = np.array(_arg1, dtype=float).reshape(-1,1)
y = np.array(_arg2, dtype=float)
mask = ~(np.isnan(x[:,0]) | np.isnan(y))
if mask.sum() >= 2:
model = LinearRegression()
model.fit(x[mask], y[mask])
predicted = model.predict(x[mask])
r2 = float(r2_score(y[mask], predicted))


else:

r2 = float('nan')
return [r2] * len(y)

",

AVG([Student Attendance Rate]),
AVG([Numeric Grade])

)

- Simple Regression R² KPI

IF FIRST() = 0 THEN
[Simple Regression R²]


END

8-Model Evaluation:

Model | MAE | RMSE | R² | | Simple Linear Regression | 4.760 | 5.548 | 0.000001186 | | Multiple Linear Regression | 4.756 | 5.546 | 0.000919827 | The predictive performance of both regression models was evaluated using Mean Absolute Error (MAE), Root Mean Squared Error (RMSE), and the Coefficient of Determination (R²). The Multiple Linear Regression model achieved the best overall performance, reducing MAE from 4.760 to 4.756 and RMSE from 5.548 to 5.546. Although these improvements are measurable, they are extremely small, indicating that the additional predictors contribute limited practical value. The R² values reveal the most important insight: both models explain less than 0.1% of the variance in student grades. This suggests that Attendance Rate, Credits Earned, and Current Semester alone are insufficient predictors of academic performance, and that other variables—such as assignment scores, study habits, or demographic and behavioral factors—would likely be required to build a substantially more accurate model. Key Insight: Model evaluation demonstrates that increasing model complexity does not necessarily improve predictive accuracy. A data-driven evaluation using MAE, RMSE, and R² is essential before selecting a predictive model.

Future Enhancements

Future development of the Campus360 analytics solution could focus on improving both the predictive performance of the models and the depth of student-level analysis. Since the current regression models explain only a very small proportion of the variation in student grades, additional academic and behavioral variables could be incorporated to capture more meaningful patterns in student performance. Potential enhancements include: Expanding Predictive Features: Incorporate additional variables such as assignment scores, quiz results, previous academic performance, course difficulty, study behavior, and historical attendance patterns to improve grade prediction. Feature Engineering: Develop new analytical features such as attendance trends, GPA changes over time, course completion ratios, accumulated credits, and semester-to-semester performance changes. Advanced Machine Learning Models: Compare Linear Regression with algorithms such as Random Forest, Gradient Boosting, and XGBoost to identify possible nonlinear relationships and improve predictive accuracy. Feature Importance Analysis: Evaluate the relative contribution of different predictors to identify the factors most strongly associated with student academic performance. Enhanced At-Risk Student Prediction: Develop a classification model that estimates the probability of a student becoming academically at risk, enabling earlier identification and intervention. Model Validation: Apply train/test splitting and cross-validation to evaluate how well predictive models generalize to unseen student data and reduce the risk of overfitting. Time-Series and Longitudinal Analysis: Track individual students across semesters to identify changes in GPA, attendance, credits earned, and academic risk over time. Real-Time Analytics: Integrate regularly updated university data so that dashboards and predictive indicators can support continuous academic monitoring and decision-making. Model Monitoring: Track MAE, RMSE, R², and other evaluation metrics over time and retrain models when significant changes in student behavior or data distributions are detected. Explainable AI: Incorporate techniques such as SHAP values to explain individual predictions and provide stakeholders with greater transparency into the factors influencing model outcomes. Future Goal: Transform Campus360 from a descriptive and experimental predictive analytics solution into an early-warning decision-support system capable of identifying academic risk, explaining the factors contributing to that risk, and supporting targeted student interventions.

Key points:

- End-to-End Analytics Solution: Developed Campus360, a fictional Canadian university management and analytics system, as an end-to-end portfolio project integrating SQL Server, Power BI, Tableau, Python, and TabPy.

- Relational Database Development: Designed and implemented a normalized SQL Server database covering students, faculties, departments, programs, professors, courses, enrollments, grades, attendance, tuition payments, and scholarships, with appropriate primary keys, foreign keys, and relationships.

- Large-Scale Synthetic Dataset: Built and analyzed a structured synthetic university dataset containing 1,000 students, approximately 3,000 enrollments, and 30,000 attendance records, together with academic, financial, and scholarship information.

- Data Quality & Relationship Validation: Performed validation checks to ensure referential integrity, correct student-course relationships, consistent department assignments, and reliable connections between academic tables before conducting downstream analysis.

- Business Intelligence & KPI Reporting: Developed interactive analytical views and KPI reporting to monitor student performance, attendance, academic progress, pass rates, and risk indicators.

- Student Risk Segmentation: Created a student-level risk classification framework combining academic performance and attendance information to categorize students into Normal, At Risk, and High Risk groups, supporting early identification of students requiring additional attention.

- Attendance–Grade Correlation: Python-based correlation analysis produced a coefficient of approximately 0.0011, indicating an extremely weak linear relationship between attendance rate and average grade in the synthetic dataset.

- Predictive Grade Modeling: Integrated Python with Tableau through TabPy to build predictive models for student grades, demonstrating how machine-learning calculations can be incorporated directly into interactive BI analytics.

- Simple Linear Regression: Developed a baseline model using Attendance Rate to predict student grades. The model achieved an MAE of 4.760, RMSE of 5.548, and R² of approximately 0.000001, indicating very limited predictive power.

- Multiple Linear Regression: Extended the predictive model using Attendance Rate, Credits Earned, and Current Semester. The model achieved an MAE of 4.756, RMSE of 5.546, and R² of approximately 0.00092.

- Model Comparison: Multiple Linear Regression slightly outperformed Simple Linear Regression; however, the improvement was negligible. MAE decreased by only 0.004 grade points, demonstrating that increasing model complexity did not produce a meaningful improvement in predictive accuracy.

- Important Modeling Insight: Both regression models explained less than 0.1% of the variation in student grades, indicating that the available predictors contain insufficient information to accurately explain academic performance.

- Data-Driven Model Evaluation: Evaluated predictive performance using multiple complementary metrics—MAE, RMSE, and R²—rather than relying on a single measure, enabling a more reliable assessment of model effectiveness.

- Actionable Analytical Finding: The weak correlation and low R² values suggest that additional variables—such as previous academic performance, assignment and exam results, course difficulty, study behavior, and historical performance trends—would be required to develop a stronger predictive model.

- Interactive Python Analytics Dashboard: Consolidated correlation analysis, predicted-versus-actual grades, at-risk student analysis, model evaluation KPIs, and model comparison into a dedicated Campus360 | Python Analytics Tableau dashboard.

- Future Analytics Potential: Established a foundation for extending Campus360 toward feature engineering, advanced machine-learning models, feature importance analysis, cross-validation, explainable AI, longitudinal analysis, and an academic early-warning system.

- Campus360 demonstrates the complete analytical workflow—from relational database design and data validation to business intelligence, statistical analysis, Python-powered predictive modeling, model evaluation, and actionable interpretation. Importantly, the project shows that a valuable analytical outcome is not necessarily a highly accurate model: identifying weak predictive relationships and determining what additional data is required are themselves meaningful data-driven findings.

Results & Recommendations:

- Establish an Early-Warning System for At-Risk Students: The analysis identified distinct Normal, At-Risk, and High-Risk student groups. The university should implement an early-warning system that continuously monitors academic performance and attendance and flags students who may require timely intervention.

- Provide Targeted Academic Support: Students classified as At Risk or High Risk should receive targeted support such as academic advising, tutoring, mentoring, study-planning assistance, and regular progress reviews rather than relying on institution-wide interventions.

- Avoid Using Attendance as a Standalone Performance Indicator: The Attendance–Grade correlation was approximately 0.0011, indicating virtually no linear relationship in the current dataset. Attendance should therefore be considered alongside broader academic and behavioral indicators rather than being used independently to assess student performance.

- Expand the Academic Data Collected: The regression models showed very low explanatory power, indicating that Attendance Rate, Credits Earned, and Current Semester alone are insufficient to explain differences in student grades. The university should incorporate additional variables such as exam results, assignment scores, previous GPA, course difficulty, failed or repeated courses, and historical academic performance.

- Monitor Student Performance Longitudinally: Instead of evaluating students only at a single point in time, the university should track changes in GPA, grades, attendance, earned credits, and risk status across semesters. Declining trends may provide earlier warning signals than absolute values alone.

- Develop Department- and Program-Level Performance Monitoring: Academic KPIs should be compared across faculties, departments, programs, and courses to identify areas with unusually high failure rates, low academic performance, or concentrations of at-risk students.

- Strengthen Course-Level Analytics: Courses with consistently low grades, high failure rates, or large numbers of at-risk students should be investigated further. This can help administrators identify potential issues related to course difficulty, curriculum design, assessment methods, or required student support.

- Use Multiple KPIs for Academic Decision-Making: Student performance should be evaluated through a combination of GPA, Average Grade, Pass Rate, Attendance Rate, Credits Earned, and Risk Level rather than a single KPI. This provides a more balanced picture of academic progress.

- Improve Predictive Models Before Operational Deployment: The Multiple Regression model achieved MAE = 4.756, RMSE = 5.546, and R² ≈ 0.00092. Although its prediction error is measurable, the extremely low R² shows that the current model does not adequately explain grade variation. It should therefore be treated as an analytical experiment rather than a production-grade decision model.

- Test More Advanced Predictive Approaches: After enriching the dataset, models such as Random Forest, Gradient Boosting, or XGBoost could be evaluated against Linear Regression. Model selection should be based on validated performance rather than model complexity.

- Introduce Model Validation and Monitoring: Predictive models should be evaluated using train/test splits, cross-validation, MAE, RMSE, R², and other relevant metrics before being used for university decisions. Their performance should also be monitored as new student data becomes available.

- Build Role-Specific Management Dashboards: Campus360 dashboards could be tailored for different stakeholders. University executives could monitor institution-level KPIs, department heads could analyze program and course performance, and academic advisors could focus on individual students requiring intervention.

- Integrate Academic and Financial Analytics: Because Campus360 also contains tuition and scholarship information, future analysis could examine relationships between scholarship support, tuition status, academic performance, retention, and student risk, providing a broader view of student success.

- Support Data-Driven Resource Allocation: Insights from student risk, course performance, and departmental KPIs can help management prioritize tutoring resources, academic advisors, student-support programs, and other interventions where they are most needed.

- Move Toward a Data-Driven Student Success System: The long-term recommendation is to develop Campus360 from a reporting solution into an integrated student-success decision-support system that combines descriptive analytics, predictive modeling, early-warning indicators, and targeted interventions.

- Campus360 demonstrates that effective university analytics should go beyond reporting historical performance. By integrating academic, attendance, enrollment, and student-level information, a university can identify emerging risks, evaluate academic outcomes, allocate support resources more effectively, and make evidence-based decisions. The current predictive results also highlight an important lesson: reliable student-success prediction requires richer and more meaningful data, not simply more complex models.

Data Dictionary:

The Data Dictionary documents the structure and purpose of each database table and field in the Campus360 University Management System. It includes column names, data types, primary keys, foreign keys, nullability, constraints, and field descriptions. The database currently includes the following core entities: Faculties, Departments, Programs, Professors, Students, Courses, Enrollments, Grades, Attendance, TuitionPayments, Scholarships, and StudentScholarships. The Data Dictionary helps ensure consistency, improves database maintainability, and makes the data model easier to understand for developers, analysts, and stakeholders.

Technology Stack:

Database Management System: Microsoft SQL Server Database Development Tool: SQL Server Management Studio (SSMS) Data Source Format: CSV Database Type: Relational Database Data Modeling: Entity Relationship Diagram (ERD) Data Architecture: Normalized relational schema Data Generation: Synthetic university data Future Analytics Integration: Power BI / Tableau

Action:

In this project, In this project, I used queries, Functions and Features to get my answers:

SQL Features:

The Campus360 University Management System demonstrates practical SQL Server skills across database design, data management, relational modeling, data validation, and business analysis. Database Design & Data Management Relational database design using Microsoft SQL Server Table creation and structured data modeling CSV data import and synthetic data generation Appropriate SQL data types PRIMARY KEY and FOREIGN KEY constraints UNIQUE and NOT NULL constraints Referential integrity and relationship validation One-to-One, One-to-Many, and Many-to-Many relationships Bridge tables for resolving Many-to-Many relationships Entity Relationship Diagram (ERD) Data Retrieval & Filtering SELECT, WHERE, DISTINCT, ORDER BY, CASE WHEN, Multi-table queries, JOIN Operations, INNER JOIN, LEFT JOIN, Multi-table JOINs, Student–Course relationships through Enrollments, tudent–Scholarship relationships through StudentScholarships Aggregation & Business Analysis GROUP BY, HAVING, COUNT(), COUNT(DISTINCT), SUM(), AVG(), MIN(), MAX() Conditional aggregation Percentage and rate calculations NULLIF() for safe calculations Advanced SQL Subqueries Common Table Expressions (CTEs) Window Functions ROW_NUMBER(), RANK(), DENSE_RANK(), Views Stored Procedures, Parameterized Stored Procedures Data Validation & Analytical SQL Data quality checks Foreign Key validation Duplicate prevention using Unique Constraints Academic performance analysis Enrollment analysis Attendance analysis Tuition and outstanding balance analysis Scholarship analysis KPI calculations Management-level reporting queries

Power BI Features:

- Data Modeling

Relationships between fact and dimension tables One-to-Many relationships Many-to-Many relationships where required Primary Key / Foreign Key based model connections

- DAX Measures

DISTINCTCOUNT() COUNTROWS() SUM() AVERAGE() DIVIDE() CALCULATE() FILTER() IF() SWITCH()

- Calculated KPIs

Student Count Average GPA Average Grade Attendance Rate Pass Rate At-Risk Student metrics Credits-related academic KPIs

- Filtering & Interactivity

Slicers Visual-level Filters Page-level Filters Report-level Filters Cross-filtering between visuals

- Dashboard Visualizations

KPI Cards Bar Charts Column Charts Pie / Donut Charts Tables Matrix visuals Interactive dashboard layouts

- Formatting & Reporting

Conditional Formatting Data Labels Custom Titles Number Formatting Percentage Formatting Dashboard layout and visual hierarchy

Tableau Features:

- Calculated Fields

Student-level calculated metrics Academic KPIs Risk classification Prediction Error calculations Model Evaluation metrics

- Level of Detail Calculations

{ FIXED [Student Number] : … } Student-level GPA calculations Student-level Attendance Rate calculations Preventing aggregation issues caused by joined tables

- Table Calculations

WINDOW_AVG() FIRST() Compute Using Specific Dimensions Student Number based partitioning

- Mathematical Functions

ABS() POWER() SQRT() AVG() MIN() SUM()

- Logical Functions

IF … THEN … ELSE … END Conditional Risk Classification Conditional KPI display using FIRST() = 0

Python Integration with TabPy

SCRIPT_REAL() Passing Tableau aggregated fields into Python Returning Python lists to Tableau Using Python predictions inside Tableau worksheets Regression Analytics Predicted Grade Simple Linear Regression Multiple Linear Regression Prediction Error MAE RMSE R²

- Correlation Analysis

Attendance–Grade Correlation Global correlation calculation Correlation KPI

- Student Risk Analytics

Student Risk Level Normal At Risk High Risk Student-level segmentation based on academic and attendance measures

- Filters / Slicers

Filters shelf Show Filter Single Value Dropdown Multiple Values Dropdown Apply to Worksheets All Using This Data Source

- Marks Card

Text Detail Tooltip Color Size

- Dashboard Features

Tiled Dashboard Layout KPI Cards Dashboard Filters Multiple Worksheet Integration Model Comparison Correlation KPI Predicted vs Actual visualization At-Risk Student Analysis

Python Features:

- Libraries Used

- - NumPy

import numpy as np Array creation Numeric conversion Missing-value handling Predictor matrix construction

- - scikit-learn

LinearRegression r2_score

- NumPy Functions & Operations

np.array() np.isnan() np.full() np.nan np.column_stack() .reshape() .tolist()

- Boolean / Data Filtering Operations

Boolean masks Missing-value filtering Combined conditions using: & | ~

Simple Linear Regression

- Main methods:

LinearRegression() .fit() .predict()

Model structure

Multiple Linear Regression Predictors used: Student Attendance Rate Credits Earned Current Semester Target variable: Average Grade

Model Evaluation

- Metrics calculated:

MAE RMSE R²

- Tableau–Python Integration

TabPy Server Port 9004 SCRIPT_REAL() Passing Tableau data to Python through: _arg1 _arg2 _arg3 _arg4 Returning Python-compatible values: float() .tolist() This was especially important because NumPy objects such as ndarray cannot be returned directly to Tableau.

Error Handling / Data Preparation

Missing value exclusion Minimum observation checks Returning np.nan when insufficient data exists Converting NumPy results into JSON-compatible Python values

Link:

Thank you so much for reading my project. I will be happy to receive your feedback and opinion about the project. If this project is useful for you, click on this link: Link to Project Post on LinkedIn