Database Schema

Relevant source files

The following files were used as context for generating this wiki page:

• Data/ApplicationDbContext.cs
• Models/ApplicationUser.cs
• Models/ExamModels.cs
• Models/ProgrammingExamModels.cs
• Migrations/ApplicationDbContextModelSnapshot.cs
• Services/OpenAIService.cs
• ViewModels/ExamViewModels.cs
• Migrations/20260108140205_AddAIEvaluationExtendedFields.Designer.cs

The database schema for the PeP (Programming Examination Platform) is designed using Entity Framework Core. It serves as the data persistence layer for the entire application, managing users, courses, and two distinct types of examinations: traditional multiple-choice exams and project-based programming exams. The central component of the data layer is the ApplicationDbContext, which defines the DbSet properties for all entities and configures their relationships and constraints.

The schema is structured to support complex examination workflows, including student attempts, automated and manual grading, AI-powered question generation and code evaluation, and secure exam session management. It separates the concerns of standard exams from the more intricate structure of programming exams, which involve tasks, test cases, project files, and detailed progress tracking.

Sources: Data/ApplicationDbContext.cs:7-41, Models/ExamModels.cs, Models/ProgrammingExamModels.cs

Entity Relationship Diagrams

The following diagrams illustrate the primary relationships between the tables in the database. Due to the complexity, the schema is split into two main parts: the Standard Exam Schema and the Programming Exam Schema.

Standard Exam Schema

This diagram shows the relationships between entities involved in standard multiple-choice exams, such as Courses, Exams, Questions, and student attempts.

Programming Exam Schema

This diagram outlines the entities and relationships specific to project-based programming exams, highlighting the structure of exams, tasks, test cases, and student progress.

Core Entities

These are the fundamental entities that support the overall application structure.

ApplicationUser

Inherits from IdentityUser and represents any user of the system (student, teacher, admin).

Field	Type	Description
Id	string	Primary Key
FirstName	string	User's first name.
LastName	string	User's last name.
StudentId	string	Optional ID for students.
Department	string	Optional department for teachers.
CreatedAt	DateTime	Timestamp of user creation.
IsActive	bool	Flag to enable or disable the user account.

Sources: Models/ApplicationUser.cs:5-30

Course

Represents a course or subject to which exams belong.

Field	Type	Description
Id	int	Primary Key
Name	string	The name of the course.
Code	string	An optional code for the course.
Exams	ICollection<Exam>	Navigation property for standard exams in this course.

Sources: Models/ExamModels.cs:5-25

PlatformSetting

A key-value store for global application settings.

Field	Type	Description
Id	int	Primary Key
Key	string	The setting key (e.g., "OpenAIApiKey").
Value	string	The setting value.

Sources: Models/ExamModels.cs:255-269, Data/ApplicationDbContext.cs:181-192

Standard Exam Schema

This section details the entities used for creating and managing traditional multiple-choice exams.

Exam

Defines a standard multiple-choice exam.

Field	Type	Description
Id	int	Primary Key
Title	string	The title of the exam.
CourseId	int	Foreign key to the Course entity.
CreatedByUserId	string	Foreign key to the ApplicationUser who created it.
DurationMinutes	int	The duration of the exam in minutes.
TotalPoints	decimal	The total points for the exam.
ScoringType	enum	Defines how answers are scored (e.g., AllOrNothing).
ShuffleQuestions	bool	Whether to randomize the question order for students.
Questions	ICollection<Question>	Collection of questions in the exam.
ExamAttempts	ICollection<ExamAttempt>	Collection of student attempts for this exam.

Sources: Models/ExamModels.cs:27-81

ExamAttempt & StudentAnswer

ExamAttempt tracks a student's session for a standard exam, while StudentAnswer stores their answer for each question.

Entity	Field	Type	Description
ExamAttempt	Id	int	Primary Key
	StudentId	string	FK to the student taking the exam.
	ExamId	int	FK to the Exam being attempted.
	StartedAt	DateTime	When the attempt began.
	SubmittedAt	DateTime?	When the student submitted the exam.
	TotalScore	decimal	The final calculated score.
	Status	enum	Current status (e.g., InProgress, Completed).
StudentAnswer	Id	int	Primary Key
	ExamAttemptId	int	FK to the parent ExamAttempt.
	QuestionId	int	FK to the Question being answered.
	SelectedChoiceId	int?	FK to QuestionChoice for single-answer questions.
	SelectedChoiceIds	string?	JSON array of choice IDs for multiple-answer questions.
	PointsEarned	decimal	Points awarded for this answer.

Sources: Models/ExamModels.cs:161-185, 192-223, Data/ApplicationDbContext.cs:75-96

Programming Exam Schema

This section details the entities for project-based programming exams.

ProgrammingExam

Defines a programming exam, including its configuration and associated tasks.

Field	Type	Description
Id	int	Primary Key
Title	string	The title of the programming exam.
CourseId	int	Foreign key to the Course.
Language	enum	The programming language for the exam (e.g., Java, Python).
ProjectName	string	The name of the student's project.
DurationMinutes	int	The time limit for the exam.
Tasks	ICollection<ProgrammingTask>	The set of tasks or problems in the exam.
StarterFiles	ICollection<ProjectFile>	The initial set of files for the student project.
Attempts	ICollection<ProgrammingExamAttempt>	Collection of student attempts.

