Table of Contents
Type: Technical Report | Subject: Computer Science | Level: Undergraduate | Word Count: ~3,000 words
This model Technical Report was produced by an Essays UK specialist as reference material for learning purposes only. For support in this field, see our computer science specialists.
Overview: This model technical report documents the design of a relational database for a medium-sized academic library. It works through requirements analysis, an entity-relationship model, normalisation to third normal form and a physical SQL schema, then evaluates the design against the stated requirements and sets out recommendations for implementation.
The growth in collection size and membership at many academic libraries has made manual and spreadsheet-based record keeping difficult to sustain, with duplicated entries and the absence of enforced relationships leading to errors in loans, reservations and fines. This report documents the design of a relational database intended to support the core operations of a medium-sized university library: cataloguing stock, managing membership, issuing and returning loans, handling reservations and recording fines.
The design was developed using the established three-level approach of conceptual, logical and physical modelling (Connolly and Begg, 2015). An entity-relationship (ER) model captured the principal entities — Member, Title, Copy, Author, Loan, Reservation and Fine — and the relationships between them. The logical model was normalised to third normal form (3NF) to remove redundancy and reduce the risk of update anomalies, and the resulting relations were translated into a physical schema expressed in SQL. The design was evaluated against the original requirements and found to satisfy them, while several limitations and opportunities for extension were identified. The report closes with recommendations for implementation, testing and future development.
A library is, in data terms, a collection of items that are repeatedly issued to and returned by members over time. Even a modest academic library may hold tens of thousands of items and serve several thousand members, with each item borrowed many times across its lifetime. Where this activity is tracked on paper or in isolated spreadsheets, the same information is often stored in more than one place: a member’s details re-typed for every loan, a book’s title re-entered for every copy. This redundancy is the source of the classic data anomalies that relational theory was designed to prevent (Codd, 1970). When one copy of a repeated value is updated and another is not, the data becomes inconsistent and reports drawn from it become unreliable.
A well-designed relational database addresses these problems by storing each fact once and using keys to link related facts. The database management system then enforces the rules of the domain — for example, that a loan cannot exist without a valid member and copy — through referential integrity constraints. This report sets out how such a database was designed for the library context.
The aim of this project was to design a normalised relational database that supports the routine operations of an academic library. The objectives were:
The design covers cataloguing, membership, loans, reservations and fines. It deliberately excludes acquisitions and supplier invoicing, inter-library loans, and the user-interface and application logic above the database. These exclusions keep the report focused on data design while leaving clear points at which the schema could later be extended.
Requirements were gathered by reviewing the existing spreadsheet records and typical library workflows. They are grouped below into functional requirements (what the system must do), non-functional requirements (qualities it must exhibit) and data requirements (what must be stored).
The principal data items are members, titles, physical copies, authors, publishers, categories, loans, reservations, fines and the staff who process transactions. A key observation is the distinction between a title (a bibliographic record, such as a particular edition of a textbook) and a copy (a single physical item on the shelf): a library may hold five copies of one title, and a loan is always of a copy, whereas a reservation is placed against a title. Capturing this distinction is central to the design.
The database was designed using the three-level methodology widely recommended in the literature (Connolly and Begg, 2015; Elmasri and Navathe, 2016). This separates the design into three progressively more detailed stages:
Working top-down keeps early decisions free of implementation detail: the structure of the data is settled before questions of storage and performance arise. The following sections work through each stage in turn.
Seven core entities were identified, supported by four reference entities. An entity type represents a class of real-world objects about which data is stored, and each occurrence is uniquely identified by a key attribute. The entities, their key attributes and a selection of descriptive attributes are summarised below.
| Entity | Key Attribute | Selected Descriptive Attributes |
|---|---|---|
| Member | MemberID | FirstName, LastName, Email, Phone, MembershipType, JoinDate |
| Title | TitleID | ISBN, TitleName, PublicationYear, Edition |
| Copy | CopyID | Barcode, Status, ShelfLocation, DateAcquired |
| Author | AuthorID | FirstName, LastName |
| Publisher | PublisherID | PublisherName, City |
| Category | CategoryID | CategoryName |
| Loan | LoanID | LoanDate, DueDate, ReturnDate |
| Reservation | ReservationID | ReservationDate, Status |
| Fine | FineID | Amount, IssuedDate, PaidDate, Status |
| Staff | StaffID | FirstName, LastName, Role |
The relationships between entities, and the cardinality of each — how many occurrences of one may be associated with another — encode the business rules of the library. They are listed below.
| Relationship | Cardinality | Business Rule |
|---|---|---|
| Publisher – Title | 1 : M | A publisher issues many titles; each title has one publisher. |
| Category – Title | 1 : M | A category classifies many titles; each title has one category. |
| Title – Author | M : N | A title may have several authors and an author may write several titles. |
| Title – Copy | 1 : M | A title has one or more physical copies; each copy is of one title. |
| Copy – Loan | 1 : M | A copy is loaned many times over its life; each loan is of one copy. |
| Member – Loan | 1 : M | A member holds many loans; each loan belongs to one member. |
| Member – Reservation | 1 : M | A member places many reservations; each reservation is by one member. |
| Title – Reservation | 1 : M | A title may be reserved many times; each reservation is for one title. |
| Loan – Fine | 1 : 0..1 | A loan may incur at most one fine; a fine relates to exactly one loan. |
| Staff – Loan | 1 : M | A staff member processes many loans; each loan is processed by one staff member. |
Two features deserve emphasis. First, the many-to-many relationship between Title and Author cannot be represented directly in the relational model and must be resolved into a linking (associative) entity, here called TitleAuthor, during logical design. Second, the Copy entity sits between Title and Loan because a loan is of a physical copy, not of an abstract title: without it, the database could not record which of several identical copies was on loan, nor distinguish an available copy from one already out.
Logical design maps the conceptual model onto relations: each strong entity becomes a relation, the many-to-many relationship becomes the TitleAuthor relation, and other relationships are implemented by placing a foreign key on the “many” side. The relations are then normalised — a formal process that decomposes relations to remove redundancy and the insertion, update and deletion anomalies it causes (Codd, 1970; Date, 2004). The process is illustrated below using the loan data.
Consider an unnormalised record that attempts to hold a member’s loans in a single row, with a repeating group of borrowed items:
LoanSheet (MemberID, MemberName, MemberEmail, {CopyID, TitleName, AuthorName, LoanDate, DueDate})
The braces denote a repeating group: several borrowed items packed into one member row. A relation is in first normal form (1NF) when every attribute holds a single, atomic value and there are no repeating groups. Removing the group gives a relation in which each borrowed item occupies its own row, with a composite key of MemberID, CopyID and LoanDate:
Loan1NF (MemberID, CopyID, LoanDate, MemberName, MemberEmail, TitleName, AuthorName, DueDate)
A relation is in second normal form (2NF) when it is in 1NF and every non-key attribute is functionally dependent on the whole of the primary key, not just part of it. In Loan1NF, MemberName and MemberEmail depend only on MemberID, while TitleName and AuthorName depend only on CopyID. These are partial dependencies and must be removed by decomposition:
A relation is in third normal form (3NF) when it is in 2NF and no non-key attribute depends on another non-key attribute — that is, there are no transitive dependencies. In CopyInfo, a copy belongs to one title, and the title determines the author and other bibliographic data, so TitleName and AuthorName are transitively dependent on the copy through the title. Separating the title from the copy removes this dependency and surfaces the many-to-many relationship that the TitleAuthor relation resolves. The result is a set of relations each describing a single subject.
Applying the same reasoning across the whole model produces the following relations, with primary keys underlined and foreign keys marked in italics. This schema is in third normal form: every non-key attribute depends on the key, the whole key and nothing but the key.
The composite primary key of TitleAuthor, made up of two foreign keys, is the standard way of resolving a many-to-many relationship. The LoanID in Fine is a foreign key that is also unique, enforcing the one-to-at-most-one rule between a loan and its fine.
Physical design translates each relation into a table in a specific database management system; the examples below use standard SQL (ISO/IEC, 2016). Each attribute is given an appropriate data type: integers for surrogate keys, character strings for names, the DATE type for calendar dates and DECIMAL for monetary amounts, which avoids the rounding errors of floating-point types. Integrity is enforced declaratively through constraints: PRIMARY KEY for entity integrity, FOREIGN KEY for referential integrity, NOT NULL for mandatory attributes, UNIQUE for alternate keys such as Email and Barcode, and CHECK for domain rules. The DDL for two representative tables is shown below.
CREATE TABLE Member (
MemberID INTEGER PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(120) NOT NULL UNIQUE,
Phone VARCHAR(20),
MembershipType VARCHAR(20) NOT NULL,
JoinDate DATE NOT NULL
);
CREATE TABLE Loan (
LoanID INTEGER PRIMARY KEY,
LoanDate DATE NOT NULL,
DueDate DATE NOT NULL,
ReturnDate DATE,
CopyID INTEGER NOT NULL,
MemberID INTEGER NOT NULL,
StaffID INTEGER NOT NULL,
CONSTRAINT fk_loan_copy FOREIGN KEY (CopyID) REFERENCES Copy(CopyID),
CONSTRAINT fk_loan_member FOREIGN KEY (MemberID) REFERENCES Member(MemberID),
CONSTRAINT fk_loan_staff FOREIGN KEY (StaffID) REFERENCES Staff(StaffID),
CONSTRAINT chk_dates CHECK (DueDate >= LoanDate)
);The foreign-key constraints guarantee that no loan can reference a member, copy or staff member that does not exist, satisfying the integrity requirement. The CHECK constraint is a simple example of a business rule enforced by the database itself rather than by application code.
Primary keys are indexed automatically, but frequent queries benefit from additional indexes; finding a member’s active loans filters the Loan table on MemberID, so an index on that column supports the access path. The query below returns all overdue loans — those not yet returned whose due date has passed — and illustrates how the normalised schema is recombined through joins to answer a realistic question:
SELECT m.LastName, t.TitleName, l.DueDate
FROM Loan l
JOIN Member m ON l.MemberID = m.MemberID
JOIN Copy c ON l.CopyID = c.CopyID
JOIN Title t ON c.TitleID = t.TitleID
WHERE l.ReturnDate IS NULL
AND l.DueDate < CURRENT_DATE
ORDER BY l.DueDate;Because each fact is stored once, the join reconstructs the full picture without risk of contradiction: the member’s name comes only from Member, the title only from Title. The cost is the need to join tables at query time, a trade-off discussed below.
The design was evaluated by tracing each functional requirement to the part of the schema that satisfies it. Recording members, cataloguing titles, distinguishing copies, issuing and returning loans, placing reservations and recording fines each map cleanly onto one or more relations, and the overdue-loan query shows that the analytical requirements can be met. The non-functional requirements are addressed structurally: referential integrity is enforced by foreign keys, consistency follows from storing each fact once, and indexing supports the common access paths. Because titles, copies, members and loans are separate relations, the collection and membership can grow without structural change, meeting the scalability requirement.
The principal trade-off is between redundancy and join cost. Normalisation to 3NF eliminates the anomalies of the original spreadsheet, but the overdue-loan query now requires joining four tables. For a library of this size that is well within the capability of any modern database management system, and the reliability of the data far outweighs the modest overhead (Hoffer, Ramesh and Topi, 2019). In a very high-volume setting, selective denormalisation or materialised views might be considered, but they would be premature here and would reintroduce the risk of inconsistency.
Several limitations should be acknowledged. The model assumes a single library branch; supporting multiple branches would require a Branch entity related to Copy. The fine is stored as data rather than expressed as a rule, so the daily rate and any caps would live in application logic. Reservations are modelled against titles, but the design does not itself manage the reservation queue or convert a reservation into a loan when a copy is returned; that behaviour would sit in the application layer or in triggers. None of these limitations undermines the core design; each is a well-defined extension point.
On the basis of this design, the following recommendations are made for implementation and future work:
This report has documented the design of a relational database for a medium-sized academic library, moving from requirements through conceptual, logical and physical design. The entity-relationship model captured the distinction between a title and its physical copies and resolved the many-to-many relationship between titles and authors through a linking relation. Normalisation to third normal form removed the redundancy and anomalies of the original spreadsheet approach, and the schema was implemented in SQL with declarative constraints that enforce the library’s business rules. Evaluation confirmed that the design meets its functional and non-functional objectives, with the trade-offs and limitations identified alongside clear routes to extension. As a model example, it illustrates how the standard three-level methodology produces a database that stores each fact once, protects the integrity of the data and remains straightforward to maintain.
You May Also Like
All work is written by human writers. 100% AI free, guaranteed.
100% money back guarantee if you find plagiarism in our work.
COMPANY DETAILS
