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

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

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)

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]