Comprehensive Database Structure Prompt for College Examination System (COE)
Objective: To create a normalized relational database schema for a complete College Examination Management System. This includes academic structure, fee management, student/staff data, exam creation, detailed mark entry by question, internal/semester grading, hall allocation, question paper processing, valuation, and result publication.

ENHANCED HALL ALLOCATION SYSTEM:
- Automatic hall allocation based on exam selection
- Course-based and room-based seating reports
- Comprehensive seating management with detailed reports
- Real-time allocation generation and preview

COMPREHENSIVE FOREIGN KEY MAPPING:

1. colleges (college_id) - Referenced by:
   - courses.college_id (fk_courses_college)
   - students.college_id (fk_students_college)
   - staff.college_id (fk_staff_college)
   - halls.college_id (fk_halls_college)

2. categories (category_id) - Referenced by:
   - courses.category_id (fk_courses_category)

3. courses (course_id) - Referenced by:
   - subjects.course_id (fk_subjects_course)
   - grading_systems.course_id (fk_grading_systems_course)
   - students.course_id (fk_students_course)
   - course_fee_structure.course_id (fk_cfs_course)

4. subjects (subject_id) - Referenced by:
   - exam_subjects.subject_id (fk_exam_subjects_subject)
   - subject_assessment_settings.subject_id (fk_sas_subject)
   - co_po_mapping.subject_id (fk_copo_subject)
   - internal_exams.subject_id (fk_ie_subject)
   - internal_marks_summary.subject_id (fk_ims_subject)
   - subject_results.subject_id (fk_sr_subject2)

5. grading_systems (grading_system_id) - Referenced by:
   - grades.grading_system_id (fk_grades_system)

6. grades (grade_id) - Referenced by:
   - student_marks_summary.grade_id (fk_sms_grade)
   - subject_results.grade_id (fk_sr_grade2)

7. fee_types (fee_type_id) - Referenced by:
   - course_fee_structure.fee_type_id (fk_cfs_fee_type)

8. students (student_id) - Referenced by:
   - student_registrations.student_id (fk_sr_student)
   - internal_marks.student_id (fk_im_student)
   - internal_marks_summary.student_id (fk_ims_student)
   - subject_results.student_id (fk_sr_student2)
   - semester_results.student_id (fk_semres_student)
   - certificate_issuance.student_id (fk_ci_student)

9. staff (staff_id) - Referenced by:
   - question_paper_orders.setter_id (fk_qpo_setter)
   - question_paper_orders.approver_id (fk_qpo_approver)
   - student_question_marks.valuator_id (fk_sqm_valuator)

10. halls (hall_id) - Referenced by:
    - hall_settings.hall_id (fk_hs_hall)
    - hall_allocations.hall_id (fk_ha_hall)

11. hall_allocations (allocation_id) - Referenced by:
    - student_seating.allocation_id (fk_ss_allocation)

12. exam_timetable (timetable_id) - Referenced by:
    - hall_allocations.timetable_id (fk_ha_timetable)
   - internal_exams.conducted_by (fk_ie_conducted_by)
   - external_staff_applications.staff_id (fk_esa_staff)
   - external_staff_applications.approved_by (fk_esa_approved_by)
   - valuation_rooms.incharge_id (fk_vr_incharge)
   - valuator_allocation.valuator_id (fk_va_valuator)
   - valuation_salary.staff_id (fk_vs_staff)
   - invigilation_duty.staff_id (fk_id_staff)

10. exam_types (exam_type_id) - Referenced by:
    - exams.exam_type_id (fk_exams_exam_type)
    - internal_exams.exam_type_id (fk_ie_exam_type)

11. exams (exam_id) - Referenced by:
    - exam_subjects.exam_id (fk_exam_subjects_exam)
    - external_staff_applications.exam_id (fk_esa_exam)
    - subject_results.exam_id (fk_sr_exam2)
    - semester_results.exam_id (fk_semres_exam)

