实例2：教室管理系统数据库设计

1、实例2:教室管理系统数据库设计1 数据库设计1.1 需求设计1.1.1 引言在大学里，教室仍然是教学的主要场所，也是学生学习的重要场所，所以教室环境和设 备直接影响到教学活动的开展和学生的学习。为了保证教室环境良好的运转，教室的管理也就显得十分的必要和重要。但是教室的管 理也并不是一件很容易的事情。教室设备（尤其是多媒体设备）有所损坏必须及时登记，修 复，否则很可能影响以后教学，造成教学事故。教室的使用具有一定的计划性和流动性，即一个班在相应的时间里，所上的课程和使用 的教室是固定的，但是不同的课程在不同的时间里会使用不同的教室，这与教务处的课程安 排是密切相关的。尤其是使用多媒体教室的时候，

2、这就显得比较麻烦。为了能够更系统的，更有序的，更合理的，更有效的进行教室管理，有必要利用计算机 来处理各种信息，这也就需要一个更有效的教室管理系统。为此，经过较详细的调查和慎重的思考， 我决定做一个教室管理系统，来解决这些问题， 使管理简化，方便而且更有效。1.1.2 目标与任务（1）目标充分了解用户的需求，了解工作的流程，以及可能出现的问题。（2）任务 处理对象：本系统总体上说涉及了三个方面的数据，职工的信息，教室的信息，课程的信息，其中 职工信息包括了员工信息和清洁工信息。此外还有值班安排信息，多媒体设备损坏纪录，常 规设备损坏纪录。处理功能要求： 教室管理系统的具体功能包括三个方面：教室

3、查询子系统，卫生管理子系统，设备管理 子系统。教室查询子系统主要完成空闲教室查询（无课教室） ，教室使用查询的功能； 卫生管理子 系统主要完成值班安排与修改功能和值班情况记录的功能；设备管理子系统比较麻烦，主要 是接收损坏信息，通过检查其有效性，把有效的损坏信息分类并且记录，然后通知维修工， 再纪录维修情况的功能。（3）安全性和完整性要求 在这个系统中涉及到了相关人员的问题，而且同一个职工可能在不同的表中存在，要考 虑到，如果辞退或者某职工辞职，那么不仅要在职工信息表中删除相应的信息，还要在其他 的相关表中删除信息， 这就涉及到了安全性的问题， 初步打算用一个触发器来解决这个问题。完整性也是一

4、个重要的内容，它也涉及安全性上的问题。阶段结果(1 )用户调查本系统的用户范围比较广，教室查询子系统主要针对学生，通过听取周围学生的看法和 意见，以及自身的体会，比较充分的了解了学生的需求；卫生管理子系统主要针对教室管理 的员工及其主任，我直接询问了相关的员工，通过对他们的询问，了解了他们的工作的基本 流程，及其需求；设备管理子系统主要针对，教室管理的员工和维修工，通过对员工的咨询, 了解了他们管理的工作流程和具体的需求。(2) 业务流程图详见附录1(3) 数据流程图下面是一个设备流程图的底层流程图:其它的详见附录 2数据项名数据项含义说明数据类型长度取值范围取值含义于其他数据项的逻辑关系数据

5、项之间的联系Cno课程号char10Cname课程名称char20Ctime课程学时int4Weed周次char10Day星期char10Node节次char20Rname教室名称char10Mno系号char10(4 )数据字典数据项：表1教室信息数据项数据项名数据项含义说明数据类型长度取值范围取值含义于其他数据项的逻辑关系数据项之间的联系Spart校区char10Rname教室名称char10Position所在位置char20Type教室类型char10Room容量int4Mname系名char20eg班级char20Number人数int4表2课程信息数据项表3职工信息数据项项据 数名

6、数据项含义说明数据类型长度取值范围取值含义于其他数据项的逻辑关系数据项之间的联系Pno职工编号char10等于维修工编号Pname姓名char20等于维修工姓名Sex性别char4Age年龄int4Jname职业名称char10Addr住址char40Tel联系电话char20Week周次char10Day星期char10Ontime上班时间char20Uptime下班时间char20表4损坏信息数据项数据项名数据项含义说明数据类型长度取值范围取值含义于其他数据项的逻辑关系数据项之间的联系Rname教室名称char10Dlevel损坏程度char10Mend修复难易char10Mno维修工编号

