项目——基于Oracle实现一个简易版的教务系统

一、背景知识

本教务系统用户分为，学生，教师，系主任，管理员。学生具有选课功能，管理员具有排课功能。对于排课与选课考虑了简单的冲突检测，如下：

1. 根据老师教学课表，为教学班所有的同学建立对相应课程的学习表
2. 管理员手动排课，系统检查教学表，课程表是否内容与时间冲突
3. 学生自助选课，检查是否课程冲突，时间冲突

二、需求分析（功能确定）

三、概念分析（E-R图）

1. 设计实体：

学生、老师、课程、班级

2. 现实关系：

一个老师可以教多个课程，多个老师可以共同教一个课程
一个老师可以教多个班级，多个老师可以共同教一个班级
一个学生可以学习多个课程，多个学生可以共同学习一个课程
一个学生只能属于一个班级，多个学生可以共同属于一个班级

3. 确定基础ER图
   
4. 实体与关系的属性

实体属性：
老师：姓名，工号，密码
学生：姓名，学号，密码，班级号
课程：名称，编号，类型，学分，学时
班级：名称，编号，班主任工号（这里班主任工号可有可无，暂定一个班主任）
联系属性：
任教：老师工号与班级建立连接
教学：老师工号与课程建立连接
学习：学生学号与课程建立连接，另外包含课程成绩

5. 完善ER图
   

四、逻辑分析（关系表）

将E-R 图转化为关系模型，并指出各关系的主码和外码
（加粗代表主码，斜体代表外码）
教师（工号，密码，姓名）；
课程（编号，名称，类型，学分，学时）；
学生（学号，密码，姓名，所属班级编号）；
班级（编号，名称，班主任工号）；
教学（教师工号，课程编号，班级编号，上课时间）；
学习（学生学号，课程编号，成绩）；

五、物理分析（数据类型）

六、代码实现

1. 创建表

--教师表
create table teacher
(tea_id int primary key,
tea_password varchar(20) not null,
tea_name varchar(20) not null
);

--课程表
create table course
(cou_id int primary key,
cou_name varchar(100) not null,
cou_type varchar(20) not null,
stu_score int not null,
stu_time int not null
);

--班级表
create table classes
(cla_id int primary key,
cla_name varchar(20) not null,
head_id int,
foreign key (head_id) references teacher(tea_id)
);

--学生表
create table student
(stu_id int primary key,
stu_password varchar(20) not null,
stu_name varchar(20) not null,
class_id int not null,
foreign key(class_id) references classes(cla_id)
);

--教学表
create table teach(
teacher_id int,
class_id int,
course_id int,
study_time varchar(20) not null,
foreign key(teacher_id) references teacher(tea_id),
foreign key(class_id) references classes(cla_id),
foreign key(course_id) references course(cou_id)
);

--学习表
(在teach初始化完成后执行)
create table study(stu_id,cou_id)
as select stu_id,course_id
from teach,student
where student.class_id=teach.class_id;
alter table study add grade int;

2. 插入数据

--教师列表
insert into teacher values('10001','123','唐卷');
insert into teacher values('10002','123','杨朔');
insert into teacher values('10003','123','谭恒良');
insert into teacher values('10004','123','丁子旋');
insert into teacher values('10005','123','陈文彬');
insert into teacher values('10006','123','陶文正');
insert into teacher values('10007','123','杨柳');
insert into teacher values('10008','123','颜国风');
insert into teacher values('10009','123','王显珉');

--班级列表
insert into classes values('40181','计科181','10009');
insert into classes values('40182','计科182','10008');
insert into classes values('40183','计科183','10003');
insert into classes values('40184','计科184','10009');
insert into classes values('40185','计科185','10002');
insert into classes values('40186','计科186','10004');

--课程列表
insert into course values('30001','编译原理','专业必修','2','32');
insert into course values('30002','编译原理实验','专业必修','0.5','16');
insert into course values('30003','机器学习与数据挖掘','专业必修','3','48');
insert into course values('30004','机器学习与数据挖掘实验','专业必修','0.5','16');
insert into course values('30005','人工智能原理','专业必修','3','48');
insert into course values('30006','人工智能原理实验','专业必修','1','32');
insert into course values('30007','Unix/Linux操作系统分析','专业选修','2','32');
insert into course values('30008','Unix/Linux操作系统分析实验','专业选修','0.5','16');
insert into course values('30009','算法设计与分析','专业选修','2','32');

--学生列表
insert into student values('20001','123','张三','40183');
insert into student values('20002','123','李四','40182');
insert into student values('20003','123','吴芳','40183');
insert into student values('20004','123','刘静','40181');
insert into student values('20005','123','金泽','40186');
insert into student values('20006','123','张良仁','40183');
insert into student values('20007','123','陈鹏','40185');
insert into student values('20008','123','林岭','40186');
insert into student values('20009','123','张义谋','40185');
insert into student values('20010','123','陈淇','40181');
insert into student values('20011','123','柳忠','40186');
insert into student values('20012','123','杨阳','40182');

