數(shù)據(jù)庫實習指導書

1、實習指導書指導教師：趙友杰實習班級：信息工程2013實習人數(shù)：45人實習題目：學生信息管理系統(tǒng)的設計與開發(fā)實習工具：SQL Server 2008 和 JSP實習目的：使用JSP與SQL Server數(shù)據(jù)庫，設計與開發(fā)學生信息管理系統(tǒng)，并在其中掌握數(shù)據(jù)庫中SQL基本命令、視圖、函數(shù)、觸發(fā)器、存儲過程、游標的創(chuàng)建和使用，及JSP系統(tǒng)的設計與開發(fā)。主要考核點：1. 數(shù)據(jù)庫設計理念及步驟，ER圖，關系數(shù)據(jù)庫設計與優(yōu)化等；2. 數(shù)據(jù)庫實現(xiàn)相關技術，主要包括SQL基本命令、函數(shù)、觸發(fā)器、存儲過程、游標等；3. 系統(tǒng)實現(xiàn)，主要包括JSP連接及調(diào)用SQL數(shù)據(jù)庫實習內(nèi)容1. 系統(tǒng)主要需求（1）學生可以登錄系統(tǒng)

2、，實現(xiàn)學生密碼修改，學生基本信息查看與修改，成績信息查看，掛科信息查看，自己的平均分排名顯示等功能；（2）班主任可以登錄系統(tǒng)，實現(xiàn)所有學生用戶信息的查看、添加、刪除和修改，學生基本信息的查看、添加、刪除和修改，課程基本信息的查看、添加、刪除和修改，學生成績的查看、添加、刪除和修改，平均分排名查看，以及教師基本信息的查看和修改；（3）管理員可以登錄系統(tǒng)，實現(xiàn)所有用戶的查看、添加、刪除和修改。（4）當學生和教師用戶被創(chuàng)建時（默認用戶名為學號），觸發(fā)器自動在學生或教師基本信息表中添加該信息（除學號外，其他為空）（5）當學生用戶被刪除時，觸發(fā)器自動刪除學生信息表，學生成績表中該生信息2. 概念設計關系

3、數(shù)據(jù)庫中至少包含以下實體及聯(lián)系：（1） 用戶實體：每個用戶有自己的用戶名、密碼、用戶類型等屬性（2） 學生實體：每個學生有自己的學號（與用戶名相同）、姓名、性別、出生年月日、籍貫、班級、學生電話、父母電話、電子郵箱等屬性（3） 課程實體：每門課程有課程號、課程名、課程類型（必修還是選修）、學分等屬性（4） 學生與課程之間應該有聯(lián)系，一個學生可以選多門課程，一門課程可以被多位同學選，上課后應該有相應的成績。（5） 班主任實體：每個班主任有自己的職工號、姓名、所任班級、所屬學院、電話號碼（6） 學生和班主任之間應該有聯(lián)系，一個班主任應該可以帶多名學生除以上實體和聯(lián)系外，也可自行添加其他實體和聯(lián)系。

4、對應所有實體和聯(lián)系，畫出ER圖。3. 邏輯設計對應概念設計的ER圖，設計相應的關系模式，至少應包含以下表：（1）用戶表us（uid，upass，utype）注：用戶類型應該至少有三種（2）學生表studentinfo（studno，）（3）課程表courseinfo（cno，）（3）成績表（studno，cno，）（4）班主任表（teachid，）備注：如果可能，所有表都盡量設置相應的主鍵與外鍵。4. 物理設計注意將以下所有SQL命令最后保存在文件test_bak.sql中（方便系統(tǒng)的安裝和移植）。（1） 使用SQL命令創(chuàng)建數(shù)據(jù)庫test（2） 使用SQL命令創(chuàng)建以上所有表（3） 使用SQL命