7、char10等于维修工编号Mname维修工姓名char10等于维修工姓名Repair是否修复char101.1 . 4数据结构表5数据结构表数据结构名含义说明组成Class(T1)教室信息校区名，教室名称，所在位置，教室类型，容量课程号，课程名称，周次，星期，节次，课程学时，教室名称，系号，系Course (T2)课程信息名，班级，人数Worker(D1)职工信息职工编号，姓名，职业名称，性别，年龄，住址，联系电话Duty(D2)值班职工编号，姓名，职业名称，星期，上班时间，下班时间State(D3)值班情况记录职工编号，姓名，职业名称，星期，周次多媒体设备损坏Media(P1)教室名称，损坏

8、程度，修复难易，维修工编号，维修工姓名，是否修复记录常规设备损坏记Routine(P2)教室名称，损坏程度，维修工编号，维修工姓名，是否修复录1.1.5 处理逻辑描述（1 ）教室查询子系统输入：查询条件输出：查询结果处理：按照条件，在相应的表中，查找相应的数据，然后输出（1 ）卫生管理子系统输入：查询、插入或修改的目标结果：输出查询的结果，或者插入成功，或者修改成功处理：在相应的表中完成相应的操作。（3 ）设备管理子系统输入：插入或修改或查询的条件或目标结果：输出查询结果或插入成功或修改成功处理：在相关表中完成相关的操作。1.2概念设计引言概念结构的实际是整个数据库设计的关键，这个阶段主要的目

9、标是通过对用户需求进行综合、归纳与抽象，形成一个独立于 DBMS 的概念模型（ E-R 图）。它的主要特点是：1.能真实、充分地反映现实世界，包括事物与事物之间的联系，能满足用户对数据的处理要求，是对现实世界的一个真实模型； 2. 易于理解，因此可以用它和不 熟悉计算机的用户交换意见；3. 易于更改，当应用环境和应用要求改变时，容易对概念模型修改和扩充； 4. 易于向关系、网状、层次等各种数据模型转换。1.2.2 概念模型设计（ 1）设计 E-R 图 详见附录 31.2.3 实体的属性、联系的属性主码表示如：教室名称；外码表示如：教室名称；普通属性如：教室名称。教室（校区名，教室名称，所在位置

10、，教室类型，容量） ； 课程（课程号，教室名称，课程名称，周次，星期，节次，课程学时，系名，班级，人 数）；职工（职工编号，姓名，职业名称，性别，年龄，住址，联系电话） ； 多媒体设备损坏（教室名称，维修工编号，损坏程度，修复难易，维修工姓名，是否修 复，备注） ；常规设备损坏（教室名称，维修工编号，损坏程度，维修工姓名，是否修复，备注） 值班信息（职工编号，姓名，职业名称，星期，上班时间，下班时间） ； 值班记录（职工编号，姓名，职业名称，星期，周次） ；1.3 逻辑设计1.3.1 引言这个阶段的任务就是把概念结构设计阶段设计好的基本 E-R 图转换为与 DBMS 所支持的 数据模型相符合的

11、逻辑结构。在这个阶段里，该系统的目标就是把基本的 E-R 图转换成关系 数据模型。1.3.2 数据组织（ 1）将 E-R 图转换成关系模型：E-R 图转换成关系模型应该遵循以下原则：1. 一个实体型转换为一个关系模式。2. 一个 1:1 联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合 并。3. 把一个 1:n 联系转换为一个独立的关系模式。4. 一个 m:n 联系转换为一个关系模式。5. 三个或三个以上实体间的一个多元联系可以转换为一个关系模式。6. 具有相同码的关系模式可合并。（ 2）转换的结果：主码表示如：教室名称；外码表示如：教室名称；普通属性如：教室名称。教室（校区

12、名，教室名称，所在位置，教室类型，容量） ； 课程（课程号，教室名称，课程名称，教师编号，周次，星期，节次，课程学时，系名， 班级，人数） ；职工（职工编号，姓名，职业名称，性别，年龄，住址，联系电话） ； 多媒体设备损坏（教室名称，维修工编号，损坏程度，修复难易，维修工姓名，是否修复，备注） ； 常规设备损坏（教室名称，维修工编号，损坏程度，维修工姓名，是否修复，备注） ； 值班信息（职工编号，姓名，职业名称，星期，上班时间，下班时间） ； 值班记录（职工编号，姓名，职业名称，星期，周次） ； 该模式没有插入异常，删除异常等操作异常现象，已经达到 3NF 。（ 3）设计用户子模式： 鉴于安全

