数据库课程设计报告完整版.docx
《数据库课程设计报告完整版.docx》由会员分享，可在线阅读，更多相关《数据库课程设计报告完整版.docx（31页珍藏版）》请在冰点文库上搜索。
数据库课程设计报告完整版
数据库课程设计报告-完整版
数据库课程设计
班级物联网1202
学号3120611027
姓名杨璐
指导老师年轶
2014年1月
一、引言
1．目的
课程设计为学生提供了一个既动手又动脑，独立实践的机会，将课本上的理论知识和实际有机的结合起来，锻炼学生的分析解决实际问题的能力。
提高学生适应实际，实践编程的能力。
课程设计的目的：
（1）加深对数据库系统、软件工程、程序设计语言的理论知识的理解和应用水平；
（2）在理论和实验教学基础上进一步巩固已学基本理论及应用知识并加以综合提高；
（3）学会将知识应用于实际的方法，提高分析和解决问题的能力，增强动手能力；
（4）为毕业设计和以后工作打下必要基础。
2．题目
题目2.设计一个大学教学数据库应用系统。
该系统涉及学生、教师、课程、分组、登记。
数据见附表2。
因时间关系，只要求每个学生任选1个题目，如有时间﹑有兴趣，可做另外一题，酌情加分。
3．要求
运用数据库基本理论与应用知识，在微机RDBMS（SQLServer）的环境上建立一个数据库应用系统。
要求把现实世界的事物及事物之间的复杂关系抽象为信息世界的实体及实体之间联系的信息模型，再转换为机器世界的数据模型和数据文件，并对数据文件实施检索、更新和控制等操作。
（1）用E-R图设计选定题目的信息模型；
（2）设计相应的关系模型，确定数据库结构；
（3）分析关系模式各属于第几范式，阐明理由；
（4）设计应用系统的系统结构图；
（5）通过设计关系的主码约束、外码约束和使用CHECK实现完整性控制；
（6）完成实验内容所指定的各项要求；
（7）分析遇到的问题，总结并写出课程设计报告；
（8）自我评价
二、系统的分析与设计
1．概念设计
2．逻辑设计
STUDENT（sno,sname,address,zip,state,city,sex）sno是主码
TEACHER（tno,tname,phone,salary）tno是主码
COURSE（cno,cname,department,credit）cno是主码
SECTION（secno,cno,tno,sqty）secno、tno、cno是主码，cno、tno是外码
ENROLL（secno,cno,sno,grade）cno,sno是主码，cno,sno是外码
3．系统功能结构
4．完整性设计
/* 包含：
关系主码、外码、CHECK约束。
并给出相应的SQL语句*/
COURSE的完整性约束:
cnoCHAR（10）PRIMARYKEY
STUDENT的完整性约束:
snoCHAR（10）PRIMARYKEY
TEACHER的完整性约束:
tnoCHAR（10）PRIMARYKEY
SECTION的完整性约束:
PRIMARYKEY（secno,tno,cno）,
FOREIGNKEY（tno）REFERENCESTEACHER（tno）,
FOREIGNKEY（cno）REFERENCESCOURSE（cno）
ENROLL的完整性约束:
PRIMARYKEY（cno,sno）,
FOREIGNKEY（sno）REFERENCESSTUDENT（sno）,
FOREIGNKEY（cno）REFERENCESCOURSE（cno）
三、系统的实现
题目2
1.创建数据库
创建名为“SCTE”的数据库
2.创建各基本表
CREATETABLECOURSE
（cnoCHAR（10）PRIMARYKEY,
cnameCHAR（30）,
departmentCHAR（30）,
creditSMALLINT
）;
CREATETABLESTUDENT
（snoCHAR（10）PRIMARYKEY,
snameCHAR（30）,
addressCHAR（30）,
zipCHAR（10）,
cityCHAR（20）,
stateCHAR（10）,
sexCHAR
（2）
）;
CREATETABLETEACHER
（tnoCHAR（10）PRIMARYKEY,
tnameCHAR（20）,
phoneCHAR（10）,
salaryNUMERIC（10,2）
）;
CREATETABLESECTION
（secnoCHAR
（2）,
tnoCHAR（10）,
cnoCHAR（10）,
sqtyINT,
PRIMARYKEY（secno,tno,cno）,
FOREIGNKEY（tno）REFERENCESTEACHER（tno）,
FOREIGNKEY（cno）REFERENCESCOURSE（cno）
）;
CREATETABLEENROLL
（cnoCHAR（10）,
seconCHAR
（2）,
snoCHAR（10）,
gradeINT,
PRIMARYKEY（cno,sno）,
FOREIGNKEY（sno）REFERENCESSTUDENT（sno）,
FOREIGNKEY（cno）REFERENCESCOURSE（cno）
）;
3.完成数据的录入
COURSE表录入数据
INSERT
INTOCOURSE
VALUES（'450','WesternCivilization','History','3'）;
INSERT
INTOCOURSE
VALUES（'730','CalculusIv','Math','4'）;
INSERT
INTOCOURSE
VALUES（'290','EnglishComposition','English','3'）;
INSERT
INTOCOURSE
VALUES（'480','CompilerWriting','ComputerScience','3'）;
SELECT*FROMCOURSE
STUDENT表录入数据
INSERT
INTOSTUDENT
VALUES（'148','Susanpowell','534EastRiverDr','19041','Haverford','PA','F'）;
INSERT
INTOSTUDENT
VALUES（'210','BobDawson','120SouthJefferson','02891','Newport','RI','M'）;
INSERT
INTOSTUDENT
VALUES（'298','HowardMansfield','290WynkoopDrive','22180','Vienna','VA','M'）;
INSERT
INTOSTUDENT
VALUES（'348','SusanPugh','534EastHamptonDr','06107','Hartford','CN','F'）;
INSERT
INTOSTUDENT
VALUES（'349','JoeAdams','73EmmersonStreet','19702','Newark','DE','M'）;
INSERT
INTOSTUDENT
VALUES（'354','JanetLadd','44110thStreet','18073','Pennsburg','PA','F'）;
INSERT
INTOSTUDENT
VALUES（'410','BillJone','120SouthHarrison','92660','Newport','CA','M'）;
INSERT
INTOSTUDENT
VALUES（'473','CarolDean','983ParkAvenue','02169','Boston','MA','F'）;
INSERT
INTOSTUDENT
VALUES（'548','Allenthomas','238WestOxRoad','60624','Chicago','IL','M'）;
INSERT
INTOSTUDENT
VALUES（'558','ValShipp','238WestportRoad','60556','Chicago','IL','F'）;
INSERT
INTOSTUDENT
VALUES（'649','JohnAnderson','473EmmoryStreet','10008','NewYork','NY','M'）;
INSERT
INTOSTUDENT
VALUES（'654','JanetYhomas','4416thStreet','16510','Erie','PA','F'）;
SELECT*FROMSTUDENT
TEACHER录入数据
INSERT
INTOTEACHER
VALUES（'303','Dr.Horn','257-3049',27540.00）;
INSERT
INTOTEACHER
VALUES（'290','Dr.Lowe','257-2390',31450.00）;
INSERT
INTOTEACHER
VALUES（'430','Dr.Engle','56-4621',38200.00）;
INSERT
INTOTEACHER
VALUES（180,'Dr.Cooke','257-8088',29560.00）;
INSERT
INTOTEACHER
VALUES（560,'Dr.Olsen','257-8086',31778.00）;
INSERT
INTOTEACHER
VALUES（784,'Dr.Scango','257-3046',32098.00）;
SELECT*FROMTEACHER
SECTION表录入数据
INSERT
INTOSECTION
VALUES（'1','303','450',2）;
INSERT
INTOSECTION
VALUES（'1','290','730',6）;
INSERT
INTOSECTION
VALUES（'1','430','290',3）;
INSERT
INTOSECTION
VALUES（'1','180','480',3）;
INSERT
INTOSECTION
VALUES（'2','560','450',2）;
INSERT
INTOSECTION
VALUES（'2','784','480',2）;
SELECT*FROMSECTION
ENROLL表录入数据
INSERT
INTOENROLL
VALUES（'730','1','148','3'）;
INSERT
INTOENROLL
VALUES（'450','2','210','3'）;
INSERT
INTOENROLL
VALUES（'730','1','210','1'）;
INSERT
INTOENROLL
VALUES（'290','1','298','3'）;
INSERT
INTOENROLL
VALUES（'480','2','298','3'）;
INSERT
INTOENROLL
VALUES（'730','1','348','2'）;
INSERT
INTOENROLL
VALUES（'290','1','349','4'）;
INSERT
INTOENROLL
VALUES（'480','1','348','4'）;
INSERT
INTOENROLL
VALUES（'480','1','410','2'）;
INSERT
INTOENROLL
VALUES（'450','1','473','2'）;
INSERT
INTOENROLL
VALUES（'730','1','473','3'）;
INSERT
INTOENROLL
VALUES（'480','2','473','0'）;
INSERT
INTOENROLL
VALUES（'290','1','548','2'）;
INSERT
INTOENROLL
VALUES（'730','1','558','3'）;
INSERT
INTOENROLL
VALUES（'730','1','649','4'）;
INSERT
INTOENROLL
VALUES（'480','1','649','4'）;
INSERT
INTOENROLL
VALUES（'450','1','654','4'）;
INSERT
INTOENROLL
VALUES（'450','2','548','1'）;
SELECT*FROMENROLL
COURSE
STUDENT
TEACHER
SECTION
ENROLL
4.检索系名为“Math”和“English”的课程信息
SELECT*
FROMCOURSE
WHEREdepartment='Math'ORdepartment='English'
5.按字母顺序列出教师姓名和电话号码
SELECTtname,phone
FROMTEACHER
ORDERBYtname
6.检索电话号码不是以“257”打头的教师姓名和电话号码
SELECTtname,phone
FROMTEACHER
WHEREphonenotlike'257%'
7.检索数学系所有成绩大于3的课程名、系名、学分
SELECTcname,department,credit
FROMCOURSE
WHEREcnoIN（SELECTcno
FROMENROLL
WHEREgrade>3ANDcno='730'）;
8.检索没有选修任何课的学生姓名、学号
SELECTsno,sname
FROMSTUDENT
WHERENOTEXISTS（SELECT*
FROMENROLL
WHERESTUDENT.sno=ENROLL.sno）;
9.检索没有选修课程“CalculusIv”的学生学号
SELECTsno
FROMSTUDENT
WHEREsnoNOTIN
（SELECTSTUDENT.sno
FROMCOURSE,STUDENT,ENROLL
WHEREcname='CalculusIv'
ANDCOURSE.cno=ENROLL.cno
ANDSTUDENT.sno=ENROLL.sno）;
*10.检索至少选修教师“Dr.Lowe”所开全部课程的学生学号
SELECTDINSTINCTsno
FROMENROLLENROLLX
WHERENOTEXISTS
（SELECT*
FROMENROLLENROLLY
WHEREcnoin（
SELECTcno
FROMTEACHER,SECTION,
WHEREtname='Dr.Lowe'
ANDTEACHER.tno=SECTION.tno）
ANDNOTEXISTS
（SELECT*
FROMENROLLENROLLZ
WHEREENROLLZ.sno=ENROLLX.sno
ANDENROLLZ.cno=ENROLLY.cno））
（修改数据验证，该老师教授课程号为730和500，只有学号148的学生同时选择了这两门课）
SECTION
ENROLL
结果
11.检索每门课学生登记的人数、相应的课程名、课程号、分组号
SELECTDISTINCTsecno,ENROLL.cno,cname,sqty
FROMENROLL,COURSE,SECTION
WHERESECTION.cno=ENROLL.cno
ANDCOURSE.cno=ENROLL.cno
12.检索选修两门以上课程的学生姓名
SELECTsname
FROMSTUDENT
WHEREsnoin（SELECTsno
FROMENROLL
GROUPBYsno
HAVINGCOUNT（*）>2）
13.检索只有男生选修的课程和学生名
SELECTcname,sname
FROMCOURSE,STUDENT,ENROLL
WHERESTUDENT.sno=ENROLL.sno
ANDCOURSE.cno=ENROLL.cno
ANDCOURSE.cnoin
（SELECTcno
FROMCOURSE
WHEREcnonotin
（SELECTDISTINCTcno
FROMSTUDENT,ENROLL
WHEREsex='F'andSTUDENT.sno=ENROLL.sno））
14.检索所有学生都选修的课程名、学生名、授课教师名、该生成绩
SELECTcname,sname,tname,grade
FROMSTUDENT,TEACHER,SECTION,ENROLL,COURSE
WHERECOURSE.cno=ENROLL.cno
ANDSTUDENT.sno=ENROLL.sno
ANDTEACHER.tno=SECTION.tno
ANDSECTION.cno=ENROLL.cno
ANDSECTION.secno=ENROLL.secon
ANDCOURSE.cno=（SELECTcno
FROMENROLL
GROUPBYcno
HAVINGCOUNT（*）=12）
15.删去名为“JoeAdams”的所有记录
DELETE
FROMSTUDENT
WHEREsname='JoeAdams';
SELECT*
FROMSTUDENT
16.把教师“Scango”的编号改为“666”
UPGRADETEACHER
SETtno='666'
WHEREtname='Dr.Scango'
SELECT*
FROMTEACHER
17.统计教师“Engle”教的英语课的学生平均分
SELECTAVG（grade）AVG
FROMENROLL
WHEREgradein（SELECTgrade
FROMTEACHER,SECTION,ENROLL
WHEREtname='Dr.Engle'
ANDTEACHER.tno=SECTION.tno
ANDSECTION.cno=ENROLL.cno
ANDENROLL.secno=SECTION.secno）
18.统计各门课程的选课人数
SELECTCOURSE.cname,COUNT（ENROLL.sno）
FROMENROLL,COURSE
WHEREENROLL.cno=COURSE.cno
GROUPBYCOURSE.cname;
19.输出如下报表：
学生名
课程名
教师名
成绩
SELECTsname学生名,cname课程名,tname教师名,grade成绩
FROMENROLL,STUDENT,COURSE,TEACHER,SECTION
WHEREENROLL.sno=STUDENT.sno
ANDSECTION.cno=COURSE.cno
ANDENROLL.secno=SECTION.secno
ANDENROLL.cno=SECTION.cno
ANDSECTION.tno=TEACHER.tno
*20.定义并验证触发器，当登记表增加一条新的记录时，自动在分组表中更新相应属性。
CREATETRIGGERENROLL_1
ONENROLL
AFTERINSERT
AS
UPDATESECTION
SETsqty=sqty+1
FROMSECTION,inserted
WHEREinserted.secno=SECTION.secno
ANDo=SECTION.cno
验证结果：
INSERT
INTOENROLL
VALUES（'450','2','354','1'）
SELECT*
FROMSECTION
原始数据
执行结果
四、课程设计小结
在本次课设之前，我已经在前段学习期间上机编写过类似相关的SQL语句来完成对于学生-课程-选课的信息查询，这次在原先实验的基础上添加了分组和教师表，创建数据库、基本表和录入数据的过程与上机实验基本相同，因此很容易就完成了前三个步骤，在这个过程中对于数据库、表的创建以及数据的输入的操作都更加的熟练。
题目4-19都是对于表中数据进行查询、修改、删除等操作，并且运用了ORDERBY、GROUPBY等功能短语以及COUNT、AVG等聚集函数。
前两题都是基本的查询，比较简单，但由于输入数据时Dr.Engle中的‘.’多按了一个空格，结果在调试的时候出现的是错误的结果，结果不正确（见截图
}。
第10题是本次课设最难的题目，自己也尝试着去做，但是由于理解的失误以及数据的巧合性，错误的SQL语句得出的结果却是正确的。
这就导致了自己以为解决了这道难题，以至于在给老师检查的时候被指出严重的错误，感到很羞愧。
后来仔细分析题意，按照所有的关键字来编写查询语句，参照书上关于notexists语句以及嵌套循环的语句，用谓词演算将题目转换成逻辑运算，等价为“没有这样课程y，老师Dr.Lowe教授了这门课，而学生X没有选”。
p表示“老师教授了课程y”，q表示“学生X选了课y”,等价于┐ョy（p∧┐q）。
参照书上的例题，分层次地编写功能语句。
因为题目的巧合性所以在检验时修改了数据，添加了该老师新开另一门课，只有学号148的学生同时选择了这门老师的两门课。
然后再去验证结果发现是正确的。
第13题的题目刚开始理解的不透彻，所以直接查询了男生选修的课程，得到的结果有10个，但照表检查可以看出有些课程仍是有女生选的，与题目“只有男生选修”的条件不符，因此重新分析，发现可以使用嵌套查询先用子查询把女生选择的课程选出来，然后父查询在子查询的结果之外（notin）查询，这样就把女生选修的课程出去，这样就避免出现选择的课程男、女生都选修的错误。
（见截图）
第15、16题是对于学生、教师表的修改和删除。
在执行过程中程序一直报错，始终找不出问题所在。
后来经过询问同学，发现SECTION和ENROLL表中引用了这两个表中的sno和tno,由于其引用关系，所以无法对于这两个表进行修改。
因此需要
升级会员 