12. exam_subjects (exam_subject_id) - Referenced by:
    - student_registrations.exam_subject_id (fk_sr_exam_subject)
    - question_patterns.exam_subject_id (fk_qp_exam_subject)
    - question_paper_orders.exam_subject_id (fk_qpo_exam_subject)
    - exam_timetable.exam_subject_id (fk_tt_exam_subject)
    - valuator_allocation.exam_subject_id (fk_va_exam_subject)
    - valuation_salary.exam_subject_id (fk_vs_exam_subject)

13. student_registrations (registration_id) - Referenced by:
    - student_question_marks.registration_id (fk_sqm_registration)
    - student_marks_summary.registration_id (fk_sms_registration)
    - student_seating.registration_id (fk_ss_registration)
    - revaluation_applications.registration_id (fk_ra_registration)

14. question_patterns (pattern_id) - Referenced by:
    - pattern_sections.pattern_id (fk_ps_pattern)
    - question_paper_orders.final_pattern_id (fk_qpo_final_pattern)

15. halls (hall_id) - Referenced by:
    - hall_allocations.hall_id (fk_ha_hall)
    - invigilation_duty.hall_id (fk_id_hall)

16. exam_timetable (timetable_id) - Referenced by:
    - hall_allocations.timetable_id (fk_ha_timetable)
    - invigilation_duty.timetable_id (fk_id_timetable)

17. hall_allocations (allocation_id) - Referenced by:
    - student_seating.allocation_id (fk_ss_allocation)

18. valuation_rooms (room_id) - Referenced by:
    - valuator_allocation.room_id (fk_va_room)

19. internal_exams (internal_exam_id) - Referenced by:
    - internal_marks.internal_exam_id (fk_im_internal_exam)

Module 1: Academic Structure
1. Table: colleges
- college_id (PK, Auto Increment, INT)
- college_code (VARCHAR(10), UNIQUE) // e.g., 'COE01'
- college_name (VARCHAR(255)) // e.g., 'College of Engineering'
- status (ENUM('Active', 'Inactive')) DEFAULT 'Active'

2. Table: categories
- category_id (PK, Auto Increment, INT)
- category_name (VARCHAR(100)) // e.g., 'UG', 'PG'
- description (TEXT, NULL)

3. Table: courses
- course_id (PK, Auto Increment, INT)
- college_id (FK -> colleges)
- category_id (FK -> categories)
- course_name (VARCHAR(255)) // e.g., 'Mechanical Engineering'
- course_code (VARCHAR(20), UNIQUE) // e.g., 'BE-ME'
- regulation_year (YEAR) // e.g., 2020
- duration_years (INT) // e.g., 4
- total_semesters (INT) // e.g., 8
- status (ENUM('Active', 'Inactive')) DEFAULT 'Active'

4. Table: subjects
- subject_id (PK, Auto Increment, INT)
- course_id (FK -> courses)
- subject_code (VARCHAR(20)) // e.g., 'CS101'
- subject_name (VARCHAR(255))
- regulation_year (YEAR) // Links to course.regulation_year
- type (ENUM('Regular', 'Practical', 'Elective', 'Lab'))
- credits (INT)
- is_lab (BOOLEAN) DEFAULT FALSE
- semester (INT) // Which sem is this subject taught in?

Module 2: Grading and Mark Settings
5. Table: grading_systems
- grading_system_id (PK, Auto Increment, INT)
- system_name (VARCHAR(100)) // e.g., 'UG Regulation 2020', 'PG Grading Scale'
- course_id (FK -> courses) // Links the grading system to a specific course/regulation
- is_active (BOOLEAN) DEFAULT TRUE

6. Table: grades
- grade_id (PK, Auto Increment, INT)
- grading_system_id (FK -> grading_systems)
- grade_name (VARCHAR(5)) // e.g., 'S', 'A', 'B', 'C', 'D', 'F', 'RA'
- min_marks (INT) // e.g., 90
- max_marks (INT) // e.g., 100
- grade_point (DECIMAL(3, 2)) // e.g., 10.0, 9.0, 8.0
- is_pass (BOOLEAN) // FALSE for 'F' and 'RA'