13、问题，每一个表都应有相应的视图。建立相关的视图如下： 教室视图： Class1（ 校区，教室名称 ,地点 ,教室类型 ,容量 ）； 课程与教室视图： Course1（ 校区，教室名称 ,教室类型 ,课程名称 ,周次 , 星期 ,节次 ,容量 ）；这两个视图包含了允许学生和员工等用户查询的属性，不允许修改，插入和删除。为职工做视图：值日视图： Duty1（ 职工编号 ,职工姓名 ,职业名称 , 上班时间 ,下班时间 ）； 清洁工值班记录： State1（ 职工编号 ,职工姓名 ,职业名称 ,日期 ,情况 ）； 员工值班记录：State2（职工编号，职工姓名，职业名称，日期，情况）;职工视图：St

14、ate3（职工编号，职工姓名，职业名称,日期,情况）；常规设备损坏纪录： Routine1（ 教室名称 ，职工编号 ，职工姓名 ，描述 ，是否修复 ，日期 ，备注）；多媒体设备损坏： Media1（ 教室名称 ，职工编号 ，职工姓名 ，描述 ，修复难易 ，是否修复 ，日期 ， 备注 ）；职工信息： Worker1 （职工编号，姓名，职业名称，性别，年龄，住址，联系电话）；在这些视图中，值日视图允许各个职工查询，但只允许管理员（主任）进行修改，插入， 删除等操作；清洁工值班记录视图，除维修工外，其他各职工都可查询，员工还可进行修改 操作，管理员（主任）可进行各种操作；员工值班记录只允许员工查询；

15、常规设备损坏纪录 视图允许员工和维修工进行查询，员工只对视图中的教室名称属性有修改，插入的权限，维 修工对其他属性有修改权限；在多媒体设备损坏信息视图中，允许员工和多媒体维修工进行 查询，员工只对教室名称有修改和插入的权限，多媒体维修工对其他的属性有修改权限。为方便，其中的部分权限在制作过程中要直接赋给基本表。133数据处理见系统的功能模块图:数室管理浆统團比 数室育理生飯的功能模块圍1.4物理设计引言数据库在物理设备上的存储与存取方法称为数据库的物理结构，它依赖于给定的计 算机系统。为一个给定的逻辑数据模型选取一个最适合应用要求的物理结构的过程，就 是数据库的物理设计。142目标与任务对数据

