Sql_Adv_Proj

🎓 Examination Management System

![Project](https://img.shields.io/badge/Project-Examination_System-4F46E5?style=for-the-badge) ![Status](https://img.shields.io/badge/Status-Production_Ready-10B981?style=for-the-badge) ![License](https://img.shields.io/badge/License-ISC-blue?style=for-the-badge) ### Full-Stack Educational Examination Platform **A comprehensive solution for managing students, instructors, courses, and online examinations** [🚀 Quick Start](#-quick-start) • [📖 Documentation](#-documentation) • [🏗️ Architecture](#️-system-architecture) • [🤝 Contributing](#-contributing) --- ![React](https://img.shields.io/badge/React-19.2.0-61DAFB?style=flat-square&logo=react&logoColor=white) ![Node.js](https://img.shields.io/badge/Node.js-18+-339933?style=flat-square&logo=node.js&logoColor=white) ![Express](https://img.shields.io/badge/Express-5.2.1-000000?style=flat-square&logo=express&logoColor=white) ![SQL Server](https://img.shields.io/badge/SQL%20Server-2019+-CC2927?style=flat-square&logo=microsoft-sql-server&logoColor=white) ![Tailwind CSS](https://img.shields.io/badge/Tailwind-4.1.18-06B6D4?style=flat-square&logo=tailwindcss&logoColor=white) ![Vite](https://img.shields.io/badge/Vite-7.2.5-646CFF?style=flat-square&logo=vite&logoColor=white)

🎯 Overview

The Examination Management System is a production-ready, full-stack web application designed for educational institutions to manage their examination processes digitally. The system provides a complete solution for:

🏫 Educational Institutions - Manage departments, tracks, and courses
👨‍🏫 Instructors - Create courses, manage students, and monitor performance
👨‍🎓 Students - Take exams, view results, and track progress
📊 Administrators - Oversee the entire examination ecosystem

Why This System?

Traditional Exams	Our Digital Solution
❌ Paper-based, time-consuming	✅ Fully digital, instant
❌ Manual grading, prone to errors	✅ Auto-grading with accuracy
❌ Delayed results	✅ Immediate results & review
❌ No real-time monitoring	✅ Live timer & status tracking
❌ Hard to analyze performance	✅ Built-in analytics

✨ Key Features

🔐 Authentication & Authorization

Feature	Description
Role-Based Access	Separate interfaces for Students and Instructors
Secure Login	Email/Password authentication
Protected Routes	Role-specific access control
Session Management	Persistent sessions with localStorage

👨‍🎓 Student Features

Feature	Description
📝 Take Exams	Interactive exam interface with MCQ and True/False questions
⏱️ Real-Time Timer	15-minute countdown with auto-submit
🚩 Question Flagging	Mark questions for later review
📊 View Results	Detailed breakdown with correct answers
📚 Browse Courses	Explore enrolled courses
💾 Resume Exams	Continue incomplete exams after refresh

👨‍🏫 Instructor Features

Feature	Description
📖 Course Management	Create and manage courses
👥 Student Management	View students, assign to tracks
📋 Exam Creation	Generate exams with custom question counts
📈 Performance Analytics	Monitor student grades and statistics

📝 Examination System

Feature	Description
Dynamic Generation	Random question selection per exam
Mixed Question Types	MCQ (A/B/C/D) and True/False
Time Tracking	Server-side time validation
Auto-Grading	Instant result calculation
Detailed Review	Question-by-question analysis

🏗️ System Architecture

┌──────────────────────────────────────────────────────────────────────────┐
│                           EXAMINATION SYSTEM                              │
└──────────────────────────────────────────────────────────────────────────┘
                                    │
         ┌──────────────────────────┼──────────────────────────┐
         │                          │                          │
         ▼                          ▼                          ▼
┌─────────────────┐      ┌─────────────────┐      ┌─────────────────┐
│    FRONTEND     │      │     BACKEND     │      │    DATABASE     │
│  (React + Vite) │◄────►│  (Node/Express) │◄────►│  (SQL Server)   │
└─────────────────┘      └─────────────────┘      └─────────────────┘
         │                        │                        │
         │                        │                        │
    ┌────┴────┐              ┌────┴────┐              ┌────┴────┐
    │         │              │         │              │         │
    ▼         ▼              ▼         ▼              ▼         ▼
┌───────┐ ┌───────┐    ┌───────┐ ┌───────┐    ┌───────┐ ┌───────┐
│Context│ │ Pages │    │Routes │ │Control│    │Tables │ │Stored │
│  API  │ │       │    │       │ │ lers  │    │       │ │ Procs │
└───────┘ └───────┘    └───────┘ └───────┘    └───────┘ └───────┘

Data Flow

┌─────────┐    HTTP     ┌─────────┐    Sequelize    ┌─────────┐
│ Browser │◄───────────►│   API   │◄────────────────►│   DB    │
│ (React) │   JSON      │(Express)│   SQL Queries   │(MSSQL)  │
└─────────┘             └─────────┘                 └─────────┘
     │                       │                           │
     │    localStorage       │     Connection Pool       │    Stored
     │    (Auth State)       │     (Sequelize)           │    Procedures
     ▼                       ▼                           ▼
┌─────────┐             ┌─────────┐                 ┌─────────┐
│  Auth   │             │ Models  │                 │ Complex │
│ Context │             │   ORM   │                 │ Queries │
└─────────┘             └─────────┘                 └─────────┘

🛠️ Tech Stack

Frontend

Technology	Version	Purpose
React	19.2.0	UI Library with Hooks
Vite	7.2.5	Build Tool & Dev Server
React Router	7.12.0	Client-Side Routing
Tailwind CSS	4.1.18	Utility-First CSS
Axios	1.13.2	HTTP Client
React Hot Toast	2.6.0	Toast Notifications

Backend

Technology	Version	Purpose
Node.js	18+	JavaScript Runtime
Express.js	5.2.1	Web Framework
Sequelize	6.37.7	ORM for SQL Server
Tedious	19.2.0	MSSQL Driver
express-validator	7.3.1	Input Validation
Morgan	1.10.1	HTTP Logger
CORS	2.8.5	Cross-Origin Requests

Database

Technology	Version	Purpose
Microsoft SQL Server	2019+	Relational Database
T-SQL	-	Stored Procedures

📁 Project Structure

Sql_adv_Project/
│
├── 📄 README.md                    # This file - Project overview
├── 📄 DATABASE_DOCUMENTATION.md    # Complete database documentation
├── 📄 db.script                    # SQL Server database script
├── 📦 dbafterstored.bak            # Database backup with stored procs
├── 📦 dbveido.bak                  # Alternative database backup
│
├── 📂 Back_sql_project/            # Backend API (Node.js/Express)
│   ├── 📄 README.md                # Backend documentation
│   ├── 📄 API_DOCUMENTATION.md     # Full API reference
│   ├── 📄 EXAMS_API_DOCUMENTATION.md # Exam endpoints details
│   ├── 📄 package.json
│   ├── 📄 index.js                 # Application entry
│   ├── 📂 Auth/                    # Authentication
│   ├── 📂 Configuration/           # Database config
│   ├── 📂 controllers/             # Business logic
│   ├── 📂 models/                  # Sequelize models
│   ├── 📂 Routes/                  # API routes
│   └── 📂 validators/              # Input validation
│
└── 📂 Front_sql_projrct/           # Frontend (React/Vite)
    ├── 📄 README.md                # Frontend documentation
    ├── 📄 Documentation.md         # Component documentation
    ├── 📄 package.json
    ├── 📄 vite.config.js
    ├── 📂 public/                  # Static assets
    └── 📂 src/
        ├── 📂 api/                 # Axios configuration
        ├── 📂 components/          # Reusable components
        ├── 📂 context/             # React Context providers
        ├── 📂 pages/               # Page components
        ├── 📂 services/            # API service functions
        └── 📂 utils/               # Utilities & constants

🚀 Quick Start

Prerequisites

Requirement	Version	Download
Node.js	18.x+	nodejs.org
SQL Server	2019+	microsoft.com
npm or yarn	Latest	Included with Node.js
Git	Latest	git-scm.com

Installation

1️⃣ Clone the Repository

git clone https://github.com/your-username/examination-system.git
cd Sql_adv_Project

2️⃣ Setup Database

# Option A: Restore from backup (Recommended)
# Use SQL Server Management Studio (SSMS) to restore dbafterstored.bak

# Option B: Run the SQL script
# Execute db.script in SSMS to create database and all objects

3️⃣ Setup Backend

# Navigate to backend directory
cd Back_sql_project

# Install dependencies
npm install

# Create environment file
cp .env.example .env

# Edit .env with your database credentials
# DB_NAME=db
# DB_USER=your_username
# DB_PASSWORD=your_password
# DB_HOST=localhost

# Start development server
npm run dev

4️⃣ Setup Frontend

# Open new terminal, navigate to frontend directory
cd Front_sql_projrct

# Install dependencies
npm install

# Start development server
npm run dev

5️⃣ Access the Application

Service	URL
Frontend	http://localhost:5173
Backend API	http://localhost:3000/api/v1

Quick Test

# Test backend health
curl http://localhost:3000

# Test API endpoint
curl http://localhost:3000/api/v1/course

📖 Documentation

Project Documentation Files

Document	Description	Location
Project README	Overview & quick start	`./README.md`
Database Docs	Schema, tables, stored procedures	`./DATABASE_DOCUMENTATION.md`
Backend README	API setup & endpoints	`./Back_sql_project/README.md`
API Documentation	Complete API reference	`./Back_sql_project/API_DOCUMENTATION.md`
Exams API Docs	Exam-specific endpoints	`./Back_sql_project/EXAMS_API_DOCUMENTATION.md`
Frontend README	React app documentation	`./Front_sql_projrct/README.md`
Component Docs	Component reference	`./Front_sql_projrct/Documentation.md`

📸 Screenshots

Landing Page

┌─────────────────────────────────────────────────────────────┐
│  ┌─────────┐                              ┌──────┐ ┌──────┐ │
│  │  LOGO   │     HOME  FEATURES  ABOUT    │LOGIN │ │SIGNUP│ │
│  └─────────┘                              └──────┘ └──────┘ │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│           Welcome to Examination System                     │
│                                                             │
│         Take your exams online with confidence              │
│                                                             │
│              ┌─────────────────────────┐                    │
│              │      GET STARTED        │                    │
│              └─────────────────────────┘                    │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Student Dashboard

┌─────────────────────────────────────────────────────────────┐
│  NAVBAR                           ┌──────────────────────┐  │
│  Dashboard | Courses | Exams      │  Student Name  ▼     │  │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  Welcome, John! 👋                                          │
│                                                             │
│  ┌──────────────┐ ┌──────────────┐ ┌──────────────┐        │
│  │   Courses    │ │    Exams     │ │    Avg       │        │
│  │      5       │ │      12      │ │   78.5%      │        │
│  └──────────────┘ └──────────────┘ └──────────────┘        │
│                                                             │
│  Recent Exams                          [View All →]         │
│  ┌─────────────────────────────────────────────────────┐   │
│  │ JavaScript  │  85%  │  Passed  │  Jan 23, 2026     │   │
│  │ SQL Basics  │  70%  │  Passed  │  Jan 22, 2026     │   │
│  └─────────────────────────────────────────────────────┘   │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Exam Taking Interface

┌─────────────────────────────────────────────────────────────┐
│  JavaScript Exam                              ⏱️ 12:45      │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  Question 3 of 10                           🚩 Flag         │
│                                                             │
│  ┌─────────────────────────────────────────────────────┐   │
│  │  What is the correct way to declare a variable      │   │
│  │  in JavaScript ES6?                                  │   │
│  └─────────────────────────────────────────────────────┘   │
│                                                             │
│  ○ A) var x = 5                                            │
│  ● B) let x = 5                                            │
│  ○ C) int x = 5                                            │
│  ○ D) variable x = 5                                       │
│                                                             │
│  ┌─────────────────────────────────────────────────────┐   │
│  │ 1 │ 2 │ 3●│ 4 │ 5 │ 6 │ 7 │ 8 │ 9 │ 10│              │   │
│  └─────────────────────────────────────────────────────┘   │
│                                                             │
│            [← Previous]              [Next →]               │
│                      [Submit Exam]                          │
│                                                             │
└─────────────────────────────────────────────────────────────┘

