logo 滴墨
期末复习

2023-2024数据库2A卷

2026 年 07 月 02 日 约 6055 字 · 16 分钟 期末复习

数据库考试题

一、解答题（第 1A、1B 中任选一题，第 2 题必做；每小题 9 分，本题共 18 分）

1A. 数据库的三级模式结构分别是指什么？以 MySales 数据库为例，举例说明该数据库如何创建和使用三级模式结构。

1B. MySQL 数据库中实现并发控制的主要机制有哪些？在多用户环境下，如果一个用户正在向订单表和订单明细表中插入某个客户的销售订单记录，而另一个用户此时在客户表中删除这个客户，阐述如何使用并发控制机制来防止数据的不一致性。

  1. 设关系模式 R(A, B, C, D, E)，根据语义有如下函数依赖集：F={D→C, CE→B, BC→D, DE→A}。

    1）计算 $(CE)_F^+$ 闭包值；

    2）求关系 R 的候选码；

    3）关系模式 R 最高属于第几范式？

二、关系代数运算题（任选 2 小题解答，本题共 12 分）

已知一个学生选课数据库包含学生、课程、教师、选课和授课等 5 个关系模式，分别用 Students, Courses, Teachers, StudCourses, Instructions 表示。各个关系模式表示如下：

  • Students (Sno, Sname, Gender, Class, Major) = 学生（学号，姓名，性别，班级，所属专业）

  • Courses (Cno, Cname, type, Credit) = 课程（课程编号，课程名称，课程类型，学分）

  • Teachers (Tno, Tname, birthdate, gender, Title, Major) = 教师（教师编号，姓名，出生日期，性别，职称，所属专业）

  • StudCourses (Sno, Cno, Period, Grade) = 选课（学号，课程编号，选课学期，成绩）

  • Instructions (Tno, Cno, Period) = 授课（教师编码，课程编号，授课学期）

假设同一学生同一学期同一课程只有一个授课教师；课程类型分为“必修”与“选修”两类；教授职称分为教授、副教授和讲师三个等级，试用关系代数完成下列查询。

  1. 检索 2022-2023-1 学期“达尔文”和“牛顿”这两位老师授课的“数据库原理与应用”这门课程中有哪些学生考试成绩是及格的，列出这些学生的学号、姓名、班级、专业和成绩。（5 分）

  2. 检索哪些学生 2022-2023-1 学期所有“必修”类课程考试成绩都是及格的，而且没有一门课程是因为之前不及格而重修选课的。（7 分）

  3. 检索在学号为 s1 这个学生所在的班级中，哪些学生至少选读了 s1 这个学生的全部课程，而且比 s1 这个学生还多选了“人工智能基础”这门课程，列出这些学生的学号和姓名。（7 分）

三、数据库设计题（本题共 36 分）

已知“美团外卖”网上订餐平台数据库至少包含顾客、骑手、站点、餐店、菜品等实体，部分实体的主要属性及语义如下：

  1. 该网上订餐平台包含多个美团服务站点，每个美团站点包含站点编码、名称、所在城市、地址、负责人等属性；每个站点可以有多个骑手（快递员），每个骑手包含员工编码、姓名、身份证号、手机号码、居住地址等属性。一个骑手在同一时间内只能属于一个美团站点，但在不同时间中可以加入不同美团站点。

  2. 一个美团站点可以有多个加盟餐店，一个餐店同一年度只能与一个美团站点签订加盟协议；餐店包含餐店编码、名称、地址、联系人、联系电话、营业执照编号等属性。

  3. 一个餐店可以提供多个菜品，一个菜品只能由一个餐店提供，不同餐店的可以有名称相同的菜名；每个菜品包含菜品编码、名称、主料、口味、报价等属性。

  4. 顾客包含顾客账号、姓名、联系电话等属性，顾客有一个默认的送货地址，但在不同时间下单可以有不同送货地址；顾客下单时，需要记录下单时间和选择到货的时间（一般是一个区间值），系统产生一个独一无二的订单编号，顾客在不同时间订购同一个菜品时，其购买单价可能不同。

  5. 平台按下单的订单编号分配骑手进行配送，每个骑手每天可接单不超过 100 件；每次配送需要记录其配送费用和配送完成时间，并对骑手服务和餐店菜品品质进行评价。

