Case Study Practical Drill: Library Management System | 案例分析实战演练：图书馆管理系统

📚 Case Study Practical Drill: Library Management System | 案例分析实战演练：图书馆管理系统

The Cambridge AS Computer Science syllabus expects learners to apply theoretical knowledge to realistic scenarios. A library management system is a classic case study that integrates data modelling, SQL, algorithm design, validation and testing. This article walks you through a complete practical drill, from requirements analysis to evaluation, modelling the kind of approach you need in examinations and coursework.

剑桥 AS 计算机科学大纲要求学生将理论知识应用于实际场景。图书馆管理系统是一个综合了数据建模、SQL、算法设计、验证和测试的经典案例。本文带你完成一次完整的实战演练，从需求分析到评估，模拟你在考试和课程项目中需要的思维方式。

---

1. Case Study Overview | 案例概览

We are asked to develop a system for a small school library. The library holds around 2000 books. Students and staff are members. The system must allow members to borrow up to three books at a time, return books, reserve currently loaned titles, and search the catalogue. Staff need reports on overdue items.

我们被要求为一所小型学校图书馆开发系统。图书馆藏书约 2000 册，学生和教职工都是会员。系统必须允许会员一次最多借阅三本书、归还图书、预约已借出的图书以及检索目录。工作人员需要逾期物品的报告。

The system will be web-based, but our analysis focuses on the logical layer: database design, queries, processing algorithms, and interface considerations. We will follow the software development life cycle (SDLC) stages appropriate for AS level.

系统将基于网页，但我们的分析聚焦于逻辑层：数据库设计、查询、处理算法和界面考虑。我们将遵循适用于 AS 阶段的软件开发生命周期阶段。

---

2. Requirements Analysis | 需求分析

Functional requirements: add new book, register member, borrow a book (check membership status and loan limit), return a book, reserve a book, search by title, author or ISBN, and generate an overdue report. Non-functional requirements: the system must respond to searches within 3 seconds, support concurrent access by 10 users, and enforce password-based authentication.

功能性需求：添加新书、注册会员、借书（检查会员状态和借阅限额）、还书、预约图书、按书名、作者或 ISBN 搜索、生成逾期报告。非功能性需求：系统搜索响应时间须在 3 秒以内，支持 10 个用户并发访问，并强制实施基于密码的认证。

We document these requirements in a table to identify key data entities and operations.

我们以表格形式记录这些需求，以便识别关键数据实体和操作。

Requirement ID	Description	Data Involved
FR1	Add new book	Book
FR2	Register member	Member
FR3	Borrow book	Book, Member, Loan
FR4	Return book	Loan
FR5	Reserve book	Reservation, Book
FR6	Search catalogue	Book
FR7	Overdue report	Loan, Member

This breakdown helps us move to logical design.

这种分解有助于我们进入逻辑设计。

---

3. Data Modelling: Entities and Relationships | 数据建模：实体与关系

From the requirements we identify four entities: BOOK, MEMBER, LOAN, RESERVATION. A MEMBER can have many LOANs (one-to-many). A BOOK can be associated with many LOANs over time, but at any moment it can be in only zero or one active LOAN. A MEMBER can place many RESERVATIONs; each RESERVATION references one BOOK.

根据需求我们识别出四个实体：图书、会员、借阅、预约。会员可以有多次借阅（一对多）。一本书可以随时间关联多次借阅，但任一时刻只能有零个或一个活跃借阅。会员可以创建多个预约；每个预约对应一本书。

We also note that a LOAN is a transaction entity linking MEMBER and BOOK with attributes due date and return date. The ER diagram would show M:1 from LOAN to MEMBER, and M:1 from LOAN to BOOK, plus M:1 from RESERVATION to MEMBER and to BOOK.

我们还注意到借阅是连接会员和图书的事务实体，属性包括应还日期和归还日期。ER 图会显示借阅与会员为多对一，借阅与图书为多对一，以及预约与会员和图书的多对一关系。

