西电数据库实验-学生学籍管理系统 数据库设计

西电数据库实验-学生学籍管理系统 数据库设计

需求分析

提取每个名词作为实体集,然后考虑实体集之间的关系.

最初的需求分析可能考虑不周全,在后面概念结构设计的时候还有机会改进完善

一个系可以若干专业，每个专业每年可以招若干班，每个班有若干学生。

系里对每个专业每年都制订了教学计划，学生每年必须按照教学计划修完一定学分的课程（必修课、限选课和任选课），如2000年入学的学生大三上学期必修课30学分，限选课10学分，任选课6学分。

系里的教师可以给多个班带课，但是不能给一个班带多门课程。

一门课程最多允许学生一次补考，学生达到如下条件之一的被开除：

一学期不及格的必修课学分超过10个；

不及格必修课学分累计超过30个；

不及格选修课学分累计超过20个；

实体集

联系集

概念结构设计

需求分析时,很容易就能想到的是,系,专业,班级,老师,学生,课程,教学计划各为一个实体集

但是这样设计是存在问题的,比如编译原理这门课,李老师开和王老师开,其课程名,学分都是一样的,这就造成了数据冗余,应该有一个静态的课程表,和一个动态的开课表.

课程表中注册课程的模板信息,比如课程名,开课的专业,学分等等万年不变的内容.开课表维护课程id,讲师id,以及开设时间等信息

于是就有 系,专业,班级,老师,学生,课程,开课,教学计划各为一个实体集

只要是有联系的两个实体集,首先建立联系集,然后考虑将联系集简化为其中一方的属性

目前的ER图长这样

其中一对一,多对一的联系,都可以作为多者一方的属性

于是现在的ER图为

只留下了一个takes联系集因为两头是多对多关系,不能省去

逻辑结构设计

实体集

d e p a r t m e n t ( i d ‾ , n a m e ) i n s t r u c t o r ( i d ‾ , n a m e , d e p t _ i d ) c l a s s ( i d ‾ , n a m e , i n s t _ i d , m a j o r _ i d ) m a j o r ( i d ‾ , n a m e , d e p t _ i d ) s t u d e n t ( i d ‾ , n a m e , e n r o l l _ t i m e , c l a s s _ i d ) c o u r s e ( i d ‾ , m a j o r _ i d , n a m e , c r e d i t , t y p e ) s e c t i o n ( i d ‾ , c o u r s e _ i d , y e a r ) p l a n ( i d ‾ , y e a r , g r a d e , m a j o r _ i d , c o m p u l s o r y _ c r e d i t s , e l e c t i v e _ c r e d i t s )

departmentinstructorclassmajorstudentcoursesectionplan​(id​,name)(id​,name,dept_id)(id​,name,inst_id,major_id)(id​,name,dept_id)(id​,name,enroll_time,class_id)(id​,major_id,name,credit,type)(id​,course_id,year)(id​,year,grade,major_id,compulsory_credits,elective_credits)​

$department(\underline{id},name)$

系表,学校里所有的系统一编号,name为系名.

两者都是码,选择id编号作为主码

$instructor(\underline{id},name,dept\_id)$

老师是属于系的,包括instructor和class等等低级表的编号有两种方法,

一种是全校的老师统一编排工号,任意两个老师不重号,此时id直接作为主码

而是一个系的老师同一编排工号,两个系的老师可以重号.此时(id,dept_id)联合才能作为主码

考虑现实中容易重名,name字段不作为主属性

西电老师的工号是全校统一编号的,因此直接id作为主码

dept_id作为外码,参考department.id

$major(\underline{id},name,dept\_id)$

专业也是属于系的,和老师的情况基本相同,全校的专业统一编号,id直接作为主码,dept_id作为外码参照department.id

$class(\underline{id},name,inst\_id,major\_id)$

班级是属于专业的,在西电,教学班也是全校统一编号的,方式是年份+系+专业+班号,比如2003058就是20级,计算机(03),软工卓越班(058)

因此这里id直接作为主码,

维护带班老师(可以理解为辅导员性质的老师)的inst_id,维护系的id

即inst_id为外码,参照instructor.id

major_id为外码,参照major.id

$course(\underline{id},major\_id,name,credit,type)$

西电的课程也是全校统一命名不带重复的,

id作为主码

major_id为外码,参照major.id

$section(\underline{id},crs\_id,inst\_id,year)$

