Database Overview

The GPH Library Management System uses MySQL as the database management system to store and manage all project-related data securely and efficiently.

The database plays an important role in handling:

• student records

• teacher records

• books data

• issued books

• renewal requests

• fines

• notifications

• OTP verification

• question papers

The database is designed using relational tables with proper primary keys and relationships to maintain data consistency and security.

Database Tasks

• To store all library records digitally.

• To manage user authentication securely.

• To track issued and returned books.

• To maintain fine and overdue records.

• To manage question paper uploads.

• To support approval workflows.

• To improve data retrieval speed and accuracy.

Database Tables Used

1. Students Table

Stores student information.

Fields:

• id

• name

• roll_no

• email

• password

• branch

• semester

• is_approved

2. Teachers Table

Stores teacher details.

Fields:

• id

• staff_id

• email

• password

• is_approved

3. Books Table

Stores all book records.

Fields:

• id

• book_name

• author

• category

• quantity

• available_books

4. Borrow Requests Table

Stores book request records from students.

Fields:

• id

• student_id

• book_id

• requested_days

• expected_return_date

• status

5. Issued Books Table

Stores issued book details.

Fields:

• id

• student_id

• book_id

• issue_date

• due_date

• return_date

• fine_amount

6. Renew Requests Table

Stores renewal requests.

Fields:

• id

• issued_book_id

• requested_extra_days

• new_due_date

• status

7. Question Papers Table

Stores uploaded question papers.

Fields:

• id

• teacher_id

• subject_name

• semester

• branch

• file_name

• status

8. Password Reset OTP Table

Stores OTP verification records.

Fields:

• id

• user_email

• otp

• created_at

9. Notifications Table

Stores reminder and alert notifications.

Fields:

• id

• user_id

• message

• created_at

Database Relationships

The database tables are interconnected using:

• Primary Keys

• Foreign Keys

Example:

• students.id → borrow_requests.student_id

• books.id → issued_books.book_id

• teachers.id → question_papers.teacher_id

These relationships help maintain data integrity and improve database efficiency.

Advantages of Database Design

• Secure data management

• Faster data retrieval

• Reduced redundancy

• Better relationship handling

• Easy scalability

• Efficient record tracking

ER Diagram