大学生创新创业项目管理系统设计——数据库实验九
本实验为自己设计完成，我当年数据库实验得了94分
目录
1.实验目的
2.实验内容和要求
3.实验步骤
4.实验心得
实验九 数据库设计
1.实验目的
掌握数据库设计的过程和方法。
2.实验内容和要求
（35）大学生创新创业项目管理系统设计
一个学生可以参加多个创新创业项目，一个创新项目也可以多个学生参加，创新项目立 项分为国家、省市、学校等不同的等级，有不同的经费资助，每个项目可以有多个指导教师， 创新项目可以用论文、软著、专利结题，论文有作者、期刊名称、发表时间、卷期页号等信 息，软著有发明人、软著名称、软著登记号等信息，专利有申请号、申请时间和发明人等信 息。试设计一个大学生创新创业管理系统，能够完成对学生创新创业的管理，可以实现统计 高校各个学院某个时间段内创新创业的参加和结题情况，也可以统计各个年级某个时间段内 创新创业的参加情况。
对以上题目进行数据库设计，通过网络平台或图书馆查找相关文献进行需求分析，按照下面的实验步骤完成设计要求。
3.实验步骤
（1）根据所选题目进行系统需求分析和系统设计，画出系统的 E-R 图，给出实体或联
系的属性，标明联系的种类；
由上图可知，学生、项目、论文（或软著、专利）之间存在冗余，我考虑了去除“结题”这一联系，但是由于学生与项目之间是多对多的关系、学生与论文（或软著、专利）之间也是多对多的关系，去除“结题”这一联系后，对于指定项目的结题成果将无法查询到，所以决定保留此冗余，以便于后续查询。
（2）把 E-R 图转换为关系模式；
以下画下划线表示主键，字体加粗的表示外键
1)院系（院系号，院系名）
2）专业（专业号，专业名，院系号）
3）学生（学号，姓名，专业号）
4）论文（卷期页号，期刊名称，发表时间，项目号）
5）专利（专利号，专利名，申请号，申请时间，项目号）
6）软著（软著登记号，软著名称，登记时间，项目号）
7）项目（项目号，项目名，立项时间，结项时间，等级号）
8）项目类型（等级号，等级名，经费）
9）指导老师（工号，姓名）
10）项目学生位次（学号，项目号，学生位次）
11）项目指导老师（工号，项目号）
12）学生所得论文（学号，卷期页号，位次）
13）学生所得软著（学号，登记号，位次）
14）学生所得专利（学号，专利号，位次）
（3）根据关系规范理论进行数据库的逻辑设计，给出数据库表的设计，数据库表设计
表1 院系表（department）
| 字段名 | 中文含义 | 类型 | 约束 | 备注 |
| did | 院系号 | char(10) | 主键 | |
| dname | 院系名 | varchar(20) | Not null | |
| comment | 备注 | varchar(20) | null |
.
表2 专业表（major）
| 字段名 | 中文含义 | 类型 | 约束 | 备注 |
| mid | 专业号 | int | 主键 | |
| mname | 专业名 | varchar(20) | not null | |
| did | 院系号 | char(10) | 外键，参照department表的did | |
| comment | 备注 | varchar(20) | null |
表3 学生表（student）
| 字段名 | 中文含义 | 类型 | 约束 | 备注 |
| sid | 学号 | int | 主键 | |
| sname | 姓名 | varchar(20) | not null | |
| mid | 专业号 | int | 外键，参照major表里的mid | |
| comment | 备注 | varchar(20) | null |
表4 论文表（paper）
| 字段名 | 中文含义 | 类型 | 约束 | 备注 |
| pid | 卷期页号 | char(10) | 主键 | |
| pname | 期刊名称 | varchar(20) | null | |
| pdate | 发表时间 | date | null | |
| prid | 项目号 | char(10) | 外键，来自project表的prid | |
| comment | 备注 | varchar(20) | null |
表5 专利表（patent）
| 字段名 | 中文含义 | 类型 | 约束 | 备注 |
| ptid | 专利号 | char(10) | 主键 | |
| ptname | 专利名 | varchar(20) | null | |
| ptdate1 | 申请时间 | date | null | |
| ptno | 申请号 | char(10) | Not null | |
| prid | 项目号 | char(10) | 外键，来自project表的prid | |
| comment | 备注 | varchar(20) | null |
表6 软著表（swcopyright）
| 字段名 | 中文含义 | 类型 | 约束 | 备注 |
| swid | 登记号 | char(10) | 主键 | |
| swname | 登记名称 | varchar(20) | null | |
| swdate | 登记时间 | date | null | |
| prid | 项目号 | char(10) | 外键，来自project表的prid | |
| comment | 备注 | varchar(20) | null |
表7 项目表（project）
| 字段名 | 中文含义 | 类型 | 约束 | 备注 |
| prid | 项目号 | char(10) | 主键 | |
| prname | 项目名 | varchar(20) | null | |
| date1 | 立项时间 | date | null | |
| date2 | 结项时间 | date | date2>date1 | |
| tid | 等级号 | char(10) | 外键，来自type表里的tid | |
| comment | 备注 | varchar(20) | null |
表8 项目类型表（type）
| 字段名 | 中文含义 | 类型 | 约束 | 备注 |
| tid | 等级号 | char(10) | 主键 | |
| tname | 等级名 | varchar(20) | null | |
| money | 经费 | budget | not null | |
| comment | 备注 | varchar(20) | null |
表9 教师表（teacher）
| 字段名 | 中文含义 | 类型 | 约束 | 备注 |
| teaid | 工号 | int | 主键 | |
| name | 姓名 | varchar(20) | Not null | |
| comment | 备注 | varchar(20) | null |
表10 项目学生位次表（prorank）
| 字段名 | 中文含义 | 类型 | 约束 | 备注 |
| prid | 项目号 | char(10) | 外键，来自project表的prid | prid和 sid 联合作为 主键 |
| sid | 学号 | int | 外键，来自project表的sid | |
| rankpr | 项目位次 | int | not null | |
| comment | 备注 | varchar(20) | null |
表11 项目指导老师（project_teacher）
| 字段名 | 中文含义 | 类型 | 约束 | 备注 |
| prid | 项目号 | char(10) | 外键，来自project表的prid | prid和 teaid 联合作为 主键 |
| teaid | 工号 | int | 外键，来自teacher表的teaid | |
| comment | 备注 | varchar(20) | null |
表12 学生所得论文表（stupaper）
| 字段名 | 中文含义 | 类型 | 约束 | 备注 |
| sid | 学号 | int | 外键，来自project表的sid | sid和 pid 联合作为 主键 |
| pid | 卷期页号 | char(10) | 外键，来自paper表的pid | |
| rankpa | 位次 | int | not null | |
| prid | 项目号 | char(10) | 外键，来自project表的prid | |
| comment | 备注 | varchar(20) | null |
表13 学生所得软著表（stusoft）
| 字段名 | 中文含义 | 类型 | 约束 | 备注 |
| sid | 学号 | int | 外键，来自project表的sid | sid和 swid 联合作为 主键 |
| swid | 登记号 | char(10) | 外键，来自swcopyright表的swid | |
| ranksw | 位次 | int | not null | |
| prid | 项目号 | char(10) | 外键，来自project表的prid | |
| comment | 备注 | varchar(20) | null |
表14 学生所得专利表（stupatent）
| 字段名 | 中文含义 | 类型 | 约束 | 备注 |
| sid | 学号 | int | 外键，来自project表的sid | sid和 ptid 联合作为 主键 |
| ptid | 专利号 | char(10) | 外键，来自patent表的ptid | |
| rankpt | 位次 | int | not null | |
| prid | 项目号 | char(10) | 外键，来自project表的prid | |
| comment | 备注 | varchar(20) | null |
（4）对给定的逻辑数据模型选取一个最适合应用环境的物理结构，进行数据库的物理
设计，考虑不同的 DBMS 选型，进行设计方案对比，对物理结构进行评价，评价的重点是
时间和空间效率；
DBMS选型：MySQL。
物理结构设计方案：
1）使用InnoDB存储引擎，因为它支持事务和行级锁定，适合具有高并发读写需求的应用环境。
2）为每个表选择合适的索引，以提高查询性能。例如，在学生表上创建一个基于mid字段的B+树索引，以加快按专业查询学生的速度。
3）考虑使用分区表来提高查询性能和维护效率。根据实际需求，可以根据日期范围或其他条件对论文、专利等表进行分区。
物理结构评价：
1）时间效率：MySQL在处理大量数据时表现良好，并且支持并发操作。通过适当的索引和分区设计，可以进一步提高查询和写入性能。
2）空间效率：MySQL的存储引擎具有紧凑的数据存储格式，并且可以通过压缩等技术来减少磁盘空间占用。
（5）在 My SQL 数据库中创建数据库projectcontrol并使用 SQL 语句创建相应的数据库表；
由于存在外键约束，建表顺序依次如下：表1 院系表（department）、表2 专业表（major）、表3 学生表（student）、表8 项目类型表（type）、表7 项目表（project）、表4 论文表（paper）、表5 专利表（patent）、表6 软著表（swcopyright）、表9 教师表（teacher）、表10 项目学生位次表（prorank）、表11 项目指导老师、表12 学生所得论文表（stupaper）、表13 学生所得软著表（stusoft）、表14 学生所得专利表（stupatent）。
以下为建表的SQL语句。
-- 表1 院系表（department）CREATE TABLE department (did CHAR(10) PRIMARY KEY,dname VARCHAR(20) NOT NULL,comment VARCHAR(20));-- 表2 专业表（major）CREATE TABLE major (mid INT PRIMARY KEY,mname VARCHAR(20) NOT NULL,did CHAR(10),comment VARCHAR(20),FOREIGN KEY (did) REFERENCES department(did));-- 表3 学生表（student）CREATE TABLE student (sid INT PRIMARY KEY,sname VARCHAR(20) NOT NULL,mid INT,comment VARCHAR(20),FOREIGN KEY (mid) REFERENCES major(mid));-- 表8 项目类型表（type）CREATE TABLE type (tid CHAR(10) PRIMARY KEY,tname VARCHAR(20),money FLOAT NOT NULL,comment VARCHAR(20));-- 表7 项目表（project）CREATE TABLE project (prid CHAR(10) PRIMARY KEY,prname VARCHAR(20),date1 DATE,date2 DATE,tid CHAR(10),comment VARCHAR(20),CHECK (date2 > date1),FOREIGN KEY (tid) REFERENCES type(tid));-- 表4 论文表（paper）CREATE TABLE paper (pid CHAR(10) PRIMARY KEY,pname VARCHAR(20),pdate DATE,prid CHAR(10),comment VARCHAR(20),FOREIGN KEY (prid) REFERENCES project(prid));-- 表5 专利表（patent）CREATE TABLE patent (ptid CHAR(10) PRIMARY KEY,ptname VARCHAR(20),ptdate1 DATE,ptno CHAR(10) NOT NULL,prid CHAR(10),comment VARCHAR(20),FOREIGN KEY (prid) REFERENCES project(prid));-- 表6 软著表（swcopyright）CREATE TABLE swcopyright (swid CHAR(10) PRIMARY KEY,swname VARCHAR(20),swdate DATE,prid CHAR(10),comment VARCHAR(20),FOREIGN KEY (prid) REFERENCES project(prid));-- 表9 教师表（teacher）CREATE TABLE teacher (teaid INT PRIMARY KEY,name VARCHAR(20) NOT NULL,comment VARCHAR(20));-- 表10 项目学生位次表（prorank）CREATE TABLE prorank (prid CHAR(10),sid INT,rankpr INT NOT NULL,comment VARCHAR(20),PRIMARY KEY (prid, sid),FOREIGN KEY (prid) REFERENCES project(prid),FOREIGN KEY (sid) REFERENCES student(sid));-- 表11 项目指导老师CREATE TABLE project_teacher (prid CHAR(10),teaid INT,comment VARCHAR(20),PRIMARY KEY (prid, teaid),FOREIGN KEY (prid) REFERENCES project(prid),FOREIGN KEY (teaid) REFERENCES teacher(teaid));-- 表12 学生所得论文表（stupaper）CREATE TABLE stupaper (sid INT,pid CHAR(10),rankpa INT NOT NULL,prid CHAR(10),comment VARCHAR(20),PRIMARY KEY (sid, pid),FOREIGN KEY (sid) REFERENCES student(sid),FOREIGN KEY (pid) REFERENCES paper(pid),FOREIGN KEY (prid) REFERENCES project(prid));-- 表13 学生所得软著表（stusoft）CREATE TABLE stusoft (sid INT,swid CHAR(10),ranksw INT NOT NULL,prid CHAR(10),comment VARCHAR(20),PRIMARY KEY (sid, swid),FOREIGN KEY (sid) REFERENCES student(sid),FOREIGN KEY (swid) REFERENCES swcopyright(swid),FOREIGN KEY (prid) REFERENCES project(prid));-- 表14 学生所得专利表（stupatent）CREATE TABLE stupatent (sid INT,ptid CHAR(10),rankpt INT NOT NULL,prid CHAR(10),comment VARCHAR(20),PRIMARY KEY (sid, ptid),FOREIGN KEY (sid) REFERENCES student(sid),FOREIGN KEY (ptid) REFERENCES patent(ptid),FOREIGN KEY (prid) REFERENCES project(prid));（6）通过导入文件的方式在数据库表中输入若干条测试数据，也可以直接插入若干条
测试数据；
这里选择通过excel文件导入数据库表数据。
首先创建数据文件:
图2 本地构建excel数据表格
按照建表顺序，导入各个excel：
图3 导入数据及成功结果
（7）自行设计若干问题并编写 SQL 语句完成对所提问题的实现，SQL 语句要求至少
12 条，自定义功能要求，并对 SQL 语句运行，要求包括下面的语句：
create table（要求包含实体完整性、参照完整性和用户自定义完整性的定义）、create
index、 create view、 select 语句 (至少 5 条，要求把 from、where、group by、having、order by 等子句用上，并实现多表查询)、insert、 delete、update、grant、revoke 语句。对于 select 语句，需要给出相应的数据查询结果的截图。
（8）对自定义的复杂的查询要求或复杂报表生成要求，以及复杂的自定义完整性要求，
使用存储过程和触发器来实现，并进行实验验证，给出验证结果截图。
下面为自拟题目及SQL语句和结果展示：
1）参照project的表结构，创建项目表(project1)时定义实体完整性、参照完整性、用户自定义完整性。
CREATE TABLE project1 (prid CHAR(10),prname VARCHAR(20) NOT NULL, date1 DATE NOT NULL,date2 DATE NOT NULL,tid CHAR(10) NOT NULL,comment VARCHAR(20),CONSTRAINT pk_project PRIMARY KEY (prid),#实体完整性CONSTRAINT fk_project_tid FOREIGN KEY (tid) REFERENCES type(tid), #参照完整性CONSTRAINT check_dates CHECK (date2 > date1)#用户自定义完整性
);
2）对项目表（project）创建一个索引（按项目号降序），索引的名字是index_prid。
对学生表（student）创建一个索引（按学号降序），索引的名字是index_sid。
CREATE INDEX index_prid ON project(prid DESC);
CREATE INDEX index_sid ON student(sid DESC);
图4 建立索引成功
3）设大创项目参与学生位次为1（即表prorank里的rankpr为‘1’）的是该项目的负责人，负责人的专业所在学院即该项目所属学院，查询属于“人工智能与数据科学学院”的全部大创项目。
SELECT * FROM project p1
WHERE p1.prid IN(SELECT p2.prid FROM project p2,prorank,student,major,departmentWHERE p2.prid = prorank.pridAND prorank.sid = student.sidAND student.mid = major.midAND major.did = department.didAND prorank.rankpr = 1AND department.dname = '人工智能与数据科学学院');
图5 查询到的智能学院项目结果
4）统计各个年级2021-2023年内创新创业的参加人数，按学号（sid）前两位表示年级。
SELECT LEFT(student.sid, 2) AS grade, COUNT(DISTINCT student.sid) AS '大创参与人数'
FROM student
WHERE EXISTS (SELECT 1 FROM prorank, projectWHERE prorank.prid = project.pridAND YEAR(project.date1) BETWEEN 2021 AND 2023 #date1为立项时间AND prorank.sid = student.sid
)
GROUP BY grade;
图6 各个年级参与大创人数
5）查询2020-2023年内创新创业项目的参加学生人数大于4的学院，并按照参加人数降序排序。
SELECT d.dname AS '学院名称', COUNT(DISTINCT st.sid) AS '参加人数'
FROM department d, major m, student st, project p,prorank pr
WHERE d.did = m.didAND m.mid = st.midAND st.sid = pr.sidAND pr.prid = p.pridAND p.date1 BETWEEN'2020-01-01' AND '2023-12-31'
GROUP BY d.did, d.dname
HAVING COUNT(DISTINCT st.sid) > 4
ORDER BY COUNT(DISTINCT st.sid) DESC;
图7 2020-2023年内创新创业项目的参加学生人数大于4的学院
6)查询“物联网工程”专业每个学生的论文、软著、专利的分别获得个数。
SELECT s.sid, s.sname, (SELECT COUNT(sp.pid) FROM stupaper sp WHERE sp.sid = s.sid) AS paper_count,(SELECT COUNT(sw.swid) FROM stusoft sw WHERE sw.sid = s.sid) AS software_count,(SELECT COUNT(pt.ptid) FROM stupatent pt WHERE pt.sid = s.sid) AS patent_count
FROM student s
WHERE s.mid IN (SELECT mid FROM major WHERE mname = '物联网工程');
图8 “物联网工程”专业每个学生的成果明细
7）查询2021-2022年每个学院所有项目的经费总金额，按经费降序排序。（各个项目类型的经费在type表里的money给出，项目归属由项目位次第一的学生所在学院决定）
SELECT department.dname AS 学院名称, SUM(type.money) AS 经费总金额
FROM project, prorank, student, major, department, type
WHERE project.prid = prorank.prid #项目排名第一人即项目负责人，其所在院系即项目所属院系AND prorank.rankpr = '1' AND prorank.sid = student.sid AND student.mid = major.midAND major.did = department.did AND project.tid = type.tidAND project.date1 BETWEEN'2021-01-01' AND '2022-12-31'
GROUP BY department.did
ORDER BY SUM(type.money) DESC;
图9 2021-2022年每个学院所有项目的经费总金额
8）查询所有项目的论文、软著、专利产出个数,并给出总数，按项目号降序排序。
SELECT project.prid AS 项目号,project.prname AS 项目名称,(SELECT COUNT(DISTINCT pid) FROM paper WHERE paper.prid = project.prid) AS 论文产出个数,(SELECT COUNT(DISTINCT swid) FROM swcopyright WHERE swcopyright.prid = project.prid) AS 软著产出个数,(SELECT COUNT(DISTINCT ptid) FROM patent WHERE patent.prid = project.prid) AS 专利产出个数,(SELECT COUNT(DISTINCT pid) FROM paper WHERE paper.prid = project.prid) + (SELECT COUNT(DISTINCT swid) FROM swcopyright WHERE swcopyright.prid = project.prid)+ (SELECT COUNT(DISTINCT ptid) FROM patent WHERE patent.prid = project.prid) AS 产出总数
FROM project
#由于prid为字符型，使用CAST函数转为整型排序
ORDER BY CAST(project.prid AS UNSIGNED);
图10 所有项目的论文、软著、专利产出个数及总数
9）创建一个视图viewdepartment1，要求显示各个项目项目号、项目名称、所在学院、立项时间、论文产出个数、软著产出个数、专利产出个数、产出总数、指导老师姓名。
CREATE VIEW viewdepartment1 AS
SELECT project.prid AS 项目号,project.prname AS 项目名称,(SELECT department.dnameFROM prorank,student,major,departmentWHERE project.prid = prorank.pridAND prorank.sid = student.sidAND student.mid = major.midAND major.did = department.didAND prorank.rankpr = 1 #按项目第一名学生所在学院决定项目归属) AS 所属学院,project.date1 AS 立项时间,(SELECT COUNT(DISTINCT pid) FROM paper WHERE paper.prid = project.prid) AS 论文产出个数,(SELECT COUNT(DISTINCT swid) FROM swcopyright WHERE swcopyright.prid = project.prid) AS 软著产出个数,(SELECT COUNT(DISTINCT ptid) FROM patent WHERE patent.prid = project.prid) AS 专利产出个数,(SELECT COUNT(DISTINCT pid) FROM paper WHERE paper.prid = project.prid) + (SELECT COUNT(DISTINCT swid) FROM swcopyright WHERE swcopyright.prid = project.prid)+ (SELECT COUNT(DISTINCT ptid) FROM patent WHERE patent.prid = project.prid) AS 产出总数,(SELECT GROUP_CONCAT(teacher.`name` SEPARATOR ',')FROM teacher,project_teacher WHERE teacher.teaid = project_teacher.teaidAND project.prid = project_teacher.prid) AS 指导老师
FROM project
#由于prid为字符型，使用CAST函数转为整型排序
ORDER BY CAST(project.prid AS UNSIGNED);
本题解释：由于“指导老师”项在一个项目中可能存在多个匹配项，直接SELECT teacher.`name` 后提示创建视图失败。通过查阅资料，选择使用GROUP_CONCAT（）函数成功解决该问题，将多个老师姓名字段拼接为一个，以逗号分隔。
视图结果如下所示：
图11 视图viewdepartment1结果
10) 创建一个视图viewstudent1，要求显示2023年内“人工智能与数据科学学院”各个学生学号、学生姓名、所在专业、发表论文篇数、登记软著个数、拥有专利个数、获得总数。其中时间按各个论文、软著、专利的发表/登记/申请时间为准。
CREATE VIEW viewstudent1 AS
SELECT student.sid AS 学号,student.sname AS 姓名,major.mname AS 所在专业,
(
SELECT COUNT(stupaper.pid) FROM stupaper,paper
WHERE stupaper.sid = student.sidAND stupaper.pid = paper.pidAND paper.pdate BETWEEN '2023-01-01' AND '2023-12-31'
) AS 发表论文篇数,
(
SELECT COUNT(stusoft.swid) FROM stusoft,swcopyright
WHERE stusoft.sid = student.sidAND stusoft.swid = swcopyright.swidAND swcopyright.swdate BETWEEN '2023-01-01' AND '2023-12-31'
) AS 登记软著个数,
(
SELECT COUNT(stupatent.ptid) FROM stupatent,patent
WHERE stupatent.sid = student.sidAND stupatent.ptid = patent.ptidAND patent.ptdate1 BETWEEN '2023-01-01' AND '2023-12-31'
) AS 拥有专利个数,
(
SELECT COUNT(stupaper.pid) FROM stupaper,paper
WHERE stupaper.sid = student.sidAND stupaper.pid = paper.pidAND paper.pdate BETWEEN '2023-01-01' AND '2023-12-31') +(SELECT COUNT(stusoft.swid) FROM stusoft,swcopyrightWHERE stusoft.sid = student.sidAND stusoft.swid = swcopyright.swidAND swcopyright.swdate BETWEEN '2023-01-01' AND '2023-12-31')+(SELECT COUNT(stupatent.ptid) FROM stupatent,patentWHERE stupatent.sid = student.sidAND stupatent.ptid = patent.ptidAND patent.ptdate1 BETWEEN '2023-01-01' AND '2023-12-31') AS 获得总数
FROM student,major,department
WHERE student.mid = major.midAND major.did = department.didAND department.dname = '人工智能与数据科学学院'
ORDER BY (student.sid) DESC;
图12 视图viewstudent1结果
11) 在学生表（student）中插入一条记录，学号21XXXX，姓名XXX，专业为物联网工程。对插入的记录进行查看。
INSERT INTO student(sid,sname,mid) #插入记录
VALUES (21XXXX,'XXX',
(SELECT major.mid FROM major WHERE major.mname = '物联网工程'));
#查看结果
SELECT*FROM student WHERE student.sname ='XXX';
12）更新11题所插入的记录，将“XXX”记录的comment更新为“本实验作者”。对更新的这条记录进行查看。
#更新操作
UPDATE student
SET comment = '本实验作者'
WHERE student.sname = 'XXX';
#查看结果
SELECT*FROM student WHERE student.sname ='XXX';
13）删除学生表中“XXX”的记录。
#删除操作
DELETE FROM student
WHERE student.sname = 'XXX';
#查看结果
SELECT*FROM student WHERE student.sname ='XXX';
执行查询语句后发现该条记录已不存在，删除成功。
图15 删除后，查询不到该记录
14）创建“admin”用户，为其创建一个管理员角色，授予其查看所有表及修改项目
表(project)、学生表（student）老师表（teacher）的权限。
CREATE USER 'admin'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
CREATE role role_admin;
GRANT SELECT ON TABLE department TO role_admin;
GRANT SELECT ON TABLE major TO role_admin;
GRANT UPDATE,SELECT ON TABLE student TO role_admin;
GRANT SELECT ON TABLE type TO role_admin;
GRANT UPDATE,SELECT ON TABLE project TO role_admin;
GRANT SELECT ON TABLE paper TO role_admin;
GRANT SELECT ON TABLE patent TO role_admin;
GRANT SELECT ON TABLE swcopyright TO role_admin;
GRANT UPDATE,SELECT ON TABLE teacher TO role_admin;
GRANT SELECT ON TABLE prorank TO role_admin;
GRANT SELECT ON TABLE project_teacher TO role_admin;
GRANT SELECT ON TABLE stupaper TO role_admin;
GRANT SELECT ON TABLE stusoft TO role_admin;
GRANT SELECT ON TABLE stupatent TO role_admin;
GRANT role_admin TO 'admin'@'localhost';
#激活角色
SET DEFAULT role ALL TO 'admin'@'localhost';
15)验证admin的权限是否分配成功。以admin用户名登录数据库，修改project表里“基于深度学习的图像识别与分类算法研究”项目的立项时间（date1）为2021-09-19，结项时间（date2）为2023-09-29；尝试修改type表，观察有何输出。
首先，以admin用户名登录数据库。
图16 admin登录数据库
然后，先查询修改前的“基于深度学习的图像识别与分类算法研究”项目的信息，然后进行UPDATE操作，最后查看修改后的结果。
#查看更新前结果
SELECT * FROM project
WHERE project.prname = '基于深度学习的图像识别与分类算法研究';
#更新操作
UPDATE project
SET project.date1 = '2021-09-19',project.date2 = '2023-09-29'
WHERE project.prname = '基于深度学习的图像识别与分类算法研究';
#查看更新后的结果
SELECT * FROM project
WHERE project.prname = '基于深度学习的图像识别与分类算法研究';
操作前后结果对比如下，成功实现更新。
图17 操作前数据
图18 更新后数据
尝试以admin用户修改项目类型type表，修改国家级项目经费为50000：
UPDATE type
SET type.money = 50000
WHERE type.tname = '国家级';
由于没有给admin授予更新type表的权限，更新失败，满足第14题的权限分配。
图19 admin无更新type表权限
16）回收admin从角色获得的权限，删除角色，并对比回收前后admin的权限变化。
#查看回收前admin的权限
SHOW GRANTS FOR 'admin' @'localhost';
#回收admin从角色获得的权限
REVOKE role_admin FROM 'admin' @'localhost';
#删除角色
DROP role role_admin;
#查看回收后admin的权限
SHOW GRANTS FOR 'admin' @'localhost';
图20 回收权限前admin的权限
图21 回收权限后admin的权限
17）定义一个存储过程 proc1，计算所有学生的综合加分，并记录在学生表中comment内。这里只给论文、专利、软著排名为第一（排名在stupaper、stupatent、stusoft表中存在）的学生加分，并且论文加2分，专利加1分，软著加0.5分，执行这个存储过程。
DELIMITER $$
DROP PROCEDURE IF EXISTS proc1;
CREATE PROCEDURE proc1()
BEGINDECLARE sid_val INT;DECLARE done INT DEFAULT 0;DECLARE comment_str VARCHAR(20);DECLARE score FLOAT;-- 声明绑定结果的游标DECLARE student_cursor CURSOR FOR SELECT sid FROM student;-- 声明异常处理程序DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;OPEN student_cursor;read_loop: LOOPFETCH student_cursor INTO sid_val;IF done THENLEAVE read_loop;END IF;SET score = 0;-- 判断论文是否排名第一SET @paper_count := (SELECT COUNT(*) FROM stupaper WHERE sid = sid_val AND rankpa = 1);IF @paper_count > 0 THENSET score = score + 2;END IF;-- 判断专利是否排名第一SET @patent_count := (SELECT COUNT(*) FROM stupatent WHERE sid = sid_val AND rankpt = 1);IF @patent_count > 0 THENSET score = score + 1;END IF;-- 判断软著是否排名第一SET @soft_count := (SELECT COUNT(*) FROM stusoft WHERE sid = sid_val AND ranksw = 1);IF @soft_count > 0 THENSET score = score + 0.5;END IF;-- 将得分值转换为字符串类型，并更新学生表中的comment字段SET comment_str = CAST(score AS CHAR);UPDATE student SET comment = comment_str WHERE sid = sid_val;END LOOP;CLOSE student_cursor;
END$$
DELIMITER ;
CALL proc1();#执行存储过程
执行后student表结果如下图所示，comment成功更新为加分的分数。
图22 存储过程 proc1执行后student表结果
18）定义一个存储过程 proc2，计算2021-2023年所有学院的大创项目的结题成果数量（包括论文、专利、软著），将总数量大于15的学院的备注（comment）标注为“优秀大创组织单位”，执行这个存储过程。
DROP PROCEDURE IF EXISTS proc2;
DELIMITER //
CREATE PROCEDURE proc2()
BEGINDECLARE did_temp CHAR(10);DECLARE count_temp INT;DECLARE done INT DEFAULT 0;DECLARE cur1 CURSOR FORSELECT DISTINCT m.didFROM prorank pr, project p, student s, major mWHERE pr.rankpr = 1AND pr.sid = s.sidAND p.prid = pr.pridAND s.mid = m.midAND p.date2 BETWEEN '2021-01-01' AND '2023-12-31';DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;OPEN cur1;read_loop: LOOPFETCH cur1 INTO did_temp;IF done THENLEAVE read_loop;END IF;SELECT COUNT(*) INTO count_tempFROM paper pa, patent pt, swcopyright sw, project p, student s, prorank pr, major m, department dWHERE (pa.prid = p.prid OR pt.prid = p.prid OR sw.prid = p.prid)AND p.prid = pr.pridAND pr.sid = s.sidAND s.mid = m.midAND m.did = d.didAND p.date2 BETWEEN '2021-01-01' AND '2023-12-31'AND d.did = did_temp;IF count_temp > 15 THENUPDATE departmentSET comment = '优秀大创组织单位'WHERE did = did_temp;END IF;END LOOP;CLOSE cur1;
END//
DELIMITER ;
CALL proc2();
图23 存储过程proc2执行后department表结果
19）定义一个存储过程 proc3，计算2021-2023年所有大创项目的结题成果数量（包括论文、专利、软著），将产出总数量大于15的项目指导老师的备注（comment）标注为“优秀指导老师”，执行这个存储过程。
DROP PROCEDURE IF EXISTS proc3;
DELIMITER //
CREATE PROCEDURE proc3()
BEGINDECLARE prid_val CHAR(10);DECLARE teaid_val INT;DECLARE proj_count INT;DECLARE comment_val VARCHAR(20);DECLARE done BOOLEAN DEFAULT FALSE;DECLARE cur CURSOR FORSELECT prid, teaid FROM project_teacher WHERE prid IN (SELECT prid FROM project WHERE YEAR(date2) BETWEEN 2021 AND 2023);DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;OPEN cur;read_loop: LOOPFETCH cur INTO prid_val, teaid_val;IF done THENLEAVE read_loop;END IF;SET proj_count = (SELECT COUNT(*) FROM (SELECT pid FROM paper WHERE prid = prid_val AND YEAR(pdate) BETWEEN 2021 AND 2023UNION ALLSELECT ptid FROM patent WHERE prid = prid_val AND YEAR(ptdate1) BETWEEN 2021 AND 2023UNION ALLSELECT swid FROM swcopyright WHERE prid = prid_val AND YEAR(swdate) BETWEEN 2021 AND 2023) AS result_count);IF proj_count > 15 THENSET comment_val = '优秀指导老师';UPDATE teacher SET comment = comment_val WHERE teaid = teaid_val;END IF;END LOOP;CLOSE cur;
END //
DELIMITER ;
-- 执行存储过程
CALL proc3();
图24 存储过程proc3执行后teacher表结果
20）创建一个学生分数表（score），包括学号，分数，备注。（只给论文、专利、软著排名为第一（排名在stupaper、stupatent、stusoft表中存在）的学生加分，并且论文加2分，专利加1分，软著加0.5分）。建完表后上定义三个AFTER INSERT触发器，当插入论文、专利、软著记录时，学生的分数自动修改，保持数据一致性。
首先，建立score表：
CREATE TABLE score (sid INT,score DECIMAL ( 5, 2 ),COMMENT VARCHAR ( 20 ),PRIMARY KEY ( sid ),
FOREIGN KEY ( sid ) REFERENCES student ( sid )
);
然后，插入score表数据:
INSERT INTO score (sid, score)
SELECT student.sid,(SELECT COALESCE(SUM(CASE WHEN rankpa = 1 THEN 2 ELSE 0 END), 0) FROM stupaper WHERE stupaper.sid = student.sid) +(SELECT COALESCE(SUM(CASE WHEN rankpt = 1 THEN 1 ELSE 0 END), 0) FROM stupatent WHERE stupatent.sid = student.sid) +(SELECT COALESCE(SUM(CASE WHEN ranksw = 1 THEN 0.5 ELSE 0 END), 0) FROM stusoft WHERE stusoft.sid = student.sid) AS score
FROM student;
其中，COALESCE 函数用于处理子查询中的求和结果。如果求和结果为 NULL，COALESCE 函数将其替换为 0，以确保计算分数时不会出现 NULL 值。
定义触发器：
检验触发器功能：
#查询200001学生的分数
SELECT * FROM score WHERE score.sid =200001;
#插入一条记录
INSERT INTO paper(pid) VALUES('PA371');
INSERT INTO stupaper(sid,pid,rankpa) VALUES(200001,'PA371',1);
#显示结果
SELECT * FROM score WHERE score.sid =200001;
#插入一条记录
INSERT INTO patent(ptid,ptno) VALUES('PT892',12121);
INSERT INTO stupatent(sid,ptid,rankpt) VALUES(200001,'PT892',3);
#显示结果
SELECT * FROM score WHERE score.sid =200001;
#插入一条记录
INSERT INTO swcopyright(swid) VALUES('SW793');
INSERT INTO stusoft(sid,swid,ranksw) VALUES(200001,'SW793',2);
#显示结果
SELECT * FROM score WHERE score.sid =200001;
由下图可知，触发器实现了相应的功能，即只有插入论文/专利/软著位次为1时，才会自动修改分数。
图25 insert触发器功能验证
21）根据第20题，设计三个before delete 触发器，实现删除相应记录时，学生的分数也自动扣除。
CREATE TRIGGER update_score_pa_before
BEFORE DELETE ON stupaper
FOR EACH ROW
BEGINIF OLD.rankpa = 1 THENUPDATE scoreSET score = score - 2WHERE sid = OLD.sid;END IF;
END;
CREATE TRIGGER update_score_pt_before
BEFORE DELETE ON stupatent
FOR EACH ROW
BEGINIF OLD.rankpt = 1 THENUPDATE scoreSET score = score - 1WHERE sid = OLD.sid;END IF;
END;
CREATE TRIGGER update_score_sw_before
BEFORE DELETE ON stusoft
FOR EACH ROW
BEGINIF OLD.ranksw = 1 THENUPDATE scoreSET score = score - 0.5WHERE sid = OLD.sid;END IF;
END;验证触发器功能：#查询200001学生的分数
SELECT * FROM score WHERE score.sid =210033;
#删除stupaper
DELETE FROM stupaperWHERE stupaper.sid = 210033 AND stupaper.rankpa = 1;
#查询200001学生的分数
SELECT * FROM score WHERE score.sid =210033;
#删除stupatent
DELETE FROM stupatentWHERE stupatent.sid = 210033 AND stupatent.rankpt =1;
#查询200001学生的分数
SELECT * FROM score WHERE score.sid =210033;
DELETE FROM stusoftWHERE stusoft.sid = 210033 AND stusoft.ranksw =1;
#查询200001学生的分数
SELECT * FROM score WHERE score.sid =210033;
图26 delete触发器验证
由上图可知，将‘210033’的所有排名第一的成果都删除后，其分数自动归零，验证了触发器的功能。
4.实验心得
通过本次实验，我对数据库设计的流程和办法有了清晰的认识，并且在自己建表、写数据的过程中体会到了完整性约束的意义；在绘制E-R图时，我考虑方便查询，保留了数据冗余，在编程解决问题时验证了我这一想法的正确性，使得查询项目的产出成果更加方便；我更加熟悉了MySQL语句的编写，并且在自己解决问题时，查阅资料发现了GROUP_CONCAT、COALESCE（）等平时讲课没有用过的函数的便利性；我对复杂问题使用了存储过程和触发器，并且在存储过程中使用了游标，进一步加强了我对存储过程和触发器的认识。
另外，设计表格时缺少考虑学生的加分这一属性，所以我在后期处理时，用存储过程直接将加分放到学生表里comment中；在实现触发器时，新建了一个grade表，圆满完成了实验。