數(shù)據(jù)庫課程設計題庫管理系統(tǒng)1 需求分析學校的題庫管理系統(tǒng)l 實現(xiàn)課程、題型等基本信息的管理；l 能管理每一門課程的題型，每門課程的章節(jié)；l 實現(xiàn)習題信息的管理，能按題型或章節(jié)錄入每門課程的習題；l 定義存儲過程查詢指定課程各種題型和各章節(jié)的習題數(shù)量；l 定義視圖查詢各門課程使用的題型；l 設每個習題有一個題號，題號由系統(tǒng)自動生成，要求從1開設編號；l 設每個習題都有習題的建立日期，其值為系統(tǒng)時間，請定義實現(xiàn)（用默認實現(xiàn)）；l 定義存儲過程實現(xiàn)查詢各門課程、各種題型的習題數(shù)量；l 可以自動抽題組成套題，習題每抽取一次，要使習題的抽取次數(shù)加1（用觸發(fā)器實現(xiàn)）；l 建立數(shù)據(jù)庫相關表之間的參照完整性約束。2 任務概述2.1、數(shù)據(jù)字典數(shù)據(jù)結構：學生組 成：學號，姓名，性別，系別說 明：學生表所存放的是學生的信息，學號為學生的用戶名，是唯一的，設為主碼。數(shù)據(jù)結構：任課老師組 成：職工號，姓名，性別，系別說 明：任課老師表所存放的是任課老師的信息，職工號為任課老師的用戶名，是唯一的，設為主碼。數(shù)據(jù)結構：課程組 成：課程名，課程號，章節(jié)號說 明：課程表存放課程的相關信息，課程號是課程的唯一標識，設為主碼。數(shù)據(jù)結構：授課組 成：課程號，教師號，學號 說 明：授課表存放任課老師和學生之間的授課關系。課程號，教師號和學號屬性組是授課表的唯一標識，設為主碼。數(shù)據(jù)結構：測驗卷組 成：試卷號，試卷名，課程號，題量，測試時間，難度說 明：測驗卷的詳細信息填入此表中。試卷號存放的是對測驗卷一個編號，課程號是課程科目的編碼，試卷號和課程號的屬性組被設為主碼；試卷名存放的是測驗卷的主題名；題目內(nèi)容存放每一題內(nèi)容，與習題庫發(fā)生聯(lián)系；題量存放的是測驗卷一共有多少題目。數(shù)據(jù)結構：課程習題組 成：課程號，章節(jié)號，建立日期，難度，題號，答案說 明：課程習題的詳細內(nèi)容存放在此表中。課程代號存放的是課程科目的編碼；章節(jié)號存放的是對章節(jié)的編號；建立日期存放的是習題的上傳日期；難度存放的是每一題的難度系數(shù)；題號存放的是每一道題的題目編號；課程代號，章節(jié)號，難度和題號的屬性組設為主碼。數(shù)據(jù)結構：選擇 組 成：試卷號，課程號，題目內(nèi)容說 明：選擇表存放的是測驗卷和課程習題之間的聯(lián)系。試卷號和課程號是選擇表的唯一標識，設為主碼。數(shù)據(jù)結構：解答 組 成：課程號，試卷號，學號，記錄號，成績說 明：解答表存放的是學生解答測驗卷的相關信息。課程號、試卷號和學號可以唯一標識解答信息，設為主碼；記錄號存放學生解答測驗卷的次數(shù)；成績存放每次學生測驗的成績。數(shù)據(jù)結構：題型組 成：題名，題號，類別名說 明：題型表存放的是學生所做的習題的相關信息。題號可以作為唯一標識解答信息，設為主碼。3 數(shù)據(jù)庫概念設計3.1、局部概念模式設計 在系統(tǒng)界定范圍內(nèi)，將靜態(tài)數(shù)據(jù)對象或邏輯上具有獨立意義的概念抽象為實體集，如學生、任課教師、課程負責人、測驗卷、課程習題、課程等。從數(shù)據(jù)字典的角度來看，可將數(shù)據(jù)源點和數(shù)據(jù)結構作為實體集，而把數(shù)據(jù)處理抽象為實體間的聯(lián)系。3.1.1實體集的屬性：任課教師性別姓名學號系別學生 性別系別姓名職工號號課程時間難度測驗時間題量試卷名試卷號 測驗卷 課程習題答案題號難度知識點章號課程號節(jié)號節(jié)號課程名章號課程號課程3.1.2、E-R圖表示數(shù)據(jù)庫的概念結構下圖為課程責任人、任課教師、學生、課程、課程習題、測試卷實體集之間的關系E-R圖M職工號姓名性別系別任課老師設定測試卷試卷號課程名試卷名題量測試時間難度1 任課老師和測試卷的E-R圖節(jié)號姓名性別系別任課老師教授課程課程號章號職工號課程名1M 任課老師和課程的E-R圖M職工號性別姓名系別任課教師教授學生學號姓名性別系別N 任課老師和學生的E-R圖節(jié)號課程號試卷名題量測試時間難度試卷號測試卷選擇課程習題課程號章號建立日期難度題號答案MN 測試卷和課程習題的E-R圖題 量難 度測試時間課程號題量試卷號試卷名學號姓名系別姓名 解答 測試卷NM 學生 學生和測試卷的E-R圖1職工號姓名性別系別任教老師設定題型題名題號類別名M 任教老師和題型的E-R圖章內(nèi)容章名章號 章1 包含M節(jié)內(nèi)容節(jié)名節(jié)號 節(jié) 章和節(jié)的E-R圖3.2全局概念模式E-R圖節(jié)號題目內(nèi)容系別性別姓名學號難度測試時間題量課程號試卷號試卷名課程習題課程號章號建立日期難度題號答案選擇測試卷設定成績解答學生任教老師教授課程職工號姓名性別系別課程號課程名設定題型題類別題號題名授課NMNMNMM1M1M1四. 數(shù)據(jù)庫邏輯設計4.1數(shù)據(jù)模型映射4.1.1 由E-R圖導出一般關系模型的四條原則原則1：E-R圖中的每一個獨立實體變換為一個關系，其屬性變?yōu)殛P系的屬性，其主標識變?yōu)殛P系的主碼。原則2：E-R圖中的從實體及相應的“的”聯(lián)系變換為一個關系，從實體的屬性加上主體關系的主碼構成這個關系的屬性。如果“的”聯(lián)系是1：1的，則以主實體關系的主碼（作為外來碼）為這個關系的主碼；如果“的”聯(lián)系是1：M的，則以主實體關系的主碼加上同一主實體個體聯(lián)系的不同從屬實體個體賴以相互區(qū)分的屬性組，組成該關系的主碼。原則3：1：M聯(lián)系通過在“多”實體關系中增加相聯(lián)系的“1”實體關系的主碼及聯(lián)系本身的屬性來表達。其中“1”實體主碼為外來碼。原則4：M：M聯(lián)系轉換成一個獨立的關系，被聯(lián)系實體關系的主碼（作為外來碼）和聯(lián)系本身的屬性作為該關系的屬性，被聯(lián)系實體關系的主碼組成其復合主碼。將數(shù)據(jù)庫概念模式映射為數(shù)據(jù)庫邏輯模式，也就是把E-R模型轉換為關系模型。既分別將每個實體集轉換為關系，再將每個聯(lián)系集也轉換為關系。將實體集映射為關系：學生(學號,學生姓名),學號是主碼。授課(課程號,職工號，,學號)，(課程號,職工號,學號)是主碼。任課教師(職工號，姓名，性別)，職工號是主碼。測驗卷(試卷號，試卷名，課程號，題量，測試時間，難度)，（試卷號，試卷名，課程號）是主碼。課程習題（課程號，章號，節(jié)號，建立日期，難度，題號，題目內(nèi)容，答案），（課程代號，章號，節(jié)號，建立日期，難度，題號）是主碼。選擇（試卷號，課程號，題目內(nèi)容），（試卷號，課程號）是主碼。解答（課程號，試卷號，學號，記錄號，成績），（課程號，試卷號，學號,記錄號）是主碼。題型（題名，題號，題類別），（題名，題號）是主碼。章（章號，章名，章內(nèi)容），（章號）是主碼。節(jié)（節(jié)號，節(jié)名，節(jié)內(nèi)容），（節(jié)號）是主碼包含（章號，節(jié)號），（章號，節(jié)號）是主碼。4.2建立數(shù)據(jù)庫題庫管理系統(tǒng)的數(shù)據(jù)庫表的結構說明表名學生信息(SB)屬性名別名類型長度是否為空說明學號Snochar12主碼姓名Snamechar10性別Sexchar2系別Sdeptchar10表名授課信息(SK)屬性名別名類型長度是否為空說明課程號Cnochar10主碼職工號Zgnochar10學號Snochar10表名任課教師信息(RKJS)屬性名別名類型長度是否為空說明職工號Zgnochar10主碼姓名Znamechar10性別Sexchar2系別Sdeptchar10表名課程習題信息(KCXT)屬性名別名類型長度是否為空說明課程號Cnochar10主碼章節(jié)號Znochar10建立日期Jrdatetime難度Ndchar10題號Tnochar10題目內(nèi)容Tnvarchar50答案Ansvarchar50表名選擇信息（XD)屬性名別名類型長度是否為空說明試卷號Sjnochar10主碼課程號Cnochar10試卷內(nèi)容Snrvarchar2000表名解答信息(JD)屬性名別名類型長度是否為空說明課程號Cnochar10主碼試卷號Sjnochar10學號Snochar12成績Gradeint3表名題型信息（TD）屬性名別名類型長度是否為空說明題號Tnochar10主碼題名Tnamechar10題類別Tlbchar6表名測驗卷信息(CYJ)屬性名別名類型長度是否為空說明試卷號SJnochar10主碼課程號Cnochar10試卷名SJnamechar2題量T1int2測試時間Ttimechar10難度Ndchar10表名題型信息（TX）屬性名別名類型長度是否為空說明題號TXnochar10主碼題名TXnamevarchar50類別名LBMchar20五物理設計5.1建立數(shù)據(jù)庫 create database 題庫管理5.2建表1.學生表create table SB (Sno char(10) primary key, Sname char(10), Sex char(2), Sdept char(10), ) 2.任課老師create table RKJS (Tno char(10) primary key, Tname char(10), Sex char(2), Sdept char(10) ) 3.課程表create table Kc (kcno char(10), primary key, kname char(10), kzhang char(10), )4.課程習題表create table KCXT ( Cno char(10), Zno char(10),Jr datetime,Nd char(10), Tno int identity(1,1),Tn varchar(50),Answer char(50), primary key (Cno,Zno,Nd,Jr,Tno), foreign key(KCno) references KC(KCno) ) 5測試卷表create table CYJ ( SJno char(10), Cno char(10), SJname char(10), Tl char(2), Ttime char(10), Nd char(10), primary key(SJno,Cno,SJname), foreign key(KCno) references KC(KCno) ) 6授課表create table SK (kcno char(10), Tno char(10), Sno char(10), primary key(kcno,Tno,Sno), foreign key (kcno) references KC(kcno), foreign key (Tno) references RKJS(Tno) , foreign key (Sno) references SB(Sno) ) 7.選擇表create table XD( SJno char(10), Kcno char(10), Tmnr char(20), primary key(SJno,Cno,), foreign key (Kcno) references KC(Kcno) ) 8.解答表create table JD ( Kcno char(10), SJno char(10), Sno char(10), Grade char(4), primary key (Kcno,SJno,Sno,JLno), foreign key (Kcno) references KC(Kcno), foreign key (Sno) references SB(Sno) ) 9.題型create table TX(TXno char(10) primary key,TXname varchar(50),LBM char(20)5.3建立索引和進行數(shù)據(jù)操作create index index_SB on SB(Sno); create index index_RKJS on RKJS(Zgno); create index unique index_CYJ on CYJ( SJno, Cno,SJname)；查詢學生成績：select Grade from JD where Cno=1 and Sjno= and Sno=0 and Jlno=1 查詢題目的答案：select Ans from KCXT where Cno=1 and Zno=第一章and Zsddh=1 and Nd=簡單and Tno=1 查詢測驗卷：select * from CYJ,KCXT where CYJ.Cno=KCXT.Cno and SJno= and Cno=1 and SJname=第一次測卷and Ttime=5.4定義存儲過程5.4.1查詢指定課程各種題型create procedure findType Tno char(10) Tname char(10) Tlb char(6) as select Tno=Tno,Tname=Tname,Tlb=Tlb from TD declare Tno Tname Tlb5.4.2查詢各章節(jié)的習題數(shù)量create procedure findNum Cno char(10) Zno char(10) as begin if(select count(*) from SK where Cno=Cno)0 begin if(select count(*) from KCXT where Cno=Cno and Zno=Zno)0 select count(Zno) from SK,KCXT where Cno=Cno and Zno=Zno and SK.Cno=KCXT.Cno else print章節(jié)號+Zno不存在 end else print課程號+Cno+不存在 end5.5創(chuàng)建視圖及查詢 create view txasselect Cno,Tno,Tname,Tlbfrom KCXT,TDwhere KCXT.Tno=TD.Tnoselect*from txwhere Cno=15.7每個習題都有習題的建立日期，其值為系統(tǒng)時間習題增加建立日期insert into KCXTvalues(03,01,GETDATE(),簡單,1+1,2)5.8觸發(fā)器可以自動抽題組成套題，習題每抽取一次，要使習題的抽取次數(shù)加1（用觸發(fā)器實現(xiàn)）；抽題屬于select操作，沒有對應的觸發(fā)器，所以可以建立一個表記錄抽取的套題，利用這個表的insert觸發(fā)器更新習題庫表中習題被抽取的次數(shù)。觸發(fā)器：所有的習題存放在表XTbank(num,XT)中，(num)為抽取的題號，XT為(num)抽取的題型，被抽取的習題存放在表new XTbank(nums)中Create trigger addXTbankOn newXTbankFor insertAs Begin transaction /開始檢查Update XTbankSet XT=XT+1Where num in(selset nums From inserted)Commit transaction /執(zhí)行Go 5.9參照完整性約束5.9.1添加唯一值約束alter table SBadd constraint un_Sno unique(Sno);alter table RKJSadd constraint un_Zgno unique(Zgno);alter table KCXTadd constraint un_Cno unique(Cno);alter table CYJadd constraint un_Sjno unique(Sjno);5.9.2添加主鍵約束alter table SBadd constraint pk_SB primary key(Sno)alter table SKadd constraint pk_SK primary key(Cno)alter table RKJSadd constraint pk_RKJS primary key(Sno,Cno)alter table KCXTadd constraint pk_KCXT primary key(Cno,Zno)alter table TDadd constraint pk_TD primary key(Tno)alter table CYJadd constraint pk_CYJ primary key(Sjno,Cno)5.9.3添加外鍵約束alter table SKadd constraint fk_Sno foreign key (Sno) references SB(Sno);alter table SKadd constraint fk_Cno foreign key (Cno) references KCXT(Cno);alter table SKadd constraint fk_Zgno foreign key (Zgno) referencesRKJS(Zgno);alter table XDadd constraint fk_Sjno foreign key (Sjno) references CYJ(Sjno);alter table XDadd constraint fk_Cno foreign key (Cno) references KCXT(Cno);alter table JDadd constraint fk_Cno foreign key Cno) references KCXT(Cno);alter table JDadd constraint fk_Sjno foreign key (Sjno) references CYJ(Sjno);alter table JDadd constraint fk_Sno foreign key (Sno) references SB(Sno);5.9.4為屬性添加范圍約束alter table SBadd cconstraint ck_Sex check(Sex=男);alter table RKJSadd cconstraint ck_Sdept check(Sdept=數(shù)學系);alter table KCXTadd cconstraint ck_Nd check(Nd=難);5.9.5為屬性添加集合約束alter table SBadd cconstraint ck_Sex check(Sex in (男,女);alt