中南大学数据库课程设计题目答案.docx

中南大学数据库课程设计题目答案.docx

《中南大学数据库课程设计题目答案.docx》由会员分享，可在线阅读，更多相关《中南大学数据库课程设计题目答案.docx（26页珍藏版）》请在冰豆网上搜索。

中南大学数据库课程设计题目答案

中南大学课程设计报告

数据库技术

题目：

数据库技术上机感悟

姓名：

陈晓璐

学号：

4303100426

班级：

会计1002班

日期：

2014年5月7号

Part1：

SQL基础篇

一、数据库（4303100426）的创建与数据录入：

1．建立上述六个表，确定主码及外码约束，并录入数据

（1）专业信息表（minf）的创建与录入

createtableminf

（mnumchar（4）notnull,

majorvarchar（50）,

mintrovarchar（500）,

primarykey（mnum）,

foreignkey（mnum）referencesmtrain（mnum））

问题：

（由于已经用企业管理器创建了minf表，这里使用minf2代替）问题如上图所示无法创建外码

解决方法：

阅读理解第2章关系数据库关于外码的定义：

若一个关系R（外键表）中包含有另一个关系S（主键表）的主码所对应的属性组F（但F不是R的主码），则称F为R的外码。

所以就业信息表minf中没有外码。

修改后如下：

createtableminf

（mnumchar（4）notnull,

majorvarchar（50）,

mintrovarchar（500）,

primarykey（mnum））

（2）学生基本信息表（s）的创建与录入

createtables

（snumchar（10）notnull,

snamechar（10）,

birthddatetime（8）,

sexchar

（2）,

mnum=left（snum,4）varchar（8）,

primarykey（snum））

问题：

datetime不能指定宽度。

mnum的设定语句出错。

解决：

datetime的列宽度不指定，会自动生成。

mnum可以先直接指定数值类型再在企业管理器操作，或者用AS语句

修改后如下：

createtables

（snumchar（10）notnull,

snamechar（10）,

birthddatetime,

sexchar

（2）,

mnumasleft（snum,4）,

primarykey（snum））

（3）课程基本信息表（c）的创建与录入

createtablec

（cnumchar（10）notnull,

cnamevarchar（50）,

primarykey（cnum））

（4）专业培养计划（mtrain）的创建与录入

createtablemtrain

（mnumchar（4）notnull,

cnumchar（10）notnull,

creditnumeric（5）,

ckindchar（10）,

cdatechar（10）,

primarykey（mnum,cnum）,

foreignkey（mnum）referencesminf（mnum）,

foreignkey（cnum）referencesc（cnum））

（5）教师开课信息表（tea）的创建与录入

createtabletea

（cnumchar（10）notnull,

teacherchar（10）notnull,

primarykey（cnum,teacher）,

foreignkey（cnum）referencesc（cnum））

（6）学生选课表（sc）的创建与录入

createtablesc

（snumchar（10）notnull,

cnumchar（10）notnull,

scorenumeric（5）,

teacherchar（10）,

primarykey（snum,cnum）,

foreignkey（snum）referencess（snum）,

foreignkey（cnum）referencesc（cnum））

3.创建学生选课视图sc_sview（学号，姓名，课程号，课程名，教师，成绩，学分，性质，开课学期）。

createviewsc_viewAS

selects.snum学号,s.sname姓名,um课程号,ame课程名,sc.teacher教师,sc.score成绩,mtrain.credit学分,mtrain.ckind性质,mtrain.cdate开课学期

fromsc,s,c,mtrain

wheresc.snum=s.snumandum=umandleft（sc.snum,4）=mtrain.mnum

4.创建“商学院”的学生视图，并由出生日期计算出年龄。

（注：

系统函数year（）取一个日期型数据的年份。

getdate（）取系统日期。

“商学院”有工商、金融等专业。

）

createview商学院（snum,sname,sex,birthd,age）AS

selectsnum学号,sname姓名,year（getdate（））-year（birthd）年龄

froms

wheresnumlike’16%’

5.创建索引：

（1）分别给专业信息表、专业培养计划表各创建一个聚集索引。

专业信息表：

createclusteredindexmtrain#onmtrain（mnum）

专业培养计划表：

createclusteredindexminf#onminf（mnum）

问题：

解决：

在企业管理器表中删除已有索引即可

