Hexo
0%

CMIS

Posted on Edited on

教室管理信息系统

在之前的基础上进行改进，优化了数据结构和存储设计，改进了索引结构，满足第四范式。

数据库部分

创建数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
create database CMIS
on primary
(
name='f1',
filename='F:\sqlserver\sqlwj\cmis\f1.mdf',
size=3mb,
maxsize=unlimited,
filegrowth=3%
),
(
name='f2',																																																																																																					
filename='F:\sqlserver\sqlwj\cmis\f2.ndf',
size=10mb,
maxsize=500mb,
filegrowth=6mb
)
log on
(
name='f3',
filename='F:\sqlserver\sqlwj\cmis\f3.ldf',
size=1mb,
maxsize=unlimited,
filegrowth=2%
)

创建学生信息表

1
2
3
4
5
6
7
8
create table 学生信息
(
学号 char(10) not null,
姓名 varchar(15) not null,
性别 varchar(5) not null,
院系号 varchar(5) not null,
密码 varchar(15) not null
)

创建教师信息表

1
2
3
4
5
6
7
8
9
--创建教师信息表
create table 教师信息
(
教师编号 char(10) not null,
教师姓名 varchar(15) not null,
性别 varchar(5) not null,
院系号 varchar(5) not null,
职称 varchar(15) not null,
)

创建院系信息表

1
2
3
4
5
6
--创建院系信息表
create table 院系信息
(
院系号 varchar(5) not null,
院系名称 varchar(15) not null
)

创建教室信息表

1
2
3
4
5
6
--创建教室信息表
create table 教室信息
(
教室编号 varchar(5) not null,
教学楼编号 varchar(5) not null,
)

创建教学楼信息表

1
2
3
4
5
6
--创建教学楼信息表
create table 教学楼信息
(
教学楼名称 varchar(10) not null,
教学楼编号 varchar(5) not null
)

创建课程信息表

1
2
3
4
5
6
7
--创建课程信息表
create table 课程信息
(
课程名称 varchar(30) not null,
课程编号 varchar(7) not null,
教师编号 char(10) not null,
)

创建教室使用信息表

1
2
3
4
5
6
7
8
--创建教室使用信息表
create table 教室使用信息
(
教室编号 varchar(5) not null,
时间 varchar(10) not null,
状态 varchar(10) not null,
课程编号 varchar(7) not null 
)

创建课程时间表

1
2
3
4
5
6
7
--创建课程时间表
create table 课程时间
(
课程编号 varchar(7) not null,
上课时间 varchar(10) not null,
上课教室 varchar(5) not null
)

约束

设置主键

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--将教师信息表中教师编号设置为主键约束，身份证号设为唯一约束
alter table 教师信息 
add constraint PK_教师编号 primary key (教师编号)
--将学生信息表中学号设置为主键约束，身份证号设为唯一约束
alter table 学生信息 
add constraint PK_学号 primary key (学号)
--将教室信息中教室编号设置为主键
alter table 教室信息
add constraint PK_教室编号 primary key(教室编号)
--将教学楼信息中教学楼编号设置为主键
alter table 教学楼信息
add constraint PK_教学楼编号 primary key(教学楼编号)
--将院系信息中院系号号设置为主键
alter table 院系信息
add constraint PK_院系号 primary key(院系号)
--将课程信息中课程编号设置为主键
alter table 课程信息
add constraint PK_课程编号 primary key(课程编号)

设置外键约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
--将教师信息中的院系号与院系信息中的院系号添加外键
alter table 教师信息
add constraint FK_教师院系号 foreign key(院系号)references 院系信息(院系号)

--将教学楼信息中的教学楼编号与教室信息中的教学楼编号添加外键
alter table 教室信息
add constraint FK_教学楼编号 foreign key(教学楼编号)references 教学楼信息(教学楼编号)

--将学生信息中的院系号与院系信息中的院系号添加外键
alter table 学生信息
add constraint FK_学生院系号 foreign key(院系号)references 院系信息(院系号)


--将教室使用信息表中教室编号与教室信息表中的教室编号添加外键
alter table 教室使用信息
add constraint FK_教室编号教室使用信息 foreign key (教室编号)references 教室信息(教室编号)

