数据库课程设计班级物联网1202学号3120611027 姓名杨璐指导老师年轶2014 年1 月目录一、引言 (2)1．目的 (2)2．题目 (2)3．要求 (2)一、引言1．目的课程设计为学生提供了一个既动手又动脑，独立实践的机会，将课本上的理论知识和实际有机的结合起来，锻炼学生的分析解决实际问题的能力。

提高学生适应实际，实践编程的能力。

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

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

该系统涉及学生、教师、课程、分组、登记。

数据见附表2。

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

3．要求运用数据库基本理论与应用知识，在微机RDBMS(SQL Server)的环境上建立一个数据库应用系统。

要求把现实世界的事物及事物之间的复杂关系抽象为信息世界的实体及实体之间联系的信息模型，再转换为机器世界的数据模型和数据文件，并对数据文件实施检索、更新和控制等操作。

(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的完整性约束:cno CHAR(10)PRIMARY KEYSTUDENT的完整性约束:sno CHAR(10)PRIMARY KEYTEACHER的完整性约束:tno CHAR(10)PRIMARY KEYSECTION的完整性约束: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)三、系统的实现题目21.创建数据库创建名为“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),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) );3.完成数据的录入COURSE表录入数据INSERTINTO COURSEVALUES('450','Western Civilization','History','3');INSERTINTO COURSEVALUES('730','Calculus Iv','Math','4');INSERTINTO COURSEVALUES('290','English Composition','English','3');INSERTINTO COURSEVALUES('480','Compiler Writing','Computer Science','3');SELECT*FROM COURSESTUDENT表录入数据INSERTINTO STUDENTVALUES('148','Susan powell','534 East River Dr','19041','Haverford','PA','F'); INSERTINTO STUDENTVALUES('210','Bob Dawson','120 South Jefferson','02891','Newport','RI','M'); INSERTINTO STUDENTVALUES('298','Howard Mansfield','290 Wynkoop Drive','22180','Vienna','VA','M'); INSERTINTO STUDENTVALUES('348','Susan Pugh','534 East Hampton Dr','06107','Hartford','CN','F'); INSERTINTO STUDENTVALUES('349','Joe Adams','73 Emmerson Street','19702','Newark','DE','M'); INSERTINTO STUDENTVALUES('354','Janet Ladd','441 10th Street','18073','Pennsburg','PA','F'); INSERTINTO STUDENTVALUES('410','Bill Jone','120 South Harrison','92660','Newport','CA','M'); INSERTINTO STUDENTVALUES('473','Carol Dean','983 Park Avenue','02169','Boston','MA','F'); INSERTINTO STUDENTVALUES('548','Allen thomas','238 West Ox Road','60624','Chicago','IL','M');INSERTINTO STUDENTVALUES('558','Val Shipp','238 Westport Road','60556','Chicago','IL','F'); INSERTINTO STUDENTVALUES('649','John Anderson','473 Emmory Street','10008','New York','NY','M'); INSERTINTO STUDENTVALUES('654','Janet Yhomas','441 6th Street','16510','Erie','PA','F');SELECT*FROM STUDENTTEACHER录入数据INSERTINTO TEACHERVALUES('303','Dr.Horn','257-3049',27540.00);INSERTINTO TEACHERVALUES('290','Dr.Lowe','257-2390',31450.00);INSERTINTO TEACHERVALUES('430','Dr.Engle','56-4621',38200.00);INSERTINTO TEACHERVALUES(180,'Dr.Cooke','257-8088',29560.00);INSERTINTO TEACHERVALUES(560,'Dr.Olsen','257-8086',31778.00);INSERTINTO TEACHERVALUES(784,'Dr.Scango','257-3046',32098.00);SELECT*FROM TEACHERSECTION表录入数据INSERTINTO SECTIONVALUES('1','303','450',2);INSERTINTO SECTIONVALUES('1','290','730',6);INSERTINTO SECTIONVALUES('1','430','290',3);INSERTINTO SECTIONVALUES('1','180','480',3); INSERTINTO SECTION VALUES('2','560','450',2); INSERTINTO SECTION VALUES('2','784','480',2); SELECT*FROM SECTIONENROLL表录入数据INSERTINTO ENROLLVALUES('730','1','148','3'); INSERTINTO ENROLLVALUES('450','2','210','3'); INSERTINTO ENROLLVALUES('730','1','210','1'); INSERTINTO ENROLLVALUES('290','1','298','3'); INSERTINTO ENROLLVALUES('480','2','298','3'); INSERTINTO ENROLLVALUES('730','1','348','2'); INSERTINTO ENROLLVALUES('290','1','349','4'); INSERTINTO ENROLLVALUES('480','1','348','4'); INSERTINTO ENROLLVALUES('480','1','410','2'); INSERTINTO ENROLLVALUES('450','1','473','2'); INSERTINTO ENROLLVALUES('730','1','473','3'); INSERTINTO ENROLLVALUES('480','2','473','0'); INSERTINTO ENROLLVALUES('290','1','548','2'); INSERTINTO ENROLLVALUES('730','1','558','3'); INSERTINTO ENROLLVALUES('730','1','649','4'); INSERTINTO ENROLLVALUES('480','1','649','4'); INSERTINTO ENROLLVALUES('450','1','654','4'); INSERTINTO ENROLLVALUES('450','2','548','1'); SELECT*FROM ENROLLCOURSESTUDENTTEACHERSECTIONENROLL4.检索系名为“Math”和“English”的课程信息SELECT*FROM COURSEWHERE department='Math'OR department='English'5.按字母顺序列出教师姓名和电话号码SELECT tname,phoneFROM TEACHERORDER BY tname6.检索电话号码不是以“257”打头的教师姓名和电话号码SELECT tname,phoneFROM TEACHERWHERE phone not like '257%'7.检索数学系所有成绩大于3的课程名、系名、学分SELECT cname,department,creditFROM COURSEWHERE cno IN(SELECT cnoFROM ENROLLWHERE grade>3 AND cno='730');8.检索没有选修任何课的学生姓名、学号SELECT sno,snameFROM STUDENTWHERE NOT EXISTS(SELECT*FROM ENROLLWHERE STUDENT.sno=ENROLL.sno);9.检索没有选修课程“Calculus Iv”的学生学号SELECT snoFROM STUDENTWHERE sno NOT IN(SELECT STUDENT.snoFROM COURSE,STUDENT,ENROLLWHERE cname='Calculus Iv'AND o=oAND STUDENT.sno=ENROLL.sno);*10.检索至少选修教师“Dr. Lowe”所开全部课程的学生学号SELECT DINSTINCT snoFROM ENROLL ENROLLXWHERE NOT EXISTS(SELECT *FROM ENROLL ENROLLYWHERE cno in(SELECT cnoFROM TEACHER,SECTION,WHERE tname='Dr.Lowe'AND TEACHER.tno=SECTION.tno)AND NOT EXISTS(SELECT*FROM ENROLL ENROLLZWHERE ENROLLZ.sno=ENROLLX.snoAND o=o))（修改数据验证，该老师教授课程号为730和500，只有学号148的学生同时选择了这两门课）SECTIONENROLL结果11.检索每门课学生登记的人数、相应的课程名、课程号、分组号SELECT DISTINCT secno,o,cname,sqtyFROM ENROLL,COURSE,SECTIONWHERE o=oAND o=o12.检索选修两门以上课程的学生姓名SELECT snameFROM STUDENTWHERE sno in (SELECT snoFROM ENROLLGROUP BY snoHAVING COUNT(*)>2)13.检索只有男生选修的课程和学生名SELECT cname,snameFROM COURSE,STUDENT,ENROLLWHERE STUDENT.sno=ENROLL.snoAND o=oAND o in(SELECT cnoFROM COURSEWHERE cno not in(SELECT DISTINCT cnoFROM STUDENT,ENROLLWHERE sex='F'and STUDENT.sno=ENROLL.sno))14.检索所有学生都选修的课程名、学生名、授课教师名、该生成绩SELECT cname,sname,tname,gradeFROM STUDENT,TEACHER,SECTION,ENROLL,COURSEWHERE o=oAND STUDENT.sno=ENROLL.snoAND TEACHER.tno=SECTION.tnoAND o=oAND SECTION.secno=ENROLL.seconAND o=(SELECT cnoFROM ENROLLGROUP BY cnoHAVING COUNT(*)=12)15.删去名为“Joe Adams”的所有记录DELETEFROM STUDENTWHERE sname='Joe Adams';SELECT*FROM STUDENT16.把教师“Scango”的编号改为“666”UPGRADE TEACHERSET tno='666'WHERE tname='Dr.Scango 'SELECT*FROM TEACHER17.统计教师“Engle”教的英语课的学生平均分SELECT AVG(grade)AVGFROM ENROLLWHERE grade in(SELECT gradeFROM TEACHER,SECTION,ENROLLWHERE tname='Dr.Engle'AND TEACHER.tno=SECTION.tnoAND o=oAND ENROLL.secno=SECTION.secno)18.统计各门课程的选课人数SELECT ame,COUNT(ENROLL.sno)FROM ENROLL,COURSEWHERE o=oGROUP BY ame;19.输出如下报表：学生名课程名教师名成绩SELECT sname学生名,cname课程名,tname教师名,grade成绩FROM ENROLL,STUDENT,COURSE,TEACHER,SECTIONWHERE ENROLL.sno=STUDENT.snoAND o=oAND ENROLL.secno=SECTION.secnoAND o=oAND SECTION.tno=TEACHER.tno*20.定义并验证触发器，当登记表增加一条新的记录时，自动在分组表中更新相应属性。