A library database

The problem

Design and build the database for a small library: members, books, copies, loans and fines, with the queries the librarians need every day.

Requirements

• An ER model of members, books, copies and loans, with the cardinality of each relationship
• Tables in third normal form, each with a primary key and the foreign keys and constraints the rules need
• A loan cannot be made for a copy that is already out, which a query can check
• Queries for: books currently out and when they are due, members with overdue books, and the most borrowed books this month
• A view that shows each member's current loans with book titles
• A transaction that records a return and any fine together

Technologies

• SQL
• SQLite
• ER modelling
• Normalization
• Views
• Transactions

Step by step

1. List the entities and their attributes, and draw the ER model with cardinalities.
2. Turn the model into CREATE TABLE statements with keys and constraints.
3. Check each table against 1NF, 2NF and 3NF, and fix any problem.
4. Insert realistic sample data, including a member with no loans and a book with no copies out.
5. Write the everyday queries, checking each result by hand on the sample data.
6. Create the view, and write the return-and-fine transaction.
7. Add indexes for the searches that are run most often, and check the query plans.

Expected result

A working library database with its design, sample data and the queries the librarians need.

How it is evaluated

• Every table is in 3NF, and no fact is stored twice
• The constraints refuse impossible data
• Every query returns exactly the right rows on the sample data
• Changes that belong together are made in one transaction

Ways to improve it

• Add reservations for books that are all out
• Keep a history of fines that have been paid
• Report each member's reading by subject