ER Design Project: Library System

Requirement

A library holds books. Each book has an ISBN, a title, a publisher and a year, and may have several authors. An author has an identifier and a name and may have written several books.

The library owns several physical copies of some books. Each copy has an accession number that is unique across the whole library, a shelf location and a condition.

Members have a membership number, a name, a category of either student or staff, and a contact number. Students may borrow four items, staff may borrow eight.

A copy is issued to a member on a date, and is due back on a date. When it is returned the return date is recorded, and a fine is calculated if it is late. The same copy is issued many times over its life, and the same member borrows many times.

Members may reserve a book, recording the date of reservation. A reservation is for a title, not for a particular copy.

The decision this project is really about

A book and a copy are different entities. The book is the title; the copy is the physical object on the shelf. Modelling them as one table is the classic library design mistake — you can then no longer say which of the six copies is on loan, and the ISBN stops identifying anything uniquely.

  WRONG                        RIGHT

  books                        books ( isbn, title, ... )
   isbn                          one row per TITLE
   title
   is_issued                   copies ( accession_no, isbn, ... )
                                 one row per PHYSICAL OBJECT
  six copies of one title
  cannot be represented       issues ( accession_no, ... )
                                a loan is of a COPY

Step 1 — Entities

COPY is strong, not weak, because the accession number is unique across the whole library. Had accession numbers restarted per title, COPY would have been weak with a partial key.

Step 2 — Relationships

Step 3 — The ER diagram

   +----------+  M                N  +----------+
   |   BOOK   |----< WRITTEN_BY >-----|  AUTHOR  |
   +----+-----+      (author_order)   +----------+
    1 |    | M
      |    +--------< RESERVES >-------+
      |          (reserved_on, status) | N
      |                          +-----v-----+
 < COPY_OF >                     |  MEMBER   |
      | N                        +-----+-----+
   +--v-------+                        | 1
   |   COPY   |                        |
   +----+-----+                  < ISSUED_TO >
      1 |                              | N
        +--------< ISSUED_COPY >-------+
                       | N
                +------v-------+
                |    ISSUE     |
                +--------------+
          issue_id, issued_on, due_on,
          returned_on (nullable), fine

Step 4 — The relational schema

  books    ( isbn PK, title NOT NULL, publisher, pub_year )

  authors  ( author_id PK, author_name NOT NULL )

  book_authors ( isbn -> books, author_id -> authors
               , author_order NOT NULL
               , PK ( isbn, author_id ) )

  copies   ( accession_no PK
           , isbn NOT NULL -> books
           , shelf, copy_condition )

  members  ( member_no PK, member_name NOT NULL
           , category NOT NULL          -- student or staff
           , contact )

  issues   ( issue_id PK
           , accession_no NOT NULL -> copies
           , member_no    NOT NULL -> members
           , issued_on    NOT NULL
           , due_on       NOT NULL
           , returned_on  NULL          -- null means ON LOAN
           , fine         DEFAULT 0 )

  reservations ( member_no -> members
               , isbn      -> books
               , reserved_on
               , status NOT NULL        -- waiting, met, cancelled
               , PK ( member_no, isbn, reserved_on ) )

  7 tables.

Step 5 — Where the rules actually live

The is_issued flag is the trap. It looks convenient and it duplicates a fact that the issue table already holds. The moment a return is recorded and the flag is not cleared, the catalogue lies. Derive it.

Verification questions

1. Which copies of a title are currently on loan, and to whom?
2. How many copies of a title does the library own, and how many are available now?
3. Which members have overdue items today, and what is the fine so far?
4. Which titles have a waiting reservation but no available copy?
5. How many times has one copy been issued in its life?

Common mistakes

• Merging book and copy into one table, making multiple copies unrepresentable.
• Adding an is_issued flag beside the issue history.
• Issuing a title rather than a copy, so the system cannot say which physical object is out.
• Making returned_on not null and creating a separate returns table, which complicates every query.
• Reserving a copy rather than a title, when the member does not care which copy they get.

Practice

1. Add a rule that a reservation expires after seven days, and say where it is enforced.
2. Write the condition that identifies every copy currently on loan.
3. Add fine payments, where a member may pay a fine in parts, and state whether the payment entity is weak.

Conclusion

Seven tables, and one modelling decision that carries the whole design: separate the title from the physical copy. The second lesson is to derive availability from the loan history rather than storing a flag that can drift.