上海大学数据库原理1研讨答案

1、第1周第四章：SQL体系结构、组成、建库建表,索引自学一、实验课：1 .建立school数据库2 .在school下建立如下数据库表,根据表中数据选取适宜的数据类型及宽度,设置各表的主键及表间外键联系：注意：字段名是对应汉字字段名的汉语拼音第一个字母组合而成学生表S:学号,姓名,性别,出生日期,籍贯,号,院系号；表1:Sxhxmxbcsrqjgsjhmyxh1101李明男1993-03-06上海021102刘晓明男1992-12-08安徽011103张颖女1993-01-05江苏011104刘晶晶女1994-11-06上海011105刘成刚男1991-06-07上海011106李二丽女1993

2、-05-04江苏011107张晓峰男1992-08-16浙江01院系表D:院系号,名称,地址,联系;表2:Dyxhmcdzlxdh01计算机学院上大东校区三号楼02通讯学院上大东校区二号楼03材料学院上大东校区四号楼教师表T:工号,姓名,性别,出生日期,学历,根本工资,院系编号;表3:Tghxmxbcsrqxljbgzyxh0101陈迪茂男1973-03-06副教授3567.00010102马小红女1972-12-08讲师010201张心颖女1960-01-05教授020103吴宝钢男1980-11-06讲师01课程表C:课号,课名,学分,学时,院系号；默认学分4,学时40表4:Ckhkmxf

3、xsyxh08305001离散数学4400108305002数据库原理4500108305003数据结构4500108305004系统结构6600108301001分子物理学4400308302001通信学33002开课表0:学期,课号,工号,上课时间;表5:0xqkhghsksj2021-2021秋季083050010103星期三5-82021-2021冬季083050020101星期三1-42021-2021冬季083050020102星期三1-42021-2021冬季083050020103星期三1-42021-2021冬季083050030102星期五5-82021-2021秋季083

4、050040101星期二1-42021-2021秋季083050010102星期一5-82021-2021冬季083020010201星期一5-8选课表E:学号,学期,课号,工号,平时成绩,测试成绩,总评成绩；成绩范围1-100表6:Exhxqkhghpscjkscjzpcj11012021-2021秋季08305001010360606011022021-2021秋季08305001010387878711022021-2021冬季08305002010182828211022021-2021秋季083050040101nullnullnull11032021-2021秋季083050010

5、10356565611032021-2021冬季08305002010275757511032021-2021冬季08305003010284848411032021-2021秋季083050010102nullnullnull11032021-2021秋季083050040101nullnullnull11042021-2021秋季08305001010374747411042021-2021冬季083020010201nullnullnull11062021-2021秋季08305001010385858511062021-2021冬季08305002010366666611072021-

6、2021秋季08305001010390909011072021-2021冬季08305003010279797911072021-2021秋季083050040101nullnullnull3.在学生表中建立索引idxl:院系号升序,姓名降序在课程表中建立索引idx2:课名二、研讨课：参考答案1 .请说明school数据库中各表的主键和外键.同时说明：在学生表中的yxh字段输入“04或在院系表中删除yxh为“01记录,会有什么结果学生表S：学号,院系号院系表D:院系号教师表T:工号,院系号课程表C:课号,院系号开课表.学期,课号,工号,课号,工号选课表E:学号,学期,课号,工号,学号,学期,

7、课号,工号不能车入04:由于外键值在对应主键中没有出现不能删除：由于外键表中有相应数据或级联删除和院系相关的数据2 .什么是关系型数据库什么是非关系型数据库各举1个典型产品简单介绍他们特点关系数据库,是建立在关系模型根底上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据.现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示.关系模型是由埃德加科德于1970年首先提出的,并配合“科德十二定律.现如今虽然对此模型有一些批评意见,但它还是数据存储的传统标准.标准数据查询语言SQL就是一种基于关系数据库的语言,这种语言执行对关系数据库中数据的检索和操作.关系模型由关系数据结构、关系

8、操作集合、关系完整性约束三局部组成.ORACLESQLServer、Sybase、Informix、mysql等.非关系型的数据库NoSQL,随着互联网网站的兴起,传统的关系数据库在应付网站,特别是超大规模和高并发的SN铁型的纯动态网站已经显得力不从心,暴露了很多难以克服的问题,而非关系型的数据库那么由于其本身的特点得到了非常迅速的开展.CouchDBMonogDB.3 .教师表中定义两个检查约束：1性别只能输入“男和“女checkxb=男'orxb='女2男老师的根本工资必须超过2500元check(xb='男'andjbgz>2500)orxb=

