Case Study Practical Exercise: Designing a School Library System | 案例分析实战演练：设计学校图书馆系统

📚 Case Study Practical Exercise: Designing a School Library System | 案例分析实战演练：设计学校图书馆系统

In this practical case study, we will walk through the process of designing and building a simple digital system for a school library. This exercise mirrors the type of project you might encounter in SQA Computing Science assessments, where you must analyse a real-world scenario, identify requirements, design solutions, implement core features, and evaluate the outcome. By the end, you will have a better understanding of the software development lifecycle and key computing concepts.

在这个实战案例研究中，我们将逐步完成一个学校图书馆简易数字系统的设计与构建过程。此练习模拟了你在 SQA 计算机科学评估中可能遇到的项目类型，你需要分析真实场景、确定需求、设计方案、实现核心功能并评估结果。通过本练习，你将更好地理解软件开发生命周期和关键的计算机概念。

1. Understanding the Problem and Stakeholders | 理解问题与利益相关者

The school librarian currently uses a paper-based method to track which student has borrowed which book. This leads to overdue fines being forgotten, books being lost, and long queues during break times. The goal is to create a digital library system that allows students to search for books, borrow and return items, and lets the librarian manage inventory and fines efficiently.

学校图书管理员目前使用纸质方式记录哪位学生借阅了哪本书。这导致逾期罚款被遗忘、图书丢失，以及课间排长队。目标是创建一个数字图书馆系统，让学生能够搜索图书、借阅和归还物品，并让管理员高效管理库存和罚款。

The key stakeholders are the librarian, who needs a reliable tool to manage loans; the students, who want a fast way to find and borrow books; the teachers, who may reserve class sets; and the school IT administrator, who must ensure the system integrates with existing school networks and follows security policies.

关键利益相关者包括图书管理员（需要可靠工具管理借阅）、学生（想快速找书和借书）、教师（可能预订班级用书）以及学校 IT 管理员（必须确保系统与现有学校网络集成并遵循安全策略）。

2. Functional and Non‑functional Requirements | 功能性需求与非功能性需求

Functional requirements describe what the system must do. For the library system, these include: a searchable catalogue by title, author or ISBN; a borrowing module that updates availability immediately; automatic calculation of due dates and fines; a return function that checks for damage; and a login system that differentiates between student and librarian accounts.

功能性需求描述了系统必须做什么。对于图书馆系统，这包括：可按书名、作者或 ISBN 搜索的目录；可立即更新可借状态的借阅模块；自动计算到期日和罚款；检查书籍是否损坏的归还功能；以及区分学生与管理员账户的登录系统。

Non‑functional requirements focus on quality attributes. The system must respond to a search within two seconds, be available during school hours (08:00–16:00), protect personal data with encrypted passwords, and have a simple interface usable by 11‑year‑old students without training.

非功能性需求关注质量属性。系统必须在两秒内响应搜索，在学校时间（08:00–16:00）可用，用加密密码保护个人数据，并拥有一个无需培训即可让 11 岁学生使用的简单界面。

3. Data Flow and System Context Diagram | 数据流与系统上下文图

To understand how information moves, we draw a context diagram showing the system as a single process. External entities are the Student, Librarian, and School Database. Data flows include: a student sends a ‘search query’ and receives ‘book availability’; the librarian sends ‘add book’ or ‘update fine’ commands and receives ‘inventory report’.

为了理解信息的流动，我们绘制了一个将系统视为单一进程的上下文图。外部实体包括学生、图书管理员和学校数据库。数据流包括：学生发送“搜索查询”并接收“图书可借状态”；管理员发送“添加图书”或“更新罚款”命令并接收“库存报告”。

A more detailed Level 0 DFD would show main processes such as ‘Manage Loans’, ‘Catalogue Management’, ‘User Authentication’ and ‘Fine Processing’. The data store ‘Books’ holds ISBNs, titles, authors and status; ‘Users’ stores credentials and contact details; ‘Loans’ links users to books with dates.

更详细的 0 级数据流图会展示主要进程，如“管理借阅”“目录管理”“用户认证”和“罚款处理”。数据存储“Books”保存 ISBN、书名、作者和状态；“Users”存储凭证与联系信息；“Loans”将用户与图书及日期关联起来。

4. Designing the User Interface | 设计用户界面

A wireframe for the student homepage includes a search bar at the top, a ‘My Loans’ button, and a ‘Browse by Category’ section. The search results page should display a table with columns: Cover image, Title, Author, Status (Available/On Loan), and a ‘Borrow’ button when available.

