Report on Hostel Management System
1. Project Overview
The Hostel Management System is designed to simplify and manage administra ve opera ons in
hostels. It helps manage student informa on, room alloca ons, payments, fines, a endance, leave
requests, and staff details. This system offers:
A user-friendly interface for administrators and users.
Robust backend func onality for database opera ons.
Indexing and query op miza on for efficient data retrieval.
2. Database Structure
2.1 Tables
1. Students
o Fields: student_id, name, email, phone_number, room_id, check_in_date,
check_out_date
o Primary Key: student_id
o Foreign Key: room_id (references rooms table)
2. Rooms
o Fields: room_id, room_number, room_type, capacity, available_beds
o Primary Key: room_id
3. Payments
o Fields: payment_id, student_id, amount, payment_date, due_date, fine_amount,
payment_status
o Primary Key: payment_id
o Foreign Key: student_id (references students table)
4. Fines
o Fields: fine_id, student_id, payment_id, fine_amount, fine_reason
o Primary Key: fine_id
o Foreign Keys: student_id (references students table), payment_id (references
payments table)
5. A endance
o Fields: a endance_id, student_id, date, status, leave_reason, leave_start, leave_end
o Primary Key: a endance_id
o Foreign Key: student_id (references students table)
6. Leave Requests
o Fields: leave_id, student_id, leave_start, leave_end, reason, status
o Primary Key: leave_id
o Foreign Key: student_id (references students table)
7. Staff
o Fields: staff_id, name, email, phone_number, role, salary
o Primary Key: staff_id
8. Room Alloca ons
o Fields: alloca on_id, student_id, room_id, alloca on_date
o Primary Key: alloca on_id
o Foreign Keys: student_id (references students table), room_id (references rooms
table)
9. Admin
o Fields: admin_id, username, password, email, role
o Primary Key: admin_id
3. Indexing
To op mize database performance:
1. Unique Index on room_number in rooms table for faster room queries.
2. Index on student_id in payments and a endance for efficient student-related queries.
3. Composite Index on student_id and payment_date in payments table for filtering by date
ranges.
4. Index on room_id in room_alloca ons for quick room alloca on lookups.
4. Triggers
Trigger Example: Automa cally Update Available Beds
CREATE OR REPLACE FUNCTION update_available_beds()
RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'INSERT') THEN
UPDATE rooms
SET available_beds = available_beds - 1
WHERE room_id = NEW.room_id;
ELSIF (TG_OP = 'DELETE') THEN
UPDATE rooms
SET available_beds = available_beds + 1
WHERE room_id = OLD.room_id;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER manage_beds
AFTER INSERT OR DELETE ON room_alloca ons
FOR EACH ROW EXECUTE FUNCTION update_available_beds();
5. Queries
5.1 CRUD Opera ons
1. Add Student
INSERT INTO students (name, email, phone_number, room_id, check_in_date,
check_out_date)
VALUES ('John Doe', '[email protected]', '1234567890', 1, '2023-11-01', '2024-05-01');
2. Update Room
UPDATE rooms
SET available_beds = 2
WHERE room_id = 1;
3. Delete Leave Request
DELETE FROM leave_requests
WHERE leave_id = 3;
4. Retrieve All Payments
SELECT * FROM payments;
5.2 Join Queries
1. Students and Room Details
SELECT s.name, r.room_number, r.room_type
FROM students s
JOIN rooms r ON s.room_id = r.room_id;
2. A endance and Payment Status
SELECT s.name, a.status, p.payment_status
FROM students s
INNER JOIN a endance a ON s.student_id = a.student_id
INNER JOIN payments p ON s.student_id = p.student_id;
5.3 Subqueries
1. Students with Total Fine Amount
SELECT s.name,
(SELECT SUM(fine_amount) FROM fines WHERE student_id = s.student_id) AS total_fine
FROM students s;
5.4 Indexing Queries
1. Payments by Student and Date Range
SELECT * FROM payments
WHERE student_id = 1 AND payment_date BETWEEN '2023-01-01' AND '2023-12-31';
2. Rooms Above Capacity
SELECT r.room_number, COUNT(ra.student_id) AS occupants, r.capacity
FROM rooms r
JOIN room_alloca ons ra ON r.room_id = ra.room_id
GROUP BY r.room_id
HAVING COUNT(ra.student_id) > r.capacity;
6. ER Diagram
Staff and Admin are not related any tables in database. These two are separate en es.
7. Frontend Integra on
The system's frontend is built with React and Bootstrap, featuring:
Query Interface for execu ng SQL queries dynamically.
Admin Dashboard for CRUD opera ons and indexing op ons.
Pages for displaying students, rooms, payments, fines, and a endance.
8. Normalisa on
The tables in database are normalised upto 3NF.
8. Features Implemented
Efficient Indexing: Ensures fast data retrieval for complex queries.
Dynamic Querying: Users can execute predefined queries or custom ones via the frontend.
Triggers: Automates cri cal updates like managing available beds.
Joins: Combines related data across mul ple tables for insigh ul reports.
Error Handling: Provides clear error messages for failed opera ons.
9. Future Enhancements
1. Implement role-based access for be er security.
2. Add analy cs dashboards for summarizing data.
3. Introduce real- me no fica ons for pending payments or leave approvals.
10. Conclusion
The Hostel Management System successfully streamlines hostel opera ons by managing students,
rooms, payments, a endance, and staff details efficiently. With features like dynamic querying,
indexing, and robust backend triggers, the system offers high performance and scalability. It provides
an intui ve user interface for administrators and users to perform CRUD opera ons and access
detailed reports.
Key highlights include:
Op mized database performance through indexing strategies.
Automa on of rou ne updates using triggers.
Comprehensive query support for retrieving insights and genera ng reports.
A scalable architecture that can adapt to addi onal modules and enhancements.
The project demonstrates the applica on of advanced database concepts like joins, subqueries,
indexing, and triggers, which are seamlessly integrated with a React-based frontend. Future
enhancements aim to improve security and provide advanced analy cs, making this system a robust
solu on for hostel management needs.
