學生成績管理系統(tǒng)數(shù)據(jù)庫設(shè)計文檔_(全)

1、. “學生成績管理 ”數(shù)據(jù)庫設(shè)計文檔 0 、前言 （一些必要的說明。） 0.1 數(shù)據(jù)庫說明 數(shù)據(jù)庫名 ： PXSCJ 邏輯名稱 ：學生成績數(shù)據(jù)庫 數(shù)據(jù)文件 ： PXSCJ.mdf 日志文件 ： PXSCJ_Log 登錄名 ： admin, 密碼 ：123456 0.2 表命名說明 Cjb: 成績表 ，保存選課信息 Cxb:查詢表 ，記錄 boolean值對應(yīng)信息 ，1 代表男 ， 0 代表女 。 Kcb: 課程表 。 Tjb:統(tǒng)計表 ，統(tǒng)計成績段分布。 Xsb:學生表 。 Yhb: 用戶表 ，保存系統(tǒng)用戶信息。 Jsb: 教師表 。 Skb: 授課表 ，記錄授課信息 。 0.3 系統(tǒng)功能模塊圖

2、 .專業(yè) .專注. . 1、 需求分析階段 說明 ：學生成績管理系統(tǒng)需要實現(xiàn)以下功能 ：一個學生可以選修多門課程 ，一門課程 可以由多個學生選修 ，學生選修一門課會有一個成績 。一個教師可以教授多個班級 ，一個 教師也可以教授多門課程 ，一個班級有多個學生 ，一門課程也可以由多個老師來上 ，一個 老師給一個班級上一門課有確定的時間和地點 。不同的用戶根據(jù)身份不同擁有不同的權(quán) 限。 （1） 數(shù)據(jù)流圖 老師 - 成績管理 ，學生信息管理 ，權(quán)限管理 - 學生成績管理系統(tǒng) 成績查詢 - 學生 （要求 ：用 visio 實現(xiàn)第一層數(shù)據(jù)流圖 ，第二層數(shù)據(jù)流圖 ，第三層數(shù)據(jù)流圖 ） p121 成績管理 信

3、息管理 學生成 成績查詢 老師 績管理 學生 權(quán)限管理 系統(tǒng) 修改密碼 第一層數(shù)據(jù)流圖 .專業(yè) .專注. . F1 用戶表 1、修改密碼 修改 學生 查詢返回 2、查詢成績 F2 成績表 （ 2 ） 數(shù)據(jù)字典 （每個實體的詳細說明 ） 數(shù)據(jù)庫編號 ： pxscj 基表編號 ： f1 基表英文名稱： yhb 基表中文名稱：用戶表 3、用戶管理 F3 學生表 F4 教師表 F5 課程表4、課程管理 F7 授課表 老師 F6 成績表5、成績管理 6、授課管理 第二層數(shù)據(jù)流圖 第三層數(shù)據(jù)流圖（略） 字段編號英文字段名中 文 字 段字段類型備注 名 1Yhbh用戶編號Char(6) .專業(yè) .專注. .

4、 2 yhm 用戶名 Char(8) 3 sf 身份 Varcha （20 ） 4 mm 密碼 Char(6) 5 bz 備注 Varcha （50 ） 說明 ： 數(shù)據(jù)庫編號 ： pxscj 基表編號 ： f2 基表英文名稱： cjb 基表中文名稱：成績表 字段編號英文字段名中 文 字 段字段類型備注 名 1 Xh 學號 Char(6) 2 Kch 課程號 Char(3) 3 Cj 成績 Int 說明 ： xh 參照學生表的 xh;kch 參照課程表的 kch;cj 介于 0-100 之間 。 數(shù)據(jù)庫編號 ： pxscj 基表編號 ： f3 .專業(yè) .專注. . 基表英文名稱： kcb 基表中

5、文名稱：課程表 字段編號英文字段名中 文 字 段字段類型備注 名 1 Xh 學號 Char(6) 2 XM 姓名 Char(8) 3 XB 性別 Bit 4 CSSJ 出生時間 Datetime 5 Bj 班級 Varchar(50 ) 6 Zy 專業(yè) Char(12) 7 ZXH 總學分 Int 8 Lxfs 聯(lián)系方式 Varchar(50 ) 9 zp 照片 Varbinary( MAX) 10 BZ Varchar(50 0) 說明 ： xh 參照學生表的 xh;kch 參照課程表的 kch;cj 介于 0-100 之間 。 xb 參照查 詢表的 xb 數(shù)據(jù)庫編號 ： pxscj .專業(yè)