📡 API Reference

Base URL

http://localhost:3000/api/v1

Authentication Endpoints

Method	Endpoint	Description
`POST`	`/auth/login`	Student login
`POST`	`/auth/register`	Student registration
`POST`	`/auth/instructor/login`	Instructor login

Core Endpoints

Resource	Endpoints	Description
Courses	`GET/POST/PATCH/DELETE /course`	Course CRUD
Students	`GET/POST/PATCH/DELETE /student`	Student CRUD
Instructors	`GET/POST/PATCH/DELETE /instructor`	Instructor CRUD
Exams	`/exams/generate`, `/exams/submit`, `/exams/review`	Exam operations

Example Requests

Generate Exam

curl -X POST http://localhost:3000/api/v1/exams/generate \
  -H "Content-Type: application/json" \
  -d '{
    "courseId": 11,
    "studentId": 1,
    "mcqCount": 6,
    "tfCount": 4
  }'

Submit Exam

curl -X POST http://localhost:3000/api/v1/exams/submit \
  -H "Content-Type: application/json" \
  -d '{
    "examID": 101,
    "studentID": 1,
    "answers": "A,B,C,True,False,A,B,True,C,A"
  }'

🗄️ Database Schema

Tables Overview

Table	Description	Records
`department`	Academic departments	~5
`track`	Learning tracks/specializations	~10
`student`	Student records	~100+
`instructor`	Instructor records	~20
`topic`	Subject categories	~10
`course`	Course catalog	~30
`question`	Question bank	~500+
`choice`	MCQ answer options	~2000+
`exam`	Generated exams	~1000+
`examquestion`	Exam-Question mapping	~10000+
`studentquestionanswer`	Student responses	~10000+

