Student Database Management System

A comprehensive student database management system built with Java, SQL (PostgreSQL/Supabase), HTML, CSS, and JavaScript.

Project Structure

student-database-management/
├── src/
│   ├── main/
│   │   ├── java/
│   │   │   └── com/
│   │   │       └── studentdb/
│   │   │           ├── model/
│   │   │           │   └── Student.java          # Student entity model
│   │   │           ├── dao/
│   │   │           │   └── StudentDAO.java       # Database access layer
│   │   │           ├── servlet/
│   │   │           │   └── StudentServlet.java   # REST API endpoints
│   │   │           └── util/
│   │   │               └── DatabaseConfig.java   # Database configuration
│   │   ├── resources/
│   │   │   └── db.properties                     # Database connection properties
│   │   └── webapp/
│   │       ├── WEB-INF/
│   │       │   └── web.xml                       # Servlet configuration
│   │       ├── css/
│   │       │   └── styles.css                    # Application styles
│   │       ├── js/
│   │       │   └── app.js                        # Frontend JavaScript
│   │       └── index.html                        # Main HTML page
├── pom.xml                                        # Maven configuration
└── README.md

Features

• CRUD Operations: Create, Read, Update, and Delete student records
• Search Functionality: Search students by name, ID, email, or major
• Real-time Statistics: View total students, active students, and average GPA
• Responsive Design: Modern, professional UI that works on all devices
• RESTful API: Clean API architecture using Java Servlets
• Database Integration: PostgreSQL database through Supabase
• Data Validation: Client and server-side validation

Technologies Used

Backend

• Java 11: Core programming language
• Java Servlets: REST API implementation
• JDBC: Database connectivity
• PostgreSQL: Relational database (Supabase)
• Maven: Build and dependency management
• Gson: JSON serialization/deserialization

Frontend

• HTML5: Structure and markup
• CSS3: Styling with modern design principles
• JavaScript (ES6+): Dynamic interactions and AJAX calls
• Fetch API: HTTP requests to backend

Database Schema

Students Table

Column	Type	Description
id	bigserial	Primary key
student_id	text	Unique student registration number
first_name	text	Student's first name
last_name	text	Student's last name
email	text	Student's email (unique)
phone	text	Student's phone number
date_of_birth	date	Student's date of birth
enrollment_date	date	Date of enrollment
major	text	Student's major/field of study
gpa	numeric(3,2)	Grade point average (0.00-4.00)
status	text	Enrollment status (active/inactive/graduated)
created_at	timestamptz	Record creation timestamp
updated_at	timestamptz	Record last update timestamp

API Endpoints

• GET /api/students - Get all students
• GET /api/students?search={term} - Search students
• GET /api/students/{id} - Get student by ID
• POST /api/students - Create new student
• PUT /api/students/{id} - Update student
• DELETE /api/students/{id} - Delete student

Setup Instructions

Prerequisites

• Java Development Kit (JDK) 11 or higher
• Apache Maven 3.6+
• Apache Tomcat 9.0+ or any Java EE application server
• PostgreSQL database (Supabase account)

Database Configuration

1. Update src/main/resources/db.properties with your database credentials:

db.url=jdbc:postgresql://[your-supabase-host]:5432/postgres
db.user=postgres
db.password=your_password

2. The database schema is already created in Supabase with proper RLS policies

Build and Run

1. Clone or download the project

2. Build the project:

   mvn clean install

3. Deploy to Tomcat:

   • Copy the generated WAR file from target/student-database-management.war to your Tomcat webapps directory
   • Start Tomcat server

4. Or run with Maven:

   mvn tomcat7:run

5. Access the application:

   • Open browser and navigate to: http://localhost:8080/

Usage

Adding a Student

1. Click "Add New Student" button
2. Fill in the required fields (Student ID, First Name, Last Name, Email)
3. Optionally fill in additional fields (Phone, DOB, Major, GPA, etc.)
4. Click "Save Student"

Editing a Student

1. Click the "Edit" button next to a student record
2. Modify the fields as needed
3. Click "Save Student"

Deleting a Student

1. Click the "Delete" button next to a student record
2. Confirm the deletion in the popup dialog

Searching Students

1. Enter search term in the search box
2. Click "Search" or press Enter
3. Results will filter based on name, ID, email, or major
4. Click "Clear" to reset the search

Security Features

• Row Level Security (RLS) enabled on database
• Prepared statements to prevent SQL injection
• Input validation on client and server side
• XSS protection with HTML escaping
• Secure database connection handling

Design Principles

• Modular Architecture: Separation of concerns with Model, DAO, Servlet layers
• RESTful Design: Clean API following REST principles
• Responsive UI: Mobile-first, accessible design
• Error Handling: Comprehensive error handling and user feedback
• Code Quality: Clean, maintainable, well-documented code

Future Enhancements

• User authentication and authorization
• Batch import/export functionality
• Advanced filtering and sorting
• Student photo upload
• Course enrollment tracking
• Grade management system
• Email notifications
• PDF report generation

License

This project is created for educational purposes.

Author

Student Database Management System v1.0.0