2025数据库系统工程师考试数据库系统性能优化与瓶颈分析实战试题

2025数据库系统工程师考试数据库系统性能优化与瓶颈分析实战试题考试时间：______分钟总分：______分姓名：______一、关系数据库设计要求：请根据以下关系模式，完成以下设计任务。关系模式：1.学生（学号，姓名，性别，年龄，班级号）2.课程（课程号，课程名，学分，教师号）3.成绩（学号，课程号，成绩）4.教师（教师号，姓名，职称，性别，年龄）1.1请为上述关系模式定义主键和外键。1.2请根据需求，设计一个查询语句，查询所有年龄大于20岁的女学生的姓名和班级号。1.3请根据需求，设计一个查询语句，查询所有课程名为“数据库系统”的课程的学分和教师姓名。1.4请根据需求，设计一个查询语句，查询所有学生的姓名和成绩，成绩高于90分的学生姓名后加“（优秀）”，低于60分的学生姓名后加“（不及格）”。1.5请根据需求，设计一个查询语句，查询所有未开设课程的教师姓名。1.6请根据需求，设计一个查询语句，查询所有男教师的姓名和年龄。1.7请根据需求，设计一个查询语句，查询所有学生的姓名和所在班级的平均成绩。1.8请根据需求，设计一个查询语句，查询所有未修满20学分的学生姓名。1.9请根据需求，设计一个查询语句，查询所有教师的姓名和职称。1.10请根据需求，设计一个查询语句，查询所有学生的姓名和所在班级的班级名。二、SQL语句编写要求：请根据以下需求，编写相应的SQL语句。2.1请删除所有年龄小于18岁的学生记录。2.2请将所有学生的年龄增加1岁。2.3请将所有教师的职称修改为“副教授”。2.4请将所有课程的学分修改为原来的2倍。2.5请将所有学生的成绩修改为原来的1.5倍。2.6请将所有学生的班级号修改为所在班级的学生数量。2.7请将所有教师的年龄修改为原来的0.9倍。2.8请将所有课程的教师号修改为1。2.9请将所有学生的性别修改为“女”。2.10请将所有课程的学分修改为原来的0.5倍。三、数据库查询优化要求：请根据以下需求，对查询语句进行优化。3.1优化以下查询语句，使其执行效率更高：SELECT*FROM学生WHERE年龄>20AND性别='女';3.2优化以下查询语句，使其执行效率更高：SELECT学号,姓名FROM学生WHERE班级号=(SELECT班级号FROM学生GROUPBY班级号HAVINGCOUNT(*)=1);3.3优化以下查询语句，使其执行效率更高：SELECT课程号,课程名FROM课程WHERE学分>(SELECTAVG(学分)FROM课程);3.4优化以下查询语句，使其执行效率更高：SELECT学生.姓名,班级.班级名FROM学生,班级WHERE学生.班级号=班级.班级号;3.5优化以下查询语句，使其执行效率更高：SELECT教师号,姓名FROM教师WHERE职称='副教授'AND年龄>(SELECTAVG(年龄)FROM教师WHERE职称='副教授');3.6优化以下查询语句，使其执行效率更高：SELECT学号,姓名FROM学生WHERE年龄>(SELECTAVG(年龄)FROM学生WHERE性别='男');3.7优化以下查询语句，使其执行效率更高：SELECT课程号,课程名FROM课程WHERE学分>(SELECTMAX(学分)FROM课程);3.8优化以下查询语句，使其执行效率更高：SELECT学生.姓名,班级.班级名FROM学生,班级WHERE学生.班级号=班级.班级号AND班级.班级名='计算机科学与技术';3.9优化以下查询语句，使其执行效率更高：SELECT教师号,姓名FROM教师WHERE职称='副教授'AND年龄>(SELECTMIN(年龄)FROM教师WHERE职称='副教授');3.10优化以下查询语句，使其执行效率更高：SELECT学生.姓名,班级.班级名FROM学生,班级WHERE学生.班级号=班级.班级号AND班级.班级名LIKE'%计算机%';四、数据库索引设计要求：请根据以下需求，设计相应的数据库索引。4.1为“学生”关系中的“学号”字段创建唯一索引。4.2为“课程”关系中的“课程号”字段创建唯一索引。4.3为“成绩”关系中的“学号”和“课程号”字段创建复合索引。4.4为“教师”关系中的“教师号”字段创建唯一索引。4.5为“班级”关系中的“班级号”字段创建唯一索引。4.6为“成绩”关系中的“成绩”字段创建非唯一索引，以便快速查询特定成绩的学生。4.7为“学生”关系中的“班级号”字段创建非唯一索引，以便快速查询特定班级的学生。4.8为“教师”关系中的“职称”字段创建非唯一索引，以便快速查询特定职称的教师。4.9为“课程”关系中的“学分”字段创建非唯一索引，以便快速查询特定学分的课程。4.10为“班级”关系中的“班级名”字段创建非唯一索引，以便快速查询特定名称的班级。五、数据库视图设计要求：请根据以下需求，设计相应的数据库视图。5.1设计一个视图，展示所有学生的姓名、班级号和所在班级的班级名。5.2设计一个视图，展示所有教师的姓名、职称和所在学院的学院名。5.3设计一个视图，展示所有课程的信息，包括课程号、课程名、学分和授课教师姓名。5.4设计一个视图，展示所有学生的姓名和成绩，包括课程名和成绩等级。5.5设计一个视图，展示所有教师的姓名和授课课程，包括课程名和学分。5.6设计一个视图，展示所有班级的班级号、班级名和班级人数。5.7设计一个视图，展示所有学生的姓名和所在班级的平均成绩。5.8设计一个视图，展示所有课程的学分和授课教师姓名，包括教师的职称。5.9设计一个视图，展示所有学生的姓名和所在班级的班级名，包括班级的成立时间。5.10设计一个视图，展示所有教师的姓名和授课班级，包括班级的成立时间。六、数据库触发器设计要求：请根据以下需求，设计相应的数据库触发器。6.1设计一个触发器，在向“学生”关系插入新记录时，自动检查年龄是否大于18岁，如果不满足条件，则不允许插入。6.2设计一个触发器，在向“课程”关系插入新记录时，自动检查学分是否大于0，如果不满足条件，则不允许插入。6.3设计一个触发器，在向“成绩”关系插入新记录时，自动检查成绩是否在0到100之间，如果不满足条件，则不允许插入。6.4设计一个触发器，在删除“教师”关系中的记录时，自动检查是否有学生记录依赖于该教师，如果有，则不允许删除。6.5设计一个触发器，在更新“班级”关系中的记录时，自动检查班级号是否已存在，如果存在，则不允许更新。6.6设计一个触发器，在删除“课程”关系中的记录时，自动检查是否有成绩记录依赖于该课程，如果有，则不允许删除。6.7设计一个触发器，在向“学生”关系插入新记录时，自动将班级号设置为默认值。6.8设计一个触发器，在更新“教师”关系中的记录时，自动检查年龄是否小于60岁，如果不满足条件，则不允许更新。6.9设计一个触发器，在删除“班级”关系中的记录时，自动删除所有依赖于该班级的学生记录。6.10设计一个触发器，在向“成绩”关系插入新记录时，自动计算成绩等级（优秀、良好、及格、不及格）。本次试卷答案如下：一、关系数据库设计1.1主键：学生（学号），外键：班级号（学生）参照班级（班级号）。1.2查询语句：SELECT姓名,班级号FROM学生WHERE年龄>20AND性别='女';1.3查询语句：SELECT学分,姓名FROM课程WHERE课程名='数据库系统';1.4查询语句：SELECT学号,姓名,CASE成绩WHEN成绩>90THENCONCAT(姓名,'（优秀）')WHEN成绩<60THENCONCAT(姓名,'（不及格）')ELSE姓名ENDAS成绩等级FROM成绩;1.5查询语句：SELECT姓名FROM教师WHERE教师号NOTIN(SELECT教师号FROM课程);1.6查询语句：SELECT姓名,年龄FROM教师WHERE性别='男';1.7查询语句：SELECT学生.姓名,班级.班级名,AVG(成绩)AS平均成绩FROM学生,成绩,班级WHERE学生.班级号=班级.班级号AND学生.学号=成绩.学号GROUPBY学生.姓名,班级.班级名;1.8查询语句：SELECT姓名FROM学生WHERE(SELECTSUM(学分)FROM课程WHERE课程号IN(SELECT课程号FROM成绩WHERE学号=学生.学号))<20;1.9查询语句：SELECT姓名,职称FROM教师;1.10查询语句：SELECT学生.姓名,班级.班级名FROM学生,班级WHERE学生.班级号=班级.班级号;二、SQL语句编写2.1DELETEFROM学生WHERE年龄<18;2.2UPDATE学生SET年龄=年龄+1;2.3UPDATE教师SET职称='副教授';2.4UPDATE课程SET学分=学分*2;2.5UPDATE成绩SET成绩=成绩*1.5;2.6UPDATE学生SET班级号=(SELECT班级号FROM学生WHERE学号=学生.学号GROUPBY班级号HAVINGCOUNT(*)=1);2.7UPDATE教师SET年龄=年龄*0.9;2.8UPDATE课程SET教师号=1;2.9UPDATE学生SET性别='女';2.10UPDATE课程SET学分=学分*0.5;三、数据库查询优化3.1优化后的查询语句：SELECT*FROM学生WHERE年龄>20AND性别='女'INDEX(idx_student_age_gender);3.2优化后的查询语句：SELECT学号,姓名FROM学生WHERE班级号=(SELECT班级号FROM学生GROUPBY班级号HAVINGCOUNT(*)=1)INDEX(idx_student_class);3.3优化后的查询语句：SELECT课程号,课程名FROM课程WHERE学分>(SELECTAVG(学分)FROM课程)INDEX(idx_course_credit);3.4优化后的查询语句：SELECT学生.姓名,班级.班级名FROM学生,班级WHERE学生.班级号=班级.班级号INDEX(idx_student_class,idx_class_name);3.5优化后的查询语句：SELECT教师号,姓名FROM教师WHERE职称='副教授'AND年龄>(SELECTAVG(年龄)FROM教师WHERE职称='副教授')INDEX(idx_teacher_title_age);3.6优化后的查询语句：SELECT学号,姓名FROM学生WHERE年龄>(SELECTAVG(年龄)FROM学生WHERE性别='男')INDEX(idx_student_age);3.7优化后的查询语句：SELECT课程号,课程名FROM课程WHERE学分>(SELECTMAX(学分)FROM课程)INDEX(idx_course_max_credit);3.8优化后的查询语句：SELECT学生.姓名,班级.班级名FROM学生,班级WHERE学生.班级号=班级.班级号AND班级.班级名='计算机科学与技术'INDEX(idx_student_class,idx_class_name);3.9优化后的查询语句：SELECT教师号,姓名FROM教师WHERE职称='副教授'AND年龄>(SELECTMIN(年龄)FROM教师WHERE职称='副教授')INDEX(idx_teacher_title_age);3.10优化后的查询语句：SELECT学生.姓名,班级.班级名FROM学生,班级WHERE学生.班级号=班级.班级号AND班级.班级名LIKE'%计算机%'INDEX(idx_student_class,idx_class_name);四、数据库索引设计4.1创建唯一索引：CREATEUNIQUEINDEXidx_student_idON学生(学号);4.2创建唯一索引：CREATEUNIQUEINDEXidx_course_idON课程(课程号);4.3创建复合索引：CREATEINDEXidx_score_student_courseON成绩(学号,课程号);4.4创建唯一索引：CREATEUNIQUEINDEXidx_teacher_idON教师(教师号);4.5创建唯一索引：CREATEUNIQUEINDEXidx_class_idON班级(班级号);4.6创建非唯一索引：CREATEINDEXidx_score_gradeON成绩(成绩);4.7创建非唯一索引：CREATEINDEXidx_student_classON学生(班级号);4.8创建非唯一索引：CREATEINDEXidx_teacher_titleON教师(职称);4.9创建非唯一索引：CREATEINDEXidx_course_creditON课程(学分);4.10创建非唯一索引：CREATEINDEXidx_class_nameON班级(班级名);五、数据库视图设计5.1视图设计：CREATEVIEWv_student_classASSELECT学生.姓名,学生.班级号,班级.班级名FROM学生,班级WHERE学生.班级号=班级.班级号;5.2视图设计：CREATEVIEWv_teacher_collegeASSELECT教师.姓名,教师.职称,学院.学院名FROM教师,学院WHERE教师.学院号=学院.学院号;5.3视图设计：CREATEVIEWv_course_teacherASSELECT课程.课程号,课程.课程名,课程.学分,教师.姓名FROM课程,教师WHERE课程.教师号=教师.教师号;5.4视图设计：CREATEVIEWv_student_scoreASSELECT学生.姓名,课程.课程名,成绩.成绩,CASE成绩WHEN成绩>90THEN'优秀'WHEN成绩>=60THEN'及格'ELSE'不及格'ENDAS成绩等级FROM学生,课程,成绩WHERE学生.学号=成绩.学号AND课程.课程号=成绩.课程号;5.5视图设计：CREATEVIEWv_teacher_courseASSELECT教师.姓名,教师.职称,课程.课程名,课程.学分FROM教师,课程WHERE教师.教师号=课程.教师号;5.6视图设计：CREATEVIEWv_class_infoASSELECT班级.班级号,班级.班级名,COUNT(学生.学号)AS学生数量FROM班级,学生WHERE班级.班级号=学生.班级号GROUPBY班级.班级号;5.7视图设计：CREATEVIEWv_student_class_avg_scoreASSELECT学生.姓名,班级.班级名,AVG(成绩)AS平均成绩FROM学生,成绩,班级WHERE学生.学号=成绩.学号AND学生.班级号=班级.班级号GROUPBY学生.姓名,班级.班级名;5.8视图设计：CREATEVIEWv_course_credit_teacherASSELECT课程.课程号,课程.课程名,课程.学分,教师.姓名,教师.职称FROM课程,教师WHERE课程.教师号=教师.教师号;5.9视图设计：CREATEVIEWv_student_class_creationASSELECT学生.姓名,班级.班级名,班级.成立时间FROM学生,班级WHERE学生.班级号=班级.班级号;5.10视图设计：CREATEVIEWv_teacher_class_creationASSELECT教师.姓名,教师.职称,班级.班级名,班级.成立时间FROM教师,班级WHERE教师.教师号=班级.教师号;六、数据库触发器设计6.1触发器设计：CREATETRIGGERtrig_student_age_before_insertBEFOREINSERTON学生FOREACHROWBEGINIFNEW.年龄<18THENSIGNALSQLSTATE'45000'SETMESSAGE_TEXT='年龄必须大于18岁';ENDIF;END;6.2触发器设计：CREATETRIGGERtrig_course_credit_before_insertBEFOREINSERTON课程FOREACHROWBEGINIFNEW.学分<=0THENSIGNALSQLSTATE'45000'SETMESSAGE_TEXT='学分必须大于0';ENDIF;END;6.3触发器设计：CREATETRIGGERtrig_score_before_insertBEFOREINSERTON成绩FOREACHROWBEGINIFNEW.成绩<0ORNEW.成绩>100THENSIGNALSQLSTATE'45000'SETMESSAGE_TEXT='成绩必须在0到100之间';ENDIF;END;6.4触发器设计：CREATETRIGGERtrig_teacher_before_deleteBEFOREDELETEON教师FOREACHROWBEGINIFEXISTS(SELECT1FROM学生WHERE学生.教师号=OLD.教师号)THENSIGNALSQLSTATE'45000'SETMESSAGE_TEXT='存在学生记录依赖于该教师，不允许删除';ENDIF;END;6.5触发器设计：CREATETRIGGERtrig_class_before_updateBEFOREUPDATEON班级FOREACHROWBEGINIFEXISTS(SELECT1FROM学生WHERE学生.班级号=OLD.班级号)THENSIGNALSQLSTATE'45000'SETMESSAGE_TEXT='班级号已存在，不允许更新';ENDIF;END;6.6触发器设