16、库进行合理的物理设计，以提高其时间和空间效率。（1）数据存取方面确定数据库的物理结构，在关系数据库中主要指存取方法和存储结构；对物理结构进行评价，评价的重点是时间和空间效率。如果评价结果满足原设计的要求,则可以进入到物理实施阶段，否则就需要重新设计或修改物理结构，有时甚至要返回逻辑设 计阶段修改数据模型。（ 2）功能模块 见图 15 所示1.5 数据库实施1.5.1 建立基本表/* 建立教室信息表 */ create table Class(Spartchar(10),Rnamechar(10) not null unique,Positionchar(20),Typechar(10),Roo

17、mint,primarykey(Rname)/* 建立课程信息表 */ create table Course(Cnochar(10),Cnamechar(20),Rnamechar(10),Cweekchar(10),Cdaychar(10),Nodechar(10),Ctimeint,Gradechar(10),Roomint,Numberint,Sdeptchar(20)/* 建立职工信息表 */create table Worker(Wno char(10) not null unique,Wnamechar(20),Wsexchar(4),int,WageJname char(10)

18、,Addrchar(40),Telchar(20),primary key(Wno), check(Wage >= 18 and Wage <= 60) )/* 建立值班信息表 */create table Duty (Wnochar(10),Wnamechar(20),Jnamechar(10),Ontimechar(20),Uptimechar(20),primarykey(Wno),foreignkey(Wno) references Worker(Wno),)/* 建立值班情况记录表 */ create table State(Wnochar(10),Wnamechar(2

19、0),Jnamechar(10),Wdatechar(20),statechar(20)/* 建立多媒体设备损坏记录表 */ create table Media(Rnamechar(10),Wnochar(10),Wnamechar(20),Describechar(20),Dlevelchar(10),Repairchar(4),Mdatechar(20),Pmarkchar(50)/* 建立常规设备损坏记录表 */ create table Routine（Rnamechar(10),Wnochar(10),Wnamechar(20),Describechar(20),Repaircha

20、r(4),Mdatechar(20),Pmarkchar(50)）1.5.2 建立视图/* 教室信息视图 */create view Class1（ 校区，教室名称 , 地点 , 教室类型 , 容量 ） asselect*fromClass/* 课程信息视图 */create view Course1（ 校区，教室名称 ,教室类型 , 课程名称 ,周次 ,星期 , 节次 ,容量 ） asselectClass.Spart,Class.Rname,Type,Cname,Cweek,Cday,Node,Class.Roomfrom Class,CourseWhereClass.Rname = Co

21、urse.Rname/* 职工信息视图 */create view Worker1（ 职工编号 ,姓名 ,职业名称 ,性别,年龄,住址 ,联系电话 ） asselect *from Worker/* 值班信息视图 */create view Duty1（ 职工编号 ,职工姓名 ,职业名称 , 上班时间 ,下班时间 ） asselectfromDuty/* 清洁工值班情况记录视图 */create view State1（ 职工编号 , 职工姓名 ,职业名称 ,日期 , 情况 ） asselect *from StatewhereJname = ' 清洁工 '/* 除清洁工外其他

22、职工值班情况视图 */create view State2（ 职工编号 , 职工姓名 ,职业名称 ,日期 , 情况 ） asselect *from StatewhereJname != ' 清洁工 '/* 总的职工值班情况视图 */create view State3（ 职工编号 , 职工姓名 ,职业名称 ,日期 , 情况 ） asselect *from State/* 常规设备损坏视图 */create view Routine1（ 教室名称 ,职工编号 , 职工姓名 ,描述 ,是否修复 ,日期 ,备注 ） asselect *from Routine/* 多媒体设备损坏

23、视图 */create view Media1（ 教室名称 ,职工编号 ,职工姓名 ,描述 ,修复难易 ,是否修复 , 日期 ,备注 ） asselect *from Media2.5.3 查询与更新/* 员工课程查询 */create Procedure js_kc1C_Spartchar（10）,C_Cweek char（6）,C_Cdaychar（4）,C_Nodechar(5)asselect校区 ,教室名称 ,课程名称 ,教室类型 ,容量fromCourse1where校区 = C_Spart and 周次 = C_Cweek and星期 = C_Cdayand节次 = C_Node

24、 and 教室类型 = ' 多媒体教室 'order by 教室名称 ASC/* 学生课程查询 */ create procedure js_kc2C_Spartchar(10),C_Cweek char(6),C_Cdaychar(4),C_Nodechar(5)asselect校区 ,教室名称 ,课程名称 ,教室类型 ,容量fromCourse1where校 区 = C_Spart and 周 次 = C_Cweek and 星 期 = C_Cday and 节 次C_Nodeorder by 教室名称 ASC/* 空教室查询 */create procedure js_k

25、js1C_Spartchar(10),C_Cweek char(6),C_Cdaychar(4),C_Node char(5)asselect校区 ,教室名称 ,教室类型 ,容量from Class1where not exists(select *from Course1where 校区 = C_Spart and 周次 = C_Cweek and 星期 = C_Cday and 节次 = C_Node and 教室名称 = Class1. 教室名称)/* 职工值班查询 */ create procedure js_zb1S_Wnochar(10)asselect职工编号 ,职工姓名 , 职

26、业名称 ,上班时间 ,下班时间from Duty1where职工编号 = S_Wno/* 职工值班记录查询 */ create procedure js_zbjl1S_Wnochar(10)asselect职工编号 ,职工姓名 , 职业名称 ,日期 ,情况from State3where职工编号 = S_Wno/* 员工对清洁工值班记录的插入 （ 视图 ）*/ create procedure js_zlc1S_Wnochar(10),S_Wnamechar(20),S_Jnamechar(10),S_Wdatechar(20),S_state char(20)asinsert into St

27、ate1values(S_Wno,S_Wname,S_Jname,S_Wdate,S_state)/* 员工对清洁工值班记录的删除( 视图 )*/create procedure js_zls1S_Wnochar(10)asdeletefrom State1where职工编号 = S_Wno/* 主任除清洁工外的其他职工值班记录的插入（视图 ）*/create procedure js_zlc2S_Wnochar(10),S_Wnamechar(20),S_Jnamechar(10),S_Wdatechar(20),S_state char(20)asinsert into State2 va

28、lues(S_Wno,S_Wname,S_Jname,S_Wdate,S_state)/* 主任除清洁工外的其他职工值班记录的删除 (视图 )*/ create procedure js_zls2S_Wnochar(10)asdeletefrom State2where职工编号 = S_Wno/* 主任对职工信息表的插入 */ create procedure js_zlc3W_Wnochar(10),W_Wnamechar(20),W_Wsexchar(4),W_Wageint,W_Jnamechar(10),W_Addrchar(40),W_Telchar(20)asinsert into

29、Workervalues(W_Wno,W_Wname,W_Wsex,W_Wage,W_Jname,W_Addr,W_Tel)/* 用游标完成的主任对职工信息表的修改 */CREATE PROCEDURE js_dmtxg1W_Wnochar(10),W_Wnamechar(20),W_Wsexchar(4),W_Wageint,W_Jnamechar(10),W_Addrchar(40),W_TelASchar(20)DECLAREaddWorker CURSOR STATIC FOR select *from Worker where Wno=W_Wno and Wname=W_Wname

30、and Wsex=W_Wsexand Wage=W_Wage and Jname=W_Jname and Addr=W_Addr and Tel=W_TelOPEN addWorkerIF CURSOR_ROWS>0UPDATE Worker set Wno=W_Wno and Wname=W_Wname and Wsex=W_Wsex and Wage=W_Wage and Jname=W_Jname and Addr=W_Addr and Tel=W_Tel where Wno=W_WnoELSEinsert into Worker(Wno,Wname,Wsex,Wage,Jname

31、,Addr,Tel) values(W_Wno,W_Wname,W_Wsex,W_Wage,W_Jname,W_Addr,W_Tel) CLOSE addWorkerDEALLOCATE addWorker/* 主任对职工信息表的删除 */ create procedure js_zls3 W_Wno char(10) as deletefrom Workerwhere Jname = W_Wno/* 员工对多媒体设备损坏纪录的插入 */ create procedure js_dmtcr1M_Rnamechar(10),M_Wnochar(10),M_Wnamechar(20),M_Desc

32、ribechar(20),M_Dlevelchar(10),M_Repairchar(4),M_Mdatechar(20),M_Pmarkchar(50)asinsert into Media values(M_Rname,null,null,null,null,null,null,null)/* 多媒体维修工对多媒体损坏纪录的修改 */ CREATE PROCEDURE js_dmtxg1M_Rnamechar(10),M_Wnochar(10),M_Wnamechar(20),M_Describechar(20),M_Dlevelchar(10),M_Repairchar(4),M_Mda

33、techar(20),M_Pmark char(50)asdeletefromMediawhereRname=M_Rname and Wno = nullinsert into Media values(M_Rname,M_Wno,M_Wname,M_Describe,M_Dlevel,M_Repair,M_Mdate,M_Pmark)/* 员工对多媒体设备损坏纪录的插入 */create procedure js_dmtcr2M_Rnamechar(10),M_Wnochar(10),M_Wnamechar(20),M_Describechar(20),M_Repairchar(4),M_M

34、datechar(20),M_Pmarkchar(50)asinsert into Routine values(M_Rname,null,null,null,null,null,null)/* 维修工对常规损坏纪录的修改 */CREATE PROCEDURE js_dmtxg2M_Rnamechar(10),M_Wnochar(10),M_Wnamechar(20),M_Describechar(20),M_Repairchar(4),M_Mdatechar(20),M_Pmarkchar(50)asdeletefrom RoutinewhereRname=M_Rname and Wno =

35、 nullinsert into Routine values(M_Rname,M_Wno,M_Wname,M_Describe,M_Repair,M_Mdate,M_Pmark)1.5.4 建立触发器/* 建立职工信息表触发器 */CREATE TRIGGER js_zg ON dbo.Worker FORDELETEASdelete Duty from deleted where Duty.Wno=deleted.Wno delete State from deleted where State.Wno=deleted.Wno2 数据库调试与试运行由于内容较多，这里只调试和运行一部分。建立

36、基本表、导入数据和建立视图比较简单， 这里就不再显示，下面调试查询与更新。2.1 查询/* 学生课程查询 */ create procedure js_kc2C_Spartchar(10),C_Cweek char(6),C_Cdaychar(4),C_Nodechar(5)asselect校区 ,教室名称 ,课程名称 ,教室类型 ,容量fromCourse1where校 区 = C_Spart and 周 次 = C_Cweek and 星 期 = C_Cday and 节 次C_Nodeorder by 教室名称 ASC调试：输入： js_kc2 ' 北校区 ','第一周 ','周 1','1-2 节 '运行结果：/*空教室查询*/create procedure