数据库实习指导书

1、实习指导书指导教师：赵友杰实习班级：信息工程2013实习人数：45人实习题目：学生信息管理系统的设计与开发实习工具：SQL Server 2008 和 JSP实习目的：使用JSP与SQL Server数据库，设计与开发学生信息管理系统，并在其中掌握数据库中SQL基本命令、视图、函数、触发器、存储过程、游标的创建和使用，及JSP系统的设计与开发。主要考核点：1. 数据库设计理念及步骤，ER图，关系数据库设计与优化等；2. 数据库实现相关技术，主要包括SQL基本命令、函数、触发器、存储过程、游标等；3. 系统实现，主要包括JSP连接及调用SQL数据库实习内容1. 系统主要需求（1）学生可以登录系统

2、，实现学生密码修改，学生基本信息查看与修改，成绩信息查看，挂科信息查看，自己的平均分排名显示等功能；（2）班主任可以登录系统，实现所有学生用户信息的查看、添加、删除和修改，学生基本信息的查看、添加、删除和修改，课程基本信息的查看、添加、删除和修改，学生成绩的查看、添加、删除和修改，平均分排名查看，以及教师基本信息的查看和修改；（3）管理员可以登录系统，实现所有用户的查看、添加、删除和修改。（4）当学生和教师用户被创建时（默认用户名为学号），触发器自动在学生或教师基本信息表中添加该信息（除学号外，其他为空）（5）当学生用户被删除时，触发器自动删除学生信息表，学生成绩表中该生信息2. 概念设计关系

3、数据库中至少包含以下实体及联系：（1） 用户实体：每个用户有自己的用户名、密码、用户类型等属性（2） 学生实体：每个学生有自己的学号（与用户名相同）、姓名、性别、出生年月日、籍贯、班级、学生电话、父母电话、电子邮箱等属性（3） 课程实体：每门课程有课程号、课程名、课程类型（必修还是选修）、学分等属性（4） 学生与课程之间应该有联系，一个学生可以选多门课程，一门课程可以被多位同学选，上课后应该有相应的成绩。（5） 班主任实体：每个班主任有自己的职工号、姓名、所任班级、所属学院、电话号码（6） 学生和班主任之间应该有联系，一个班主任应该可以带多名学生除以上实体和联系外，也可自行添加其他实体和联系。

4、对应所有实体和联系，画出ER图。3. 逻辑设计对应概念设计的ER图，设计相应的关系模式，至少应包含以下表：（1）用户表us（uid，upass，utype）注：用户类型应该至少有三种（2）学生表studentinfo（studno，）（3）课程表courseinfo（cno，）（3）成绩表（studno，cno，）（4）班主任表（teachid，）备注：如果可能，所有表都尽量设置相应的主键与外键。4. 物理设计注意将以下所有SQL命令最后保存在文件test_bak.sql中（方便系统的安装和移植）。（1） 使用SQL命令创建数据库test（2） 使用SQL命令创建以上所有表（3） 使用SQL命

5、令创建视图Vbadcourse，显示所有不及格的学生学号、课程号、课程名、学分、成绩（4） 使用SQL命令创建函数Fscore，输入某个学号，返回该学号对应的挂科总学分（该函数创建中需要使用游标）（5） 使用SQL命令创建视图Vscore，显示学号、平均分、总分、挂科总学分（其中挂科总学分需要调用Fscore函数）（6） 使用SQL命令创建触发器tri1，实现需求分析中的（4） (（4）当学生和教师用户被创建时（默认用户名为学号），触发器自动在学生或教师基本信息表中添加该信息（除学号外，其他为空）)（7） 使用SQL命令创建触发器tri2，实现需求分析中的（5）(（5）当学生用户被删除时，触发

6、器自动删除学生信息表，学生成绩表中该生信息)（8） 使用SQL命令创建存储过程proc1，当班主任需要添加成绩时，实现判断是否有该生的课程成绩，如果有则执行修改成绩，如果没有则执行添加成绩。 参考附件1-数据相关命令及TSQL编程5. 系统设计与实现（模块1，2，5必须做，其他3.4模块任选其一）（1） 登录模块（至少包含管理员，班主任，学生三个选项）（2） 管理员模块：用户表的查看、添加、删除和修改。（3） 学生模块：学生密码修改，学生基本信息查看与修改，成绩信息查看，挂科信息查看，自己的平均分排名显示等功能；（4） 班主任模块：学生用户信息的查看、添加、删除和修改，学生基本信息的查看、添加

