数据库课程设计报告-.docx

数据库课程设计班级物联网1202学 号 3120611027 姓名 杨璐
指导老师 年轶2014年1月INSERTINTOSTODENT
VALUESC148'；Susan poweU,；534 East River Dr','19041','Haverford','PA',F);INSERTINTO STUDENTVALUESf210'；Bob Dawson*,*120 South Jefferson'；02891 '/Newport','RI'/M*);
INSERTINTO STUDENTVALUESf298','Howard Mansfield',*290 Wynkoop Drive','22180','Vienna',INSERT
INTO STUDENTVALUESC348','Susan Pugh','534 East Hampton Dr',‘06107’,'Hattfbrd','CN',F);INSERTINTO STUDENT
VALUESf349*：Joe Adams'：73 Emmerson Street*； 19702*,'Newark','DE'/M');INSERTINTO STUDENTVALUESC354'，3anct Ladd','441 10th Street','18073','Pennsburg','PA'T);
INSERTINTO STUDI-NTVALUES(*41O',B11 Jone'：120 South Harrison','92660,,,Newport',,CA\,M,);INSERT
INTO STUDENTVALUESC473*,•Carol Dean',*983 Park Avenue','02169','Boston','MA',F);INSERTINTO STUDENT
VALUESf548,.,Allcn thomas'；238 West Ox Road','6()624‘、'Chicagp','IL','M');INSERTINTO STUDENTVALUESC558','Vai Shipp','238 Westport Road','60556','Chicago','IL','F);
INSERTINTO STUDENTVALUESC649\john Anderson*,'473 Emmory Street^0008','New York'/NY/M*);INSERT
INTO STUDENTVALUESe54'Janct Yhomas',,441 6th Street,：l6510,,'Hrie,.'PA'；F);SELECT* FROM STUDENTTEACHER录入数据
INSERTINTO TEACHERVALUES(,303,,,Dr.Horn\,257-3049',27540.00);【NSHRT
INTO TEACHERVALUESC290,；Dr.Lo\ve,,,257-2390,,31450.00);INSERTINTO TEACHER
VALUES('430,；Dr.Englc,；56-462r,38200.00);INSERTINTO TEACHERVALUES(180：Dr.Cooke',,257-8088\29560.00：i;
INSERTINTO TEACHERVALUES(560；Dr.01sen,；257-8086,,31778.00);INSI-RT
INTO TEACHERVALUES<784；Dr.Scangp,；257-3046,,32098.00);SELECTTROM TEACHERSECTION表录入数据
INSERTINTO SECTIONVALUES(T,'303','450'2);INSERT
INTO SECTIONVALUES(T,'2907730',6);INSERTINTO SECTION
VALUES(T, 4307290,、3);INSERTINTO SECTIONVALUES(T,'18(T480'3);
INSERTINTO SECTIONVALUESf2'；560'；450,,2);INSERT
INTO SECTIONVALUES(,2,；784\'480\2);SELECT* FROM SECTIONENROLL表录入数据
INSERTINTO ENROLLVALUES(1730',T,'148','3‘)；INSERT
INTO ENROLLVALUESC450'；2,；210,；3,);INSERT皆辟
U,KM9；.l；.0SK'SamVATIOHMI (ILN11HHSNI •(,Kl6t9；,l,i.08K'SUa,IVA TK)H、a OJLNI1HHSNI
•CK.6^9/. iZ-OC^SHniVAFIOHNH OJLNIJWSNl •(l£；l8SS；.l：.0£Ll)SamVA TIOHNH OXNIJLHHSNI
•(,zA8K.\i;.06ZM'samvATIOXNH OJLNI 1HHSNIUl()；lVZ.K.3；.0Sh)S7inrIVATIOHNH OJLNI
：(£'曲玷 itocGsamvAT1OHM-I (UNIJ’MSNIW 以圮i伽h)samvA
TI()*7H O.LNTJLMSNIWQiKi：,08h)sarnvATIOHMH OJLNI luasNi
•(.K.KK/.l/.OSK'SHmVA 'I'IOH、m O1NIJ,海 SNIXm'，6 栈「L.06zJsarnvAT1()H7H OJ.NI
±HHSNITIOHNH OJLNI±HHSNI：(,£「86 乙 208 代)SH1TIVA
TIOHNH OJLNIJMRN1^£；.862；J；.063jSamVA TIOHN-IOXNIJLHHSNI
UjAoiz(\i;.o£z.;samvATIOXMHOLNIINSERTINTO ENROLL
VALUESC450,,,2\'548,：r);SELECT*FROM I-\ROLI,COURSE3结果也消息

eno
cname
department
credit
1
I 290 «
English Composition
English
3
2
450
Western Civilization
History
3
3
480
Compiler Writing
Computer Saence
3
4
730
Calculus Iv
Math
4
STUDENT9虽已成a执行。
3结果I伪消息

sno
sname
address
zQ
city
state
sex
1
(148 |
Susan powell
534 East River Dr
19041
Havefford
PA
F
2
210
Bob Dawson
120 South Jefferson
02891
Newport
Rl
M
3
298
Howard Mansfield
290 Wynkoop Dnve
22180
Vienna
VA
M
4
348
Susan Pugh
534 East Hampton Dr
06107
Hartford
CN
F
5
349
Joe Adams
73 Emmerson Street
19702
Newark
DE
M
6
354
Janet Ladd
441 10th Street
18073
Pennsb...
PA
F
7
410
Bill Jone
120 South Hanison
92660
Newport
CA
M
8
473
Carol Dean
983 Park Avenue
02169
Boston
MA
F
9
548
Allen thomas
238 West Ox Road
60624
Chicago
IL
M
10
558
Vai Shipp
238 West port Road
60556
Chicago
IL
F
11
649
John Anderson
473 Emmory Street
10008
New York
NY
M
12
654 Janet Yhomas
441 Gth Street
16510
Erie
PA
F
TEACHER□结果消息SECTION

tno
tname
phone
salary
1
| 180
j Dr. Cooke
257-8088
29560.00
2
290
Dr. Lowe
257-2390
31450.00
3
303
Dr. Hom
257-3049
27540.00
4
430
Dr .Engle
2564621
38200.00
5
560
Dr. Olsen
257-8086
31778.00
6
784
Dr. Sea...
257-3046
32098.00

secno
tno
eno
sqty
1
1

480
3
2
1
290
730
6
3
1
303
450
2
4
1
430
290
3
5
2
560
450
2
6
2
784
480
2
ENROLL②查询已成功执行.

eno
secno
sno
grade
1
i 290 i
1
298
3
2
290
1
349
4
3
290
1
548
2
4
450
2
210
3
5
450
1
473
2
6
450
2
548
1
7
450
1
654
4
8
480
2
298
3

eno
secno
sno
grade
8
480
2
298
3
9
480
1
348
4
10
480
1
410
2
11
480
2
473
0
12
480
1
649
4
13
730
1
148
3
14
730
1
210
1
15
730
1
348
2
16
730
1
473
3
17
730
1
558
3
18
730
1
649
4
4.检索系名为“Math”和“English”的课程信息SELECT *FROM COURSE
WHERE department = 'Math' OR department = 'English'按字母顺序列出教帅•姓名和电话号码SELECT tname,phone
口结果爵消息
eno cname
department
credit
1| 290 | English Composition
English
3
2730 Calculus Iv
Math
4
FROM TEACHI-RORDER BY tiiame园结果厄消息tname phone
1
! Dr.Cooke \
257-8088
2
Dr. Engle
2564621
3
Dr. Hom
257-3049
4
Dr. Lowe
257-2390
5
Dr.Olsen
257-8086
6
Dr.Scango
257-3046
检索电话号码不是以“257”打头的教师姓名和电话号码SELEC1' tnamc,phoneFROM TEACHER
WHERE phone not like '257%'
□]结果_J消息
tname phone
1\ Dr.Engte ] 2564621检索数学系所有成绩大于3的课程名、系名、学分SELECT cnamc,clcpartmcnt,crcditFROM COURSE
WHERE eno IN (SELECT enoFROM ENROLLWHERE grade>3 AND cno=,730r);cname department credit
1] Calculus Iv \ Math 4检索没有选修任何课的学生姓名、学号
SELECT sno,snamcFROM STUDENTWHERE NOT 1-XISTS(SELECT *FROM ENROLL
9.检索没有选修课程“Calculus Iv”的学生学号SELECT sno
FROM STUDENTWHERE sno NOT I、(SELECT STUDENT.sncFROM COURSE,STUDENT,ENROLL
WHERE cname-'Caiculus lv'
AND COURSE.cno二ENROLL.cn。
AND STUDENT.sno二ENROLL.sno);
□结果j消息*10检索至少选修教师“Dr. Lowe”所开全部课程的学生学号SELECT DINSTINCT sno

sno
1
[ 298 j
2
349
3
354
4
410
5
548
6
654
FROM ENROLL ENROLLXWHERE NOT I EXISTS(SELECT *
FROM l<NR()IJ.ENR()LLY
WHERE eno in(
SELECT eno
FROM TEACHER,SECTION,
WHERE tname=,Dr.I^)\ve'
AND TEACHER.tno=SECTION.cno)
AND NOT EXISTS
(SELECT *
FROM ENROLL ENROLLZ
WHERE EXR()LLZ.sno=EN ROLLX.sno
AND ENROLLZ.cno二ENROLLY.cn。))
口结果消息
1
sno
矿
2
210
3
348
4
473
5
558
6
649
②甄已赧瞄.
（修改数据验证，该老师教授课程号为730和500,只有学号148的学生同时选择了这两门 课）SECTIONENROLL

secno
tno
eno
sqty
1
H ””
j 180
480
3
2
1
290
500
1
3
1
290
730
6
4
1
303
450
2
5
1
430
230
3
6
2
560
450
2
7
2
784
480
2
13 5001
14 7301
结果
148 3
148 3
口结果消息

sno1 I 148检索每门课学生登记的人数、相应的课程名、课程号、分组号
SELECT DISTINCT secno,ENR()LL.cno,cnamc,sqtyFROM I-N R()LI.,COU RSI •：,SI ACTIONWHERE 。
AND COURSE.cno=F；,NROLL.cn。

secno
eno
cname
sqty
1

I 290
English Composition
3
2
1
450
Western Civilization
2
3
1
480
Compiler Writing
3
4
1
730
Calculus Iv
6
5
2
450
Western Gvilization
2
6
2
480
Compiler Writing
2
检索选修两门以上课程的学生姓名SELECT snamcFROM STUDENT
WHERE sno in (SELECT snoFROM ENROLLGROUP BY snoHAVING COUNT(*)>2)
sname
1 ［英Q查询已成功执行.
11. 检索只有男生选修的课程和学生名SELECT cnamc,snameFROM COURSE ,STUDI：NT ,R\ROLLWHERE STUDENT.sno二ENROLLsno
AND COURSE.cno=F；.N
AND COURSE.cno in
(SELECT enoFROM COURSEWHERE eno not in (SELECT DISTINCT enoFROM STUDENT,ENROLL
WHERE sex =T' and STUDENT.sno=ENROLL.sno))②面旬已做执行。

cname
sname
1
| English Composition
:Howard Mansfield
:
2
English Composition
Joe Adams
3
English Composition
Allen thomas
检索所有学生都选修的课程名、学生名、授课教师名、该生成绩SELECT cname,sname,tnamc,gradeI'ROM STUDENT ,TI：ACHI：R ,SIX：TI()N,C()l；RSI•:
WHERE COURSE.cno二E\ROLL.cn。
AND STUDENT.sno=E\R()LL.sno
AND TEACHER.tno=SECTION.tno
AND SECTION.cno二ENROLL.cn。
AND SECTION.sccno=ENROLL.sccon
AND COURSE.cno=(SELECT enoFROM ENROLLGROUP BY enoHAVING COUNT (*)=12i
cname sname tname grade②面旬已翰执行.
15.删去名为"joe Adams”的所有记录DELBTKFROM STUDENTWHERE sname='Joc Adams,
SELECT *FROM STUDENT一、引言2
1. 目的2
2. 题目2
3. 要求2二、系统的分析与设计3
1. 概念设计3
2. 逻辑设计3
3. 系统功能结构4
4. 完整性设计5三、系统的实现6四、课程设计小结22

sno
sname
address
zip
city
state
sex
1
“48 !
Susan powell
534 East River Dr
19041
Haverford
PA
F
2
210
Bob Dawson
120 South Jefferson
02891
Newport
Rl
M
3
238
Howard Ma...
290 Wynkoop Drive
22180
Vienna
VA
M
4
348
Susan Pugh
534 East Hampto...
06107
Hartford
CN
F
5
354
Janet Ladd
441 10th Street
18073
Pennsb...
PA
F
6
410
Bill Jone
120 South Harrison
92660
Newport
CA
M
7
473
Carol Dean
983 Park Avenue
02169
Boston
MA
F

尸
AM
ccc、,，• z\ ryi

ii
.・
粉查询已成功执行.
8
548
Allen thomas
238 West Ox Road
60624
Chicago
IL
M
9
558
Vai Shipp
238 Westport Road
60556
Chicago
IL
F
10
649
John Ander...
473 Emmory Street
10008
New York
NY
M
11
654
Janet Yho...
441 fth Street
16510
Erie
PA
F
16.把教师“Scangp"的编号改为“666"UPGRADE TEACHI-RSET tno='666'
WI Il lRbL tname 'Dr.Scangp 'SELECT *FROMTHACHKR6666 Dr.Sca... 257-3046 32038.00
17.统计教师"Engle”教的英语课的学生平均分SELECT AVG(grade)AV(；FROM ENROLLWHERE grade in(SELECT grade
FROM TEACHER,SECTION,ENROLLWHERE mamc^'Dr.Englc*AND TEACHER.tno=SECTION.tnoAND SECTION.cno二ENROLL.cn。
AND ENROLL.secno=SECTION.secno)
AVG
1L.3…]18.统计各门课程的选课人数SELECT COURSE.cnamc,COUNT(ENR()LL.sno)FROM ENROLL,COURSE
WIH-RR ENR()LL.cno=COURSE.cnoGROUP BY COURSE.cnamc;J结果 j消息

cname
QTY
1
i Calculus Iv
I 6
2
Compiler Writing
5
3
English Composition
3
4
Western Civilization
4
19.输出如下报表:
学生名
课程名
教师名
成绩
SELECT snamc学生名,cname课程名,tname教帅•名,grade成绩 FROM ENROLL,STCDENT,COURSE,TEACHER.SECTION WHERE EN R( )LLsnc二STUDENT.snc
AND SECTION.eno = COURSE.cno
AND ENROLL.secno = SECTION.sccno
AND I：NR()LL.cno = SKCTION.cno
AND SECTION.tno 二 TEACHER.tno*20.定义并验证触发器，当登记表增加一条新的记录时，自动在分组表中更新相应属性。

学生名
课程名
教弗名
成绩
1
Susan Pugh
Compiler Writing
Dr. Cooke
4
2
Bill Jone
Compiler Writing
Dr. Cooke
2
3
John Ande...
Compiler Writing
Dr. Cooke
4
4
Susan po...
Calculus Iv
Dr. Lowe
3
5
Bob Daws...
Calculus Iv
Dr. Lowe
1
6
Susan Pugh
Calculus Iv
Dr. Lowe
I 2 I
7
Carol Dean
Calculus Iv
Dr. Lowe
3
8
Vai Shipp
Calculus Iv
Dr. Lowe
3

学生名
深程名
教师名
成绩
9
John Ande...
Calculus Iv
Dr. Lowe
4
10
Carol Dean
Western Ovii...
Dr. Hom
2
11
Janet Vho...
Western Qvili...
Dr. Hom
4
12
Howard M...
English Comp...
Dr Engle
3
13
Joe Adams
English Comp...
Dr Engle
4
14
Allen thomas
English Comp...
Dr .Engle
2
15
Bob Daws...
Western Ovii...
Dr. CHsen
3
16
Allen thomas
Western Qvili...
Dr. Olsen
1
17
Howard M...
Compiler Writing
Dr. Sea...
3
18
Carol Dean
Compiler Writing
Dr. Sea...
0
CREATE TRIGGER ENROLL,!
ON ENROLLAFTER INSERTASUPDATE SECTION
SET sqty=sqty+1FROM SECTION,insertedWHERE inscrtcd.sccno=SECTION.sccnoANDo=SECTION.cno
验证结果：
INSERTINTO ENROLLVALUES350',2,'354',T)SELECT *■
FROM SECTION原始数据执行结果Z1结果，消息

secno
tno
eno
sqty
1

180
480
3
2
1
290
500
1
3
1
290
730
6
4
1
303
450
2
5
1
430
290
3
6
2
560
450
2
7
2
784
480
2

secno
tno
eno
sqty
1
lO
180
480
3
2
1
290
500
1
3
1
290
730
6
4
1
303
450
2
5
1
430
290
3
6
2
560
450
3
7
2
784
480
2

四、课程设计小结
在本次课设之前，我已经在前段学习期间上机编写过类似相关的SQL语句来完成对于 学生-课程-选课的信息查询，这次在原先实验的基础上添加了分组和教师表，创建数据库、 基本表和录入数据的过程与上机实验基本相同，因此很容易就完成了前三个步骤，在这个过 程中对于数据库、表的创建以及数据的输入的操作都更加的熟练。
题目4-19都是对于表中数据进行查询、修改、删除等操作，并且运用了。RDERBY、 GROUP BY等功能短语以及COUNT、AVG等聚集函数。前两题都是基本的查询，比较简 单，但由于输入数据时Dr.Engle中的多按了一个空格，结果在调试的时候出现的是错tname phone.・・•••・・•••・•・•・•••・・・•••・・••••・・・••1\ Dr .Engle \ 56*4621
、・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・♦
误的结果，结果不正确(见截图2& Cooke 257-80S8 }o
第10题是本次课设最难的题目，自己也尝试着去做，但是由于理解的失误以及数据的 巧合性，错误的SQL语句得出的结果却是正确的。这就导致了自己以为解决了这道难题， 以至于在给老师检查的时候被指出严重的错误，感到很羞愧。后来仔细分析题意，按照所有 的关键字来编写查询语句，参照书上关于not exists语句以及嵌套循环的语句，用谓词演算 将题目转换成逻辑运算，等价为“没有这样课程y,老师Dr.Lowc教授了这门课，而学生X 没有选”。P表示“老师教授了课程y”，q表示“学生X选了课y”，等价于「3 y(pAnq)o 参照书上的例题，分层次地编写功能语句。因为题目的巧合性所以在检验时修改了数据，添 加了该老师新开另一门课，只有学号148的学生同时选择了这门老师的两门课。然后再去验 证结果发现是正确的。
口结果也消息
sno
第13题的题目刚开始理解的不透彻，所以直接查询了男生选修的课程，得到的结果有 10个，但照表检查可以看出有些课程仍是有女生选的，与题目“只有男生选修”的条件不 符，因此重新分析，发现可以使用嵌套查询先用了查询把女生选择的课程选出来，然后父查 询在子查询的结果之外(not in)查询，这样就把女生选修的课程出去，这样就避免出现选 择的课程男、女生都选修的错误。(见截图)口结果 j消息

cname
sname
J
•••••••••••••••••••••••••••••••••••••••••••••••••••••••J
:English Composition j
Howard Mansfield
2
English Composition
Joe Adams
3
English Composition
Allen thomas
4
Western Civilization
Bob Dawson
5
Western Ovilization
Allen thomas
6
Compiler Writing
Howard Mansfield
7
Compiler Writinq
Bill Jone
②查i旬已曲执行.
第15、16题是对于学生、教师表的修改和删除。在执行过程中程序一直报错，始终找 不出问题所在。后来经过询问同学，发现SECTION和ENROLL表中引用了这两个表中的 sn。和tno,由于其引用关系，所以无法对于这两个表进行修改。因此需要先删除外码的引用 关系，然后才能对其进行修改和删除。
第20题定义触发器这个比较陌生，在学习的时候就不是很掌握，因此对于这个题目第 一印象就是不想去做，我不会。但是后来通过翻阅书本并且询问同学，并且自己不断的摸索, 虽然起初•直报错，但是我并没有气馁，在各种帮助和努力卜终于实现了最后-个功能。不 过在给老师检查的过程中，发现UPDATE语句后面自己添加了 FROM的语句，虽然对于整 个执行结果没有太大的影响，但这是一个常识性的错误，不应该出现如此低级的错误，因为 当时是想新表需要和SECTION、ENROLL表进行连接，所以在写的过程中直接添加了 o=SECTION.cno AND inserted.secno=SECTI()N.secno 的错误语句,而且还误将其 中的SECTION写成了 ENROI.L表，以至于在老师询问的时候，自己也被自己的代码弄得 不知所措。后来修改的时候索性把所有的条件语句都删除了，并没有进行临时新表inserted 与SECTION表的连接，以至于结果每一行的学生登记数量sqty都加一，得到错误的结果。
过程比较艰辛但是最后完成的时候内心的喜悦确实满满洋溢的。果然自己去努力实现的 甲情可以给自己带来无限的快乐，自己从中收获的经验和知识也是更多的。不要因为自己对 于某方面不熟悉就直接放弃，而应该努力去尝试一下。只有试过了，才知道自己到底可不可 以完成这个看似困难的问题！
附表2课程(COURSE)学生(STUDENT)
课程号
(eno)
课程名
(cname)
系名
(department)
学分
(credit)
450
Western Civilization
History
3
730
Calculus Iv
Math
4
290
English Composition
En^ish
3
480
Compiler Writing
Computer Science
3
学号
(sno)
姓名
(sname)
地址
(address)
邮政编码
(zip)
市
(city)
省(state)
性别
(sex)
148
Susan powcll
534 East River Dr
19041
Havcrfbrd
PA
F
210
Bob Dawson
120 South Jefferson
02891
Newport
RI
M
298
Howard Mansfield
290 Wynkoop Drive
22180
Vienna
VA
M
348
Susan Pugh
534 East Hampton Dr
06107
Hartford
CN
F
349
Joe Adams
473 Emmerson Street
19702
Newark
DE
M
354
Janet Ladd
441 10th Street
18073
Pennsburg
PA
F
410
Bill Jone
120 South Harrison
92660
Newport
CA
M
473
Carol Dean
983 Park Avenue
02169
Boston
MA
F
548
Allen thomas
238 West Ox Road
60624
Chicago
IL
M
558
Vai Shipp
238 Westport Road
60556
Chicago
IL
F
649
John Anderson
473 Emmery Street
10008
New York
NY
M
654
Janet Yhomas
441 6th Street
16510
Erie
PA
F
教师(TEACHER)分组(SECTION)登记(ENROLL)
教师编号
姓名
电话号码
工资
(tn。)
(tname)
(phone)
(salary)
303
Dr. Horn
257-3049
27540.00
290
Dr. Ix)we
257-2390
31450.00
430
Dr .Engle
256-4621
38200.00
180
Dr. Cooke
257-8088
29560.00
560
Dr. Olsen
257-8086
31778.00
784
Dr. Scango
257-3046
32098.00
组号
教师编号
课程号
学生数量
(secno)
(tno)
(eno)
(sqty)
]
303
450
2
1
290
730
6
1
430
290
3
1
180
480
3
2
560
450
2
2
784
480
2

课程号
(eno)
组号
(secno)
学生学号
(sno)
评分
(grade)
730
1
148
3
450
2
210
3
730
1
210
1
290
1
298
3
480
2
298
3
730
1
348
2
290
1
349
4
480
1
348
4
480
1
410
2
450
1
473
2
730
1
473
3
480
2
473
0
290
1
548
2
730
1
558
3
730
1
649
4
480
1
649
4
450
1
654
4
450
2
548
1
如有侵权请联系告知删除，感谢你们的配合!

1. 目的
课程设计为学生提供了一个既动手又动脑，独立实践的机会，将课本上的理论知识和实 际有机的结合起来，锻炼学生的分析解决实际问题的能力。提高学生适应实际，实践编程的 能力。课程设计的目的：
⑴加深对数据库系统、软件工•程、程序设计语言的理论知识的理解和应用水平；⑵在理论和实验教学基础上进一步巩固已学基本理论及应用知识并加以综合提高；学会将知识应用于实际的方法，提高分析和解决问