數(shù)據(jù)庫設(shè)計(學生選課系統(tǒng))

1、& r? x 4 ft數(shù)據(jù)庫原理與應(yīng)用實驗指導與報告2010/2011 學年第_2_學期姓 名：季亞學號：090709118班級：09數(shù)媒（1 ）班指導教師：周蓓計算機科學與工程學院2011目錄實驗八 數(shù)據(jù)庫設(shè)計 2一、概要設(shè)計 21.1 目的和意義 21.2 內(nèi)容和要求 3二、需求分析 32.1 背景 32.2 概要分析 42.3 開發(fā)技術(shù) 42.4 系統(tǒng)主要功能 4三、E-R 圖 53.1 概念設(shè)計 53.2 E-R 圖 6四、邏輯結(jié)構(gòu) 84.1 邏輯轉(zhuǎn)換 84.2 細化表結(jié)構(gòu) 8五、數(shù)據(jù)庫實施 1.15.1 創(chuàng)建表 1.2.5.2 創(chuàng)建必要視圖 1.55.3 創(chuàng)建必要觸發(fā)器 1.

2、65.4 創(chuàng)建必要存儲過程 1.9六、總結(jié) 2.2.參考文獻 2.3.實驗八 數(shù)據(jù)庫設(shè)計一、 概要設(shè)計1.1 目的和意義隨著無紙化辦公的普遍實現(xiàn)， 信息的自動處理以及網(wǎng)絡(luò)式的信息交互方式已 經(jīng)被人們廣泛應(yīng)用。 讓計算機來管理學生的信息是現(xiàn)在各個高校都在積極進行的 工作之一， 也是高校教學管理工作的重要內(nèi)容之一。 網(wǎng)上選課與傳統(tǒng)的選課方式 相比更加節(jié)約資源，增加了學生選課自主權(quán)。學生選課系統(tǒng)作為一種現(xiàn)代化的教學技術(shù)， 越來越受到人們的重視， 是一個 學校不可或缺的部分 , 學生選課系統(tǒng)就是為了管理好選課信息而設(shè)計的。學生選 課系統(tǒng)的將使選課管理工作規(guī)范化、系統(tǒng)化、程序化，避免選課管理的隨意性，

3、提高信息處理的速度和準確性，能夠準確、及時、 有效的查詢和修改學生選課情 況。與傳統(tǒng)的選課方式相比， 網(wǎng)上選課系統(tǒng)利用局域網(wǎng)為學生選課帶來了極大的 便捷。學生在公共機房，或者宿舍的個人電腦上便可以通過校園網(wǎng)絡(luò)來選課。在 選課期間內(nèi)， 學生能夠使用選課系統(tǒng)靈活的修改自己的選課情況， 大大提高了學 校選課工作的效率。 教務(wù)處的教師則可以通過選課系統(tǒng)的管理員子系統(tǒng)來管理學 生的選課情況， 使得學生選課工作達到系統(tǒng)化和自動化， 大大提高了學校的工作 效率，為廣大師生及相關(guān)人員節(jié)省了極多的時間。數(shù)據(jù)庫對于一個應(yīng)用系統(tǒng)的意義是相當重要的，一個設(shè)計良好的數(shù)據(jù)庫系統(tǒng) 一方面，能夠給開發(fā)者帶來便捷，更輕松的進行

4、系統(tǒng)設(shè)計與編碼；另一方面，對于系統(tǒng)的后期維護也非常重要，一個良好的數(shù)據(jù)庫系統(tǒng)能夠保證系統(tǒng)的可擴充 性，以及系統(tǒng)的移植性等問題。1.2 內(nèi)容和要求本實驗將設(shè)計出一個高校網(wǎng)上選課數(shù)據(jù)庫系統(tǒng)，其要求簡要如下：(1) 系統(tǒng)用戶由三類組成：教師、學生和管理員。(2) 管理員負責的主要功能： 用戶管理(老師、學生及管理員的增、刪、改) ； 課程管理(添加、刪除和修改) ； 選課管理(實現(xiàn)選課功能開放和禁止、老師成績輸入開放和禁止)(3) 學生通過登錄，可以查詢課程的基本信息、實現(xiàn)選課、退課和成績查詢；(4) 老師通過登錄，可以查看選課學生的基本信息，可以輸入成績。需求分析2.1背景全校性選修課開設(shè)的目的在

