数据库课程设计报告完整版

1、数据库课程设计班级物联网 1202学号3120611027姓名杨璐指导老师年轶2014年1 月目 录一、引言21目的22题目23要求2二、系统的分析与设计31概念设计32逻辑设计33系统功能结构44完整性设计5三、系统的实现6四、课程设计小结22第 1 页一、引言1目的课程设计为学生提供了一个既动手又动脑，独立实践的机会，将课本上的理论知识和实际有机的结合起来，锻炼学生的分析解决实际问题的能力。提高学生适应实际，实践编程的能力。课程设计的目的：(1) 加深对数据库系统、软件工程、程序设计语言的理论知识的理解和应用水平；(2) 在理论和实验教学基础上进一步巩固已学基本理论及应用知识并加以综合提高

2、；(3) 学会将知识应用于实际的方法，提高分析和解决问题的能力，增强动手能力；(4) 为毕业设计和以后工作打下必要基础。2题目题目 2. 设计一个大学教学数据库应用系统。该系统涉及学生、教师、课程、分组、登记。数据见附表 2。因时间关系，只要求每个学生任选1 个题目，如有时间有兴趣，可做另外一题，酌情加分。3要求运用数据库基本理论与应用知识，在微机RDBMS(SQLServer) 的环境上建立一个数据库应用系统。 要求把现实世界的事物及事物之间的复杂关系抽象为信息世界的实体及实体之间联系的信息模型，再转换为机器世界的数据模型和数据文件，并对数据文件实施检索、更新和控制等操作。(1) 用 E-R

3、 图设计选定题目的信息模型；(2) 设计相应的关系模型，确定数据库结构；(3) 分析关系模式各属于第几范式，阐明理由；(4) 设计应用系统的系统结构图；(5) 通过设计关系的主码约束、外码约束和使用CHECK实现 完整性控制 ；(6) 完成实验内容所指定的各项要求；(7) 分析遇到的问题，总结并写出课程设计报告；(8)自我评价第 2 页二、系统的分析与设计1概念设计2逻辑设计STUDENT(sno,sname,address,zip,state,city,sex) sno是主码TEACHER(tno,tname,phone,salary)tno是主码COURSE(cno,cname,depar

4、tment,credit)cno是主码SECTION(secno,cno,tno,sqty)secno、tno 、cno 是主码， cno、tno是外码ENROLL(secno,cno,sno,grade)cno,sno 是主码， cno,sno 是外码第 3 页3系统功能结构第 4 页4完整性设计/* 包含：关系主码、外码、CHECK约束。并给出相应的SQL语句 */COURSE的完整性约束 :cnoCHAR( 10 )PRIMARYKEYSTUDENT的完整性约束:snoCHAR( 10 )PRIMARYKEYTEACHER的完整性约束:tnoCHAR( 10 )PRIMARYKEYSEC

5、TION的完整性约束:PRIMARYKEY( secno, tno , cno ),FOREIGNKEY( tno )REFERENCESTEACHER( tno ),FOREIGNKEY( cno )REFERENCESCOURSE( cno )ENROLL的完整性约束 :PRIMARYKEY( cno , sno ),FOREIGNKEY( sno )REFERENCESSTUDENT ( sno ),FOREIGNKEY( cno )REFERENCESCOURSE( cno )第 5 页三、系统的实现题目 21. 创建数据库创建名为“ SCTE”的数据库2. 创建各基本表CREATET