学生主页的线框包括顶部的搜索栏、一个“我的借阅”按钮和一个“按分类浏览”区域。搜索结果页应展示一个表格，列包括：封面图片、书名、作者、状态（可借/已借出），可借时显示“借阅”按钮。

For the librarian’s dashboard, the design prioritises efficiency. A side menu gives access to ‘Add new book’, ‘Scan barcode’, ‘Manage fines’, and ‘Generate report’. A central panel shows an alert list of overdue books with the student’s name and fine amount.

在图书管理员仪表盘中，设计优先考虑效率。侧边菜单可访问“添加新书”“扫描条码”“管理罚款”和“生成报告”。中央面板显示带有学生姓名和罚款金额的逾期图书提醒列表。

5. Database Design with Tables and Relationships | 数据库设计：表与关系

A relational database is suitable. We identify three main tables: Books (BookID PK, Title, Author, ISBN, Year, Copies, AvailableCopies), Users (UserID PK, Name, YearGroup, Email, PasswordHash, Role), and Loans (LoanID PK, UserID FK, BookID FK, DateOut, DueDate, DateReturned, FinePaid).

关系型数据库是合适的。我们确定了三个主表：Books（BookID 主键，书名，作者，ISBN，年份，馆藏数，可借数），Users（UserID 主键，姓名，年级，邮箱，密码哈希，角色），和 Loans（LoanID 主键，UserID 外键，BookID 外键，借出日期，到期日，归还日期，罚款已付）。

The relationship between Users and Loans is one‑to‑many; a user can have many loans over time. Books to Loans is also one‑to‑many, but an individual book copy is tracked through the AvailableCopies field rather than a separate inventory record, keeping the design simple for Year 9 level.

Users 与 Loans 之间是一对多的关系；一个用户可以在不同时间有多条借阅记录。Books 与 Loans 也是一对多，但通过 AvailableCopies 字段追踪单册图书，而不是单独库存记录，这使得设计在 Year 9 水平上保持简单。

6. Implementing the System with HTML and SQL | 使用 HTML 和 SQL 实现系统

The front end can be built with HTML and CSS. A simple online library interface might use an HTML form for searching books:

前端可以用 HTML 和 CSS 构建。一个简单的在线图书馆界面可以用 HTML 表单搜索图书：

When implementing the borrowing feature, a server-side script (e.g. PHP or Python Flask) connects to the database. The SQL command to record a new loan, assuming the user with ID 105 borrows book 42 with a 14‑day loan period, would be:

在实现借阅功能时，服务器端脚本（如 PHP 或 Python Flask）连接数据库。记录新借阅的 SQL 命令，假设用户 ID 105 借阅图书 42，借期 14 天，如下：

INSERT INTO Loans (UserID, BookID, DateOut, DueDate, DateReturned, FinePaid)
VALUES (105, 42, ‘2025-04-01’, ‘2025-04-15’, NULL, FALSE);
UPDATE Books SET AvailableCopies = AvailableCopies – 1 WHERE BookID = 42;

These two operations should be wrapped in a transaction so that the book count is not incorrectly reduced if the INSERT fails. In a Python example we might use import sqlite3 and connection.execute().

这两步操作应该包装在事务中，以防 INSERT 失败时图书计数被错误地减少。在 Python 示例中我们可能使用 import sqlite3 和 connection.execute()。

7. Testing Strategies and Test Cases | 测试策略与测试用例

Testing is essential to ensure the system meets its requirements. We will use both black‑box and white‑box techniques. Black‑box testing treats the system as a ‘black box’, checking outputs for given inputs without knowing internal code. White‑box testing examines the logic of code, for example testing both branches of an if‑statement that checks if a book is available.

测试对于确保系统满足需求至关重要。我们将使用黑盒和白盒两种技术。黑盒测试把系统当作“黑盒”，检查给定输入的输出而不了解内部代码。白盒测试检查代码逻辑，例如，测试检查图书是否可借的 if 语句的两个分支。

Below is a test table for the borrowing function:

下面是借阅功能的测试表：

Test ID	Description	Input	Expected Outcome	Actual
T01	Borrow available book	User 105, Book 42 (copies>0)	Loan created, copies reduced	Pass/Fail
T02	Borrow unavailable book	User 105, Book 42 (copies=0)	Error message ‘Book not available’	Pass/Fail
T03	User exceeds loan limit	User with 3 active loans tries to borrow	Error ‘Loan limit reached’	Pass/Fail

