數(shù)據(jù)庫實習(xí)報告-教務(wù)管理系統(tǒng)

1、數(shù)據(jù)庫系統(tǒng)課程設(shè)計學(xué)生姓名：班學(xué)號：指導(dǎo)教師：樊 文 有中國地質(zhì)大學(xué)（武漢）信息工程學(xué)院2009年 2 月 25 日教務(wù)管理系統(tǒng)1、需求分析1 1 信息要求：教務(wù)管理系統(tǒng)涉及的實體有：教師工作證號、姓名、職稱、電話等；學(xué)生學(xué)號、姓名、性別、出生年月等；班級班號、最低總學(xué)分等；系系代號、系名和系辦公室電話等；課程課序號、課名、學(xué)分、上課時間及名額等。這些實體之間的聯(lián)系如下：每個學(xué)生都屬于一個班，每個班都屬于一個系，每個教師也都屬于一個系。每個班的班主任都由一名教師擔(dān)任。一名教師可以教多門課，一門課可以有幾位主講老師，但不同老師講的同一門課其課序號是不同的（課序號是唯一的） 。一名同學(xué)可以選多門課

2、，一門課可被若干同學(xué)選中。一名同學(xué)選中的課若已學(xué)完，應(yīng)該記錄有相應(yīng)成績。本單位學(xué)生、教師都有重名，工作證號、學(xué)號可以作為標(biāo)識。1 2 處理要求：教學(xué)系統(tǒng)主要提供數(shù)據(jù)維護、選課和信息查詢。 其中常見的查詢有：系統(tǒng)中各對象的基本信息查詢。詢指定班、系的學(xué)生信息（名單、人數(shù)等）。查詢學(xué)生的成績、學(xué)分情況。查詢教師授課情況和學(xué)生選課情況 。1 3 安全性與完整性要求：查安全性要求：1.系統(tǒng)應(yīng)設(shè)置訪問用戶的標(biāo)識以鑒別是否是合法用戶，并要求合法用戶設(shè)置其密碼，保證用戶身份不被盜用；2.系統(tǒng)應(yīng)對不同的數(shù)據(jù)設(shè)置不同的訪問級別，限制訪問用戶可查詢和處理數(shù)據(jù)的類別和內(nèi)容；3.系統(tǒng)應(yīng)對不同用戶設(shè)置不同的權(quán)限，區(qū)分不

3、同的用戶，如學(xué)生，教師，系統(tǒng)管理員。完整性要求：1.各種信息記錄的完整性，關(guān)鍵信息記錄內(nèi)容不能為空；2.各種數(shù)據(jù)間相互的聯(lián)系的正確性；3.相同的數(shù)據(jù)在不同記錄中的一致性。1 4 系統(tǒng)功能的設(shè)計和劃分根據(jù)如上得到的用戶需求，我們將本系統(tǒng)按照所完成的功能分成以下幾部分：第一部分：用戶管理部分第二部分：管理員管理部分各部分完成的功能如下：1、用戶管理部分1、處理用戶注冊2、處理用戶登錄3、用戶可以查詢學(xué)生信息。4、用戶可以查詢快件信息。5、用戶可以提交與查詢報修信息。6、用戶可以插入與刪除用戶自己的離返校信息。7、用戶可以修改用戶自己密碼。2、管理員管理部分1、處理管理員登錄2、管理員可以查詢學(xué)生信

4、息。3、管理員可以發(fā)布與更新快件信息。4、管理員可以插入、修改與查詢報修信息。5、管理員可以插入與查詢學(xué)生夜歸信息。6、管理員可以查詢在校與離校學(xué)生信息。7、管理員可以修改管理員密碼。2、概念設(shè)計（ 1）概念模型（ E-R 圖）：學(xué)號出生年月最低總學(xué)分姓名學(xué)生n屬于1性別隸屬關(guān)系的分E-R 圖班號工作證號姓名職稱班級教師電話nn屬于1系1工作系代號系名系辦公室電話學(xué)號出生年月成績課序號課名學(xué)分姓名上課時間mn選修課程性別學(xué)生名額選修聯(lián)系的分E-R 圖班號班級11班主任負責(zé)最低總學(xué)分工作證號姓名職稱電話負責(zé)聯(lián)系的分E-R 圖工作證號姓名學(xué)號姓名性別班號最低總學(xué)分系代號課序號課名學(xué)分教師1上課時間

