Sql_Adv_Proj

📚 Examination System - Backend API Documentation

Overview
Tech Stack
Project Structure
Getting Started
Environment Variables
Database Schema
API Endpoints
Request/Response Format
Validation Schemas
Error Handling
Stored Procedures

Overview

This is a comprehensive Examination Management System backend API built with Node.js and Express. The system manages students, instructors, courses, exams, and tracks within an educational institution. It uses Microsoft SQL Server as the database with Sequelize ORM for database interactions.

Key Features

🔐 Student and Instructor Authentication (Login/Register)
📖 Course Management (CRUD operations)
👨‍🎓 Student Management with Track Assignment
👨‍🏫 Instructor Management with Department Association
📝 Exam System with Questions and Choices
✅ Request Validation using express-validator
📄 Pagination Support for listing endpoints

Tech Stack

Technology	Version	Purpose
Node.js	Latest	Runtime Environment
Express	^5.2.1	Web Framework
Sequelize	^6.37.7	ORM for SQL Server
Tedious	^19.2.0	MS SQL Server Driver
express-validator	^7.3.1	Request Validation
Morgan	^1.10.1	HTTP Request Logger
dotenv	^17.2.3	Environment Variables
Nodemon	^3.1.11	Development Server (dev)

Project Structure

Back_sql_project/
├── index.js                    # Application entry point
├── package.json                # Dependencies and scripts
├── .env                        # Environment variables (not in repo)
├── .gitignore                  # Git ignore rules
│
├── Auth/
│   └── auth.js                 # Authentication routes
│
├── Configuration/
│   └── Sequelize.js            # Database configuration
│
├── controllers/
│   ├── Courses.js              # Course controller logic
│   ├── Exams.js                # Exam controller logic
│   ├── instructors.js          # Instructor controller logic
│   ├── Students.js             # Student auth controller
│   └── StudentsController.js   # Student operations controller
│
├── models/
│   ├── init-models.js          # Model initialization & associations
│   ├── choice.js               # Choice model
│   ├── course.js               # Course model
│   ├── department.js           # Department model
│   ├── exam.js                 # Exam model
│   ├── examquestion.js         # Exam-Question junction model
│   ├── instructor.js           # Instructor model
│   ├── question.js             # Question model
│   ├── student.js              # Student model
│   ├── studentquestionanswer.js # Student answers model
│   ├── topic.js                # Topic model
│   └── track.js                # Track model
│
├── Routes/
│   ├── Courses.js              # Course routes
│   ├── ExamsRoute.js           # Exam routes (placeholder)
│   ├── instructor.js           # Instructor routes
│   └── StudentRoute.js         # Student routes
│
└── validators/
    ├── coursesValidators.js            # Course validation schemas
    ├── validationSchemaForExams.js     # Exam validation schemas
    ├── validationSchemaForInstructors.js # Instructor validation schemas
    └── ValidationSchemaForStudent.js   # Student validation schemas

Getting Started

Prerequisites

Node.js (v18 or higher recommended)
Microsoft SQL Server
npm or yarn

Installation

Clone the repository

git clone <repository-url>
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
DB_NAME=your_database_name
DB_USER=your_database_user
DB_PASSWORD=your_database_password
DB_HOST=localhost

Restore the database

Use one of the provided backup files:
- dbafterstored.bak
- dbveido.bak

Start the server

# Development mode (with hot reload)
npm run dev

# Production mode
npm start

Verify the server is running

Server running at: http://localhost:3000

Environment Variables

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

Database Schema

Entity Relationship Diagram

┌─────────────┐       ┌─────────────┐       ┌─────────────┐
│  Department │       │    Topic    │       │   Choice    │
├─────────────┤       ├─────────────┤       ├─────────────┤
│ departmentid│       │ topicid (PK)│       │ choiceid(PK)│
│ departmentname│     │ topicname   │       │ choicelabel │
└──────┬──────┘       └──────┬──────┘       │ choicetext  │
       │                     │              │ questionid  │
       │                     │              └──────┬──────┘
       ▼                     ▼                     │
┌─────────────┐       ┌─────────────┐              │
│    Track    │       │   Course    │              │
├─────────────┤       ├─────────────┤              │
│ trackid(PK) │       │ courseid(PK)│              │
│ trackname   │       │ coursename  │              │
│ deptid (FK) │       │instructorid │              │
└──────┬──────┘       │ topicid(FK) │              │
       │              └──────┬──────┘              │
       │                     │                     │
       ▼                     ▼                     │
