【正文】 業(yè) 072 班數(shù)據(jù)庫系統(tǒng)概論課程設(shè)計論文 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。 , 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è)計論文 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è)備損壞報告基本表 */ 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è)計論文 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é)生進出教室信息表 */ 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。 /* 創(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 )。0920703539。000239。2021123039。0920703539。 CREATE TRIGGER DAMAGEEVENT ON DAMAGEREPORT AFTER INSERT AS BEGIN UPDATE CLASSROOM SET IsUsable=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。 WHERE RoomNo=RoomNo。 END /*使用完后將教室狀態(tài)改回可用狀態(tài) */ CREATE PROCEDURE CHANGESTATEMENT(RoomNo CHAR(4)) AS BEGIN UPDATE CLASSROOM SET IsUsable=39。 WHERE RoomNo=RoomNo。 END 可以看到調(diào)課記錄中自動生成了調(diào)課的記錄，包含調(diào)課前后課程教室的信息。 ELSE UPDATE ATTENDENCE SET AttendSum=AttendSum+1 WHERE SNo=SNo AND CNo=CNo EXEC CHANGEATTENDENCE 39。,39。 處理查詢事務(wù)的部分存儲過程的建立與驗證。可用 3