9、9;女')第2周(第四章：投影、选择、多表连接和嵌套,排序自学)一、实验课：1,查询2021年进校年龄大于20岁的男学生的学号与姓名.2 .检索刘晓明不学的课程的课程号.3 .检索马小红老师所授课程的学年,学期,课程号,上课时间.4 .查询计算机学院男生总评成绩及格、教授开设的课程的课程号、课名、开课教师姓名,按开课教师升序,课程号降序排序.5 .检索学号比张颖同学大,年龄比张颖同学小的同学学号、姓名.6 .检索同时选修了“08305001和“08305002的学生学号和姓名.二、研讨课：1,用四种方法表达查询：检索马小红老师所授课程的学期,课程号,上课时间.1) selectxq,k

10、h,sksjfromo,twhere=andxm='马小红'2) selectxq,kh,sksjfromowhereghin(selectghfromtwherexm='马小红)3) selectxq,kh,sksjfromowhereexists(select*fromtwhere=andxm='马小红)4) selectxq,kh,sksjfromowhere'马小红'in(selectxmfromtwhere=2 .检索有学生重修的教师编号和姓名.selectgh,xmfromtwhereghin(selectfromea,ebwhere

11、=and=and>3 .如果O表加一个个开课编号(标识码)会带来哪些影响(优缺点)第3周(第四章：除法、聚合函数、分组、集合操作,外连接自学)一、实验课：1 .验证在1000万个以上记录时在索引和不索引时的查询时间区别.2 .查询每个学生选课情况(包括没有选修课程的学生).selects.*,e.*fromsleftjoineon=3 .检索所有课程都选修的的学生的学号与姓名.4 .检索选修课程包含1106同学所学全部课程的学生学号和姓名.5 .查询每门课程中分数最高的学生学号和学生姓名.6 .查询年龄小于本学院平均年龄,所有课程总评成绩都高于所选课程平均总评成绩的学生学号、姓名和平均总

12、评成绩,按年龄排序.select,avgaspjfromsasx1joineon=whereyear(getdate()-year<(selectavg(year(getdate()-year)fromsasx2where=andnotin(selectfromeasy1where<=(selectavgfromeasy2where=)groupby,year(getdate()-yearorderbyyear(getdate()-year二、研讨课：1 .什么是聚集索引什么是非聚集索引索引的作用是什么请说明上述实验题1的验证过程及所得到的结果聚集索引：在聚集索引中,表中各行的物理

13、顺序与索引键值的逻辑(索引)顺序相同.表只能包含一个聚集索引.聚集索引通常可加快UPDATEDELET限作的速度,由于这两个操作需要读取大量的数据.创立或修改聚集索引可能要花很长时间,由于执行这两个操作时要在磁盘上对表的行进行重组.非聚集索引：非聚集索引具有完全独立于数据行的结构,使用非聚集索引不用将物理数据页中的数据按列排序.非聚集索引中存储了组成非聚集索引的关键字值和行定位器.非聚集索常用于：包含大量非重复值的列,不返回大型结果集的查询,精确匹配的查询经常使用的列.索引的作用是加快查询速度2 .用多种方法表达查询：检索同时选修了“数据库原理和“离散数学的学生学号和姓名.1) selectx

14、h,xmfromswherexhin(selectxhfrome,cwhere=and='数据库原理)intersectselectxh,xmfromswherexhin(selectxhfrome,cwhere=and='离散数学)2) selectxh,xmfromswherexhin(selectfromea,ebwhere=andin(selectkhfromcwherekm数据库原理)andin(selectkhfromcwherekm-离散数学)3 .查询每门课的排名,输出课程号,学号,总评成绩,排名；按课程号升序,课程相同按排名从高到低.(提示：某个同学一门课的排

15、名就是该门课成绩大于等于他的人数)select,count(*)aspmfromEasx,Easywhere<or=and=)and=groupby,orderby1,4-加上学期select,count(*)pmfromeasx,easywhere<or=and=)and=and=groupby,orderby1,2,5第4周(第四章：数据更新、视图、嵌入式SQL局部自学)一、实验课：1 .建立计算机学院总评不及格成绩学生的视图,包括学生学号、姓名、性别、所选课程和成绩.2 .在E表中插入记录,把每个学生没学过的课程都插入到E表中,使得每个学生都选修每门课.insertintoe

16、(xh,xq,kh,gh)select,froms,owhere+notin(selectxh+khfrome)and+in(selectkh+ghfromoasxwheregh<=all(selectghfromoasywhere=)3 .求年龄大于所有女同学年龄的男学生姓名和年龄.4 .在E表中彳改08305001课程的平时成绩,假设成绩小于等于75分时提升5%假设成绩大于75分时提升4%5 .删除没有开课的学院.6 .查询优、良、中、及格、不及格学生人数二、研讨课：1. 补全下面程序段虚线框中的语句,使该程序能够正确地利用给出的数据库,逐个显示年龄高于某个值(通过共享变量maxag

17、e给出)的学生的学号、姓名、性别、院系号,并按用户的当场要求,保持或修改这个学生的院系号(通过共享变量newdno中给出).voidChangeDNO()EXECSQLBEGINDECLARESECTION;intmaxage;charxh7,xm9,sex3,yxh4,newdno4;EXECSQLENDDECLARESECTION;gets(maxage);EXECSQLDECLAREexcursorforselectxh,xm,xb,yxhfromswhere；EXECSQLfetchfromexinto:xh,:xm,:xb,:yxh;IEXECSQLOPENexwhile(1)if!

18、=00000')break;printf("%s,%s,%s,%s,eno,ename,sex,yxh);printf("UPDATEDN(J);scanf("%C,&yn);if(yn=,y,oryn=,丫,)printf("INPUTNEWDNO:");EXECSQLUPDATE;setyxh=:newdnowherecurrentofexscanf("%C,&newdno);IEXECSQLfetchfromexinto:xh,:xm,:xb,:yxh;EXECSQLCLOSEex;2. 1)用多种方法表

19、达查询：检索刘晓明不学的课程的课程号. selectkhfromcwherekhnotin(selectkhfroms,ewhere=andxm='刘晓明) selectkhfromcexceptselectkhfroms,ewhere=andxm='刘晓明 selectfromcleftjoin(selectkhfroms,ewhere=andxm='刘晓明')asxon=whereisnullselectfromejoinson=andxm='刘晓明'rightjoincon=whereisnull2)删除没有开课的学院.deletefrom

20、cwhereyxhnotin(selectyxhfromc,owhere=deletefromtwhereyxhnotin(selectyxhfromc)deletefromswhereyxhnotin(selectyxhfromc)deletefromdwhereyxhnotin(selectyxhfromc)1)视图判断实验课第一题的视图能否更新.3.1)设计一个能更新的视图,要求更新后的数据满足视图定义的范围：“系统结构还未有总评成绩的选课视图.写出如下对视图的更新命令,并判断是否可行,如不可行请说出理由createviewstasselect*fromewherezpcjisnulla

21、ndkhin(selectknfromcwherekm='系统结构')withcheckoption插入数据(1107,2021-2021秋季,08305004,0101,null,null,null)不能,主键重复插入数据(1107,2021-2021冬季,08305002,0102,null,null,null)不能,插入的不是“系统结构将所有学生平时成绩增加10分,但不能超过100分updatestsetpscj=100wherepscj>90updatestsetpscj=pscj+10wherepscj<=90第5周第一、二章：数据治理、数据库技术、数据库

22、体系结构、DBMS自学DBS一、实验课：抽查前四周的实验内容二、研讨课：根据上半学期的学习情况,对学生在学习中的问题,两个班合班上习题课三、作业：数据库中的“3只针对数据库原理11 .数据库开展史上的3个里程碑2 .数据库界3个图灵奖获得者3 .数据治理的3个阶段4 .3种数据模型5 .数据库系统3层体系结构6 .数据抽象过程中的3个世界7 .数据描述时的3层设计8 .数据模型3要素9 .3种数据模式10 .数据库的3次大论战11 .关系数据库的3种完整性约束12 .能唯一标识关系中每一行的属性或属性组,有那3种键13 .实体间的联系有那3种14 .关系的3种更新15 .文件系统的3个缺点第6

23、周(第二、六章：E_R莫型和扩展E-R模型)一、实验课：数据库连接编程(与SQLServer)：第13章实验5-1二、研讨课：1 .根据以下说明设计实体联系图【说明】某单位资料室需要建立一个图书治理系统,初步的需求分析结果如下：(1)资料室有图书治理员假设干名,他们负责已购入图书的编目和借还工作,每名图书管理员的信息包括工号和姓名；(2)读者可在阅览室读书,也可通过图书流通室借还图书,读者信息包括读者ID、姓名、和Email,系统为不同读者生成不同的读者ID;(3)每部书在系统中对应惟一的一条图书在版编目数据(CIP,以下简称书目),书目的根本信息包括ISBN号、书名、作者、出版商、出版年月,

24、以及本资料室拥有该书的册数(以下简称册数),不同书目的ISBN号不相同；(4)资料室对于同一书目的图书可拥有多册(本),图书信息包括图书ID、ISBN号、存放位置、当前状态,每一本书在系统中被赋予惟一的图书ID;(5)一名读者最多只能借阅十本图书,且每本图书最多只能借两个月,读者借书时需由图书治理员登记读者ID、所借图书ID、借阅时间和应还时间,读者还书时图书治理员在对应的借书信息中记录归还时间；(6)当某书目的可借出图书的数量为零时,读者可以对其进行预约登记,即记录读者ID、需要借阅的图书的ISBN号、预约时间.某书目的信息如表1-1所示,与该书目对应的图书信息如表1-2所示.表1-1书目信

25、息书名作者出版商ISBN号出版年月册数经办人?数据结构?严蔚敏吴伟民清华大学出版社ISBN7-302-02368-9401表1-2图书信息图书IDISBN号存放位置状态经办人ISBN7-302-02368-9图书流通室已借出01ISBN7-302-02368-9图书阅览室不外借01ISBN7-302-02368-9图书流通室未借出01ISBN7-302-02368-9图书流通室已预约01【系统的主要业务处理如下】(1)入库治理：图书购进入库时,治理员查询本资料室的书目信息,假设该书的书目尚未建立,那么由治理员编写该书的书目信息并录入系统,然后编写并录入图书信息；否那么,修改该书目的册数,然后编

26、写并录入图书信息,对于进入流通室的书,其初始状态为“未借出,而送入阅览室的书的状态始终为“不外借(2)借书治理：读者借书时,假设有,那么由治理员为该读者办理借书手续,并记录该读者的借书信息,同时将借出图书的状态修改为“已借出(3)预约治理：假设图书流通室没有读者要借的书,那么可为该读者建立预约登记,需要记录读者ID、书的ISBN号、预约时间和预约期限(最长为10天).一旦其他读者归还这种书,就自动通知该预约读者.系统将自动去除超出预约期限的预约记录并修改相关信息.(4)还书治理：读者还书时,那么记录相应借还信息中的“归还时间,对于超期归还者,系统自动计算罚金(具体的计算过程此处省略).系统同时

27、自动查询预约登记表,假设存在其他读者预约该书的记录,那么将该图书的状态修改为“已预约,并将该图书ID写入相应的预约记录中(系统在去除超出预约期限的记录时解除该图书的“已预约状态);否那么,将该图书的状态修改为“未借出.(5)通知处理：对于已到期且未归还的图书,系统通过Email自动通知读者；假设读者预约的书已到,系统那么自动通过Email通知该读者来办理借书手续.(1)n(2)m(3)1(4)n,或m(5)m(6)n其中,(1)、(2)的答案可互换,(5)、(6)的答案可互换.2 .根据以下说明设计实体联系图【说明】某汽车维修站拟开发一套小型汽车维修治理系统,对车辆的维修情况进行治理.1对于新

28、客户及车辆,汽车维修治理系统首先登记客户信息,包括：客户编号、客户名称、客户性质个人、单位、折扣率、联系人、联系等信息；还要记录客户的车辆信息,包括：车牌号、车型、颜色等信息.一个客户至少有一台车.客户及车辆信息如表2-1所示.表2-1客户及车辆信息客户编号GS0051客户名称XX公司客户性质单位折扣率95%联系人杨浩东联系车牌号颜色车型车辆类别*0765白色帕萨特微型车2记录维修车辆的故障信息.包括：维修类型普通、加急、作业分类大、中、小修、结算方式自付、三包、索赔等信息.维修厂的员工分为：维修员和业务员.车辆维修首先委托给业务员.业务员对车辆进行检查和故障分析后,与客户磋商,确定故障现象,

29、生成维修委托书.如表2-2所示.表2-2维修委托书登记日期：2021-07-02车牌号*0765客户编号GS0051维修类型普通作业分类中修结算方式自付进厂时间11:09业务员张小江业务员编号012预计完工时间故障描述车头损坏,水箱漏水2-33维修车间根据维修委托书和车辆的故障现象,在已有的维修工程中选择并确定一个或多个具体维修工程,安排相关的维修工及工时,生成维修派工单.维修派工单如表所示.表2-3维修派工单维修工程编号维修工程工时维修员编号维修员工种012维修车头012机修012维修车头023漆工015水箱焊接补漏006焊工017更换车灯012机修4客户车辆在车间修理完毕后,根据维修工程单

30、价和维修派工单中的工时计算车辆此次维修的总费用,记录在委托书中.3.设计的实体联系图不完整如图3-1所示.某公司拟开发一套小区物业收费治理系统.初步的需求分析结果如下:1业主信息主要包括：业主编号,姓名,房号,房屋面积,工作单位,联系等.房号可唯一标识一条业主信息,且一个房号仅对应一套房屋；一个业主可以有一套或多套的房屋.2部门信息主要包括：部门号,部门名称,部门负责人,部门等；一个员工只能属于一个部门,一个部门只有一位负责人.3员工信息主要包括：员工号,姓名,出生年月,性别,住址,联系,所在部门号,职务和密码等.根据职务不同员工可以有不同的权限,职务为“经理的员工具有更改添加、删除和修改员工

31、表中本部门员工信息的操作权限；职务为“收费的员工只具有收费的操作权限.4收费信息包括：房号,业主编号,收费日期,收费类型,数量,收费金额,员工号等.收费类型包括物业费、卫生费、水费和电费,并按月收取,收费标准如表3-1所示.其中：物业费=房屋面积平方米x每平米单价,卫生费=套房数量套x每套房单价,水费=用水数量吨X每吨水单价,电费=用电数量度X每度电单价.5收费完毕应为业主生成收费单,收费单例如如表3-2所示.表3-2收费单不例表3-1收费标准房号：A1608业主姓名：李斌收费类型单位单价物业费平方米卫生费套水费吨电费度序号收费类型数量金额1物业费2卫生费13水费64电费102合计壹佰玖拾肆元

32、肆角整收费日期：2021-9-2员工号：001【问题】填写图3-1中af处联系的类型注：一方用1表示,多方用m或n或*表示,并补充完整图3-1中的实体、联系和联系的类型.业主部门图2-1补充完整的实体联系图三、作业：画出以下3题白EE-R模型1 .第1周所建数据库2 .假定一个数据库包括如下信息：职工：工号、姓名、所在部门部门：部门名、经理、部门所有职工、销售产品产品：产品名、制造商名、价格、型号制造商：制造商名、地址、生产的产品名、价格3 .假设要为某高校后勤关于班车运行治理信息系统建立一个数据库,对车辆、司机、维修、保险和工作班车运行等信息进行治理.该高校后勤有关班车运行的业务治理规那么如

33、下：该高校有假设干个校区,每个校区有一个车队.每个车队有一个车队长、假设干车辆和司机.车辆和司机分别在不同的保险公司里投保.车辆由假设干汽车维修公司进行维修.每个工作班车包括班次编号、起点、终点和发车时间.每个车队负责该高校假设干不同工作班车的运行.第7周第二、七章：数据模型,E-R模型转关系模型一、实验课：数据库连接编程与SQLServer：第13章实验5-2二、研讨课：1 .设计第6周研讨第1题图书治理系统的主要关系模式,然后给出各关系模式的主键和外键.治理员._,姓名读者读者ID,姓名,Email书目ISBN号,书名,作者,出版商,出版年月,册数,经办人图书图书ID,I-SBN号,存放位

34、置,斗优态,经办人.借还记录读者ID,图书ID,借出时间,应还时间,归还时间外键：读者ID,图书ID预约登记读者ID,ISBN号,预约时间,预约期限外键：读者ID,ISBN号2 .在第6周研讨第3题的根底上完成以下工作：1根据概念模型设计阶段完成的实体联系图,得出如下关系模式不完整：业主1,姓名,房屋面积,工作单位,联系员工2,姓名,出生年月,性别,住址,联系,职务,密码部门3,部门名称,部门权限职务,操作权限收费标准4收费信息5,收费类型,收费金额,员工号2根据图3-1,将逻辑结构设计阶段生成的关系模式中的空15补充完整,然后给出各关系模式的主键和外键.1业主编号,房号主键：房号外键：无2员

35、工号,所在部门号主键：员工号外键：所在部门号(3)部门号,部门负责人主键：部门号外键：部门负责人(4)收费类型,单位,单价主键：收费类型外键：无(5)房号,业主编号,收费日期主键：房号,业主编号,收费日期外键：房号,员工号3.一个三元关系是否可以用三个二元的M:n关系代替,试举例说明.(示意图)例：E1:表小学生；E2表木图书；E3表小书店R12表示学生有哪些书；R23表示书店卖那些书；R13表示学生去过哪些书店但表示不了R学生在那个书店卖了那本书、作业：将第6周3题白EE-R模型转为关系模型第8周(第三章：关系代数,关系演算自学)一、实验课：数据库连接编程(与SQLServer)：第13章实验5-3二、研讨课：1,请用SQL关系代