（2）在学生选课表的学号、课程号字段上创建一个非聚集索引。

（索引按学号升序、同一学号按课程号降序排列。

）

createindexsc#onsc（snumasc,cnumdesc）

（3）在学生基本信息表的学号字段上创建一个非聚集索引

createindexs#ons（snum）

二、数据查询（注：

下面的查询可根据需要先创建视图，然后再对视图查询。

）

1.检索学号为’160512╳╳╳╳’的学生所学必修课程的课程名与任课教师名；（注：

学号自定。

）

selectdistinctcname课程名,teacher任课教师名

fromsc,c,mtrain

wheresnumlike‘160512%’andckind=’必’andum=umandum=um

2.检索至少选了”刘兴”老师所授课程中一门课程的男学生姓名；

selectsname姓名

froms,sc

wheresc.snumandsex=’男’andteacher=’刘兴’

3.检索没有选刘兴老师所授课程的女学生姓名和学号；

selectsname姓名,s.snum学号

froms,sc

wheresc.snum=s.snumandsex=’女’andteacher<>’刘兴’

问题:

snum列名不明确

解决:

由于s表和sc表中都有snum，所以需要指定列名所在的表

4．检索’王丽’同学第四学期所有选修课程的课程号、课程名及成绩；

selectum课程号,cname课程名,score成绩

froms,c,sc,mtrain

wherecdate=’四’andsname=’王’andckind=’选修’andum=umandum=umandsc.snum=s.snum

问题：

语句可以执行，但是结果的数量是0条

解决：

ckind的条件应该是’选’而不是’选修’

修改后能成功返回记录

5．检索工商12级至少选了三门选修课程的学生姓名和学号；（注：

选修指选修课，下同。

）

selectsnum学生姓名,snum学号

froms

wheresnumin

（selectsnumfromscwherecnumin

（selectcnumfrommtrainwhereckind=’选’）andsnumin（selectsnumfromscgroupbysnumhavingcount（cnum）>=3））

6.在表SC中统计开设课程的教师人数；

selectcount（distinctteacher）教师人数

fromsc

7.统计工商12级男生、女生各年龄的人数；

selectsex性别,year（getdate（））-year（birthd）年龄,count（year（getdate（））-year（birthd））人数

froms

wheresnumlike’160112%’

groupbysex,year（getdate（））-year（birthd）

8.统计每个学生选修课程（已有成绩）的门数、必修学分、选修学分及平均成绩，按平均成绩降序排列；

selectsc.snum学号,sname姓名,count（um）门数,sum（credit）学分,ckind性质,AVG（score）平均成绩

fromsc,mtrain,s

wherescoreisnotnullandum=umandleft（s.snum,4）=mtrain.mnumandsc.snum=s.snum

groupbysc.snum,sname,ckind

orderbyAVG（score）desc

9.统计工商11级学生必修、选修课程（已有成绩）的总门数、总学分及平均成绩。

selectcount（um）总门数,sum（credit）总学分,AVG（score）平均成绩,s.snum学号,sname姓名

fromsc,mtrain,s

wherescoreisnotnullands.snumlike‘160111%’andsc.snum=s.snumandleft（snum,4）=mtrain.mnum

groupbys.snum,sname

10.分别统计各个专业必修、选修课程的门数，查询结果按专业排序；

selectmajor专业,ckind课程性质,count（ckind）人数

fromminf,mtrain

whereminf.mnum=mtrain.mnum

groupbymajor,ckind

orderbymajor

11.查询工商12级第三学期及以前所选课还没有成绩的学生学号、课程号、课程名及任课老师；

selectsnum学号,um课程号,cname课程名,teacher任课老师

fromc,sc,mtrain

wherescoreisnullandum=um

andum=umandleft（sc.snum,4）=mtrain.mnum

andsnumlike'160112%'

andcdatein（'一','二',’三’）

12.查询工商12级选修课学分大于15分的学生学号及姓名

selectsnum学号,sname姓名,sum（credit）选修学分

frommtrain,s

whereleft（snum,6）=160112andleft（snum,4）=mtrain.mnum

groupbysnum,sname

havingsum（credit）>15

13.查询工商专业第五学期所有的选修课程；

selectcname课程名fromc,mtrain

wherecdate='五'andmnum='1601'andum=um