7. Table: subject_assessment_settings
- setting_id (PK, Auto Increment, INT)
- subject_id (FK -> subjects)
- exam_type (ENUM('Internal', 'Semester')) // Defines if these are internal or sem pass rules
- max_marks (INT) // e.g., 100 for Sem, 50 for Internal
- pass_marks (INT) // e.g., 40 for Sem, 25 for Internal
- min_marks (INT) DEFAULT 0 // The minimum marks a student can get in this subject's exam

Module 3: Fee Structure
8. Table: fee_types
- fee_type_id (PK, Auto Increment, INT)
- fee_name (VARCHAR(100)) // e.g., 'Exam Fee Per Paper', 'Revaluation', 'Duplicate Certificate', 'Late Fee'
- description (TEXT, NULL)

9. Table: course_fee_structure
- fee_structure_id (PK, Auto Increment, INT)
- course_id (FK -> courses)
- fee_type_id (FK -> fee_types)
- amount (DECIMAL(10, 2))
- effective_from (DATE)
- effective_to (DATE, NULL) // NULL means currently active

Module 4: People (Student & Staff)
10. Table: students
- student_id (PK, Auto Increment, INT)
- university_rollno (VARCHAR(20), UNIQUE)
- college_id (FK -> colleges)
- course_id (FK -> courses)
- first_name (VARCHAR(100))
- last_name (VARCHAR(100))
- date_of_joining (DATE)
- current_semester (INT)
- status (ENUM('Active', 'Completed', 'Dropped Out'))

11. Table: staff
- staff_id (PK, Auto Increment, INT)
- college_id (FK -> colleges) // NULL if external
- staff_code (VARCHAR(20), UNIQUE)
- first_name (VARCHAR(100))
- last_name (VARCHAR(100))
- email (VARCHAR(255), UNIQUE)
- phone (VARCHAR(15))
- role (ENUM('Internal', 'External', 'Valuator', 'Invigilator')) // Can be multiple
- status (ENUM('Active', 'Inactive'))

Module 5: Exam Definition & Registration
12. Table: exam_types
- exam_type_id (PK, Auto Increment, INT)
- type_name (VARCHAR(50)) // e.g., 'SEM', 'MODAL', 'CI1', 'CI2', 'SUPPLEMENTARY'
- description (TEXT, NULL)

13. Table: exams
- exam_id (PK, Auto Increment, INT)
- exam_name (VARCHAR(255)) // e.g., 'B.E. Mechanical - Jun 2024 - Semester 6'
- exam_type_id (FK -> exam_types)
- academic_year (VARCHAR(9)) // e.g., '2023-2024'
- semester (INT) // e.g., 6
- status (ENUM('Draft', 'Announced', 'In Progress', 'Completed', 'Results Published'))
- is_arrear_allowed (BOOLEAN) DEFAULT TRUE
- mark_conversion_type (ENUM('Best of 2', 'Average of 3', 'Percentage (e.g., 80%)')) // Logic for sem mark conversion
- conversion_value (DECIMAL(5,2), NULL) // e.g., 80.00 if percentage is chosen

14. Table: exam_subjects (Linking table: Which subjects are part of which exam?)
- exam_subject_id (PK, Auto Increment, INT)
- exam_id (FK -> exams)
- subject_id (FK -> subjects)
- max_marks (INT) // Should copy from subject_assessment_settings for 'Semester'
- pass_marks (INT) // Should copy from subject_assessment_settings for 'Semester'
- exam_date (DATE, NULL) // To be set later in timetable
- session (ENUM('FN', 'AN'), NULL)

