1、數(shù)據(jù)庫(kù)原理與應(yīng)用實(shí)驗(yàn)指導(dǎo)與報(bào)告 2010 / 2011 學(xué)年 第 2 學(xué)期姓 名： 季亞 學(xué) 號(hào)： 班 級(jí)： 09數(shù)媒（1）班 指導(dǎo)教師： 周蓓 計(jì)算機(jī)科學(xué)與工程學(xué)院2011目錄實(shí)驗(yàn)八 數(shù)據(jù)庫(kù)設(shè)計(jì)1一、概要設(shè)計(jì)11.1目的和意義11.2內(nèi)容和要求1二、需求分析22.1背景22.2概要分析22.3開(kāi)發(fā)技術(shù)22.4系統(tǒng)主要功能3三、E-R圖43.1 概念設(shè)計(jì)43.2 E-R圖4四、邏輯結(jié)構(gòu)64.1邏輯轉(zhuǎn)換64.2細(xì)化表結(jié)構(gòu)6五、數(shù)據(jù)庫(kù)實(shí)施85.1創(chuàng)建表95.2創(chuàng)建必要視圖105.3創(chuàng)建必要觸發(fā)器115.4創(chuàng)建必要存儲(chǔ)過(guò)程13六、總結(jié)14參考文獻(xiàn)16實(shí)驗(yàn)八 數(shù)據(jù)庫(kù)設(shè)計(jì)一、 概要設(shè)計(jì)1.1 目的和意義

2、隨著無(wú)紙化辦公的普遍實(shí)現(xiàn)，信息的自動(dòng)處理以及網(wǎng)絡(luò)式的信息交互方式已經(jīng)被人們廣泛應(yīng)用。讓計(jì)算機(jī)來(lái)管理學(xué)生的信息是現(xiàn)在各個(gè)高校都在積極進(jìn)行的工作之一，也是高校教學(xué)管理工作的重要內(nèi)容之一。網(wǎng)上選課與傳統(tǒng)的選課方式相比更加節(jié)約資源，增加了學(xué)生選課自主權(quán)。 學(xué)生選課系統(tǒng)作為一種現(xiàn)代化的教學(xué)技術(shù)，越來(lái)越受到人們的重視，是一個(gè)學(xué)校不可或缺的部分, 學(xué)生選課系統(tǒng)就是為了管理好選課信息而設(shè)計(jì)的。學(xué)生選課系統(tǒng)的將使選課管理工作規(guī)范化、系統(tǒng)化、程序化，避免選課管理的隨意性，提高信息處理的速度和準(zhǔn)確性，能夠準(zhǔn)確、及時(shí)、有效的查詢和修改學(xué)生選課情況。與傳統(tǒng)的選課方式相比，網(wǎng)上選課系統(tǒng)利用局域網(wǎng)為學(xué)生選課帶來(lái)了極大的便捷

3、。學(xué)生在公共機(jī)房，或者宿舍的個(gè)人電腦上便可以通過(guò)校園網(wǎng)絡(luò)來(lái)選課。在選課期間內(nèi)，學(xué)生能夠使用選課系統(tǒng)靈活的修改自己的選課情況，大大提高了學(xué)校選課工作的效率。教務(wù)處的教師則可以通過(guò)選課系統(tǒng)的管理員子系統(tǒng)來(lái)管理學(xué)生的選課情況，使得學(xué)生選課工作達(dá)到系統(tǒng)化和自動(dòng)化，大大提高了學(xué)校的工作效率，為廣大師生及相關(guān)人員節(jié)省了極多的時(shí)間。數(shù)據(jù)庫(kù)對(duì)于一個(gè)應(yīng)用系統(tǒng)的意義是相當(dāng)重要的，一個(gè)設(shè)計(jì)良好的數(shù)據(jù)庫(kù)系統(tǒng)一方面，能夠給開(kāi)發(fā)者帶來(lái)便捷，更輕松的進(jìn)行系統(tǒng)設(shè)計(jì)與編碼；另一方面，對(duì)于系統(tǒng)的后期維護(hù)也非常重要，一個(gè)良好的數(shù)據(jù)庫(kù)系統(tǒng)能夠保證系統(tǒng)的可擴(kuò)充性，以及系統(tǒng)的移植性等問(wèn)題。1.2 內(nèi)容和要求本實(shí)驗(yàn)將設(shè)計(jì)出一個(gè)高校網(wǎng)上選課

