ReadEase is a college DBMS mini project built with HTML, CSS, JavaScript, Node.js, Express.js, and MySQL. It demonstrates normalized schema design, primary keys, foreign keys, one-to-many relationships, many-to-many relationships, constraints, CRUD operations, joins, aggregate queries, and beginner-friendly REST APIs.
ReadEase
├── frontend/ HTML, CSS, and fetch API JavaScript
├── backend/
│ ├── config/ MySQL connection pool
│ ├── controllers/ Request handling and SQL logic
│ ├── routes/ REST API endpoints
│ └── server.js Express app entry point
├── database/
│ ├── schema.sql CREATE TABLE + sample data
│ └── queries.sql DBMS demonstration queries
└── docs/
The frontend communicates with the backend using the browser fetch() API. The backend exposes REST endpoints. The backend uses mysql2 to run SQL queries on MySQL.
Student stores student account details. It has a primary key student_id and a unique email constraint so the same email cannot register twice.
Book stores book-level data such as title, ISBN, and published year. It does not store copy availability because a real library can own many physical copies of the same book.
Author stores author names separately. This avoids repeating author names for every book.
BookAuthor is a junction table. It is needed because one book can have many authors and one author can write many books. This is a many-to-many relationship.
Category stores category names such as Programming, Database, and Fiction.
BookCategory is another junction table. It allows one book to belong to multiple categories and one category to contain many books.
BookCopy tracks physical copies separately. If a library has five copies of Clean Code, there is one row in Book and five rows in BookCopy. This makes borrowing accurate because users borrow a physical copy, not just a book title.
BorrowRecord stores issue and return history. It connects a student to a specific copy and tracks borrow_date, due_date, and return_date.
Run database/schema.sql in MySQL Workbench to create the database, tables, constraints, indexes, and sample data.
Run database/queries.sql to demonstrate SELECT, JOIN, aggregate, UPDATE, and DELETE queries.
Key DBMS concepts demonstrated:
- Primary keys: every table has a unique identifier.
- Foreign keys: child tables reference parent tables.
- One-to-many:
BooktoBookCopy,StudenttoBorrowRecord. - Many-to-many:
BooktoAuthor,BooktoCategory. - Constraints:
NOT NULL,UNIQUE,CHECK,ENUM, foreign keys. - Joins: used to combine normalized tables for meaningful output.
- Aggregate functions:
COUNT,SUM,GROUP_CONCAT.
The backend lives in backend. Main files:
- backend/server.js: Express app setup.
- backend/config/db.js: MySQL connection pool.
- backend/routes: API route definitions.
- backend/controllers: SQL query logic.
Important packages:
npm install express mysql2 dotenv cors
npm install --save-dev nodemonThe frontend lives in frontend. Pages:
index.html: login and register.dashboard.html: student dashboard and borrow history.books.html: book listing and search.borrow.html: borrow an available book.return.html: return currently borrowed books.admin.html: add authors, categories, books, copies, and view records.
The shared API helper is frontend/js/api.js.
Base URL:
http://localhost:5000/api
POST /auth/register
{
"name": "Nisha Verma",
"email": "nisha@example.com",
"password": "12345",
"department": "Computer Science"
}POST /auth/login
{
"email": "aarav@example.com",
"password": "12345"
}GET /books
GET /books?search=database
POST /borrow
{
"student_id": 1,
"book_id": 3
}PUT /borrow/return/1
GET /borrow/student/1
POST /admin/authors
{ "author_name": "Sanjay Sharma" }POST /admin/categories
{ "category_name": "Operating Systems" }POST /admin/books
{
"title": "Database Design Basics",
"isbn": "9780000000001",
"published_year": 2024,
"author_ids": [1],
"category_ids": [3]
}POST /admin/copies
{ "book_id": 1 }GET /admin/students
GET /admin/borrow-records
GET /admin/overdue
- Install Node.js on Windows 11.
- Install MySQL Server and MySQL Workbench.
- Open MySQL Workbench.
- Run database/schema.sql.
- In the project root, create
.envfrom.env.example. - Update your MySQL password in
.env. - Install dependencies:
npm install- Start the server:
npm start- Open:
http://localhost:5000
Sample login:
Email: aarav@example.com
Password: 12345
Normalization means splitting data into related tables to reduce duplication and improve accuracy. In ReadEase, authors are stored in Author, categories are stored in Category, and book copies are stored in BookCopy. This avoids repeating the same author or category text again and again.
A single book can have multiple authors. A single author can write multiple books. This cannot be represented cleanly with only one foreign key in Book. The BookAuthor table solves this by storing pairs of book_id and author_id.
The same logic applies to BookCategory.
A foreign key points from one table to another table. For example, BookCopy.book_id points to Book.book_id. This means every copy must belong to a valid book.
Normalized data is split across multiple tables. Joins combine those tables when we need meaningful output. A borrow record only stores IDs, but users need to see student names and book titles. Joins connect those IDs to readable data.
Think of a join as matching rows using a common value. If BorrowRecord.copy_id = BookCopy.copy_id, SQL can attach the copy details to the borrow record. Then BookCopy.book_id = Book.book_id attaches the book title.
INNER JOIN returns only matching rows from both tables. LEFT JOIN returns all rows from the left table, even if the right table has no match.
JOIN explains how tables are connected. WHERE filters which rows should appear.
Example:
FROM BorrowRecord br
INNER JOIN Student s ON br.student_id = s.student_id
WHERE br.return_date IS NULLThe JOIN connects borrow records to students. The WHERE clause keeps only books that are not returned.
Indexes make searching faster. ReadEase adds indexes on email, book title, copy status, and due date because these columns are commonly searched or filtered. Too many indexes can slow inserts and updates, so indexes should be added only where they help frequent queries.
Large systems add staff roles, fine calculation, reservations, barcode scanning, audit logs, full-text search, email reminders, payment integrations, and stronger authentication. They also add caching, backups, indexes, transactions, and reporting dashboards.
ReadEase is a Smart Library Management System designed to manage students, books, authors, categories, physical book copies, and borrowing records. The system uses a normalized MySQL database and a Node.js backend to provide REST APIs for student and admin operations.
Libraries need accurate tracking of books, copies, students, borrowing dates, due dates, and returns. Manual systems are slow and error-prone. ReadEase digitizes the core library workflow with a clean database design and simple web interface.
Manual library management makes it difficult to know which books are available, who borrowed a copy, when it is due, and which books are overdue. The project solves this by creating a structured DBMS-backed application.
- Register and login students.
- Maintain books, authors, categories, and copies.
- Borrow and return physical book copies.
- Prevent issuing unavailable copies.
- Track due dates and overdue books.
- Demonstrate DBMS concepts using SQL.
The project covers student borrowing, return tracking, admin book management, availability tracking, and DBMS query demonstration. It is suitable for a college mini project and can be expanded into a production library system.
Traditional library systems often use registers or simple spreadsheets. These systems lack automatic availability checks, due date tracking, and relational reporting.
ReadEase provides a web-based system backed by MySQL. It uses normalized tables, foreign keys, and REST APIs to manage books and borrowing accurately.
- Student registration and login.
- View and search books.
- Borrow available books.
- Return borrowed books.
- Admin can add authors, categories, books, and copies.
- Admin can view students, borrow records, and overdue books.
- Simple and responsive UI.
- Beginner-friendly code.
- Reliable relational constraints.
- Local Windows 11 setup.
- Maintainable MVC-style structure.
Entities are Student, Book, Author, Category, BookCopy, and BorrowRecord. BookAuthor and BookCategory are relationship tables. A student can have many borrow records. A book can have many copies. A borrow record belongs to exactly one student and one copy.
Level 0: Student and Admin interact with ReadEase. ReadEase reads and writes data to MySQL.
Level 1: Student sends login, search, borrow, and return requests. Admin sends add book, add author, add category, add copy, and report requests. The backend validates requests, updates the database, and returns responses to the frontend.
ReadEase successfully demonstrates a practical DBMS application with normalized schema design, constraints, joins, CRUD operations, and a usable frontend. It shows how relational databases support real-world workflows.
- Password hashing with bcrypt.
- Admin authentication.
- Fine calculation.
- Book reservation queue.
- Email due-date reminders.
- Barcode or QR scanning.
- Advanced search with full-text indexes.
- Deployment with cloud database.
What is normalization?
Normalization is the process of organizing data into multiple related tables to reduce duplication and improve consistency.
Why is BookCopy separate from Book?
Book stores title-level information. BookCopy stores each physical copy, so availability can be tracked accurately.
What is a primary key?
A primary key uniquely identifies each row in a table.
What is a foreign key?
A foreign key links one table to another and protects referential integrity.
What is a junction table?
A junction table represents a many-to-many relationship, such as books and authors.
Difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only matching rows. LEFT JOIN returns all rows from the left table and matching rows from the right table when available.
Which aggregate functions are used?
COUNT, SUM, and GROUP_CONCAT are used for copy counts and grouped author/category names.
How does borrowing work?
The backend finds one available copy, marks it as borrowed, and inserts a borrow record with borrow and due dates.
How does returning work?
The backend updates the borrow record return date and changes the copy status back to available.
What can be improved later?
Security, admin roles, fines, reservations, reminders, better indexing, and deployment can be added.