Sql_Adv_Proj

🗄️ Examination Management System - Database Documentation

![SQL Server](https://img.shields.io/badge/SQL%20Server-2019+-CC2927?style=for-the-badge&logo=microsoft-sql-server&logoColor=white) ![Database](https://img.shields.io/badge/Database-db-blue?style=for-the-badge) ![Tables](https://img.shields.io/badge/Tables-11-green?style=for-the-badge) ![Stored Procedures](https://img.shields.io/badge/Stored_Procedures-45+-purple?style=for-the-badge) **Complete database documentation for the Examination Management System**

📋 Table of Contents

Overview
Database Information
Entity Relationship Diagram
Tables
- department
- track
- student
- instructor
- topic
- course
- question
- choice
- exam
- examquestion
- studentquestionanswer
Relationships
Stored Procedures
Constraints & Indexes
Business Rules
Setup Instructions

🎯 Overview

The Examination Management System Database is a comprehensive SQL Server database designed to manage educational institution operations including:

🏢 Department & Track Management - Organizational structure
👨‍🎓 Student Management - Student records with track assignments
👨‍🏫 Instructor Management - Instructor profiles with department association
📚 Course Management - Courses linked to instructors and topics
📝 Question Bank - MCQ and True/False questions with choices
📋 Exam Generation - Dynamic exam creation with random questions
✅ Answer Processing - Student answer submission and auto-grading
📊 Results & Analytics - Grade calculation and exam history

🔧 Database Information

Property	Value
Database Name	`db`
Compatibility Level	SQL Server 2019 (150)
Collation	DATABASE_DEFAULT
Recovery Model	FULL
File Growth	64 MB
Auto Close	ON

📊 Entity Relationship Diagram

┌─────────────────┐
│   department    │
│─────────────────│
│ PK departmentid │
│    departmentname│
└────────┬────────┘
         │
         │ 1:N
         ▼
┌─────────────────┐          ┌─────────────────┐
│     track       │          │   instructor    │
│─────────────────│          │─────────────────│
│ PK trackid      │          │ PK instructorid │
│    trackname    │          │    instructorname│
│ FK deptid ──────┼──────────│    email        │
└────────┬────────┘          │    password     │
         │                   │ FK deptid ──────┤
         │ 1:N               └────────┬────────┘
         ▼                            │
┌─────────────────┐                   │ 1:N
│    student      │                   ▼
│─────────────────│          ┌─────────────────┐          ┌─────────────────┐
│ PK studentid    │          │     course      │          │     topic       │
│    studentname  │          │─────────────────│          │─────────────────│
│    email (UQ)   │          │ PK courseid     │◄─────────│ PK topicid      │
│    password     │          │    coursename   │          │    topicname    │
│ FK trackid ─────┤          │ FK instructorid │          └─────────────────┘
└────────┬────────┘          │ FK topicid      │
         │                   └────────┬────────┘
         │                            │
         │                            │ 1:N
         │                            ▼
         │                   ┌─────────────────┐          ┌─────────────────┐
         │                   │    question     │──────────│     choice      │
         │                   │─────────────────│   1:N    │─────────────────│
         │                   │ PK questionid   │          │ PK choiceid     │
         │                   │    questiontext │          │    choicelabel  │
         │                   │    questiontype │          │    choicetext   │
         │                   │    correctanswer│          │ FK questionid   │
         │                   │    degree       │          └─────────────────┘
         │                   │ FK courseid     │
         │                   └────────┬────────┘
         │                            │
         │         ┌──────────────────┴──────────────────┐
         │         │                                     │
         │         ▼                                     ▼
         │  ┌─────────────────┐                 ┌─────────────────────────┐
         │  │      exam       │                 │     examquestion        │
         │  │─────────────────│                 │─────────────────────────│
         └─►│ PK examid       │◄────────────────│ PK,FK examid            │
            │ FK courseid     │                 │ PK,FK questionid        │
            │ FK studentid    │                 └─────────────────────────┘
            │    grade        │
            │    ExamStartedAt│
            └────────┬────────┘
                     │
                     │ 1:N
                     ▼
            ┌─────────────────────────┐
            │  studentquestionanswer  │
            │─────────────────────────│
            │ PK,FK examid            │
            │ PK,FK studentid         │
            │ PK,FK questionid        │
            │    studentanswer        │
            │    mark                 │
            └─────────────────────────┘

📁 Tables

1. department

Stores academic department information.

Column	Data Type	Nullable	Description
`departmentid`	INT	NO	Primary Key - Unique department identifier
`departmentname`	VARCHAR(100)	YES	Name of the department

Example Data:

| departmentid | departmentname      |
|--------------|---------------------|
| 1            | Computer Science    |
| 2            | Information Systems |
| 3            | Software Engineering|

2. track

Represents learning tracks/specializations within departments.

Column	Data Type	Nullable	Description
`trackid`	INT	NO	Primary Key - Unique track identifier
`trackname`	VARCHAR(100)	YES	Name of the track
`deptid`	INT	YES	Foreign Key → `department.departmentid`

Relationships:

Belongs to one department (ON DELETE SET NULL)
Has many student records

Example Data:

| trackid | trackname           | deptid |
|---------|---------------------|--------|
| 1       | Web Development     | 1      |
| 2       | Mobile Development  | 1      |
| 3       | Data Science        | 2      |

3. student

Stores student information and credentials.

Column	Data Type	Nullable	Constraints	Description
`studentid`	INT IDENTITY(1,1)	NO	Primary Key	Auto-generated student ID
`studentname`	VARCHAR(100)	YES		Full name of student
`email`	VARCHAR(100)	NO	Unique	Student email (login)
`trackid`	INT	YES	Foreign Key	Track assignment
`password`	VARCHAR(50)	NO		Account password

Relationships:

Belongs to one track (ON DELETE SET NULL)
Has many exam records
Has many studentquestionanswer records

Constraints:

PK_student - Primary key on studentid
UQ_Student_Email - Unique constraint on email
fk_student_track - Foreign key to track

4. instructor

Stores instructor/teacher information.

Column	Data Type	Nullable	Description
`instructorid`	INT	NO	Primary Key - Instructor identifier
`instructorname`	VARCHAR(100)	YES	Full name
`email`	VARCHAR(100)	YES	Email address (login)
`deptid`	INT	YES	Foreign Key → `department.departmentid`
`password`	VARCHAR(50)	NO	Account password

Relationships:

Belongs to one department (ON DELETE SET NULL)
Teaches many course records

5. topic

Categorizes courses by subject/topic.

Column	Data Type	Nullable	Description
`topicid`	INT	NO	Primary Key - Topic identifier
`topicname`	VARCHAR(100)	YES	Topic/subject name

Example Data:

| topicid | topicname          |
|---------|--------------------|
| 1       | Programming        |
| 2       | Database           |
| 3       | Networking         |

6. course

Stores course information with instructor and topic assignments.

Column	Data Type	Nullable	Description
`courseid`	INT IDENTITY(1,1)	NO	Primary Key - Auto-generated
`coursename`	VARCHAR(100)	YES	Course title
`instructorid`	INT	YES	Foreign Key → `instructor.instructorid`
`topicid`	INT	YES	Foreign Key → `topic.topicid`

Relationships:

Belongs to one instructor (ON DELETE SET NULL)
Belongs to one topic (ON DELETE SET NULL)
Has many question records
Has many exam records

7. question

Stores the question bank with MCQ and True/False questions.

Column	Data Type	Nullable	Description
`questionid`	INT	NO	Primary Key - Question identifier
`questiontext`	VARCHAR(MAX)	YES	The question text
`questiontype`	VARCHAR(50)	YES	Type: `'MCQ'` or `'TF'`
`correctanswer`	VARCHAR(100)	YES	Correct answer (e.g., ‘A’, ‘B’, ‘True’, ‘False’)
`degree`	INT	YES	Points/marks for the question
`courseid`	INT	YES	Foreign Key → `course.courseid`

Relationships:

Belongs to one course (ON DELETE CASCADE)
Has many choice records (for MCQ)
Can be in many examquestion records
Has many studentquestionanswer records

Question Types:

MCQ - Multiple Choice Question (has choices A, B, C, D)
TF - True/False Question (no choices needed)

8. choice

Stores answer choices for MCQ questions.

Column	Data Type	Nullable	Description
`choiceid`	INT	NO	Primary Key - Choice identifier
`choicelabel`	VARCHAR(10)	YES	Choice label (A, B, C, D)
`choicetext`	VARCHAR(200)	YES	Choice text/content
`questionid`	INT	YES	Foreign Key → `question.questionid`

Relationships:

Belongs to one question (ON DELETE CASCADE)

Example Data:

| choiceid | choicelabel | choicetext              | questionid |
|----------|-------------|-------------------------|------------|
| 1        | A           | JavaScript              | 1          |
| 2        | B           | Python                  | 1          |
| 3        | C           | Java                    | 1          |
| 4        | D           | All of the above        | 1          |

9. exam

Stores exam instances for students.

Column	Data Type	Nullable	Default	Description
`examid`	INT	NO		Primary Key - Exam identifier
`courseid`	INT	YES		Foreign Key → `course.courseid`
`studentid`	INT	YES		Foreign Key → `student.studentid`
`grade`	INT	YES	NULL	Final grade (0-100)
`ExamStartedAt`	DATETIME2(7)	NO	`SYSUTCDATETIME()`	Exam start timestamp (UTC)

Relationships:

Belongs to one course
Belongs to one student
Has many examquestion records
Has many studentquestionanswer records

Notes:

Grade is NULL until exam is submitted and corrected
ExamStartedAt is automatically set when exam is generated
Used to calculate remaining time (15-minute limit)

10. examquestion

Junction table linking exams to their questions.

Column	Data Type	Nullable	Description
`examid`	INT	NO	Primary Key (Composite), Foreign Key → `exam.examid`
`questionid`	INT	NO	Primary Key (Composite), Foreign Key → `question.questionid`

Relationships:

Links exam and question (Many-to-Many)
ON DELETE CASCADE for both foreign keys

11. studentquestionanswer

Stores student answers for each question in an exam.

Column	Data Type	Nullable	Description
`examid`	INT	NO	Primary Key (Composite), FK → `exam.examid`
`studentid`	INT	NO	Primary Key (Composite)
`questionid`	INT	NO	Primary Key (Composite), FK → `question.questionid`
`studentanswer`	VARCHAR(100)	YES	Student’s answer (e.g., ‘A’, ‘True’)
`mark`	INT	YES	1 = Correct, 0 = Wrong, NULL = Not graded

Relationships:

Belongs to one exam (ON DELETE CASCADE)
Belongs to one question (ON DELETE CASCADE)

🔗 Relationships

Foreign Key Summary

Child Table	Column	Parent Table	Parent Column	On Delete
`track`	`deptid`	`department`	`departmentid`	SET NULL
`student`	`trackid`	`track`	`trackid`	SET NULL
`instructor`	`deptid`	`department`	`departmentid`	SET NULL
`course`	`instructorid`	`instructor`	`instructorid`	SET NULL
`course`	`topicid`	`topic`	`topicid`	SET NULL
`question`	`courseid`	`course`	`courseid`	CASCADE
`choice`	`questionid`	`question`	`questionid`	CASCADE
`exam`	`studentid`	`student`	`studentid`	NO ACTION
`examquestion`	`examid`	`exam`	`examid`	CASCADE
`examquestion`	`questionid`	`question`	`questionid`	CASCADE
`studentquestionanswer`	`examid`	`exam`	`examid`	CASCADE
`studentquestionanswer`	`questionid`	`question`	`questionid`	CASCADE

📦 Stored Procedures

CRUD Operations

Department Operations

Procedure	Parameters	Description
`insert_department`	`@deptId`, `@deptname`	Insert new department
`select_department`	`@deptId` (optional)	Get all or specific department
`update_department`	`@deptId`, `@deptName`	Update department name
`delete_department`	`@deptId`	Delete department

Track Operations

Procedure	Parameters	Description
`insert_track`	`@trackId`, `@trackName`, `@deptid`	Insert new track
`select_track`	`@trackId` (optional)	Get all or specific track
`update_track`	`@trackId`, `@trackName`, `@deptId`	Update track
`delete_track`	`@trackId`	Delete track

Student Operations

Procedure	Parameters	Description
`insert_student`	`@studentname`, `@email`, `@trackid`, `@password`	Register new student
`select_student`	`@studentid` (optional)	Get all or specific student
`update_student`	`@studentid`, `@studentname`, `@email`, `@trackid`	Update student info
`delete_student`	`@studentid`	Delete student

Instructor Operations

Procedure	Parameters	Description
`insert_instructor`	`@instructorid`, `@instructorname`, `@email`, `@deptid`	Add instructor
`select_instructor`	`@instructorid` (optional)	Get all or specific instructor
`delete_instructor`	`@instructorid`	Delete instructor

Topic Operations

Procedure	Parameters	Description
`insert_topic`	`@topicId`, `@topicname`	Insert new topic
`select_topic`	`@topicId` (optional)	Get all or specific topic
`update_topic`	`@topicID`, `@topicName`	Update topic
`delete_topic`	`@topicID`	Delete topic

Course Operations

Procedure	Parameters	Description
`insert_course`	`@coursename`, `@instructorid`, `@topicid`	Create course
`select_course`	`@courseid`, `@PageNumber`, `@PageSize`	Get courses (paginated)
`update_course`	`@courseid`, `@coursename`, `@instructorid`, `@topicid`	Update course
`delete_course`	`@courseid`	Delete course

Question Operations

Procedure	Parameters	Description
`insert_question`	`@questionid`, `@questiontext`, `@questiontype`, `@correctanswer`, `@degree`, `@courseid`	Add question
`select_question`	`@questionid` (optional)	Get questions
`update_question`	All fields	Update question
`delete_question`	`@questionid`	Delete question

Choice Operations

Procedure	Parameters	Description
`insert_choice`	`@choiceid`, `@choicelabel`, `@choicetext`, `@questionid`	Add choice
`select_choice`	`@choiceid` (optional)	Get choices
`update_choice`	All fields	Update choice
`delete_choice`	`@choiceid`	Delete choice

Authentication Procedures

sp_StudentLogin

Authenticates a student and returns their profile with enrolled courses.

EXEC sp_StudentLogin @email = 'student@example.com', @password = 'password123'

Parameters:

Parameter	Type	Description
`@email`	VARCHAR(100)	Student email
`@password`	VARCHAR(100)	Student password

Returns (2 Result Sets):

Student Profile:
- studentid, studentname, email, departmentname, trackname
- StatusMessage: ‘Success’, ‘Email not found’, or ‘Wrong password’
- Success: 1 or 0
Enrolled Courses:
- courseid, coursename, departmentname, instructorname

sp_InstructorLogin

Authenticates an instructor and returns their profile.

EXEC sp_InstructorLogin @email = 'instructor@example.com', @password = 'password123'

Parameters:

Parameter	Type	Description
`@email`	VARCHAR(100)	Instructor email
`@password`	VARCHAR(50)	Instructor password

Returns:

instructorid, instructorname, email, departmentname
StatusMessage, Success

Exam Management Procedures

generate_exam

Generates a new exam with random MCQ and True/False questions.

EXEC generate_exam 
    @courseID = 11, 
    @studentID = 1, 
    @mcqCount = 6, 
    @TFCount = 4

Parameters:

Parameter	Type	Default	Description
`@courseID`	INT	Required	Course to generate exam for
`@studentID`	INT	Required	Student taking the exam
`@mcqCount`	INT	Required	Number of MCQ questions
`@TFCount`	INT	Required	Number of True/False questions
`@Count`	INT	10	Total questions (must equal mcqCount + TFCount)

Business Logic:

Validates that mcqCount + TFCount = 10
Generates unique examid
Randomly selects MCQ questions from course
Randomly selects TF questions from course
Creates studentquestionanswer records for each question
Sets ExamStartedAt to current UTC time

Returns:

| Status | StatusMessage              | ExamID | StudentID |
|--------|----------------------------|--------|-----------|
| 1      | Exam generated successfully| 101    | 1         |

getexamquestions

Retrieves all questions and choices for an exam.

EXEC getexamquestions @examid = 101

Returns:

| questionid | questiontext        | questiontype | choicelabel | choicetext |
|------------|---------------------|--------------|-------------|------------|
| 1          | What is JavaScript? | MCQ          | A           | A language |
| 1          | What is JavaScript? | MCQ          | B           | A framework|
| 2          | SQL is a language   | TF           | NULL        | NULL       |

exam_answers

Submits student answers for an exam (10 answers).

EXEC exam_answers 
    @examID = 101,
    @studentID = 1,
    @ans1 = 'A',
    @ans2 = 'B',
    @ans3 = 'True',
    @ans4 = 'C',
    @ans5 = 'False',
    @ans6 = 'A',
    @ans7 = 'D',
    @ans8 = 'True',
    @ans9 = 'B',
    @ans10 = 'A'

Parameters:

Parameter	Type	Default	Description
`@examID`	INT	Required	Exam identifier
`@studentID`	INT	Required	Student identifier
`@ans1` - `@ans10`	VARCHAR(10)	’’	Answers in order

exam_correction

Grades the exam and calculates final score.

EXEC exam_correction @examID = 101, @studentID = 1

Business Logic:

Compares student answers with correct answers
Sets mark = 1 for correct, 0 for wrong
Calculates total marks × 10 = grade
Updates exam.grade

Returns:

| Status | StatusMessage                          | Grade | studentID | examID |
|--------|----------------------------------------|-------|-----------|--------|
| 1      | Exam corrected and Marks added...     | 80    | 1         | 101    |

GetExamReview_Split

Returns detailed exam review with questions and choices.

EXEC GetExamReview_Split @examID = 101, @studentID = 1

Returns (2 Result Sets):

Question Details:

| questionid | questiontext | SelectedLabel | CorrectLabel | mark |
|------------|--------------|---------------|--------------|------|
| 1          | What is...   | A             | A            | 1    |
| 2          | SQL is...    | True          | True         | 1    |

Choice Map:

| questionid | choicelabel | choicetext          |
|------------|-------------|---------------------|
| 1          | A           | A programming lang  |
| 1          | B           | A markup language   |

GetStudentExamsHistory

Retrieves all exams taken by a student.

EXEC GetStudentExamsHistory @studentID = 1

Returns:

| examid | coursename | grade | ExamStartedAt            |
|--------|------------|-------|--------------------------|
| 101    | JavaScript | 80    | 2026-01-23T10:30:00.000Z |
| 100    | SQL        | 70    | 2026-01-22T09:00:00.000Z |

GetExamStartTime

Returns when an exam was started (for timer calculation).

EXEC GetExamStartTime @examID = 101, @studentID = 1

Returns:

| examid | ExamStartedAt            |
|--------|--------------------------|
| 101    | 2026-01-23T10:30:00.000Z |

🔐 Constraints & Indexes

Primary Keys

Table	Constraint Name	Column(s)
`department`	PK (Clustered)	`departmentid`
`track`	PK (Clustered)	`trackid`
`student`	`PK_student`	`studentid`
`instructor`	PK (Clustered)	`instructorid`
`topic`	PK (Clustered)	`topicid`
`course`	`PK__course__2AAB4BC9279D1F4D`	`courseid`
`question`	PK (Clustered)	`questionid`
`choice`	PK (Clustered)	`choiceid`
`exam`	PK (Clustered)	`examid`
`examquestion`	PK (Clustered)	`examid`, `questionid`
`studentquestionanswer`	PK (Clustered)	`examid`, `studentid`, `questionid`

Unique Constraints

Table	Constraint Name	Column
`student`	`UQ_Student_Email`	`email`

Default Constraints

Table	Constraint Name	Column	Default Value
`exam`	`DF_ExamStartedAt`	`ExamStartedAt`	`SYSUTCDATETIME()`

📋 Business Rules

Exam Generation Rules

Question Count: Total must equal 10 (MCQ + TF)
Random Selection: Questions selected randomly using NEWID()
Course Specific: Questions come from the specified course only
Unique Questions: Each question appears only once per exam
Auto Timestamp: ExamStartedAt set automatically on generation

Exam Timing Rules

Duration: 15 minutes from ExamStartedAt
Time Calculation: Frontend calculates remaining time from ExamStartedAt
Late Submission: Backend can mark as timed out if submitted after 15 minutes

Grading Rules

Points: Each question worth 10 points (10 questions × 10 = 100 max)
Binary Marking: mark = 1 (correct) or 0 (wrong)
Grade Calculation: SUM(mark) × 10
Pass/Fail: Typically 60+ is passing (configurable)

Answer Format

Question Type	Valid Answers
MCQ	‘A’, ‘B’, ‘C’, ‘D’
TF	‘True’, ‘False’, ‘T’, ‘F’

🚀 Setup Instructions

Prerequisites

Microsoft SQL Server 2019 or higher
SQL Server Management Studio (SSMS)
Sufficient disk space for data files

Installation Steps

Connect to SQL Server

-- Connect using SSMS or sqlcmd
sqlcmd -S localhost -E

Execute the Script
```
-- Run the db.script file
:r "path/to/db.script"
```
Or in SSMS:
- Open db.script
- Press F5 to execute

Verify Installation

USE db;
GO
   
-- Check tables
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';
   
-- Check procedures
SELECT name FROM sys.procedures;

Insert Sample Data (Optional)

-- Insert department
EXEC insert_department @deptId = 1, @deptname = 'Computer Science';
   
-- Insert track
EXEC insert_track @trackId = 1, @trackName = 'Web Development', @deptid = 1;
   
-- Insert student
EXEC insert_student 
    @studentname = 'John Doe', 
    @email = 'john@example.com', 
    @trackid = 1, 
    @password = 'password123';

Backup Files

The project includes backup files for quick restoration:

dbafterstored.bak - Database with stored procedures
dbveido.bak - Alternative backup

Restore Command:

RESTORE DATABASE db 
FROM DISK = 'path/to/dbafterstored.bak'
WITH REPLACE,
MOVE 'db' TO 'D:\SQL_Server\...\db.mdf',
MOVE 'db_log' TO 'D:\SQL_Server\...\db_log.ldf';

📊 Sample Queries

Get Student with Track and Department

SELECT 
    s.studentid,
    s.studentname,
    s.email,
    t.trackname,
    d.departmentname
FROM student s
LEFT JOIN track t ON s.trackid = t.trackid
LEFT JOIN department d ON t.deptid = d.departmentid;

Get Course with Instructor and Topic

SELECT 
    c.courseid,
    c.coursename,
    i.instructorname,
    t.topicname
FROM course c
LEFT JOIN instructor i ON c.instructorid = i.instructorid
LEFT JOIN topic t ON c.topicid = t.topicid;

Get Exam Statistics by Course

SELECT 
    c.coursename,
    COUNT(e.examid) AS TotalExams,
    AVG(e.grade) AS AverageGrade,
    MAX(e.grade) AS HighestGrade,
    MIN(e.grade) AS LowestGrade
FROM exam e
JOIN course c ON e.courseid = c.courseid
GROUP BY c.coursename;

Get Student Exam Performance

SELECT 
    s.studentname,
    COUNT(e.examid) AS ExamsTaken,
    AVG(e.grade) AS AverageGrade,
    SUM(CASE WHEN e.grade >= 60 THEN 1 ELSE 0 END) AS PassedExams
FROM student s
LEFT JOIN exam e ON s.studentid = e.studentid
GROUP BY s.studentid, s.studentname;

👨‍💻 Author

CrossITI Team

**⭐ Star this repository if you found it helpful!** Made with ❤️ using Microsoft SQL Server