【正文】 ent,Tel) (6)維修記錄 (RoomNo,RepairerNo,RepaireRecordNo,RepaireResult,RepairTime, Supplyname) (7)課程安排 (CNo,TNo,RoomNo,WeekNo,Weekday,SectionNo,Pname,ClassNo ,NewWeekday, (8)NewWeekNo,NewSectionNo,NewRoomNo, AdjLessonID) (9)學(xué)生出勤記錄 (SNo,CNo,AttendSum) (10)學(xué)生使用教室 (SNo,RoomNo,DamageTime,DamageInfo,Devicename, LeaveTime,EnterTime) (11)教室信息 (RoomNo,Roomname,Campusname,Buildname,Capacity, IsUusable,Type,RemainCapacity) (12)單位借用記錄 (RoomNo, Deptname, RentReason, WeekNo,Weekday,SectionNo) 對模式進(jìn)行檢驗(yàn)和優(yōu)化。操作上存在大量冗余。空教室查詢中包含空閑教室和教室空余座位的查詢以及學(xué)生上課出勤的查詢統(tǒng)計(jì)；調(diào)課管理子系統(tǒng)主要包含教室調(diào)課管理與課 表查詢；教室借用子系統(tǒng)主要完成教室借用管理和記錄的查詢功能；設(shè)備管理子系統(tǒng)主要有設(shè)備損壞報(bào)告記錄查詢和設(shè)備維護(hù)記錄查詢。 索引的建立 1. 建立唯 一性索引 在課程安排表上建立課程號的唯一索引，以加快查詢速度。數(shù)據(jù)入庫通過兩種途徑：一是 EXCEL 表格導(dǎo)入信息，二是存儲過程錄入信息。 測試內(nèi)容 針對系統(tǒng)的幾大功能模塊，涉及的待測試的內(nèi)容主要有： 。加深了對數(shù)據(jù)庫設(shè)計(jì)每一步驟的理解，例如對每一個步驟的目的、任務(wù)和所要得到的技術(shù)文檔。在接下來的概念設(shè)計(jì)、邏輯結(jié)構(gòu)設(shè)計(jì)和物理設(shè)計(jì)階段，我更是加深了對每一階段特點(diǎn)的理解，并且按照每一階段的任務(wù)，設(shè)計(jì)了 ER圖，數(shù)據(jù)庫模式和用戶子模式，以及存儲過程和觸發(fā)器，來完成事務(wù)邏輯。在實(shí)習(xí)中，我發(fā)現(xiàn)自己理論知識還并不是很扎實(shí)，這就要求在以后要加強(qiáng)數(shù)據(jù)庫理論的學(xué)習(xí)，并且爭取機(jī)會，多做類似的實(shí)戰(zhàn)訓(xùn)練。在邏輯設(shè)計(jì)階段，對課程表信息在數(shù)據(jù)庫中的存儲方式不太清楚。 /* 創(chuàng)建學(xué)生信息基本表 */ CREATE TABLE STUDENT ( SNo Char(8) PRIMARY KEY, Sname Char(14) NOT NULL, Sex Char(3) NOT NULL, BirthYear int, Pname Char(24) NOT NULL, ClassNo Char(5) NOT NULL, AttendSum int, photo Image, DeptChar(20), CHECK (Sex =39。), ) /* 創(chuàng)建課程信息表 */ CREATE TABLE COURSE ( Cno Char(6) PRIMARY KEY, Cname Char(20)Not null, Time smallint, WeekNo Char(10) Not null, Weekday Char(10) Not null, SectionNo Char(10) Not null, Pname Char(20), ClassNo Char(5), StuNum int, CHECK (time BETWEEN 1 AND 200), CHECK(StuNum BETWEEN 1 AND 300), ) 07 信管專業(yè) 072 班數(shù)據(jù)庫系統(tǒng)概論課程設(shè)計(jì)論文 23 /* 創(chuàng)建教室信息基本表 */ CREATE TABLE CLASSROOM ( RoomNo Char(4) PRIMARY KEY, Roomname Char(6) , Campusname Char(10), Buildname Char(10), Capacity int, IsUusable Char(8), Type Char(10), RemainCapacity int, CHECK (Capacity BETWEEN 20 AND 500), CHECK (RemainCapacity =Capacity ), ) /* 創(chuàng)建教師信息基本表 */ CREATE TABLE TEACHER ( Tno Char(6) PRIMARY KEY, Tname Char(14) NOT NULL, DeptChar(20), Title Char(8), Sex char(30), Tel Char(12), CHECK (Sex IN(39。)), ) /* 創(chuàng)建借用單位信息基本表 */ CREATE TABLE Department ( RentDeptname Char(20) PRIMARY KEY, Leadername Char(14) NOT NULL, IDtype Char(6) NOT NULL, ID Char(20) NOT NULL, Tel Char(12) NOT NULL, ) /* 創(chuàng)建維修工信息基本表 */ CREATE TABLE Repairer ( RepairerNo Char(6) PRIMARY KEY, Repairername Char(14)NOT NULL, Statement Char(6), 07 信管專業(yè) 072 班數(shù)據(jù)庫系統(tǒng)概論課程設(shè)計(jì)論文 24 Tel Char(12), ) /* 創(chuàng)建學(xué)生出勤信息表 */ CREATE TABLE ATTENDENCE ( SNo Char(8), CNo Char(6), AttendSum smallint, PRIMARY KEY (SNo,CNo), FOREIGN KEY (SNo) REFERENCES STUDENT (SNo), FOREIGN KEY (CNo) REFERENCES COURSE (CNo), CHECK (AttendSum=0), ) /* 創(chuàng)建設(shè)備損壞報(bào)告基本表 */ CREATE TABLE DAMAGEREPORT ( SNo Char(8), RoomNo Char(4), DamageTime smalldatetime, DamageInfo Char(50), Devicename Char(20), PRIMARY KEY (SNo,RoomNo), FOREIGN KEY (SNo) REFERENCES STUDENT (SNo), FOREIGN KEY (RoomNo) REFERENCES CLASSROOM (RoomNo), ) /* 創(chuàng)建維修記錄基本表 */ CREATE TABLE MENDRECORD ( RepairerNo Char(6), RoomNo Char(4) NOT NULL, RepaireRecordNo Char(6) UNIQUE, Devicename char(10), RepaireResult char(6), Supplyname char(10), RepaireTime smalldatetime, FOREIGN KEY (RoomNo) REFERENCES CLASSROOM (RoomNo), FOREIGN KEY (RepairerNo) REFERENCES Repairer (RepairerNo), ) /* 創(chuàng)建課程安排基本表 */ CREATE TABLE CURRICULUM ( 07 信管專業(yè) 072 班數(shù)據(jù)庫系統(tǒng)概論課程設(shè)計(jì)論文 25 CNo Char(6), RoomNo Char(4), Tno Char(6), WeekNo Char(10), Weekday Char(10), SectionNo Char(10), Pname Char(20), ClassNo Char(6), PRIMARY KEY(CNo,RoomNo,TNo,WeekNo,Weekday,SectionNo), FOREIGN KEY (RoomNo) REFERENCES CLASSROOM (RoomNo), FOREIGN KEY (CNo) REFERENCES COURSE (CNo), FOREIGN KEY (TNo) REFERENCES TEACHER(TNo), ) /* 創(chuàng)建調(diào)課記錄基本表 */ CREATE TABLE ADJUSTRECORD ( CNo Char(6), RoomNo Char(4), Tno Char(6), WeekNo Char(10), Weekday Char(10), SectionNo Char(10), NewWeekNo Char(10), NewWeekday Char(10), NewSectionNo Char(10), NewRoomNo Char(4), PRIMARY KEY(RoomNo,WeekNo,Weekday,SectionNo), FOREIGN KEY (RoomNo) REFERENCES CLASSROOM (RoomNo), FOREIGN KEY (CNo) REFERENCES COURSE (CNo), FOREIGN KEY (TNo) REFERENCES TEACHER(TNo), ) /* 創(chuàng)建借用記錄表 */ CREATE TABLE RENTRECORD ( RoomNo Char(4), Deptname Char(20), RentReason Char(50), WeekNo Char(10), Weekday Char(10), SectionNo Char(10), PRIMARY KEY(RoomNo,Deptname), FOREIGN KEY(RoomNo) REFERENCES CLASSROOM (RoomNo), FOREIGN KEY(Deptname) REFERENCES Department(RentDeptname), 07 信管專業(yè) 072 班數(shù)據(jù)庫系統(tǒng)概論課程設(shè)計(jì)論文 26 ) /* 創(chuàng)建學(xué)生進(jìn)出教室信息表 */ CREATE TABLE ENTER_LEAVE ( SNo Char(8), RoomNo Char(4), EnterTime smalldatetime, LeaveTime smalldatetime, PRIMARY KEY(SNo,RoomNo,EnterTime), FOREIGN KEY (SNo) REFERENCES STUDENT (SNo), FOREIGN KEY (RoomNo) REFERENCES CLASSROOM (RoomNo), CHECK(lEAVETIMEENTERTIME), ) 視圖的建立 /* 創(chuàng)建學(xué)生出勤視圖 */ CREATE VIEW V_Attendence AS SELECT ,Sname,Cname, FROM STUDENT,ATTENDENCE,COURSE WHERE = AND = /* 建立查看空教室的視圖 */ CREATE VIEW EMPTYCLASSROOM AS SELECT Roomname,Campusname,Buildname,IsUsable,Type,RemainCapacity FROM CLASSROOM WHERE Isusable=39。0920703539。2021123039。 CREATE TRIGGER DAMAGEEVENT ON DAMAGEREPORT AFTER INSERT AS BEGIN UPDATE CLASSROOM SET IsUsable=39。 WHERE RoomNo=RoomNo。 WHERE RoomNo=RoomNo。 ELSE UPDATE ATTENDENCE SET AttendSum=AttendSum+1 WHERE SNo=SNo AND CNo=CNo EXEC CHANGEATTENDENCE 39。 處理查詢事務(wù)的部分存儲過程的建立與驗(yàn)