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

数据库课程设计

班 级 物联网1202 学 号 ********** 姓 名 杨璐 指导老师 年轶

2014 年 1 月

目 录

一、引言 ..................................................................................................... 2 1．目的 ............................................... 2 2．题目 ............................................... 2 3．要求 ............................................... 2 二、系统的分析与设计 ............................................................................ 3 1．概念设计 ........................................... 3 2．逻辑设计 ........................................... 3 3．系统功能结构 ....................................... 4 4．完整性设计 ......................................... 5 三、系统的实现......................................................................................... 6 四、课程设计小结................................................................................... 22

第1页

一、引言

1．目的

课程设计为学生提供了一个既动手又动脑,实践的机会，将课本上的理论知识和实际有机的结合起来,锻炼学生的分析解决实际问题的能力。提高学生适应实际，实践编程的能力。课程设计的目的：

(1)加深对数据库系统、软件工程、程序设计语言的理论知识的理解和应用水平; (2）在理论和实验教学基础上进一步巩固已学基本理论及应用知识并加以综合提高; （3）学会将知识应用于实际的方法,提高分析和解决问题的能力，增强动手能力; (4)为毕业设计和以后工作打下必要基础。 2．题目

题目2。设计一个大学教学数据库应用系统。

该系统涉及学生、教师、课程、分组、登记。数据见附表2。

因时间关系，只要求每个学生任选1个题目，如有时间﹑有兴趣，可做另外一题，酌情加分。

3．要求

运用数据库基本理论与应用知识,在微机RDBMS(SQL Server）的环境上建立一个数据库应用系统.要求把现实世界的事物及事物之间的复杂关系抽象为信息世界的实体及实体之间联系的信息模型，再转换为机器世界的数据模型和数据文件，并对数据文件实施检索、更新和控制等操作.

(1）用E-R图设计选定题目的信息模型； (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，department,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的完整性约束：

cno CHAR(10） PRIMARY KEY

STUDENT的完整性约束：

sno CHAR（10） PRIMARY KEY

TEACHER的完整性约束:

tno CHAR（10) PRIMARY KEY

SECTION的完整性约束：

PRIMARY KEY（secno,tno，cno),

FOREIGN KEY(tno) REFERENCES TEACHER（tno), FOREIGN KEY（cno） REFERENCES COURSE（cno)

ENROLL的完整性约束:

PRIMARY KEY(cno,sno）,

FOREIGN KEY(sno) REFERENCES STUDENT(sno）, FOREIGN KEY（cno） REFERENCES COURSE(cno)

第5页

三、系统的实现

题目2

1.创建数据库

创建名为“SCTE”的数据库

2。创建各基本表

CREATE TABLE COURSE

(cno CHAR(10） PRIMARY KEY, cname CHAR（30)， department CHAR(30)， credit SMALLINT ）;

CREATE TABLE STUDENT (sno CHAR（10) PRIMARY KEY， sname CHAR(30），

第6页

address CHAR（30)， zip CHAR(10)， city CHAR(20)， state CHAR(10)， sex CHAR(2）

）;

CREATE TABLE TEACHER

（tno CHAR（10) PRIMARY KEY, tname CHAR（20)， phone CHAR（10)， salary NUMERIC（10，2） ）；

CREATE TABLE SECTION (secno CHAR(2), tno CHAR（10）， cno CHAR(10), sqty INT,

PRIMARY KEY（secno,tno,cno),

FOREIGN KEY（tno） REFERENCES TEACHER(tno)， FOREIGN KEY（cno） REFERENCES COURSE(cno) );

CREATE TABLE ENROLL (cno CHAR（10), secon CHAR（2）, sno CHAR(10）, grade INT，

PRIMARY KEY（cno,sno)，

FOREIGN KEY(sno) REFERENCES STUDENT（sno）, FOREIGN KEY（cno) REFERENCES COURSE(cno）

);

第7页

3。完成数据的录入

COURSE表录入数据 INSERT INTO COURSE

VALUES('450'，'Western Civilization’，’History','3')； INSERT INTO COURSE

