教室管理信息系统数据库的设计

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