學生信息管理系統(tǒng)SQL數(shù)據(jù)庫技術(shù)

1、 四個表的E-R實體模型圖分析：教師學生 學教 1 N N M這四個表的總的實體-關(guān)系圖：班級號學號號學生信息表出生日期號學號號性別號出生日期號教師姓名號成績表號部門號 成績號教師信息表課程名稱號課程號號 職稱號教師編號號課程表設(shè)計數(shù)據(jù)表：通過E-R圖分析，現(xiàn)在已經(jīng)對數(shù)據(jù)庫有一個很清楚的認識了。 在此學生成績信息中有4個表需要建立學生信息表（student）包括學號（sno）、姓名（sname）、性別（Ssex）、班級（class）、出生日期（sbirth）教師信息表( teacher)包括教師編號（tno）、姓名（tname）、性別（Tsex）、部門（depart）、職稱（prof）、出生日

2、期（tbirth）成績表（score）包括學號（sno）、課程號（cno）、成績（degree）課程信息表（course）包括課程號（cno）、課程名稱（cname）、教師編號（tno） 五、表結(jié)構(gòu) 在teacher表中，以教師編號tno為主鍵，對其進行惟一性約束。在Course表中，以課程號為主鍵，對其進行惟一性約束。 1、Student表：snoChar(8)不允許為空snameChar(10)不允許為空SsexChar(2)不允許為空sbirthDatetime(8)允許為空classChar(4)不允許為空typeChar(7)允許為空score表結(jié)構(gòu)：snoChar(8)不允許為空c

3、noChar(5)不允許為空degreeFloat(8)不允許為空Course表：以課程號為主鍵，通過這個主鍵來對這個表進行唯一性約束。cno（主鍵）Char(5)不允許為空cnamevarchar(10)不允許為空tnochar(3)不允許為空Teacher表結(jié)構(gòu)：Tno（主鍵）Char(5)不允許為空tnamevarchar(10)不允許為空departVarchar(8)不允許為空tsexChar(2)不允許為空tbirthdatatime允許為空profChar(6)允許為空創(chuàng)建規(guī)則（1）、創(chuàng)建一個degree_rule規(guī)則create rule degree_rule asvalue

4、s>0把此規(guī)則綁定到score表中degree列exec sp_bindrule 'degree_rule','score.degree'在向成績表中添加記錄時，如果成績degree<0，則插入不成功。(2)、創(chuàng)建一個tel_rule規(guī)則create rule tel_ruleas value like '0-9 0-9 0-9 0-9 0-9 0-9 0-9 0-9'把此規(guī)則綁定到teacher表中tel列exec sp_bindrule 'tel_rule','teacher.tel'在向教師表中添

5、加記錄時，如果電話號碼不是0-9的數(shù)字，則插入不成功。10、創(chuàng)建存儲過程（1）、創(chuàng)建一個存儲過程，來顯示成績表中的課程號在課程表中并且所任教師性別為男、所在部門是計算機系的成績表中的列create proc student_11as select * from score where cno in (select cno from course ,teacher where course.tno=teacher.tno and depart='計算機系' and tsex='男')調(diào)用此存儲過程Execstudent_11 （2）、創(chuàng)建一個帶輸入?yún)?shù)的存儲過程，

6、調(diào)用此存儲過程時，給出一個學生名，顯示出此學生的學號，姓名，所學課程號，課程名稱和對應(yīng)的成績create proc student_namesname varchar(10)as select student.sno,sname,o,degree cname from student,score,course where student.sno=score.sno and o=o and sname=sname調(diào)用此存儲過程，（此例是輸出姓名為歷史的學生的信息）exec student_name '歷史'（3）、創(chuàng)建一個存儲過程，在執(zhí)行此存儲過程時，如果沒有給出參數(shù)（學生姓名）

7、，則輸入全部的學生的學號，姓名，班級，任課教師編號及其姓名，所學課程名稱和成績，如果有，則顯示此學生的以上信息。exec student_teacher（沒有實參） exec student_teacher '歷史' （查詢姓名為歷史的學生的選課信息和成績）（4）、創(chuàng)建一個存儲過程，傳遞一個學生姓名，先判斷此學生是否有郵箱，如果有，則顯示此學生的姓名，郵箱地址，學號，班級；如果沒有的話，輸出此句話'the semail is empty'create proc student_emailsname varchar(10)asbegin if (select se

8、mail from student where sname=sname) is nullbegin print'the semail is empty' endelse select sname,semail,sno,class from student where sname=sname end調(diào)用此存儲過程 exec student_email 'super'當給出姓名的那個學生沒有郵箱地址時,則會顯示如下內(nèi)容。 exec student_email dfdf'11、觸發(fā)器 （1）、創(chuàng)建一個觸發(fā)器，來檢查學生的郵箱地址是否相同，如果相同，輸出'

9、;inserting fail'，并且回滾事務(wù)；如果不相同，則插入成功。create trigger studentinserton studentafter insertas if (select semail from inserted where semail in (select semail from student) is not nullbegin print 'inserting fail' rollback transactionendelse print'insering succeed'向?qū)W生信息表中插入一條記錄，檢驗是否成功插入i

10、nsert into student values('114','lengbing','女','1985-12-12','11','lengbingssh','一般')（2）、在成績表中建立一個觸發(fā)器，當向表中添加記錄時，此學生的成績都乘以1.2create trigger scoreupdate on scoreafter insertas update score set degree=degree*1.2 from score where sno in (select sno

11、from inserted )向表中插入一條記錄，檢驗觸發(fā)器是否有用。insert into score values('108','01','56')（3）、在成績表建立一個觸發(fā)器，在向表中插入記錄時，檢驗插入的課程號是否在課程表中的課程號的范圍之內(nèi)。如果在，則插入成功；否則，提示信息'沒有這門課程'，回滾事務(wù)。create trigger course_scoreon scoreafter insertas if (select cno from inserted where cno in(select cno from co

12、urse) is null begin print'沒有這門課程' rollback transaction end向表中添加一條記錄，進行驗證。insert into score values('108','06','60')12、自定義函數(shù)（1）、創(chuàng)建一個用戶自定義函數(shù)，輸出與指定的學生同班的學生個數(shù)create function studentcount(sno char(5)returns intbegineclare counter intselect counter=count(*) from student wher

13、e class=(select class from student where sno=sno)return counterend調(diào)用此自定義函數(shù)（本例是查找與學號102同班的學生個數(shù)）declare a intset a=dbo.studentcount('102')print convert(char(3),a)（2）、創(chuàng)建一個用戶自定義函數(shù)，用于輸出同一個班級中的學生信息create function studentclass(class char(5)returns tablereturn(select * from student where class=class

14、)調(diào)用自定義函數(shù)（本例是輸出12班的學生信息）select * from studentclass('12')（3）、創(chuàng)建一個自定義函數(shù)，把某一學生所學課程名稱，課程號及其成績插入一個臨時表中顯示出來。create function studentscore(sno varchar(5)returns student_score table(sno char(5), sname varchar(10), cno char(5), cname varchar(10), degree float)begin insert student_scoreselect student.sno,sname,o,cname,degree from student,score,coursewhere student.sno=score.sno and o=o and student.sno=sno returnend查看學號為103的學生的各科成績。select * from studentscore('103')13、建立索引（1）、在學生信息表郵箱列上創(chuàng)建一個惟一性