GAC017 计算机 III：数据科学与网页应用

Follow one request from browser to database

教师询问哪些俱乐部还有名额。电子表格可以一次性回答。 Web应用允许读者使用不同的筛选条件再次提问。

我们的示例中有四名学生、四个班级和五次报名。所有记录均为虚构。 这是一个学习项目，而非学校预订服务。

三个部分有不同的职责：

HTTP请求 请求信息 要求获取资源。HTTP响应 响应信息 包含状态和内容。 浏览器发送GET /api/courses?min=2。服务器检查该数值，然后向SQLite查询课程总计。 它返回一个 JSON 对象 ⟦数据对象⟧。浏览器读取该对象并创建表格单元格。 数据库不会向浏览器发送 HTML。浏览器也不会运行我们服务器的 SQL 语句。

使用 node server.mjs 启动提供的三文件示例。打开它打印出的地址。 使用 Node.js 22.13 或更高版本，并配合内置的 SQLite 模块。 请保留 server.mjs、index.html 和 courses.sql 在您自己的练习副本中。 无需注册账户或下载软件包。使用 Ctrl-C 停止自己的服务器。

从站点的静态教学文件夹获取完整示例文件：

• /static/teaching/gac_computing/database-app/server.mjs
• /static/teaching/gac_computing/database-app/courses.sql
• /static/teaching/gac_computing/database-app/index.html.txt
• /static/teaching/gac_computing/database-app/README.txt

将前两个文件按显示的名称保存。将 index.html.txt 另存为 index.html。 最后一个文件包含完整的运行和修改说明。 在站点地址后使用这些路径。页面文件以文本形式提供供下载。 它必须通过本地示例服务器运行才能访问对应的 API。

此示例使用 内存数据库 ⟦in-memory database⟧。重启后会重新生成原始记录。 文件系统数据库可在重启后保留更改。这需要不同的存储方案。

Design relationships before writing queries

每个学生对应 students 中的一行。每个班级对应 courses 中的一行。 选课记录将学生与班级关联起来。一名学生可能选修多个班级。 一个班级可能包含多名学生。这是一种 多对多关系 ⟦many-to-many relationship⟧。 第三张表每行存储一个学生-班级配对。

学生 1 选修了课程 10 和 20。课程 10 包含学生 1、2 和 3。 同一学生姓名不必在每个选课记录行中重复出现。 要修改 Mei 的姓名，只需更改一条学生记录。这样可以避免数据冲突。

以下两个代码块构成一个完整脚本。请在新建的空练习数据库中按顺序运行它们。 请勿针对现有的项目数据库运行此脚本。

PRAGMA foreign_keys = ON;
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE courses (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  capacity INTEGER NOT NULL CHECK (capacity >= 0)
);
CREATE TABLE enrolments (
  student_id INTEGER NOT NULL REFERENCES students(id),
  course_id INTEGER NOT NULL REFERENCES courses(id),
  PRIMARY KEY (student_id, course_id)
);

此时表已创建。添加虚构记录，然后查询每个班级的总数。

INSERT INTO students VALUES
  (1, 'Mei'), (2, 'Kai'), (3, 'Lin'), (4, 'Jia');
INSERT INTO courses VALUES
  (10, 'Coding', 3), (20, 'Coding', 2),
  (30, 'Art', 2), (40, 'Music', 3);
INSERT INTO enrolments VALUES
  (1, 10), (2, 10), (3, 10), (1, 20), (4, 30);
SELECT c.id, c.title, c.capacity,
       COUNT(e.student_id) AS enrolled
FROM courses c
LEFT JOIN enrolments e ON c.id = e.course_id
GROUP BY c.id, c.title, c.capacity
ORDER BY enrolled DESC, c.id;

约束（constraint）会拒绝违反规则的数据。 NOT NULL 要求必须有值。CHECK (capacity >= 0) 拒绝负数容量。 配对外键主键会拒绝重复的选课记录，例如两次出现 (1,10)。 任一 ID 均可出现在多组配对中，但配对本身必须唯一。

当启用外键检查时，外键会拒绝缺失的学生或班级记录。 该脚本显式启用了该项检查。外键不会自动创建缺失的行。 这些规则无法杜绝所有错误。例如，容量设置 3 本身并不限制选课人数为 3。 实际的预订操作需要增加容量检查，并妥善处理并发预订的情况。

Count classes without losing empty ones

课程 10 和 20 的标题均为“编程”（Coding），但它们是不同的班级。 除标题和容量外，还需按课程 ID 进行分组。 仅按标题分组会导致其选课记录被合并，从而得出错误的结论。

LEFT JOIN 保留所有课程，包括没有选课记录的“音乐”课。 未匹配的课程对应的选课部分为空。 COUNT(e.student_id) 统计已匹配的 student ID 数量，并为“音乐”课返回零。 COUNT(*) 统计连接后的行数，包含上述未匹配行，因此会给“音乐”课计数为一。

浏览器通过容量减去已报名人数来计算剩余名额。 存在五条报名记录，但仅有四名学生。梅出现在两条报名记录中。 不要将五视为唯一学生的人数。

若要保留至少包含两名学生的班级，请在 GROUP BY 之后添加此行：

HAVING COUNT(e.student_id) >= 2

这是一个查询片段，添加到完整查询中。它仅返回课程 10。 HAVING 检查每组的总数。WHERE 在计算总数之前检查单独的行。 例如，WHERE c.id = 20 在分组前选择单个班级；它不测试其总数。