--将教室使用信息表中课程编号与课程信息表中的课程编号添加外键
alter table 教室使用信息
add constraint FK_课程编号教室使用信息 foreign key (课程编号)references 课程信息(课程编号)

--对课程信息表中教师编号添加外键约束
alter table 课程信息
add constraint FK_教师编号课程信息 foreign key(教师编号)references 教师信息(教师编号)

--对课程时间表中上课教室添加外键约束
alter table 课程时间
add constraint FK_上课教室课程时间 foreign key(上课教室)references 教室信息(教室编号)

--对课程时间表中课程编号添加外键约束
alter table 课程时间
add constraint FK_课程编号课程时间 foreign key(课程编号)references 课程信息(课程编号)

设置检查约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
--对教师信息中的性别添加约束
　　alter table 教师信息
　　add constraint CK_性别教师 check (性别='男' or 性别='女')

--对学生信息中的性别添加约束
　　alter table 学生信息
　　add constraint CK_性别学生 check (性别='男' or 性别='女')

--对教室使用信息的时间进行约束
  alter table 教室使用信息
  add constraint CK_时间 check (时间='周一一二' or 时间='周一三四' or 时间='周一五六' or 时间='周一七八' or 
  时间='周二一二' or 时间='周二三四' or 时间='周二五六' or 时间='周二七八' or 
  时间='周三一二' or 时间='周三三四' or 时间='周三五六' or 时间='周三七八' or 
  时间='周四一二' or 时间='周四三四' or 时间='周四五六' or 时间='周四七八' or 
  时间='周五一二' or 时间='周五三四' or 时间='周五五六' or 时间='周五七八' or 
  时间='周六一二' or 时间='周六三四' or 时间='周六五六' or 时间='周六七八' or 
  时间='周日一二' or 时间='周日三四' or 时间='周日五六' or 时间='周日七八' 
  )

--对课程时间中上课时间约束
alter table 课程时间
  add constraint CK_课程时间 check (上课时间='周一一二' or 上课时间='周一三四' or 上课时间='周一五六' or 上课时间='周一七八' or 
  上课时间='周二一二' or 上课时间='周二三四' or 上课时间='周二五六' or 上课时间='周二七八' or 
  上课时间='周三一二' or 上课时间='周三三四' or 上课时间='周三五六' or 上课时间='周三七八' or 
  上课时间='周四一二' or 上课时间='周四三四' or 上课时间='周四五六' or 上课时间='周四七八' or 
  上课时间='周五一二' or 上课时间='周五三四' or 上课时间='周五五六' or 上课时间='周五七八' or 
  上课时间='周六一二' or 上课时间='周六三四' or 上课时间='周六五六' or 上课时间='周六七八' or 
  上课时间='周日一二' or 上课时间='周日三四' or 上课时间='周日五六' or 上课时间='周日七八' 
  )

插入信息

插入教学楼基本信息

1
2
3
4
5
6
7
8
insert into 教学楼信息
values('正心','ZX')

insert into 教学楼信息
values('诚意','CY')

insert into 教学楼信息
values('格物','GW')

插入教室基本信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
insert into 教室信息
values('ZX101','ZX')

insert into 教室信息
values('ZX102','ZX')

insert into 教室信息
values('ZX103','ZX')

insert into 教室信息
values('ZX104','ZX')


insert into 教室信息
values('ZX201','ZX')

insert into 教室信息
values('ZX202','ZX')

insert into 教室信息
values('ZX203','ZX')

insert into 教室信息
values('ZX204','ZX')

insert into 教室信息
values('ZX301','ZX')

insert into 教室信息
values('ZX302','ZX')

insert into 教室信息
values('ZX303','ZX')

insert into 教室信息
values('ZX304','ZX')


insert into 教室信息
values('ZX401','ZX')

insert into 教室信息
values('ZX402','ZX')

insert into 教室信息
values('ZX403','ZX')

insert into 教室信息
values('ZX404','ZX')

insert into 教室信息
values('ZX501','ZX')

insert into 教室信息
values('ZX502','ZX')

insert into 教室信息
values('ZX503','ZX')

insert into 教室信息
values('ZX504','ZX')


insert into 教室信息
values('ZX601','ZX')