西电的开课记录也是全校统一编号的,

id作为主码

crs_id为外码参照course.id

inst_id为外码参照instructor.id

$student(\underline{id},name,cls\_id,enroll\_year)$

西电的学生也是统一编号的

id作为主码

cls_id为外码,参照class.id

$plan(\underline{id},year,grade,major\_id,compulsory\_credits,elective\_credits)$

教学计划,不妨令他也是全校统一编号

id为主码

major_id为外码参照major.id

联系集

经过联系集到属性的转换,现在只剩下了takes这个学生选课联系

考虑该联系集如何转化为表

A学生对于1号课程只能选择一次,因此学生id和开课id联合就可以作为主码了

这里有一个前提是,学生id和开课id都是全校独立的,这就限制了一个学生只能选择一个开课课一次,选多了就会造成主键重复插入失败

$takes(\underline{stu\_id,sec\_id},score,makeup,pass)$

(stu_id,sec_id)联合作为主键

makeup是补考剩余次数,pass为是否通过

实现

处理要求：

1.查询学生所选修的课程及成绩，并给出必修课平均成绩和选修课平均成绩；

2.查某一个学生被哪些教师教过课；

3.查询应被开除的学生（假定差2学分即被开除）

具体查询时用到了视图,视图具体如何建立的,见附录代码或者本目录下的view_class.svg矢量图文件

查询学生选课及成绩

select
    student_id,
    student_name,
    course_id,
    course_name,
    course_credit,
    takes_score,
    takes_makeup,
    takes_pass
from
    view_student_takes_section_course
where
    student_id = '20009101015';
1
2
3
4
5
6
7
8
9
10
11
12
13

查询学生必修课平均成绩,选修课平均成绩

查询必修平均成绩

select
    student_name,
    avg(takes_score)
from
    view_student_takes_section_course
where
    course_type = 'compulsory'
    and student_id = '20009101015';
1
2
3
4
5
6
7
8

查询选修成绩

select
    student_name,
    avg(takes_score)
from
    view_student_takes_section_course
where
    course_type = 'elective'
    and student_id = '20009101015';
1
2
3
4
5
6
7
8

查询一个学生被哪些老师教过课

select
    student_name,
    section_id,
    instructor_name
from
    view_student_takes_section_instructor
where
    student_id = '20009101015';
1
2
3
4
5
6
7
8

查询应该开除的学生

mysql> select * from view_expulsion;
+-------------+--------------+
| student_id  | student_name |
+-------------+--------------+
| 20009200591 | las          |
| 20009200582 | lql          |
+-------------+--------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

总结

0.最好不要上来就空手套白狼,看看有经验的人怎么写,然后再自己写.

写之前参考了19级嵌入rk1和20级嵌入rk1的报告,使劲找茬,然后确保自己不会犯同样的错误.

仔细参考课本给的大学模式图,对自己的设计很有帮助

1.开始写代码之前,一定一定把所有都设计好,必须至少3范式,把应该作为属性还是关系表想好.

好的设计在增删改查的时候会方便很多.

问了19级计科网安rk3学长,他当时光数据库设计花了半个星期.

确实设计是个功夫活,不要吝啬时间.

2.以自己学校的实际情况为主要参考,遇到不知道咋整的情况,想想自己学校的数据库应该是怎么设计的

3.不要怕麻烦,应该分裂的表一定分裂,不要图省事把冗余的信息都放到一张表上.

比如如果把(课程名,课程学分,任课老师,开课id)放到一张表上.一门课可以有多个老师多开,但是课程学分都是一样的,这就存在传递函数依赖,开课id->课程名->课程学分.

更有甚者将选课的学生信息也放到上表中,鉴定为纯纯的shit

4.工具使用上

vscode中shift+alt+F可以自动缩进调整格式,ctrl+F2可以多行光标一起修改

datagrip等数据库工具可以做到可视化并且自动绘制图关系

5.sql语言上

1.外键不能少,并且注明级联修改和级联删除

2.建立视图,大大简化sql语句嵌套层数,提高可读性

3.命名一定要规范,知道每个视图每个表都是干啥的,并且有统一的命名方式,下划线就下划线到底,驼峰就驼峰到底,睡一觉起来也应该记得表和视图的作用

附录

插入使用的数据都是乱写的,如有雷同,纯属巧合

建表