6、 .專注. . 基表編號 ： f4 基表英文名稱： jsb 基表中文名稱：教師表 字段編號英文字段名中 文 字 段字段類型備注 名 1 Jsbh 教師編號 Char(6) 2 Jsxm 教師姓名 Char(8) 3 zy 專業(yè) Varchar(30 ) 4 Fy 分院 Varchar(30 ) 5 Zc 職稱 Varchar(20 ) 6 Zc 職務(wù) Varchar(20 ) 7 Bz Varchar(50 ) 說明 ： xh 參照學生表的 xh;kch 參照課程表的 kch;cj 介于 0-100 之間 。 數(shù)據(jù)庫編號 ： pxscj 基表編號 ： f5 基表英文名稱： kcb .專業(yè) .專

7、注. . 基表中文名稱 ：課程表 字段編號 英文字段名 中文字段 字段類型 備注 名 1 KCH 課程號 Char(3) 2 KCM 課程名 Char(16) 3 Cj 成績 Int 說明 ： xh 參照學生表的 xh;kch 參照課程表的 kch;cj 介于 0-100 之間 。 數(shù)據(jù)庫編號 ： pxscj 基表編號 ： f6 基表英文名稱： cjb 基表中文名稱：課程表 字段編號英文字段名中 文 字 段字段類型備注 名 1 Xh 學號 Char(6) 2 Kch 課程號 Char(3) 3 KKXQ Tinyint 4 XS 學生 tinyint 5 XF 學分 tinyint 說明 ：

8、xh 參照學生表的 xh;kch 參照課程表的 kch; .專業(yè) .專注. . 數(shù)據(jù)庫編號 ： pxscj 基表編號 ： f7 基表英文名稱 ： skb 基表中文名稱 ： 授課表 字段編號 英文字段名 中文字段 字段類型 備注 名 1 Jsbh 教師編號 Char(6) 2 Kch 課程號 Char(3) 3 Bj 班級 Varchar(50 ) 4 Sj 時間 Varchar(50 ) 5 dd 地點 Varchar(50 ) 說明 ： jsbh 參照教師表的 jsbh;kch 參照課程表的 kch; 數(shù)據(jù)庫編號 ： pxscj 基表編號 ： f8 基表英文名稱： CXB 基表中文名稱： 查

9、詢表 字段編號英文字段名中 文 字 段字段類型備注 名 .專業(yè) .專注. . 1XB性別Bit 2XBM性別名Char(4) 說明 ： 2、 概念設(shè)計階段 （ 1） 分 ER圖 （ 兩個分 ER 圖，1）學生和課程 ，2）教師，課程，班級） 學生 教師 m m p 選課 授課 課程 nn 課程 班級 （ 2） 總 ER圖 （ 由分 ER 圖畫出總 ER 圖） 學生教師 m m np 選課課程授課 n 班級 3、 邏輯設(shè)計階段 （ 1）表關(guān)系圖 .專業(yè) .專注. . （看是否可以畫出） （ 2）表結(jié)構(gòu)圖 Xsb 結(jié)構(gòu) Kcb 結(jié)構(gòu) Cjb 結(jié)構(gòu) Yhb 結(jié)構(gòu) .專業(yè) .專注. . Jsb 結(jié)構(gòu)

10、Skb 結(jié)構(gòu) Tjb 結(jié)構(gòu) Cxb 結(jié)構(gòu) （ 3）表優(yōu)化 （判斷每個關(guān)系是否達到3NF 要求 ，如果沒有達到，則繼續(xù)規(guī)范 ） 按照 （ 1nf-2nf-3nf進行判斷和優(yōu)化） Cjb（ xh,kch,cj ） 碼：（ xh,kch ） .專業(yè) .專注. . 非主屬性 ： cj 因為 cj 完全依賴于 （xh,kch ）， 所以屬于2NF 因為不存在傳遞函數(shù)依賴，所以屬于3NF Cxb(xb,xbm) 碼：（ xbm ） 非主屬性 ： xb 因為 xb 完全依賴于 （ kch ）， 所以屬于 2NF 因為不存在傳遞函數(shù)依賴，所以屬于3NF Jsb(jsbh,jsxm,zy,fy,zc,zw,bz

11、) 碼：（ jsbh ） 非主屬性 ： jsxm,zy,fy,zc,zw,bz 因為 jsxm,zy,fy,zc,zw,bz完全依賴于 （ kch ）， 所以屬于 2NF 因為不存在傳遞函數(shù)依賴，所以屬于3NF Kcb(kch,kcm,kkxq,xs,xf) 碼：（ kch ） 非主屬性 ： kcm,kkxq,xs,xf 因為 kcm,kkxq,xs,xf完全依賴于 （kch ）， 所以屬于 2NF 因為不存在傳遞函數(shù)依賴，所以屬于3NF .專業(yè) .專注. . Skb(jsbh,kch,bj,sj,dd) 碼：（ jsbh,kch,bj ） 非主屬性 ： sj,dd 因為 sj,dd 完全依賴