5、授課n課程職稱電話名額授課聯(lián)系的分E-R 圖出生年月成績課序號課名學(xué)分上課時間學(xué)生m選修n課程名額n屬于n1班級1授課n負責(zé)屬于111系1工作n教師系名系辦公室電話工作證號姓名職稱電話教務(wù)管理系統(tǒng)的總E-R 圖（ 2）數(shù)據(jù)字典 ：a.數(shù)據(jù)項編號屬性名1 學(xué)號2 姓名3 性別4 出生年月5 班號6 最低總學(xué)分7 工作證號8 教師姓名9 職稱10 電話11 系代號12 系名13 系辦公室電話14 課序號15 課名16 學(xué)分17 上課時間18 名額19 成績b、數(shù)據(jù)結(jié)構(gòu)編號數(shù)據(jù)結(jié)構(gòu)名1 學(xué)生2 教師3 班級4 系5 課程6 選課記錄c、數(shù)據(jù)流編號數(shù)據(jù)流1變更學(xué)生信息2查詢學(xué)生信息3變更教師信息4查詢

6、教師信息5變更班級信息6查詢班級信息7變更系信息8查詢系信息9變更課程信息10查詢課程信息11選課12查詢選課信息13查詢授課信息存儲代碼類型長度備注Snochar11學(xué)生編號Snamechar20學(xué)生的姓名，任意合法字符Ssexchar2學(xué)生的性別Sbirthdate出生時間間接表示年齡Classchar6班級編號TotalCredit浮點數(shù)4必須修足的學(xué)分總數(shù)Tnochar6教師的編號Tnamechar20教師的姓名，任意合法的字符Titlechar8教師的職位稱號Telchar11教師的聯(lián)系電話Dnochar3院系的編號Departmentchar10院系的名稱Dphonechar11院

7、系辦公室的工作電話Cnochar6可區(qū)分不同老師教授的同一課程Cnamechar20課程的名稱Credit浮點數(shù)2課程的學(xué)分Ctimedatetime上課的時間CnumInt課容量GradeInt選課學(xué)生某門課的成績組成學(xué)號、姓名、性別、出生年月、所屬班級班號工作證號、教師姓名、職稱、電話、所在系的系號班號、最低總學(xué)分、班主任的工作證號、所屬系的系號系代號、系名、系辦公室電話課序號、課名、學(xué)分、上課地點、名額、授課教師的工作證號課序號、學(xué)號、成績名輸入輸出變更信息學(xué)生信息學(xué)號學(xué)生信息變更信息教師信息工作證號教師信息變更信息班級信息班號班級信息變更信息系信息系代號系信息變更信息課程信息課序號課程

8、信息選課表變更信息選課信息學(xué)號或課序號選課信息工作證號授課信息d、數(shù)據(jù)存儲數(shù)據(jù)存儲名輸入數(shù)據(jù)流輸出數(shù)據(jù)流說明部分學(xué)生信息表修改學(xué)生信息學(xué)生信息某課程的學(xué)生信息教師信息表修改教師授課教師信息班主任信息課程信息表所有開設(shè)課程信息學(xué)生選擇的課程信息教師任教的課程信息選課表學(xué)生所有的選課記錄學(xué)生的選課表隨機存取記錄學(xué)生本學(xué)期所選課程信息班級信息表所有班級信息班級基本信息系信息表所設(shè)系的信息系的基本信息e、處理過程處理過程名輸入數(shù)據(jù)流輸出數(shù)據(jù)流選課選課信息選定課信息登記成績成績信息學(xué)生所選課程成績信息修改學(xué)生信息更改學(xué)生信息修改后的學(xué)生信息修改教師信息更改教師信息修改后的教師信息數(shù)據(jù)維護各信息表的修改信

9、息修改后各信息表（ 3）數(shù)據(jù)流圖查詢課程信息課程信息查詢課程信息表課課程選課信息選課信息成績信息成績信息程信登記信選課息成績息選已學(xué)生課成教師選績課表信信學(xué)息息學(xué)生信查詢查詢生息 選課信息選課信息選課信息選課信息信息系修改學(xué)學(xué)生信息學(xué)生信息學(xué)生信息表信教生信息息師信查詢教班息教師信息教師信息教師信息表級師信息信息系查詢學(xué)信生信息息教師修改教信班息級師信息信息班級信息班級信息表班級信息查詢查詢系信息系信息表系信息總數(shù)據(jù)流圖3、邏輯結(jié)構(gòu)設(shè)計E-R圖向關(guān)系模型的轉(zhuǎn)換（關(guān)系的碼用下橫線表出）教師（工作證號，姓名，職稱，電話，系代號）代碼表示： Teacher ( Tno, Tname, Title,

10、Tel, Dno )此為“教師”實體對應(yīng)的關(guān)系模式。其中1： n 聯(lián)系“工作”與此關(guān)系模式合并，Dno 為外碼。存在的函數(shù)依賴為 Tno （ Tname，Title ， Tel， Dno ），且不存在多值依賴，所以滿足4NF 。學(xué)生（學(xué)號，姓名，性別，出生年月，班號）代碼表示： Student ( Sno, Sname, Ssex, Sbirth, Class )此為“學(xué)生”實體對應(yīng)的關(guān)系模式。其中1：n 聯(lián)系“屬于”與此關(guān)系模式合并，Class為外碼。存在的函數(shù)依賴為 Sno （ Sname， Ssex， Sbirth， Class），且不存在多值依賴，所以滿足4NF 。系（系代號，系名，系

