Changes between Version 1 and Version 2 of P6
- Timestamp:
- 06/25/26 15:51:20 (13 hours ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
P6
v1 v2 4 4 5 5 === Data requirements idea/concept title === 6 7 6 '''Faculty and Staffing Overview by University''' 8 7 9 8 === Data requirements description === 10 11 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. 9 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. 12 10 13 11 === Solution SQL === 14 15 12 {{{ 16 13 #!sql 17 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 18 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 19 GROUP BY u.name, f.name 14 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 15 FROM University u 16 JOIN Faculty f ON u.Id = f.University_Id 17 LEFT JOIN Professor p ON f.Id = p.Faculty_Id 18 LEFT JOIN Subject s ON f.Id = s.Faculty_Id 19 GROUP BY u.Name, f.Name 20 20 ORDER BY total_professors DESC; 21 21 }}} 22 22 23 23 === Solution Relational Algebra === 24 25 24 {{{ 26 gamma_{u.name, f.name, count(p.id), count(s.id)} 27 (University |><| Faculty |><| Professor |><|Subject)25 γ_{u.Name, f.Name, COUNT(p.Id) → total_professors, COUNT(s.Id) → total_subjects} 26 (University ⋈_{u.Id = f.University_Id} Faculty ⋈_{f.Id = p.Faculty_Id} Professor ⋈_{f.Id = s.Faculty_Id} Subject) 28 27 }}} 29 28 … … 31 30 32 31 === Data requirements idea/concept title === 33 34 32 '''Student Engagement Across Enrollment and Advice Modules''' 35 33 36 34 === Data requirements description === 37 38 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. 35 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. 39 36 40 37 === Solution SQL === 41 42 38 {{{ 43 39 #!sql 44 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 45 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 46 GROUP BY s.id, s.name, s.surname, s.index_number, f.name 40 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 41 FROM Student s 42 JOIN Faculty f ON s.Faculty_Id = f.Id 43 LEFT JOIN Student_Subject ss ON s.Id = ss.Student_Id 44 LEFT JOIN Advice a ON s.Id = a.Student_Id 45 GROUP BY s.Id, s.Name, s.Surname, s.Student_Index, f.Name 47 46 ORDER BY enrolled_subjects DESC; 48 47 }}} 49 48 50 49 === Solution Relational Algebra === 51 52 50 {{{ 53 gamma_{s.name, s.index_number, count(ss.subj_id), count(a.id)} 54 (Student |><| Faculty |><| Student_Subject |><|Advice)51 γ_{s.Name, s.Surname, s.Student_Index, f.Name, COUNT(ss.Subject_Id) → enrolled_subjects, COUNT(a.Professor_Id) → advice_requests} 52 (Student ⋈_{s.Faculty_Id = f.Id} Faculty ⋈_{s.Id = ss.Student_Id} Student_Subject ⋈_{s.Id = a.Student_Id} Advice) 55 53 }}} 56 57 54 58 55 [[br]] … … 64 61 = Advanced Reports AI Usage = 65 62 66 Name of AI service: Gemini 3 Flash 63 '''Name of AI service:''' Gemini 67 64 68 URL:https://gemini.google.com/65 '''URL:''' https://gemini.google.com/ 69 66 70 Type of service:Free Tier67 '''Type of service:''' Free Tier 71 68 72 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.69 '''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. 73 70 74 Results in details: 75 76 - 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. 77 78 - 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. 79 80 - 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. 81 82 Entire AI usage log: 83 84 - [User provided current table structures and SQL drafts] 85 86 - [AI identified syntax errors in the Wiki markup] 87 88 - [User and AI collaborated to ensure SQL queries aligned with existing project code without adding unnecessary features] 71 '''Results in details:''' 72 * '''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. 73 * '''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. 74 * '''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.