---

4. Database Design: Logical Schema | 数据库设计：逻辑模式

We translate the data model into a relational schema. Four tables are defined with primary keys underlined and foreign keys listed. We ensure that data types match the domain values. The schema is normalised to third normal form (3NF).

我们将数据模型转化为关系模式。定义了四张表，主键用下划线标出，外键单独列出。我们确保数据类型匹配域值，模式规范化至第三范式。

Table	Attributes	PK / FK
BOOK	BookID (integer), Title (string), Author (string), ISBN (string), PublicationYear (integer), Available (boolean)	PK: BookID
MEMBER	MemberID (integer), FirstName (string), LastName (string), MembershipType (string), JoinDate (date), PasswordHash (string)	PK: MemberID
LOAN	LoanID (integer), BookID (integer), MemberID (integer), LoanDate (date), DueDate (date), ReturnedDate (date)	PK: LoanID, FK: BookID -> BOOK, MemberID -> MEMBER
RESERVATION	ReservationID (integer), BookID (integer), MemberID (integer), ReservationDate (date), Status (string)	PK: ReservationID, FK: BookID -> BOOK, MemberID -> MEMBER

The Available flag in BOOK is a derived field used for quick checks. It is updated by the procedure when a loan or return occurs.

图书表的 Available 标志是用于快速检查的派生字段，在借阅或归还发生时由过程更新。

---

5. Query Design: SQL Examples | 查询设计：SQL 示例

We now write several SQL queries that the system will need. These use standard DML statements. SQL keywords are capitalised for clarity. All queries are tested against the schema above.

我们现在编写系统需要的几条 SQL 查询，使用标准 DML 语句。SQL 关键字大写以示清晰，所有查询均基于上述模式测试。

Search books by title keyword:

按书名关键词搜索：

SELECT BookID, Title, Author, ISBN, Available FROM BOOK WHERE Title LIKE ‘%science%’ ORDER BY Title;

This returns all books containing ‘science’. The % wildcard allows partial matching.

这将返回所有包含 ‘science’ 的图书。% 通配符允许部分匹配。

Check active loan count for a member before borrowing:

借书前检查会员当前借阅数量：

SELECT COUNT(*) FROM LOAN WHERE MemberID = 101 AND ReturnedDate IS NULL;

If the count is 3, the system rejects the new loan. This enforces the maximum three-book rule.

如果计数为 3，系统拒绝新的借阅。这执行了最多三本的规定。

Generate overdue report:

生成逾期报告：

SELECT M.LastName, M.FirstName, B.Title, L.DueDate FROM LOAN L
JOIN MEMBER M ON L.MemberID = M.MemberID
JOIN BOOK B ON L.BookID = B.BookID
WHERE L.ReturnedDate IS NULL AND L.DueDate < CURRENT_DATE;

This joins three tables to list overdue books with member details. Current date function varies by DBMS; here we use standard SQL.

该查询联结三个表，列出逾期图书及会员信息。当前日期函数因数据库而异，此处使用标准 SQL。

---

6. Pseudocode Algorithms: Core Processes | 伪代码算法：核心过程

The borrow book process requires multiple checks. We design a pseudocode procedure that uses the queries above. This algorithm can be implemented in any procedural language.

借书过程需要多重检查。我们设计了一个使用上述查询的伪代码过程，该算法可用任何过程化语言实现。

PROCEDURE BorrowBook (IN p_MemberID, IN p_BookID)
DECLARE v_ActiveLoans INTEGER;
DECLARE v_BookAvailable BOOLEAN;
SELECT COUNT(*) INTO v_ActiveLoans FROM LOAN
WHERE MemberID = p_MemberID AND ReturnedDate IS NULL;
IF v_ActiveLoans >= 3 THEN
RAISE ERROR ‘Loan limit exceeded’;
RETURN;
END IF;
SELECT Available INTO v_BookAvailable FROM BOOK
WHERE BookID = p_BookID;
IF v_BookAvailable = FALSE THEN
RAISE ERROR ‘Book not available’;
RETURN;
END IF;
INSERT INTO LOAN (BookID, MemberID, LoanDate, DueDate)
VALUES (p_BookID, p_MemberID, CURRENT_DATE, CURRENT_DATE + 14);
UPDATE BOOK SET Available = FALSE WHERE BookID = p_BookID;
END PROCEDURE;