6、ABLECOURSE( cnoCHAR( 10 )PRIMARYKEY,cnameCHAR( 30 ),departmentCHAR( 30 ),creditSMALLINT);CREATE TABLESTUDENT( snoCHAR( 10 )PRIMARYKEY,snameCHAR( 30 ),第 6 页addressCHAR( 30 ),zipCHAR( 10 ),cityCHAR( 20 ),stateCHAR( 10 ),sexCHAR( 2);CREATE TABLETEACHER( tnoCHAR( 10 )PRIMARYKEY,tnameCHAR( 20 ),phoneCHAR

7、( 10 ),salaryNUMERIC ( 10 , 2 );CREATE TABLESECTION( secnoCHAR( 2),tnoCHAR( 10 ),cnoCHAR( 10 ),sqtyINT ,PRIMARYKEY( secno, tno , cno ),FOREIGNKEY( tno )REFERENCES TEACHER( tno ),FOREIGNKEY( cno )REFERENCES COURSE( cno );CREATETABLEENROLL( cnoCHAR( 10 ),seconCHAR( 2),snoCHAR( 10 ),gradeINT ,PRIMARYKE

8、Y( cno , sno ),FOREIGNKEY( sno )REFERENCES STUDENT( sno ),FOREIGNKEY( cno )REFERENCES COURSE( cno );第 7 页3. 完成数据的录入COURSE表录入数据INSERTINTOCOURSEVALUES ( '450', 'Western Civilization', 'History', '3');INSERTINTOCOURSEVALUES ( '730', 'Calculus Iv', 'Ma

9、th', '4' );INSERTINTOCOURSEVALUES ( '290', 'English Composition', 'English', '3');INSERTINTOCOURSEVALUES ( '480', 'Compiler Writing', 'Computer Science', '3' );SELECT * FROM COURSESTUDENT 表录入数据INSERTINTOSTUDENTVALUES ( '

10、148', 'Susan powell', '534 East RiverDr' , '19041', 'Haverford', 'PA', 'F');INSERTINTOSTUDENTVALUES ( '210', 'Bob Dawson', '120 SouthJefferson', '02891', 'Newport', 'RI', 'M');INSERTINTOSTUDEN

11、TVALUES ( '298', 'Howard Mansfield', '290 WynkoopDrive', '22180', 'Vienna', 'VA', 'M');INSERTINTOSTUDENTVALUES ( '348', 'Susan Pugh', '534 East HamptonDr' , '06107', 'Hartford', 'CN', 'F'

12、);INSERTINTOSTUDENTVALUES ( '349', 'Joe Adams', '73 EmmersonStreet', '19702', 'Newark', 'DE' , 'M');INSERTINTOSTUDENTVALUES ( '354', 'Janet Ladd', '441 10thStreet', '18073', 'Pennsburg', 'PA', 

13、9;F');INSERTINTOSTUDENTVALUES ( '410', 'Bill Jone', '120 South第 8 页Harrison', '92660', 'Newport', 'CA', 'M');INSERTINTOSTUDENTVALUES ( '473', 'CarolDean' , '983 Park Avenue' , '02169', 'Boston', '

14、MA', 'F');INSERTINTOSTUDENTVALUES ( '548', 'Allen thomas', '238 West OxRoad', '60624', 'Chicago', 'IL', 'M');INSERTINTOSTUDENTVALUES ( '558', 'Val Shipp', '238 WestportRoad', '60556', 'Chicago'

15、;, 'IL', 'F');INSERTINTOSTUDENTVALUES ( '649', 'John Anderson', '473 Emmory Street', '10008', 'NewYork', 'NY' , 'M' );INSERTINTOSTUDENTVALUES ( '654', 'JanetYhomas', '4416thStreet', '16510', '

16、Erie', 'PA', 'F');SELECT * FROM STUDENTTEACHER录入数据INSERTINTOTEACHERVALUES ( '303' , 'Dr.Horn', '257-3049', 27540.00);INSERTINTOTEACHERVALUES ( '290' , 'Dr.Lowe', '257-2390', 31450.00);INSERTINTOTEACHERVALUES ( '430' , 'D

17、r.Engle', '56-4621', 38200.00);INSERTINTOTEACHERVALUES ( 180 , 'Dr.Cooke', '257-8088', 29560.00);INSERTINTOTEACHERVALUES ( 560 , 'Dr.Olsen', '257-8086', 31778.00);INSERTINTOTEACHERVALUES ( 784 , 'Dr.Scango', '257-3046', 32098.00);SELECT * F

18、ROM TEACHERSECTION 表录入数据INSERT第 9 页INTOSECTIONVALUES ( '1', '303', '450', 2 );INSERTINTOSECTIONVALUES ( '1', '290', '730', 6 );INSERTINTOSECTIONVALUES ( '1', '430', '290', 3 );INSERTINTOSECTIONVALUES ( '1', '180'

19、, '480', 3 );INSERTINTOSECTIONVALUES ( '2', '560', '450', 2 );INSERTINTOSECTIONVALUES ( '2', '784', '480', 2 );SELECT * FROM SECTIONENROLL 表录入数据INSERTINTOENROLLVALUES ( '730' , '1', '148', '3');INSERTINTOENROLLVA

20、LUES ( '450' , '2', '210', '3');INSERTINTOENROLLVALUES ( '730' , '1', '210', '1');INSERTINTOENROLLVALUES ( '290' , '1', '298', '3');INSERTINTOENROLLVALUES ( '480' , '2', '298', 

21、9;3');INSERTINTOENROLLVALUES ( '730' , '1', '348', '2');INSERTINTOENROLLVALUES ( '290' , '1', '349', '4');INSERTINTOENROLLVALUES ( '480' , '1', '348', '4');第 10 页INSERTINTOENROLLVALUES ( '480'

22、 , '1', '410', '2');INSERTINTOENROLLVALUES ( '450' , '1', '473', '2');INSERTINTOENROLLVALUES ( '730' , '1', '473', '3');INSERTINTOENROLLVALUES ( '480' , '2', '473', '0');INSERTINTO

23、ENROLLVALUES ( '290' , '1', '548', '2');INSERTINTOENROLLVALUES ( '730' , '1', '558', '3');INSERTINTOENROLLVALUES ( '730' , '1', '649', '4');INSERTINTOENROLLVALUES ( '480' , '1', '649&#

24、39;, '4');INSERTINTOENROLLVALUES ( '450' , '1', '654', '4');INSERTINTOENROLLVALUES ( '450' , '2', '548', '1');SELECT * FROM ENROLLCOURSE第 11 页STUDENTTEACHERSECTION第 12 页ENROLL4. 检索系名为“ Math”和“ English ”的课程信息SELECT*FROM COURSEWHE

25、RE department='Math'OR department='English'5. 按字母顺序列出教师姓名和电话号码SELECTtname , phoneFROM TEACHERORDER BY tname第 13 页6. 检索电话号码不是以“ 257”打头的教师姓名和电话号码SELECTtname , phoneFROM TEACHERWHERE phone not like'257%'7. 检索数学系所有成绩大于 3 的课程名、系名、学分SELECTcname , department, creditFROM COURSEWHERE

26、cnoIN( SELECTcnoFROM ENROLLWHERE grade>3AND cno = '730');8. 检索没有选修任何课的学生姓名、学号SELECTsno , snameFROM STUDENT WHERE NOT EXISTS(SELECT*FROM ENROLLWHERE STUDENT. sno = ENROLL. sno );9. 检索没有选修课程“ Calculus Iv ”的学生学号SELECTsnoFROM STUDENTWHERE snoNOT IN第 14 页( SELECTSTUDENT . snoFROM COURSE, STUDE

27、NT , ENROLLWHERE cname = 'Calculus Iv'AND COURSE. cno = ENROLL . cnoAND STUDENT. sno = ENROLL . sno );*10. 检索至少选修教师“Dr. Lowe ”所开全部课程的学生学号SELECTDINSTINCTsnoFROM ENROLLENROLLXWHERE NOT EXISTS( SELECT*FROM ENROLLENROLLYWHERE cnoin(SELECTcnoFROM TEACHER, SECTION ,WHERE tname = 'Dr.Lowe'A

28、ND TEACHER. tno =SECTION . tno )AND NOT EXISTS( SELECT*FROM ENROLLENROLLZWHERE ENROLLZ . sno = ENROLLX . snoAND ENROLLZ . cno =ENROLLY . cno )（修改数据验证，该老师教授课程号为730 和 500，只有学号148 的学生同时选择了这两门课）SECTION第 15 页ENROLL结果11. 检索每门课学生登记的人数、相应的课程名、课程号、分组号SELECTDISTINCTsecno, ENROLL . cno , cname , sqtyFROM ENROL

29、L, COURSE, SECTIONWHERE SECTION . cno = ENROLL . cnoAND COURSE. cno = ENROLL . cno12. 检索选修两门以上课程的学生姓名SELECTsnameFROM STUDENTWHERE snoin (SELECTsnoFROM ENROLLGROUP BY snoHAVINGCOUNT(*)>2 )13. 检索只有男生选修的课程和学生名SELECTcname , sname第 16 页FROM COURSE , STUDENT, ENROLLWHERE STUDENT . sno = ENROLL . snoAND

30、 COURSE. cno = ENROLL . cnoAND COURSE. cnoin( SELECTcnoFROM COURSEWHERE cnonotin( SELECTDISTINCTcnoFROM STUDENT, ENROLLWHERE sex= 'F'andSTUDENT . sno = ENROLL . sno )14. 检索所有学生都选修的课程名、学生名、授课教师名、该生成绩SELECTcname , sname , tname , gradeFROM STUDENT , TEACHER , SECTION , ENROLL , COURSE WHERE CO

31、URSE. cno =ENROLL . cnoAND STUDENT. sno = ENROLL . snoAND TEACHER. tno = SECTION . tnoAND SECTION . cno = ENROLL . cnoAND SECTION . secno= ENROLL . seconAND COURSE. cno =( SELECTcnoFROMENROLLGROUP BYcnoHAVINGCOUNT (*)=12 )15. 删去名为“ Joe Adams”的所有记录DELETEFROM STUDENTWHERE sname = 'Joe Adams'SE

32、LECT*FROM STUDENT第 17 页16. 把教师“ Scango”的编号改为“ 666”UPGRADE TEACHERSETtno ='666'WHERE tname = 'Dr.Scango 'SELECT*FROM TEACHER17. 统计教师“ Engle ”教的英语课的学生平均分SELECTAVG( grade) AVGFROM ENROLLWHERE gradein(SELECTgradeFROM TEACHER, SECTION , ENROLLWHERE tname = 'Dr.Engle'AND TEACHER. t

33、no =SECTION . tnoAND SECTION . cno =ENROLL . cnoAND ENROLL . secno= SECTION . secno)18. 统计各门课程的选课人数SELECTCOURSE. cname , COUNT( ENROLL.sno)FROM ENROLL, COURSEWHERE ENROLL . cno =COURSE. cnoGROUP BY COURSE. cname ;第 18 页19. 输出如下报表：学生名课程名教师名成绩SELECT sname 学生名 , cname 课程名 , tname 教师名 , grade 成绩 FROM EN

34、ROLL, STUDENT , COURSE, TEACHER, SECTION WHERE ENROLL . sno =STUDENT . snoAND SECTION . cno=COURSE. cnoAND ENROLL . secno=SECTION . secnoAND ENROLL . cno=SECTION . cnoAND SECTION . tno=TEACHER. tno*20. 定义并验证触发器，当登记表增加一条新的记录时，自动在分组表中更新相应属性。CREATETRIGGERENROLL_1ON ENROLLAFTER INSERTAS第 19 页UPDATESECTI

35、ONSETsqty=sqty+1FROM SECTION , insertedWHERE inserted. secno= SECTION . secnoANDinserted. cno = SECTION . cno验证结果：INSERTINTOENROLLVALUES ( '450', '2', '354', '1')SELECT*FROM SECTION原始数据执行结果第 20 页四、课程设计小结在本次课设之前，我已经在前段学习期间上机编写过类似相关的SQL 语句来完成对于学生 -课程 -选课的信息查询，这次在原先实验的基础

36、上添加了分组和教师表，创建数据库、基本表和录入数据的过程与上机实验基本相同，因此很容易就完成了前三个步骤，在这个过程中对于数据库、表的创建以及数据的输入的操作都更加的熟练。题目 4-19 都是对于表中数据进行查询、修改、删除等操作，并且运用了ORDER BY 、GROUP BY 等功能短语以及COUNT 、AVG 等聚集函数。前两题都是基本的查询，比较简单，但由于输入数据时Dr.Engle 中的 .多按了一个空格，结果在调试的时候出现的是错误的结果，结果不正确（见截图 。第 10 题是本次课设最难的题目，自己也尝试着去做，但是由于理解的失误以及数据的巧合性，错误的SQL 语句得出的结果却是正确

37、的。这就导致了自己以为解决了这道难题，以至于在给老师检查的时候被指出严重的错误，感到很羞愧。 后来仔细分析题意，按照所有的关键字来编写查询语句，参照书上关于not exists 语句以及嵌套循环的语句，用谓词演算将题目转换成逻辑运算，等价为“没有这样课程y，老师 Dr.Lowe 教授了这门课，而学生X没有选”。p 表示“老师教授了课程y”， q 表示“学生X 选了课 y” ,等价于 y(p q)。参照书上的例题，分层次地编写功能语句。因为题目的巧合性所以在检验时修改了数据，添加了该老师新开另一门课， 只有学号 148 的学生同时选择了这门老师的两门课。 然后再去验证结果发现是正确的。第 13

38、题的题目刚开始理解的不透彻，所以直接查询了男生选修的课程，得到的结果有10 个，但照表检查可以看出有些课程仍是有女生选的，与题目“只有男生选修”的条件不符，因此重新分析， 发现可以使用嵌套查询先用子查询把女生选择的课程选出来，然后父查询在子查询的结果之外（ not in）查询，这样就把女生选修的课程出去，这样就避免出现选择的课程男、女生都选修的错误。 （见截图）第 15、 16 题是对于学生、教师表的修改和删除。在执行过程中程序一直报错，始终找不出问题所在。后来经过询问同学，发现 SECTION和 ENROLL表中引用了这两个表中的 sno 和 tno, 由于其引用关系，所以无法对于这两个表进

39、行修改。因此需要先删除外码的引用关第 21 页系，然后才能对其进行修改和删除。第 20 题定义触发器这个比较陌生，在学习的时候就不是很掌握，因此对于这个题目第一印象就是不想去做， 我不会。但是后来通过翻阅书本并且询问同学，并且自己不断的摸索，虽然起初一直报错， 但是我并没有气馁， 在各种帮助和努力下终于实现了最后一个功能。 不过在给老师检查的过程中， 发现 UPDATE语句后面自己添加了 FROM的语句， 虽然对于整个执行结果没有太大的影响， 但这是一个常识性的错误， 不应该出现如此低级的错误， 因为当时是想新表需要和 SECTION、ENROLL表进行连接, 所以在写的过程中直接添加了o=S

40、ECTION.cno AND inserted.secno=SECTION.secno的错误语句，而且还误将其中的SECTION写成了 ENROLL表，以至于在老师询问的时候，自己也被自己的代码弄得不知所措。 后来修改的时候索性把所有的条件语句都删除了，并没有进行临时新表inserted与 SECTION表的连接，以至于结果每一行的学生登记数量sqty都加一，得到错误的结果。过程比较艰辛但是最后完成的时候内心的喜悦确实满满洋溢的。果然自己去努力实现的事情可以给自己带来无限的快乐，自己从中收获的经验和知识也是更多的。不要因为自己对于某方面不熟悉就直接放弃， 而应该努力去尝试一下。 只有试过了， 才知道自己到底可不可以完成这个看