┌─────────────┐       ┌─────────────┐       ┌──────┴──────┐
│   Student   │       │  Instructor │       │  Question   │
├─────────────┤       ├─────────────┤       ├─────────────┤
│studentid(PK)│       │instructorid │       │questionid(PK)│
│ studentname │       │instructorname│      │ questiontext│
│ email       │       │ email       │       │ questiontype│
│ trackid(FK) │       │ deptid(FK)  │       │correctanswer│
└──────┬──────┘       └─────────────┘       │ degree      │
       │                                     │ courseid(FK)│
       │              ┌─────────────┐       └──────┬──────┘
       │              │    Exam     │              │
       │              ├─────────────┤              │
       │              │ examid(PK)  │              │
       └──────────────│ studentid   │──────────────┘
                      │ courseid    │
                      │ grade       │
                      │ examdate    │
                      └──────┬──────┘
                             │
              ┌──────────────┼──────────────┐
              ▼              ▼              ▼
       ┌─────────────┐ ┌─────────────┐
       │ExamQuestion │ │StudentAnswer│
       ├─────────────┤ ├─────────────┤
       │ examid(PK)  │ │ examid(PK)  │
       │questionid(PK)│ │studentid(PK)│
       └─────────────┘ │questionid(PK)│
                       │studentanswer│
                       │ mark        │
                       └─────────────┘

Models Description

Model	Table	Description
Student	`student`	Students enrolled in tracks
Instructor	`instructor`	Teaching staff assigned to departments
Course	`course`	Courses taught by instructors
Department	`department`	Academic departments
Track	`track`	Learning tracks within departments
Topic	`topic`	Course topics/categories
Exam	`exam`	Exams taken by students
Question	`question`	Exam questions with answers
Choice	`choice`	Multiple choice options for questions
ExamQuestion	`examquestion`	Junction table linking exams to questions
StudentQuestionAnswer	`studentquestionanswer`	Student responses to exam questions

API Endpoints

Base URL

http://localhost:3000/api/v1

Authentication

Register Student

Creates a new student account.

POST /api/v1/auth/register

Request Body:

Field	Type	Required	Description
`studentname`	string	✅	Student’s full name (min 3 chars)
`email`	string	✅	Valid email address
`password`	string	✅	Password (min 6 chars)
`trackid`	integer	❌	Track ID to assign student

Example Request:

{
  "studentname": "John Doe",
  "email": "john.doe@example.com",
  "password": "securePass123",
  "trackid": 1
}

Success Response (201):

{
  "success": true,
  "data": "Student registered successfully"
}

Error Response (400):

{
  "success": false,
  "data": [
    {
      "msg": "studentname must be at least 3 characters long",
      "param": "studentname",
      "location": "body"
    }
  ]
}

Authenticates a student and returns their data with enrolled courses.

POST /api/v1/auth/login

Request Body:

Field	Type	Required	Description
`email`	string	✅	Registered email address
`password`	string	✅	Account password (min 6 chars)

Example Request:

{
  "email": "john.doe@example.com",
  "password": "securePass123"
}

Success Response (200):

{
  "success": true,
  "data": {
    "student": {
      "studentid": 1,
      "studentname": "John Doe",
      "email": "john.doe@example.com",
      "trackid": 1
    },
    "courses": [
      {
        "courseid": 1,
        "coursename": "Introduction to SQL"
      }
    ]
  }
}

Register Instructor

Creates a new instructor account.

POST /api/v1/auth/instructor/register

Request Body:

Field	Type	Required	Description
`instructorname`	string	✅	Instructor’s full name (min 3 chars)
`email`	string	✅	Valid email address
`password`	string	✅	Password (min 6 chars)
`deptid`	integer	❌	Department ID

Example Request:

{
  "instructorname": "Dr. Jane Smith",
  "email": "jane.smith@university.edu",
  "password": "profPass123",
  "deptid": 2
}

Success Response (201):

{
  "success": true,
  "data": "Instructor registered successfully"
}

Authenticates an instructor and returns their data with assigned courses.

POST /api/v1/auth/instructor/login

Request Body:

Field	Type	Required	Description
`email`	string	✅	Registered email address
`password`	string	✅	Account password (min 6 chars)