12、于 （ jsbh,kch,bj ），所以屬于2NF 因為不存在傳遞函數(shù)依賴，所以屬于3NF Tjb(kch,rs1,rs2,rs3,rs4,rs5) 碼：（ kch ） 非主屬性 ： rs1,rs2,rs3,rs4,rs5 因為 rs1,rs2,rs3,rs4,rs5完全依賴于 （ kch ），所以屬于2NF 因為不存在傳遞函數(shù)依賴，所以屬于3NF Xsb(xh,xm,xb,cssj,bj,zy,zxf,bz,lxfs,zp) 碼：（ xh ） 非主屬性 ： xm,xb,cssj,bj,zy,zxf,bz,lxfs,zp 因為 xm,xb,cssj,bj,zy,zxf,bz,lxfs,zp完全

13、依賴于 （ xh ），所以屬于2NF 因為不存在傳遞函數(shù)依賴，所以屬于3NF Yhb(yhbh,yhm,mm,bz) 碼：（ yhbh ） 非主屬性 ： yhm,mm,bz 因為 yhm,mm,bz完全依賴于 （ yhbh ），所以屬于2NF .專業(yè) .專注. . 因為不存在傳遞函數(shù)依賴，所以屬于3NF 4、物理設(shè)計 選擇合適的DBMS （要求用 sql server 2008） 5、實施 （把 sql 語句貼在下面 ） （ 1）創(chuàng)建數(shù)據(jù)庫 （ 把 sql 語句貼在下面 ） create databasepxscj1 on ( name = pxscj1_data, filename = f:

14、pxscj1_data.mdf, size = 3mb , filegrowth= 10% ) log on ( name = pxscj1_log , filename = f:pxscj1_log.ldf, size = 4mb , maxsize = 6mb , filegrowth= 1mb ) .專業(yè) .專注. . （ 2）創(chuàng)建表 （ 把 sql 語句貼在下面 ） create table xsb ( xh char (6) not null primary key , xm char (8) not null, xb bit , cssj datetime , bj varchar

15、 (50 ) not null, zy char (12 ), zxf int , bz varchar (500 ), lxfs varchar (50 ), zp varbinary (max ) ) create tablekcb ( kch char (3) not nullprimarykey , kcm char (16 ) notnull, kkxq tinyint , xs tinyint , xf tinyint ) create tablecjb ( .專業(yè) .專注. . xh char (6), kch char (6), cj int , constraintc1 pr

16、imarykey (xh ,kch ) ) create tableyhb ( yhbh char (6) primarykey notnull, yhm char (8 ), sf varchar (20), mm char (6), bz varchar (5 ) ) create tablejsb ( jsbh char (6) not null primarykey , jsxm char (8), zy varchar (30), fy varchar (30), zc varchar (20 ), zw varchar (20 ), bz varchar (50 ) ) creat

17、e tableskb ( .專業(yè) .專注. . jsbh char (6) not null, kch char (3) not null, bj varchar (50 ) not null, sj varchar (20), dd varchar (50), constraintc2 primarykey (jsbh ,kch ,bj ) ) create table tjb ( kch char (3) primary key not null, rs1 int , rs2 int , rs3 int , rs4 int , rs5 int ) create table cxb ( xb

18、 bit not null, xbm char (4 ) not null primary key ) （ 3）數(shù)據(jù)庫完整性 2）視圖 學生選課情況視圖（學號，姓名 ，課程號 ，課程名 ，成績，學分 ，總學分 ，教師 ） .專業(yè) .專注. . create view xs_xk_view as select xsb .XH,XM ,kcb .KCH,kcm ,CJ,xf ,zxf ,jsb .jsbh ,jsxm from xsb ,KCB,CJB,jsb,skb where xsb .XH = cjb .XH and kcb .KCH= cjb .KCH and cjb .KCH= skb

