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

1、中南大学数据库课程设计题目答案中南大学课程设计报告数据库技术题目：数据库技术上机感悟姓名：陈晓璐学号：4303100426班级：会计1002班 日期：2014年5月7号Part 1：SQL基础篇一、 数据库（4303100426）的创建与数据录入：1 建立上述六个表，确定主码及外码约束，并录入数据（1） 专业信息表（minf）的创建与录入create table minf(mnum char(4) not null,major varchar(50),mintro varchar(500),primary key(mnum),foreign key(mnum) references mtrai

2、n(mnum)问题：（由于已经用企业管理器创建了minf表，这里使用minf2代替）问题如上图所示无法创建外码解决方法：阅读理解第2章关系数据库关于外码的定义：若一个关系R（外键表）中包含有另一个关系S（主键表）的主码所对应的属性组F（但F不是R的主码），则称F为R的外码。所以就业信息表minf中没有外码。修改后如下：create table minf(mnum char(4) not null,major varchar(50),mintro varchar(500),primary key(mnum)（2） 学生基本信息表（s）的创建与录入create table s(snum char(

3、10) not null, sname char(10), birthd datetime(8), sex char(2), mnum=left(snum,4) varchar(8), primary key(snum)问题：datetime不能指定宽度。mnum的设定语句出错。解决：datetime的列宽度不指定，会自动生成。mnum可以先直接指定数值类型再在企业管理器操作，或者用AS语句修改后如下：create table s(snum char(10) not null,sname char(10), birthd datetime, sex char(2),mnum as left(s

4、num,4),primary key(snum)（3） 课程基本信息表（c）的创建与录入create table c(cnum char(10) not null,cname varchar(50),primary key(cnum)（4） 专业培养计划（mtrain）的创建与录入create table mtrain(mnum char(4) not null,cnum char(10) not null,credit numeric(5),ckind char(10),cdate char(10),primary key(mnum,cnum),foreign key(mnum) refer

5、ences minf(mnum),foreign key(cnum) references c(cnum)（5） 教师开课信息表(tea)的创建与录入create table tea(cnum char(10) not null,teacher char(10) not null,primary key(cnum,teacher),foreign key(cnum) references c(cnum)（6） 学生选课表(sc)的创建与录入create table sc(snum char(10) not null,cnum char(10) not null,score numeric(5)

6、,teacher char(10),primary key(snum,cnum),foreign key(snum) references s(snum),foreign key(cnum) references c(cnum)3.创建学生选课视图sc_sview（学号，姓名，课程号，课程名，教师，成绩，学分，性质，开课学期）。create view sc_view ASselect s.snum 学号,s.sname 姓名,um 课程号,ame 课程名,sc.teacher 教师,sc.score 成绩,mtrain.credit 学分,mtrain.ckind 性质,mtrain.cdat

7、e 开课学期from sc,s,c,mtrain where sc.snum=s.snum and um=um and left(sc.snum,4)=mtrain.mnum4.创建“商学院”的学生视图，并由出生日期计算出年龄。（注：系统函数year()取一个日期型数据的年份。getdate()取系统日期。“商学院”有工商、金融等专业。）create view 商学院(snum,sname,sex,birthd,age) ASselect snum 学号,sname 姓名, year(getdate()-year(birthd) 年龄from swhere snum like 16%5.创建索

8、引：(1)分别给专业信息表、专业培养计划表各创建一个聚集索引。专业信息表：create clustered index mtrain# on mtrain(mnum)专业培养计划表：create clustered index minf# on minf(mnum)问题：解决：在企业管理器表中删除已有索引即可(2)在学生选课表的学号、课程号字段上创建一个非聚集索引。（索引按学号升序、同一学号按课程号降序排列。）create index sc# on sc(snum asc,cnum desc)(3)在学生基本信息表的学号字段上创建一个非聚集索引create index s# on s(snum

9、)二、 数据查询（注：下面的查询可根据需要先创建视图，然后再对视图查询。）1. 检索学号为160512的学生所学必修课程的课程名与任课教师名；（注：学号自定。）select distinct cname 课程名,teacher 任课教师名 from sc,c,mtrainwhere snum like 160512% and ckind=必and um=um and um=um2. 检索至少选了”刘兴”老师所授课程中一门课程的男学生姓名；select sname 姓名 from s,sc where sc.snum and sex=男 and teacher=刘兴3. 检索没有选刘兴老师所授课

10、程的女学生姓名和学号；select sname 姓名,s.snum 学号 from s,sc where sc.snum=s.snum and sex=女 and teacher刘兴问题:snum列名不明确解决:由于s表和sc表中都有snum，所以需要指定列名所在的表4检索王丽同学第四学期所有选修课程的课程号、课程名及成绩；select um 课程号,cname 课程名,score 成绩 from s,c,sc,mtrainwhere cdate=四 and sname=王 and ckind=选修 and um=um and um=um and sc.snum=s.snum 问题：语句可以执

11、行，但是结果的数量是0条解决：ckind 的条件应该是选而不是选修 修改后能成功返回记录5检索工商12级至少选了三门选修课程的学生姓名和学号；（注：选修指选修课，下同。）select snum 学生姓名, snum 学号from swhere snum in (select snum from sc where cnum in (select cnum from mtrain where ckind=选) and snum in (select snum from sc group by snum having count(cnum)=3)6. 在表SC中统计开设课程的教师人数；select

12、count(distinct teacher) 教师人数 from sc7.统计工商12级男生、女生各年龄的人数；select sex 性别,year(getdate()-year(birthd)年龄,count(year(getdate()-year(birthd)人数 from swhere snum like160112% group by sex,year(getdate()-year(birthd)8.统计每个学生选修课程（已有成绩）的门数、必修学分、选修学分及平均成绩，按平均成绩降序排列；select sc.snum 学号, sname 姓名,count(um) 门数, sum(c

13、redit) 学分,ckind 性质, AVG(score) 平均成绩from sc,mtrain,swhere score is not null and um=um and left(s.snum,4)=mtrain.mnum and sc.snum=s.snumgroup by sc.snum,sname, ckindorder by AVG(score) desc9.统计工商11级学生必修、选修课程（已有成绩）的总门数、总学分及平均成绩。select count(um) 总门数, sum(credit) 总学分,AVG(score) 平均成绩,s.snum 学号,sname 姓名fro

14、m sc,mtrain,swhere score is not null and s.snum like 160111% and sc.snum=s.snum and left(snum,4)=mtrain.mnumgroup by s.snum, sname10.分别统计各个专业必修、选修课程的门数，查询结果按专业排序；select major 专业,ckind 课程性质,count(ckind)人数from minf,mtrainwhere minf.mnum=mtrain.mnum group by major,ckindorder by major11. 查询工商12级第三学期及以前所

15、选课还没有成绩的学生学号、课程号、课程名及任课老师；select snum 学号,um 课程号,cname 课程名,teacher 任课老师from c,sc,mtrainwhere score is null and um=um and um=um and left(sc.snum,4)=mtrain.mnum and snum like160112%and cdate in (一,二,三)12. 查询工商12级选修课学分大于15分的学生学号及姓名select snum 学号,sname 姓名,sum(credit) 选修学分from mtrain,swhere left(snum,6)=1

16、60112 and left(snum,4)=mtrain.mnumgroup by snum,snamehaving sum(credit)1513.查询工商专业第五学期所有的选修课程；select cname 课程名 from c,mtrain where cdate=五 and mnum=1601 and um=um14.查询工商11级学生选修课学分还没有达到25分的学生学号、姓名、专业及年级；select s.snum 学号,sname 姓名,major 专业, substring(snum,5,2) 年级from s,minf,mtrainwhere s.mnum=minf.mnum

17、 and s.mnum=mtrain.mnum and s.snum like 160111%and s.snum in (select s.snum,sum(credit) from sc,mtrainwhere um=um and sum(credit)25andckind=选 group by s.snum having sum(credit)25) 15. 按学期、按专业统计高强老师所教授课程的学生的平均成绩，并把该查询该结果以表的形式存储。select AVG(score) 平均成绩,major 专业, cdate 学期 into 表15from sc,mtrain,minfwher

18、e teacher=高强 and left(snum,4)=mtrain,mnum and um=umand minf,mnum=mtrain.mnumgroup by major,cdate16. 查询工商12级第三学期各门课程不及格的学生，并用视图的形式存储不及格名单。create view 不及格名单 ASselect s.snum 学号, sname 姓名,score 成绩from sc,s,mtrainwhere cdate=三 and s.snum like 160112% and left(s.snum,4)=mtrain.mnumand score60 and um=um17.

19、 检索未选修“李中华”老师所授全部课程的学生学号；select distinct snum 学号 from sc where cnum not in (select distinct cnum from sc where teacher =李中华)问题：出现选修李中华部分课程的学生学号的多余项解决：将cnum 改成snum,只要是选了李中华课的学号均不要修正后如下：select distinct snum 学号 from sc where snum not in (select distinct snum from sc where teacher =李中华)18. 统计商学院学生每门课程的平

20、均成绩，要求列出课程名、平均成绩，并按平均成绩从高到低排列。select cname 课程名, AVG(score) 平均成绩from sc,cwhere um=umgroup by cnameorder by AVG(score) desc19. 统计每个老师所授每门课程的平均成绩，要求列出课程名、平均成绩，并按平均成绩从高到低排序。select cname 课程名,teacher 教师, AVG(score) 平均成绩from sc,cwhere um=um group by teacher,cnameorder by AVG(score) desc20. 统计商学院12级学生第一学年的平

21、均成绩及排名，检索结果送往另一个表存储，作为分专业的依据。select snum 学号,AVG(score) 平均成绩 into 分专业依据from scwhere snum like 160112% and cnum in (select cnum from mtrain where cdate=一 or cdate=二)group by snumorder by AVG(score)三、 数据备份用SQLSERVER2000提供的备份功能对原始数据进行完全备份，并在做了第四部分数据更新后恢复数据库到更新前的状态。（备份方法见附录）四、 数据更新1.在SC中删除选修课尚无成绩的选课元组；de

22、lete from scwhere score is null and cnum in (select cnum from mtrain where ckind=选)2.将选修 刘兴老师课程的女同学选课元组全部删除；delete from scwhere teacher=刘兴 and snum in (select snum from s where sex=女) and cnum in(select cnum from mtrain where ckind=选)3.将工商12级学生高等数学课不及格的成绩全改为60分；update sc set score=60where snum like

23、160112% and cnum in (select cnum from c where cname=高等数学 and score60)4.将工商12级学生“数据库技术”课程低于该课平均成绩的女同学成绩提高5；update sc score=score*1.05where snum in (select snum from s where sex=女 and snum like 160112%and score(select AVG(score) from sc where cnum in(select cnum from c where cname=数据库技术)group by cnum)

24、5.在工商专业、电子商务专业培养计划表中增加一门“网络营销”必修课，计划在第五学期开设；insert into c(cnum,cname) values(c003,网络营销)insert into mtrain(mnum,ckind,cdate)values(1601,必,五)insert into mtrain(mnum,ckind,cdate)values(1607,必,五)注意点：insert into 的 into 可以省略；如果只输入部分属性值，则表后需按对应顺序写上列名。五、数据库安全性管理（先阅读教材9.8节的内容，再做此题！）1.以另外的windows 用户身份登录window

25、s(机房合法的WIN用户见附录2)，检查是否具有连接SQLServer数据库服务器的权限。（注：数据库服务器“安全性管理”文件夹中必须有相应的登录帐户才有连接数据库服务器的权限。）答：注销student 用户，用poweruser 用户进入，不能完成数据库的注册，说明poweruser用户没有连接 SQL SERVER数据库服务器的权限。2.登录帐户只有在成为数据库用户后才能访问数据库。（注：做此步需切换windows 用户身份）先在“数据库服务器”“安全性”“登录”创建一个与数据库服务器连接的合法登录帐户，给帐户名和密码。（注意登录验证：选择SQLSERVER身份验证）。答：我创建了Cc的登

26、陆账户，并设置密码。在设置systemadministrator时显示没有访问权限。查找原因发现没有在student用户名下对Cc进行授权。在Cc的登陆账号下新建了Cc数据库用户。3.以另外的windows 用户身份登录windows，再检查刚才创建的登录帐户是否有连接数据库服务器的权限。答：在student账户下，对已注册的Cc用户进行授权，此时Cc有连接数据库服务器的权限。4.登录帐户只有成为数据库用户后才能访问数据库。在自己的数据库中选择“用户”，创建合法用户，登录名用已创建的SQL服务器连接帐户，并输入用户名，再实现下面的操作。授权后再验证是否只具有相应的权限。（1） 把查询学生表的“

27、学号，姓名，性别，专业”的权力授予用户。（注：为创建的用户。）grant select (snum,sname,sex,major)on s,minf to Cc with grant option（2） 把查询课程表、教师开课表、专业培养计划表的权力授予用户。grant select on c,tea,mtrain to Ccwith grant option（3） 授予用户录入选课记录的权力。 grant insert on sc to Cc（4） 收回用户录入选课记录的权力revoke insert on sc to Cc六、完整性规则练习1. 给学生表中的性别给定默认值为“男”，学生所

28、在系默认为“商学院”。定义后再给学生表中插入几条记录，观察列值的变化。alter table s add default 男 for sexalter table sadd default 16% for mnum变化：不录入性别和所在系的值，这些属性值会显示默认值“男”和“商学院”2. 给学生表中的性别创建只能为“男”或“女”的check约束。定义后再给学生表中插入记录，观察列值的限制。alter table sadd constraint chk_scheck(sex=男 or sex=女)限制:给学生表中的性别输入“男”和“女”之外的值将显示不合法，只能输入“男”或者“女”3. 给学生选

29、课表中的成绩创建一个check约束，该约束要求插入到成绩列中值范围在0100分之间。给学生选课表录入成绩时，观察score列的数据限制。alter table scadd constraint chk_s check(score between 0 and 100)限制：成绩只能在0和100之间，不能超出这个范围Part2：设计篇：宾馆信息管理系统的设计传统的宾馆管理往往令管理者花大量的时间来处理顾客投诉、错误查询、繁琐的登记和结账手续、旅客费用计算等日常事务，空余客房资料不能及时提供也会影响出租率。另外随着旅游业的发展，宾馆、餐饮、娱乐行业日趋发达，引入全方位的计算机服务和管理是宾馆现代形象的一个重要标志。 一般大型宾馆管理系统包括：客户预定系统、前台接待系统、前台收银系统、客房系统、电话系统和自动计费等等，系统非常庞大。本设计是针对一个小型宾馆进行数据库设计，该数据库系统要存储和处理的信息有：（） 有关客房标准的信息，含客房标准名称、房间面积、床位数量、价格及是否有空调、电话、网络、电视和卫生间等信息，为了便于管理，每一种客房标准有唯一的标准标号。（） 客房基本信息，含客房编号、客房类型（即标准）、客房位置、客房单价、客房状态及备注信息等。（） 订房信息，含顾客的身份证号，顾客姓名，顾客的说明信息（如是否是会员，可作备注栏）、入住日期、客房号等。（） 结算信