5、于擴大學生的知識面、 加強學生素質(zhì)教育、 培養(yǎng) 復合型高級人才， 具有不可替代的重要性。 隨著教育改革的不斷深入和素質(zhì)教育 的加強，學分制的實施，選修課在一個學生的培養(yǎng)計劃中占的比重將越來越大。網(wǎng)上選課系統(tǒng)的出現(xiàn)使同學們能夠更加自主、 便捷、準確的進行選課。 但是， 由于一般高校中的學生都比較多， 因此帶來了諸多如信息管理等問題， 鑒于需要 將學生信息、 選課信息等信息數(shù)字化以便于管理維護， 我們便想到了利用數(shù)據(jù)庫 能夠比較良好地解決此類問題，由此下面我將設(shè)計出一個高校選課系統(tǒng)以供參考。2.2 概要分析根據(jù) 1.2 節(jié)中所描述的系統(tǒng)分析要求，我們的高校選課系統(tǒng)將包含學生、教 師、管理員等實體，

6、學生可以在規(guī)定的時間內(nèi)選課、退選和成績查詢等操作；教 師可以查看學生的相關(guān)信息，錄入學生成績等操作；管理員可以添加管理員， 管 理教師、學生等信息。2.3 開發(fā)技術(shù)開發(fā)工具： Microsoft SQL Server 2000開發(fā)語言： SQL開發(fā)技術(shù) ：數(shù)據(jù)庫開發(fā)技術(shù)面向?qū)ο?：需求者SQL Server 2000 是 Microsoft 公司推出的 SQL Server 數(shù)據(jù)庫管理系 統(tǒng)，該版本繼承了 SQL Server 7.0 版本的優(yōu)點，同時又比它增加了許多更先進 的功能。具有使用方便可伸縮性好與相關(guān)軟件集成程度高等優(yōu)點， 可跨越從運行 Microsoft Windows 98 的膝

7、上型電腦到運行 Microsoft Windows 2000 的大 型多處理器的服務(wù)器等多種平臺使用。本實驗中最終將使用 Microsoft SQL Server 2000 數(shù)據(jù)庫管理系統(tǒng)將我們設(shè)計的數(shù)據(jù)庫實現(xiàn)。2.4 系統(tǒng)主要功能實驗選課系統(tǒng)分為教師， 學生及系統(tǒng)管理員三類用戶， 學生的功能包括選課、 退選、查詢選課信息等，教師的功能包括學生成績錄入，查詢實驗信息等。管理員的功能包括新建教師、學生賬戶，添加課程信息，其系統(tǒng)功能模塊如圖2-1 :三、E-R圖本章節(jié)主要包含概念設(shè)計、E-R圖，以及如何將E-R圖轉(zhuǎn)換為實際的物理模 型等內(nèi)容。3.1概念設(shè)計在我們的數(shù)據(jù)庫系統(tǒng)中共有6個實體：學生、教