Sources: Models/ProgrammingExamModels.cs:8-76

ProgrammingTask & TestCase

A ProgrammingTask is a specific problem to be solved, and TestCase defines the criteria for evaluating a solution.

Entity	Field	Type	Description
ProgrammingTask	Id	int	Primary Key
	ProgrammingExamId	int	FK to the parent ProgrammingExam.
	Title	string	The title of the task.
	Instructions	string	Detailed instructions for the student.
	Points	decimal	Maximum points for this task.
	TargetFiles	string?	Comma-separated list of relevant files for this task.
	TestCases	ICollection<TestCase>	Automated test cases for this task.
TestCase	Id	int	Primary Key
	ProgrammingTaskId	int	FK to the parent ProgrammingTask.
	Input	string?	Input to be provided to the student's code.
	ExpectedOutput	string?	The expected output for the given input.
	IsHidden	bool	If true, the test case is not visible to the student.
	Points	decimal	Points awarded if this test case passes.

Sources: Models/ProgrammingExamModels.cs:112-137, 142-171

ProgrammingExamAttempt

This entity tracks a single student's attempt at a programming exam, including their files, progress, and evaluation status.

Field	Type	Description
Id	int	Primary Key
StudentId	string	FK to the ApplicationUser (student).
ProgrammingExamId	int	FK to the ProgrammingExam.
StartedAt	DateTime	Timestamp when the attempt began.
SubmittedAt	DateTime?	Timestamp of final submission.
TotalScore	decimal	The final score for the attempt.
Status	enum	The current status (e.g., InProgress, Completed, Grading).
AIEvaluationCompleted	bool	Flag indicating if AI evaluation has finished.
StudentFiles	ICollection<StudentProjectFile>	The student's modified project files.
TaskProgress	ICollection<TaskProgress>	Tracks progress on individual tasks.
Submissions	ICollection<CodeSubmission>	Snapshots of code saved during the exam.

Sources: Models/ProgrammingExamModels.cs:215-270

TaskProgress

This entity stores the state and evaluation results for a student's work on a single ProgrammingTask.

Field	Type	Description
Id	int	Primary Key
ProgrammingExamAttemptId	int	FK to the parent ProgrammingExamAttempt.
ProgrammingTaskId	int	FK to the ProgrammingTask.
PointsEarned	decimal	Points awarded for this task.
AIFeedback	string?	AI-generated feedback on the student's code.
AIScore	decimal?	AI-generated score for the task.
CodeQualityScore	decimal?	AI-generated score for code quality (0-100).
CorrectnessScore	decimal?	AI-generated score for correctness (0-100).
TestCaseResults	ICollection<TestCaseResult>	Results from running automated test cases.

Sources: Models/ProgrammingExamModels.cs:321-372, Migrations/20260108140205_AddAIEvaluationExtendedFields.Designer.cs

Database Context and Configuration

The ApplicationDbContext class is the gateway to the database. It uses EF Core's OnModelCreating method to configure entity relationships, constraints, and initial data seeding.

Key configurations include:

• Relationships: Defining foreign keys and navigation properties between entities.
• Delete Behavior: Specifying cascade or restrict behavior on deletion. For example, deleting a ProgrammingExam will also delete its associated ProgrammingTasks.
• Indexes: Creating unique indexes on fields like ExamCode.Code to ensure uniqueness.
• Data Types: Specifying precision for decimal types to ensure consistency for points and scores.
• Data Seeding: Populating the PlatformSettings table with default values on database creation.

Sources: [Data/ApplicationDbContext.cs:43-314]

The following snippet from ApplicationDbContext shows how the relationship between ProgrammingExamAttempt and its child entities is configured.

// File: Data/ApplicationDbContext.cs

// StudentProjectFile
modelBuilder.Entity<StudentProjectFile>()
    .HasOne(spf => spf.ProgrammingExamAttempt)
    .WithMany(pea => pea.StudentFiles)
    .HasForeignKey(spf => spf.ProgrammingExamAttemptId)
    .OnDelete(DeleteBehavior.Cascade);

// TaskProgress
modelBuilder.Entity<TaskProgress>()
    .HasOne(tp => tp.ProgrammingExamAttempt)
    .WithMany(pea => pea.TaskProgress)
    .HasForeignKey(tp => tp.ProgrammingExamAttemptId)
    .OnDelete(DeleteBehavior.Cascade);

// CodeSubmission
modelBuilder.Entity<CodeSubmission>()
    .HasOne(cs => cs.ProgrammingExamAttempt)
    .WithMany(pea => pea.Submissions)
    .HasForeignKey(cs => cs.ProgrammingExamAttemptId)
    .OnDelete(DeleteBehavior.Cascade);

Sources: Data/ApplicationDbContext.cs:266-296

Conclusion

The PeP5 database schema is a well-structured and comprehensive design that effectively models the complex requirements of an online examination platform. By separating standard and programming exam models, it maintains clarity while supporting rich features for each exam type. The integration of fields for AI evaluation directly into the TaskProgress and ProgrammingExamAttempt tables demonstrates a forward-looking architecture prepared for automated assessment workflows. The use of Entity Framework Core allows for clear definition of relationships and constraints, ensuring data integrity throughout the application.

Sources: Data/ApplicationDbContext.cs, Models/ProgrammingExamModels.cs, Models/ExamModels.cs