中南大学商学院数据库实践报告.docx
《中南大学商学院数据库实践报告.docx》由会员分享，可在线阅读，更多相关《中南大学商学院数据库实践报告.docx（16页珍藏版）》请在冰豆网上搜索。
中南大学商学院数据库实践报告
数据库实践报告
-
姓名：
学号：
班级：
指导老师：
简惠云
Part1
1、建表
1、专业信息表
createtableminf
（mnumvarchar（6）notnullunique，
majorvarchar（6），
mintrovarchar（6），
primarykey（mnum））
2、学生基本信息表
createtables
（snumvarchar（6）notnullunique,
snamevarchar（6）,
birthdsmalldatetime,
sexvarchar（6）,
mnumvarchar（6）
primarykey（snum））
3、课程基本信息表
createtablec
（cnumvarchar（6）notnullunique,
cnamevarchar（6）
primarykey（cnum））
4、专业培养计划
createtablemtrain
（mnumvarchar（6）notnullunique,
cnumvarchar（6）notnullunique,
ccreditnumeric（6,2）,
ckindvarchar（6），
ckindvarchar（6），
primarykey（mnum，cnum）
foreignkey（mnum）referencesminf（mnum）,
foreignkey（cnum）referencesc（cnum））
5、教师开课信息表
createtabletea
（cnumvarchar（6）notnullunique,
teachervarchar（6）,
primarykey（cnum）
foreignkey（cnum）referencesc（cnum））
6、学生选课表
createtablesc
（snumvarchar（6）notnulunique,
cnumvarchar（6）
teachervarchar（6）,
scorevarchar（6）,
primarykey（snum）
foreignkey（cnum）referencesc（cnum））
二、建视图
1、创建学生选课试图s-sview（学号，姓名，课程号，教师，成绩，学分，性质，开课学期）
createviewsc_sviewasselect
s.snum,s.sname,um,sc.teacher,sc.score,ccredit,ckind,cdatefroms,c.mtrain,sc
whereum=umands.snum=sc.snumandum=um
2、商学院学生视图，并由出生日期计算年龄
createview商学院asselectsnum,sname,year（getdate（））-year（birth）asage,majorfromminf,swhereminf.mnum=s.mnum
三、创建索引
1、分别给专业信息表，专业培养计划表各创建一个聚集索引
createclusteredindexaonminf（mnum）
createclusteredindexbonmtrain（mnum）
2、在学生选课的学号，课程号字段上创建一个非聚集索引
createindexconsc（snum,cnumdesc）
3、在学生基本信息表的学号字段上创建一个非聚集索引
createindexdons（snum）
四、数据查询
1、检索学号为‘1605051111‘的学生所学必修课程的课程名与任课教师名
selectum,teacherfroms,mtrain,scwheres.snum=sc.snumandum=umands.snum='1605051111'andckind='必修'
2、检索至少选了‘刘兴‘老师所授课程中一门课程的男学生姓名
selectsnamefroms,scwheres.snum=sc.snumandsex='男'andteacher='刘兴'
3、检索没有选‘刘兴‘老师所授课程的女学生是姓名和学号
selectsname,s.snumfroms,scwheres.snum=sc.snumandsex='女'ands.snumnotin（selectsnumfromscwhereteacher='刘兴'）
4、检索‘王丽‘同学第四学期所有选修课程的课程号，课程名和成绩
selectum,cname,scorefroms,mtrain,sc,cwhereum=umands.snum=sc.snumandum=umandsname='王丽'andcdate='第四学期'
5、检索工商07级至少选了三门选修课程的学生姓名和学号
selects.snum,s.snamefroms,scwheres.snumin（selectsnumfromscgroupbysnumhaving（count（cnum）>=3）
6、检索工商07级全部学生都选修的课程的课程号和课程名
selectsname,snumfroms,mtrainwheres.mnum=mtrain.mnumandsnumlike'160507?
'andckind='选修'groupbysname,snumhavingcount（cnum）>=3
7、在表sc中统计开设课程的教师人数
selectdistinctcount（teacher）教师人数fromsc
8、统计工商07级男生，女生各年龄的人数
selectcount（s.snum）人数,year（getdate（））-year（birth）年龄fromswheresnumlike‘160507%’groupbysex,year（getdate（））-year（birth）
9、统计每个学生选修课程（已有成绩）的门数，必修学分，选修学分级平均娼妓，按平均成绩降序排列
selects.snum,count（um）选修课程门数,sum（b.ccredit）必修学分,sum（a.ccredit）选修学分,avg（score）平均成绩fromsc,s,mtrainasa,mtrainasb,mtrainwheres.snum=sc.snumandum=umanda.mnum=b.mnumandscoreisnotnullanda.ckind='选修'andb.ckind='必修'groupbys.snumorderbyavg（score）desc
10、统计工商05级学生必修，选修课程（已有成绩）的总门数，总学分及平均成绩
selects.snum,count（um）选修课程门数,count（um）必修课程门数,sum（a.ccredit）+sum（b.ccredit）总学分,avg（score）平均成绩fromsc,s,mtrainasa,mtrainasb,mtrainwheres.snum=sc.snumandum=umanda.mnum=b.mnumandscoreisnotnullanda.ckind='选修'andb.ckind='必修'ands.snumlike'160505?
'groupbys.snum
11、分别统计各个专业必修，选修课程的门数，查询结果按必修门数降序，选修门数降序
selecta.mnum,count（um）选修课程门数,count（um）必修课程门数frommtrainasa,mtrainasb,mtrainwherea.mnum=b.mnumgroupbya.mnumorderbycount（um）desc,count（um）
12、查询工商07级第四学期级以前所选课还没有成绩的学生学号，
程号，课程名及任课教师
selectsc.snum,um,cname,tea.teacherfromsc,s,tea,mtrain,cwheresc.snum=s.snumandum=umandum=umandsc.snumlike‘160507%’andscoreisnull
13、查询工商07级选修课学分大于15分的学生学号及姓名
selectsc.snum,s.snamefromsc,swheresc.snumlike’160507%’andumin（selectcnumfrommtraingroupbycnumhavingsum（ccredit）>=15）andcnumin（selectcnumfrommtrainwhereckind=’选修’）
14、查询工商专业第五学期所有的选修课程
selectumfromsc,mtrainwhereum=umandcdate='第五学期'andsnumlike'1605?
'
15、查询工商05级学生选修课学分还没有达到25分的学生学号，姓名，专业及年级
selectdistinctsc.snum,s.sname,minf.majorfromsc,s,minfwheres.snum=sc.snumands.mnum=minf.mnumandsc.snumlike’160505%’andumin（selectcnumfrommtraingroupbycnumhavingsum（ccredit）<=25）andcnumin（selectcnumfrommtrainwhereckind=’选修’）
16、按学期，按专业统计‘高强‘老师所授课程的学生的平均成绩
selectmnum,cdate,avg（score）平均成绩frommtrain,scwhereum=umandteacher='高强'groupbymnum,cdate
17、查询工商07级第四学期各门课程不及格的学生，并用视图是形式存储不及格名单
createviewv07_bjgasselectsnumfromscwheresnumlike‘160507%’andscore<=60
18、查询未选修‘李中华‘老师所受全部课程的学生学号
selectsnumfromscwheresnumnotin（selectsnumfromscwhereteacher='李中华'）
19、统计商学院学生每门课程是平均成绩，要求列出课程名，平均成绩，并按平均成绩从高到低排列
selectcname,avg（score）平均成绩fromc,scwhereum=umgroupbyum,cnameorderbyavg（score）desc
20、统计商学院09级学生第一学年的平均成绩级排名，检索结果送往另一个表存储，作为分专业的依据
selectavg（score）平均成绩into第一学年学生成绩fromsc,swheres.snum=sc.snumgroupbys.snumorderbyavg（score）
五、数据更新
1、在sc中删除选修课尚无成绩的选课元组
deletefromscwherescoreisnullandsnumin（selectsnumfromsc,mtrainwhereum=umandckind='选修'）
2、将选修‘刘兴‘老师课程的女同学选修元组全部删除
deletefromscwheresnumin（selects.snumfromsc,swheresc.snum=s.snumandsex='女'andteacher='刘兴'）
3、将工商07级学生‘高等数学‘课不及格的成绩全改为60分
updatescsetscore=60wherecnumin（selectumfromsc,cwhereum=umandcname='高等数学'andsnumlike'160507?
'）
4、将工商07级学生‘数据库技术‘课程低于该课程平均成绩的女同学成绩提高5%
updatescsetscore=score*1.05wheresnumlike’160507%’andsnumin（selectsnumfromswheresex=’女’）andscorein（selectfromscgroupbyscorehavingscore<=avg（score））andcnum=（selectcnumfromcwherecname=’数据库技术’）
5、在工商专业，电子商务专业培养计划表中增加一门‘网络营销‘必修课，计划在第五学期开设
insertmtrainvalues（‘1601’,’c012’,’2.5’,’第五学期’）
6、遇到的问题及解决办法
1、问题：
查询有时得不到想要的结果
解决：
多录入几组数据
2、问题：
查询结果元组比正常的多
解决：
进行等值连接
3、问题：
必修选修在同一表中不好分开统计
解决：
进行自身连接
Part2
一、E-R图
二、逻辑结构
字段代号
字段含义
数据类型
数据长度
是否主属性（√）
是否为空（√为空）
备注（是否外码√为是）
kfbh
客房编号
varchar
6
√
kfmc
客房名称
varchar
6
√
fjmj
房间面积
numeric,
（6,2）
√
cwsl,
床位数量
varchar
6
√
jg
价格
varchar
6
√
dh
电话
varchar
6
√
字段代号
字段含义
数据类型
数据长度
是否主属性（√）
是否为空（√为空）
备注（是否外码√为是）
kfbh
客房编号
varchar
6
√
kflx
客房类型
varchar
6
√
kfwz
客房位置
varchar
6
√
kfdj
客房单价
varchar
6
kfzt
客房状态
varchar
6
√
字段代号
字段含义
数据类型
数据长度
是否主属性（√）
是否为空（√为空）
备注（是否外码√为是）
sfzh
身份证号
varchar
6
√
gkxx
顾客姓名
varchar
6
√
gksmxx
顾客说明信息
varchar
6
√
rzri
入住日期
smalldatetime
√
kfbh
客房编号
varchar
6
字段代号
字段含义
数据类型
数据长度
是否主属性（√）
是否为空（√为空）
备注（是否外码√为是）
sfzh
身份证号
varchar
6
√
jsri
结算日期
smalldatetime
√
zkl
折扣率
numeric
（6,2）
√
jsje
结算金额
numeric
（6,2）
√
三、宾馆日常操作
1、客房标准信息表
createtablekfbzb
（kfbhvarchar（6）notnullunique,
kfmcvarchar（6）,
fjmjnumeric（6,2）,
cwslvarchar（6）,
jgvarchar（6）,
dhvarchar（6）,
primarykey（kfbh））
2、客房基本信息表
createtablekfjbxx
（kfbhvarchar（6）notnullunique,
kflxvarchar（6）,
kfwzvarchar（6）,
kfdjvarchar（6）,
kfztvarchar（6）,
primarykey（kfbh））
3、订房信息表
createtabledfxx
（sfzhvarchar（6）notnullunique,
gkxxvarchar（6）,
gksmxxvarchar（6）,
rzrismalldatetime,
kfbhvarchar（6）,
primarykey（sfzh））
4、结算信息表
createtablejsxx
（sfzhvarchar（6）notnullunique,
jsrismalldatetime，
zklnumeric（6,2）,
jsjenumeric（6,2）,
primarykey（sfzh））
1、一一，身份证号123456789，于2012年6月29日入住宾馆，客房号为1234
insertintodfxx（sfzh,gkxx,gksmxx,rzri,kfbh）
values（'123456789','一一','无','2012/6/29','1234'）
2、微微，身份证号987654321，于2012-6-29入住宾馆，客房号位1243
insertintodfxx（sfzh,gkxx,gksmxx,rzri,kfbh）
values（'987654321','微微','无','2012/6/29','1243'）
3、天天，身份证号123456798，于2012-6-29入住宾馆，客房号为2134
insertintodfxx（sfzh,gkxx,gksmxx,rzri,kfbh）
values（'123456798','天天','无','2012/6/29','2134'）
4、慢慢，身份证号123456987，于2012-6-29入住宾馆，客房号为4567
insertintodfxx（sfzh,gkxx,gksmxx,rzri,kfbh）
values（'123456987','慢慢,'无','2012/6/29','4567'）
5、安安，身份证号987654123，2012-6-29入住宾馆，客房号为9876
insertintodfxx（sfzh,gkxx,gksmxx,rzri,kfbh）
values（'987654123','安安','无','2012/6/29','9876'）
6、将安安的客房编号改为6789
updatedfxxsetkfbh=6789wheresfzh=987654123
7、将一一的客房编号改为4567
updatedfxxsetkfbh=4567wheresfzh=123456789
8、将天天的客房号改为1357
updatedfxxsetkfbh=1357wheresfzh=123456798
9、查找住在一楼的顾客的姓名
selectgkxxfromdfxxwherekfbhlike‘1?
10、查找2012-6-29入住的顾客姓名
selectgkxxfromdfxxwhererzri=’2012-6-29’
4、对SQLSERVER2000安全管理的认识
数据库必须具有坚固的安全系统，才能控制可以执行的活动以及可以查看和修改的信息。
无论用户如何获得对数据库的访问权限，坚固的安全系统都可确保对数据进行保护。
SQLServer的安全机制一般主要包括三个方面:
1、服务器级别的安全机制:
这个级别的安全性主要通过登录帐户进行控制,要想访问一个数据库服务器,必须拥有一个登录帐户。
2、数据库级别的安全机制:
这个级别的安全性主要通过用户帐户进行控制,要想访问一个数据库，必须拥有该数据库的一个用户账户身份。
3、数据对象级别的安全机制:
这个级别的安全性通过设置数据对象的问提权限进行控制。
Part3
1、保证某门课已有人选修，则专业培养计划表中该科不能删除，并给出信息‘由学生正在选秀该课，不能删除！
’
use
go
ifexists（selectnamefromsysbjects
wherename=’t_dw_d’andtype=’tr’）
droptriggert_dw_d
onmtrain
fordelete
as
declare@dwnovarchar（16）,@dwtempvarchar（16）
select@dwno=cnum
fromdeleted
select@dwtemp=cnum
fromsc
where（havingcount（snum）>=1）and（cnum=@dwno）
if@dwno=@dwtemp
print’有学生正在选修该科，不能删除！
’
else
print’已删除！
’
go
2、当某们选修课人数达到10人时，则不允许再有人选修该课，并给出信息‘该门课选课人数已满，请另选其他课程！
’
use
ifexists（selectnamefromsysobjects
wherename=’t_overarr’andtype=’tr’）
droptriggert_over
go
createtriggert_fcmc
onsc
afterupdate
as
declare@qnovarchar（8）,@snovarchar（8）
select@qno=zdrs,@sno=sjrs
frommtrain
wherelkh=
升级会员 