University Database System: Schema Design

This project presents a comprehensive relational database schema for a university management system. The schema includes tables to track professors, students, courses, registrations, exams, and other academic activities. Below is a detailed description of each component:

Core Tables

1. Professors Table

Purpose: Stores information about faculty members

Structure:

id: Unique identifier (primary key)

name: Professor's full name (up to 100 characters)

department: Academic department affiliation (up to 100 characters)

2. Students Table

Purpose: Maintains student records

Structure:

id: Unique identifier (primary key)

name: Student's full name (up to 100 characters)

academic_status: Enrollment status with check constraint (only 'active' or 'suspended' allowed)

total_credits: Accumulated credit hours

3. Courses Table

Purpose: Catalog of all offered courses

Structure:

id: Unique identifier (primary key)

name: Course title (up to 100 characters)

professor_id: Foreign key linking to Professors table

credit_hours: Number of credits the course carries

prerequisite_course_id: Self-referential foreign key for course prerequisites

Constraints:

fk_professor: Ensures course instructor exists in Professors table

fk_prerequisite: Enforces valid prerequisite relationships

Relationship Tables

4. Register Table

Purpose: Manages the many-to-many relationship between students and courses (enrollments)

Structure:

id: Unique registration identifier (primary key)

student_id: Foreign key to Students table

course_id: Foreign key to Courses table

Constraints:

fk_student: Ensures only registered students can enroll

fk_course: Ensures enrollment only in existing courses

Assessment Tables

5. Exams Table

Purpose: Tracks examination schedules

Structure:

id: Unique exam identifier (primary key)

course_id: Foreign key to Courses table

exam_date: Date of examination

exam_type: Description of exam type (midterm, final, etc.)

Constraint:

fk_exam_course: Ensures exams are for valid courses

6. ExamResults Table

Purpose: Records student performance in exams

Structure:

id: Unique result identifier (primary key)

registration_id: Foreign key to Register table (linking student+course)

grade: Letter grade with check constraint (A, B, C, D, or F)

status: Pass/fail status with check constraint

Constraint:

fk_registration: Ensures results are tied to valid enrollments

Administrative Tables

7. AuditTrail Table

Purpose: Logs changes to database for security and compliance

Structure:

id: Unique log entry identifier (primary key)

table_name: Name of modified table

operation: Type of operation (insert, update, delete)

old_data: Snapshot of data before change

new_data: Snapshot of data after change

timestamp: Automatic timestamp of when change occurred

8. Warnings Table

Purpose: Records disciplinary or academic warnings for students

Structure:

id: Unique warning identifier (primary key)

student_id: Foreign key to Students table

warning_reason: Description of warning (up to 255 characters)

warning_date: Date warning was issued

Constraint:

fk_warning_student: Ensures warnings are for valid students

Key Features of the Database Design

Referential Integrity: Comprehensive foreign key constraints maintain data consistency

Data Validation: Check constraints enforce domain rules (grades, statuses)

Academic Relationships:

Tracks course prerequisites (self-referential relationship)

Manages student enrollments (many-to-many via Register table)

Links exams to courses and results to enrollments

Administrative Functions:

Audit trail for change tracking

Warning system for student management

Scalability: Design supports adding more entities (like departments, classrooms) as needed

This schema provides a solid foundation for a university information system, supporting core academic operations while maintaining data integrity and enabling administrative oversight.