Assignment – Relational Database Design for a University Library System

Introduction

Relational database technology remains the dominant approach to managing structured operational data more than five decades after the relational model was first formalised (Codd, 1970). This assignment presents the design of a relational database for the library of a mid-sized Australian university that serves approximately 38,000 enrolled students and holds a physical collection of about 600,000 catalogued items across 410,000 titles. The library lends items to students and staff, manages reservations for titles in heavy demand around semester peaks, raises charges for overdue and lost items, and contributes annual statistical returns to the national sector body for university libraries. Because borrower records constitute personal information under the Privacy Act 1988 (Cth), the design must also satisfy the security and retention expectations of the Australian Privacy Principles (Office of the Australian Information Commissioner [OAIC], 2023).

The report follows the conventional three-phase methodology of conceptual, logical, and physical design (Connolly & Begg, 2015). It states the system requirements, identifies the entities and their relationships, presents the entity-relationship (ER) model, demonstrates normalisation to third normal form (3NF) through a worked example, and concludes with sample data definition language (DDL), integrity constraints, and an indexing strategy matched to the expected workload.

System Requirements

Functional Requirements

Requirements were derived from the assignment case description and from standard circulation practice in Australian academic libraries. The database must support the following core functions:

1. maintain bibliographic records for each title and item-level records for each physical copy, including barcode and shelf location;
2. register members in three categories (student, staff, and community borrower), with the category determining loan entitlements;
3. record loans and returns, applying a 28-day loan period for students and a 42-day period for staff, renewable unless a reservation is pending on the title;
4. manage title-level reservation queues ordered by the time each reservation was placed;
5. raise a charge against a loan that is overdue by more than seven days, at A$0.50 per day capped at A$25.00, or at replacement cost where the item is declared lost; and
6. produce operational reports, including loans by faculty, overdue listings, and collection turnover figures for annual statistical returns (Council of Australian University Librarians [CAUL], 2023).

Data Volumes and Non-Functional Requirements

Sizing assumptions are consistent with sector figures reported for Australian and New Zealand university libraries (CAUL, 2023). The system must hold 38,000 member records and 600,000 copy records, and absorb approximately 380,000 loan transactions per year. Load is strongly seasonal: the first three teaching weeks of each semester generate up to 4,000 loans per day, roughly four times the off-peak rate, so read performance at the circulation desk is the critical workload. Non-functional requirements include 99.5 per cent availability during teaching weeks, nightly backup with a recovery point objective of 24 hours, and role-based access control aligned with the least-privilege and event-logging expectations of the Information Security Manual (Australian Cyber Security Centre [ACSC], 2024). Under Australian Privacy Principle 11, borrowing histories must be de-identified once they are no longer required for lending or statistical purposes; the design therefore assumes de-identification of loan records seven years after a loan is closed (OAIC, 2023).

Conceptual Design

Entity Identification

Entity identification distinguishes the bibliographic title (BOOK) from the physical item (BOOK_COPY), a separation that is standard in library systems because one title commonly has many copies, each with its own barcode, shelf location, and status. Surrogate primary keys are used throughout because they are compact, stable, and immune to changes in real-world identifiers such as ISBN formats (Kroenke et al., 2018); natural identifiers are retained as unique alternate keys. Table 1 lists the six core entities, their representative attributes, and their keys.

Table 1: Core entities of the library database, with representative attributes and keys

Author details are deliberately excluded from the core scope. Author is a multi-valued fact about a title, and a complete implementation would add an AUTHOR entity and a BOOK_AUTHOR associative entity to resolve the many-to-many relationship. The exclusion is a bounded scope decision rather than a modelling oversight, and the normalisation logic that justifies the split is demonstrated in the worked example below.

Entity-Relationship Model

Figure 1 illustrates the ER model, applying the entity-relationship approach introduced by Chen (1976) with cardinalities labelled 1, M, and 0..1. A member places many loans over time, and each loan concerns exactly one copy, so a borrowing session of three items is recorded as three loan rows. A book has many copies, while reservations are held at title level because any available copy satisfies the reservation. A loan may incur at most one fine, which preserves a direct audit trail from every charge back to the causal loan.

