07信管专业072班数据库系统概论课程设计论文
)
/* 创建学生进出教室信息表*/ 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(lEAVETIME>ENTERTIME), )
视图的建立
/* 创建学生出勤视图*/ CREATE VIEW V_Attendence AS
SELECT STUDENT.SNo,Sname,Cname,ATTENDENCE.Attendsum FROM STUDENT,ATTENDENCE,COURSE
WHERE STUDENT.SNo=ATTENDENCE.SNo AND ATTENDENCE.CNo=COURSE.CNo
/* 建立查看空教室的视图*/
CREATE VIEW EMPTYCLASSROOM AS
SELECT Roomname,Campusname,Buildname,IsUsable,Type,RemainCapacity FROM CLASSROOM WHERE Isusable='可用'
/* 创建课程表视图 */
CREATE VIEW KECHENGBIAO AS
SELECT CURRICULUM.WeekNo,CURRICULUM.Weekday, CURRICULUM.SectionNo,COURSE.Cname,TEACHER.Tname, CLASSROOM.Roomname,Buildname,Campusname
FROM COURSE, TEACHER,CURRICULUM,CLASSROOM
WHERE COURSE.CNo=CURRICULUM.CNo AND TEACHER.TNo=CURRICULUM.TNo AND CLASSROOM.RoomNo=CURRICULUM.RoomNo
/* 创建维修记录查询的视图*/ CREATE VIEW MENDRECORDS AS
26
07信管专业072班数据库系统概论课程设计论文
SELECT RepaireRecordNo,Repairername,Roomname,Devicename,Supplyname,RepaireTime From Repairer,MendRecord,CLASSROOM
where Repairer.RepairerNo=MendRecord.RepairerNo and MendRecord.RoomNo=CLASSROOM.RoomNo
/* 创建教室借用记录的视图*/
CREATE VIEW RentRecords AS
SELECT
Roomname,Department.RentDeptname,Leadername,Tel,RentReason,WeekNo,Weekday,SectionNo
FROM RENTRECORD,Department,CLASSROOM
WHERE RENTRECORD.Deptname=Department.RentDeptname AND CLASSROOM.RoomNo=RENTRECORD.RoomNo
27
07信管专业072班数据库系统概论课程设计论文
附录3 存储过程、触发器的创建与数据库验证
1、 处理教室人数增加或减少
/* 创建教室人数增加时触发器*/ DROP TRIGGER ADDSTUDENT CREATE TRIGGER ADDSTUDENT ON ENTER_LEAVE AFTER INSERT AS BEGIN
UPDATE CLASSROOM
SET RemainCapacity=RemainCapacity-1 WHERE RoomNo IN (SELECT RoomNo FROM INSERTED ); END
INSERT INTO ENTER_LEAVE VALUES ('09207035','0002','2009-12-30',) /* 创建教室人数减少时触发器*/ 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='09207035'
2、 处理设备损坏时
当教室损坏时，将教室的状态设置为不可用。 CREATE TRIGGER DAMAGEEVENT ON DAMAGEREPORT AFTER INSERT AS BEGIN
UPDATE CLASSROOM SET IsUsable='损坏'
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
28
07信管专业072班数据库系统概论课程设计论文
附3-1 设备损坏报告事务处理
3、 处理单位借用时 /* 单位借用教室处理*/
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='借用'
WHERE RoomNo=@RoomNo; INSERT INTO RENTRECORD
VALUES(@RoomNo,@deptname,@Reason,@WeekNo,@Weekday,@SectionNo); END
/*使用完后将教室状态改回可用状态 */
CREATE PROCEDURE CHANGESTATEMENT(@RoomNo CHAR(4)) AS BEGIN
UPDATE CLASSROOM SET IsUsable='可用'
WHERE RoomNo=@RoomNo; END
附3-2 教室借用事务处理
4、 处理教师调课时的事务
/* 处理教师调课事务*/
CREATE TRIGGER AdjustCourse ON CURRICULUM
29