11、辦公室電話）代碼表示： Department ( Dno, Dname, Dphone )此為“系”實體對應(yīng)的關(guān)系模式。存在的函數(shù)依賴為Dno （Dname，Dphone ），且不存在多值依賴，所以滿足 4NF 。課程（課序號，課名，學(xué)分，上課時間，名額，工作證號）代碼表示： Course ( Cno, Cname, Credit, Ctime, Cnum, Tno )此為“課程”實體對應(yīng)的關(guān)系模式。其中1： n 聯(lián)系“授課”與此關(guān)系模式合并，Tno 為外碼。存在的函數(shù)依賴為 Cno （ Cname， Credit， Ctime，Cnum， Tno ），且不存在多值依賴，所以滿足4NF 。選課

12、（學(xué)號，課序號，成績）代碼表示： SC ( Sno, Cno, Grade )此為 m：n 聯(lián)系“選修” 所對應(yīng)的關(guān)系模式。Sno 和 Cno 均為外部碼。 存在的函數(shù)依賴為完全函數(shù)依賴（ Sno，Cno） Grade，且不存在多值依賴，所以滿足4NF 。班級（班號，最低總學(xué)分，工作證號，系代號）代碼表示： Class ( Class, TotalCredit, Tno, Dno )此為“班級”實體對應(yīng)的關(guān)系模式。其中1：n 聯(lián)系“屬于”和1：1 聯(lián)系“負責(zé)”與此關(guān)系模式合并，Tno和Dno為外碼。該關(guān)系模式中存在傳遞函數(shù)依賴：Class Tno, (TnoClass),TnoDno,可得Cl

13、ass Dno, 故 Class 不屬于 3NF 。解決方法是將其模式分解為： Class ( Class, TotalCredit, Tno )和 T-D( Tno, Dno) ，分解后的關(guān)系模式滿足 4NF ，并且 T-D 與 Teacher 的碼相同， 可合并為同一模式。 但是考慮到系統(tǒng)要經(jīng)常進行系的學(xué)生信息查詢，此時就不得不對 Student、Class 和 Teacher 三個表進行連接查詢，而連接是影響查詢效率的主要原因，所以取消對 Class的模式分解。此時 Class關(guān)系模式滿足 2NF 。設(shè)計用戶子模式為了方便程序查詢，建立了如下用戶視圖：1、某系的學(xué)生信息視圖（學(xué)號，姓名，

14、性別，出生年月，班號）2、某系的教師信息視圖（工作證號，教師姓名，職稱，電話，負責(zé)班級）3、某課程的學(xué)生成績單（學(xué)號，姓名，班級，成績）4、教師授課信息表（工作證號，教師姓名，職稱，教授課程，學(xué)分，上課時間）5、學(xué)生已選課表（課序號，課名，學(xué)分，授課教師，上課時間，名額）4、物理設(shè)計4 1選擇存取方法1、數(shù)據(jù)庫管理系統(tǒng)自動在每個表的主碼上建立索引，通常為B + 樹索引。2、在 Student 表 Sname列上建立Stu_name 索引，因為查詢操作很多都使通過學(xué)生姓名進行的。3、在 Teacher 表 Tname 列上建立Tea_name索引，因為查詢操作很多都使通過教師姓名進行的。4 2確

15、定數(shù)據(jù)庫的存儲結(jié)構(gòu)1、為了提高系統(tǒng)性能，根據(jù)具體情況將數(shù)據(jù)的易變部分與穩(wěn)定部分、經(jīng)常存取部分和存取頻率較低部分分開存放。2、 DBMS 產(chǎn)品一般都提供了一些系統(tǒng)配置變量、存儲分配參數(shù)，根據(jù)應(yīng)用環(huán)境確定這些參數(shù)值，并且在系統(tǒng)運行時還要根據(jù)系統(tǒng)實際運行情況進行調(diào)整，以使系統(tǒng)性能最佳。4 3評價物理結(jié)構(gòu)根據(jù)所選用的 DBMS ，如 SQL Server 2000，從定量估算各種方案的存儲空間、 存取時間和維護代價入手，對估算結(jié)果進行權(quán)衡、比較，選擇一個較優(yōu)的合理的物理結(jié)構(gòu)。5、系統(tǒng)實施編寫關(guān)系模式，載入數(shù)據(jù)。分期分批地組織數(shù)據(jù)入庫，先輸入小批量數(shù)據(jù)做調(diào)試用，待試運行基本合格后，再大批量輸入數(shù)據(jù)，逐步