14.查询工商11级学生选修课学分还没有达到25分的学生学号、姓名、专业及年级；

selects.snum学号,sname姓名,major专业,substring（snum,5,2）年级

froms,minf,mtrain

wheres.mnum=minf.mnumands.mnum=mtrain.mnumands.snumlike‘160111%’

ands.snumin（selects.snum,sum（credit）fromsc,mtrain

whereum=umandsum（credit）<25

andckind=’选’groupbys.snumhavingsum（credit）<25）

15.按学期、按专业统计‘高强’老师所教授课程的学生的平均成绩，并把该查询该结果以表的形式存储。

selectAVG（score）平均成绩,major专业,cdate学期into表15

fromsc,mtrain,minf

whereteacher=’高强’andleft（snum,4）=mtrain,mnumandum=um

andminf,mnum=mtrain.mnum

groupbymajor,cdate

16.查询工商12级第三学期各门课程不及格的学生，并用视图的形式存储不及格名单。

createview不及格名单AS

selects.snum学号,sname姓名,score成绩

fromsc,s,mtrain

wherecdate=’三’ands.snumlike‘160112%’andleft（s.snum,4）=mtrain.mnum

andscore<60andum=um

17.检索未选修“李中华”老师所授全部课程的学生学号；

selectdistinctsnum学号fromsc

wherecnumnotin

（selectdistinctcnumfromscwhereteacher=’李中华’）

问题：

出现选修李中华部分课程的学生学号的多余项

解决：

将cnum改成snum,只要是选了李中华课的学号均不要

修正后如下：

selectdistinctsnum学号fromsc

wheresnumnotin

（selectdistinctsnumfromscwhereteacher=’李中华’）

18.统计商学院学生每门课程的平均成绩，要求列出课程名、平均成绩，并按平均成绩从高到低排列。

selectcname课程名,AVG（score）平均成绩

fromsc,c

whereum=um

groupbycname

orderbyAVG（score）desc

19.统计每个老师所授每门课程的平均成绩，要求列出课程名、平均成绩，并按平均成绩从高到低排序。

selectcname课程名,teacher教师,AVG（score）平均成绩

fromsc,c

whereum=um

groupbyteacher,cname

orderbyAVG（score）desc

20.统计商学院12级学生第一学年的平均成绩及排名，检索结果送往另一个表存储，作为分专业的依据。

selectsnum学号,AVG（score）平均成绩into分专业依据

fromsc

wheresnumlike‘160112%’andcnumin

（selectcnumfrommtrainwherecdate=’一’orcdate=’二’）

groupbysnum

orderbyAVG（score）

三、数据备份

用SQL　SERVER　2000提供的备份功能对原始数据进行完全备份，并在做了第四部分数据更新后恢复数据库到更新前的状态。

（备份方法见附录）

四、数据更新

1.在SC中删除选修课尚无成绩的选课元组；

deletefromsc

wherescoreisnullandcnumin

（selectcnumfrommtrainwhereckind=’选’）

2.将选修’刘兴’老师课程的女同学选课元组全部删除；

deletefromsc

whereteacher=’刘兴’

andsnumin

（selectsnumfromswheresex=’女’）

andcnumin

（selectcnumfrommtrainwhereckind=’选’）

3.将工商12级学生’高等数学’课不及格的成绩全改为60分；

updatescsetscore=’60’

wheresnumlike‘160112%’andcnumin

（selectcnumfromcwherecname=’高等数学’andscore<60）

4.将工商12级学生“数据库技术”课程低于该课平均成绩的女同学成绩提高5％；

updatescscore=score*1.05

wheresnumin

（selectsnumfromswheresex=’女’andsnumlike‘160112%’

andscore<

（selectAVG（score）fromscwherecnumin

（selectcnumfromcwherecname=’数据库技术’）

groupbycnum）

5.在工商专业、电子商务专业培养计划表中增加一门“网络营销”必修课，计划在第五学期开设；

insertintoc（‘cnum’,’cname’）

values（‘c003’,’网络营销’）

insertintomtrain（‘mnum,’ckind’,’cdate’）

values（‘1601’,’必’,’五’）

insertintomtrain（‘mnum’,’ckind’,’cdate’）

values（‘1607’,’必’,’五’）

注意点：

insertinto的into可以省略；如果只输入部分属性值，则表后需按对应顺序写上列名。

