STUDENT MANAGEMENT SYSTEM USING SQL
A REPORT ON
Student Management System
SUBMITTED TO SAVITRIBAI PHULE PUNE UNIVERSITY
IN PARTIAL FULFILLMENT OF THE REQUIREMENT FOR
OF
DATABASE MANAGEMENT SYSTEMS (THIRD YEAR ENGINEERING)
SUBMITTED BY
Kajal Singh (5322)
Kratika Rai (5326)
“Student Management system”
Submitted by :
KAJAL SINGH (5322)
KRATIKA RAI (5326)
is a bonafide student of this institute and the work has been carried out by him/her under the
supervision of Dr. Sandeep Bidwai and it is approved for the partial fulfillment of the
requirement of , First year course on Project Based learning of Savitribai Phule Pune University.
(Dr. Sandeep Bidwai) (Dr G. R. Patil)
Guide Head,
Department of E&TC Department of ETC
(Dr. [Link])
Principal,
Army Institute of Technology, Dighi, Pune – 411015
Table of Contents
S. Page
Contents No.
No.
3
1. Introduction
4
2. Features of Student Management System
5
3. Database Schema and Relationships
7
4. Design
8
5. Implementation and Code
9
6. Operations and Testing
13
7. Conclusion
1. Introduction
The Student Management System is a comprehensive software application
designed to facilitate the efficient management of student data and academic
activities within educational institutions. Traditionally, academic and
administrative activities such as student registration, course management,
attendance tracking, and report generation were carried out manually, which was
often time-consuming, error-prone, and difficult to maintain.
This project aims to automate and streamline these processes by providing a
centralized system where information related to students, staff, courses,
registrations, project transactions, and reports can be stored, accessed, and
managed easily. The system supports the core functionalities needed by
administrators, staff, and students, enhancing operational efficiency and ensuring
data integrity.
Using a relational database management system (RDBMS), the project structures
data effectively, enforces relationships through entity-relationship modeling, and
provides the foundation for flexible queries and reporting. This automation helps
reduce redundancy, minimizes errors, and allows for quick retrieval of academic
information, making it easier for stakeholders to make informed decisions.
The Student Management System thus represents an essential tool for modern
educational institutions seeking to improve their administrative capabilities, save
time, and provide better services to students and staff alike.
2. Features of Student Management System
• Student Registration and Record Management
Add, update, delete, and view student details efficiently.
• Course and Staff Management
Assign courses to students and manage faculty responsibilities.
• Staff Department Management
Maintain detailed faculty information along with their department or
course line.
• Student Registration and Transactions
Manage student registrations and related academic transactions with staff.
• Project Transactions and Reports
Record various project-related transactions and generate academic or
administrative reports.
• Search and Filter
Quickly retrieve specific records using efficient search and filter
mechanisms.
• User-Friendly Interface
Simple and responsive interface for easy data handling and interaction.
• Database Connectivity
Secure and structured data storage using MySQL with reliable relational
mapping.
3. Database Schema and Relationships
The database represents real-world entities involved in academic administration,
establishing relationships to maintain data integrity and eliminate redundancy.
Key Relationships:
• One-to-Many relationship: One staff member can manage multiple courses
or registrations.
• Many-to-One relationship: Each registration or transaction links to exactly
one student and one staff.
• One-to-One relationship: Each student has a unique student ID.
Entities and attributes:
Entity Attributes
Student stud_ID (PK), stfname, stlname, stcourse, styear, stcontact,
stage, stbirthdate, stgender
Staff staff_ID (PK), fname, lname, contact, address, gender
Courses_Offered course_ID (PK), staff_ID (FK)
Staff_Department staff_ID (PK), course_line
Student_Registration trans_ID (PK), tname, date, stud_ID (FK), staff_ID (FK)
Project_Transaction trans_ID (PK), name, stud_ID (FK), staff_ID (FK), tdate
Reports report_ID (PK), name, stud_rec (FK), trans_rep (FK)
This relational schema ensures consistent data updates, accurate reporting, and
supports a scalable academic management system.
ER Diagram
4. Design
Logical Design: key tables; Physical Design: indexes; Insert table design screenshot
placeholder.
.
5. Implementation and Code
Database creation snippet; stored procedure snippet; triggers snippet; execution
screenshots placeholder.
CREATE TABLE Student (
stud_ID INT PRIMARY KEY,
stfname VARCHAR(30),
stlname VARCHAR(30),
stcourse VARCHAR(30),
styear INT,
stcontact VARCHAR(20),
stage INT,
stbirthdate DATE,
stgender VARCHAR(10)
);
INSERT INTO Student VALUES
(1, 'Amit', 'Sharma', 'BSc CS', 2, '9000000123', 19, '2006-01-10', 'Male'),
(2, 'Priya', 'Desai', 'BSc Maths', 1, '9000001223', 18, '2007-06-15', 'Female'),
(3, 'Karan', 'Patel', 'BCA', 3, '9000011001', 20, '2005-02-20', 'Male'),
(4, 'Sonal', 'Gill', 'BSc CS', 2, '9000010002', 19, '2006-10-26', 'Female'),
(5, 'Rohit', 'Gupta', 'BSc Maths', 1, '9000033333', 18, '2007-01-31', 'Male');
CREATE TABLE Staff (
staff_ID INT PRIMARY KEY,
fname VARCHAR(30),
lname VARCHAR(30),
contact VARCHAR(20),
address VARCHAR(255),
gender VARCHAR(10)
);
INSERT INTO Staff VALUES
(101, 'Neha', 'Verma', '9200000001', 'Pune', 'Female'),
(102, 'Arjun', 'Rao', '9200002222', 'Mumbai', 'Male'),
(103, 'Preeti', 'Singh', '9200003333', 'Nashik', 'Female'),
(104, 'Amit', 'Joshi', '9200009999', 'Nagpur', 'Male');
CREATE TABLE Courses_Offered (
course_ID INT PRIMARY KEY,
staff_ID INT,
FOREIGN KEY (staff_ID) REFERENCES Staff(staff_ID)
);
INSERT INTO Courses_Offered VALUES
(201, 101),
(202, 102),
(203, 103),
(204, 104);
CREATE TABLE Staff_Department (
staff_ID INT,
course_line VARCHAR(40),
PRIMARY KEY (staff_ID, course_line),
FOREIGN KEY (staff_ID) REFERENCES Staff(staff_ID)
);
INSERT INTO Staff_Department VALUES
(101, 'Science'),
(102, 'Mathematics'),
(103, 'Computer Applications'),
(104, 'Physics');
.
CREATE TABLE Student_Registration (
trans_ID INT PRIMARY KEY,
tname VARCHAR(30),
date DATE,
stud_ID INT,
staff_ID INT,
FOREIGN KEY (stud_ID) REFERENCES Student(stud_ID),
FOREIGN KEY (staff_ID) REFERENCES Staff(staff_ID)
);
INSERT INTO Student_Registration VALUES
(1001, 'Freshers Registration', '2025-01-15', 1, 101),
(1002, 'Semester 1 Registration', '2025-01-20', 2, 102),
(1003, 'Special Registration', '2025-01-22', 3, 103),
(1004, 'Sports Registration', '2025-02-10', 4, 101),
(1005, 'Seminar Registration', '2025-03-01', 5, 104);
.
CREATE TABLE Project_Transaction (
trans_ID INT PRIMARY KEY,
name VARCHAR(30),
stud_ID INT,
staff_ID INT,
tdate DATE,
FOREIGN KEY (stud_ID) REFERENCES Student(stud_ID),
FOREIGN KEY (staff_ID) REFERENCES Staff(staff_ID)
);
INSERT INTO Project_Transaction VALUES
(3001, 'Seminar: AI', 1, 101, '2025-02-05'),
(3002, 'Workshop: Data Science', 2, 102, '2025-03-10'),
(3003, 'Exam Supervision', 1, 103, '2025-03-15'),
(3004, 'Annual Function', 4, 101, '2025-03-20'),
(3005, 'Library Setup', 5, 104, '2025-04-10');
CREATE TABLE Reports (
report_ID INT PRIMARY KEY,
name VARCHAR(30),
stud_rec INT,
trans_rep INT,
FOREIGN KEY (stud_rec) REFERENCES Student(stud_ID),
FOREIGN KEY (trans_rep) REFERENCES Project_Transaction(trans_ID)
);
INSERT INTO Reports VALUES
(401, 'Annual Report', 1, 3001),
(402, 'Maths Performance', 2, 3002),
(403, 'Paper Checking', 1, 3003),
(404, 'Function Review', 4, 3004),
(405, 'Library Audit', 5, 3005);
.
Sample Queries on Tables -
6. Operations and Testing
Testing Methodology
The project was tested systematically to ensure correct functionality and
reliability:
• Unit Testing: Each module—Student, Staff, Course, and Report—was tested
individually to verify expected performance.
• Integration Testing: Modules were combined to ensure smooth data flow
and proper linkage between tables.
• Functional Testing: All forms, buttons, and operations were checked for
correct response and data validation.
• Database Testing: Constraints such as primary keys, foreign keys, and
unique IDs were verified to maintain data consistency.
• Validation Testing: Incorrect or missing inputs were tested to ensure that
the system prevents invalid entries.
• Performance Testing: Multiple records were handled simultaneously to
confirm the system’s responsiveness and stability.
Test Results
All major test cases passed successfully. Student and staff records were correctly
saved, updated, and retrieved. Data relationships were maintained as per the ER
diagram, and all constraints worked properly without causing any redundancy or
data loss.
.
7. Conclusion
The Student Management System successfully automates the process of
managing student information in an educational institute. It provides a centralized
platform that improves accuracy, accessibility, and efficiency in handling academic
data. The system minimizes manual work, reduces the chances of error, and
enhances institutional management through effective data organization.
This project also demonstrates the practical application of DBMS
concepts, frontend-backend integration, and full-stack development principles.