19、.kch and skb .jsbh = jsb .jsbh select * fromxs_xk_view 3）自定義數(shù)據(jù)庫類型 學號 ，課程號可以考慮用自定義的數(shù)據(jù)類型。 sp_addtypexh ,char(6) sP_addtypekch ,char(3) 4）默認值對象 Xsb:性別默認為1，總學分默認為0。 Kcb: 學生人數(shù)默認為40，學分默認為2。 Jsb:zy 計算機 ，fy 信息 ， zc 講師 ， zw 無。 Yhb:mm 123456 Xsb:性別默認為1， alter tablexsb add constraintb1 DEFAULT 1 for xb 總學分默認為0

20、。 altertablexsb add constraintb2 DEFAULT 0 for zxf Kcb: 學生人數(shù)默認為40， .專業(yè) .專注. . alter tablekcb add constraintb3 DEFAULT 40 for xs 學分默認為2。 alter tablekcb add constraintb4 DEFAULT 2 for xf Jsb:zy 計算機 ， alter tablejsb add constraintb5 DEFAULT 計算機 for zy fy 信息 ， alter tablejsb add constraintb6 DEFAULT 信息

21、for fy zc 講師， alter tablejsb add constraintb7 DEFAULT 講師 for zc zw 無 。 alter tablejsb add constraintb8 DEFAULT 無 for zw Yhb:mm 123456 alter tableyhb add constraintb9 DEFAULT 123456for mm 5）規(guī)則和 check 約束 Xsb:zxf 范圍在 0-160 之間 。 Kcb:kkxq 范圍在 1-8 之間， xf 在 1-15 之間 Cjb:cj 范圍在 0-100 之間 Xsb:zxf 在 0-160 之間 。

22、alter tablexsb add constraintpk_1 check (zxf between0 and 160 ) Kcb:kkxq 在 1-8 之間， xf 在 1-15 之間 alter tablekcb .專業(yè) .專注. . add constraintpk_2 check (kkxq between1 and 8 ) alter tablekcb add constraintpk_3 check (xf between1 and 15 ) Cjb:cj 在 0-100 之間 alter tablecjb add constraintpk_4 check (cj betwee

23、n0 and 100 ) 6) 參照關(guān)系 cjb(xh) 參照 xsb(xh) cjb(kch) 參照 kcb(kch) yhb(yhbh) 參照 xsb(xh) 和 jsb(jsbh)必須用觸發(fā)器實現(xiàn) skb( jsbh) 參照 jsb(jsbh) skb(bj) 參照 xsb(bj) skb(kch) 參照 kcb(kch) cjb (xh ) xsb (xh ) alter tablecjb add constraintfk_1 foreignkey (xh) referencesxsb (xh) cjb (kch ) kcb (kch ) alter tablecjb add cons

24、traintfk_2 foreignkey (kch) referenceskcb (kch ) .專業(yè) .專注. . skb(jsbh ) jsb (jsbh ) alter tableskb add constraintfk_4 foreignkey (jsbh ) referencesjsb (jsbh ) skb(kch ) kcb (kch ) alter tableskb add constraintfk_6 foreignkey (kch) referenceskcb (kch ) skb(bj ) xsb(bj ) - 用觸發(fā)器實現(xiàn) create triggert1 on sk