8、師、管理員、專業(yè)、院系、 課程。（1）學生的屬性：學號、姓名、性別、生日、密碼（2）教師的屬性：工號、姓名、性別、生日、密碼、職稱（3）管理員的屬性：工號、姓名、性別、生日、密碼、權(quán)限標志（4）專業(yè)的屬性：專業(yè)號碼、專業(yè)名、輔導員、聯(lián)系方式、專業(yè)介紹（5） 院系的屬性：系號碼、系名稱、系主任、聯(lián)系方式、系介紹（6）課程的屬性：課程號碼、學時、學分、課程介紹（7）控制設(shè)置屬性：選課控制、成績錄入控制3.2 E-R 圖各個實體的E-R圖如下所示:學生學號 姓名 性別 生日 密碼圖1學生教師工號 姓名 性別 生日 密碼 職稱圖2教師圖3管理員專業(yè)/ 專業(yè)號碼專業(yè)名 輔導員 聯(lián)系方式 專業(yè)介紹圖4專業(yè)

9、系號碼 系名 系主任j :聯(lián)系方式系介紹課程111 1 1'L=二 Tf課程號 課程名 學時學分 課程介紹E-R圖，如圖7所示:圖6課程然后，將以上實體之間聯(lián)系表示出來，畫出數(shù)據(jù)庫系統(tǒng)的專業(yè)號碼系號碼 系名 系主任聯(lián)系方式系介紹專業(yè)名輔導員專業(yè)M屬于1專業(yè)介紹屬于屬于工號1 一 管理員工號 姓名 性別管理M1 管理M姓名學號姓名性別密碼成績生日1 /密碼生日. 一JT管理講授密碼生日職稱選課課程課程號學時學分課程介紹數(shù)據(jù)庫系統(tǒng)E-R圖四、邏輯結(jié)構(gòu)4.1邏輯轉(zhuǎn)換根據(jù)3.2節(jié)中的E-R圖可以將系統(tǒng)中的概念模型轉(zhuǎn)換為具體的表（即關(guān)系） 結(jié)構(gòu)，共分為7個關(guān)系，詳細信息如下所示： 學生（學號、專

10、業(yè)號碼、姓名、性別、生日、密碼） 教師（教師工號、系號碼、姓名、性別、生日、密碼、職稱） 管理員（管理員工號、姓名、性別、生日、密碼、權(quán)限標志） 專業(yè)（專業(yè)號碼、系號碼、專業(yè)名、輔導員、聯(lián)系方式、專業(yè)介紹） 院系（系號碼、系名稱、系主任、聯(lián)系方式、系介紹） 課程（課程號碼、學時、學分、課程介紹） 選課信息（學號、課程號碼、教師工號、成績）4.2細化表結(jié)構(gòu)為方便，根據(jù)上述文字描述，用英文簡寫為表和列取名，確定列的數(shù)據(jù)類型及必要的約束規(guī)則，給出如下所示數(shù)據(jù)庫表的基本結(jié)構(gòu)及說明：（1）學生信息表列名說明數(shù)據(jù)類型約束Stude ntNum學號char(10)主碼MajorNum專業(yè)號碼char(10)

11、not null，引用tb_major 的外碼Stude ntName姓名varchar(10)Not nullStude ntSex性別char not null，取“男”或“女”Stude ntBirthday生日datetimenot nullStude ntPassword密碼varchar(20)not nu II,(2) 教師信息表列名說明數(shù)據(jù)類型約束TeacherNum教師工號char(10)主碼DeptNum院系號碼char(10)not null ，引用tb_dept的外碼TeacherName姓名varchar(10)not n ullTeacherSex性別char(2)

12、not null，取“男”或“女”TeacherBirthday生日datetimenot n ullTeacherTitle職稱varchar(20)(3)管理員信息表列名說明數(shù)據(jù)類型約束Man agerNum管理員工號char(10)主碼Man agerName姓名varchar(10)not nullMan agerSex性別char not null ，取“男”或“女”Man agerBirthday生日datetimenot null列名說明數(shù)據(jù)類型約束MajorNum專業(yè)號碼char(10)主碼DeptNum系號碼char(10)not null ，引用tb_dept的外碼Majo

