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

文章目录

• 西电数据库实验-学生学籍管理系统 数据库设计
• • 需求分析
  • • 实体集
    • 联系集
  • 概念结构设计
  • 逻辑结构设计
  • • 实体集
    • • department(id‾,name)department(\underline {id},name)department(id​,name)
      • instructor(id‾,name,dept_id)instructor(\underline{id},name,dept\_id)instructor(id​,name,dept_id)
      • major(id‾,name,dept_id)major(\underline{id},name,dept\_id)major(id​,name,dept_id)
      • class(id‾,name,inst_id,major_id)class(\underline{id},name,inst\_id,major\_id)class(id​,name,inst_id,major_id)
      • course(id‾,major_id,name,credit,type)course(\underline{id},major\_id,name,credit,type)course(id​,major_id,name,credit,type)
      • section(id‾,crs_id,inst_id,year)section(\underline{id},crs\_id,inst\_id,year)section(id​,crs_id,inst_id,year)
      • student(id‾,name,cls_id,enroll_year)student(\underline{id},name,cls\_id,enroll\_year)student(id​,name,cls_id,enroll_year)
      • plan(id‾,year,grade,major_id,compulsory_credits,elective_credits)plan(\underline{id},year,grade,major\_id,compulsory\_credits,elective\_credits)plan(id​,year,grade,major_id,compulsory_credits,elective_credits)
    • 联系集
    • • takes(stu_id,sec_id‾,score,makeup,pass)takes(\underline{stu\_id,sec\_id},score,makeup,pass)takes(stu_id,sec_id​,score,makeup,pass)
  • 实现
  • • 查询学生选课及成绩
    • 查询学生必修课平均成绩,选修课平均成绩
    • 查询一个学生被哪些老师教过课
    • 查询应该开除的学生
  • 总结
  • 附录
  • • 建表
    • 插入数据
    • 建立视图

需求分析

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

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

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

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

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

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

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

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

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

实体集

联系集

概念结构设计

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

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

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

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

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

目前的ER图长这样

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

于是现在的ER图为

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

逻辑结构设计

实体集

department(id‾,name)instructor(id‾,name,dept_id)class(id‾,name,inst_id,major_id)major(id‾,name,dept_id)student(id‾,name,enroll_time,class_id)course(id‾,major_id,name,credit,type)section(id‾,course_id,year)plan(id‾,year,grade,major_id,compulsory_credits,elective_credits)\begin{aligned} 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{aligned} 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(id‾,name)department(\underline {id},name)department(id​,name)

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

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

instructor(id‾,name,dept_id)instructor(\underline{id},name,dept\_id)instructor(id​,name,dept_id)

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

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

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

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

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

dept_id作为外码,参考department.id

major(id‾,name,dept_id)major(\underline{id},name,dept\_id)major(id​,name,dept_id)

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

class(id‾,name,inst_id,major_id)class(\underline{id},name,inst\_id,major\_id)class(id​,name,inst_id,major_id)

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

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

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

即inst_id为外码,参照instructor.id

major_id为外码,参照major.id

course(id‾,major_id,name,credit,type)course(\underline{id},major\_id,name,credit,type)course(id​,major_id,name,credit,type)

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

id作为主码

major_id为外码,参照major.id

section(id‾,crs_id,inst_id,year)section(\underline{id},crs\_id,inst\_id,year)section(id​,crs_id,inst_id,year)

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

id作为主码

crs_id为外码参照course.id

inst_id为外码参照instructor.id

student(id‾,name,cls_id,enroll_year)student(\underline{id},name,cls\_id,enroll\_year)student(id​,name,cls_id,enroll_year)

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

id作为主码

cls_id为外码,参照class.id

plan(id‾,year,grade,major_id,compulsory_credits,elective_credits)plan(\underline{id},year,grade,major\_id,compulsory\_credits,elective\_credits)plan(id​,year,grade,major_id,compulsory_credits,elective_credits)

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

id为主码

major_id为外码参照major.id

联系集

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

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

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

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

takes(stu_id,sec_id‾,score,makeup,pass)takes(\underline{stu\_id,sec\_id},score,makeup,pass)takes(stu_id,sec_id​,score,makeup,pass)

(stu_id,sec_id)联合作为主键

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

实现

处理要求：

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

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

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

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

查询学生选课及成绩

selectstudent_id,student_name,course_id,course_name,course_credit,takes_score,takes_makeup,takes_pass
fromview_student_takes_section_course
wherestudent_id = '20009101015';

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

查询必修平均成绩

selectstudent_name,avg(takes_score)
fromview_student_takes_section_course
wherecourse_type = 'compulsory'and student_id = '20009101015';

