Advanced Reports

Report 1: Academic Resource Distribution

Data requirements idea/concept title

Faculty and Staffing Overview by University

Data requirements description

This report summarizes how many professors and subjects are assigned to each faculty within every university. It utilizes existing relationships between the University, Faculty, Professor, and Subject tables to provide a distribution matrix of academic resources.

Solution SQL

SELECT u.Name AS university_name, f.Name AS faculty_name, COUNT(DISTINCT p.Id) AS total_professors, COUNT(DISTINCT s.Id) AS total_subjects 
FROM University u 
JOIN Faculty f ON u.Id = f.University_Id 
LEFT JOIN Professor p ON f.Id = p.Faculty_Id 
LEFT JOIN Subject s ON f.Id = s.Faculty_Id
GROUP BY u.Name, f.Name
ORDER BY total_professors DESC;

Solution Relational Algebra

γ_{u.Name, f.Name, COUNT(p.Id) → total_professors, COUNT(s.Id) → total_subjects} 
(University ⋈_{u.Id = f.University_Id} Faculty ⋈_{f.Id = p.Faculty_Id} Professor ⋈_{f.Id = s.Faculty_Id} Subject)

Report 2: Student Activity and Advising Monitor

Data requirements idea/concept title

Student Engagement Across Enrollment and Advice Modules

Data requirements description

This report combines two separate many-to-many relationship structures (Student_Subject and Advice) into a unified diagnostic view. It displays each student's name, index, and home faculty along with the count of distinct subjects they are actively enrolled in and total academic advising sessions logged.

Solution SQL

SELECT s.Name || ' ' || s.Surname AS student_name, s.Student_Index, f.Name AS faculty_name, COUNT(DISTINCT ss.Subject_Id) AS enrolled_subjects, COUNT(DISTINCT a.Professor_Id) AS advice_requests
FROM Student s 
JOIN Faculty f ON s.Faculty_Id = f.Id 
LEFT JOIN Student_Subject ss ON s.Id = ss.Student_Id 
LEFT JOIN Advice a ON s.Id = a.Student_Id
GROUP BY s.Id, s.Name, s.Surname, s.Student_Index, f.Name
ORDER BY enrolled_subjects DESC;

Solution Relational Algebra

γ_{s.Name, s.Surname, s.Student_Index, f.Name, COUNT(ss.Subject_Id) → enrolled_subjects, COUNT(a.Professor_Id) → advice_requests} 
(Student ⋈_{s.Faculty_Id = f.Id} Faculty ⋈_{s.Id = ss.Student_Id} Student_Subject ⋈_{s.Id = a.Student_Id} Advice)

Advanced Reports AI Usage

Name of AI service: Gemini

URL: ​https://gemini.google.com/

Type of service: Free Tier

Final result: The AI was utilized to troubleshoot Trac Wiki rendering syntax issues, cross-reference query definitions with strict production schema tables, and format relational algebra groupings safely.

Results in details:

• Debugging Wiki Syntax: After identifying formatting block constraint errors within the wiki parser engine, I used the AI to refine character spacing configurations and clean up block parameters.
• Schema Verification: I used the AI to audit my outer join parameters. It helped verify that I correctly mapped standard casing names (like using Student_Index instead of index_number, and Student_Id instead of stud_id), bringing the reports into perfect synchronization with my DDL code files.
• Relational Algebra Formatting: The AI assisted in writing out formalized relational algebra sequences using projection, conditional theta-joins, and aggregate extensions ($\gamma$) that accurately express the logic of our underlying SQL queries.