Example Request:

{
  "email": "jane.smith@university.edu",
  "password": "profPass123"
}

Success Response (200):

{
  "success": true,
  "data": {
    "InstructorData": {
      "instructorid": 1,
      "instructorname": "Dr. Jane Smith",
      "email": "jane.smith@university.edu",
      "deptid": 2
    },
    "courses": [
      {
        "courseid": 1,
        "coursename": "Advanced Database Systems"
      }
    ],
    "track": {
      "trackid": 1,
      "trackname": "Backend Development"
    }
  }
}

Courses

Get All Courses (Paginated)

Retrieves a paginated list of all courses.

GET /api/v1/course

Query Parameters:

Parameter	Type	Required	Default	Description
`page`	integer	❌	1	Page number (min: 1)
`size`	integer	❌	10	Items per page (min: 1)

Example Request:

GET /api/v1/course?page=1&size=10

Success Response (200):

{
  "success": true,
  "data": [
    {
      "courseid": 1,
      "coursename": "Introduction to SQL",
      "instructorid": 1,
      "topicid": 1
    },
    {
      "courseid": 2,
      "coursename": "Advanced Node.js",
      "instructorid": 2,
      "topicid": 2
    }
  ]
}

Get Course by ID

Retrieves a specific course by its ID.

GET /api/v1/course/:id

Path Parameters:

Parameter	Type	Description
`id`	integer	Course ID

Example Request:

GET /api/v1/course/1

Success Response (200):

{
  "success": true,
  "data": [
    {
      "courseid": 1,
      "coursename": "Introduction to SQL",
      "instructorid": 1,
      "topicid": 1
    }
  ]
}

Create Course

Creates a new course.

POST /api/v1/course

Request Body:

Field	Type	Required	Description
`coursename`	string	✅	Course name (min 3 chars)
`instructorid`	integer	❌	Instructor ID
`topicid`	integer	❌	Topic ID

Example Request:

{
  "coursename": "Machine Learning Basics",
  "instructorid": 3,
  "topicid": 5
}

Success Response (201):

{
  "success": true,
  "data": [
    {
      "courseid": 10
    }
  ]
}

Update Course

Updates an existing course.

PATCH /api/v1/course

Request Body:

Field	Type	Required	Description
`courseid`	integer	✅	Course ID to update
`coursename`	string	✅	New course name
`instructorid`	integer	✅	New instructor ID
`topicid`	integer	✅	New topic ID

Example Request:

{
  "courseid": 1,
  "coursename": "Advanced SQL Techniques",
  "instructorid": 2,
  "topicid": 1
}

Success Response (200):

{
  "success": true,
  "data": "Course updated successfully"
}

Delete Course

Deletes a course by ID.

DELETE /api/v1/course

Request Body:

Field	Type	Required	Description
`courseid`	integer	✅	Course ID to delete

Example Request:

{
  "courseid": 5
}

Success Response (200):

{
  "success": true,
  "data": "Course deleted successfully"
}

Instructors

Get All Students

Retrieves all students (instructor access).

GET /api/v1/instructor/students

Success Response (200):

{
  "success": true,
  "data": [
    {
      "studentid": 1,
      "studentname": "John Doe",
      "email": "john.doe@example.com",
      "trackid": 1
    },
    {
      "studentid": 2,
      "studentname": "Jane Smith",
      "email": "jane.smith@example.com",
      "trackid": 2
    }
  ]
}

Get Student by ID

Retrieves a specific student by their ID.

GET /api/v1/instructor/students/:studentId

Path Parameters:

Parameter	Type	Description
`studentId`	integer	Student ID

Example Request:

GET /api/v1/instructor/students/1

Success Response (200):

{
  "success": true,
  "data": [
    {
      "studentid": 1,
      "studentname": "John Doe",
      "email": "john.doe@example.com",
      "trackid": 1
    }
  ]
}

Assign Student to Track

Assigns a student to a specific track.

PATCH /api/v1/instructor/students

Request Body:

Field	Type	Required	Description
`studentid`	integer	✅	Student ID
`trackid`	integer	✅	Track ID to assign

Example Request:

{
  "studentid": 1,
  "trackid": 3
}

Success Response (200):

{
  "success": true,
  "data": "Student assigned to track successfully"
}

Students

Note: Student routes are currently minimal. The main student functionality is handled through the authentication endpoints.