--教学列表
insert into teach values('10001','40181','30001','1-16周，周三，3-4节');
insert into teach values('10001','40181','30002','1-16周，周三，7-8节');
insert into teach values('10001','40182','30001','1-16周，周三，3-4节');
insert into teach values('10001','40182','30002','1-16周，周三，7-8节');
insert into teach values('10002','40181','30003','1-16周，周三，1-2节');
insert into teach values('10002','40183','30003','1-16周，周三，1-2节');
insert into teach values('10002','40181','30004','1-16周，周三，5-6节');
insert into teach values('10002','40182','30004','1-16周，周三，7-8节');
insert into teach values('10003','40181','30005','1-8周，周一，3-4节');
insert into teach values('10003','40182','30005','1-8周，周一，3-4节');
insert into teach values('10003','40183','30005','1-8周，周一，3-4节');
insert into teach values('10003','40181','30005','1-16周，周五，1-2节');
insert into teach values('10003','40182','30005','1-16周，周五，1-2节');
insert into teach values('10003','40183','30005','1-16周，周五，1-2节');
insert into teach values('10003','40181','30006','1-16周，周五，7-8节');
insert into teach values('10003','40182','30006','1-16周，周五，5-6节');
insert into teach values('10003','40183','30006','1-16周，周五，3-4节');
insert into teach values('10006','40181','30007','1-10周，周二，9-11节');
insert into teach values('10006','40181','30008','1-8周，周二，1-2节');
insert into teach values('10006','40182','30007','1-10周，周二，9-11节');
insert into teach values('10006','40182','30008','1-8周，周三，5-6节');
insert into teach values('10005','40181','30009','1-16周，周一，5-6节');
insert into teach values('10005','40182','30009','1-16周，周一，5-6节');
insert into teach values('10005','40183','30009','1-16周，周一，5-6节');
insert into teach values('10007','40184','30009','1-16周，周一，5-6节');
insert into teach values('10007','40185','30009','1-16周，周一，5-6节');
insert into teach values('10007','40186','30009','1-16周，周一，5-6节');

--更新学习列表的成绩
update study set grade='89' where stu_id='20002' and cou_id='30001';
update study set grade='85' where stu_id='20004' and cou_id='30001';
update study set grade='84' where stu_id='20002' and cou_id='30002';
update study set grade='91' where stu_id='20004' and cou_id='30002';

七、分析实体对表的权限

注：红√表示只对自己的列表有相关功能

八、功能的代码实现

1. 学生功能

--查询课表
create view stu_table(tea_id,tea_name,cou_id,cou_name,study_time)
as select teach.teacher_id, teacher.tea_name, course.cou_id, course.cou_name, teach.study_time
from student,classes,teach,course,teacher
where student.class_id=classes.cla_id and classes.cla_id=teach.class_id 
and teach.course_id=course.cou_id and teach.teacher_id=teacher.tea_id and student.stu_id='20002';

--查询分数
create view stu_grade(course_id,course_name, stu_score, grade)
as select study.cou_id, course.cou_name, course.stu_score, study.grade from study,course
where stu_id='20002' and course.cou_id=study.cou_id and grade is not null;

--查询绩点（总学分，算术平均分，加权平均分）
create view stu_point(all_score,avg_grade,grade_piont)
as select sum(stu_score),avg(grade),sum(stu_score*grade)/sum(stu_score) 
from stu_grade;

2. 教师功能

--教师任教课程
create view tea_course(cou_id, cou_name)
as select distinct cou_id, cou_name
from teach,course 
where teach.course_id=course.cou_id and teach.teacher_id='10001';

--根据点击的任教课程进行成绩查询与修改
create view tea_grade(cou_id, cou_name,stu_id,stu_name,grade)
as select study.cou_id, cou_name, study.stu_id,stu_name,grade
from teach,study,course,student 
where teach.course_id=course.cou_id and teach.class_id=student.class_id
and student.stu_id=study.stu_id and teach.course_id=study.cou_id
and teach.teacher_id='10001' and teach.course_id='30001';

--任课课表查询
create tea_table(class_id,class_nname,cou_id,cou_name,study_time)
as select cla_id,cla_name,cou_id,cou_name,study_time
from teach,classes,course
where teach.class_id=classes.cla_id and teach.course_id=course.cou_id
and teach.teacher_id='10001';

3. 系主任功能——整合批量的学生功能和教师功能（删除id挑选条件）

4. 管理员功能

--用于手动排课中的专业选修(判断老师课表是否时间冲突)
select count(*) from (select teacher_id from teach
where teacher_Id Like'10001' and study_time Like '1-16周，周三，7-8节');

--用于手动排课中的专业必修(判断老师课表是否时间冲突 and 判断学生课表是否课程冲突或时间冲突)
select count(*) from(select teacher_id from teach
where teacher_id='10001' and study_time='1-16周，周三，1-2节'
or class_id='40183' and course_id='30001' and study_time='' or class_id='40183' and study_time='1-16周，周三，1-2节');

--用于学生自助选课(检查学生课表是否时间冲突或课程冲突)
select count(*) from (
select teach.teacher_id, teacher.tea_name, course.cou_id, course.cou_name, teach.study_time
from student,classes,teach,course,teacher
where student.class_id=classes.cla_id and classes.cla_id=teach.class_id 
and teach.course_id=course.cou_id and teach.teacher_id=teacher.tea_id and student.stu_id='20002')
where study_time='1-8周，周一，3-4节' or cou_id='30002' and study_time='';

十、不足与思考

1、学生选课实际上还涉及并发性问题
2、管理员功能应该实现智能排课，管理员只做排课合法性检测
3、教务系统存储大量数据，涉及查询效率优化的需求