13、rName專業(yè)名varchar(20)not nulMajorAssista nt輔導員varchar(10)not n ullMajorTel聯(lián)系方式varchar(15)not n ull(5)院系信息表列名說明數(shù)據(jù)類型約束DeptNum系號碼char(10)主碼DeptName系名稱varchar(20)not n ullDeptChairma n系主任varchar(10)not n ullDeptTel聯(lián)系方式varchar(15)not n ullDeptDesc系介紹textnot n ull(6) 課程信息表列名說明數(shù)據(jù)類型約束CourseNum課程號碼char(10)主碼Co

14、urseName課程名varchar(20)not n ullCourseCredit學分floatnot n ullCourseClass學時smalli ntnot n ullCourseDesc課程介紹textnot n ull列名說明數(shù)據(jù)類型約束StuCourselD選課編號int主碼，自動遞增Stude ntNum學號char(10)notnull ，引用tb_stude nt的外碼CourseNum課程號碼char(10)notnull ，引用tb_course的外碼TeacherNum教師工號char(10)notnull ，引用tb_stude nt的外碼Grade成績smal

15、li nt(8)控制設(shè)置表列名說明數(shù)據(jù)類型約束IfTakeCourse選課控制char(1)not null，取“ 0”或“ 1 ”IfIn putGrade成績錄入控制char(1)not null，取“ 0”或“ 1 ”備注：選課和成績錄入功能的開放和禁止，0為禁止，1為開放五、數(shù)據(jù)庫實施本章節(jié)主要包含創(chuàng)建表、添加數(shù)據(jù)和創(chuàng)建必要的視圖、觸發(fā)器和存儲過程等內(nèi)容5.1創(chuàng)建表考慮到各個表之間的約束條件以及外鍵索引等要求，在創(chuàng)建表的時候應(yīng)當按 照一定的次序進行創(chuàng)建，否則會出現(xiàn)錯誤，還有一種方法是先創(chuàng)建各個基本表， 然后在對特定的表添加列和外碼約束，在本報告冊中將采取第一種方法。(1) tb_dep

