數(shù)據(jù)庫實習(xí)指導(dǎo)書

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

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

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

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

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

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

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

8、總結(jié)的撰寫：相關(guān)實習(xí)總結(jié)參考畢業(yè)論文的格式撰寫。要求：數(shù)據(jù)庫設(shè)計完成，jsp至少實現(xiàn)一次添加、一次刪除、一次修改、一次查詢。實習(xí)最后提交資料主要包含：taskkill /T /F /PID 3036 netstat -ano |findstr 3306（端口號） （1） 數(shù)據(jù)庫設(shè)計和開發(fā)的所有代碼（2） 規(guī)范的實習(xí)總結(jié).doc文檔提交格式：名為（學(xué)號_姓名）的文件夾，里面包含以上兩部分（系統(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,-用戶名=學(xué)號upass varchar(20),utype varchar(10);insert into us values('2011001','2011001','學(xué)生');insert into us values('2011002','2011001','學(xué)生');insert into us values('2011003','2011001','學(xué)生');insert into us values('2

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

11、'2011001','學(xué)生');insert into us values('2011009','2011001','學(xué)生');insert into us values('2011010','2011005','學(xué)生');insert into us values('2011011','2011088','學(xué)生');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-學(xué)分)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)-設(shè)置學(xué)號為外鍵 )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班','計算機學(xué)院','#39;);insert into teacherinfo values('2013117','華夏','2班','園林學(xué)院','#39;);insert into teacherinfo values('2013118','李級','3班','土木學(xué)院','#39;);insert into teacherinfo va

30、lues('2013119','李虎','4班','林學(xué)院','#39;);insert into teacherinfo values('2013120','李小號','1班','計算機學(xué)院','#39;);insert into teacherinfo values('2013121','華浴霸','2班','園林學(xué)院','158

31、96332487');insert into teacherinfo values('2013122','李咳咳','3班','土木學(xué)院','#39;);insert into teachernfo values('2013123','李里','4班','林學(xué)院','#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)-計算總學(xué)分returns floatasbegindeclare studxuefen cursor -定義游標(biāo)聲明指向結(jié)果集的游標(biāo)for select coursecredit from dbo.Vbadcourse where studno=snoopen studxuefen-打開游標(biāo)declare sum int,j int ,i int -使用游標(biāo)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-釋放游標(biāo)return sum enddrop function Fscoreselect dbo.Fscore('2011003') as 總學(xué)分create view Vscore-創(chuàng)建視圖Vscore計算平均分總分掛科總學(xué)分asselect a.studno as 學(xué)號,AVG(b.studscore) as 平均分,SUM(b.studscore) as 總分,d

35、bo.Fscore('2011003') as 掛科總學(xué)分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='學(xué)生')-借助inserted虛擬表添加學(xué)生用戶基本信息 -執(zhí)行事務(wù)，新行同時添加到inserted表和觸發(fā)器中 insert into dbo.studentinfo(studno) values(sno)-單獨添加學(xué)號列，其他為空if (type='老師')-借助inserted虛擬表添加教師用戶基本信息 insert into dbo.teacherinfo(teachid) values(sno)-單獨添加教師編號列，其他為空endgo測試語句insert

37、 into us values('2013120','2011011','老師');insert into us values('2011012','2011011','學(xué)生');insert into us values('2011013','2011011','學(xué)生');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