Key Stored Procedures

Procedure	Purpose
`generate_exam`	Create exam with random questions
`exam_answers`	Submit student answers
`exam_correction`	Grade exam and calculate score
`GetExamReview_Split`	Get detailed exam review
`GetStudentExamsHistory`	Fetch student’s exam history
`sp_StudentLogin`	Authenticate student
`sp_InstructorLogin`	Authenticate instructor

📚 Full database documentation: DATABASE_DOCUMENTATION.md

🚢 Deployment

Production Checklist

Set NODE_ENV=production
Configure production database credentials
Enable HTTPS for both frontend and backend
Set up proper CORS origins
Enable SQL Server encryption
Configure connection pooling
Set up monitoring and logging
Configure backup strategy

Environment Variables

Backend (.env)

NODE_ENV=production
PORT=3000
API_VERSION=api/v1
DB_NAME=ExaminationDB
DB_USER=app_user
DB_PASSWORD=secure_password
DB_HOST=your-sql-server-host

Frontend (.env)

VITE_API_BASE_URL=https://api.yourdomain.com/api/v1

Docker Deployment (Optional)

# Backend Dockerfile
FROM node:18-alpine
WORKDIR /app
COPY package*.json ./
RUN npm ci --only=production
COPY . .
EXPOSE 3000
CMD ["npm", "start"]