insert into 教室信息
values('ZX602','ZX')

insert into 教室信息
values('ZX603','ZX')

insert into 教室信息
values('ZX604','ZX')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
insert into 教室信息
values('CY101','CY')

insert into 教室信息
values('CY102','CY')

insert into 教室信息
values('CY103','CY')

insert into 教室信息
values('CY104','CY')


insert into 教室信息
values('CY201','CY')

insert into 教室信息
values('CY202','CY')

insert into 教室信息
values('CY203','CY')

insert into 教室信息
values('CY204','CY')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
insert into 教室信息
values('GW101','GW')

insert into 教室信息
values('GW102','GW')

insert into 教室信息
values('GW103','GW')

insert into 教室信息
values('GW104','GW')


insert into 教室信息
values('GW201','GW')

insert into 教室信息
values('GW202','GW')

insert into 教室信息
values('GW203','GW')

insert into 教室信息
values('GW204','GW')

插入院系信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
insert into 院系信息
values('001','计算机')

insert into 院系信息
values('002','航天')

insert into 院系信息
values('003','机电工程')

insert into 院系信息
values('004','材料科学')

insert into 院系信息
values('005','数学')

insert into 院系信息
values('006','经济与管理')

插入教师信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
insert into 教师信息
values('0000000001','姜老师','男','006','教授')

insert into 教师信息
values('0000000002','邹老师','男','001','教授')

insert into 教师信息
values('0000000003','陈老师','女','001','教授')

insert into 教师信息
values('0000000004','金老师','男','006','教授')

insert into 教师信息
values('0000000005','左老师','男','001','教授')

insert into 教师信息
values('0000000006','刘老师','男','001','教授')

插入课程信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
insert into 课程信息
values('国际结算','EM32907','0000000001')

insert into 课程信息
values('数据库系统','CS33503','0000000002')

insert into 课程信息
values('编译系统','CS33502','0000000003')

insert into 课程信息
values('国际企业管理','EM32408','0000000004')

insert into 课程信息
values('模式识别与深度学习','CS33262','0000000005')

insert into 课程信息
values('操作系统设计与实现','CS32202','0000000006')

插入课程时间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
insert into 教室使用信息
 values('ZX301','周一三四','有课','CS32202')
 insert into 教室使用信息
 values('ZX301','周三三四','有课','CS32202')
  insert into 课程时间
 values('CS32202','周一三四','ZX301')
  insert into 课程时间
 values('CS32202','周三三四','ZX301')
 
 insert into 教室使用信息
 values('ZX201','周一七八','有课','CS33262')
 insert into 教室使用信息
 values('ZX201','周三五六','有课','CS33262')
 insert into 课程时间
 values('CS33262','周一七八','ZX201')
  insert into 课程时间
 values('CS33262','周三五六','ZX201')
 
 insert into 教室使用信息
 values('ZX202','周二三四','有课','CS33502')
 insert into 教室使用信息
 values('ZX202','周四一二','有课','CS33502')
 insert into 课程时间
 values('CS33502','周二三四','ZX202')
  insert into 课程时间
 values('CS33502','周四一二','ZX202')
 
 insert into 教室使用信息
 values('ZX102','周一五六','有课','CS33503')
 insert into 教室使用信息
 values('ZX102','周四五六','有课','CS33503')
 insert into 课程时间
 values('CS33503','周一五六','ZX102')
  insert into 课程时间
 values('CS33503','周四五六','ZX102')
 
 insert into 教室使用信息
 values('ZX402','周五一二','有课','EM32408')
 insert into 教室使用信息
 values('ZX402','周五三四','有课','EM32408')
 insert into 课程时间
 values('EM32408','周五一二','ZX402')
  insert into 课程时间
 values('EM32408','周五三四','ZX402')
 
 insert into 教室使用信息
 values('ZX601','周四三四','有课','EM32907')

 insert into 课程时间
 values('EM32907','周四三四','ZX601')

编写存储

插入课程信息存储

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create procedure information_entry
 @coursename varchar(15),--输入参数，课程名称
 @coursenumber varchar(7),--输入参数，课程编号
 @coursetime varchar(10),--输入参数，课程时间
 @teachername varchar(15),--输入参数，教师名称
 @teachernumber char(10),--输入参数，教师编号
 @buildingnumber varchar(5),--输入参数，教学楼编号
 @roomnumber varchar(5)--输入参数教室编号