Validate and bind the backend input

该路由 /api/courses 接受来自 0 到 99 的最小值。 浏览器数字控件帮助用户输入该数值。直接请求可跳过该控件。 因此服务器会再次检查输入内容。

它会拒绝负数、小数、100、重复的最小参数以及文本。 缺失的 min 表示零。没有课程的成功查询仍然是有效请求。

预编译语句（prepared statement）将 SQL 结构与值分离开来。 服务器使用 >= ? 准备其按课程统计的查询，然后调用 courses.all(minimum)。 绑定的数值填入值占位符位置，但不会拼接进 SQL 文本中。

绑定值可防止该值通过注入攻击危害此查询。 但这并不能证明所有路由或操作都是安全的。 SQL 关键字和列名不能作为普通绑定值提供。 保持查询结构固定，或从服务器允许的选项中进行选择。

服务器仅发送公开的课程统计数据，学生姓名不包含在此响应中。 实际记录还需访问规则和权限才能使用。 虚构示例无需真实的学生信息。

Handle loading, empty results and failures

浏览器使用 fetch 请求数据，必须检查响应状态。 完成的网络请求仍可能返回 400 或 500。 浏览器的 response.ok 用于区分成功的 HTTP 响应与错误响应。

页面在加载前会清除旧行，否则失败的请求可能导致旧结果看似最新。 请求期间显示加载消息并禁用加载按钮。 随后显示结果、空消息或失败消息。 按钮恢复可用状态，以便用户重试。

每个显示的数值应填入 textContent，而不是填入由数据字符串构建的 HTML 中。 课程标题作为文本置于单元格内，不被视为页面标记语言。

该示例使用请求编号，在发起新请求后忽略较旧的响应。 这可以防止页面因延迟响应而替换最新结果。 它不会修改数据库记录，也不能保证预订的安全。

依次尝试最小值 0、2 和 4。预期结果分别为四门课程、一门课程和无课程。 然后停止服务器并再次尝试加载。页面应解释失败原因并允许重试。 重新启动服务器并再次加载。原始虚构数据集应返回。

Turn results into a supported academic decision

以一个问题开始：目前哪些班级还有剩余名额？ 说明你的分析单位：一个班级，通过课程ID进行标识。 在分析之前，检查缺失值、重复的注册对、有效的ID以及非负容量。 在真实报告中注明数据日期，因为注册人数可能会发生变化。

当前答案为：编程 20、艺术 30 和音乐 40。 音乐班有三个剩余名额；其余两个班各有一个。 教师在宣布这些名额前，可以先核实它们是否仍然可用。

可以使用条形图来比较每个课程ID的注册人数与容量。 将两个编程班分开显示，并用它们的ID进行标注。 展示音乐班的零注册人数。不要因其条形较短而将其隐藏。

解释此决定的局限性。这些记录描述的是某一时刻四个虚构的班级。 它们无法衡量教学质量、未来需求或学生选择某门课程的原因。 更多的注册人数并不能证明某门课程带来了更好的学习效果。 避免使用描述性计数作为因果主张的证据。

一份简短报告可采用五个部分：问题、数据与核查、方法、结果、局限及后续行动。 包含查询语句或注明计算名称，以便其他读者能够复现结果。 将学生人数与注册人数分开统计。

Practise with changes and explain your answers

1. 绘制三张表的草图。它们之间通过哪些键连接？为什么需要第三张表？
2. 在运行任一查询之前，预测最小 1 和最小 3 的值。
3. 将 COUNT(e.student_id) 替换为 COUNT(*)。哪个原始结果会出错，原因是什么？
4. 仅按标题分组。两个编程类课程会发生什么变化？
5. 添加课程 50（戏剧），容量为 2，且无注册人数。预测其总人数和剩余名额。
6. 添加注册记录 (2,20)。此时最小 2 应返回什么值？
7. 尝试重复配对 (1,10) 和缺失学生配对 (99,10)。解释每种情况被拒绝的原因。
8. 为什么服务器必须检查浏览器已经验证过的最小值限制？
9. 解释为何空数组会返回 200，而负数最小值会返回 400。
10. 使用原始数据撰写两条建议句和一个局限性说明。

答案解析

1. 学生ID和课程ID将关联的注册表与其父表连接起来。 第三张表表示多名学生参与多门课程，同时避免了姓名或课程信息的重复。
2. 最小 1 返回 10、20 和 30。最小 3 仅返回 10。两个过滤器均包含等值条件。
3. 音乐类的数值由零变为一。COUNT(*) 统计了保留下来的未匹配课程行。
4. 编程类的总数合并为四。这描述的是标题分组，而非单个具体课程。
5. 戏剧类课程注册人数为零，尚余两个名额。左连接操作使其保留在最小 0 中。
6. 编码 20 现在有两名学生注册。最低 2 返回 ID 10 和 20，总数分别为三和两。
7. 配对外键拒绝了重复项。已启用的外键拒绝学生 99，因为该学生不存在。
8. 调用者可以在不使用页面的情况下发送请求。仅靠浏览器检查无法保护服务器。
9. 无匹配行是一个成功的查询。负数最小值违反了 API 的输入规则。
10. 在提供课程之前，请检查编码 20、艺术 30 和音乐 40 的剩余名额。 在此示例中，音乐课程的剩余名额最多。虚构的总数无法预测实际的学生需求。