五、数据库安全性管理（先阅读教材9.8节的内容，再做此题！

）

1.以另外的windows用户身份登录windows（机房合法的WIN用户见附录2），检查是否具有连接SQL　Server数据库服务器的权限。

（注：

数据库服务器“安全性管理”文件夹中必须有相应的登录帐户才有连接数据库服务器的权限。

）

答：

注销student用户，用poweruser用户进入，不能完成数据库的注册，说明poweruser用户没有连接SQLSERVER数据库服务器的权限。

2.登录帐户只有在成为数据库用户后才能访问数据库。

（注：

做此步需切换windows用户身份）

先在“数据库服务器”　→“安全性”→“登录”创建一个与数据库服务器连接的合法登录帐户，给帐户名和密码。

（注意登录验证：

选择SQL　SERVER身份验证）。

答：

我创建了Cc的登陆账户，并设置密码。

在设置systemadministrator时显示没有访问权限。

查找原因发现没有在student用户名下对Cc进行授权。

在Cc的登陆账号下新建了Cc数据库用户。

3.以另外的windows用户身份登录windows，再检查刚才创建的登录帐户是否有连接数据库服务器的权限。

答：

在student账户下，对已注册的Cc用户进行授权，此时Cc有连接数据库服务器的权限。

4.登录帐户只有成为数据库用户后才能访问数据库。

在自己的数据库中选择“用户”，创建合法用户，登录名用已创建的SQL服务器连接帐户，并输入用户名，再实现下面的操作。

授权后再验证是否只具有相应的权限。

（1）把查询学生表的“学号，姓名，性别，专业”的权力授予╳╳用户。

（注：

╳╳为创建的用户。

）

grantselect（snum,sname,sex,major）

ons,minftoCcwithgrantoption

（2）把查询课程表、教师开课表、专业培养计划表的权力授予╳╳用户。

grantselectonc,tea,mtraintoCc

withgrantoption

（3）授予╳╳用户录入选课记录的权力。

grantinsertonsctoCc

（4）收回╳╳用户录入选课记录的权力

revokeinsertonsctoCc

六、完整性规则练习

1.给学生表中的性别给定默认值为“男”，学生所在系默认为“商学院”。

定义后再给学生表中插入几条记录，观察列值的变化。

altertables

adddefault‘男’forsex

altertables

adddefault‘16%’formnum

变化：

不录入性别和所在系的值，这些属性值会显示默认值“男”和“商学院”

2.给学生表中的性别创建只能为“男”或“女”的check约束。

定义后再给学生表中插入记录，观察列值的限制。

altertables

addconstraintchk_s

check（sex=’男’orsex=’女’）

限制:

给学生表中的性别输入“男”和“女”之外的值将显示不合法，只能输入“男”或者“女”

3.给学生选课表中的成绩创建一个check约束，该约束要求插入到成绩列中值范围在0－100分之间。

给学生选课表录入成绩时，观察score列的数据限制。

altertablesc

addconstraintchk_s

check（scorebetween0and100）

限制：

成绩只能在0和100之间，不能超出这个范围

Part2：

设计篇：

宾馆信息管理系统的设计

传统的宾馆管理往往令管理者花大量的时间来处理顾客投诉、错误查询、繁琐的登记和结账手续、旅客费用计算等日常事务，空余客房资料不能及时提供也会影响出租率。

另外随着旅游业的发展，宾馆、餐饮、娱乐行业日趋发达，引入全方位的计算机服务和管理是宾馆现代形象的一个重要标志。

一般大型宾馆管理系统包括：

客户预定系统、前台接待系统、前台收银系统、客房系统、电话系统和自动计费等等，系统非常庞大。

本设计是针对一个小型宾馆进行数据库设计，该数据库系统要存储和处理的信息有：

（１）有关客房标准的信息，含客房标准名称、房间面积、床位数量、价格及是否有空调、电话、网络、电视和卫生间等信息，为了便于管理，每一种客房标准有唯一的标准标号。

（２）客房基本信息，含客房编号、客房类型（即标准）、客房位置、客房单价、客房状态及备注信息等。

（３）订房信息，含顾客的身份证号，顾客姓名，顾客的说明信息（如是否是会员，可作备注栏）、入住日期、客房号等。

（４）结算信