16、t院系信息表CREATE TABLE tb_dept(DeptNum char(10) NOT NULL PRIMARY KEY,DeptName varchar(20) NOT NULL,DeptChairman varchar(10) NOT NULL,DeptTel varchar(15) NOT NULL,DeptDesc text NOT NULL,)(2) tb_major專業(yè)信息表CREATE TABLE tb_major(MajorNum char(10) NOT NULL PRIMARY KEY,DeptNum char(10) NOT NULL,MajorName varc

17、har(20) NOT NULL,MajorAssista nt varchar(10) NOT NULL,MajorTel varchar(15) NOT NULL,FOREIGN KEY (DeptNum) REFERENCES tb_dept(DeptNum)CREATE TABLE tb_stude nt(Stude ntNum char(10) NOT NULL PRIMARY KEY,MajorNum char(10) NOT NULL,Stude ntName varchar(10) NULL,Stude ntSex char(2) NOT NULL,Stude ntBirthd

18、ay datetime NOT NULL,Stude ntPassword varchar(20) NOT NULL,FOREIGN KEY (MajorNum) REFERENCES tb_major(MajorNum)(4) tb_teacher教師信息表CREATE TABLE tb_teacher(TeacherNum char(10) NOT NULL PRIMARY KEY,DeptNum char(10) NOT NULL,TeacherName varchar(10) NOT NULL,TeacherSex char NOT NULL,TeacherBirthday datet

19、ime NOT NULL,TeacherTitle varchar(20) NULL,FOREIGN KEY (DeptNum) REFERENCES tb_dept(DeptNum)(5) tb_ma nager管理員信息表CREATE TABLE tb_ma nager(Man agerNum char(10) NOT NULL PRIMARY KEY,Man agerName varchar(IO) NOT NULL,Man agerSex char(2) NOT NULL,Man agerBirthdate datetime NOT NULL,Man agerRights int NO

20、T NULL)(6) tb_course課程信息表CREATE TABLE tb_course(CourseNum varchar(10) NOT NULL PRIMARY KEY,CourseName varchar(20) NOT NULL,CourseCredit float NOT NULL,CourseClass smalli nt NOT NULL,CourseDesc text NOT NULL,)(7) tb_stucourse 學生選課信息表CREATE TABLE tb_stucourse(Stude ntNum char(10) NOT NULL,CourseNum ch

21、ar(10) NOT NULL,TeacherNum char(10) NOT NULL,Grade smalli nt NULL,FOREIGN KEY (Stude ntNum) REFERENCES tb_stude nt(Stude ntNum),FOREIGN KEY (CourseNum) REFERENCES tb_Course(CourseNum),FOREIGN KEY (TeacherNum) REFERENCES tb_teacher(TeacherNum),)(8) tb_con trol控制設(shè)置表CREATE TABLE tb_con trol(IfTakeCours

22、e char(1) NOT NULL check(lfTakeCourse in ( 'O', '1'),IfIn putGrade char(1) NOT NULL check(IfI nputGrade in ( '0', '1'),)5.2創(chuàng)建必要視圖(1) 建立學生成績視圖vi_grade，從學生、老師、選課表中選擇Grade不為空的記錄，其關(guān)鍵代碼如下所示：CREATE VIEW vi_gradeASSELECT tb_stucourse.Stude ntNum,Stude ntName,CourseName,Cours

23、eCredit,TeacherName,GradeFROM tb_stucourse,tb_stude nt,tb_course,tb_teacherwhere tb_stucourse.Stude ntNum=tb_stude nt.Stude ntNum andtb_stucourse.TeacherNum=tb_teacher.TeacherNum and tb_stucourse.CourseNum=tb_course.CourseNum and Grade is not n ull(2) 建立專業(yè)學生信息視圖vi_major ，從學生、專業(yè)表中選擇學生中的專業(yè)號 碼與專業(yè)表中專業(yè)號碼

24、相等的記錄，其關(guān)鍵代碼如下所示：CREATE VIEW vi_majorASSELECTtb_major.MajorName,Stude ntNum,Stude ntName,Stude ntSex,Stude ntBirthdayFROM tb_major,tb_studentWHERE tb_major.MajorNum=tb_stude nt.MajorNum5.3創(chuàng)建必要觸發(fā)器(1) 建立學生添加院系觸發(fā)器tri_adddept ，當該表中已存在所對應(yīng)院系號 碼的院系時，系統(tǒng)給與錯誤提示并回滾，其關(guān)鍵代碼如下所示：CREATE TRIGGER tri_adddept ON tb_dep

25、tFOR INSERT,UPDATEASIF(SELECT COUNT(*) FROM tb_dept,i nsertedWHERE tb_dept.DeptNum=i nserted.DeptNum)>0BEGINPRINT '院系號碼產(chǎn)生沖突，請核對后重試！ROLLBACKEND(2) 建立學生添加專業(yè)觸發(fā)器tb_major ，當專業(yè)信息中的院系號不對或者該表中已存在所對應(yīng)專業(yè)號碼的專業(yè)時，系統(tǒng)給與錯誤提示并回滾，其關(guān)鍵代碼如下所示:CREATE TRIGGER tri_addmajor ON tb_majorFOR INSERT,UPDATEASIF(SELECT COUN

26、T(*) FROM tb_dept,i nsertedWHERE tb_dept.DeptNum=i nserted.DeptNum)=OBEGINPRINT '未找到該專業(yè)的院系信息，請?zhí)砑酉鄳?yīng)院系后重試！ROLLBACKENDELSE IF(SELECT COUNT(*) FROM tb_major,i nsertedWHERE tb_major.MajorNum=i nserted.MajorNum)>0BEGINPRINT '院系號碼產(chǎn)生沖突，請核對后重試！ROLLBACKEND(3) 建立添加學生觸發(fā)器tri_addstudent ，當學生信息中的專業(yè)號不對或者

27、 系統(tǒng)中已存在所對應(yīng)學號的學生時， 系統(tǒng)給與錯誤提示并回滾，其關(guān)鍵代碼如下 所示：CREATE TRIGGER tri_addstude nt ON tb_stude ntFOR INSERT,UPDATEASIF(SELECT COUNT(*) FROM tb_major,i nsertedWHERE tb_major.MajorNum=i nserted.MajorNum)=0BEGINPRINT '未找到該學生的專業(yè)信息，請?zhí)砑酉鄳?yīng)專業(yè)后重試！ROLLBACKENDELSE IF(SELECT COUNT(*) FROM tb_stude nt,i nsertedWHERE tb

28、_stude nt.Stude ntNum=i nserted.Stude ntNum)>0BEGINPRINT '學號產(chǎn)生沖突，請核對后重試！ROLLBACKEND(4) 建立學生選課觸發(fā)器tri_takecourse，課程選課人數(shù)超過40個或者對應(yīng) 學生選課門數(shù)超過5門或者當前時間不是選課時間段時，系統(tǒng)給與錯誤提示并回 滾，其關(guān)鍵代碼如下所示：CREATE TRIGGER tri_takecourse ON tb_stucourseFOR INSERT,UPDATEASIF(SELECT COUNT(*) FROM tb_stucourse,i nsertedWHERE tb

29、_stucourse.CourseNum=i nserted.CourseNum)>40BEGINPRINT '所對應(yīng)課程選課人數(shù)不能超過 40個！ROLLBACKENDELSE IF(SELECT COUNT(*) FROM tb_stude nt,i nsertedWHERE tb_stude nt.Stude ntNum=i nserted.Stude ntNum)>5BEGINPRINT '對應(yīng)學生的選課不能超過 5門！ROLLBACKENDELSE IF(SELECT IfTakeCourse FROM tb_con trol)='0'BE

30、GINPRINT '當前不是選課時間段！'ROLLBACKEND5.4創(chuàng)建必要存儲過程(1)建立學生選課存儲過程tri_takecourse，其作用為查詢所有學生的選課記錄，其關(guān)鍵代碼如下所示：CREATE PROC proc_getcourseStude ntNum char(10) OUT,Stude ntName char(10) OUT,CourseName varchar(20) OUT,CourseCredit float OUT,TeacherName char(10) OUTASSELECT Stude ntNum=tb_stude nt.Stude ntNum

31、,Stude ntName=tb_stude nt.Stude ntName,CourseName=tb_course.CourseName,CourseCredit=tb_course.CourseCredit,TeacherName=tb_teacher.TeacherNameFROM tb_stucourse,tb_course,tb_stude nt,tb_teacherWHERE tb_stucourse.Stude ntNum=tb_stude nt.Stude ntNum AND tb_stucourse.CourseNum=tb_course.CourseNum AND tb_

32、stucourse.TeacherNum=tb_teacher.TeacherNum(2) 建立查詢教師課程存儲過程 proc_teachercourse，其作用為查詢指定教師所有課程的選課記錄，其關(guān)鍵代碼如下所示：CREATE PROC proc_teachercourseTeacherNum char(10),Stude ntNum char(10) OUT,Stude ntName char(10) OUT,CourseName varchar(20) OUT,CourseCredit float OUT,TeacherName char(10) OUTASSELECT Stude nt

33、Num=tb_stude nt.Stude ntNum,Stude ntName=tb_stude nt.Stude ntName,CourseName=tb_course.CourseName,CourseCredit=tb_course.CourseCredit,TeacherName=tb_teacher.TeacherNameFROM tb_stucourse,tb_course,tb_stude nt,tb_teacherWHERE tb_stucourse.Stude ntNum=tb_stude nt.Stude ntNum ANDtb_stucourse.CourseNum=tb_course.CourseNum AND