School Library Management System

A database-backed web application for managing a network of school libraries, developed by Team 81 for the Database Systems course at the School of Electrical and Computer Engineering, National Technical University of Athens (NTUA), in 2022–2023.

The project covers the full path from entity–relationship modeling and relational schema design to SQL business rules and an Express/EJS application. It supports library registration, book catalogues, borrowing, reservations, review moderation, and reporting across schools.

Node.js · Express · EJS · MySQL · JavaScript · SQL

Features · Database design · Getting started · Report · Contributors

Features

The application provides separate interfaces for three user groups:

User group	Main capabilities
Central administrator	Approve or reject new library/operator applications; inspect network-wide statistics on borrowing, authors, categories, and operator activity; change account password.
Library operator	Manage the school's books and members; approve membership applications; record loans and returns; manage reservations; moderate reviews; search overdue borrowers and inspect ratings.
Students and teachers	Register for library membership; search their school's books; make or cancel reservations; view borrowing history; submit and view reviews; manage account details, with profile editing available to teachers.

Examples of reporting implemented through SQL views and procedures include:

• Borrowing counts per school for a selected month and year.
• Authors and teachers associated with a selected thematic category.
• Teachers with the most borrowings and authors whose books have never been borrowed.
• Frequently co-borrowed category pairs and operators with matching borrowing totals.
• Overdue borrowers and average review ratings by borrower/category.

The interface is primarily in Greek. This is an academic demonstration with seeded data; see Project status for its practical limits.

Database design

The schema contains 11 tables, 13 stored procedures, 16 views, 7 triggers, 3 explicit search indexes, and 1 scheduled event.

Part of the model	Tables and purpose
Administration	administrator, school_unit, operator: schools, operators, and approval relationships.
Membership	student_professor, borrower_card: member roles, approval state, and issued library cards.
Catalogue	book, book_authors, book_thematic_categories: school-specific book records with multiple authors and categories.
Circulation	book_borrowing, reservations: loans, due/return dates, and reservation status.
Feedback	reviews: ratings, review text, and moderation status.

Foreign keys connect these records, while composite keys represent multi-valued book attributes and several circulation/review relationships. Explicit indexes cover book titles, authors, and thematic categories.

The database implements borrowing and reservation rules through stored procedures called by triggers. These include different limits for students and teachers, overdue-book checks, duplicate-borrowing checks, and copy availability. Other triggers issue borrower cards, update availability when a book is returned, and restrict reviews to books the member has borrowed. A daily event expires reservations.

View the relational schema

The executable source of truth is MySQL/library-schema.sql. The report and diagrams document the design process and may contain earlier descriptions of individual attributes.

Application structure

Express routes dispatch requests to controllers, which execute SQL queries or call stored procedures through a mysql2 connection pool. EJS templates render the results, and express-session stores login state.

Location	Contents
application/app.js	Express setup, session configuration, and route mounting.
application/database.js	Environment-configurable MySQL connection pool.
application/routes/	Administrator, operator, member, and home endpoints.
application/controllers/	Request handling and database interaction.
application/views/	EJS pages for forms, dashboards, reports, and result messages.
MySQL/	Schema/business logic, sample data, and the original delete script.
Diagrams/	Initial/final ER diagrams and relational schema.
Documents/	Final report and original assignment, both in Greek.
Scripts for dummy data/	Notebooks used to generate sample records.
book_borrowing_guide.txt	Original guide to the sample loan/reservation scenarios.

Getting started

1. Prerequisites

• Node.js 22.9 or later and npm. The start scripts use Node's built-in optional .env loading; no separate environment-file package is needed.
• A local MySQL-compatible database server and a SQL client such as MySQL Workbench or the mysql command-line client. The original project was run using XAMPP and Workbench on Windows.
• A database account with privileges to create the library schema, routines, triggers, and events. The original schema also enables the global event scheduler, which requires administrative privileges.

The documentation/configuration update was checked with Node.js 24.19.0. A fresh database import and database-backed workflows have not been revalidated across server versions.

2. Clone and install

git clone https://github.com/johnskop7/Databases_Project_2023_Team81.git
cd Databases_Project_2023_Team81/application
npm ci

Dependencies are installed from the committed lockfile. node_modules/ is generated locally and is not versioned.

3. Initialize the database

Use a disposable local database: library-schema.sql drops and recreates the library schema. The insert/delete scripts also remove data; they are not application migrations.

From the repository root, open a MySQL client:

mysql -u root -p --default-character-set=utf8mb4

Then execute these statements in the same client session, in this order:

SET SQL_SAFE_UPDATES = 0;
SOURCE MySQL/library-schema.sql;
SOURCE MySQL/library-insert.sql;
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;

Alternatively, execute both files in order in the same Workbench connection, with safe updates disabled. The original sample-data script inserts some related records out of foreign-key order and relies on the checks disabled by the schema script. Running the imports in separate connections can therefore fail. Resolve any import errors before starting the app; do not suppress them with a force-import option.

The schema contains SET GLOBAL event_scheduler = ON. If your database account cannot change this setting, ask the local database administrator to enable it and handle that statement before importing. Reservation expiry depends on the event scheduler.

4. Configure the application

Copy application/.env.example to application/.env. In PowerShell, for example:

Copy-Item .env.example .env

On Linux/macOS, use cp .env.example .env. Edit the file to match your local server:

Variable	Default / purpose
DB_HOST	127.0.0.1
DB_PORT	3306
DB_USER	root; replace with your local database account if appropriate.
DB_PASSWORD	Empty by default; set your database password.
DB_NAME	library; this must match the imported schema.
SERVER_PORT	3000
SESSION_SECRET	Set a private random value to keep sessions stable across restarts. If empty, the app generates a new value at startup.

Local .env files are ignored by Git. To generate a session secret, run node -e "console.log(require('crypto').randomBytes(32).toString('hex'))" and paste the output into the file.

5. Start and explore

From application/:

npm start

Open http://localhost:3000. For automatic restarts during development, use npm run dev.

The following demonstration accounts are defined in the sample-data script:

Interface	URL	Username	Password
Administrator	/admin_login	admin	123
Library operator	/operator_login	kristin60	12
Teacher/member	/member_login	manderson	123

These are public sample credentials for the local demonstration. Other sample users are listed in library-insert.sql.

Project status

The original database and application workflows are preserved as coursework. The repository now includes explicit start scripts, local configuration, reproducible dependency installation, and documentation aimed at readers exploring the project.

• Seed records contain historical dates. Overdue status, reservation expiry, and date-dependent reports will reflect the current database date; they may differ from the report's original examples.
• Account passwords are stored in plain text, sessions use the default in-memory store, and route authorization/input handling need further work before deployment. Use sample data locally.
• The administrator's “Backup and restore” button is present in the interface but has no implemented action.
• There is no automated database integration test suite. Starting the server and loading login pages does not verify database-backed operations.

Documentation

• Final project report (Greek): database design, SQL implementation, application walkthrough, and original installation instructions.
• Original assignment (Greek).
• Original assessment/submission notes: the former README, preserved for historical context.

Contributors

Developed collaboratively by Team 81, ECE NTUA, academic year 2022–2023:

Team member	GitHub
Christos Kranias	kranias2001
Ioannis Litsos	johnskop7
Athanasios-Rafail Remoundos	Thanasisrem

The database design, application, and report are presented as shared team work. Individual commits remain available in the repository history.