Request/Response Format

Standard Success Response

{
  "success": true,
  "data": "<response_data>"
}

Standard Error Response

{
  "success": false,
  "data": "<error_message>",
  "error": "<detailed_error>" // Optional
}

Validation Error Response

{
  "success": false,
  "data": [
    {
      "type": "field",
      "value": "<invalid_value>",
      "msg": "<error_message>",
      "path": "<field_name>",
      "location": "body"
    }
  ]
}

Validation Schemas

Student Validation

Register Schema

Instructor Validation

Register Schema

Edit (Assign to Track) Schema

Course Validation

Get Courses Schema

Add Course Schema

Error Handling

HTTP Status Codes

Code	Status	Description
`200`	OK	Request successful
`201`	Created	Resource created successfully
`400`	Bad Request	Validation error or invalid request
`404`	Not Found	Resource not found
`500`	Internal Server Error	Server-side error

Common Error Scenarios

Validation Errors (400)
- Missing required fields
- Invalid field format (email, integer, etc.)
- Field length constraints violated
Database Errors (400/500)
- Foreign key constraint violations
- Duplicate entries
- Connection failures
Authentication Errors (500)
- Invalid credentials
- User not found

Stored Procedures

The application uses SQL Server stored procedures for database operations:

Procedure	Purpose	Parameters
`insert_student`	Register new student	`@studentname`, `@email`, `@trackid`, `@password`
`sp_StudentLogin`	Authenticate student	`@email`, `@password`
`insert_instructor`	Register new instructor	`@instructorname`, `@email`, `@password`, `@deptid`
`sp_InstructorLogin`	Authenticate instructor	`@email`, `@password`
`select_course`	Get courses (paginated)	`@PageNumber`, `@PageSize`, `@courseid`
`insert_course`	Create new course	`@coursename`, `@instructorid`, `@topicid`
`update_course`	Update existing course	`@courseid`, `@coursename`, `@instructorid`, `@topicid`
`delete_course`	Delete course	`@courseid`
`select_student`	Get student(s)	`@studentId`
`assignStudentToTrack`	Assign student to track	`@studentid`, `@trackid`

API Quick Reference

Method	Endpoint	Description
`POST`	`/api/v1/auth/register`	Register student
`POST`	`/api/v1/auth/login`	Login student
`POST`	`/api/v1/auth/instructor/register`	Register instructor
`POST`	`/api/v1/auth/instructor/login`	Login instructor
`GET`	`/api/v1/course`	Get all courses
`GET`	`/api/v1/course/:id`	Get course by ID
`POST`	`/api/v1/course`	Create course
`PATCH`	`/api/v1/course`	Update course
`DELETE`	`/api/v1/course`	Delete course
`GET`	`/api/v1/instructor/students`	Get all students
`GET`	`/api/v1/instructor/students/:studentId`	Get student by ID
`PATCH`	`/api/v1/instructor/students`	Assign student to track

Future Enhancements

The following features are planned or in development:

JWT Authentication & Authorization
Exam CRUD operations (/api/v1/exam)
Question management endpoints
Student exam submission
Grade calculation and reporting
Rate limiting
API documentation with Swagger/OpenAPI
Unit and integration tests

Contributing

Fork the repository
Create a 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

License

This project is licensed under the ISC License.

Support

For support, please open an issue in the repository or contact the development team.

Documentation generated on January 22, 2026

Sql_Adv_Proj

📚 Examination System - Backend API Documentation

Table of Contents

Overview

Key Features

Tech Stack

Project Structure

Getting Started

Prerequisites

Installation

Environment Variables

Database Schema

Entity Relationship Diagram

Models Description

API Endpoints

Base URL

Authentication

Register Student

Login Student

Register Instructor

Login Instructor

Courses

Get All Courses (Paginated)

Get Course by ID

Create Course

Update Course

Delete Course

Instructors

Get All Students

Get Student by ID

Assign Student to Track

Students

Request/Response Format

Standard Success Response

Standard Error Response

Validation Error Response

Validation Schemas

Student Validation

Register Schema

Login Schema

Instructor Validation

Register Schema

Login Schema

Edit (Assign to Track) Schema

Course Validation

Get Courses Schema

Add Course Schema

Error Handling

HTTP Status Codes

Common Error Scenarios

Stored Procedures

API Quick Reference

Future Enhancements

Contributing

License

Support