T01 checks a normal scenario; T02 and T03 are boundary and exceptional tests. These test cases would be executed and the Actual column filled during testing. Regression testing ensures that new changes do not break previous functionality.

T01 检查正常场景；T02 和 T03 是边界测试和异常测试。这些测试用例将被执行，实际结果列在测试中填写。回归测试确保新更改不会破坏之前的功能。

8. Security, Privacy and Legal Considerations | 安全、隐私与法律考量

Any system handling personal data must comply with data protection laws such as the UK GDPR. For our school library, we collect names, email addresses, and year groups of students. This data is personal and must be stored securely, used only for library operations, and never shared without consent.

任何处理个人数据的系统都必须遵守数据保护法，如英国通用数据保护条例（GDPR）。对于我们的学校图书馆，我们收集学生的姓名、电子邮件地址和年级。这些数据属于个人信息，必须安全存储，仅用于图书馆业务，未经同意不得分享。

Passwords must be hashed (using algorithms like bcrypt) before storage. The login form should use HTTPS to encrypt data in transit. Access control ensures a student cannot view the librarian’s dashboard or alter fines. SQL injection attacks can be prevented by using parameterised queries instead of concatenating user input directly into SQL statements.

密码必须在存储前进行哈希处理（使用 bcrypt 等算法）。登录表单应使用 HTTPS 加密传输数据。访问控制确保学生无法查看管理员仪表盘或修改罚款。通过使用参数化查询而非将用户输入直接拼接到 SQL 语句中，可以防止 SQL 注入攻击。

The school must have a clear privacy notice explaining what data is collected and why. Regular backups of the database protect against accidental loss, and a disaster recovery plan should be in place.

学校必须有清晰的隐私声明，解释收集哪些数据及原因。定期备份数据库可防止意外丢失，并且应制定灾难恢复计划。

9. Evaluation and Future Improvements | 评估与未来改进

After implementation, we evaluate the system against its original requirements. Does it reduce the time taken to check out a book? The librarian can now scan a barcode and the loan is recorded in under 10 seconds, compared to the previous 2‑minute manual entry. Students can check availability from home, reducing unnecessary trips to the library.

实施后，我们对照原始需求评估系统。它是否缩短了借书所需的时间？图书管理员现在可以扫描条码，借阅记录在 10 秒内完成，而之前手工录入需要 2 分钟。学生可以在家查看可借状态，减少了无谓的图书馆之行。

However, several improvements could be made in a future version. A barcode scanner integration would speed up data entry. A reservation system could allow students to join a queue for popular books. An automated email reminder for overdue books would help reduce fines. Additionally, a simple mobile‑friendly interface would make the system accessible from any device.

但是，未来版本可以做一些改进。集成条码扫描器能加快数据录入。预约系统可让学生排队预约热门图书。逾期图书自动邮件提醒有助于减少罚款。此外，简洁的移动端友好界面将使系统在任何设备上都可访问。

From a development perspective, using an agile methodology with frequent user feedback would have helped catch usability issues earlier. Testing with real students during prototype stage could have refined the search interface.

从开发角度看，采用敏捷方法并频繁获取用户反馈，可以更早地发现可用性问题。在原型阶段与真实学生一起测试，原本可以优化搜索界面。

10. Conclusion and Key Takeaways | 总结与关键要点

This case study has walked you through the entire lifecycle of a computing project, from identifying the problem to evaluating the solution. You have seen how to gather requirements, design a database, build a simple interface with HTML, write SQL queries, and plan testing with a test table. The project also highlighted the importance of security, legal compliance, and iterative evaluation.

本案例研究带你走过了计算项目的整个生命周期，从问题识别到方案评估。你了解了如何收集需求、设计数据库、用 HTML 构建简单界面、编写 SQL 查询以及用测试表规划测试。项目还强调了安全性、法律合规和迭代评估的重要性。

When tackling your own SQA practical assignments, remember to clearly define stakeholders, draft visual designs such as wireframes, and always test for both normal uses and edge cases. The skills developed here—analysis, design, implementation, testing, and evaluation—are the foundation of successful software development and are assessed regularly in SQA examinations.

当你处理自己的 SQA 实践作业时，记住要清晰定义利益相关者，绘制线框图等可视化设计，并始终测试正常使用和极端情况。这里培养的分析、设计、实施、测试和评估技能，是成功软件开发的基础，也是 SQA 考试中经常评估的内容。

Published by TutorHao | Computing Revision Series | aleveler.com

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

Case Study Practical Exercise: Designing a School Library System | 案例分析实战演练：设计学校图书馆系统