2026年《数据库系统模型与语言》(哈尔滨工业大学)章节作业及答案

2026年《数据库系统模型与语言》(哈尔滨工业大学)章节作业及答案一、关系代数与SQL综合练习1.给定关系模式：用户表User（UID，Uname，RegTime，City），其中UID为主键，RegTime为注册时间（日期类型），City为用户所在城市；订单表Order（OID，UID，OrderTime，TotalAmount，Status），OID为主键，UID为外键（引用User.UID），Status表示订单状态（1=未支付，2=已支付，3=已取消）；商品表Goods（GID，Gname，Price，Category），GID为主键，Category为商品类别；订单明细表OrderDetail（OID，GID，Quantity），OID和GID共同主键，OID外键引用Order.OID，GID外键引用Goods.GID。（1）用关系代数表达式表示：查询2025年1月1日后注册，且所在城市为“北京”的用户，其2025年12月期间已支付（Status=2）订单中，购买“电子产品”类商品的用户UID、Uname及订单总金额（TotalAmount）。（2）将上述关系代数表达式转换为等价的SQL语句（要求使用JOIN语法，不允许使用子查询）。（3）编写SQL语句：查询每个用户（按UID分组）的首单时间（最早的OrderTime），若用户无订单则显示NULL，结果按首单时间降序排列（最近的首单在前）。二、ER模型设计某智能家居管理系统需设计数据库，涉及以下业务规则：系统用户（User）通过手机号（唯一）注册，需记录姓名、注册时间、所在城市；用户可添加多个智能设备（Device），设备有唯一的设备编号（DeviceID），需记录设备类型（如智能灯泡、智能插座）、品牌、出厂时间；用户可创建场景模式（Scene），场景模式有唯一标识（SceneID），需记录名称、触发条件（如“晚上7点”“用户到家”）、执行动作（如“打开客厅灯”“关闭空调”）；一个场景模式可关联多个设备（如同时控制灯和插座），一个设备可被多个场景模式关联；用户可收藏（Collect）其他用户创建的场景模式，收藏需记录收藏时间；设备需记录最后一次在线时间（LastOnline），若超过30天未在线则标记为“离线”。要求：（1）绘制该系统的ER图（需标注实体、属性、联系类型及约束）；（2）将ER图转换为关系模式，说明主键和外键（若存在多值属性需处理）。三、事务隔离级别与并发控制某电商平台库存管理系统存在以下事务：事务T1：查询商品G001的当前库存（Stock），若库存≥100则减少50（Stock=Stock-50）；事务T2：查询商品G001的当前库存，若库存≥80则减少30（Stock=Stock-30）；假设初始库存为120，两个事务同时执行，隔离级别分别为：（1）读未提交（ReadUncommitted）；（2）可重复读（RepeatableRead）；分析两种隔离级别下，可能出现的执行结果及原因（需说明是否存在脏读、不可重复读或幻读问题）。四、查询优化与执行计划某数据库中有订单表Order（OID，UID，OrderTime，TotalAmount），其中UID为用户ID，OrderTime为订单时间（时间戳类型），TotalAmount为订单金额（数值类型）。表中数据量为1000万条，已建立以下索引：索引I1：(UID,OrderTime)（复合索引）；索引I2：(OrderTime,TotalAmount)（复合索引）；索引I3：(TotalAmount)（单列索引）。现有查询Q：“查找2025年1月1日至2025年12月31日期间，UID=‘U1001’的用户，其订单金额大于5000元的所有订单OID及OrderTime”。（1）分析该查询的最优执行计划（需说明索引选择、访问路径及原因）；（2）若表中OrderTime字段存在大量重复值（如按天聚合的订单），是否会影响索引I2的查询效率？请解释原因；（3）假设查询需返回订单的详细商品信息（需关联OrderDetail表），此时是否需要调整索引策略？若需要，给出建议。答案一、关系代数与SQL综合练习（1）关系代数表达式：π_UID,Uname,TotalAmount(σ_RegTime>'2025-01-01'∧City='北京'(User)⋈(σ_OrderTime≥'2025-12-01'∧OrderTime≤'2025-12-31'∧Status=2(Order)⋈(σ_Category='电子产品'(Goods)⋈OrderDetail)))步骤解释：首先过滤User表中2025年1月1日后注册且城市为北京的用户；同时过滤Order表中2025年12月期间已支付的订单；Goods表过滤电子产品类商品，与OrderDetail连接获取订单对应的商品；最后将User、Order、OrderDetail+Goods连接，投影所需字段。（2）等价SQL语句：SELECTu.UID,u.Uname,o.TotalAmountFROMUseruJOINOrderoONu.UID=o.UIDJOINOrderDetailodONo.OID=od.OIDJOINGoodsgONod.GID=g.GIDWHEREu.RegTime>'2025-01-01'ANDu.City='北京'ANDo.OrderTimeBETWEEN'2025-12-01'AND'2025-12-31'ANDo.Status=2ANDg.Category='电子产品';（3）首单时间查询SQL：SELECTu.UID,MIN(o.OrderTime)ASFirstOrderTimeFROMUseruLEFTJOINOrderoONu.UID=o.UIDGROUPBYu.UIDORDERBYFirstOrderTimeDESCNULLSLAST;注：使用LEFTJOIN保留无订单用户，MIN函数在无匹配时返回NULL，NULLSLAST确保NULL值排在末尾（具体语法可能因数据库系统略有差异，如PostgreSQL支持NULLSLAST，MySQL可通过IFNULL处理）。二、ER模型设计（1）ER图关键要素：实体：User（属性：UID（主键）、手机号、姓名、注册时间、城市）；Device（DeviceID（主键）、设备类型、品牌、出厂时间、LastOnline）；Scene（SceneID（主键）、名称、触发条件、执行动作）；Collect（User.UID，Scene.SceneID，收藏时间，主键：(UID,SceneID)）。联系：User与Device：1:N（一个用户添加多个设备），联系名为Owns，约束：User必须存在（强制参与），Device可选（设备可未被用户添加？不，根据业务规则“用户可添加多个设备”，设备需被用户添加，故Device的参与为强制）；User与Scene：1:N（一个用户创建多个场景），联系名为Creates，约束：User强制参与，Scene强制参与（场景必须由用户创建）；Scene与Device：M:N（一个场景关联多个设备，一个设备被多个场景关联），联系名为Associate，无额外约束；User与Scene（收藏）：M:N（一个用户收藏多个场景，一个场景被多个用户收藏），联系名为Collect，属性：收藏时间，主键为(UID,SceneID)。（2）关系模式转换：User(UID,手机号（唯一）,姓名,注册时间,城市)，主键：UID；Device(DeviceID,设备类型,品牌,出厂时间,LastOnline,UID)，主键：DeviceID，外键：UIDREFERENCESUser(UID)（表示设备所属用户）；Scene(SceneID,名称,触发条件,执行动作,CreatorUID)，主键：SceneID，外键：CreatorUIDREFERENCESUser(UID)（表示创建该场景的用户）；Associate(SceneID,DeviceID)，主键：(SceneID,DeviceID)，外键：SceneIDREFERENCESScene(SceneID)，DeviceIDREFERENCESDevice(DeviceID)；Collect(UID,SceneID,收藏时间)，主键：(UID,SceneID)，外键：UIDREFERENCESUser(UID)，SceneIDREFERENCESScene(SceneID)。注：原ER中User与Device的Owns联系通过Device表中的UID外键实现；User与Scene的Creates联系通过Scene表中的CreatorUID外键实现；Scene与Device的M:N联系通过Associate表实现；收藏关系通过Collect表实现。三、事务隔离级别与并发控制（1）读未提交（ReadUncommitted）：可能结果：库存最终为120-50-30=40，或120-30-50=40，或其中一个事务读到另一个事务未提交的中间值。具体分析：假设T1先读取库存为120（满足≥100），将库存减为70（未提交）；此时T2读取到未提交的70（脏读），判断70≥80不成立，不执行更新；T1提交后库存为70，最终库存为70。问题：存在脏读（T2读取了T1未提交的数据），导致T2错误地未执行更新，最终库存可能为70而非预期的40。（2）可重复读（RepeatableRead）：可能结果：库存最终为120-50-30=40（若T1和T2按顺序执行），或出现写冲突（如T1和T2同时读取初始值120，分别计算为70和90，提交时后提交的事务覆盖前一个）。具体分析：T1启动时读取库存为120（快照），执行更新前，T2启动并读取同一快照的120（可重复读保证两次读取一致）；T1提交后库存变为70，T2执行时仍认为库存是120（基于快照），判断120≥80成立，将库存减为90（120-30），最终库存为90（与实际库存70冲突）。问题：可重复读避免了脏读和不可重复读，但可能导致幻读（此处表现为写倾斜，即两个事务基于同一快照更新，导致最终结果不符合预期）。四、查询优化与执行计划（1）最优执行计划：选择索引I1：(UID,OrderTime)。访问路径：首先通过UID='U1001'在I1中快速定位用户的所有订单，然后在该用户的订单中筛选OrderTime在2025年内的记录（I1的OrderTime为第二列，可利用范围查询），最后对这些记录检查TotalAmount>5000，返回OID和OrderTime。原因：查询条件中UID是等值查询，OrderTime是范围查询，符合I1的“左前缀匹配”原则（UID为第一列，OrderTime为第二列），可高效过滤用户和时间范围；而I2的OrderTime为第一列，但需先过滤时间范围再匹配UID，无法利用索引快速定位特定用户；I3仅针对TotalAmount，无法处理UID和时间的联合条件。（2）OrderTime大量重复对I2的影响：会影响效率。索引I2的顺序是(OrderTime,TotalAmount)，若OrderTime重复值多（如按天聚合），则索引的区分度降低，等值或范围查询时需要扫描更多索引条目。例如，查询2025年的订单可能对应多个重复的OrderTime值，导致索引I2的范围扫描需要遍历大量相同OrderTime的记录，增加I/O开销；而I1中UID='U1001'的区分度高（UID是用户唯一标识），即使OrderTime重复，也只需扫描该用户的少量记录，效率更高。（3）关联OrderDetail表时的索引调整建议：需要调整。原查询仅涉及Order表，但关联OrderDetail表（OID,GID,Quantity）后，需