= 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)
}}}

== Report 3: Current Semester Completed Subjects and GPA ==

=== Data requirements idea/concept title ===
'''Students Who Completed All Currently Enrolled Semester Courses with GPA'''

=== Data requirements description ===
This complex report identifies students who have successfully completed and graded all courses they were enrolled in for the current semester (meaning no active/failed grades left), showing their average grade (GPA) for those subjects. It uses subqueries and group constraints to filter out students with pending grades.

=== Solution SQL ===
{{{
#!sql
SELECT s.Student_Index, s.Name || ' ' || s.Surname AS student_name, AVG(ss.Final_Grade) AS semester_gpa, COUNT(ss.Subject_Id) AS completed_courses_count
FROM Student s
JOIN Student_Subject ss ON s.Id = ss.Student_Id
JOIN Subject sub ON ss.Subject_Id = sub.Id
WHERE sub.Semester = 'Summer'
  AND ss.Status = 'Completed'
  AND ss.Final_Grade IS NOT NULL
GROUP BY s.Student_Index, s.Name, s.Surname
HAVING COUNT(ss.Subject_Id) = (
    SELECT COUNT(*) 
    FROM Student_Subject ss2
    WHERE ss2.Student_Id = s.Id
)
ORDER BY semester_gpa DESC;
}}}

=== Solution Relational Algebra ===
{{{
γ_{s.Student_Index, s.Name, s.Surname, AVG(ss.Final_Grade) → semester_gpa, COUNT(ss.Subject_Id) → completed_courses_count}
(σ_{sub.Semester='Summer' ∧ ss.Status='Completed'} (Student ⋈_{s.Id=ss.Student_Id} Student_Subject ⋈_{ss.Subject_Id=sub.Id} Subject))
}}}

== Report 4: Top Performer (Highest GPA) by Enrollment Year ==

=== Data requirements idea/concept title ===
'''Yearly Academic Champions'''

=== Data requirements description ===
This advanced analytical report identifies the top student (having the absolute highest GPA) for each enrollment year cohort. It utilizes windowing functions or correlated subqueries to dynamically partition students by year and isolate the single record with the highest average grade.

=== Solution SQL ===
{{{
#!sql
WITH StudentGPA AS (
    SELECT s.Student_Index, s.Name || ' ' || s.Surname AS student_name, 
           EXTRACT(YEAR FROM ss.Enrollment_Date) AS enrollment_year, 
           AVG(ss.Final_Grade) AS cumulative_gpa,
           RANK() OVER (PARTITION BY EXTRACT(YEAR FROM ss.Enrollment_Date) ORDER BY AVG(ss.Final_Grade) DESC) as gpa_rank
    FROM Student s
    JOIN Student_Subject ss ON s.Id = ss.Student_Id
    WHERE ss.Final_Grade IS NOT NULL
    GROUP BY s.Student_Index, s.Name, s.Surname, EXTRACT(YEAR FROM ss.Enrollment_Date)
)
SELECT enrollment_year, Student_Index, student_name, cumulative_gpa
FROM StudentGPA
WHERE gpa_rank = 1
ORDER BY enrollment_year DESC;
}}}

=== Solution Relational Algebra ===
{{{
τ_{enrollment_year DESC} (
  π_{enrollment_year, Student_Index, student_name, cumulative_gpa} (
    σ_{gpa_rank = 1} (
      γ_{enrollment_year, Student_Index, student_name, AVG(ss.Final_Grade) → cumulative_gpa, RANK() → gpa_rank} (
        Student ⋈_{s.Id = ss.Student_Id} Student_Subject
      )
    )
  )
)
}}}

[[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, design highly complex analytical reports (GPA tracking, cohorts), 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.
* '''Analytical Report Design:''' The AI assisted in writing complex queries (using Window Functions like RANK OVER PARTITION, EXTRACT, and HAVING clauses) to generate precise semester-by-semester GPA aggregates and student yearly performance matrices.
* '''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 (γ) that accurately express the logic of our underlying SQL queries.