AS
 insert into 课程信息
 values(@coursename,@coursenumber,@teachernumber)
 insert into 课程时间
 values(@coursenumber,@coursetime,@roomnumber)
 insert into 教室使用信息
 values(@roomnumber,@coursetime,'有课',@coursenumber)
go

查询指定教室使用情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create procedure classroom_query
 @room varchar(5),
 @time varchar(10),
 @state varchar(10)='空闲' output
 AS

 select @state=状态
 from 教室使用信息
 where 教室编号=@room and 时间=@time
 if @state IS NULL
   set  @state='空闲'
   else if @state='有课'
   begin
    select @state=课程编号
 from 教室使用信息
 where 教室编号=@room and 时间=@time

   end
 go

少一个查询指定课程

借用存储

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
declare @state varchar(10)
go
create procedure borrow
 @building varchar(5),
 @room varchar(5),
 @time varchar(10),
 @reason varchar(10),
 @state varchar(10) output
AS
declare @state1 varchar(10)
 select @state1=状态
 from 教室使用信息
 where 教室编号=@room and 时间=@time
 if @state1 IS NULL
 begin
   set  @state1='空闲'
   
   print '此时教室空闲，可以借用'
   insert into 教室使用信息
   values(@room,@time,@reason,'其他')
  end
else
  print '此教室被占用，正在'+@state1
  set @state=@state1
  print @state
  go

一个例子

1
2
3
4
select *from 教室使用信息
declare @state varchar(10)
exec borrow '001','1102','周一三四','其他',@state output
print @state

判断密码是否正确

1
2
3
4
5
6
7
8
9
10
11
12
13
create procedure password_query
 @user char(10),
 @password varchar(15),
 @state varchar(10)='false' output
AS
declare @password1 varchar(15)
 select @password1=密码  from 学生信息
 where 学号=@user
 if @password1 IS NOT NULL and @password1=@password
 set @state='true'
 else
 set @state='false'
go

删除课程

1
2
3
4
5
6
7
create procedure delete_course
 @number varchar(7)
 AS
delete from 课程信息 where 课程编号=@number
delete from 教室使用信息 where 课程编号=@number
delete from 课程时间 where 课程编号=@number

删除教师

1
2
3
4
5
6
7
8
9
10
create procedure delete_teacher
 @number char(10)
 AS
 delete from 课程时间 where 课程编号 in(
select 课程编号 from 课程信息 where 教师编号=@number)
delete from 教室使用信息 where 课程编号 in(
select 课程编号 from 课程信息 where 教师编号=@number)
delete from 课程信息 where 教师编号=@number
delete from 教师信息 where 教师编号=@number

创建存储往学生信息插入大量数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
--创建循环插入10000条记录的存储过程
CREATE  PROCEDURE CREATE_student 
AS
declare @cnt INT=0;
while @cnt<10000
begin
declare @name varchar(15)
set @name=(SELECT   LEFT(LOWER(NEWID()),8))
declare @password varchar(15)
set @password=(SELECT   LEFT(LOWER(NEWID()),9))
declare @sex INT
set @sex=(SELECT 1 + RAND() * 2)
declare @sno char(10)
set @sno=replace(str(@cnt,10),' ','0')
declare @y INT
set @y=(SELECT 1 + RAND() * 6)
    INSERT INTO 学生信息
    VALUES
    (
     @sno,
    @name
    ,CASE @sex  --随机选取3个枚举值
          when 1 then '男'
          when 2 then '女'                  
     END
	 ,CASE @y  --随机选取6个枚举值
          when 1 then '001'
          when 2 then '002' 
		  when 3 then '003'
          when 4 then '004'
		  when 5 then '005'
          when 6 then '006'
     END
	  ,@password
	  
   
    );
	set @cnt=@cnt+1;
end;

创建视图

创建视图，查询计算机学院邹老师所有课程名称

1
2
3
4
5
6
7
8
9
--创建视图，查询计算机学院邹老师所有课程名称
go
create VIEW view_邹
as 
select 课程名称 
from 课程信息
where 教师编号='0000000002'
go