Changes between Initial Version and Version 1 of P6

Timestamp:

05/16/26 10:40:15 (11 days ago)

Author:

216009

Comment:

--

P6

@@ +1 @@
+= 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]