25、b for insert as begin if not exists( select * fromxsb where bj =( select bj frominserted ) begin print插入的班級不存在！ rollbacktransaction end end yhb(yhbh) xsb(xh) 和 jsb(jsbh) -必須用觸發(fā)器實現(xiàn) .專業(yè) .專注. . alter triggert2 on yhb for insert as begin ifnot exists( select * fromxsb wherexh=( select yhbhfrominserted )

26、 begin if not exists( select * fromjsb where jsbh =( select yhbhfrominserted ) begin print 插入的編號出錯！ rollbacktransaction end end end （ 4） 用戶自定義函數(shù) （ 把 sql 語句貼在下面 ） （ 5） 用戶自定義存儲過程 1） 根據(jù)學號查詢學生的選課情況 （把 sql 語句貼在下面 ） CREATE PROCEDURE xhcxxs xh char (6) as begin if exists( select * fromcjb wherexh = xh ) .專

27、業(yè) .專注. . select * fromcjb wherexh = xh else print無此學生選課信息 end 2） 根據(jù)課程號查詢課程的選修情況 （把 sql 語句貼在下面 ） CREATE PROCEDURE kchcxxkkch char (3) as begin if exists( select * fromcjb wherekch = kch ) select * fromcjb wherekch = kch else print無此課程選課信息 end 根據(jù)教師編號查詢授課情況 （把 sql 語句貼在下面 ） CREATE PROCEDURE jsbhcxskjsbh

28、char (6) as begin if exists( select * fromskb wherejsbh = jsbh ) select * fromskb wherejsbh = jsbh else .專業(yè) .專注. . print無此教師授課信息 end 3） 根據(jù)班級查詢該班級的授課情況 （把 sql 語句貼在下面 ） CREATE PROCEDURE bjcxsk bjvarchar (50) as begin if exists( select * fromskb wherebj = bj ) select * fromskb wherebj = bj else print無此

29、班級授課信息 end 4） 根據(jù)課程號號查看授課情況 （把 sql 語句貼在下面 ） CREATE PROCEDURE kchcxsk kch char (3) as begin if exists( select * fromskb wherekch = kch ) select * fromskb wherekch = kch else print無此課程授課信息 end .專業(yè) .專注. . 5） 課程成績分布統(tǒng)計。存儲過程名稱TJ_CJ。 參數(shù)：課程號 （ kch ）。 實現(xiàn)功能 ： 把成績表 （CJB）中指定課程按照分數(shù)段人數(shù)進行統(tǒng)計，放入統(tǒng)計表 （TJB）中。 編寫思路 ：（ 1）

30、清空 TJB表，插入一行所有分數(shù)段的人數(shù)都為0 的所要查找的課程的 記錄 。（ 2）判斷所查的課程號在CJB 表中是否有記錄，若有則查找出各個分數(shù)段的人數(shù)并 且更新到 TJB 表中 。 方法一 ： create PROCEDURE dbo .TJ_CJ(kch char (3) as truncatetableTJB insert into TJB values (kch ,0,0,0,0,0) if exists (select * fromCJB where KCH= kch ) begin updateTJB set RS1=( select count (*) fromCJB wher

31、eCJ= 0 and CJ= 60 and CJ= 70 and CJ= 80 and CJ= 90 and CJ= 100 and KCH= kch ) .專業(yè) .專注. . end exec TJ_CJ101 方法二 ： 如果選擇統(tǒng)計的課程在成績表（ CJB）已經(jīng)存在 ，可以采用下列代碼完成統(tǒng)計功能， 并且代碼效率較高。但 CJB 表中沒有所選擇的課程記錄，執(zhí)行的結(jié)果是各個分數(shù)段的人數(shù) 為 null 。 create procedure dbo .TJ_CJ (KCH varchar (3) as begin delete fromTJB insert intoTJB(KCH,RS1,RS

32、2,RS3,RS4,RS5) selectKCH , sum (case whenCJ = 60 and CJ = 70 and CJ = 80 and CJ = 90 and CJ = 60 ) begin updatexsb set zxf= zxf+ xf where xh = xh end end create triggercjb_deletiggeron cjb for delete as begin declarexh varchar (6) declarecj int .專業(yè) .專注. . declarexf int declarekch char (3) select xh

33、= xh,cj = cj ,kch = kch fromdeleted select xf = xf fromkcb wherekch = kch if(cj = 60 ) begin updatexsb set zxf= zxf - xf where xh = xh end end 6）通過 instead of觸發(fā)器實現(xiàn)對學生選課情況視圖（學號 ，姓名，課程號 ，課程名 ， 成績 ，學分，總學分 ，教師 ）的插入數(shù)據(jù)操作、更新數(shù)據(jù)操作 、刪除數(shù)據(jù)操作。 （把 sql 語句貼在下面 ） create view v1 as select cjb .xh ,xm ,xsb .bj ,cjb .kch ,kcm ,CJ,xf ,zxf,jsb .jsbh ,jsxm fromXSB,KCB,CJB,jsb,skb where xsb .XH= CJB.XH and kcb .KCH= cjb .KCH and jsb .jsbh = skb .jsbh and skb .kch = CJB.KCH create triggerv1_