7、、删除和修改，课程基本信息的查看、添加、删除和修改，学生成绩的查看、添加、删除和修改，平均分排名查看，以及教师基本信息的查看和修改；（5） 在JSP中测试调用存储过程，函数，触发器针对以上要求画出对应的功能模块图。6. 测试和维护（1） 模块基本功能是否正常完成（2） 数据溢出测试，输入较长字符是否有相应判断（3） 数据注入式攻击测试（4） 其他扩展实习：文件中电子表格“传感器数据.xls”，为西南林业大学物联网监测实时数据，请设计其数据库，实现对监测数据的分类存储，及实时更新，并设计jsp页面实现信息查询。参考附件1-数据相关命令及TSQL编程参考附件2-JSP连接SQL数据库及操作数据实习

8、总结的撰写：相关实习总结参考毕业论文的格式撰写。要求：数据库设计完成，jsp至少实现一次添加、一次删除、一次修改、一次查询。实习最后提交资料主要包含：taskkill /T /F /PID 3036 netstat -ano |findstr 3306（端口号） （1） 数据库设计和开发的所有代码（2） 规范的实习总结.doc文档提交格式：名为（学号_姓名）的文件夹，里面包含以上两部分（系统单独放在一个名为系统的文件夹中）1.E_R图2.功能模块图3.sql语句create database test;use test;create table us-创建用户表(uid varchar(20)

9、 primary key,-用户名=学号upass varchar(20),utype varchar(10);insert into us values('2011001','2011001','学生');insert into us values('2011002','2011001','学生');insert into us values('2011003','2011001','学生');insert into us values('2

10、011004','2011001','学生');insert into us values('2011005','2011001','学生');insert into us values('2011006','2011001','学生');insert into us values('2011007','2011001','学生');insert into us values('2011008',

11、'2011001','学生');insert into us values('2011009','2011001','学生');insert into us values('2011010','2011005','学生');insert into us values('2011011','2011088','学生');insert into us values('2011099','2011099

12、','管理员');insert into us values('2011100','2011099','管理员');insert into us values('2011101','2011099','管理员');insert into us values('2011102','2011099','管理员');insert into us values('2013116','2011097',&#

13、39;老师');insert into us values('2013117','2011097','老师');insert into us values('2013118','2011097','老师');insert into us values('2013119','2011097','老师');insert into us values('2013121','2013121','老师');

14、insert into us values('2013122','2013121','老师');insert into us values('2013123','2013121','老师');sp_password null,'123456','lisa'update us set upass='' where uid=''drop table usselect *from dbo.us create table studentinfo

15、( studno varchar(20) primary key, studname varchar(20) null, studsex varchar(20) null, stucsny varchar(20) null, stujiguan varchar(20) null, classid varchar(20) null, stuphone varchar(20) null, stuparentsphone varchar(20)null, email varchar(20) null, );-drop table studentinfodrop table studentinfoin

16、sert into studentinfo values('2011001','包就','男','1991/1/1','云南','1班','11799226230','#39;,'#39;);insert into studentinfo values('2011002','包好','女','1991/1/1','云南昆明','1班&

17、#39;,'12799226230','#39;,'#39;);insert into studentinfo values('2011003','包了','男','1991/1/1','云南','1班','#39;,'#39;,'#39;);insert into studentinfo values('201100

18、4','包去','女','1991/1/1','云南昆明','1班','#39;,'#39;,'#39;);insert into studentinfo values('2011005','包怕','男','1991/1/1','云南','1班','#39;,'18478683

19、685','#39;);insert into studentinfo values('2011006','包能','女','1991/1/1','云南','1班','#39;,'#39;,'#39;);insert into studentinfo values('2011007','陈想','男','199

20、2/1/2','贵州毕节','2班','#39;,'#39;,'#39;);insert into studentinfo values('2011008','邓画面','女','1993/1/3','云南','2班','#39;,'#39;,'#39;);insert

21、into studentinfo values('2011009','邓开卷','男','1994/1/4','贵州毕节','1班','#39;,'#39;,'#39;);insert into studentinfo values('2011010','包与','女','1991/1/1','云南','1班'

22、,'#39;,'#39;,'#39;);insert into studentinfo values('2011011','包慢','男','1991/1/1','云南昆明','1班','#39;,'#39;,'#39;);select *from studentinfocreate table coursei

23、nfo-课程表(courseid varchar(20) primary key,coursename varchar(20)not null,coursetype varchar(20)not null,-必修或选修coursecredit int not null-学分)insert into courseinfovalues('课程','C语言','选修','3');insert into courseinfovalues('课程','数据库','选修','3'

24、);insert into courseinfovalues('课程','英语','选修','3');insert into courseinfovalues('课程','思想','选修','3');select *from dbo.courseinfocreate table studscoreinfo(studno varchar(20) ,coursename varchar(20) primary key, studscore int not null - c

25、onstraint pk_S_C primary key(studno,coursename)-设置学号为外键 )drop table dbo.studscoreinfoinsert into studscoreinfovalues('2011001','数据库','69')insert into studscoreinfovalues('2011002','C语言','85')insert into studscoreinfovalues('2011003','C语言

26、9;,'76')insert into studscoreinfovalues('2011003','思想','99')insert into studscoreinfovalues('2011004','数据库','67')insert into studscoreinfovalues('2011005','数据库','63')insert into studscoreinfovalues('2011006','

27、;英语','67')insert into studscoreinfovalues('2011007','数据库','80')insert into studscoreinfovalues('2011008','数据库','36')insert into studscoreinfovalues('2011009','英语','81')insert into studscoreinfovalues('2011010'

28、;,'数据库','88')select *from studscoreinfocreate table teacherinfo( teachid varchar(20) primary key, teachname varchar(20) null, suorenclassid varchar(20) null, suosuxueyuan varchar(20) null, teachphone varchar(20) null )insert into teacherinfo values('2013116','李军','

29、;1班','计算机学院','#39;);insert into teacherinfo values('2013117','华夏','2班','园林学院','#39;);insert into teacherinfo values('2013118','李级','3班','土木学院','#39;);insert into teacherinfo va

30、lues('2013119','李虎','4班','林学院','#39;);insert into teacherinfo values('2013120','李小号','1班','计算机学院','#39;);insert into teacherinfo values('2013121','华浴霸','2班','园林学院','158

31、96332487');insert into teacherinfo values('2013122','李咳咳','3班','土木学院','#39;);insert into teachernfo values('2013123','李里','4班','林学院','#39;);select *from dbo.teacherinfodrop table teacherinfocreate vie

32、w Vbadcourse-创建视图Vbadcourse不及格的分数as select a.studno,b.courseid,b.coursename,b.coursecredit,c.studscorefrom dbo.studentinfo as a,dbo.courseinfo as b,dbo.studscoreinfo as cwhere a.studno=c.studno and b.coursename=c.coursename and c.studscore<60;drop view Vbadcourseselect *from Vbadcoursecreate func

33、tion Fscore(sno varchar(20)-计算总学分returns floatasbegindeclare studxuefen cursor -定义游标声明指向结果集的游标for select coursecredit from dbo.Vbadcourse where studno=snoopen studxuefen-打开游标declare sum int,j int ,i int -使用游标set i=0set sum=0set i=1fetch next from studxuefen into jwhile (FETCH_STATUS=0)-判断上一条语句取值是否成功

34、begin set sum=sum+j fetch next from studxuefen into sum set i=i+1endclose studxuefendeallocate studxuefen-释放游标return sum enddrop function Fscoreselect dbo.Fscore('2011003') as 总学分create view Vscore-创建视图Vscore计算平均分总分挂科总学分asselect a.studno as 学号,AVG(b.studscore) as 平均分,SUM(b.studscore) as 总分,d

35、bo.Fscore('2011003') as 挂科总学分from dbo.studentinfo as a,dbo.studscoreinfo as bwhere a.studno=b.studnogroup by a.studnodrop view Vscoreselect *from Vscorecreate trigger tri1-创建触发器tri1，实现虚拟表的插入操作on us-将触发器安装在用户表us上for insert asbeginprint '有新的用户被添加'declare sno varchar(20),type varchar(10

36、)select sno=uid,type=utype from insertedif (type='学生')-借助inserted虚拟表添加学生用户基本信息 -执行事务，新行同时添加到inserted表和触发器中 insert into dbo.studentinfo(studno) values(sno)-单独添加学号列，其他为空if (type='老师')-借助inserted虚拟表添加教师用户基本信息 insert into dbo.teacherinfo(teachid) values(sno)-单独添加教师编号列，其他为空endgo测试语句insert

37、 into us values('2013120','2011011','老师');insert into us values('2011012','2011011','学生');insert into us values('2011013','2011011','学生');select *from dbo.teacherinfoselect *from dbo.studentinfoselect *from usdrop trigger tri1 -删除触发器execute sp_helptext tri1-查看触发器的内容execute sp_depends tri1-查看触发器的依赖create trig