SQL Examination Database System

The SQL Examination Database System optimizes educational institutions' exam processes and data management. It includes tables for branches, courses, exams, and students, with stored procedures for creating and assigning exams. Security roles ensure data integrity and access control.

Tables

1. Branches

• Purpose: Stores information about different branches.

• Columns: branch_id (Primary Key), branch_name.

2. intake

• Purpose: Stores intake years.

• Columns: intake_id (Primary Key), intake_year.

3. Departments

• Purpose: Stores department details.

• Columns: dept_id (Primary Key), dept_name, branch_id (Foreign Key referencing Branches).

4. Tracks

• Purpose: Stores track details within departments.

• Columns: Track_id (Primary Key), Track_name, dept_id (Foreign Key referencing Departments).

5. branch_track

• Purpose: Associate branches with tracks.

• Columns: branch_id (Foreign Key referencing Branches), track_id (Foreign Key referencing Tracks), Composite Primary Key (branch_id, track_id).

6. Instructors

• Purpose: Stores instructor details.

• Columns: ins_id (Primary Key), ins_fname, ins_lname, track_id (Foreign Key referencing Tracks).

7. Course

• Purpose: Stores course details.

• Columns: Crs_id (Primary Key), Crs_name, Crs_Description, Crs_Max_Deg, Crs_Min_Deg, ins_id (Foreign Key referencing Instructors), track_id (Foreign Key referencing Tracks).

8. Exams

• Purpose: Stores exam details.

• Columns: exam_id (Primary Key), exam_type, start_time, end_time, total_time (calculated), allowance_options, year, crs_id (Foreign Key referencing Course), ins_id (Foreign Key referencing Instructors), track_id (Foreign Key referencing Tracks). 9. Questions

• Purpose: Stores questions for exams.

• Columns: Ques_id (Primary Key), Question, Ques_type, crs_id (Foreign Key referencing Course).

10. Choices

• Purpose: Stores possible answers to questions.

• Columns: ChoiceID (Primary Key), QuestionID (Foreign Key referencing Questions), ChoiceText, IsCorrect.

11. student

• Purpose: Stores student details.

• Columns: st_id (Primary Key), st_fname, st_lname, crs_id (Foreign Key referencing Course), exam_id (Foreign Key referencing Exams), intake_id (Foreign Key referencing intake).

12. stud_ques

• Purpose: Stores student answers to questions.

• Columns: stud_id (Foreign Key referencing student), ques_id (Foreign Key referencing Questions), stud_answer, mark, Composite Primary Key (stud_id, ques_id).

13. exam_ques

• Purpose: Associates exams with their questions.

• Columns: exam_id (Foreign Key referencing Exams), ques_id (Foreign Key referencing Questions).

Procedure

1. Create_exam

This procedure creates an exam for a specific course and instructor. It checks if the instructor’s track matches the provided track ID and if the course belongs to that track. If both conditions are met, it inserts the exam details into the Exams table and randomly selects five questions from the Questions table to insert into the exam_ques table. If the conditions are not met, it returns an error message indicating the mismatch.

2. StudentExam

This procedure assigns an exam to a student. It checks if the student’s course matches the course of the exam. If they match, it updates the student’s record with the exam ID. If they don’t match, it returns an error message indicating the mismatch.

3. store_answer

This procedure stores a student’s answer to a question. It checks if the question belongs to the student’s exam and if the provided answer is a valid choice. If both conditions are met, the answer is inserted into the stud_ques table and the validity and marks are updated based on whether the answer is correct. If the conditions are not met, an appropriate error message will be returned.

4. results This procedure calculates and updates the total mark for a student based on their answers. It sums the marks from the stud_ques table and updates the student’s record in the student table. If the student ID is invalid, it returns an error message.
5. showexam

This procedure displays the questions and choices for a student’s exam. It checks if the exam ID matches the student’s exam and retrieves the questions and their choices from the Questions and Choices tables. If the exam ID is invalid, it returns an error message.

Views

1. V_ShowStudents

This view displays student details, including their ID, full name, course name, intake year, and mark. It joins the student, Course, and intake tables.

2. V_ShowInstructors

This view shows instructor details, including their ID, full name, track name, and course name. It joins the Instructors, Tracks, and Course tables.

Security Features

Admin Role: Full control over the database.

Training Manager Role: Manage training-related data.

Instructor Role: Manage exams and view course information.

Student Role: View exam results and course information.

Running Queries

1- Show Exam (procedure)

2-V_show_instructors (View)

3- Student_show_exam (procedure)

4-results (procedure)