The procedure first checks the number of active loans. If less than three, it checks book availability. If both pass, it creates a LOAN record with a 14-day due date and marks the book unavailable.

过程首先检查活跃借阅数量。如果少于三本，则检查图书可用性。如果两关都通过，则创建一条借阅记录，设定 14 天借期，并将图书标记为不可用。

Return book procedure is simpler:

还书过程则简单一些：

PROCEDURE ReturnBook (IN p_LoanID)
DECLARE v_BookID INTEGER;
SELECT BookID INTO v_BookID FROM LOAN WHERE LoanID = p_LoanID;
UPDATE LOAN SET ReturnedDate = CURRENT_DATE WHERE LoanID = p_LoanID;
UPDATE BOOK SET Available = TRUE WHERE BookID = v_BookID;
— Check if there is a pending reservation
IF EXISTS (SELECT 1 FROM RESERVATION WHERE BookID = v_BookID AND Status = ‘Pending’)
THEN
— Logic to notify the first reserving member
END IF;
END PROCEDURE;

This updates the return date, makes the book available, and optionally inspects reservations. Notification logic is left as a placeholder.

此过程更新归还日期，使图书恢复可用，并可检查预约记录。通知逻辑留作占位符。

---

7. User Interface Design: Key Screens | 用户界面设计：关键屏幕

We sketch a simple form-based interface. The main menu offers: Search Catalogue, My Loans, Reserve a Book, Staff Reports. We apply input validation on each form to reduce errors.

我们勾勒一个简单的表单式界面。主菜单提供：搜索目录、我的借阅、预约图书、员工报告。我们在每个表单上应用输入验证以减少错误。

For the Search screen, a text box accepts a search term with a drop-down for filter type (title/author/ISBN). A results table displays BookID, Title, Author, Status (Available/Loaned). A ‘Reserve’ button appears next to loaned books.

搜索屏幕有一个文本框接受搜索词，并有一个过滤类型下拉菜单（书名/作者/ISBN）。结果表显示图书 ID、书名、作者、状态（可用/已借出）。已借出的图书旁边有一个“预约”按钮。

In the My Loans screen, the member sees all current loans with due dates highlighted in red if overdue. A ‘Return’ button triggers the return procedure.

在我的借阅屏幕中，会员看到所有当前借阅记录，逾期项以红色突出显示。一个“归还”按钮触发还书过程。

The Staff Reports screen provides a date range picker and generates overdue lists or loan history. Design consistency is maintained through a template with navigation bar.

员工报告屏幕提供日期范围选择器，并生成逾期列表或借阅历史。通过带导航栏的模板保持设计一致性。

---

8. Validation and Verification | 验证与校验

Validation ensures data entered is reasonable before processing. Verification checks that data matches its source (e.g. double entry of ISBN). We apply several validation rules on input fields.

验证确保在数据处理前输入的数据是合理的。校验则检查数据是否与原始来源一致（如 ISBN 的重复录入）。我们对输入字段施加了多个验证规则。

Field	Validation Rule	Type
ISBN	Must be 13 digits; check digit calculation	Format + check digit
PublicationYear	Range 1000..CURRENT_YEAR	Range check
MembershipType	Must be one of ‘Student’, ‘Staff’	List check
LoanDate	Cannot be in the future	Presence + reasonableness

These rules are implemented client-side (JavaScript) and server-side (SQL constraints) to ensure double-layer protection.

这些规则在客户端（JavaScript）和服务器端（SQL 约束）实现，以提供双层保护。