# docker-compose.yml
version: '3.8'
services:
  backend:
    build: ./Back_sql_project
    ports:
      - "3000:3000"
    environment:
      - DB_HOST=sqlserver
      - DB_NAME=db
    depends_on:
      - sqlserver

  frontend:
    build: ./Front_sql_projrct
    ports:
      - "80:80"

  sqlserver:
    image: mcr.microsoft.com/mssql/server:2019-latest
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=YourStrong@Password
    ports:
      - "1433:1433"

🧪 Testing

Backend Testing

cd Back_sql_project
npm test

Frontend Testing

cd Front_sql_projrct
npm run lint

API Testing with Postman

Import the API collection from Back_sql_project/postman_collection.json (if available)

🤝 Contributing

We welcome contributions! Please follow these steps:

1. Fork & Clone

git clone https://github.com/your-username/examination-system.git

2. Create Branch

git checkout -b feature/amazing-feature

3. Make Changes

Follow the coding style guidelines
Write meaningful commit messages
Add documentation for new features

4. Submit PR

git push origin feature/amazing-feature

Then open a Pull Request on GitHub.

Coding Guidelines

Area	Guideline
JavaScript	ES6+ syntax, functional components
React	Hooks, Context API, PascalCase components
CSS	Tailwind utility classes
API	RESTful conventions, proper status codes
SQL	Stored procedures for complex logic
Git	Conventional commits

📄 License

This project is licensed under the ISC License.

ISC License

Copyright (c) 2026 CrossITI Team

Permission to use, copy, modify, and/or distribute this software for any
purpose with or without fee is hereby granted, provided that the above
copyright notice and this permission notice appear in all copies.

THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.

👨‍💻 Team

CrossITI Team

Role	Responsibility
Full-Stack Developer	Frontend & Backend development
Database Engineer	SQL Server design & optimization
UI/UX Designer	User interface design

🙏 Acknowledgments

### 🌟 Star this repository if you found it helpful! [![GitHub stars](https://img.shields.io/github/stars/crossiti/examination-system?style=social)](https://github.com/crossiti/examination-system) --- **Built with ❤️ by CrossITI Team** *January 2026*