Year 11 SQA Computing: Case Study Practical Exercise | SQA计算机11年级案例分析实战演练

📚 Year 11 SQA Computing: Case Study Practical Exercise | SQA计算机11年级案例分析实战演练

SQA Computing Science at Year 11 involves tackling practical case study questions that assess your ability to analyze a scenario, design a database, write programs, and evaluate a solution. This article takes you through a complete walkthrough using a fictional bookstore inventory system, highlighting the key steps and skills needed to score high marks on your assignment or exam.

十一年级SQA计算机科学课程涉及解决实际案例研究问题，考查你分析情境、设计数据库、编写程序以及评估方案的能力。本文通过一个虚构的书店库存系统，带你完整演练关键步骤和所需技能，帮助你在作业或考试中取得高分。

1. Understanding the Case Study Scenario | 理解案例情境

The scenario describes a small bookstore called “Chapter One” that currently uses paper records. They want a digital system to handle book inventory and sales. Key details: each book has an ISBN, title, author, price, and stock quantity. Staff must be able to add new titles, update stock when deliveries arrive, record sales (which decrease stock), and generate a report of books with stock below 5. A customer search function by title or author is also required. Read the scenario twice and underline the entities, attributes, and processes.

案例描述了一家名为“Chapter One”的小型书店，目前使用纸质记录，希望建立一个数字系统来管理图书库存和销售。关键信息：每本书有ISBN、书名、作者、价格和库存数量。店员需要能够添加新书、更新进货后的库存、记录销售（会减少库存），并生成库存低于5本的图书报告。还需要提供按书名或作者搜索客户的图书功能。仔细阅读两遍，划出实体、属性和处理过程。

Helpful tip: create a context diagram showing inputs and outputs. For “Chapter One”, inputs include new book details, stock updates, sales, and search queries. Outputs are low-stock alerts, confirmation messages, and search results.

实用提示：创建一个上下文图展示输入输出。对于“Chapter One”，输入包括新书信息、库存更新、销售记录和搜索查询。输出是低库存警报、确认信息和搜索结果。

2. Identifying Functional Requirements | 识别功能需求

Functional requirements describe what the system must do. List them clearly: 1) Add a new book with ISBN, title, author, price, stock. 2) Update stock of an existing book when new copies arrive. 3) Record a sale: enter ISBN and quantity, reduce stock accordingly, prevent sale if insufficient stock. 4) Display all books with stock less than 5. 5) Search by title keyword or author, showing matching results. 6) Calculate total sales amount for a given period (optional). Turn these into a numbered table or bullet points in your documentation.

功能需求描述系统必须执行的任务。清晰列出：1）添加新书，包括ISBN、书名、作者、价格、库存；2）更新现有图书库存（新到货时）；3）记录销售：输入ISBN和数量，相应减少库存，若库存不足则阻止销售；4）显示所有库存低于5本的图书；5）按书名关键字或作者搜索，显示匹配结果；6）计算指定时间段内的销售总额（可选）。在文档中将其转化为编号表格或要点。

Prioritise the requirements: core operations (add, update, sell, report) are essential, while the sales calculation can be an extension. Writing them as user stories (e.g., “As a staff member, I want to add a new book …”) also demonstrates analysis skills.

对需求进行优先级排序：核心操作（添加、更新、销售、报告）必不可少，而销售计算可作为扩展。将它们写成用户故事（例如“作为员工，我想添加新书……”）也能展示分析技能。

3. Designing the Database Structure | 设计数据库结构

Based on the scenario, we need at least two tables: Books and Sales. Design the fields and data types carefully. For SQA purposes, use standard data types such as TEXT, INTEGER, REAL. The Books table will have ISBN (TEXT, primary key), Title (TEXT), Author (TEXT), Price (REAL), Stock (INTEGER). The Sales table: SaleID (INTEGER, primary key, auto-increment), ISBN (TEXT, foreign key), Quantity (INTEGER), SaleDate (TEXT in format YYYY-MM-DD). Use SQL CREATE TABLE statements in your design document.

根据案例，我们至少需要两个表：图书表和销售表。仔细设计字段和数据类型。SQA课程中，使用标准数据类型，如TEXT、INTEGER、REAL。图书表包含ISBN（TEXT，主键）、书名（TEXT）、作者（TEXT）、价格（REAL）、库存（INTEGER）。销售表：SaleID（INTEGER，主键，自增）、ISBN（TEXT，外键）、数量（INTEGER）、销售日期（TEXT，格式YYYY-MM-DD）。在设计文档中使用SQL CREATE TABLE语句。

CREATE TABLE Books (
    ISBN TEXT PRIMARY KEY,
    Title TEXT NOT NULL,
    Author TEXT NOT NULL,
    Price REAL CHECK(Price > 0),
    Stock INTEGER DEFAULT 0
);