---

9. Normalization Check | 规范化检查

Our schema is already in 3NF. Each table has a single primary key; all non-key attributes depend entirely on the key. There are no repeating groups (1NF), no partial dependencies (2NF), and no transitive dependencies (3NF).

我们的模式已经处于第三范式。每张表有一个主键；所有非键属性完全依赖于主键。不存在重复组（1NF），没有部分依赖（2NF），也没有传递依赖（3NF）。

For example, in LOAN, DueDate and LoanDate depend on the full key (LoanID), not just part of it. Member details are stored separately in MEMBER, avoiding duplication. This design prevents update anomalies.

例如在借阅表中，应还日期和借阅日期依赖于完整键（LoanID），而不是部分。会员详细信息单独存储在会员表中，避免重复。此设计可防止更新异常。

---

10. Security and Ethical Considerations | 安全与道德考量

Since member data and loan records are sensitive, we hash passwords using SHA-256 and store only hashes. Access control divides users into Members and Librarians; librarians can run reports, members can only view personal data. SQL queries use parameterised statements to prevent injection.

由于会员数据和借阅记录是敏感的，我们使用 SHA-256 对密码进行哈希处理，只存储哈希值。访问控制将用户分为会员和图书馆员；图书馆员可以运行报告，会员只能查看个人数据。SQL 查询使用参数化语句以防止注入攻击。

Ethically, the system must not share member reading history with third parties. The overdue report should avoid public shaming; notices are emailed privately. Data retention complies with school policy.

从道德上讲，系统不得将会员阅读记录分享给第三方。逾期报告应避免公开羞辱；通知通过电子邮件私下发送。数据保留遵循学校政策。

---

11. Testing Strategies | 测试策略

We adopt a V-model testing approach. Unit tests cover each procedure (BorrowBook, ReturnBook) with test data. Integration tests check the flow from search to reservation. System tests validate the whole application against requirements.

我们采用 V 模型测试方法。单元测试用测试数据覆盖每个过程（借书、还书）。集成测试检查从搜索到预约的流程。系统测试对照需求验证整个应用程序。

Sample test case for borrow book:

借书的示例测试用例：

Test ID	Input	Expected Outcome
TC01	Member with 2 active loans, available book	New loan created
TC02	Member with 3 active loans	Error message ‘limit exceeded’
TC03	Book already loaned out	Error message ‘not available’

Boundary value testing on loan limit (2,3,4) and overdue date calculation is also planned. A final user acceptance test with staff members confirms usability.

还计划对借阅限额（2,3,4）和逾期日期计算进行边界值测试。最终的员工用户验收测试确认可用性。

---

12. Evaluation and Refinement | 评估与改进

The designed system meets all specified requirements. The relational schema is efficient, normalised, and extensible – for example, adding a FINE table for late returns. Performance of search queries can be improved by creating an index on Title and Author columns.

所设计的系统满足所有指定需求。关系模式高效、规范化且可扩展——例如，可以为逾期归还添加罚款表。通过在书名和作者列上创建索引，可以改进搜索查询的性能。

Limitations include the simplistic reservation mechanism (no priority queue for multiple reservations). Future enhancements could include barcode scanning integration and an online public access catalogue (OPAC).

局限性包括预约机制过于简单（未为多个预约实现优先队列）。未来增强功能可以包括条形码扫描集成和在线公共访问目录（OPAC）。

This case study drill has demonstrated how Cambridge topics – data modelling, SQL, pseudocode, validation, testing – coalesce into a coherent project. Practice applying the same analytical pattern to other scenarios like hospital appointments or inventory control.

这次案例演练展示了剑桥课程中的主题——数据建模、SQL、伪代码、验证、测试——如何整合成一个连贯的项目。请练习将同样的分析模式应用于其他场景，如医院预约或库存控制。

Published by TutorHao | Computer Science Revision Series | aleveler.com

更多咨询请联系16621398022（同微信）