📚 Case Study Workout: SQA Computing Science | 案例分析实战演练
Case studies are a key part of the SQA Computing Science course. They test your ability to analyse a real-world problem, design a solution, and justify your decisions. In this article, we will walk through a complete case study for GreenLeaf Organics, an organic food supplier needing an inventory system.
案例研究是SQA计算机科学课程的重要组成部分，考察你分析现实问题、设计解决方案并论证决策的能力。本文将通过一个完整的案例——GreenLeaf Organics（一家有机食品供应商）的库存管理系统——进行实战演练。
1. Understanding the Case Study Scenario | 理解案例背景
GreenLeaf Organics is a growing company that supplies organic fruits, vegetables, and packaged goods to local retailers. They currently manage stock using spreadsheets, but as the business expands, they need a dedicated inventory management system to track products, suppliers, orders, and stock levels.
GreenLeaf Organics 是一家成长型企业，为当地零售商供应有机水果、蔬菜和包装食品。目前他们使用电子表格管理库存，但随着业务扩展，需要一套专用的库存管理系统来跟踪产品、供应商、订单和库存水平。
The system must allow warehouse staff to record goods received and dispatched, generate purchase orders automatically when stock falls below a reorder level, and provide managers with reports on stock turnover and supplier performance.
系统必须允许仓库人员记录到货与发货，在库存低于再订货点时自动生成采购订单，并为管理者提供库存周转与供应商表现报告。
Key constraints include a budget of £15,000 for development and the requirement that the system must run on existing office hardware (Windows PCs and a local server).
关键约束包括开发预算为15,000英镑，且系统必须能在现有办公硬件（Windows PC与本地服务器）上运行。
2. Stakeholder Analysis | 利益相关者分析
Stakeholders include the warehouse manager who needs real-time stock updates, purchasing staff who place orders with suppliers, and the finance department who require cost reports. The end-users are warehouse operatives using barcode scanners.
利益相关者包括需要实时库存更新的仓库经理、向供应商下单的采购人员，以及需要成本报告的财务部门。最终用户是使用条形码扫描器的仓库操作员。
Each stakeholder group has distinct requirements. The warehouse manager prioritises accuracy and speed of data entry, while the finance department needs integration with the company's existing Sage accounting software. Purchasing staff want a clear interface to view supplier catalogues and order histories.
每个利益相关者群体有不同的需求。仓库经理最看重数据输入的准确性与速度，财务部门需要与公司现有的Sage会计软件集成。采购人员则希望有一个清晰的界面来查看供应商目录与订单历史。
Interviews with stakeholders revealed that warehouse operatives often wear gloves, so the system must support large touch-friendly buttons and barcode scanning rather than keyboard-intensive input.
与利益相关者的访谈显示，仓库操作员经常戴着手套工作，因此系统必须支持大尺寸触控按钮与条形码扫描，而非依赖键盘输入。
3. Functional & Non-functional Requirements | 功能与非功能需求
Functional requirements describe what the system must do: add, edit, and archive products; record supplier details including lead times; process stock-in and stock-out transactions; generate low-stock alerts when quantity falls below the reorder level; and produce monthly inventory valuation reports.
功能性需求描述了系统必须完成的任务：添加、编辑和归档产品；记录供应商信息及交货周期；处理入库、出库事务；在库存低于再订货点时生成低库存警报；生成月度库存估值报告。
Non-functional requirements include a response time of under 2 seconds for stock lookups, 99.9% uptime during working hours, user authentication with role-based access control, and data encryption for sensitive financial data. The system must also be usable by staff with minimal IT training.
非功能性需求包括库存查询响应时间低于2秒、工作时段99.9%的正常运行时间、带基于角色的访问控制的用户身份验证，以及对敏感财务数据进行加密。系统还须能让仅接受过最少IT培训的员工使用。
A constraint recorded in the analysis is that barcode scanners output data as keyboard emulation, simplifying integration but requiring careful input validation.
分析中记录的一项约束是，条形码扫描器以键盘模拟方式输出数据，这简化了集成但需仔细进行输入验证。
4. Data Modelling: Entity-Relationship Diagram | 数据建模：实体关系图
The main entities identified are Product, Supplier, PurchaseOrder, OrderLine, and StockMovement. A Supplier can supply many Products, creating a one-to-many relationship from Supplier to Product. Each Product can appear on many OrderLines, while each PurchaseOrder is placed with exactly one Supplier and contains many OrderLines.
识别出的主要实体有：Product（产品）、Supplier（供应商）、PurchaseOrder（采购订单）、OrderLine（订单行）和StockMovement（库存变动）。一个供应商可以供应多种产品，构成从Supplier到Product的一对多关系。每个产品可以出现在多个OrderLine中，而每个PurchaseOrder只对应一个供应商，并包含多条OrderLine。
A StockMovement records each individual stock change (in or out) for a Product, with a timestamp and user reference. This is a many-to-one relationship from StockMovement to Product.
StockMovement记录每个产品的单次库存变动（入库或出库），并附带时间戳和用户引用。这是从StockMovement到Product的多对一关系。
| Entity | Key Attributes |
| Product | ProductID, Name, Category, UnitPrice, ReorderLevel, SupplierID (FK) |
| Supplier | SupplierID, Name, Contact, LeadTimeDays |
| PurchaseOrder | OrderID, OrderDate, SupplierID (FK), Status |
| OrderLine | LineID, OrderID (FK), ProductID (FK), Quantity, UnitCost |
| StockMovement | MovementID, ProductID (FK), QuantityChange, Timestamp, UserID |
5. Database Design: Table Structures | 数据库设计：表结构
Based on the data model, we can define the SQL table structures. The Product table uses an auto-increment primary key. The ReorderLevel field is critical for trigger-based low-stock alerts.
基于数据模型，可以定义SQL表结构。Product表使用自增主键。ReorderLevel字段对于基于触发器的低库存警报至关重要。
Product table: EMENT, Name VARCHAR(100) NOT NULL, Category VARCHAR(50), UnitPrice DECIMAL(8,2), ReorderLevel INT, SupplierID INT, FOREIGN KEY (SupplierID) REFERENCES Supplier(SupplierID).
Product 表： EMENT, Name VARCHAR(100) NOT NULL, Category VARCHAR(50), UnitPrice DECIMAL(8,2), ReorderLevel INT, SupplierID INT, FOREIGN KEY (SupplierID) REFERENCES Supplier(SupplierID)。
The StockMovement table captures every stock change. Its QuantityChange can be positive (stock-in) or negative (stock-out). A composite index on ProductID and Timestamp will speed up reports.
StockMovement 表记录每次库存变化。其 QuantityChange 可正（入库）可负（出库）。在 ProductID 和 Timestamp 上建立复合索引可加速报告生成。
All tables use the InnoDB engine to enforce foreign key constraints and support transactions. A before-insert trigger on StockMovement updates the current stock level in a separate StockLevel table for fast queries.
所有表均使用 InnoDB 引擎，以强制外键约束并支持事务。在 StockMovement 上设置 before-insert 触发器，更新一个独立的 StockLevel 表，用于快速查询当前库存水平。
6. Input and Output Design | 输入与输出设计
Input forms must be simple and minimise typing. The 'Record Stock In' form uses a barcode scan to populate the product field, then asks for quantity and an optional supplier delivery note number. Validation ensures quantity is a positive integer.
输入表单必须简单且尽量减少打字。‘Record Stock In’ 表单通过扫描条形码填充产品字段，然后要求输入数量以及可选的供应商送货单号。验证确保数量为正整数。
A 'Generate Purchase Order' wizard guides the purchasing staff: first select a supplier, then the system lists all products from that supplier currently below reorder level, allowing the user to adjust suggested quantities.
‘生成采购订单’向导指导采购人员：首先选择供应商，然后系统列出该供应商所有当前低于再订货点的产品，允许用户调整建议数量。
Outputs include a daily Low-Stock Alert Report, displayed as a dashboard widget and emailed to the warehouse manager. The report is formatted as a table with columns: Product Name, Current Stock, Reorder Level, Supplier, and Suggested Order Quantity. A sample is shown below.
输出包括每日低库存警报报告，以仪表板小组件显示并邮件发送给仓库经理。报告格式化为表格，列包括：产品名称、当前库存、再订货点、供应商和建议订购数量。示例如下。
| Organic Bananas | 3 | 20 | FreshFarm Ltd | 50 |
| Wholegrain Rice | 7 | 15 | GrainsCo | 30 |
Financial reports for the finance department include an 'Inventory Valuation' report calculated as SUM(Product.UnitPrice * StockLevel.CurrentQty) grouped by category.
财务部门所需的财务报告包括‘库存估值’报告，计算公式为按类别分组的 SUM(Product.UnitPrice * StockLevel.CurrentQty)。
7. Algorithmic Thinking: Pseudocode | 算法思维：伪代码
The low-stock alert process runs daily as a scheduled task. It loops through all products, compares current stock against reorder level, and compiles a list for notification. The pseudocode is:
低库存警报程序作为每日计划任务运行。它遍历所有产品，将当前库存与再订货点比较，并编制通知列表。伪代码如下：
FOR each product IN Products
currentStock ← getStockLevel(product.ProductID)
IF currentStock < product.ReorderLevel THEN
alertList.ADD(product, currentStock, product.Supplier)
END IF
END FOR
SORT alertList BY product.Supplier ASC, currentStock ASC
SEND alertList AS email TO warehouseManager
n average monthly usage (AMU) and lead time. The suggested quantity = (AMU × (LeadTimeDays/30) × 1.2) - CurrentStock, rounded up.
另一个关键算法处理自动采购订单创建。它将低库存产品按供应商分组，并根据月均用量（AMU）和交货周期建议订购数量。建议数量 = (AMU × (LeadTimeDays/30) × 1.2) - 当前库存，并向上取整。
8. Implementation Considerations | 实施注意事项
The chosen technology stack is Python with the Flask web framework and a MySQL database, because it fits the budget and the in-house team has Python skills. The web interface is kept simple with HTML, CSS (Bootstrap), and minimal JavaScript, ensuring compatibility with older office PCs.
选定的技术栈是 Python 配合 Flask 网页框架和 MySQL 数据库，因为它符合预算且内部团队具备 Python 技能。网页界面保持简洁，使用 HTML、CSS（Bootstrap）和最少的 JavaScript，确保与较旧的办公 PC 兼容。
Security is addressed with parameterised queries to prevent SQL injection, hashed passwords with bcrypt, and role-based access: warehouse operatives can only record movements, while managers can approve purchase orders. All connections use TLS.
安全方面，使用参数化查询防止 SQL 注入，用 bcrypt 对密码哈希，并实施基于角色的访问控制：仓库操作员只能记录变动，经理可以批准采购订单。所有连接均使用 TLS。
Barcode scanner integration is handled by treating scanner input as rapid keyboard entry terminated by a TAB character, which triggers a JavaScript function to populate the product field. Input validation ensures the scanned code exists in the Product table.
条形码扫描器集成通过将扫描器输入视为以 TAB 字符结束的快速键盘输入来处理，这触发 JavaScript 函数填充产品字段。输入验证确保扫描的代码存在于 Product 表中。
9. Testing Strategy | 测试策略
A comprehensive test plan includes unit testing for the stock update function, integration testing for the email alert module, and user acceptance testing (UAT) with warehouse staff. Test cases are designed to cover normal, boundary, and erroneous conditions.
全面的测试计划包括对库存更新函数的单元测试、对邮件警报模块的集成测试，以及与仓库员工一起进行的用户验收测试（UAT）。测试用例旨在覆盖正常、边界和错误条件。
| Test ID | Description | Input Data | Expected Outcome |
| TC01 | Stock-in normal case | ProductID=101, Qty=25 | Stock level increases by 25; movement logged |
| TC02 | Stock-out leads to negative stock | ProductID=201, Qty=-100 when stock=80 | Error message 'Insufficient stock' |
| TC03 | Reorder level boundary | Stock=20, ReorderLevel=20 | No alert generated |
| TC04 | Barcode not found | Scanned code = 'Z999' nonexistent | 'Product not found' warning |
Performance testing will simulate 50 concurrent warehouse transactions to ensure the 2-second response time is met. Usability testing observes staff completing tasks like receiving stock and generating a purchase order, aiming for a task time under 3 minutes after basic training.
性能测试将模拟 50 个并发的仓库事务，以确保满足 2 秒响应时间。可用性测试观察员工完成收货、生成采购订单等任务，目标是在基础培训后任务时间低于 3 分钟。
10. Evaluation & Maintenance | 评估与维护
After implementation, the system will be evaluated against the original requirements. Metrics include reduction in stock-outs, time saved generating reports, and user satisfaction scores from a questionnaire. A pilot phase at one warehouse will run for 4 weeks before full deployment.
实施后，系统将对照原始需求进行评估。指标包括缺货减少次数、生成报告节省的时间以及用户问卷满意度评分。在全面部署之前，将在一个仓库进行为期 4 周的试点。
Maintenance will be handled through a service-level agreement with the development team, guaranteeing response within 4 hours for critical issues. A version control repository (Git) and issue tracker will track change requests, such as adding integration with a new supplier's EDI system.
维护将通过与发展团队的服务水平协议处理，确保关键问题在 4 小时内响应。版本控制仓库（Git）和问题跟踪器将管理变更请求，例如增加与新供应商 EDI 系统的集成。
The system is designed with scalability in mind: the database can be migrated to a cloud service, and the web interface can be made responsive for mobile devices used on the warehouse floor.
系统设计中考虑了可扩展性：数据库可迁移至云服务，网页界面可调整为响应式设计，以适配仓库现场使用的移动设备。
Lessons learned from this case study emphasise the importance of thorough stakeholder interviews and the value of iterative prototyping, which helped refine the barcode scanning workflow early in development.
从这个案例中汲取的经验教训强调了彻底的利益相关者访谈的重要性以及迭代原型制作的价值，这有助于在开发早期完善条形码扫描工作流程。
Published by TutorHao | Computing Science Revision Series | aleveler.com
更多咨询请联系16621398022（同微信）
或截图后用「扫一扫 → 相册」选择本图