Logical Design and Normalisation

Normalisation decomposes relations to eliminate redundancy and the insertion, update, and deletion anomalies that redundancy causes, with 3NF the accepted benchmark for operational schemas (Coronel & Morris, 2019). A relation is in first normal form (1NF) when every attribute value is atomic; in second normal form (2NF) when, additionally, no non-key attribute depends on only part of a composite key; and in 3NF when no non-key attribute depends transitively on the key through another non-key attribute (Elmasri & Navathe, 2017).

Worked Example: From the Paper Loan Slip to 3NF

The library’s legacy paper loan slip is a convenient unnormalised source document. One slip records a member borrowing several items in a single visit: the slip number, loan and due dates, the member’s details, and a repeating group of item details. Table 2 traces the decomposition of this document through each normal form, with primary key attributes shown in bold.

Table 2: Normalisation of the loan slip from unnormalised form to third normal form (primary keys in bold)

At 1NF, the member’s name is repeated on every row of every slip, so a change of email address would need to be applied to thousands of rows, and a member who has never borrowed cannot be recorded at all. The 2NF step removes the dependencies on part of the composite key, and the 3NF step removes the dependencies that pass through member_id and isbn. In the implemented schema, the loan_item bridging relation is absorbed by recording one loan row per item borrowed, and surrogate keys replace slip_no, barcode, and isbn as primary keys, with barcode and isbn retained as unique alternate keys. The result is exactly the set of relations shown in Figure 1. Each relation also satisfies Boyce-Codd normal form, because every determinant is a candidate key and no relation carries overlapping composite candidate keys (Date, 2019).

Physical Design

Sample Data Definition Language

The DDL below defines the member and loan tables in standard SQL; the remaining tables follow the same pattern.

CREATE TABLE member (
  member_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  member_name VARCHAR(80) NOT NULL,
  email VARCHAR(120) NOT NULL UNIQUE,
  member_type VARCHAR(12) NOT NULL CHECK (member_type IN (‘STUDENT’, ‘STAFF’, ‘COMMUNITY’)),
  faculty VARCHAR(60),
  date_joined DATE NOT NULL,
  member_status VARCHAR(10) DEFAULT ‘ACTIVE’ NOT NULL CHECK (member_status IN (‘ACTIVE’, ‘SUSPENDED’, ‘CLOSED’))
);

CREATE TABLE loan (
  loan_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  member_id INTEGER NOT NULL,
  copy_id INTEGER NOT NULL,
  loan_date DATE NOT NULL,
  due_date DATE NOT NULL,
  return_date DATE,
  CONSTRAINT fk_loan_member FOREIGN KEY (member_id) REFERENCES member (member_id) ON DELETE RESTRICT,
  CONSTRAINT fk_loan_copy FOREIGN KEY (copy_id) REFERENCES book_copy (copy_id) ON DELETE RESTRICT,
  CONSTRAINT chk_due_after_loan CHECK (due_date > loan_date),
  CONSTRAINT chk_return_valid CHECK (return_date IS NULL OR return_date >= loan_date)
);

Integrity Constraints

Integrity is enforced declaratively wherever possible, because rules held in the schema cannot be bypassed by application defects (Harrington, 2016). Entity integrity is guaranteed by the identity-generated primary keys. Referential integrity is enforced through foreign keys, and ON DELETE RESTRICT is deliberately chosen for both foreign keys on loan so that no member or copy involved in historical lending can ever be deleted; account closure is represented by member_status instead, which preserves the audit trail expected of a financial record that may carry fines. Domain integrity is enforced through NOT NULL, UNIQUE, and CHECK constraints, including the date-ordering rules shown in the DDL and a CHECK on fine.amount restricting values to the range 0 to 25 dollars for overdue charges. Business rules that span multiple rows, such as the 20-item concurrent loan cap for students, cannot be expressed as single-row CHECK constraints and are enforced in a trigger or in the application service layer.