CREATE TABLE Sales (
    SaleID INTEGER PRIMARY KEY AUTOINCREMENT,
    ISBN TEXT,
    Quantity INTEGER,
    SaleDate TEXT,
    FOREIGN KEY (ISBN) REFERENCES Books(ISBN)
);

Note the use of CHECK constraint to ensure price is positive and DEFAULT 0 for stock. These constraints improve data integrity and are valued in SQA marking. Also describe a data dictionary with field names, types, descriptions, and validation rules.

注意使用CHECK约束确保价格为正，库存的DEFAULT 0。这些约束提升数据完整性，在SQA评分中会被看重。另外，描述一个数据字典，包含字段名、类型、描述和验证规则。

4. Implementing Input Validation | 实现输入验证

When writing the Python program (or your chosen language), robust input validation is essential. For example, when adding a new book, price must be a positive number, stock a positive integer. Use a loop that repeats until valid input is given. Handle exceptions using try-except to avoid program crashes. Below is a function to get a valid float for price:

编写Python程序（或所选语言）时，健壮的输入验证至关重要。例如，添加新书时，价格必须是正数，库存为正整数。使用循环直到输入有效。利用try-except处理异常，避免程序崩溃。下面是一个获取有效float价格的函数：

def get_positive_float(prompt):
    while True:
        try:
            value = float(input(prompt))
            if value > 0:
                return value
            else:
                print("Must be greater than zero.")
        except ValueError:
            print("Invalid number, try again.")

Then explain the logic in your documentation: the function repeatedly asks for input, catches non-numeric input, and checks positivity. Apply similar validation for stock (integer > -1) and ISBN format (e.g., must be 10 or 13 digits). This shows thoroughness and prevents runtime errors.

然后在文档中解释逻辑：该函数反复要求输入，捕获非数字输入，并检查是否为正。对库存（整数 > -1）和ISBN格式（如必须是10或13位数字）应用类似验证。这展示了细致程度，能防止运行时错误。

5. Developing Search and Sort Algorithms | 开发搜索与排序算法

Two common algorithms in case studies are linear search and bubble sort. For the search function, given a list of books (read from a database or file), implement linear search to find all books where the title contains a keyword. Example pseudocode:

案例研究中常见的两种算法是线性搜索和冒泡排序。对于搜索功能，给定从数据库或文件读取的书籍列表，实现线性搜索以查找书名包含关键字的所有图书。示例伪代码：

FUNCTION search_by_title(books, keyword)
    results = empty list
    FOR each book IN books
        IF keyword.lower() IN book.title.lower()
            APPEND book TO results
    RETURN results

For generating a sorted report (e.g., low-stock books sorted by title), use bubble sort on the extracted list. While Python’s built-in sorted() is efficient, SQA often expects you to demonstrate understanding of a basic sorting algorithm. You could write a bubble sort function and explain its complexity O(n²). Include a swap flag to optimize.

要生成排序报告（如按书名排序的低库存图书），对提取列表使用冒泡排序。虽然Python内置sorted()效率高，但SQA通常希望展示对基本排序算法的理解。你可以编写冒泡排序函数并解释其复杂度O(n²)。加入交换标志进行优化。

Remember to insert appropriate comments in your code, as marks are awarded for readability and maintainability. Clearly identify the algorithm’s name and trace through a small dataset.

记得在代码中插入适当注释，因为可读性和可维护性会得分。清楚标明算法名称，并使用小型数据集进行跟踪测试。

6. Creating User-Friendly Output | 创建用户友好输出

The low-stock report should be clearly formatted with column headings, perhaps using formatted strings. For example:

低库存报告应格式清晰，包含列标题，可使用格式化字符串。例如：

print(f"{'ISBN':<15} {'Title':<30} {'Stock':<6}")
print("-"*51)
for book in low_stock_books:
    print(f"{book.isbn:<15} {book.title:<30} {book.stock:<6}")

This output aligns columns, making it easy to read. Similarly, after recording a sale, display a confirmation with the new stock level. Colour-coding (using ANSI codes) is optional but can impress if explained. Always keep the user informed of the system status.

该输出对齐列，便于阅读。同样，记录销售后，显示确认信息和新库存水平。使用颜色编码（ANSI代码）是可选的，如果解释得当可以加分。务必让用户始终了解系统状态。

Additionally, consider writing results to a CSV file for record-keeping. Use the csv module and ensure proper error handling for file operations (e.g., file not found, permission errors).

此外，考虑将结果写入CSV文件以

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

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

Year 11 SQA Computing: Case Study Practical Exercise | SQA计算机11年级案例分析实战演练