Library Management System
Titles versus physical copies: why a loan points at a copy and not at a book, and how a copy gets an external identifier built from its title.
Beginner
The diagram
Opening an example creates a copy in your own projects. The original example is never modified.
The problem
A city library keeps a catalogue of the books it owns and lends them to its members. A title such as a novel is bought in several physical copies, and those copies are not interchangeable: one may be damaged, another may be in repair, a third may be on the shelf. When a member borrows something, the librarian must record which physical copy left the building, on which day, when it is due back and, later, when it was actually returned. The same member may hold several loans at the same time, and the same copy is lent again and again over the years, so the history of a copy has to be kept and not overwritten. A title can be in the catalogue before any copy has been purchased, and a newly registered member may not have borrowed anything yet. The library also wants to answer two questions quickly: what does this member currently have at home, and where is copy number 2 of this title?
Requirements
- The system must record every title in the catalogue with its ISBN, its title and, when known, the year of publication.
- The system must record every physical copy the library owns, with the copy number printed on its label and its current status.
- The system must record members with a unique membership number, surname, first name and e-mail address.
- The system must record every loan, with the copy lent, the member who borrowed it, the loan date, the due date and the return date.
- A title may have one or more copies in the catalogue, and every copy belongs to exactly one title.
- A member may have zero, one or many loans, and every loan is made by exactly one member.
- A copy may have zero, one or many loans over time, and every loan concerns exactly one copy.
- A copy cannot start two loans on the same day.
Entities and attributes
| Entity | Attributes | Identifier |
|---|---|---|
| Book | isbn, title, publication_year | isbn |
| Copy | copy_number, status | copy_number |
| Member | membership_no, last_name, first_name, email | membership_no |
| Loan | loan_date, due_date, return_date | (copy_number, loan_date) |
Relations and cardinalities
| Relation | From | To | Cardinality (min, max) | Note |
|---|---|---|---|---|
| HasCopy | Book | Copy | (1,1) – (1,N) |
One-to-many from a title to its physical items: a book has one or more copies and every copy belongs to exactly one book. The relation carries sequence_no, the ordinal of the copy inside its title, which with isbn forms the copy's external identifier. On this diagram the tight (1,1) is drawn on the Book branch and the (1,N) on the Copy branch, the reverse of the convention used by the other two relations here; read the pair by meaning: many copies per title, one title per copy. |
| Borrows | Member | Loan | (0,N) – (1,1) |
A member may have zero, one or many loans: a member who has just registered has none, and a member who has returned everything still keeps the history. A loan is made by exactly one member, so the minimum on the Loan side is 1 and the foreign key membership_no sits on Loan. |
| LoansCopy | Copy | Loan | (0,N) – (1,1) |
A copy may have zero, one or many loans over time: a copy just bought has never left the library, and returned loans are kept rather than deleted. A loan concerns exactly one copy, and this (1,1) is the whole point of the example: the loan is of the copy, not of the title. |
Reading the diagram
- The diagram has four entities and three relations. Book and Copy are the two that students usually merge, and keeping them apart is the whole point of the example: Book describes a title (an ISBN, a title, a year of publication), Copy describes one physical object on the shelf (a copy number, a status).
- HasCopy links them one-to-many: a title has at least one copy, and every copy belongs to exactly one title. The relation carries sequence_no, the ordinal of the copy inside its title, which is the attribute that turns 'copy 2' into something the database can identify. Note the direction of the two tags: here the tight (1,1) sits on the Book branch and the (1,N) on the Copy branch, the opposite of the convention used by the other relations in this same diagram, so read them by meaning rather than by position.
- copy_number is the external identifier printed on the label. It is unique among the copies of one title, not among all the copies of the library, so the real identity of a copy is the pair (isbn, sequence_no). In the relational schema that becomes a uniqueness constraint over the two columns, while copy_number stays as the key the librarian actually types.
- Loan is an entity, not an association between Member and Copy. As an association it could not hold loan_date, due_date and return_date, and it could not be the subject of its own queries. The diagram therefore shows two one-to-many relations, Borrows and LoansCopy, both with (1,1) on the Loan branch: every loan is made by exactly one member and concerns exactly one copy.
- Those two (1,1) tags are the reason a loan is a loan of a copy and not of a book. Read from the loan side they give the sentence the example is built on: one loan, one member, one physical copy. A loan of a title would leave the librarian unable to say which item is missing from the shelf, and unable to record that copy 2 came back while copy 5 is still out.
- The (0,N) on Member and on Copy express history, not current state. A member who has never borrowed anything and a copy that has never left the library are both legal, and a member who has returned twenty books still has twenty loans. This is why the minimum is 0 and not 1: forcing a loan to exist for every member would be false the moment a new member registers.
- return_date is the only nullable attribute in the diagram, and the NULL is information: it marks the loans that are still open. Everything else — title, copy number, loan date, due date — is mandatory. Finally, the title is stored once, in Book, and a loan reaches it through Copy: no title is repeated in Loan, which is the absence of redundancy that Module 6 will name.
From the conceptual model to the logical schema
Book(isbn, title, publication_year) PRIMARY KEY (isbn) Copy(copy_number, isbn, sequence_no, status) PRIMARY KEY (copy_number) FOREIGN KEY (isbn) REFERENCES Book(isbn) UNIQUE (isbn, sequence_no) Member(membership_no, last_name, first_name, email) PRIMARY KEY (membership_no) Loan(copy_number, loan_date, membership_no, due_date, return_date) PRIMARY KEY (copy_number, loan_date) FOREIGN KEY (copy_number) REFERENCES Copy(copy_number) FOREIGN KEY (membership_no) REFERENCES Member(membership_no)