Skip to content
Tutorials Center
My learning

Topics

Lesson 38 of 40 ยท Projects and Practice

Project: Library Management System in MariaDB

Build a library database step by step: schema and constraints, lending and returning books safely, overdue and fine reports, views, indexes and the MariaDB procedure and event behind each step.

In this project you design a small library database and use almost everything from the course: constraints and keys, joins and aggregates, window functions, views, procedures, triggers, events and indexes.

Each step shows the MariaDB version as read-only code where the syntax is MariaDB-specific (AUTO_INCREMENT, ENUM, procedures, events) and a runnable portable version with the same tables and queries so you can see real results.

What we are building

erDiagram
  MEMBERS ||--o{ LOANS : borrows
  BOOKS ||--o{ COPIES : "has copies"
  COPIES ||--o{ LOANS : "lent as"
  LOANS ||--o| FINES : "may produce"

A member borrows physical copies of a book; a late return may produce a fine.

Rules of the library:

  1. A copy can be on loan to only one member at a time.
  2. The loan period is 14 days; a late return costs 0.25 per day, capped at the book's replacement price.
  3. A member may hold at most 3 loans at once.
  4. Every action must be traceable.

Step 1: the schema

MariaDB version:

SQL
CREATE TABLE members (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name  VARCHAR(100) NOT NULL,
  email      VARCHAR(190) NOT NULL,
  joined_on  DATE NOT NULL DEFAULT (CURRENT_DATE),
  UNIQUE KEY uq_members_email (email)
) ENGINE=InnoDB;

CREATE TABLE books (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  isbn        CHAR(13) NOT NULL,
  title       VARCHAR(200) NOT NULL,
  author      VARCHAR(120) NOT NULL,
  genre       VARCHAR(40)  NOT NULL,
  price       DECIMAL(8,2) NOT NULL CHECK (price >= 0),
  UNIQUE KEY uq_books_isbn (isbn),
  FULLTEXT KEY ft_books (title, author)
) ENGINE=InnoDB;

CREATE TABLE copies (
  id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  book_id  INT UNSIGNED NOT NULL,
  status   ENUM('available','on_loan','lost') NOT NULL DEFAULT 'available',
  CONSTRAINT fk_copies_book FOREIGN KEY (book_id) REFERENCES books (id),
  KEY idx_copies_book_status (book_id, status)
) ENGINE=InnoDB;

CREATE TABLE loans (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  copy_id     INT UNSIGNED NOT NULL,
  member_id   INT UNSIGNED NOT NULL,
  borrowed_on DATE NOT NULL,
  due_on      DATE NOT NULL,
  returned_on DATE NULL,
  CONSTRAINT fk_loans_copy   FOREIGN KEY (copy_id)   REFERENCES copies (id),
  CONSTRAINT fk_loans_member FOREIGN KEY (member_id) REFERENCES members (id),
  CHECK (due_on >= borrowed_on),
  KEY idx_loans_member (member_id, returned_on),
  KEY idx_loans_open (returned_on, due_on)
) ENGINE=InnoDB;

Design notes: copies separates "the title" from "the physical object", so one book can have several copies; returned_on IS NULL means an open loan; indexes support "loans of this member" and "overdue open loans".

Runnable version (portable types) with sample data:

SQL Create the schema and load sample data

Press Run (or Ctrl + Enter) to see the result here.

Step 2: lending a book safely

Lending must (a) check the member's open loans, (b) pick an available copy, (c) mark it on loan and (d) create the loan, as one atomic unit. MariaDB version as a stored procedure:

SQL
DELIMITER //
CREATE PROCEDURE lend_book(IN p_member INT, IN p_book INT, OUT p_loan_id INT)
BEGIN
  DECLARE v_copy INT;
  DECLARE v_open INT;

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN ROLLBACK; RESIGNAL; END;

  START TRANSACTION;

  SELECT COUNT(*) INTO v_open FROM loans WHERE member_id = p_member AND returned_on IS NULL;
  IF v_open >= 3 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Loan limit reached';
  END IF;

  -- lock one available copy so two clerks cannot take the same one
  SELECT id INTO v_copy FROM copies
  WHERE book_id = p_book AND status = 'available'
  ORDER BY id LIMIT 1
  FOR UPDATE SKIP LOCKED;           -- SKIP LOCKED: MariaDB 10.6+

  IF v_copy IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No available copy';
  END IF;

  UPDATE copies SET status = 'on_loan' WHERE id = v_copy;
  INSERT INTO loans (copy_id, member_id, borrowed_on, due_on)
  VALUES (v_copy, p_member, CURDATE(), CURDATE() + INTERVAL 14 DAY);
  SET p_loan_id = LAST_INSERT_ID();

  COMMIT;
END //
DELIMITER ;

The runnable version performs the same steps as a script for member 3 borrowing book 3:

SQL Lend a copy of book 3 to member 3

Press Run (or Ctrl + Enter) to see the result here.

Returning a book is the mirror image: set returned_on, set the copy available, and compute the fine.

Step 3: returns and fines

Fine rule: 0.25 per late day, capped at the book price. As a query (this is also what a BEFORE UPDATE trigger or the return procedure would compute):

MariaDB version of the fine calculation in a function:

SQL
CREATE FUNCTION fine_for(p_due DATE, p_returned DATE, p_price DECIMAL(8,2))
RETURNS DECIMAL(8,2) DETERMINISTIC
RETURN LEAST(GREATEST(DATEDIFF(p_returned, p_due), 0) * 0.25, p_price);
SQL Fines for returned loans

Press Run (or Ctrl + Enter) to see the result here.

Loan 3 is 119 days late, 29.75 before the cap, so the fine is limited to the price 18.50. In MariaDB, LEAST and GREATEST replace the two-argument MIN/MAX of the portable engine.

Step 4: overdue report as a view

Today is 2026-03-15 in the examples. In MariaDB use CURDATE().

SQL
CREATE VIEW overdue_loans AS
SELECT l.id AS loan_id, m.full_name, m.email, b.title, l.due_on,
       DATEDIFF(CURDATE(), l.due_on) AS days_overdue
FROM loans l
JOIN members m ON m.id = l.member_id
JOIN copies  c ON c.id = l.copy_id
JOIN books   b ON b.id = c.book_id
WHERE l.returned_on IS NULL AND l.due_on < CURDATE();
SQL Overdue loans and who owes what

Press Run (or Ctrl + Enter) to see the result here.

A nightly event could read this view and insert reminder rows into an outbox table that the application mails.

Step 5: reports with aggregates and window functions

Which genres circulate most, and who are the most active members?

SQL Popularity ranking

Press Run (or Ctrl + Enter) to see the result here.

Step 6: enforce the 3-loan limit with a trigger

A trigger guarantees the rule for every client, including manual inserts:

SQL
DELIMITER //
CREATE TRIGGER loans_bi_limit
BEFORE INSERT ON loans
FOR EACH ROW
BEGIN
  IF (SELECT COUNT(*) FROM loans WHERE member_id = NEW.member_id AND returned_on IS NULL) >= 3 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Loan limit reached';
  END IF;
END //
DELIMITER ;
SQL The loan limit in a trigger

Press Run (or Ctrl + Enter) to see the result here.

Member 1 ends with 3 loans; the fourth was refused.

Step 7: indexes and a security setup

  • idx_loans_member (member_id, returned_on) serves "open loans of this member" and the trigger's count; idx_loans_open (returned_on, due_on) serves the overdue view; the unique ISBN index prevents duplicate books. Confirm each with EXPLAIN (explain and index design).
  • Accounts: library_app gets SELECT, INSERT, UPDATE on library.* and EXECUTE on the procedures (no DELETE, no DROP); reporter gets SELECT on the views only; backups use mariadb-dump --single-transaction --routines --events nightly (backup and restore, users, grants and roles).

Extensions to try

  1. A reservations table with a queue per book and a procedure that gives a returned copy to the first waiting member.
  2. A FULLTEXT search page: MATCH(title, author) AGAINST ('data' IN BOOLEAN MODE).
  3. System-versioned loans (WITH SYSTEM VERSIONING) to query "what did the loan table look like last Tuesday?".
  4. A monthly summary table refreshed by an event.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does the schema separate books from copies?

    Titles and physical objects have different attributes; modelling them separately avoids repeating book data per copy.
  2. 2. How does the lending procedure stop two clerks from lending the same copy?

    Row locking inside one transaction makes the selection and update atomic for concurrent sessions.
  3. 3. Which value means a loan is still open?

    NULL means not returned yet; compare with IS NULL, not =
  4. 4. What does LEAST(GREATEST(days_late, 0) * 0.25, price) do?

    GREATEST floors negative lateness at 0; LEAST caps the amount at the price.

Summary

  • Model titles, copies, members and loans with keys and constraints; use NULL in returned_on for open loans.
  • Do multi-step changes inside a transaction (procedure) and guard rules with triggers or constraints.
  • Views and window functions deliver reports; indexes follow the queries; accounts follow least privilege.

Next: Project: Sales Reporting Database