USE university;
-- 首先删除所有的表然后重建,删除表的时候要按照拓扑顺序,先删除最顶层没有被任何表引用的表
DROP TABLE  IF EXISTS takes;
DROP TABLE  IF EXISTS plan;
DROP TABLE  IF EXISTS section;
DROP TABLE  IF EXISTS course;
DROP TABLE  IF EXISTS student;
DROP TABLE  IF EXISTS class;
DROP TABLE  IF EXISTS instructor;
DROP TABLE  IF EXISTS major;
DROP TABLE  IF EXISTS department;


-- 系表,只有id和name
CREATE TABLE department(
    id      varchar(255) NOT NULL UNIQUE PRIMARY KEY,
    name    varchar(255) NOT NULL
);

-- 专业表,有id,name,dept_id引用系id
CREATE TABLE   major(
    id      varchar(255) NOT NULL UNIQUE PRIMARY KEY,
    name    varchar(255) NOT NULL,
    dept_id varchar(255) NOT NULL,
    FOREIGN KEY(dept_id) REFERENCES department(id) ON DELETE CASCADE ON UPDATE CASCADE
);


-- 教师表
CREATE TABLE   instructor(
    id      varchar(255) NOT NULL UNIQUE PRIMARY KEY,
    name    varchar(255) NOT NULL,
    dept_id varchar(255) NOT NULL,
    FOREIGN KEY(dept_id) REFERENCES department(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- 班级表
CREATE TABLE   class(
    id      varchar(255) NOT NULL UNIQUE PRIMARY KEY,
    name    varchar(255) NOT NULL,
    major_id varchar(255) NOT NULL,
    inst_id varchar(255) NOT NULL,
    FOREIGN KEY(major_id) REFERENCES major(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(inst_id) REFERENCES instructor(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- 静态课程表,万年不变,一条记录维护一门课程比如数据库共有的信息,显然任课老师不会出现在该表上
CREATE TABLE   course(
    id      varchar(255) NOT NULL UNIQUE PRIMARY KEY,
    name    varchar(255) NOT NULL,
    major_id varchar(255) NOT NULL,
    credit   INT NOT NULL ,
    type    ENUM('compulsory','elective'),
    FOREIGN KEY(major_id) REFERENCES major(id)
);

-- 实际开课表,维护某课程一次开课的动态信息,比如任课老师,开课时间(年)
CREATE TABLE   section(
    id      varchar(255) NOT NULL UNIQUE PRIMARY KEY,
    crs_id    varchar(255) NOT NULL,
    inst_id varchar(255) NOT NULL,
    year   YEAR NOT NULL,
    FOREIGN KEY(crs_id) REFERENCES course(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(inst_id) REFERENCES instructor(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- 学生表
CREATE TABLE   student(
    id      varchar(255) NOT NULL UNIQUE PRIMARY KEY ,
    name    varchar(255) NOT NULL,
    cls_id varchar(255) NOT NULL,-- 所在教学班级id
    enroll_year   YEAR NOT NULL,-- 入学时间
    FOREIGN KEY(cls_id) REFERENCES class(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- 教学计划
CREATE TABLE   plan(
    id      varchar(255) NOT NULL UNIQUE PRIMARY KEY,
    year   YEAR NOT NULL,-- 年份
    grade   ENUM('1','2','3','4') NOT NULL,-- 一个专业的教学计划也要根据年纪有所不同
    major_id    varchar(255) NOT NULL, -- 专业id
    compulsory_credits  INT NULL,   -- 必修学分要求
    elective_credits    INT NULL,   -- 选修学分要求
    FOREIGN KEY(major_id) REFERENCES major(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- 选课信息表,记录section和student的联系,表明哪个学生选了哪门课,结果如何
CREATE TABLE   takes(
    stu_id varchar(255) NOT NULL,
    sec_id varchar(255) NOT NULL,
    score INT NOT NULL,
    makeup ENUM('0','1') NOT NULL,
    pass   ENUM('0','1') NOT NULL,
    FOREIGN KEY(stu_id) REFERENCES student(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(sec_id) REFERENCES section(id) ON DELETE CASCADE ON UPDATE CASCADE
);

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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97

插入数据

插入使用的数据都是乱写的,如有雷同,纯属巧合

-- 插入使用的数据都是乱写的,如有雷同,纯属巧合

INSERT   INTO department(id,name)VALUES('1','CS');
INSERT   INTO department(id,name)VALUES('2','EE');
INSERT   INTO department(id,name)VALUES('3','PHY');
INSERT   INTO department(id,name)VALUES('4','MATH');
INSERT   INTO department(id,name)VALUES('5','AUTO');
INSERT   INTO department(id,name)VALUES('6','FORE');




INSERT   INTO major(id,name,dept_id)VALUES('1','SE','1');
INSERT   INTO major(id,name,dept_id)VALUES('2','CS','1');
INSERT   INTO major(id,name,dept_id)VALUES('3','IS','1');
INSERT   INTO major(id,name,dept_id)VALUES('4','AI','1');
INSERT   INTO major(id,name,dept_id)VALUES('5','EE','2');
INSERT   INTO major(id,name,dept_id)VALUES('6','ES','2');
INSERT   INTO major(id,name,dept_id)VALUES('7','CE','2');
INSERT   INTO major(id,name,dept_id)VALUES('8','ME','2');
INSERT   INTO major(id,name,dept_id)VALUES('9','ME','2');
INSERT   INTO major(id,name,dept_id)VALUES('10','IE','2');


INSERT   INTO instructor(id,name,dept_id)VALUES('1','wxb','1');
INSERT   INTO instructor(id,name,dept_id)VALUES('2','dlb','1');
INSERT   INTO instructor(id,name,dept_id)VALUES('3','ghc','1');
INSERT   INTO instructor(id,name,dept_id)VALUES('4','zsp','1');
INSERT   INTO instructor(id,name,dept_id)VALUES('5','zl','1');
INSERT   INTO instructor(id,name,dept_id)VALUES('6','yq','1');
INSERT   INTO instructor(id,name,dept_id)VALUES('11','af','1');
INSERT   INTO instructor(id,name,dept_id)VALUES('7','gxd','2');
INSERT   INTO instructor(id,name,dept_id)VALUES('8','cb','2');
INSERT   INTO instructor(id,name,dept_id)VALUES('9','zwt','2');
INSERT   INTO instructor(id,name,dept_id)VALUES('10','ll','2');
INSERT   INTO instructor(id,name,dept_id)VALUES('12','zf','2');
INSERT   INTO instructor(id,name,dept_id)VALUES('13','lh','2');
INSERT   INTO instructor(id,name,dept_id)VALUES('14','lx','2');
INSERT   INTO instructor(id,name,dept_id)VALUES('15','gx','2');
INSERT   INTO instructor(id,name,dept_id)VALUES('16','jzp','2');
INSERT   INTO instructor(id,name,dept_id)VALUES('17','dy','2');


INSERT   INTO class(id,name,inst_id,major_id)VALUES('2003058','SEexcel','6','1');
INSERT   INTO class(id,name,inst_id,major_id)VALUES('2003052','SEnormal1','6','1');
INSERT   INTO class(id,name,inst_id,major_id)VALUES('2003053','SEnormal2','6','1');
INSERT   INTO class(id,name,inst_id,major_id)VALUES('2003054','SEnormal3','11','1');
INSERT   INTO class(id,name,inst_id,major_id)VALUES('2003055','SEnormal2','11','2');
INSERT   INTO class(id,name,inst_id,major_id)VALUES('2003051','CSexcel','11','2');
INSERT   INTO class(id,name,inst_id,major_id)VALUES('2003050','CSnormal1','11','2');

INSERT   INTO student(id,name,enroll_year,cls_id)VALUES('20009101015','a','2020','2003058');
INSERT   INTO student(id,name,enroll_year,cls_id)VALUES('20079100002','b','2020','2003058');
INSERT   INTO student(id,name,enroll_year,cls_id)VALUES('20049200450','c','2020','2003058');
INSERT   INTO student(id,name,enroll_year,cls_id)VALUES('20009200685','d','2020','2003058');
INSERT   INTO student(id,name,enroll_year,cls_id)VALUES('20079100044','e','2020','2003058');
INSERT   INTO student(id,name,enroll_year,cls_id)VALUES('20049200192','f','2020','2003058');
INSERT   INTO student(id,name,enroll_year,cls_id)VALUES('20069100175','g','2020','2003058');
INSERT   INTO student(id,name,enroll_year,cls_id)VALUES('20049200342','h','2020','2003058');
INSERT   INTO student(id,name,enroll_year,cls_id)VALUES('20009101515','i','2020','2003058');
INSERT   INTO student(id,name,enroll_year,cls_id)VALUES('20009200549','j','2020','2003052');
INSERT   INTO student(id,name,enroll_year,cls_id)VALUES('20009201250','k','2020','2003051');
INSERT   INTO student(id,name,enroll_year,cls_id)VALUES('20009201270','l','2020','2003053');
INSERT   INTO student(id,name,enroll_year,cls_id)VALUES('20009200043','m','2020','2003051');
INSERT   INTO student(id,name,enroll_year,cls_id)VALUES('20009200374','n','2020','2003053');
INSERT   INTO student(id,name,enroll_year,cls_id)VALUES('20009200472','o','2020','2003053');
INSERT   INTO student(id,name,enroll_year,cls_id)VALUES('20009200179','p','2020','2003051');
INSERT   INTO student(id,name,enroll_year,cls_id)VALUES('20009200582','q','2020','2003053');
INSERT   INTO student(id,name,enroll_year,cls_id)VALUES('20009200591','r','2020','2003052');


INSERT   INTO plan(id,year,grade,major_id,compulsory_credits,elective_credits)VALUES('4','2022','4','1',8,0);
INSERT   INTO plan(id,year,grade,major_id,compulsory_credits,elective_credits)VALUES('3','2022','3','1',10,4);
INSERT   INTO plan(id,year,grade,major_id,compulsory_credits,elective_credits)VALUES('2','2022','2','1',8,4);
INSERT   INTO plan(id,year,grade,major_id,compulsory_credits,elective_credits)VALUES('1','2022','1','1',16,4);


INSERT   INTO course(id,major_id,name,credit,type)VALUES('cs-101','1','C',4,'compulsory');
INSERT   INTO course(id,major_id,name,credit,type)VALUES('cs-102','1','HigherMath(I)',5,'compulsory');
INSERT   INTO course(id,major_id,name,credit,type)VALUES('cs-103','1','HigherMath(II)',5,'compulsory');
INSERT   INTO course(id,major_id,name,credit,type)VALUES('cs-104','1','Physics(I)',4,'compulsory');
INSERT   INTO course(id,major_id,name,credit,type)VALUES('cs-201','1','Physics(II)',4,'compulsory');
INSERT   INTO course(id,major_id,name,credit,type)VALUES('se-202','1','JAVA',2,'elective');
INSERT   INTO course(id,major_id,name,credit,type)VALUES('se-203','1','C++',3,'elective');
INSERT   INTO course(id,major_id,name,credit,type)VALUES('cs-204','1','DataStructure',4,'compulsory');
INSERT   INTO course(id,major_id,name,credit,type)VALUES('cs-205','1','DiscreteMath',3,'elective');
INSERT   INTO course(id,major_id,name,credit,type)VALUES('cs-206','1','MathAnalize',2,'elective');
INSERT   INTO course(id,major_id,name,credit,type)VALUES('se-207','1','Python',2,'elective');

INSERT   INTO course(id,major_id,name,credit,type)VALUES('cs-301','1','Algorithm',5,'compulsory');
INSERT   INTO course(id,major_id,name,credit,type)VALUES('cs-302','1','OperatingSystem',5,'compulsory');
INSERT   INTO course(id,major_id,name,credit,type)VALUES('cs-303','1','ComputerComponent',5,'compulsory');
INSERT   INTO course(id,major_id,name,credit,type)VALUES('se-304','1','SoftwareEngineer',5,'compulsory');
INSERT   INTO course(id,major_id,name,credit,type)VALUES('se-305','1','SoftwareArchitecture',4,'elective');
INSERT   INTO course(id,major_id,name,credit,type)VALUES('se-306','1','SoftwareEconomics',6,'elective');
INSERT   INTO course(id,major_id,name,credit,type)VALUES('cs-307','1','Database',6,'compulsory');

INSERT   INTO course(id,major_id,name,credit,type)VALUES('se-401','1','WebDevelopmentIntroduction',3,'elective');
INSERT   INTO course(id,major_id,name,credit,type)VALUES('se-402','1','BigDataIntroduction',3,'elective');
INSERT   INTO course(id,major_id,name,credit,type)VALUES('se-403','1','InformationSecurityIntroduction',3,'elective');
INSERT   INTO course(id,major_id,name,credit,type)VALUES('se-404','1','GraduationProject',8,'compulsory');


INSERT   INTO section(id,crs_id,year,inst_id)VALUES('cs-101-1','cs-101','2022','4');
INSERT   INTO section(id,crs_id,year,inst_id)VALUES('cs-307-1','cs-307','2022','1');
INSERT   INTO section(id,crs_id,year,inst_id)VALUES('cs-307-2','cs-307','2022','2');
INSERT   INTO section(id,crs_id,year,inst_id)VALUES('cs-307-3','cs-307','2022','3');
INSERT   INTO section(id,crs_id,year,inst_id)VALUES('cs-301-1','cs-301','2022','4');
INSERT   INTO section(id,crs_id,year,inst_id)VALUES('cs-301-2','cs-301','2022','13');
INSERT   INTO section(id,crs_id,year,inst_id)VALUES('cs-301-3','cs-301','2022','14');
INSERT   INTO section(id,crs_id,year,inst_id)VALUES('cs-302-1','cs-302','2022','11');
INSERT   INTO section(id,crs_id,year,inst_id)VALUES('cs-302-2','cs-302','2022','12');
INSERT   INTO section(id,crs_id,year,inst_id)VALUES('cs-303-1','cs-303','2022','15');
INSERT   INTO section(id,crs_id,year,inst_id)VALUES('cs-303-2','cs-303','2022','16');
INSERT   INTO section(id,crs_id,year,inst_id)VALUES('se-304-1','se-304','2022','17');
INSERT   INTO section(id,crs_id,year,inst_id)VALUES('se-305-1','se-305','2022','17');
INSERT   INTO section(id,crs_id,year,inst_id)VALUES('se-306-1','se-306','2022','17');


INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009101015','cs-301-1','1','1',100);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009101015','cs-302-1','1','1',98);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009101015','cs-303-1','1','1',99);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009101015','se-304-1','1','1',95);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009101015','se-305-1','1','1',91);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009101015','se-306-1','1','1',92);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009101015','cs-307-1','1','1',97);

INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200591','cs-301-1','0','0',20);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200591','cs-302-2','0','0',30);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200591','cs-303-1','0','0',25);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200591','se-304-1','0','0',30);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200591','se-305-1','0','0',40);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200591','se-306-1','0','0',20);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200591','cs-307-3','0','0',30);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200591','cs-301-2','0','0',30);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200591','cs-301-3','0','0',30);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200591','cs-302-1','0','0',30);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200591','cs-307-2','0','0',30);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200591','cs-307-1','0','0',30);


INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200582','cs-301-1','0','0',20);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200582','cs-302-2','0','0',30);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200582','cs-303-1','0','0',25);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200582','se-304-1','0','0',30);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200582','se-305-1','0','0',40);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200582','se-306-1','0','0',20);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200582','cs-307-3','0','0',30);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200582','cs-301-2','0','0',30);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200582','cs-301-3','0','0',30);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200582','cs-302-1','0','0',30);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200582','cs-307-2','0','0',30);
INSERT INTO takes(stu_id,sec_id,makeup,pass,score)VALUES('20009200582','cs-307-1','0','0',30);


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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155

