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

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

需求分析

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

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

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

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

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

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

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

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

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

实体集

语义	实体集
系	department
专业	major
班	class
学生	student
课程	course
教学计划	plan
教师	instructor

联系集

语义	联系集
一个系多个专业.一个专业属于一个系	dept_major
一个专业多个班,一个班属于一个专业	major_cls
一个班多个学生,一个学生属于一个班	cls_stu
一个系开各种课程,一个课程属于一个系	dept_crs
老师教多门课,一门课一个老师教	crs_inst
学生可以上多门课	takes
老师带多个班,一个班有一个老师(辅导员性质)	inst_cls
老师属于一个系	inst_dept
系给专业指定教学计划	dept_plan major_plan

概念结构设计

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

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

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

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

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

目前的ER图长这样

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

联系	转化为属性
dept_inst	instructor.dept_id
dept_major	major.dept_id
major_cls	class.major_id
cls_stu	student.cls_id
cls_inst	cls.inst_id
major_crs	course.major_id
major_plan	plan.major_id
dept_plan	不需要, 已经有plan.major_id, 就可以决定哪个系了

于是现在的ER图为

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

逻辑结构设计

实体集

$$\begin{array}{rl}department& (\underline{id},name)\\ instructor& (\underline{id},name,dept\_id)\\ class& (\underline{id},name,inst\_id,major\_id)\\ major& (\underline{id},name,dept\_id)\\ student& (\underline{id},name,enroll\_time,class\_id)\\ course& (\underline{id},major\_id,name,credit,type)\\ section& (\underline{id},course\_id,year)\\ plan& (\underline{id},year,grade,major\_id,compulsory\_credits,elective\_credits)\end{array}$$

$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';

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

查询必修平均成绩

select
    student_name,
    avg(takes_score)
from
    view_student_takes_section_course
where
    course_type = 'compulsory'
    and student_id = '20009101015';

查询选修成绩

select
    student_name,
    avg(takes_score)
from
    view_student_takes_section_course
where
    course_type = 'elective'
    and student_id = '20009101015';

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

select
    student_name,
    section_id,
    instructor_name
from
    view_student_takes_section_instructor
where
    student_id = '20009101015';

查询应该开除的学生

mysql> select * from view_expulsion;
+-------------+--------------+
| student_id  | student_name |
+-------------+--------------+
| 20009200591 | las          |
| 20009200582 | lql          |
+-------------+--------------+
2 rows in set (0.00 sec)

总结

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
);

插入数据

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

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

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);

建立视图

-- 所有的表不能直接拿来查,因为多个表连接的时候可能导致列重复,因此首先给每个表建立一个视图,意在修改属性名称,加上表名前缀,消除重复列
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;