Sql_Adv_Proj

🎓 Examination Management System - Backend API

![Node.js](https://img.shields.io/badge/Node.js-18+-339933?style=for-the-badge&logo=node.js&logoColor=white) ![Express](https://img.shields.io/badge/Express-5.2.1-000000?style=for-the-badge&logo=express&logoColor=white) ![Sequelize](https://img.shields.io/badge/Sequelize-6.37.7-52B0E7?style=for-the-badge&logo=sequelize&logoColor=white) ![MS SQL Server](https://img.shields.io/badge/SQL%20Server-CC2927?style=for-the-badge&logo=microsoft-sql-server&logoColor=white) ![License](https://img.shields.io/badge/License-ISC-blue?style=for-the-badge) **A robust and scalable RESTful API for managing educational examinations, students, instructors, and courses.** [Getting Started](#-getting-started) • [API Documentation](#-api-endpoints) • [Database Schema](#-database-schema) • [Contributing](#-contributing)

🎯 Overview

The Examination Management System is a comprehensive backend solution designed to handle all aspects of educational institution operations. Built with modern Node.js and Express.js, this API provides secure, efficient, and scalable endpoints for managing:

Student enrollment and authentication
Instructor management and course assignments
Dynamic exam generation with multiple question types
Real-time exam monitoring with time tracking
Automated grading and result computation

✨ Features

Feature	Description
🔐 Authentication	Secure login/register for students and instructors
📚 Course Management	Full CRUD operations with instructor assignment
👨‍🎓 Student Management	Track assignment, enrollment, and progress tracking
👨‍🏫 Instructor Management	Department association and course management
📝 Dynamic Exams	Auto-generated exams with MCQ and True/False questions
⏱️ Time Tracking	Real-time exam timer with auto-submission on timeout
✅ Auto-Grading	Instant result calculation upon submission
📊 Exam Review	Detailed review with correct/incorrect answers
🛡️ Validation	Request validation using express-validator
📄 Pagination	Efficient data retrieval with pagination support

🛠️ Tech Stack

Technology	Version	Purpose
Node.js	18+	JavaScript Runtime
Express.js	5.2.1	Web Application Framework
Sequelize	6.37.7	ORM for SQL Server
Tedious	19.2.0	MS SQL Server Driver
express-validator	7.3.1	Input Validation Middleware
Morgan	1.10.1	HTTP Request Logger
CORS	2.8.5	Cross-Origin Resource Sharing
dotenv	17.2.3	Environment Configuration
Nodemon	3.1.11	Development Hot Reload

📁 Project Structure

Back_sql_project/
│
├── 📄 index.js                 # Application entry point
├── 📄 package.json             # Dependencies & scripts
├── 📄 .env                     # Environment variables
├── 📄 .gitignore               # Git ignore rules
│
├── 🔐 Auth/
│   └── auth.js                 # Authentication routes & logic
│
├── ⚙️ Configuration/
│   └── Sequelize.js            # Database connection config
│
├── 🎮 controllers/
│   ├── Courses.js              # Course business logic
│   ├── Exams.js                # Exam generation & grading
│   ├── instructors.js          # Instructor operations
│   ├── Students.js             # Student auth logic
│   └── StudentsController.js   # Student CRUD operations
│
├── 📊 models/
│   ├── init-models.js          # Model initialization & relations
│   ├── choice.js               # Answer choices model
│   ├── course.js               # Course model
│   ├── department.js           # Department model
│   ├── exam.js                 # Exam model
│   ├── examquestion.js         # Exam-Question junction
│   ├── instructor.js           # Instructor model
│   ├── question.js             # Question model
│   ├── student.js              # Student model
│   ├── studentquestionanswer.js # Student answers model
│   ├── topic.js                # Topic/Subject model
│   └── track.js                # Learning track model
│
├── 🛤️ Routes/
│   ├── Courses.js              # /api/v1/course routes
│   ├── ExamsRoute.js           # /api/v1/exams routes
│   ├── instructor.js           # /api/v1/instructor routes
│   └── StudentRoute.js         # /api/v1/student routes
│
└── ✅ validators/
    ├── coursesValidators.js            # Course input validation
    ├── validationSchemaForExams.js     # Exam input validation
    ├── validationSchemaForInstructors.js # Instructor validation
    └── ValidationSchemaForStudent.js   # Student validation

🚀 Getting Started

Prerequisites

Before you begin, ensure you have the following installed:

Node.js (v18.x or higher) - Download
Microsoft SQL Server (2019 or higher) - Download
npm or yarn package manager
Git (optional) - Download

Installation

Clone the repository

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

Install dependencies
```
npm install
```

Configure environment variables

Create a .env file in the root directory:

PORT=3000
API_VERSION=api/v1
   
# Database Configuration
DB_NAME=ExaminationDB
DB_USER=sa
DB_PASSWORD=your_secure_password
DB_HOST=localhost

Restore the database

Use SQL Server Management Studio (SSMS) to restore one of the backup files:
- dbafterstored.bak - Database with stored procedures
- dbveido.bak - Alternative backup

Start the server

# Development mode (with hot reload)
npm run dev
   
# Production mode
npm start

Verify the installation

Open your browser and navigate to:
```
http://localhost:3000
```

🔧 Environment Variables

Variable	Description	Default	Required
`PORT`	Server port number	`3000`	No
`API_VERSION`	API version prefix	`api/v1`	No
`DB_NAME`	SQL Server database name	-	✅ Yes
`DB_USER`	Database username	-	✅ Yes
`DB_PASSWORD`	Database password	-	✅ Yes
`DB_HOST`	Database host address	`localhost`	No

📡 API Endpoints

Base URL

http://localhost:3000/api/v1

Authentication

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

Students

Method	Endpoint	Description
`GET`	`/student`	Get all students (paginated)
`GET`	`/student/:id`	Get student by ID
`POST`	`/student`	Create new student
`PATCH`	`/student/:id`	Update student
`DELETE`	`/student/:id`	Delete student
`PATCH`	`/student/:id/track`	Assign student to track

Instructors

Method	Endpoint	Description
`GET`	`/instructor`	Get all instructors
`GET`	`/instructor/:id`	Get instructor by ID
`POST`	`/instructor`	Create new instructor
`PATCH`	`/instructor/:id`	Update instructor
`DELETE`	`/instructor/:id`	Delete instructor

Courses

Method	Endpoint	Description
`GET`	`/course`	Get all courses
`GET`	`/course/:id`	Get course by ID
`POST`	`/course`	Create new course
`PATCH`	`/course/:id`	Update course
`DELETE`	`/course/:id`	Delete course

Exams

Method	Endpoint	Description
`POST`	`/exams/generate`	Generate new exam
`POST`	`/exams/submit`	Submit exam answers
`GET`	`/exams/review/:examId/:studentId`	Get exam review
`GET`	`/exams/student/:studentId`	Get student’s exam history
`GET`	`/exams/status/:examId/:studentId`	Check exam status

🗄️ Database Schema

Entity Relationship

┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│  Department │────<│  Instructor │>────│   Course    │
└─────────────┘     └─────────────┘     └─────────────┘
                                              │
┌─────────────┐     ┌─────────────┐           │
│    Track    │────<│   Student   │>──────────┤
└─────────────┘     └─────────────┘           │
                          │                   │
                          │              ┌────┴────┐
                          │              │  Topic  │
                          │              └────┬────┘
                          │                   │
                    ┌─────┴─────┐       ┌─────┴─────┐
                    │   Exam    │>──────│ Question  │
                    └─────┬─────┘       └─────┬─────┘
                          │                   │
                    ┌─────┴─────────────┐    ┌┴────────┐
                    │ StudentQuestion   │    │ Choice  │
                    │     Answer        │    └─────────┘
                    └───────────────────┘

Key Tables

Table	Description
`Student`	Student information and credentials
`Instructor`	Instructor details and department
`Course`	Course catalog with instructor assignment
`Track`	Learning paths/specializations
`Department`	Academic departments
`Question`	Question bank (MCQ, True/False)
`Choice`	Answer choices for questions
`Exam`	Generated exam instances
`ExamQuestion`	Exam-Question mapping
`StudentQuestionAnswer`	Student responses
`Topic`	Subject/Topic categorization

🔧 Stored Procedures

The system leverages SQL Server stored procedures for complex operations:

Procedure	Description
`GenerateExam`	Creates exam with random questions
`SubmitExamAnswers`	Processes and grades submissions
`GetExamReview`	Retrieves detailed exam results
`GetStudentExams`	Fetches exam history
`CheckExamStatus`	Validates exam state and timing

⚠️ Error Handling

Standard Error Response

{
  "success": false,
  "message": "Error description",
  "error": "Detailed error message (development only)"
}

HTTP Status Codes

Code	Description
`200`	Success
`201`	Created
`400`	Bad Request (validation error)
`401`	Unauthorized
`404`	Not Found
`500`	Internal Server Error

Validation Error Response

{
  "success": false,
  "data": [
    {
      "msg": "Field is required",
      "param": "fieldName",
      "location": "body"
    }
  ]
}

📚 Additional Documentation

For detailed API documentation with request/response examples:

🤝 Contributing

Contributions are welcome! Please follow these steps:

Fork the repository
Create your feature branch (git checkout -b feature/AmazingFeature)
Commit your changes (git commit -m 'Add some AmazingFeature')
Push to the branch (git push origin feature/AmazingFeature)
Open a Pull Request

Code Style

Use ES6+ syntax
Follow ESLint configurations
Write meaningful commit messages
Add JSDoc comments for functions

📝 Scripts

# Start production server
npm start

# Start development server with hot reload
npm run dev

# Run tests
npm test

🔒 Security Considerations

⚠️ Never commit .env files to version control
🔐 Use strong, unique passwords for database
🛡️ Enable HTTPS in production
🔑 Implement JWT for production authentication
📊 Enable SQL Server encryption for sensitive data

📄 License

This project is licensed under the ISC License - see the LICENSE file for details.

👨‍💻 Author

CrossITI Team

**⭐ Star this repository if you found it helpful!** Made with ❤️ using Node.js and Express