建立视图

-- 所有的表不能直接拿来查,因为多个表连接的时候可能导致列重复,因此首先给每个表建立一个视图,意在修改属性名称,加上表名前缀,消除重复列
create
or replace view view_takes as
select
    stu_id as takes_stu_id,
    sec_id as takes_sec_id,
    makeup as takes_makeup,
    pass as takes_pass,
    score as takes_score
from
    takes;

create
or replace view view_section as
select
    id as section_id,
    crs_id as section_crs_id,
    inst_id as section_inst_id,
    year as section_year
from
    section;

create
or replace view view_course as
select
    id as course_id,
    name as course_name,
    major_id as course_major_id,
    credit as course_credit,
    type as course_type
from
    course;

create
or replace view view_student as
select
    id as student_id,
    name as student_name,
    cls_id as student_cls_id,
    enroll_year as student_enroll_year
from
    student;

create
or replace view view_instructor as
select
    id as instructor_id,
    name as instructor_name,
    dept_id as instructor_dept_id
from
    instructor;


-- 以学生id为依据,连接student和takes表,意在获得学生姓名,并向学生的class方向拓展,takes表则保留向section拓展的可能
create
or replace view view_student_takes as
select
    *
from
    view_student
    join view_takes on view_student.student_id = view_takes.takes_stu_id;