Two constraint decisions respond directly to Australian regulatory context. First, a scheduled job repoints the member reference of loans closed more than seven years earlier to a single de-identified placeholder member, which removes the personal link while preserving referential integrity and the statistical value of the row (OAIC, 2023). Second, privileged database accounts are separated from application accounts and all privileged activity is logged, consistent with ACSC (2024) guidance on database hardening.

Storage and Growth Estimate

A loan row stores three 4-byte integer keys and three dates plus row overhead, an estimated 64 bytes on disk. Annual growth is therefore:

Annual loan storage = rows per year × bytes per row = 380,000 × 64 = 24,320,000 bytes, approximately 23.2 MB.

Across the seven-year retention window the loan table accumulates roughly 2.66 million rows and about 162 MB of base data, rising to approximately 227 MB once a 40 per cent allowance for indexes and page overhead is added (162 × 1.4). The whole database, dominated by the 600,000-row copy table and bibliographic text, remains well under 2 GB, so commodity hardware is sufficient and design effort is better directed at indexing than at partitioning.

Indexing Strategy

Secondary indexes are justified query by query, because every index accelerates reads while taxing each insert and update (Silberschatz et al., 2020). The busiest query, retrieving the current loans of the member standing at the circulation desk, is served by a composite B+ tree index on loan(member_id, return_date). Its usefulness can be quantified from the volume assumptions:

Average rows per member per year = total loans / distinct members = 380,000 / 38,000 = 10 rows, which is 10 / 380,000 = 0.003 per cent of the table.

Because the query touches far less than the 1-2 per cent of rows at which index access typically ceases to beat a sequential scan, the index reduces the lookup from hundreds of thousands of row reads to a handful (Silberschatz et al., 2020). Table 3 summarises the proposed secondary indexes and the workload each serves.

Table 3: Proposed secondary indexes and their rationale

No index is created on low-cardinality columns such as member_type, where poor selectivity would leave a sequential scan cheaper than index access, and the fine table is left unindexed beyond its keys because its query volume is trivial.

Conclusion

This assignment has designed a relational database for an Australian university library from requirements through to physical implementation. The six-entity model separates bibliographic titles from physical copies, records one loan row per item borrowed, holds reservations at title level, and ties every fine to its causal loan. The worked decomposition of the paper loan slip verified that the schema is in 3NF, and indeed Boyce-Codd normal form, so the design is free of the redundancy anomalies that afflict flat-file lending records. Physical design decisions, restrictive foreign keys, declarative CHECK constraints, five targeted B+ tree indexes, and a seven-year de-identification rule, align the system with both its seasonal workload and Australian obligations under the Privacy Act 1988 (Cth) and the Information Security Manual. The schema extends naturally to authors, serials, and interlibrary lending through the Libraries Australia resource-sharing service operated by the National Library of Australia, without disturbing the core structure presented here.

References

Australian Cyber Security Centre. (2024). Information security manual. Australian Signals Directorate.

Chen, P. P. (1976). The entity-relationship model: Toward a unified view of data. ACM Transactions on Database Systems, 1(1), 9-36.

Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377-387.

Connolly, T., & Begg, C. (2015). Database systems: A practical approach to design, implementation, and management (6th ed.). Pearson Education.

Coronel, C., & Morris, S. (2019). Database systems: Design, implementation, and management (13th ed.). Cengage Learning.

Council of Australian University Librarians. (2023). CAUL statistics 2022: Australian and New Zealand university libraries.

Date, C. J. (2019). Database design and relational theory: Normal forms and all that jazz (2nd ed.). Apress.

Elmasri, R., & Navathe, S. B. (2017). Fundamentals of database systems (7th ed.). Pearson.

Harrington, J. L. (2016). Relational database design and implementation (4th ed.). Morgan Kaufmann.

Kroenke, D. M., Auer, D. J., Vandenberg, S. L., & Yoder, R. C. (2018). Database processing: Fundamentals, design, and implementation (15th ed.). Pearson.

Office of the Australian Information Commissioner. (2023). Australian Privacy Principles guidelines.

Silberschatz, A., Korth, H. F., & Sudarshan, S. (2020). Database system concepts (7th ed.). McGraw-Hill Education.