完成本实验后，你应能够：
EXPLAIN QUERY PLAN 和运行时间对比，判断查询是否使用索引以及索引是否真的带来性能收益。你正在参与一个清华大学校园数据平台的原型设计。这个平台一开始要服务教务场景：记录院系、学生、课程以及开课历史，并支持后续基于关系数据库的查询与维护。作为数据库小组的一员，你首先需要把需求整理成清晰的 E/R 模型，再将模型转换为可以在 SQLite 中创建的关系表。
但一个数据库系统仅仅“能存数据”还不够。系统上线后，查询会变多、数据会变大，原本正确的 SQL 也可能因为缺少索引而运行缓慢。为了在更大的数据规模上理解查询优化，本实验的第二部分将使用一个航班数据库 data/flight.db 进行性能诊断：你需要为给定查询选择索引，观察查询计划，并用运行时间截图验证优化效果。
你的工作将分为两个阶段：
评分规则： 共 100 分（2 个任务）。
| 任务 | 内容 | 分值 |
|---|---|---|
| 任务 1 | 数据库设计：E/R 模型与关系模式 | 50 分 |
| 任务 2 | 查询优化：索引、查询计划与运行时间对比 | 50 分 |
作答要求：
[请填写] 的位置写下你的文字答案。# 请在这里填写你的代码 的代码单元中完成 SQL 实现。assets/ER-diagram.png，并确保 notebook 中可以正常显示。runtimes/ 文件夹中的对应图片。本实验会用到以下材料：
data/campus.db：用于数据库设计任务的 SQLite 数据库；assets/ER-diagram.png：你的 E/R 图文件；data/flight.db：用于索引优化任务的航班数据库；runtimes/：保存查询运行时间截图的文件夹。在第一部分中，你会把校园教务需求转换为 data/campus.db 中的关系表。下面的代码连接到该数据库。
%load_ext sql
%sql sqlite:///data/campus.db
你需要为清华大学设计一个简化版教务数据库。该数据库面向课程管理和学生选课场景，需要记录院系、学生、课程以及课程开设信息，具体需求如下：
院系(departments) 信息包括院系名称和地址。院系名称是唯一的，不会被其他院系共享。
数据库需要记录学生与其所属院系之间的关系。每个学生必须且只能隶属于一个院系。
课程(course) 信息包括课程编号(number), 如 30240243、课程名称(name), 如 数据库技术与应用 和容量(capacity)， 如 110。数据库还需要记录每门课程唯一所属的院系：
数据库需要记录清华大学历史上每门课程在哪些学期被开设过。一个学期由 semester（例如 spring 或 fall）和 year（例如 2026）共同标识。
假设一门课程在某个学期开设时，至少有一名学生选修该课程。同一门课程在同一个学期至多开设一次，也就是说，同一学期内不考虑多个 section。
学生可以选修不属于自己所在院系的课程。同时，每个学生至少应该选修一门课程。
请根据上面的业务需求，使用课堂中学习过的 E/R 模型表示法，绘制清华大学教务数据库的 E/R 图。你的图需要准确表达所有实体、属性、联系、和约束。
请将完成后的图片保存为 assets/ER-diagram.png。下面会显示该文件，你可以用它检查 notebook 中的图片路径是否正确。
请根据你绘制的 E/R 图，编写 SQL 语句，在 data/campus.db 中创建所需的关系表。
要求：
# 请在这里填写你的代码
现在，你已经完成了数据库设计的第一步。但在真实系统中，数据库很快会遇到另一个问题：数据量上来以后，查询是否还能跑得动？
为了更直观地观察索引的作用，本任务使用一个规模更大的航班数据库 data/flight.db。数据库中只有一张表 FLIGHTS，其模式如下：
FLIGHTS (fid, year, month_id, day_of_month, day_of_week_id, carrier_id, flight_num, origin_city, origin_state, dest_city, dest_state, departure_delay, taxi_out, arrival_delay, canceled, actual_time, distance)
本任务只需要关注四个属性：carrier_id、origin_city、actual_time 和 dest_city。
你需要完成三件事：选择索引、检查查询计划、用运行时间截图验证索引效果。
请考虑以下三个查询：
sqlite
(a): SELECT DISTINCT carrier_id
FROM Flights
WHERE origin_city = 'Seattle WA' AND actual_time <= 180;sqlite
(b): SELECT DISTINCT carrier_id
FROM Flights
WHERE origin_city = 'Gunnison CO' AND actual_time <= 180;sqlite
(c): SELECT DISTINCT carrier_id
FROM Flights
WHERE origin_city = 'Seattle WA' AND actual_time <= 30;请为这三个查询选择一个最可能同时加速它们的简单索引。简单索引指只建立在一个属性上的索引。
CREATE INDEX 语句（5 分）：请打开命令行中的 SQLite，使用 sqlite3 data/flight.db 连接提供的数据库，检查 FLIGHTS 表是否已经存在你选择的索引。如果不存在，请把该索引添加到 FLIGHTS 表中。
FLIGHTS 表是否已经存在你选择的索引？（2 分）请使用 EXPLAIN QUERY PLAN ... 检查每个查询是否使用了该索引。
请考虑下面的查询：
sqlite
(d): SELECT DISTINCT F2.origin_city
FROM Flights F1, Flights F2
WHERE F1.dest_city = F2.dest_city
AND F1.origin_city='Gunnison CO'
AND F1.actual_time <= 30;请选择一个与 2.1 中不同的简单索引，使它有可能加速这个查询。请写出 CREATE INDEX 语句，并解释你的选择。
CREATE INDEX 语句（5 分）：请使用 sqlite3 data/flight.db 连接提供的数据库，检查 FLIGHTS 表是否已经存在这个第二个索引。如果不存在，请把该索引添加到 FLIGHTS 表中。
FLIGHTS 表是否已经存在你选择的第二个索引？（2 分）请使用 EXPLAIN QUERY PLAN ... 检查查询 (d) 是否使用了这个第二个索引。
现在我们想知道：这两个索引到底有多有效？请比较查询 (a) 到 (d) 在没有索引和有索引两种情况下的运行时间。
提示：在 SQLite 中可以使用 .timer on 打开计时功能。开始计时前，请先用 .schema Flights 或 PRAGMA index_list(Flights); 确认当前表是否处于你需要的索引状态；如果需要回到无索引状态，可以使用 DROP INDEX 索引名; 删除你创建的索引。
请在没有这两个索引的 FLIGHTS 表上执行查询 (a) 到 (d)，并为每个查询的运行时间截图。下面是示例截图位置，请用你自己的截图替换 runtimes/ 文件夹中的对应图片。
 |
 |
 |
 |
请在已经创建两个索引的 FLIGHTS 表上执行查询 (a) 到 (d)，并为每个查询的运行时间截图。下面是示例截图位置，请用你自己的截图替换 runtimes/ 文件夹中的对应图片。
 |
 |
 |
 |
恭喜你完成了从数据库设计到查询优化的一次完整实践。你已经经历了数据库系统建设中非常典型的两步：先把现实需求抽象成清晰的数据模型，再通过索引和查询计划让系统在更大数据量上跑得更快。
提交前最终检查：
assets/ER-diagram.png 已替换为你绘制的 E/R 图，并能在 notebook 中正常显示。data/campus.db 中已经创建了你设计的关系表。runtimes/ 中的 8 张运行时间截图已经替换为你自己的实验结果。[请填写]。提交文件： 请将以下内容打包为一个压缩文件提交：
Lab7.ipynb；assets/ER-diagram.png；data/campus.db；runtimes/ 文件夹。你不需要提交 data/flight.db。