数据库试题.pdf

1 五、计算题（每小题 4 分，本大题共 24 分。 ） 现有一图书管理系统数据模型： 图书品种（ISBN 号，书名，出版社，作者，出版日期，单价） 图书（图书号，ISBN 号，库位，架位，借出否） 读者（读者号，姓名，身份证号，电话，停借，挂失，读者类型号） 读者类型（读者类型号，名称，最大借书天数，最大借书数量） 借阅（图书号，读者号，借书日期，还书日期） 说明说明： “图书”表中记录图书馆的每一本书的信息，每本书一个图书号 。 读者一但还回所借图书，立即从借阅表中删除该记录。 “借出否”字段的取值类型为 bit 类型，0 表示未借出，1 表示借出。 “停借” 字段的取值类型为 bit 类型，0 表示未停借，1 表示停借。 “挂失” 字段的取值类型为 bit 类型，0 表示未挂失，1 表示挂失。 “出版日期” 、 “借书日期” 、 “还书日期”字段取值类型为日期类型。 请用关系代数做如下操作请用关系代数做如下操作： 1、检索至少借了两本书的读者的读者号。 读者号（借阅SC1借阅SC2） SC1.读者号= SC2.读者号ANDSC1.书号 SC2. 书号 请用请用 SQL 语句做如下操作语句做如下操作： 1、查询借了两本以上图书的读者的读者号、姓名。 SELECT读者.读者号，姓名 FROM读者，借阅 WHERE读者.读者号=借阅.读者号 GROUP BY 读者号 HAVINGCOUNT（*）=2 2、统计图书馆所购买的“清华大学出版社” 在 2006-1-1 到 2006-12-31 期间，出版的图书 数量，总金额。 SELECTCOUNT(*) ，SUM(单价) FROM图书品种，图书 WHERE图书品种.ISBN 号=图书.ISBN 号 AND出版社= 清华大学出版社AND 出版日期BETWEEN2006-1-1AND2006-12-31 3、统计读者号=200518001的读者所借图书的总数。 SELECTCOUNT（*） FROM借阅 WHERE读者号=200518001 4、以 2006-12-31 为期限，将有到期未还的图书的读者的“停借”属性设置为 1。 UPDATE读者 SET停借=1 WHERE读者号IN（SELECT 读者号 FROM 借阅 WHERE还书日期80 ) 4、请统计学生的“学号” ， “选课门数” ， “平均分” ，并将结果插入到“学生成绩”统计表中。 注意：成绩值为 NULL 的课程不在统计范围内。 INSERTINTO 学生成绩统计 （学号，选课门数，平均分） VALUES SELECT学号，COUNT(*)，AVG(成绩) FROM选课 GROUPBY学号 2、统计教师名为LHB的教师所上课的“课号” ，学生“平均分” 。 SELECTC# , AVG(SCORE) FROMT ，TC ，SC WHERET.T#=TC.T#AND TC.C# =SC.C#ANDTNAME=LHB GROUPC# 3、请将选课平均分大于 80 分的学生的“评价 ASSESS”字段的值改为“优秀” UPDATES SETASSESS=优秀 WHERES#IN（SELECTS# FROMSC GROUPS# HAVINGAVG（SCORE）80 ) 4、请为学号为200518110的学生选上课号为18001的课。 INSERTINTOSC ( S# , C# ) VALUES ( 200518110, 18001) 5、删除 2002 级、性别为女的学生的选课信息。 DELETEFROMSC WHERES#LIKE2000%ANDS#IN(SELECTS# FROMS WHERESEX=女) 1、查询除去汉族 、 蒙古族以外的所有民族的学生信息。 SELECT* FROM学生 WHERE民族NOTIN（ 汉族 ， 蒙古族 ） 2、计算 S# =S2的学生所选各门课的平均分。 SELECTAVG(成绩) FROM选课 WHERE学号=S2 GROUP BY 学号 3、统计每门课程的学生人数。要求：显示课号、人数，并按人数降序输出。 SELECT课号，COUNT(*) FROM选课 GROUP BY 课号 ORDERBYCOUNT(*)DESC 4、查询至少选了两门课的学生的学号、姓名。 6 SELECT学号，姓名 FROM选课 GROUP BY 学号 HAVINGCOUNT（*）=2 5、把选修C2课的蒙族学生成绩增加 5 分。 UPDATE选课 SET成绩=成绩+5 WHERE学号 IN（SELECT学号 FROM学生 WHERE民族LIKE 蒙% ） 请用关系代数、元组演算做如下查询：请用关系代数、元组演算做如下查询： 1、检索 LIU 老师所授教课程的课号、课名。 2、检索年龄大于 23 岁的男同学的学号、姓名。 3、检索至少选修 LIU 老师所授课程中一门课的女学生姓名。 4、检索 WANG 同学不 的课程。 5、检索至少选修两门课的学生学号。 6、检索全部学生都选修的课程课号。 7、检索选修课程包含 LIU 老师所授全部课程的学生学号。 请用请用 SQL 语句做如下操作语句做如下操作： 2、统计教师名为LHB的教师所上课的“课号” ，学生“平均分” 。 3、请将选课平均分大于 80 分的学生的“评价 ASSESS”字段的值改为“优秀” 4、请为学号为200518110的学生选上课号为18001的课。 5、请将选修了C2课，成绩不为 NULL 且成绩90 的“蒙族”学生的成绩加 10 分。 6、删除 2002 级、性别为女的学生的选课信息。 7、查询 T# =T2的教师所教课程的选课人数。 8、计算 T# =T2的教师所教课程的平均成绩。 9、计算 S# =S2的学生所选各门课的平均分。 10、计算 S# =S2的学生选课门数。 11、统计每门课程的学生人数。要求：显示课号、人数，并按人数降序输出。 12、计算每个学生的平均分，输出格式为：学号，平均分，按平均分由高到低顺序输出。 13、查询除去汉族 、 蒙古族以外的所有民族的学生信息。 14、查询至少选了两门课的学生的学号、姓名。 15、查询选课人数不超过 20 人的课程信息。 五、计算题（每小题 4 分，本大题共 24 分。 ） 现有一超市销售管理系统数据模型： 商品（商品号，名称，规格，零售价，商品分类号） 商品分类（商品分类号，商品分类名称） 销售单（销售单号，时间，总金额，售货员号） 销售单细节（销售单号，商品号，数量，合计金额） 售货员（售货员号，姓名，生日，民族，参加工作时间，身份证号，级别） 售货员日工作量统计（售货员号，日期，销售商品总数，总销售金额） 请用关系代数做如下操作请用关系代数做如下操作： 1、查询商品分类名称=日用品的商品编号，商品名称。 商品号，商品名称（商品分类号（(商品分类名称=日用品)（商品分类）商品） 得分 7 2、查询至少购买了销售单号=234561所购商品的销售单。 T1=商品号（(销售单号=234561)（销售单细节） T2=商品号，销售单号（销售单细节） T3=T2T1 六： 2、 （12 分） 为某家城市银行的人民币活期储蓄业务管理设计一个数据库，该银行的业务规则如下： 该行有若干支行，每个支行有编号（编号全行唯一） 、名称、地址、电话。 每个支行有若干储蓄所，每个储蓄所有编号（编号全行唯一） 、名称、地址、电话。 每个储蓄所有若干工作人员，一个工作人员有职工号（编号全行唯一） 、姓名、性别、 民族、生日、参加工作时间、身份证号。 该行有多种存款帐户类型，例如：活期，定期等。 一个人可以在该行开设多个帐户， 在储蓄所办理开户。 每个帐户有帐号 （编号全行唯一） 、 存款人身份证、密码、开户日期、余额。储户可以通过电话、网站挂失存折。 每个帐户要与一个存款帐户类型相对应。 银行要统计每个储蓄所每年的开户情况。 储户可以在该行的任何一个储蓄所办理存款、取款业务。系统要记载 办理的储蓄所、办理人员、日期、金额信息。 银行要统计每个储蓄所的日交易量， 以便确定每日给该储蓄所的送款额、 收款额及确定 对储蓄所的工作量核算。 请设计该数据库的 ER 模型，并将其转换成关系模型。 1、ER 模型 储户 存款单 取款单 业务员 支行 储蓄所隶属 办理 2办理 1 归属 2、实体的属性 支行（支行号，名称，电话，地址，负责人） 8 储蓄所（储蓄所号，支行号，名称，电话，地址，负责人） 业务员（业务人员号，储蓄所号，姓名，性别，民族，生日，身份证号，参加工作日期） 帐户（帐号，姓名，联系电话，身份证号，密码，帐户类型，开户日期，开户支行，挂失否， 余额） 存款单（流水号，帐户，储蓄所，业务员，取款时间，取款金额） 取款单（流水号，帐户，储蓄所，业务员，取款时间，取款金额） 3、联系的属性 4、关系模型 六、数据库设计题（本大题共 20 分。 ） 1、设关系模式 R（职工号，日期，日营业额，部门名，部门经理） ， 该关系用来统计商店每个职工的日营业额， 以及记载职工所在的部门 和经理是谁。如果规定：每个职工每天只有一个营业额；每个职工只在一个部门工作；每个 部门只有一个经理。请回答下列问题： 根据上述规定，写出模式 R 的基本 FD 和候选键。2 分 说明 R 是否为 2NF，并