试根据上述语义完成下列各题。

  1. 设计满足上述语义要求的 E-R 图，需标明实体的属性、实体之间的联系以及联系产生的新属性。（12 分）

  2. 将该 E-R 图转换成关系模式，并指出每一个关系模式中的主码和外码。（10 分）

  3. 使用 MySQL 建表语句创建“骑手”这个关系模式的数据表，设置其主键和外键等约束条件；同时添加 2 个计算列，要求根据“骑手”的身份证号，自动计算得到“骑手”的出生日期（date型，例如 1986-02-10）和“性别”（采用“男”或“女”两种值）这两个列的值。（6 分）

  4. “每个骑手每天可接单不超过 100 件、一个餐店同一年度只能与一个美团站点签订加盟协议”，阐述在 MySQL 数据库设计时可使用什么机制去实现上述这两个语义规定。（4 分）

  5. 在 MySQL 中如何根据“骑手”的出生日期得到其实际年龄？如何根据身份证号的前 4 个字符（例如 3301），通过与地区表（Areas）相连接得到快递员身份证号所对应的那个城市的名称？使得在每次数据检索时可以直接提取年龄和城市这两个列的值，而无需每次计算或多表连接得到这两个列的值。（4 分）

四、SQL 程序设计题（本题共 34 分）

  1. 将下列关系代数转换为一条 SQL 语句。（7 分）
\[R = \Pi_{OrderID}(\sigma_{Orderdate>='2018-01-01' \land Orderdate<='2018-06-30'}(Orders) \bowtie OrderItems \bowtie \sigma_{Productname='青岛啤酒'}(Products) \cap \sigma_{Orderdate>='2018-01-01' \land Orderdate<='2018-06-30'}(Orders) \bowtie OrderItems \bowtie \sigma_{Productname='百威啤酒'}(Products))\] \[\Pi_{CustomerID, Companyname}(Customers) - \Pi_{CustomerID, Companyname}(R \bowtie Customers)\]
  1. 包含视图 v1 定义如下。创建一个存储过程，输入一个月份（含年份，如 2019-02），要求利用这个视图 v1，按树型结构输出数据检索结果，具体要求如下：树的第一层结点为客户所属省份（结点文本内容为省份名称与销售额的拼接）；第二层为每个省份所属的各个客户信息（结点文本内容为客户编码、客户名称与其销售额的拼接）；第三层为每个客户这个月份的所有销售订单信息（结点文本为订单号、订单日期与订单销售汇总值，按订单日期排序）。整个树型结构中没有销售记录的省份和客户不需要出现。（本题 12 分）

SQL

create or replace view v1 as
select a.*, b.Amount, c.OrderID,
c.Orderdate, c.CustomerID,
d.Companyname, d.RegionID, d.CityID
from Products a
join Orderitems b using(productid)
join Orders c using(orderid)
join Customers d using(customerid);

提示：可以使用视图 v1 和 with as 简化数据查询过程；注意销售额汇总值的计算。

（树型结构输出示例）：

  • 上海市(5874.24万元)

  • 江苏省(9459.81万元)

  • 浙江省(13403.19万元)

    • 博创商贸有限公司(1011.28万元)

    • 博客工贸有限公司(244.77万元)

      • 16468 2018-10-10(11385.50元)

      • 16501 2018-10-11(19078.50元)

      • 16715 2018-10-23(24884.72元)

      • 16788 2018-10-25(14472.36元)

      • 16863 2018-10-29(20677.00元)

    • 恒宇农业开发有限公司(216.63万元)

    • 湖州味源饮料食品商贸公司(350.76万元)

      • 16309 2018-10-03(14962.85元)

      • 16421 2018-10-09(16846.25元)

  1. 已知一个 JSON 对象数组存储的两个商品数据集分别为 oldData 和 newData，其数据结构举例如下，每个 JSON 元素包含 productid、productname、quantityperunit、unit、unitprice、supplierid、categoryid、subcategoryid 这 8 个属性。假设商品编码为自增列，试编写存储过程，比较判断这两个数组对应的商品信息中各个列的数据是否全部相同（不考虑数组中商品记录的先后次序）。如果两者数据存在差异，则编写程序实现下列各项数据操作功能。提示：可以使用多个或多个存储过程实现，同时也可以使用 temporary 临时表。（15 分）

    1. 将商品编码在 newData 和 olddata 中都存在的那些商品记录更新（修改）到商品表中去，以 newData 中的最新数值为准，原来记录的商品编码值不变。

    2. 将商品编码在 newData 其值为 0（实际上在 olddata 中是不存在的）的那些商品记录新增（插入）到商品表中去，要求自动生成这些新增商品编码自增列的值。

    3. 将商品编码在 newData 不存在而在 olddata 中存在的这些商品记录从商品表中删除。