VALUES('730','Calculus Iv’,'Math’，'4')； INSERT INTO COURSE

VALUES（’290’,'English Composition’，'English'，’3’）； INSERT INTO COURSE

VALUES('480',’Compiler Writing'，’Computer Science’,’3’）; SELECT*FROM COURSE

STUDENT表录入数据 INSERT

INTO STUDENT

VALUES（'148’,’Susan powell'，'534 East River Dr’,'19041',’Haverford’，'PA’，’F’)； INSERT

INTO STUDENT

VALUES(’210’，’Bob Dawson’，’120 South Jefferson','021’，'Newport’，'RI’，’M’); INSERT

INTO STUDENT

VALUES(’298',’Howard Mansfield’，’290 Wynkoop Drive’,'22180'，'Vienna'，'VA’，’M'）； INSERT

INTO STUDENT

VALUES（’348’，'Susan Pugh',’534 East Hampton Dr'，'06107’，'Hartford',’CN’，’F’）; INSERT

INTO STUDENT

VALUES（'349'，’Joe Adams'，’73 Emmerson Street’，'19702',’Newark’，'DE’,’M’）； INSERT

INTO STUDENT

VALUES('3'，’Janet Ladd',’441 10th Street'，’18073'，’Pennsburg','PA'，'F’)； INSERT

INTO STUDENT

VALUES（'410'，’Bill Jone'，’120 South Harrison',’92660’,’Newport’,'CA’，

第8页

'M’）; INSERT

INTO STUDENT

VALUES（'473’,'Carol Dean'，'983 Park Avenue',’02169'，’Boston'，’MA'，’F')； INSERT

INTO STUDENT

VALUES（’8’,'Allen thomas','238 West Ox Road',’60624',’Chicago’,’IL',’M’)； INSERT

INTO STUDENT

VALUES(’558’，’Val Shipp’，’238 Westport Road'，'60556',’Chicago’,'IL’,'F'）； INSERT

INTO STUDENT

VALUES('9','John Anderson’，'473 Emmory Street','10008'，'New York’,’NY'，’M'); INSERT

INTO STUDENT

VALUES（'6’，'Janet Yhomas'，’441 6th Street'，’16510',’Erie’，'PA'，’F'）; SELECT＊FROM STUDENT

TEACHER录入数据 INSERT INTO TEACHER

VALUES(’303’,'Dr.Horn'，’257—3049’，270。00)； INSERT INTO TEACHER

VALUES('290',’Dr.Lowe',’257-2390'，31450.00）； INSERT INTO TEACHER

VALUES('430',’Dr.Engle’，’56-4621',38200.00)； INSERT INTO TEACHER

VALUES（180，'Dr。Cooke'，’257-8088’,29560.00）; INSERT INTO TEACHER

VALUES（560,’Dr.Olsen','257—8086'，31778。00）； INSERT INTO TEACHER

VALUES（784,'Dr.Scango’,’257—3046’，32098.00）; SELECT*FROM TEACHER

SECTION表录入数据 INSERT

第9页

INTO SECTION

VALUES（’1',’303'，'450’,2)； INSERT

INTO SECTION

VALUES('1',’290’,'730’，6)； INSERT

INTO SECTION

VALUES(’1’，’430'，’290’，3); INSERT

INTO SECTION

VALUES(’1'，'180'，'480’，3); INSERT

INTO SECTION

VALUES('2’，’560’,'450’，2); INSERT

INTO SECTION

VALUES（'2'，'784’,'480’，2）； SELECT＊FROM SECTION

ENROLL表录入数据 INSERT INTO ENROLL

VALUES(’730'，’1’，’148’,'3'); INSERT INTO ENROLL

VALUES（'450’，’2’，’210’，’3'); INSERT INTO ENROLL

VALUES（'730'，'1’，’210'，’1'）; INSERT INTO ENROLL

VALUES('290’,’1’，’298’，’3'）; INSERT INTO ENROLL

VALUES('480’，'2’,'298','3'）; INSERT INTO ENROLL

VALUES('730'，'1’，'348',’2')； INSERT INTO ENROLL

VALUES('290’,'1'，'349’，’4’); INSERT INTO ENROLL

VALUES（’480'，'1'，'348’,'4’)；

第10页

INSERT INTO ENROLL

VALUES('480'，’1','410','2’）； INSERT INTO ENROLL

VALUES（’450','1’，’473’，’2’)； INSERT INTO ENROLL

VALUES(’730','1','473'，'3’); INSERT INTO ENROLL

VALUES（’480’,’2',’473'，’0')； INSERT INTO ENROLL

VALUES(’290'，’1’，'8','2'）； INSERT INTO ENROLL

VALUES（’730',’1’，’558’,'3')； INSERT INTO ENROLL

VALUES(’730'，'1’，'9'，’4'）; INSERT INTO ENROLL

VALUES(’480’,'1'，'9’,’4')； INSERT INTO ENROLL

VALUES（’450',’1','6’，’4'）; INSERT INTO ENROLL

VALUES（’450',’2’，’8'，’1')； SELECT＊FROM ENROLL

COURSE

第11页

STUDENT

TEACHER

SECTION

第12页

ENROLL

4.检索系名为“Math”和“English”的课程信息

SELECT *

FROM COURSE

WHERE department = 'Math' OR department = ’English'

5。按字母顺序列出教师姓名和电话号码

SELECT tname，phone FROM TEACHER ORDER BY tname

第13页

6.检索电话号码不是以“257”打头的教师姓名和电话号码

SELECT tname，phone FROM TEACHER

WHERE phone not like ’257%’

7。检索数学系所有成绩大于3的课程名、系名、学分

SELECT cname,department,credit FROM COURSE

WHERE cno IN(SELECT cno FROM ENROLL

WHERE grade>3 AND cno='730'）;

8。检索没有选修任何课的学生姓名、学号

SELECT sno，sname FROM STUDENT

WHERE NOT EXISTS（SELECT ＊ FROM ENROLL

WHERE STUDENT。sno=ENROLL。sno);

9.检索没有选修课程“Calculus Iv”的学生学号

SELECT sno FROM STUDENT

第14页

WHERE sno NOT IN (SELECT STUDENT。sno

FROM COURSE，STUDENT，ENROLL WHERE cname='Calculus Iv’

AND COURSE.cno=ENROLL.cno AND STUDENT.sno=ENROLL。sno）;

*10。检索至少选修教师“Dr。 Lowe\"所开全部课程的学生学号

SELECT DINSTINCT sno FROM ENROLL ENROLLX WHERE NOT EXISTS （SELECT *

FROM ENROLL ENROLLY WHERE cno in( SELECT cno

FROM TEACHER，SECTION， WHERE tname=’Dr.Lowe’

AND TEACHER。tno=SECTION。tno) AND NOT EXISTS (SELECT *

FROM ENROLL ENROLLZ

WHERE ENROLLZ。sno=ENROLLX.sno AND ENROLLZ.cno=ENROLLY.cno))

(修改数据验证,该老师教授课程号为730和500，只有学号148的学生同时选择了这两门课） SECTION

第15页

ENROLL

结果

11。检索每门课学生登记的人数、相应的课程名、课程号、分组号

SELECT DISTINCT secno,ENROLL。cno，cname,sqty FROM ENROLL，COURSE,SECTION WHERE SECTION。cno=ENROLL.cno

AND COURSE.cno=ENROLL.cno

12。检索选修两门以上课程的学生姓名

SELECT sname FROM STUDENT

WHERE sno in （SELECT sno FROM ENROLL GROUP BY sno

HAVING COUNT（＊)〉2)

13。检索只有男生选修的课程和学生名

第16页

SELECT cname,sname

FROM COURSE ,STUDENT ，ENROLL WHERE STUDENT.sno=ENROLL.sno

AND COURSE.cno=ENROLL。cno AND COURSE.cno in (SELECT cno FROM COURSE WHERE cno not in (SELECT DISTINCT cno

FROM STUDENT ，ENROLL

WHERE sex ='F’ and STUDENT.sno=ENROLL.sno）)

14。检索所有学生都选修的课程名、学生名、授课教师名、该生成绩

SELECT cname，sname,tname，grade

FROM STUDENT ,TEACHER ，SECTION ,ENROLL ,COURSE WHERE COURSE.cno=ENROLL.cno

AND STUDENT.sno=ENROLL。sno AND TEACHER。tno=SECTION。tno AND SECTION。cno=ENROLL。cno AND SECTION.secno=ENROLL。secon AND COURSE.cno=（SELECT cno FROM ENROLL GROUP BY cno

HAVING COUNT （*)=12）

15.删去名为“Joe Adams\"的所有记录

DELETE FROM STUDENT

WHERE sname='Joe Adams'； SELECT *

FROM STUDENT

第17页

16.把教师“Scango\"的编号改为“666”

UPGRADE TEACHER SET tno=’666'

WHERE tname=’Dr。Scango ’ SELECT *

FROM TEACHER

17。统计教师“Engle”教的英语课的学生平均分

SELECT AVG(grade）AVG FROM ENROLL

WHERE grade in（SELECT grade

FROM TEACHER,SECTION,ENROLL

WHERE tname='Dr。Engle'

AND TEACHER.tno=SECTION。tno AND SECTION.cno=ENROLL。cno

AND ENROLL.secno=SECTION.secno)

18。统计各门课程的选课人数

SELECT COURSE.cname,COUNT（ENROLL.sno） FROM ENROLL，COURSE

WHERE ENROLL。cno=COURSE.cno GROUP BY COURSE.cname；

第18页

19.输出如下报表:

学生名 课程名 教师名 成绩 SELECT sname 学生名，cname 课程名，tname 教师名,grade 成绩 FROM ENROLL,STUDENT,COURSE，TEACHER,SECTION WHERE ENROLL.sno=STUDENT.sno

AND SECTION。cno = COURSE。cno AND ENROLL。secno = SECTION。secno AND ENROLL.cno = SECTION。cno AND SECTION.tno = TEACHER.tno

*20.定义并验证触发器，当登记表增加一条新的记录时，自动在分组表中更新相应属性。

CREATE TRIGGER ENROLL_1 ON ENROLL AFTER INSERT AS

第19页

UPDATE SECTION SET sqty=sqty+1

FROM SECTION，inserted

WHERE inserted.secno=SECTION.secno AND inserted。cno=SECTION.cno 验证结果: INSERT INTO ENROLL

VALUES（’450’,’2','3’，'1’) SELECT * FROM SECTION

原始数据

执行结果

第20页

四、课程设计小结

在本次课设之前，我已经在前段学习期间上机编写过类似相关的SQL语句来完成对于学生-课程-选课的信息查询，这次在原先实验的基础上添加了分组和教师表，创建数据库、基本表和录入数据的过程与上机实验基本相同,因此很容易就完成了前三个步骤，在这个过程中对于数据库、表的创建以及数据的输入的操作都更加的熟练。

题目4-19都是对于表中数据进行查询、修改、删除等操作，并且运用了ORDER BY、GROUP BY等功能短语以及COUNT、AVG等聚集函数。前两题都是基本的查询，比较简单，但由于输入数据时Dr。Engle中的‘。’多按了一个空格，结果在调试的时候出现的是

错误的结果,结果不正确（见截图｝。

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

第13题的题目刚开始理解的不透彻，所以直接查询了男生选修的课程，得到的结果有10个,但照表检查可以看出有些课程仍是有女生选的，与题目“只有男生选修”的条件不符，因此重新分析，发现可以使用嵌套查询先用子查询把女生选择的课程选出来，然后父查询在子查询的结果之外(not in）查询，这样就把女生选修的课程出去，这样就避免出现选择的课程男、女生都选修的错误。（见截图)

第15、16题是对于学生、教师表的修改和删除.在执行过程中程序一直报错，始终找不出问题所在.后来经过询问同学,发现SECTION和ENROLL表中引用了这两个表中的sno和tno,由于其引用关系，所以无法对于这两个表进行修改。因此需要先删除外码的引用关系，然后

第21页

才能对其进行修改和删除。

第20题定义触发器这个比较陌生,在学习的时候就不是很掌握，因此对于这个题目第一印象就是不想去做,我不会.但是后来通过翻阅书本并且询问同学，并且自己不断的摸索,虽然起初一直报错，但是我并没有气馁，在各种帮助和努力下终于实现了最后一个功能.不过在给老师检查的过程中，发现UPDATE语句后面自己添加了FROM的语句,虽然对于整个执行结果没有太大的影响，但这是一个常识性的错误，不应该出现如此低级的错误，因为当时是想新表需要和SECTION、ENROLL表进行连接，所以在写的过程中直接添加了inserted。cno=SECTION.cno AND inserted.secno=SECTION.secno的错误语句，而且还误将其中的SECTION写成了ENROLL表，以至于在老师询问的时候，自己也被自己的代码弄得不知所措.后来修改的时候索性把所有的条件语句都删除了，并没有进行临时新表inserted与SECTION表的连接，以至于结果每一行的学生登记数量sqty都加一，得到错误的结果。 过程比较艰辛但是最后完成的时候内心的喜悦确实满满洋溢的。果然自己去努力实现的事情可以给自己带来无限的快乐，自己从中收获的经验和知识也是更多的。不要因为自己对于某方面不熟悉就直接放弃，而应该努力去尝试一下。只有试过了，才知道自己到底可不可以完成这个看似困难的问题！

第22页

附表2

课程(COURSE）

课程号 （cno） 450 730 290 480 学号 (sno） 148 210 298 348 349 3 410 473 8 558 9 6 课程名 （cname） Calculus Iv English Composition Compiler Writing 姓名 (sname） Susan powell Bob Dawson Susan Pugh Joe Adams Janet Ladd Bill Jone Carol Dean Allen thomas Val Shipp John Anderson Janet Yhomas 地址 (address) 534 East River Dr 120 South Jefferson 534 East Hampton Dr 473 Emmerson Street 441 10Street 120 South Harrison 983 Park Avenue 238 West Ox Road 238 Westport Road 473 Emmory Street 441 6 Street 教师（TEACHER）

教师编号 (tno） 303 290 430 180 560 784 组号 （secno） 1 1 1 1 2 姓名 (tname） Dr. Horn Dr。 Lowe Dr .Engle Dr。 Cooke Dr. Olsen Dr. Scango 教师编号 （tno） 303 290 430 180 560 电话号码 （phone） 257—3049 257—2390 256—4621 257—8088 257—8086 257—3046 课程号 （cno） 450 730 290 480 450 分组（SECTION） 学生数量 （sqty） 2 6 3 3 2 工资 （salary） 270.00 31450。00 38200.00 29560。00 31778.00 32098。00 thth 系名 （department) Math English Computer Science 邮政编码（zip） 19041 021 22180 06107 19702 18073 92660 02169 60624 60556 10008 16510 市 学分 (credit） 3 4 3 3 省性别F M M F M F M F M F M F Western Civilization History 学生（STUDENT) (city） Newport Vienna Hartford Newark Newport Boston Chicago Chicago New York Erie (state) （sex） RI VA CN DE CA MA IL IL NY PA Haverford PA Howard Mansfield 290 Wynkoop Drive Pennsburg PA 第23页

2 课程号 (cno) 730 450 730 290 480 730 290 480 480 450 730 480 290 730 730 480 450 450 784 组号 （secno） 1 2 1 1 2 1 1 1 1 1 1 2 1 1 1 1 1 2 480 登记（ENROLL） 学生学号 (sno） 148 210 210 298 298 348 349 348 410 473 473 473 8 558 9 9 6 8 2 评分 (grade） 3 3 1 3 3 2 4 4 2 2 3 0 2 3 4 4 4 1 第24页