蘇州大學(xué)數(shù)據(jù)庫課程設(shè)計報告

《數(shù)據(jù)庫課程設(shè)計》實驗報告專業(yè)

計算機學(xué)技術(shù)年級15級姓名學(xué)號指導(dǎo)老師使用日期

一：概述：SQL二：需求分析系統(tǒng)概述教務(wù)系統(tǒng)的圖

數(shù)據(jù)庫表（1）Student表

男

（2）Teacher表

（3）Class表

班級編號

于

（4）College表

專業(yè)編號

（5）

于0

男

（6）Major表

于0

（7）

于

于

（8）Teaching表

于

（9）TPlan表

于

于0

（10）CourseClass

（11）TempScore

intbit

于0于于于

是重

于于

于

于

（12）表

int

于0于于于

于于

于

于

（13）RebuildScore表

于0

于于于

于于

于

于

（14）CourseSelect表

于

（15）ChangeLog

于0于0于0

于0于0

于0于0于0于0于0

于0于0

于0于0

三：程序代碼建表代碼表/Teacher(varchar(20keyvarchar(20),(10check(in('男,')),varchar(20),varchar(20)表/Secretary(

varchar(20keyvarchar(20),varchar(10)(in(','女')),varchar),

(20))表/College(varchar(20keyvarchar(20),varchar(20null,int,key)Secretary())表/Major(varchar(20varchar(20),intcheck(>=0),

--班級數(shù)varchar(20notkey)College(college_id))表/

Class(varchar(20varchar(20),intcheck(0varchar(20null,varchar(10

key)Major))表/Student(varchar(20keyvarchar(20),varchar(10checkin('男,'女varchar(20null,varchar(20null,datedatevarchar(20null,

varchar(20notkey)Classkey)Majorkey)College(college_id))表/Course(varchar(20keyvarchar),int,int)表/(varchar(20null,varchar(20null,varchar(20not

varchar(20),varchar),

varchar(10checkin('周,'','周

varchar(20null,key,class_id),key)Coursekey)Classkey)Teacher(teacher_id),key)Major))表/TPlan(varchar(20null,varchar(20null,varchar(10check(term>0),

varchar(20notintkey,key)Majorkey)Course

key)College(college_id))表/(varchar(20null,varchar(20null,int,bitbit

key,course_id),keyStudentstu_idkey)Course))表/Teaching(varchar(20notvarchar(20),varchar(20null,varchar(20null,int,

varchar(20),key,class_idkey)Teacher(teacher_id),key)Classkey)Coursekey)Major))表/TempScore(varchar(20null,varchar(20null,varchar(20null,int,bitintcheckscore_ordtime0--intcheck>=

intcheckscore_termial0--intcheckscore_expe>=0),

intcheck(>=0),--最終總intcheckcourse_credit0float

key,course_id),keyStudentstu_idkey)Coursekey)Class))表/(varchar(20null,varchar(20null,varchar(20null,int,int

intcheckscore_ordtime0--intcheck>=

intcheckscore_termial0--intcheckscore_expe>=0),

intcheck(>=0),--最終總intcheckcourse_credit0floatkey,course_id,rebuild_id),keyStudentstu_id

key)Coursekey)Class))表*/(varchar(20null,varchar(20null,varchar(20null,int,intcheckscore_ordtime0intcheck>=intcheckscore_termial0intcheckscore_expe>=0),intcheck(>=0),intcheckcourse_credit0floatkey,course_id),keyStudentstu_idkey)Coursekey)Class))

表/ChangeLog(varchar(20null,varchar(20null,date

int(>=0),intcheckscore_pri_mid>=0),int(>=0),intcheck(0),intcheck(>=0),int(>=0),float,

-int(>=0),intcheckscore_cur_mid>=0),int(>=0),intcheck(0),intcheck(>=0),int(>=0),float,key,course_id),

keyStudentstu_idkey)Course)2.觸發(fā)器代碼FinalScore進行除到中*/varchar20),varchar(20),varchar(20),intint,int,int,intint,=stu_id,=,@class_id=class_id,@termterm,=score_mid,

=,=score_expe,=course_credit,=ChangeLog(,course_id,,,,,score_cur_final,cur_GPA,update_time)values(,@score_ordtime,,@score_expe,@course_credit@GPA,varchar20),varchar(20),varchar(20),@termintint,int,,@score_expeintintfloat

=stu_id,=,@class_id=class_id,@termterm,=score_mid,=,=score_expe,=course_credit,=from=(course_id,,,,,score_cur_final,,update_time)values(,@score_ordtime,,@score_expe,,,GETDATE())

=,=,score_cur_termial=@score_termial,=,==,=,=GETDATE=@stu_idand==stu_id,=,@class_id=class_id,@termterm,=score_mid,=,=score_expe,=course_credit,==@score_ordtime=@score_termial,=,=,,

=@GPA=@stu_idand=varchar20),varchar(20),varchar(20),@termintint,int,,@score_expeintintfloat=stu_id,=,@class_id=class_id,@termterm,=score_mid,=,=score_expe,=course_credit,=from=

(course_id,,score_cur_termial,score_cur_expe,,cur_course_credit,cur_GPA)values(GETDATE())===null,=====GETDATE()stu_idand==stu_id,=,@class_id=class_id,@termterm,=score_mid,=,=score_expe

,=course_credit,=,=,score_pri_expe=@score_expe,=,=@GPA=@stu_idand=中/varchar20),varchar(20),varchar(20),@termintint,int,

int,@score_expe,int,float=stu_id,=,@class_id=class_id,@termtermrebuild_id,=score_ordtime,score_mid,=score_termial,@score_expe=score_expe,score_final,=course_credit,=GPA=score_final=@stu_idand=(<)=,=,=@score_mid,=

,score_expe@score_expe=@score_final,=,GPA==@stu_idand=varchar20),varchar(20),varchar(20),int,int,int,int,int,intintfloat

=stu_id,=,@class_id=class_id,@termtermrebuild_id,=score_ordtime,score_mid,=score_termial,@score_expe=score_expe,score_final,=course_credit,=GPA=score_final=@stu_idand=(<)=@term,@score_ordtime=@score_mid,,@score_expe=@score_final,=,GPA==@stu_idand=

U_JWMSselect,insertupdate,deleteontoU_JWMS;select,insertupdate,deleteonCollegetoU_JWMSselect,insertupdate,deleteonCoursetoU_JWMS;select,insertupdate,deleteonCourseClassto;,insert,update,deleteon;select,insertupdate,deleteonFinalScoreU_JWMS;select,insertupdate,deleteontoU_JWMS;,insert,update,deleteon;select,insertupdate,deleteonSecretaryU_JWMS;select,insertupdate,deleteonStudenttoU_JWMSselect,insertupdate,deleteonTeachertoU_JWMSselect,insertupdate,deleteonTeaching;select,insertupdate,deleteonTempScoreU_JWMS;select,in