JSON

newData=[{"unit":"支", "productid": 36, "unitprice": 10.00, "categoryid":"B", "supplierid":"ZHYT", "productname":"天禾青芥辣", "subcategoryid": "B1", "quantityperunit":"43g"}, {"unit":"箱", "productid": 37, "unitprice": 20.00, "categoryid":"G", "supplierid":"XYNY", "productname":"恩施富硒小土豆", "subcategoryid": "G1", "quantityperunit":"2500g"}, {"unit":"盒", "productid": 38, "unitprice": 12.50, "categoryid":"B", "supplierid":"XHSP", "productname":"欣和黄豆酱", "subcategoryid": "B1", "quantityperunit":"800g"}, {"unit":"袋", "productid": 13, "unitprice": 52.00, "categoryid":"H", "supplierid":"JHHT", "productname":"大连鲜活基围虾", "subcategoryid": "H101", "quantityperunit":"500g"}, {"unit":"箱", "productid": 0, "unitprice": 18.00, "categoryid":"A", "supplierid":"WHHJ", "productname":"娃哈哈纯净水", "subcategoryid": "A3", "quantityperunit":"596ml*12瓶"}];

oldData=[{"unit":"支", "productid": 36, "unitprice": 10.00, "categoryid":"B", "supplierid":"ZHYT", "productname":"天禾青芥辣芥末酱", "subcategoryid": "B1", "quantityperunit":"43g"}, {"unit":"箱", "productid": 37, "unitprice": 20.00, "categoryid":"G", "supplierid":"XYNY", "productname":"恩施富硒小土豆", "subcategoryid": "G1", "quantityperunit":"2500g"}, {"unit":"盒", "productid": 38, "unitprice": 12.50, "categoryid":"B", "supplierid":"XHSP", "productname":"欣和黄豆酱", "subcategoryid": "B1", "quantityperunit":"800g"}, {"unit":"包", "productid": 15, "unitprice": 50.00, "categoryid":"F", "supplierid":"JHHT", "productname":"金华火腿切片", "subcategoryid": "F22", "quantityperunit":"288g"}, {"unit":"袋", "productid": 14, "unitprice": 22.50, "categoryid":"E", "supplierid":"HSSP", "productname":"亨氏婴幼儿面条", "subcategoryid": "E3", "quantityperunit":"252g"}];

  1. （附加题，本题 15 分。只有基本全对才给分）创建一个存储过程，输入一条 SQL 查询语句 Selectsql、分页页码 spageno 和每页显示行数 spagesize 这三个参数，以 JSON 对象格式返回该条查询语句这一页的输出结果，要求输出结果中包含行的总数（属性为 total），参考格式如下。

    注意：查询语句中可能带有 with as 的 CTE 表达式；不可以调用 sys_gridpaging 存储过程。

JSON

{"total":"145", "rows":[{"productid":"1", "quantityperunit":"330ml*6罐", "productname":"王老吉凉茶", "unitprice":"19.80", "categoryid":"A", "key":"1"}, ..., {"productid":"2", "quantityperunit":"330ml*6罐", "productname":"青岛啤酒", "unitprice":"26.00", "categoryid":"A", "key":"2"}]}

语句与函数使用方法示例：

SQL

SELECT * FROM JSON_TABLE(@data, "$[*]" COLUMNS(
    CustomerID char(10) PATH "$.customerid",
    Companyname varchar(100) PATH "$.companyname",
    Amount decimal(12,2) PATH "$.amount" )
) as p;

set @n:=0; SHOW COLUMNS FROM orders where @n:=@n+1;
SELECT JSON_UNQUOTE(json_extract(@data, '$[0].customerid')) as CustomerID...
本文作者 Dimo，采用 CC BY-NC-SA 4.0 协议，转载请注明出处。
原文链接：https://dimo.qwq6.xyz/2026/07/02/%E6%95%B0%E6%8D%AE%E5%BA%932/