16、增加數(shù)據(jù)量，逐步完成運行評價。需要注意的是，要首先調(diào)試運行 DBMS 的恢復(fù)功能，做好數(shù)據(jù)庫的轉(zhuǎn)儲和恢復(fù)工作?；?SQL 語言代碼如下：CREATE SCHEMA "TeachAffair" AUTHORIZATION GaoCREATE TABLE Department(Dno CHAR(3) PRIMARY KEY, /* 列級完整性約束條件， Dno 是主碼 */ Dept CHAR(20) UNIQUE NOT NULL,Dphone CHAR(11),);CREATE TABLE Teacher(Tno CHAR(6) PRIMARY KEY,/* 列級完整性約

17、束條件，Tname CHAR(20) NOT NULL,Title CHAR(8),Tel CHAR(11),Dno CHAR(3),FOREIGN KEY (Dno) REFERENCES Department(Dno)/*);Tno 是主碼 */表級完整性約束條件，Dno是外碼 */CREATE TABLE Class(Class CHAR(6) PRIMARY KEY,/* 列級完整性約束條件，Class是主碼 */TotalCredit NUMERIC(4,1),Tno CHAR(6),Dno CHAR(3),FOREIGN KEY (Tno) REFERENCES Teacher(T

18、no),/* 表級完整性約束條件，F(xiàn)OREIGN KEY (Dno) REFERENCES Department(Dno)/* 表級完整性約束條件，);Tno Dno是外碼 */是外碼 */CREATE TABLE Student(Sno CHAR(11) PRIMARY KEY,/* 列級完整性約束條件，Sno 是主碼 */Sname CHAR(20) NOT NULL,Ssex CHAR(2),Sbirth DATETIME,Class CHAR(6),FOREIGN KEY (Class) REFERENCES Class(Class)/* 表級完整性約束條件，);Class 是外碼 *

19、/CREATE TABLE Course(Cno CHAR(6) PRIMARY KEY,/* 列級完整性約束條件，Cno 是主碼 */Cname CHAR(20) NOT NULL,Credit NUMERIC(2,1),Ctime DATETIME,Cnum SMALLINT,Tno CHAR(6),FOREIGN KEY (Tno) REFERENCES Teacher(Tno)/* 表級完整性約束條件，);Tno 是外碼 */CREATE TABLE SC(Sno CHAR(11) NOT NULL,Cno CHAR(6) NOT NULL,Grade SMALLINT CHECK(G

20、rade>=0 AND Grade<=100),PRIMARY KEY (Sno, Cno),/* 主碼由兩個屬性構(gòu)成，必須作為表級完整性進行定義*/FOREIGN KEY (Sno) REFERENCES Student(Sno) /*表級完整性約束條件，Sno 是外碼 */ON DELETE CASCADE/*當(dāng)刪除 Student表中的元組時，級連刪除ON UPDATE CASCADE,/* 當(dāng)更新 Student 表中的 Sno 時，級連更新FOREIGN KEY (Cno) REFERENCES Course(Cno) /*表級完整性約束條件，ON DELETE NO A

21、CTION/*當(dāng)刪除 Course表中的元組造成了與ON UPDATE CASCADE,/* 當(dāng)更新 Course 表中的 Cno 時，級連更新SC 表中相應(yīng)的元組*/SC 表中相應(yīng)的元組*/Sno 是外碼 */SC表不一致時，拒絕刪除SC 表中相應(yīng)的元組 */*/);/*DROP TABLE SC;*/* 建立索引 */CREATE UNIQUE INDEX Stu_name ON Student(Sname);CREATE UNIQUE INDEX Tea_name ON Teacher(Tname);CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno

22、DESC);/* 查詢某個系（以GIS 為例）的學(xué)生信息*/CREATE VIEW GIS_Student/* 建立某個系（以GIS 為例）的學(xué)生信息視圖*/ASSELECT *FROM StudentWHERE Class IN(SELECT ClassFROM ClassWHERE Dname='GIS')WITH CHECK OPTION;SELECT *FROM GIS_Student;SELECT COUNT(*)FROM GIS_Student;/* 查詢班級的學(xué)生信息*/SELECT Sno, Sname, Ssex, BirthFROM StudentWHERE Class = '114072'SELECT COUNT(*)FROM StudentWHERE Class='114072'/* 查詢學(xué)生的成績、學(xué)分*/SELECT SC.Cno, Cname, Credit, GradeFROM Course,