4、數(shù)據(jù)庫(kù)系統(tǒng)，其要求簡(jiǎn)要如下：(1) 系統(tǒng)用戶由三類組成：教師、學(xué)生和管理員。(2) 管理員負(fù)責(zé)的主要功能： 用戶管理（老師、學(xué)生及管理員的增、刪、改）； 課程管理（添加、刪除和修改）； 選課管理（實(shí)現(xiàn)選課功能開(kāi)放和禁止、老師成績(jī)輸入開(kāi)放和禁止）。(3) 學(xué)生通過(guò)登錄，可以查詢課程的基本信息、實(shí)現(xiàn)選課、退課和成績(jī)查詢；(4) 老師通過(guò)登錄，可以查看選課學(xué)生的基本信息，可以輸入成績(jī)。二、 需求分析2.1 背景全校性選修課開(kāi)設(shè)的目的在于擴(kuò)大學(xué)生的知識(shí)面、加強(qiáng)學(xué)生素質(zhì)教育、培養(yǎng)復(fù)合型高級(jí)人才，具有不可替代的重要性。隨著教育改革的不斷深入和素質(zhì)教育的加強(qiáng)，學(xué)分制的實(shí)施，選修課在一個(gè)學(xué)生的培養(yǎng)計(jì)劃中占的比

5、重將越來(lái)越大。網(wǎng)上選課系統(tǒng)的出現(xiàn)使同學(xué)們能夠更加自主、便捷、準(zhǔn)確的進(jìn)行選課。但是，由于一般高校中的學(xué)生都比較多，因此帶來(lái)了諸多如信息管理等問(wèn)題，鑒于需要將學(xué)生信息、選課信息等信息數(shù)字化以便于管理維護(hù)，我們便想到了利用數(shù)據(jù)庫(kù)能夠比較良好地解決此類問(wèn)題，由此下面我將設(shè)計(jì)出一個(gè)高校選課系統(tǒng)以供參考。2.2 概要分析根據(jù)1.2節(jié)中所描述的系統(tǒng)分析要求，我們的高校選課系統(tǒng)將包含學(xué)生、教師、管理員等實(shí)體，學(xué)生可以在規(guī)定的時(shí)間內(nèi)選課、退選和成績(jī)查詢等操作；教師可以查看學(xué)生的相關(guān)信息，錄入學(xué)生成績(jī)等操作；管理員可以添加管理員，管理教師、學(xué)生等信息。2.3 開(kāi)發(fā)技術(shù)開(kāi)發(fā)工具：Microsoft SQL Serv

6、er 2000 開(kāi)發(fā)語(yǔ)言：SQL開(kāi)發(fā)技術(shù)：數(shù)據(jù)庫(kù)開(kāi)發(fā)技術(shù)面向?qū)ο螅盒枨笳逽QL Server 2000 是Microsoft 公司推出的SQL Server 數(shù)據(jù)庫(kù)管理系統(tǒng)，該版本繼承了SQL Server 7.0 版本的優(yōu)點(diǎn)，同時(shí)又比它增加了許多更先進(jìn)的功能。具有使用方便可伸縮性好與相關(guān)軟件集成程度高等優(yōu)點(diǎn)，可跨越從運(yùn)行Microsoft Windows 98 的膝上型電腦到運(yùn)行Microsoft Windows 2000 的大型多處理器的服務(wù)器等多種平臺(tái)使用。本實(shí)驗(yàn)中最終將使用Microsoft SQL Server 2000數(shù)據(jù)庫(kù)管理系統(tǒng)將我們?cè)O(shè)計(jì)的數(shù)據(jù)庫(kù)實(shí)現(xiàn)。2.4 系統(tǒng)主要功能實(shí)驗(yàn)選

