Changes between Version 1 and Version 2 of DatabaseCreation

Timestamp:

07/01/26 17:46:23 (4 days ago)

Author:

231082

Comment:

--

DatabaseCreation

@@ -70 +70 @@
 
 PostgreSQL stores only a 4‑byte numeric OID per value rather than the literal string. In contrast, a `TEXT` column with a `CHECK` constraint would store variable‑length strings (10‑12 bytes per value).
+
+
+== Data Population ==
+
+=== Data Generation Approach ===
+
+The database was populated using a combination of:
+
+- '''Local Large Language Models with Tool Calling''' – CSV data was generated programmatically, producing realistic academic data including member names, course codes, enrollment patterns, and grade distributions.
+
+- '''Python’s faker Library''' – Supplemented data generation for realistic names, dates, and other attributes where a more controlled generator was needed.
+
+=== Database Size ===
+
+The final database contains approximately 13 GB of data across 30 tables. The largest tables are:
+
+|| '''Table''' || '''Row Count''' || '''Total Size''' || '''Data Size''' || '''Index Size''' ||
+|| student_answer || 26,574,600 || 4,230 MB || 3,194 MB || 1,036 MB ||
+|| member_message || 19,999,100 || 3,113 MB || 2,512 MB || 602 MB ||
+|| exam_attempt || 8,858,161 || 853 MB || 509 MB || 345 MB ||
+|| survey_response || 8,447,940 || 1,079 MB || 551 MB || 528 MB ||
+|| exercise_submission || 7,353,124 || 1,617 MB || 1,327 MB || 290 MB ||
+|| semester_enrollment || 4,387,348 || 373 MB || 185 MB || 188 MB ||
+|| course_enrollment || 2,214,813 || 217 MB || 94 MB || 123 MB ||
+|| student_grade || 1,087,533 || 77 MB || 46 MB || 31 MB ||
+|| member || 1,050,000 || 173 MB || 105 MB || 69 MB ||
+|| program_enrollment || 1,049,789 || 118 MB || 55 MB || 62 MB ||
+|| student || 1,000,000 || 110 MB || 42 MB || 67 MB ||
+...and 20 additional tables
+
+'''Total Database Size: 13 GB'''
+
+The large volume of data ensures that performance testing and query optimization exercises reflect realistic production conditions.
+
+=== Trimming the Database ===
+
+Initially, the database grew to approximately 63 GB. During a laboratory session, it was reduced to its current 13 GB size to ensure manageable operations while maintaining sufficient data volume for meaningful performance analysis. The database remained local throughout the project after disk space constraints were addressed.
+
+== Views Overview ==
+
+=== Non‑Analytical Views ===
+
+The system includes several non‑analytical views (no big `COUNT`, `SUM`, `MIN`, `MAX` aggregates) for operational queries:
+- `v_student_current_semester` – Student’s current semester course enrollments
+- `v_student_upcoming_deadlines` – Upcoming exercises and exams
+- `v_student_transcript` – Complete academic transcript
+- `v_course_announcements` – Course announcements with threading
+- `v_exercise_grading_queue` – Pending and graded submissions
+- `v_course_prerequisite_chain` – Recursive prerequisite tree
+- `v_student_registration_state` – Course registration eligibility (later replaced by a function)
+- `v_teaching_assistant_eligibility` – TA eligibility for course editions (later replaced by a function)
+
+=== Analytical Views ===
+
+Additional analytical views (with aggregates) are provided as extras:
+
+- `v_course_edition_overview` – Enrollment and teacher statistics
+- `v_survey_status` – Survey response counts and capacity
+- `v_teacher_dashboard` – Teacher activity metrics
+- `v_exam_results_summary` – Exam statistics and pass rates
+- `v_member_inbox` – Message thread summaries
+- `v_program_catalog` – Program statistics
+- `v_course_catalog` – Course offering history
+- `v_exam_attempt_detail` – Detailed exam attempt analysis
+- `v_program_completion_status` – Graduation eligibility tracking