查询选修成绩

selectstudent_name,avg(takes_score)
fromview_student_takes_section_course
wherecourse_type = 'elective'and student_id = '20009101015';

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

selectstudent_name,section_id,instructor_name
fromview_student_takes_section_instructor
wherestudent_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,-- 所在教学班级idenroll_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, -- 专业idcompulsory_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
selectstu_id as takes_stu_id,sec_id as takes_sec_id,makeup as takes_makeup,pass as takes_pass,score as takes_score
fromtakes;create
or replace view view_section as
selectid as section_id,crs_id as section_crs_id,inst_id as section_inst_id,year as section_year
fromsection;create
or replace view view_course as
selectid as course_id,name as course_name,major_id as course_major_id,credit as course_credit,type as course_type
fromcourse;create
or replace view view_student as
selectid as student_id,name as student_name,cls_id as student_cls_id,enroll_year as student_enroll_year
fromstudent;create
or replace view view_instructor as
selectid as instructor_id,name as instructor_name,dept_id as instructor_dept_id
frominstructor;-- 以学生id为依据,连接student和takes表,意在获得学生姓名,并向学生的class方向拓展,takes表则保留向section拓展的可能
create
or replace view view_student_takes as
select*
fromview_studentjoin 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*
fromview_student_takesjoin view_section on takes_sec_id = view_section.section_id;-- 拓展到了course,由此可以算出学生的学分了
create
or replace view view_student_takes_section_course as
select*
fromview_student_takes_sectionjoin view_course on section_crs_id = course_id;-- 拓展到指导老师,可以查学生被哪个老师教过课了
create
or replace view view_student_takes_section_instructor as
select*
fromview_student_takes_sectionjoin view_instructor on section_inst_id = instructor_id;-- 学生学分信息视图,可以给定姓名或者学号查成绩了,下面可以拓展到专业方案,为查询是否应该开除做铺垫
create
or replace view view_student_credit as
selectstudent_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
fromview_student_takes_section_course
group bystudent_id,section_year,takes_pass,course_type;-- 走student的另一条路,向class,major,plan拓展,意在获得学生当前应该遵守的专业计划
create
or replace view view_class as
selectid as class_id,name as class_name,major_id as class_major_id,inst_id as class_inst_id
fromclass;create
or replace view view_plan as
selectid 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
fromplan;-- 已经拓展到了专业,再连接上学期就可以继续连接计划了
create
or replace view view_major as
selectid as major_id,name as major_name,dept_id as major_dept_id
frommajor;create
or replace view view_student_class as
select*
fromview_studentjoin view_class on student_cls_id = class_id;-- student-class-major为了获得学生的所在专业
create
or replace view view_student_class_major as
select*
fromview_student_classjoin view_major on class_major_id = major_id;-- 只需要保留学生-专业信息,班级信息在查分时不重要
create
or replace view view_student_major as
selectstudent_id,major_id
fromview_student_class_major;-- 拓展到了学分,就差一步,再连接plan就可以查开除了
create
or replace view view_student_major_credit as
selectstudent_id,student_name,major_id,student_grade,takes_pass,course_type,sum_course_credit
fromview_student_major naturaljoin view_student_credit;-- 终于计划情况和学生个人情况的信息都汇总到了一张视图上
create
or replace view view_student_major_credit_plan as
selectstudent_id,student_name,major_id,student_grade,takes_pass,course_type,sum_course_credit,plan_compulsory_credits,plan_elective_credits
fromview_student_major_creditjoin view_plan on major_id = plan_major_idand student_grade = plan_grade;-- 查差两个学分的情况
create
or replace view view_lack_two as
select*
fromview_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*
fromview_student_major_credit_plan
wheretakes_pass = '0'and course_type = 'compulsory'and sum_course_credit > 10;-- 查累计必修学分差30分的情况
create
or replace view view_compulsory_fail_tot as
select*
fromview_student_major_credit_plan
wheretakes_pass = '0'and course_type = 'compulsory'
group bystudent_id
havingsum(sum_course_credit) > 30;-- 查选秀学分累计差20分的情况
create
or replace view view_elective_fail_tot as
select*
fromview_student_major_credit_plan
wheretakes_pass = '0'and course_type = 'elective'
group bystudent_id
havingsum(sum_course_credit) > 20;-- 汇总上述四种开除情形,给一个总的开除名单
create
or replace view view_expulsion as
selectstudent_id,student_name
fromview_lack_two
union
selectstudent_id,student_name
fromview_compulsory_fail_grade
union
selectstudent_id,student_name
fromview_compulsory_fail_tot
union
selectstudent_id,student_name
fromview_elective_fail_tot
group bystudent_id;