-- 以section_id为依据,向section方向拓展,由此可以继续拓展到任课教师和静态课程信息
create
or replace view view_student_takes_section as
select
    *
from
    view_student_takes
    join view_section on takes_sec_id = view_section.section_id;

-- 拓展到了course,由此可以算出学生的学分了
create
or replace view view_student_takes_section_course as
select
    *
from
    view_student_takes_section
    join view_course on section_crs_id = course_id;

-- 拓展到指导老师,可以查学生被哪个老师教过课了
create
or replace view view_student_takes_section_instructor as
select
    *
from
    view_student_takes_section
    join view_instructor on section_inst_id = instructor_id;

-- 学生学分信息视图,可以给定姓名或者学号查成绩了,下面可以拓展到专业方案,为查询是否应该开除做铺垫
create
or replace view view_student_credit as
select
    student_id,
    course_major_id,
    student_name,
    ABS(section_year - student_enroll_year + 1) as student_grade,
    takes_pass,
    course_type,
    sum(course_credit) as sum_course_credit
from
    view_student_takes_section_course
group by
    student_id,
    section_year,
    takes_pass,
    course_type;


-- 走student的另一条路,向class,major,plan拓展,意在获得学生当前应该遵守的专业计划
create
or replace view view_class as
select
    id as class_id,
    name as class_name,
    major_id as class_major_id,
    inst_id as class_inst_id
