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

07信管专业072班数据库系统概论课程设计论文

FOR UPDATE AS BEGIN

INSERT INTO ADJUSTRECORD(CNo,RoomNo,TNo, WeekNo,Weekday,SectionNo,NewWeekNo, NewWeekday,NewSectionNo,NewRoomNo) SELECT

DELETED.CNo,DELETED.RoomNo,DELETED.TNo,DELETED.WeekNo,DELETED.Weekday, DELETED.SectionNo,INSERTED.WeekNo,INSERTED.Weekday,INSERTED.SectionNo, INSERTED.RoomNo

FROM DELETED JOIN INSERTED ON INSERTED.CNo=DELETED.CNo; END

附3-3 教师调课事务处理

可以看到调课记录中自动生成了调课的记录，包含调课前后课程教室的信息。

5、 修改学生的出勤次数

/* 修改学生的出勤次数 */

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 '20080004','0001';

6、 处理查询事务的部分存储过程的建立与验证。

/*查询某班级上课应到人数*/

CREATE PROCEDURE SearchNumofPerson(@Pname CHAR(20)) AS

SELECT Cname,SUM(StuNum) AS 应到人数 FROM COURSE

WHERE Pname=@Pname GROUP BY Cname

/* 创建查询空教室的存储过程*/

30

07信管专业072班数据库系统概论课程设计论文

CREATE PROCEDURE SearchEmptyClassroom(@WeekNO char(10), @Weekday char(10),@SectionNo char(10)) AS

SELECT RoomNo,Roomname,Buildname,Campusname,RemainCapacity FROM CLASSROOM

WHERE IsUsable='可用' AND RoomNo NOT IN (

SELECT RoomNo

FROM CURRICULUM

WHERE WeekNo=@WeekNO AND Weekday=@Weekday AND SectionNo=@SectionNo )

附3-4 查询空教室结果

/*查询班级课程表*/

CREATE PROCEDURE SearchCurrivulum(@pname CHAR(20),@classNo CHAR(4)) AS BEGIN

SELECT WeekNo,Weekday,SectionNo,Pname,ClassNo,

KECHENGBIAO.Tname,Roomname,KECHENGBIAO.Cname FROM KECHENGBIAO,COURSE

WHERE KECHENGBIAO.Cname=Course.Cname

AND Pname=@pname AND COURSE.ClassNo=@classNo END

附3-5 班级课程表查询事务处理

31