= 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 ===
{{{
#!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 ===
{{{
#!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)
}}}

[[br]]

[[br]]

[[br]]

= 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.