from
    class;


create
or replace view view_plan as
select
    id as plan_id,
    year as plan_year,
    grade as plan_grade,
    major_id as plan_major_id,
    compulsory_credits as plan_compulsory_credits,
    elective_credits as plan_elective_credits
from
    plan;

-- 已经拓展到了专业,再连接上学期就可以继续连接计划了
create
or replace view view_major as
select
    id as major_id,
    name as major_name,
    dept_id as major_dept_id
from
    major;


create
or replace view view_student_class as
select
    *
from
    view_student
    join view_class on student_cls_id = class_id;

-- student-class-major为了获得学生的所在专业
create
or replace view view_student_class_major as
select
    *
from
    view_student_class
    join view_major on class_major_id = major_id;

-- 只需要保留学生-专业信息,班级信息在查分时不重要
create
or replace view view_student_major as
select
    student_id,
    major_id
from
    view_student_class_major;

-- 拓展到了学分,就差一步,再连接plan就可以查开除了
create
or replace view view_student_major_credit as
select
    student_id,
    student_name,
    major_id,
    student_grade,
    takes_pass,
    course_type,
    sum_course_credit
from
    view_student_major natural
    join view_student_credit;

-- 终于计划情况和学生个人情况的信息都汇总到了一张视图上
create
or replace view view_student_major_credit_plan as
select
    student_id,
    student_name,
    major_id,
    student_grade,
    takes_pass,
    course_type,
    sum_course_credit,
    plan_compulsory_credits,
    plan_elective_credits