7、課系統(tǒng)分為教師，學(xué)生及系統(tǒng)管理員三類用戶，學(xué)生的功能包括選課、退選、查詢選課信息等，教師的功能包括學(xué)生成績(jī)錄入，查詢實(shí)驗(yàn)信息等。管理員的功能包括新建教師、學(xué)生賬戶，添加課程信息，其系統(tǒng)功能模塊如圖2-1：圖2-1 系統(tǒng)功能模塊三、 E-R圖本章節(jié)主要包含概念設(shè)計(jì)、E-R圖，以及如何將E-R圖轉(zhuǎn)換為實(shí)際的物理模型等內(nèi)容。3.1 概念設(shè)計(jì)在我們的數(shù)據(jù)庫(kù)系統(tǒng)中共有6個(gè)實(shí)體：學(xué)生、教師、管理員、專業(yè)、院系、課程。（1） 學(xué)生的屬性：學(xué)號(hào)、姓名、性別、生日、密碼（2） 教師的屬性：工號(hào)、姓名、性別、生日、密碼、職稱（3） 管理員的屬性：工號(hào)、姓名、性別、生日、密碼、權(quán)限標(biāo)志（4） 專業(yè)的屬性：專業(yè)號(hào)碼、

8、專業(yè)名、輔導(dǎo)員、聯(lián)系方式、專業(yè)介紹（5） 院系的屬性：系號(hào)碼、系名稱、系主任、聯(lián)系方式、系介紹（6） 課程的屬性：課程號(hào)碼、學(xué)時(shí)、學(xué)分、課程介紹（7） 控制設(shè)置屬性：選課控制、成績(jī)錄入控制3.2 E-R圖各個(gè)實(shí)體的E-R圖如下所示：圖1 學(xué)生圖2 教師圖3 管理員圖4 專業(yè)圖5 系圖6 課程然后，將以上實(shí)體之間聯(lián)系表示出來(lái)，畫(huà)出數(shù)據(jù)庫(kù)系統(tǒng)的E-R圖，如圖7所示：圖7 數(shù)據(jù)庫(kù)系統(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個(gè)關(guān)系，詳細(xì)信息如下所示：學(xué)生（學(xué)號(hào)、專業(yè)號(hào)碼、姓名、性別、生日、密碼）教師（教師工號(hào)、系號(hào)碼、姓

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

10、名varchar(10)Not nullStudentSex性別char(2)not null，取“男”或“女”StudentBirthday生日datetimenot nullStudentPassword密碼varchar(20)not null,（2） 教師信息表列名說(shuō)明數(shù)據(jù)類型約束TeacherNum教師工號(hào)char(10)主碼DeptNum院系號(hào)碼char(10)not null，引用tb_dept的外碼TeacherName姓名varchar(10)not nullTeacherSex性別char(2)not null，取“男”或“女”TeacherBirthday生日dateti

11、menot nullTeacherTitle職稱varchar(20)（3） 管理員信息表列名說(shuō)明數(shù)據(jù)類型約束ManagerNum管理員工號(hào)char(10)主碼ManagerName姓名varchar(10)not nullManagerSex性別char(2)not null，取“男”或“女”ManagerBirthday生日datetimenot null（4） 專業(yè)信息表列名說(shuō)明數(shù)據(jù)類型約束MajorNum專業(yè)號(hào)碼char(10)主碼DeptNum系號(hào)碼char(10)not null，引用tb_dept的外碼MajorName專業(yè)名varchar(20)not nulMajorAssi

12、stant輔導(dǎo)員varchar(10)not nullMajorTel聯(lián)系方式varchar(15)not null（5） 院系信息表列名說(shuō)明數(shù)據(jù)類型約束DeptNum系號(hào)碼char(10)主碼DeptName系名稱varchar(20)not nullDeptChairman系主任varchar(10)not nullDeptTel聯(lián)系方式varchar(15)not nullDeptDesc系介紹textnot null（6） 課程信息表列名說(shuō)明數(shù)據(jù)類型約束CourseNum課程號(hào)碼char(10)主碼CourseName課程名varchar(20)not nullCourseCredit

13、學(xué)分floatnot nullCourseClass學(xué)時(shí)smallintnot nullCourseDesc課程介紹textnot null（7） 選課信息表列名說(shuō)明數(shù)據(jù)類型約束StuCourseID選課編號(hào)int主碼，自動(dòng)遞增StudentNum學(xué)號(hào)char(10)not null，引用tb_student的外碼CourseNum課程號(hào)碼char(10)not null，引用tb_course的外碼TeacherNum教師工號(hào)char(10)not null，引用tb_student的外碼Grade成績(jī)smallint（8） 控制設(shè)置表列名說(shuō)明數(shù)據(jù)類型約束IfTakeCourse選課控制ch

14、ar(1)not null，取“0”或“1”IfInputGrade成績(jī)錄入控制char(1)not null，取“0”或“1”備注：選課和成績(jī)錄入功能的開(kāi)放和禁止，0為禁止，1為開(kāi)放。五、 數(shù)據(jù)庫(kù)實(shí)施本章節(jié)主要包含創(chuàng)建表、添加數(shù)據(jù)和創(chuàng)建必要的視圖、觸發(fā)器和存儲(chǔ)過(guò)程等內(nèi)容。5.1 創(chuàng)建表考慮到各個(gè)表之間的約束條件以及外鍵索引等要求，在創(chuàng)建表的時(shí)候應(yīng)當(dāng)按照一定的次序進(jìn)行創(chuàng)建，否則會(huì)出現(xiàn)錯(cuò)誤，還有一種方法是先創(chuàng)建各個(gè)基本表，然后在對(duì)特定的表添加列和外碼約束，在本報(bào)告冊(cè)中將采取第一種方法。（1） tb_dept院系信息表CREATE TABLE tb_dept(DeptNum char(10) NO

15、T 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 varchar(20) NOT NULL,MajorAssistant varchar(10) NO

16、T NULL,MajorTel varchar(15) NOT NULL,FOREIGN KEY (DeptNum) REFERENCES tb_dept(DeptNum)（3） tb_student學(xué)生信息表CREATE TABLE tb_student(StudentNum char(10) NOT NULL PRIMARY KEY,MajorNum char(10) NOT NULL,StudentName varchar(10) NULL,StudentSex char(2) NOT NULL,StudentBirthday datetime NOT NULL,StudentPassw

17、ord 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(2) NOT NULL,TeacherBirthday datetime NOT NULL,TeacherTitle varch

18、ar(20) NULL,FOREIGN KEY (DeptNum) REFERENCES tb_dept(DeptNum)（5） tb_manager管理員信息表CREATE TABLE tb_manager(ManagerNum char(10) NOT NULL PRIMARY KEY,ManagerName varchar(10) NOT NULL,ManagerSex char(2) NOT NULL,ManagerBirthdate datetime NOT NULL,ManagerRights int NOT NULL )（6） tb_course課程信息表CREATE TABLE

19、 tb_course(CourseNum varchar(10) NOT NULL PRIMARY KEY,CourseName varchar(20) NOT NULL,CourseCredit float NOT NULL,CourseClass smallint NOT NULL,CourseDesc text NOT NULL,)（7） tb_stucourse學(xué)生選課信息表CREATE TABLE tb_stucourse(StudentNum char(10) NOT NULL,CourseNum char(10) NOT NULL,TeacherNum char(10) NOT

20、NULL,Grade smallint NULL,FOREIGN KEY (StudentNum) REFERENCES tb_student(StudentNum),FOREIGN KEY (CourseNum) REFERENCES tb_Course(CourseNum),FOREIGN KEY (TeacherNum) REFERENCES tb_teacher(TeacherNum),)（8） tb_control控制設(shè)置表CREATE TABLE tb_control(IfTakeCourse char(1) NOT NULL check(IfTakeCourse in ( 0,

21、1),IfInputGrade char(1) NOT NULL check(IfInputGrade in ( 0, 1),)5.2 創(chuàng)建必要視圖（1） 建立學(xué)生成績(jī)視圖vi_grade，從學(xué)生、老師、選課表中選擇Grade不為空的記錄，其關(guān)鍵代碼如下所示：CREATE VIEW vi_gradeASSELECT tb_stucourse.StudentNum,StudentName,CourseName,CourseCredit,TeacherName,GradeFROM tb_stucourse,tb_student,tb_course,tb_teacherwhere tb_stucou

22、rse.StudentNum=tb_student.StudentNum and tb_stucourse.TeacherNum=tb_teacher.TeacherNum and tb_stucourse.CourseNum=tb_course.CourseNum and Grade is not null（2） 建立專業(yè)學(xué)生信息視圖vi_major，從學(xué)生、專業(yè)表中選擇學(xué)生中的專業(yè)號(hào)碼與專業(yè)表中專業(yè)號(hào)碼相等的記錄，其關(guān)鍵代碼如下所示：CREATE VIEW vi_majorASSELECT tb_major.MajorName,StudentNum,StudentName,StudentS

23、ex,StudentBirthdayFROM tb_major,tb_studentWHERE tb_major.MajorNum=tb_student.MajorNum5.3 創(chuàng)建必要觸發(fā)器（1） 建立學(xué)生添加院系觸發(fā)器tri_adddept，當(dāng)該表中已存在所對(duì)應(yīng)院系號(hào)碼的院系時(shí)，系統(tǒng)給與錯(cuò)誤提示并回滾，其關(guān)鍵代碼如下所示：CREATE TRIGGER tri_adddept ON tb_deptFOR INSERT,UPDATEASIF(SELECT COUNT(*) FROM tb_dept,inserted WHERE tb_dept.DeptNum=inserted.DeptNum)

24、0BEGINPRINT 院系號(hào)碼產(chǎn)生沖突，請(qǐng)核對(duì)后重試！ROLLBACKEND（2） 建立學(xué)生添加專業(yè)觸發(fā)器tb_major，當(dāng)專業(yè)信息中的院系號(hào)不對(duì)或者該表中已存在所對(duì)應(yīng)專業(yè)號(hào)碼的專業(yè)時(shí)，系統(tǒng)給與錯(cuò)誤提示并回滾，其關(guān)鍵代碼如下所示：CREATE TRIGGER tri_addmajor ON tb_majorFOR INSERT,UPDATEASIF(SELECT COUNT(*) FROM tb_dept,insertedWHERE tb_dept.DeptNum=inserted.DeptNum)=0BEGINPRINT 未找到該專業(yè)的院系信息，請(qǐng)?zhí)砑酉鄳?yīng)院系后重試！ROLLBACKEN

25、DELSE IF(SELECT COUNT(*) FROM tb_major,inserted WHERE tb_major.MajorNum=inserted.MajorNum)0BEGINPRINT 院系號(hào)碼產(chǎn)生沖突，請(qǐng)核對(duì)后重試！ROLLBACKEND（3） 建立添加學(xué)生觸發(fā)器tri_addstudent，當(dāng)學(xué)生信息中的專業(yè)號(hào)不對(duì)或者系統(tǒng)中已存在所對(duì)應(yīng)學(xué)號(hào)的學(xué)生時(shí)，系統(tǒng)給與錯(cuò)誤提示并回滾，其關(guān)鍵代碼如下所示：CREATE TRIGGER tri_addstudent ON tb_studentFOR INSERT,UPDATEASIF(SELECT COUNT(*) FROM tb_ma

26、jor,insertedWHERE tb_major.MajorNum=inserted.MajorNum)=0BEGINPRINT 未找到該學(xué)生的專業(yè)信息，請(qǐng)?zhí)砑酉鄳?yīng)專業(yè)后重試！ROLLBACKENDELSE IF(SELECT COUNT(*) FROM tb_student,inserted WHERE tb_student.StudentNum=inserted.StudentNum)0BEGINPRINT 學(xué)號(hào)產(chǎn)生沖突，請(qǐng)核對(duì)后重試！ROLLBACKEND（4） 建立學(xué)生選課觸發(fā)器tri_takecourse，課程選課人數(shù)超過(guò)40個(gè)或者對(duì)應(yīng)學(xué)生選課門數(shù)超過(guò)5門或者當(dāng)前時(shí)間不是選課時(shí)間

27、段時(shí)，系統(tǒng)給與錯(cuò)誤提示并回滾，其關(guān)鍵代碼如下所示：CREATE TRIGGER tri_takecourse ON tb_stucourseFOR INSERT,UPDATEASIF(SELECT COUNT(*) FROM tb_stucourse,insertedWHERE tb_stucourse.CourseNum=inserted.CourseNum)40BEGINPRINT 所對(duì)應(yīng)課程選課人數(shù)不能超過(guò)40個(gè)！ROLLBACKENDELSE IF(SELECT COUNT(*) FROM tb_student,inserted WHERE tb_student.StudentNum=

28、inserted.StudentNum)5BEGINPRINT 對(duì)應(yīng)學(xué)生的選課不能超過(guò)5門！ROLLBACKENDELSE IF(SELECT IfTakeCourse FROM tb_control)=0BEGINPRINT 當(dāng)前不是選課時(shí)間段！ROLLBACKEND5.4 創(chuàng)建必要存儲(chǔ)過(guò)程（1） 建立學(xué)生選課存儲(chǔ)過(guò)程tri_takecourse，其作用為查詢所有學(xué)生的選課記錄，其關(guān)鍵代碼如下所示：CREATE PROC proc_getcourseStudentNum char(10) OUT,StudentName char(10) OUT,CourseName varchar(20)

29、OUT,CourseCredit float OUT,TeacherName char(10) OUTASSELECT StudentNum=tb_student.StudentNum,StudentName=tb_student.StudentName, CourseName=tb_course.CourseName,CourseCredit=tb_course.CourseCredit, TeacherName=tb_teacher.TeacherName FROM tb_stucourse,tb_course,tb_student,tb_teacher WHERE tb_stucours

30、e.StudentNum=tb_student.StudentNum ANDtb_stucourse.CourseNum=tb_course.CourseNum ANDtb_stucourse.TeacherNum=tb_teacher.TeacherNum（2） 建立查詢教師課程存儲(chǔ)過(guò)程proc_teachercourse，其作用為查詢指定教師所有課程的選課記錄，其關(guān)鍵代碼如下所示：CREATE PROC proc_teachercourseTeacherNum char(10),StudentNum char(10) OUT,StudentName char(10) OUT,CourseN

31、ame varchar(20) OUT,CourseCredit float OUT,TeacherName char(10) OUTASSELECT StudentNum=tb_student.StudentNum,StudentName=tb_student.StudentName, CourseName=tb_course.CourseName,CourseCredit=tb_course.CourseCredit, TeacherName=tb_teacher.TeacherName FROM tb_stucourse,tb_course,tb_student,tb_teacher WHERE tb_stucourse.StudentNum=tb_student.StudentNum ANDtb_stucourse.CourseNum=tb_course.CourseNum ANDtb_stucou