= 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 uses existing relationships between the university, faculty, professor, and subject tables to provide a "snapshot" of the university's size.

=== 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.universityid LEFT JOIN professor p ON f.id = p.facultyid LEFT JOIN subject s ON f.id = s.facultyid
GROUP BY u.name, f.name
ORDER BY total_professors DESC;
}}}

=== Solution Relational Algebra ===

{{{
gamma_{u.name, f.name, count(p.id), count(s.id)}
(University |><| Faculty |><| Professor |><| 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 relationships (student_subject and advice) into one view. It shows each student's home faculty, how many subjects they are currently enrolled in, and how many times they have requested advice from a professor.

=== Solution SQL ===

{{{
#!sql
SELECTs.name || ' ' || s.surname AS student_name, s.index_number, f.name AS faculty_name, COUNT(DISTINCT ss.subj_id) AS enrolled_subjects, COUNT(DISTINCT a.id) AS advice_requests
FROM student s JOIN faculty f ON s.facultyid = f.id LEFT JOIN student_subject ss ON s.id = ss.stud_id LEFT JOIN advice a ON s.id = a.student_id
GROUP BY s.id, s.name, s.surname, s.index_number, f.name
ORDER BY enrolled_subjects DESC;
}}}

=== Solution Relational Algebra ===

{{{
gamma_{s.name, s.index_number, count(ss.subj_id), count(a.id)}
(Student |><| Faculty |><| Student_Subject |><| Advice)
}}}


[[br]]

[[br]]

[[br]]

= Advanced Reports AI Usage =

Name of AI service: Gemini 3 Flash

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

Type of service: Free Tier

Final result: The AI was utilized to troubleshoot Trac Wiki rendering issues and to cross-reference my SQL logic against the established database schema to ensure naming consistency.

Results in details:

- Debugging Wiki Syntax: After encountering a "Failed to load processor" error in the wiki editor, I used the AI to identify that a specific newline character was missing after the #!sql declaration.

- Schema Verification: I used the AI to double-check the join conditions between my junction tables. It helped verify that I was correctly using student_id for the advice table and stud_id for the student_subject table, matching my existing database implementation.

- Relational Algebra Formatting: The AI assisted in converting my logical steps into a standard text-based format (gamma and |><|) that would display correctly within the university's wiki environment without breaking the layout.

Entire AI usage log:

- [User provided current table structures and SQL drafts]

- [AI identified syntax errors in the Wiki markup]

- [User and AI collaborated to ensure SQL queries aligned with existing project code without adding unnecessary features]