5、令創(chuàng)建視圖Vbadcourse，顯示所有不及格的學生學號、課程號、課程名、學分、成績（4） 使用SQL命令創(chuàng)建函數(shù)Fscore，輸入某個學號，返回該學號對應的掛科總學分（該函數(shù)創(chuàng)建中需要使用游標）（5） 使用SQL命令創(chuàng)建視圖Vscore，顯示學號、平均分、總分、掛科總學分（其中掛科總學分需要調(diào)用Fscore函數(shù)）（6） 使用SQL命令創(chuàng)建觸發(fā)器tri1，實現(xiàn)需求分析中的（4） (（4）當學生和教師用戶被創(chuàng)建時（默認用戶名為學號），觸發(fā)器自動在學生或教師基本信息表中添加該信息（除學號外，其他為空）)（7） 使用SQL命令創(chuàng)建觸發(fā)器tri2，實現(xiàn)需求分析中的（5）(（5）當學生用戶被刪除時，觸發(fā)

6、器自動刪除學生信息表，學生成績表中該生信息)（8） 使用SQL命令創(chuàng)建存儲過程proc1，當班主任需要添加成績時，實現(xiàn)判斷是否有該生的課程成績，如果有則執(zhí)行修改成績，如果沒有則執(zhí)行添加成績。 參考附件1-數(shù)據(jù)相關命令及TSQL編程5. 系統(tǒng)設計與實現(xiàn)（模塊1，2，5必須做，其他3.4模塊任選其一）（1） 登錄模塊（至少包含管理員，班主任，學生三個選項）（2） 管理員模塊：用戶表的查看、添加、刪除和修改。（3） 學生模塊：學生密碼修改，學生基本信息查看與修改，成績信息查看，掛科信息查看，自己的平均分排名顯示等功能；（4） 班主任模塊：學生用戶信息的查看、添加、刪除和修改，學生基本信息的查看、添加

7、、刪除和修改，課程基本信息的查看、添加、刪除和修改，學生成績的查看、添加、刪除和修改，平均分排名查看，以及教師基本信息的查看和修改；（5） 在JSP中測試調(diào)用存儲過程，函數(shù)，觸發(fā)器針對以上要求畫出對應的功能模塊圖。6. 測試和維護（1） 模塊基本功能是否正常完成（2） 數(shù)據(jù)溢出測試，輸入較長字符是否有相應判斷（3） 數(shù)據(jù)注入式攻擊測試（4） 其他擴展實習：文件中電子表格“傳感器數(shù)據(jù).xls”，為西南林業(yè)大學物聯(lián)網(wǎng)監(jiān)測實時數(shù)據(jù)，請設計其數(shù)據(jù)庫，實現(xiàn)對監(jiān)測數(shù)據(jù)的分類存儲，及實時更新，并設計jsp頁面實現(xiàn)信息查詢。參考附件1-數(shù)據(jù)相關命令及TSQL編程參考附件2-JSP連接SQL數(shù)據(jù)庫及操作數(shù)據(jù)實習

8、總結的撰寫：相關實習總結參考畢業(yè)論文的格式撰寫。要求：數(shù)據(jù)庫設計完成，jsp至少實現(xiàn)一次添加、一次刪除、一次修改、一次查詢。實習最后提交資料主要包含：taskkill /T /F /PID 3036 netstat -ano |findstr 3306（端口號） （1） 數(shù)據(jù)庫設計和開發(fā)的所有代碼（2） 規(guī)范的實習總結.doc文檔提交格式：名為（學號_姓名）的文件夾，里面包含以上兩部分（系統(tǒng)單獨放在一個名為系統(tǒng)的文件夾中）1.E_R圖2.功能模塊圖3.sql語句create database test;use test;create table us-創(chuàng)建用戶表(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','貴州畢節(jié)','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','貴州畢節(jié)','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('課程','數(shù)據(jù)庫','選修','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','數(shù)據(jù)庫','69')insert into studscoreinfovalues('2011002','C語言','85')insert into studscoreinfovalues('2011003','C語言

26、9;,'76')insert into studscoreinfovalues('2011003','思想','99')insert into studscoreinfovalues('2011004','數(shù)據(jù)庫','67')insert into studscoreinfovalues('2011005','數(shù)據(jù)庫','63')insert into studscoreinfovalues('2011006','

27、;英語','67')insert into studscoreinfovalues('2011007','數(shù)據(jù)庫','80')insert into studscoreinfovalues('2011008','數(shù)據(jù)庫','36')insert into studscoreinfovalues('2011009','英語','81')insert into studscoreinfovalues('2011010'

28、;,'數(shù)據(jù)庫','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-創(chuàng)建視圖Vbadcourse不及格的分數(shù)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-創(chuàng)建視圖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-創(chuàng)建觸發(fā)器tri1，實現(xiàn)虛擬表的插入操作on us-將觸發(fā)器安裝在用戶表us上for insert asbeginprint '有新的用戶被添加'declare sno varchar(20),type varchar(10

36、)select sno=uid,type=utype from insertedif (type='學生')-借助inserted虛擬表添加學生用戶基本信息 -執(zhí)行事務，新行同時添加到inserted表和觸發(fā)器中 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 -刪除觸發(fā)器execute sp_helptext tri1-查看觸發(fā)器的內(nèi)容execute sp_depends tri1-查看觸發(fā)器的依賴create trig