【導(dǎo)讀】通過調(diào)查研究，發(fā)現(xiàn)教室管理大多采用人工方式，不便于管理和用戶使用。理人員和用戶，采用存儲過程和觸發(fā)器，實現(xiàn)數(shù)據(jù)的增刪改，方便用戶操作和系統(tǒng)的實現(xiàn)?，F(xiàn)了空教室查詢，教師調(diào)課的管理，設(shè)備維護(hù)管理和教室借用的管理。

　　

【正文】 ( 07 信管專業(yè) 072 班數(shù)據(jù)庫系統(tǒng)概論課程設(shè)計論文 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è)計論文 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?？捎?39。 /* 創(chuàng)建課程表視圖 */ CREATE VIEW KECHENGBIAO AS SELECT , , ,Buildname,Campusname FROM COURSE, TEACHER,CURRICULUM,CLASSROOM WHERE = AND = AND = /* 創(chuàng)建維修記錄查詢的視圖 */ CREATE VIEW MENDRECORDS AS 07 信管專業(yè) 072 班數(shù)據(jù)庫系統(tǒng)概論課程設(shè)計論文 27 SELECT RepaireRecordNo,Repairername,Roomname,Devicename,Supplyname,RepaireTime From Repairer,MendRecord,CLASSROOM where = and = /* 創(chuàng)建教室借用記錄的視圖 */ CREATE VIEW RentRecords AS SELECT Roomname,Leadername,Tel,RentReason,WeekNo,Weekday,SectionNo FROM RENTRECORD,Department,CLASSROOM WHERE = AND = 07 信管專業(yè) 072 班數(shù)據(jù)庫系統(tǒng)概論課程設(shè)計論文 28 附錄 3 存儲過程、觸發(fā)器的創(chuàng)建與數(shù)據(jù)庫驗證 處理教室人數(shù)增加或減少 /* 創(chuàng)建教室人數(shù)增加時觸發(fā)器 */ DROP TRIGGER ADDSTUDENT CREATE TRIGGER ADDSTUDENT ON ENTER_LEAVE AFTER INSERT AS BEGIN UPDATE CLASSROOM SET RemainCapacity=RemainCapacity1 WHERE RoomNo IN (SELECT RoomNo FROM INSERTED )。 END INSERT INTO ENTER_LEAVE VALUES (39。0920703539。,39。000239。,39。2021123039。,) /* 創(chuàng)建教室人數(shù)減少時觸發(fā)器 */ CREATE TRIGGER PLUSSTUDENT ON ENTER_LEAVE AFTER DELETE AS BEGIN UPDATE CLASSROOM SET RemainCapacity=RemainCapacity+1 WHERE RoomNo IN (SELECT RoomNo FROM DELETED ) END DELETE FROM ENTER_LEAVE WHERE SNo=39。0920703539。 處理設(shè)備損壞時 當(dāng)教室損壞時，將教室的狀態(tài)設(shè)置為不可用。 CREATE TRIGGER DAMAGEEVENT ON DAMAGEREPORT AFTER INSERT AS BEGIN UPDATE CLASSROOM SET IsUsable=39。損壞 39。 WHERE RoomNo IN (SELECT RoomNo FROM INSERTED ) END CREATE PROCEDURE DamageInfoReport(@SNo CHAR(8),@RoomNo CHAR(4),@DamageTime SMALLDATETIME,@damageInfo CHAR(50),@Devicename CHAR(20)) AS BEGIN INSERT INTO DAMAGEREPORT VALUES(@SNo,@RoomNo,@DamageTime,@damageInfo,@Devicename) END 07 信管專業(yè) 072 班數(shù)據(jù)庫系統(tǒng)概論課程設(shè)計論文 29 處理單位借用時 /* 單位借用教室處理 */ CREATE PROCEDURE RENTCLASSROOM(@RoomNo CHAR(4),@deptname char(14),@Reason char(50),@WeekNo char(10),@Weekday char(10),@SectionNo char(10)) AS BEGIN UPDATE CLASSROOM SET IsUsable=39。借用 39。 WHERE RoomNo=@RoomNo。 INSERT INTO RENTRECORD VALUES(@RoomNo,@deptname,@Reason,@WeekNo,@Weekday,@SectionNo)。 END /*使用完后將教室狀態(tài)改回可用狀態(tài) */ CREATE PROCEDURE CHANGESTATEMENT(@RoomNo CHAR(4)) AS BEGIN UPDATE CLASSROOM SET IsUsable=39。可用 39。 WHERE RoomNo=@RoomNo。 END 處理教師調(diào)課時的事務(wù) /* 處 理教師調(diào)課事務(wù) */ CREATE TRIGGER AdjustCourse ON CURRICULUM 附 32 教室借用事務(wù)處理 附 31 設(shè)備損壞報告事務(wù)處理 07 信管專業(yè) 072 班數(shù)據(jù)庫系統(tǒng)概論課程設(shè)計論文 30 FOR UPDATE AS BEGIN INSERT INTO ADJUSTRECORD(CNo,RoomNo,TNo, WeekNo,Weekday,SectionNo,NewWeekNo, NewWeekday,NewSectionNo,NewRoomNo) SELECT ,, , FROM DELETED JOIN INSERTED ON =。 END 可以看到調(diào)課記錄中自動生成了調(diào)課的記錄，包含調(diào)課前后課程教室的信息。 修改學(xué)生的出勤次數(shù) /* 修改學(xué)生的出勤次數(shù) */ CREATE PROCEDURE CHANGEATTENDENCE(@SNo CHAR(8),@CNo CHAR(4)) AS IF NOT EXISTS(SELECT *FROM ATTENDENCE WHERE SNo=@SNo AND CNo=@CNo) INSERT INTO ATTENDENCE VALUES(@SNo,@CNo,1)。 ELSE UPDATE ATTENDENCE SET AttendSum=AttendSum+1 WHERE SNo=@SNo AND CNo=@CNo EXEC CHANGEATTENDENCE 39。2021000439。,39。000139。 處理查詢事務(wù)的部分存儲過程的建立與驗證。 /*查詢某班級上課應(yīng)到人數(shù) */ CREATE PROCEDURE SearchNumofPerson(@Pname CHAR(20)) AS SELECT Cname,SUM(StuNum) AS 應(yīng)到人數(shù) FROM COURSE WHERE Pname=@Pname GROUP BY Cname /* 創(chuàng)建查詢空教室的存儲過程 */ 附 33 教師調(diào)課事務(wù)處理 07 信管專業(yè) 072 班數(shù)據(jù)庫系統(tǒng)概論課程設(shè)計論文 31 CREATE PROCEDURE SearchEmptyClassroom(@WeekNO char(10), @Weekday char(10),@SectionNo char(10)) AS SELECT RoomNo,Roomname,Buildname,Campusname,RemainCapacity FROM CLASSROOM WHERE IsUsable=39?？捎?39。 AND RoomNo NOT IN ( SELECT RoomNo FROM CURRICULUM WHERE WeekNo=@WeekNO AND Weekday=@Weekday AND SectionNo=@SectionNo ) /*查詢班級課程表 */ CREATE PROCEDURE SearchCurrivulum(@pname CHAR(20),@classNo CHAR(4)) AS BEGIN SELECT WeekNo,Weekday,SectionNo,Pname,ClassNo, ,Roomname, FROM KECHENGBIAO,COURSE WHERE = AND Pname=@pname AND =@classNo END 附 35 班級課程表查詢事務(wù)處理 附 34 查詢空教室結(jié)果