from
    view_student_major_credit
    join view_plan on major_id = plan_major_id
    and student_grade = plan_grade;

-- 查差两个学分的情况
create
or replace view view_lack_two as
select
    *
from
    view_student_major_credit_plan
where
    (
        (
            (sum_course_credit - plan_compulsory_credits < -2)
            and course_type = 'compulsory'
        )
        or (
            (sum_course_credit - plan_elective_credits < -2)
            and course_type = 'elective'
        )
    );

-- 查一级中必修学分差10分的情况
create
or replace view view_compulsory_fail_grade as
select
    *
from
    view_student_major_credit_plan
where
    takes_pass = '0'
    and course_type = 'compulsory'
    and sum_course_credit > 10;

-- 查累计必修学分差30分的情况
create
or replace view view_compulsory_fail_tot as
select
    *
from
    view_student_major_credit_plan
where
    takes_pass = '0'
    and course_type = 'compulsory'
group by
    student_id
having
    sum(sum_course_credit) > 30;

-- 查选秀学分累计差20分的情况
create
or replace view view_elective_fail_tot as
select
    *
from
    view_student_major_credit_plan
where
    takes_pass = '0'
    and course_type = 'elective'
group by
    student_id
having
    sum(sum_course_credit) > 20;

-- 汇总上述四种开除情形,给一个总的开除名单
create
or replace view view_expulsion as
select
    student_id,
    student_name
from
    view_lack_two
union
select
    student_id,
    student_name
from
    view_compulsory_fail_grade
union
select
    student_id,
    student_name
from
    view_compulsory_fail_tot
union
select
    student_id,
    student_name
from
    view_elective_fail_tot
group by
    student_id;

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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293