15. Table: student_registrations (Nominal Roll)
- registration_id (PK, Auto Increment, INT)
- student_id (FK -> students)
- exam_subject_id (FK -> exam_subjects) // Links to a specific subject in a specific exam
- is_arrear (BOOLEAN) DEFAULT FALSE // Is this an arrear paper for the student?
- fee_amount (DECIMAL(10, 2)) // Snapshot of fee at time of registration
- fee_status (ENUM('Paid', 'Pending'))
- UNIQUE KEY unique_registration(student_id,exam_subject_id`) // Prevents duplicate registrations

Module 6: Question Paper & Mark Entry Template
16. Table: question_patterns
- pattern_id (PK, Auto Increment, INT)
- pattern_name (VARCHAR(100)) // e.g., 'Standard Pattern - 20/50/30'
- total_marks (INT)
- exam_subject_id (FK -> exam_subjects, UNIQUE) // The final pattern used for this specific exam subject

17. Table: pattern_sections
- section_id (PK, Auto Increment, INT)
- pattern_id (FK -> question_patterns)
- section_name (VARCHAR(100)) // e.g., 'Part A'
- num_questions (INT) // e.g., 10
- marks_per_question (INT) // e.g., 2
- section_total (INT) // e.g., 20 (Calculated: 10*2)
- question_start_num (INT) // e.g., 1 (Question 1 to 10 are in Part A)
- question_end_num (INT) // e.g., 10

18. Table: question_paper_orders
- order_id (PK, Auto Increment, INT)
- exam_subject_id (FK -> exam_subjects, UNIQUE) // One order per exam subject
- setter_id (FK -> staff) // Internal/External staff creating the paper
- status (ENUM('Order Sent', 'Accepted', 'Draft Submitted', 'Approved', 'Rejected'))
- time_duration (INT, NULL) // in minutes, set by the setter
- approver_id (FK -> staff, NULL) // Staff who approved it
- approved_at (DATETIME, NULL)
- final_pattern_id (FK -> question_patterns, NULL) // The approved pattern

19. Table: co_po_mapping (Course Outcome - Program Outcome Mapping for questions)
- mapping_id (PK, Auto Increment, INT)
- subject_id (FK -> subjects)
- co_code (VARCHAR(10)) // e.g., 'CO1'
- po_codes (JSON) // e.g., ['PO1', 'PO3'] // Can store multiple POs for a CO

20. Table: pattern_questions (Questions per section with CO/PO mapping)
- id (PK, Auto Increment, INT)
- section_id (FK -> pattern_sections)
- question_no (INT)
- question_text (TEXT)
- co_codes (JSON) // e.g., ['CO1', 'CO2']
- po_codes (JSON) // e.g., ['PO1', 'PO3']

Module 7: Detailed Mark Entry
20. Table: student_question_marks (Core Table for Detailed Marks)
- sqm_id (PK, Auto Increment, INT)
- registration_id (FK -> student_registrations) // Links to a student + their exam subject
- question_number (INT) // e.g., 1, 2, 3, ... (from the template)
- marks_obtained (DECIMAL(5,2)) // e.g., 1.5, 2.0, 4.75
- co_mapped (VARCHAR(10), NULL) // e.g., 'CO1' (for analysis)
- valuator_id (FK -> staff, NULL) // Staff who evaluated this question
- UNIQUE KEY unique_student_question (registration_id, question_number)

21. Table: student_marks_summary (Aggregated View for Performance)
- summary_id (PK, Auto Increment, INT)
- registration_id (FK -> student_registrations, UNIQUE)
- marks_internal (INT, NULL) // If applicable, fetched from another internal exam record
- marks_external (DECIMAL(7,2)) // SUM of all marks_obtained from student_question_marks
- marks_total (DECIMAL(7,2)) // Calculated (internal + external)
- result (ENUM('Pass', 'Fail', 'Absent')) DEFAULT 'Absent'
- grade_id (FK -> grades, NULL) // Derived by comparing marks_total to the grades table
- grade_point (DECIMAL(3,2), NULL) // Copied from the grades table
- attendance_status (ENUM('Present', 'Absent')) DEFAULT 'Absent'
- is_revaluation_applied (BOOLEAN) DEFAULT FALSE
- revaluation_marks (DECIMAL(7,2), NULL)
- revaluation_result (ENUM('Pass', 'Fail', 'Unchanged'), NULL)

Module 8: Timetable & Hall Allocation
22. Table: halls
- hall_id (PK, Auto Increment, INT)
- college_id (FK -> colleges)
- hall_name (VARCHAR(100))
- block_name (VARCHAR(50), NULL)
- capacity (INT)

23. Table: exam_timetable (Finalized Schedule)
- timetable_id (PK, Auto Increment, INT)
- exam_subject_id (FK -> exam_subjects, UNIQUE with session) // Upsert by (exam_subject_id, session)
- exam_date (DATE)
- session (ENUM('FN', 'AN'))
- start_time (TIME)
- end_time (TIME)

Timetable Generator (api/exam_timetable/generate.php):
- Inputs: exam_id (optional), start_date (default today), shuffle (0/1), clear_existing (0/1)
- Logic: For each exam_subject with registrations, create FN entry for current registrations and AN entry for arrear registrations on the same date. If shuffle=1, randomize order of subjects before assigning dates. If clear_existing=1, delete previous timetable entries (scoped by exam_id if provided).
- Output: JSON with counts created/updated and generated rows.

24. Table: hall_settings (Per-hall seating and invigilation configuration)
- hall_setting_id (PK, Auto Increment, INT)
- hall_id (FK -> halls, UNIQUE) // One settings row per hall
- rows_count (INT) // Number of bench rows
- columns_count (INT) // Number of benches per row
- seating_method (ENUM('Parallel','Curved')) // Layout style
- bench_capacity (INT, NULL) // Students per bench if applicable
- max_students (INT, NULL) // Override capacity; otherwise derive: rows*columns*bench_capacity
- row_labeling (ENUM('Numeric','Alphabetic')) // Seat labels series
- internal_invigilators_required (INT) // Internal invigilators needed
- external_invigilators_required (INT) // External invigilators needed
- aisle_every_n_cols (INT, NULL, default 0) // vertical aisle after every N columns (0=none)
- aisle_every_n_rows (INT, NULL, default 0) // horizontal aisle after every N rows (0=none)

25. Table: hall_allocations
- allocation_id (PK, Auto Increment, INT)
- timetable_id (FK -> exam_timetable)
- hall_id (FK -> halls)
- num_students (INT) // Number of students assigned to this hall for this exam

26. Table: student_seating (Your core requirement: Who sits where?)
- seating_id (PK, Auto Increment, INT)
- registration_id (FK -> student_registrations) // Links to a student + their exam
- allocation_id (FK -> hall_allocations) // Links to a specific hall for a specific exam
- seat_number (VARCHAR(10))
- UNIQUE KEY unique_seat(allocation_id,seat_number) -UNIQUE KEY unique_student_exam (registration_id) // A student can only have one seat per exam

Module 9: Valuation & Salary
26. Table: valuation_rooms
- room_id (PK, Auto Increment, INT)
- room_name (VARCHAR(100))
- incharge_id (FK -> staff)

27. Table: valuator_allocation
- allocation_id (PK, Auto Increment, INT)
- exam_subject_id (FK -> exam_subjects)
- valuator_id (FK -> staff)
- room_id (FK -> valuation_rooms)
- num_scripts (INT) // Number of answer scripts allocated
- num_scripts_completed (INT) DEFAULT 0

28. Table: valuation_salary
- salary_id (PK, Auto Increment, INT)
- staff_id (FK -> staff)
- exam_subject_id (FK -> exam_subjects)
- rate_per_script (DECIMAL(10, 2))
- num_scripts (INT)
- total_amount (DECIMAL(10, 2)) // Calculated
- payment_status (ENUM('Pending', 'Processing', 'Paid'))

Module 10: Internal Exam Mark Entry
29. Table: internal_exams
- internal_exam_id (PK, Auto Increment, INT)
- exam_type_id (FK -> exam_types) // e.g., CI1, CI2
- subject_id (FK -> subjects)
- academic_year (VARCHAR(9)) // e.g., '2023-2024'
- semester (INT) // e.g., 6
- max_marks (INT) // Can be different from subject_assessment_settings? Or should we use that?
- pass_marks (INT)
- exam_date (DATE, NULL)
- conducted_by (FK -> staff) // The staff who conducted the exam

30. Table: internal_marks
- internal_mark_id (PK, Auto Increment, INT)
- internal_exam_id (FK -> internal_exams)
- student_id (FK -> students)
- marks_obtained (DECIMAL(5,2))
- attendance_status (ENUM('Present', 'Absent')) DEFAULT 'Present'
- UNIQUE KEY unique_internal_mark (internal_exam_id, student_id)

31. Table: internal_marks_summary (To store the calculated internal total for a student in a subject for a semester)
- summary_id (PK, Auto Increment, INT)
- student_id (FK -> students)
- subject_id (FK -> subjects)
- academic_year (VARCHAR(9))
- semester (INT)
- internal_total (DECIMAL(5,2)) // Calculated based on the rule (best of, average, etc.)
- internal_grade (VARCHAR(5), NULL) // Optional, if grading is done for internals
- UNIQUE KEY unique_internal_summary (student_id, subject_id, academic_year, semester)

Module 12: External Staff Application
32. Table: external_staff_applications
- application_id (PK, Auto Increment, INT)
- staff_id (FK -> staff) // External staff (college_id is NULL)
- exam_id (FK -> exams) // The exam they are applying for
- preferred_role (ENUM('Valuator', 'Invigilator'))
- application_status (ENUM('Applied', 'Approved', 'Rejected'))
- applied_date (DATETIME)
- approved_by (FK -> staff, NULL) // Internal staff who approved
- approved_date (DATETIME, NULL)

Module 13: Revaluation Details
33. Table: revaluation_applications
- reval_id (PK, Auto Increment, INT)
- registration_id (FK -> student_registrations)
- apply_date (DATETIME)
- fee_paid (DECIMAL(10,2))
- fee_status (ENUM('Paid', 'Pending'))
- status (ENUM('Applied', 'In Progress', 'Completed', 'Result Updated'))
- result (ENUM('Pass', 'Fail', 'Unchanged'), NULL) // Final result after revaluation

Module 15: Result Publishing
34. Table: subject_results
- result_id (PK, Auto Increment, INT)
- student_id (FK -> students)
- subject_id (FK -> subjects)
- exam_id (FK -> exams) // The semester exam for which this result is published
- internal_marks (DECIMAL(5,2)) // From internal_marks_summary
- external_marks (DECIMAL(5,2)) // From student_marks_summary
- total_marks (DECIMAL(5,2))
- grade_id (FK -> grades)
- grade_point (DECIMAL(3,2))
- result (ENUM('Pass', 'Fail'))
- UNIQUE KEY unique_subject_result (student_id, subject_id, exam_id)

35. Table: semester_results
- sem_result_id (PK, Auto Increment, INT)
- student_id (FK -> students)
- exam_id (FK -> exams) // The semester exam (e.g., Jun 2024 Semester 6)
- total_credits (INT)
- earned_credits (INT)
- sgpa (DECIMAL(4,2))
- cgpa (DECIMAL(4,2))
- result (ENUM('Pass', 'Fail', 'Withheld'))

Module 19: Staff Allocation for Invigilation
36. Table: invigilation_duty
- duty_id (PK, Auto Increment, INT)
- staff_id (FK -> staff)
- timetable_id (FK -> exam_timetable) // Which exam session
- hall_id (FK -> halls) // Which hall
- role (ENUM('Chief Invigilator', 'Invigilator'))
- status (ENUM('Assigned', 'Present', 'Absent'))

Module 22: Certificates
37. Table: certificate_issuance
- certificate_id (PK, Auto Increment, INT)
- student_id (FK -> students)
- certificate_type (ENUM('Degree', 'Provisional', 'Migration', 'Other'))
- issue_date (DATE)
- status (ENUM('Applied', 'Issued', 'Delivered'))

Module 25: Audit Logs (System)
38. Table: audit_logs
- id (PK, BIGINT UNSIGNED)
- module (VARCHAR(100))
- action (ENUM('create','update','delete'))
- entity_table (VARCHAR(100))
- entity_id (VARCHAR(100))
- before_data (JSON)
- after_data (JSON)
- message (VARCHAR(255))
- created_by_id (VARCHAR(50))
- created_by_name (VARCHAR(100))
- ip_address (VARCHAR(45))
- user_agent (VARCHAR(255))
- created_at (TIMESTAMP DEFAULT CURRENT_TIMESTAMP)

Key Application Logic Notes (Revised):
Mark Entry Flow:

An admin first defines the question_pattern and its pattern_sections for an exam_subject.

This pattern is linked to the question_paper_orders table upon approval.

During valuation, the staff enters marks question-by-question into the student_question_marks table.

A database trigger or application logic automatically updates the student_marks_summary table, calculating the marks_external total and determining the result, grade_id, and grade_point based on the defined grading_system and subject_assessment_settings.

Internal vs. Semester Exams: The subject_assessment_settings table has an exam_type field. This allows you to define different pass marks and max marks for Internal exams (e.g., CI1, CI2) and the final Semester exam. The exam_types table is used to categorize the exam event itself.

Grading Calculation: When a student's total marks are saved in student_marks_summary, the application must:

Find the active grading_system for the student's course_id and regulation.

Query the grades table to find the grade where min_marks <= marks_total <= max_marks.

Populate the grade_id and grade_point accordingly.

Integrity: The student_question_marks table ensures data integrity for detailed mark entry and enables powerful analysis like CO-PO attainment for each student and each question.

This enhanced structure provides a complete solution for your requirements, from the finest detail of a single question's marks to the overall grade and result of a student.

Complete List of Tables with Foreign Key Relationships:
colleges (4 columns) - Referenced by 4 tables
categories (3 columns) - Referenced by 1 table
courses (9 columns) - Referenced by 4 tables
subjects (9 columns) - Referenced by 6 tables
grading_systems (4 columns) - Referenced by 1 table
grades (7 columns) - Referenced by 2 tables
subject_assessment_settings (6 columns) - Referenced by 0 tables
fee_types (3 columns) - Referenced by 1 table
course_fee_structure (6 columns) - Referenced by 0 tables
students (9 columns) - Referenced by 6 tables
staff (9 columns) - Referenced by 10 tables
exam_types (3 columns) - Referenced by 2 tables
exams (9 columns) - Referenced by 4 tables
exam_subjects (7 columns) - Referenced by 6 tables
student_registrations (6 columns) - Referenced by 4 tables
question_patterns (4 columns) - Referenced by 2 tables
pattern_sections (8 columns) - Referenced by 0 tables
question_paper_orders (8 columns) - Referenced by 0 tables
co_po_mapping (4 columns) - Referenced by 0 tables
student_question_marks (6 columns) - Referenced by 0 tables
student_marks_summary (12 columns) - Referenced by 0 tables
halls (5 columns) - Referenced by 2 tables
exam_timetable (6 columns) - Referenced by 2 tables
hall_allocations (4 columns) - Referenced by 1 table
student_seating (4 columns) - Referenced by 0 tables
valuation_rooms (3 columns) - Referenced by 1 table
valuator_allocation (6 columns) - Referenced by 0 tables
valuation_salary (7 columns) - Referenced by 0 tables
internal_exams (9 columns) - Referenced by 1 table
internal_marks (5 columns) - Referenced by 0 tables
internal_marks_summary (7 columns) - Referenced by 0 tables
external_staff_applications (8 columns) - Referenced by 0 tables
revaluation_applications (7 columns) - Referenced by 0 tables
subject_results (10 columns) - Referenced by 0 tables
semester_results (8 columns) - Referenced by 0 tables
invigilation_duty (6 columns) - Referenced by 0 tables
certificate_issuance (5 columns) - Referenced by 0 tables

Total: 37 Core Tables with comprehensive foreign key relationships ensuring data integrity and referential constraints.

College Examination Management System (COE) - Database Schema
Based on your requirements, I'll design a comprehensive database structure for your College Examination System. This schema includes all the modules you mentioned with special attention to the mark entry system based on question templates and the grading system.

Database Connection Details:
- Database Name: autonomous_college
- User: root
- Password: (empty)
- Host: localhost
- Port: 3306

All foreign key relationships are properly established with appropriate constraint names following the naming convention: fk_[table]_[referenced_table].