2025年山东科技大学数据库课后习题答案

2025年山东科技大学数据库课后习题答案1.关系代数与SQL综合应用题已知学生表S（SnoCHAR(8),SnameVARCHAR(20),SageINT,SdeptVARCHAR(20)），课程表C（CnoCHAR(4),CnameVARCHAR(30),CpnoCHAR(4),CcreditTINYINT），选课表SC（SnoCHAR(8),CnoCHAR(4),GradeSMALLINT）。其中Sno为主键，Cno为主键，SC的主键为(Sno,Cno)，且Sno是S的外键，Cno是C的外键。（1）用关系代数表达式表示：查询选修了课程号为'C001'且成绩在85分以上的学生学号、姓名及所在院系。解答：首先通过σ操作筛选SC表中满足Cno='C001'且Grade>85的记录，得到中间表SC1；然后将SC1与S表进行自然连接（⋈），关联学生学号；最后通过投影（π）提取Sno、Sname、Sdept属性。表达式为：πSno,Sname,Sdept(σCno='C001'∧Grade>85(SC⋈S))（2）用SQL语句实现：查询所有选修了“数据库系统”课程且成绩及格（≥60分）的学生姓名、课程名及成绩，结果按成绩降序排列，若成绩相同则按姓名升序排列。解答：首先通过JOIN操作连接C、SC、S三张表，其中C.Cname='数据库系统'且SC.Grade≥60；然后选择Sname、Cname、Grade字段；最后使用ORDERBY子句指定排序规则。SQL语句如下：SELECTS.Sname,C.Cname,SC.GradeFROMSJOINSCONS.Sno=SC.SnoJOINCONSC.Cno=C.CnoWHEREC.Cname='数据库系统'ANDSC.Grade>=60ORDERBYSC.GradeDESC,S.SnameASC;（3）用SQL语句实现：统计各院系学生的平均年龄（保留2位小数），仅显示平均年龄超过20岁的院系，结果按平均年龄升序排列。解答：使用GROUPBY按Sdept分组，计算AVG(Sage)并四舍五入保留2位小数；通过HAVING子句筛选平均年龄>20的院系；最后排序。SQL语句如下：SELECTSdept,ROUND(AVG(Sage),2)ASAvgAgeFROMSGROUPBYSdeptHAVINGAVG(Sage)>20ORDERBYAvgAgeASC;2.关系数据库规范化理论分析题给定关系模式R(U,F)，其中U={A,B,C,D,E,G}，函数依赖集F={AB→C,C→D,D→E,B→G,E→B}。（1）求R的候选码。解答：候选码是能唯一决定所有属性的最小属性集。首先计算AB的闭包：AB+=AB（初始）由AB→C，得AB+=ABC由C→D，得AB+=ABCD由D→E，得AB+=ABCDE由E→B，得AB+=ABCDE（B已存在）由B→G，得AB+=ABCDEG（包含所有属性）因此AB是候选码。检查是否存在更小的子集：A+=A（无法推导其他属性）B+=BG（无法推导C、D、E）故AB是唯一候选码。（2）判断R属于第几范式（1NF/2NF/3NF/BCNF），并说明理由。解答：1NF：所有属性不可再分，满足。2NF：要求不存在非主属性对候选码的部分函数依赖。候选码是AB，非主属性为C、D、E、G。AB→C（完全依赖，因为A或B单独无法决定C）但B→G（G是非主属性，B是候选码的真子集），存在部分函数依赖（B→G），因此不满足2NF。综上，R仅满足1NF。（3）将R分解为3NF，保持函数依赖且无损连接。解答：首先分解部分依赖：将B→G单独作为关系模式R1(B,G)，主码为B。剩余函数依赖：AB→C,C→D,D→E,E→B。此时原关系模式剩余属性为A,B,C,D,E。检查是否存在传递依赖：AB→C→D→E→B。其中AB→C（完全依赖），C→D（非主属性对主属性的传递），D→E（传递），E→B（B是主属性，传递依赖到主属性）。分解传递依赖：保留AB→C，得到R2(A,B,C)，主码AB。C→D，得到R3(C,D)，主码C。D→E，得到R4(D,E)，主码D。E→B，得到R5(E,B)，主码E。验证是否保持函数依赖：所有原函数依赖AB→C、C→D、D→E、E→B、B→G均被包含在分解后的关系中。验证无损连接：分解后的关系通过自然连接可恢复原关系，例如R2与R3连接（C），再与R4连接（D），再与R5连接（E），最后与R1连接（B），可恢复原属性集。最终3NF分解为：R1(B,G),R2(A,B,C),R3(C,D),R4(D,E),R5(E,B)。3.数据库设计与E-R模型转换题某高校需设计“教师科研项目管理”数据库，需求如下：教师（工号、姓名、职称、所属院系）科研项目（项目编号、项目名称、类别（纵向/横向）、经费、起始时间、结束时间）教师可参与多个项目，每个项目有多名教师参与，需记录教师在项目中的角色（如负责人、成员）和贡献度（百分比）。（1）绘制简化的E-R图（实体-联系-属性）。解答：实体1：教师（工号，姓名，职称，所属院系），主码：工号。实体2：项目（项目编号，项目名称，类别，经费，起始时间，结束时间），主码：项目编号。联系：参与（教师，项目），类型为多对多（M:N），属性：角色、贡献度。（2）将E-R图转换为关系模型，注明主码和外码。解答：教师表T（Tno,Tname,Title,Dept），主码Tno。项目表P（Pno,Pname,Ptype,Fund,StartDate,EndDate），主码Pno。参与表TP（Tno,Pno,Role,Contribution），主码(Tno,Pno)（联合主码），外码Tno引用T.Tno，外码Pno引用P.Pno。4.事务管理与并发控制题假设银行数据库中有账户表Account（AccNo,Balance），主码AccNo。现有两个事务T1和T2，操作如下：T1：BEGIN;UPDATEAccountSETBalance=Balance+100WHEREAccNo='A001';UPDATEAccountSETBalance=Balance-100WHEREAccNo='A002';COMMIT;T2：BEGIN;SELECTBalanceFROMAccountWHEREAccNo='A001';SELECTBalanceFROMAccountWHEREAccNo='A002';COMMIT;（1）若T1和T2并发执行，可能产生哪些并发问题？举例说明。解答：可能产生“不可重复读”问题。例如：T1先执行第一个UPDATE（A001余额+100），但未提交；T2此时读取A001的余额（已更新后的值），然后T1回滚或继续执行第二个UPDATE（A002余额-100）；T2再次读取A002的余额（可能已被T1修改），导致T2两次读取的A001或A002余额不一致，违反可重复读。（2）若采用封锁机制，如何设置锁的类型和粒度以避免上述问题？解答：T1在更新A001和A002时需加排他锁（X锁），且保持到事务结束（两阶段锁协议）。T2在读取A001和A002时需加共享锁（S锁），若T1已持有X锁，T2需等待T1释放锁后再获取S锁，从而保证T2读取的是T1提交后的数据，避免不可重复读。5.索引设计与查询优化题某电商数据库中有订单表Order（OrderID,UserID,OrderTime,TotalAmount,Status），其中OrderID为主键，UserID为用户编号，OrderTime为下单时间，TotalAmount为订单总金额，Status为订单状态（0-未支付，1-已支付，2-已发货等）。（1）分析以下查询场景，建议是否创建索引并说明理由：①查询某用户（UserID='U1001'）的所有已支付（Status=1）订单，按OrderTime降序排列。解答：建议在(UserID,Status,OrderTime)上创建复合索引。理由：查询条件包含UserID和Status的等值匹配，且需要按OrderTime排序。复合索引可直接覆盖查询条件，避免全表扫描，同时排序操作可利用索引的有序性，减少排序开销。②统计近30天内（OrderTime≥当前时间-30天）所有订单的总金额（TotalAmount）。解答：建议在OrderTime上创建索引。理由：查询条件是OrderTime的范围查询（近30天），索引可快速定位到符合条件的记录，减少扫描的数据量；统计总金额需遍历这些记录，索引可加速范围扫描。（2）若执行SQL语句“SELECTUserID,AVG(TotalAmount)FROMOrderWHEREStatus=1GROUPBYUserID;”，如何优化其执行效率？解答：优化方法：在(Status,UserID)上创建复合索引，包含TotalAmount（覆盖索引）。这样查询时可直接通过索引获取Status=1的记录，并按UserID分组计算平均值，避免回表查询TotalAmount，减少I/O消耗。若数据量极大，可考虑定期汇总提供物化视图（如每日统计各用户已支付订单的总金额和数量），查询时直接从物化视图获取AVG值，降低实时计算成本。6.数据库安全与完整性控制题设计某医院信息系统的患者表Patient（PID,Pname,Gender,Age,Disease,DoctorID），其中PID为主键，DoctorID为负责医生的工号（引用医生表Doctor的DID）。（1）说明需定义的完整性约束（至少3种）。解答：实体完整性：PID为主键，需定义NOTNULL且UNIQUE。参照完整性：DoctorID为外码，需引用Doctor表的DID，可设置ONDELETESETNULL（若医生离职，患者的负责医生置空）或ONDELETERESTRICT（禁止删除有患者关联的医生）。用户定义完整性：Gender只能取'男'或'女'（CHECK(GenderIN('男','女'))）；Age需大于0且小于150（CHECK(Age>0ANDAge<150)）；Disease不能为空（DiseaseNOTNULL）。（2）若需限制“主任医师（职称）可查看所有患者信息，主治医师仅能查看自己负责患者的信息”，如何通过SQL实现？解答：创建角色：CREATEROLE主任医师;CREATEROLE主治医师;为主任医师授予Patient表的SELECT权限：GRANTSELECTONPa