2024年江西省职业院校技能大赛高职组“数据库运行与管理“竞赛样题解析答案

旺仔Sec

于 2024-10-08 20:23:43 发布

阅读量813

点赞数 24

分类专栏：各类系统知识&竞赛任务书文章标签：数据库 oracle

本文链接：https://blog.csdn.net/qq_50377269/article/details/142768081

版权

各类系统知识&竞赛任务书专栏收录该内容

44 篇文章 15 订阅

订阅专栏

2024年江西省职业院校技能大赛高职组"数据库运行与管理"竞赛样题解析答案

文章目录

- 2024年江西省职业院校技能大赛高职组"数据库运行与管理"竞赛样题解析答案

一、赛项背景
本赛项内容基于数据库课程教学标准及人才培养目标，引领相关专业课程改革创新，促进高等职业院校信息类相关专业建设，培养技能型人才;产教融合、校企合作，通过赛项展示和提高教师的数据库教学科研能力,提升学生从事数据库相关岗位的适配性，提供数据库领域的高素质技术技能型人才。全面考察高职学生在数据库概念设计、逻辑设计、物理设计、数据查询与分析、数据库部署与维护、数据库备份与恢复等知识、技术技能以及职业素养能力；全面检验学生在数据库方面的工程实践能力和创新能力；以高水平赛事引领职业教育高质量发展，发挥树旗、导航、定标、催化作用。
二、竞赛内容
赛卷分模块A、模块B和模块C三个部分，每个模块均为独立模块，得分不传递。
在这里插入图片描述

三、成果物提交
“数据库运行与管理”赛项参赛选手须按照三个模块的任务要求完成对应的成果物并提交。模块A、C在竞赛平台中完成考核并提交，无需额外再提交成果物，模块B的成果物打包成压缩包提交到竞赛平台。
参赛选手在比赛结束前可以自行重新提交成果物，比赛结束后选手无法提交成果物。
四、竞赛注意事项
提交的成果物资源内容中，不能填写与选手相关的信息，如姓名和院校等。如出现上述标记，成绩按照零分处理。

模块A：数据库理论

一、模块考核点
本模块分值为15分。
本模块采用机考方式，试题为单选题、多选题、判断题等，主要考查从事本职业应掌握的基础知识、专业知识、课程思政等内容。包括但不限于：数据库基础理论、SQL语言与查询优化、数据库设计与建模、事务与并发控制、数据库索引与性能优化、数据库安全、数据库备份与恢复、分布式数据库与大数据处理、存储引擎、计算机网络与数据库的交互、服务器配置与数据库管理、相应的法律法规及课程思政等内容。

二、模块任务
1.（单选题）以下关于SQL语句优化的说法中错误的是（）。
A.尽可能地减少多表查询
B.只检索需要的属性列
C.尽量使用相关子查询
D.经常提交修改，尽早释放锁
答案：C
解析：
A 选项：多表查询会增加查询的复杂度和资源消耗，尽可能减少多表查询可以提高查询效率，所以该说法正确。
B 选项：只检索需要的属性列可以减少数据的传输量，从而提高查询性能，该说法正确。
C 选项：相关子查询会导致查询效率低下，应该尽量避免使用，而不是尽量使用，所以该说法错误。
D 选项：经常提交修改，尽早释放锁可以提高数据库的并发性能，该说法正确。

2.（多选题）下面哪些选项是关系型数据库的特性？（）。
A.数据以表格形式组织
B.支持非结构化数据存储
C.使用 SQL 进行数据查询
D.数据存储在文档中
答案：AC
解析：
A 选项：关系型数据库中数据以表格形式组织，这是关系型数据库的基本特征之一，所以该选项正确。
B 选项：关系型数据库主要处理结构化数据，而非结构化数据存储不是其特性，该选项错误。
C 选项：SQL 是关系型数据库进行数据查询、操作等的标准语言，该选项正确。
D 选项：数据存储在文档中是文档型数据库的特性，而非关系型数据库，该选项错误。
3.（判断题）在关系型数据库中，每个表都必须有一个主键。（）。
A.正确
B.错误
答案：A
解析：在关系型数据库中，主键用于唯一标识表中的每一行记录。虽然在某些数据库管理系统中允许表没有显式定义主键，但从关系型数据库的理论和数据完整性的角度看，每个表都应该有一个主键来确保数据的唯一性、完整性以及方便数据的操作和管理等。所以该说法正确。

模块B：数据库设计与运维

一、模块考核点
模块分值45分。
本模块主要考察选手在数据库设计和维护中的综合能力。选手需围绕给定的业务场景和需求描述，详细分析业务流程，识别关键实体和流程需求，理解数据流动和交互，设计合理的实体关系图（ER图）或数据流图，定义实体、属性及其相互关系，确保数据的完整性和规范化。在设计阶段，选手需合理规划字段类型、主键、外键约束，并构建数据库表结构，完成数据库的概念设计、逻辑设计和物理设计全过程。最后，选手需根据任务要求，完成MySQL、Redis等数据库的部署和运维，优化数据库性能，确保系统的高效运行和稳定性。
二、模块任务
任务一：数据库设计
某校图书馆为了提升效率，需要开发一个图书馆管理系统。请根据下述需求描述完成该系统的数据库设计。
【需求描述】
（1）记录书籍信息，包括书籍的名称、ISBN、ISSN、作者、出版日期、出版社和价格。
（2）记录书籍作者信息，一本书可以有多个作者，一个作者可以写多本书，每位作者有一个唯一的ID、姓名。
（3）记录图书馆会员信息：每位会员有一个唯一的ID、姓名和注册日期。
（4）记录会员借阅记录：每位会员可以借阅多本书，每本书可以被多个会员借阅，记录每本书的借阅情况，包括借阅书籍、借书日期、还书日期和会员ID
1.根据需求阶段收集的信息，设计一个概念模型（ER图）。需包含需求描述所涉及的实体，并定义他们之间的关系。
2.基于概念模型，设计一个逻辑模型，定义表结构和字段，请你使用 SQL DDL 语句来表示这些设计。包括：每个表的字段及其合适的数据类型、确定字段的数据存储规格（例如，字符串字段的长度）、有意义的字段名称、主键和外键约束、表与表之间的关系。
3.在逻辑模型的基础上，进行物理设计。请考虑为表设计索引，以优化查询性能，设计表的分区（如果适用）和存储方案，以提高性能和管理大数据量。

`任务一参考答案：`

概念模型（ER图）
- 实体：
  - 书籍（Book）：具有名称（Name）、ISBN、ISSN、出版日期（PublishDate）、出版社（Publisher）、价格（Price）等属性。
  - 作者（Author）：具有唯一ID（AuthorID）、姓名（AuthorName）等属性。
  - 会员（Member）：具有唯一ID（MemberID）、姓名（MemberName）、注册日期（RegisterDate）等属性。
  - 借阅记录（BorrowRecord）：具有借书日期（BorrowDate）、还书日期（ReturnDate）等属性。
- 关系：
  - 书籍与作者之间是多对多关系（一本书可以有多个作者，一个作者可以写多本书），通过一个关联表（Book_Author）来表示这种关系，关联表包含书籍ID（BookID）和作者ID（AuthorID）。
  - 会员与书籍之间是多对多关系（每位会员可以借阅多本书，每本书可以被多个会员借阅），通过借阅记录（BorrowRecord）表来表示这种关系，借阅记录表中包含会员ID（MemberID）和书籍ID（BookID），同时记录借书日期和还书日期。
逻辑模型（SQL DDL语句）
- 书籍表（Book）

CREATE TABLE Book (
    BookID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(255),
    ISBN VARCHAR(13),
    ISSN VARCHAR(9),
    PublishDate DATE,
    Publisher VARCHAR(255),
    Price DECIMAL(10, 2)
);

作者表（Author）

CREATE TABLE Author (
    AuthorID INT AUTO_INCREMENT PRIMARY KEY,
    AuthorName VARCHAR(255)
);

书籍 - 作者关联表（Book_Author）

CREATE TABLE Book_Author (
    BookID INT,
    AuthorID INT,
    FOREIGN KEY (BookID) REFERENCES Book(BookID),
    FOREIGN KEY (AuthorID) REFERENCES Author(AuthorID),
    PRIMARY KEY (BookID, AuthorID)
);

会员表（Member）

CREATE TABLE Member (
    MemberID INT AUTO_INCREMENT PRIMARY KEY,
    MemberName VARCHAR(255),
    RegisterDate DATE
);

借阅记录表（BorrowRecord）

CREATE TABLE BorrowRecord (
    BorrowRecordID INT AUTO_INCREMENT PRIMARY KEY,
    BookID INT,
    MemberID INT,
    BorrowDate DATE,
    ReturnDate DATE,
    FOREIGN KEY (BookID) REFERENCES Book(BookID),
    FOREIGN KEY (MemberID) REFERENCES Member(MemberID)
);

物理设计
- 索引设计
  - 在书籍表（Book）中，对ISBN字段建立索引，因为ISBN是书籍的唯一标识符，常用于查询特定书籍。

CREATE INDEX idx_ISBN ON Book(ISBN);

在会员表（Member）中，对MemberName字段建立索引，方便根据会员姓名查询会员信息。

CREATE INDEX idx_MemberName ON Member(MemberName);

在借阅记录表（BorrowRecord）中，对BookID和MemberID字段建立索引，因为这两个字段经常用于查询借阅关系。

CREATE INDEX idx_BookID_MemberID ON BorrowRecord(BookID, MemberID);

分区和存储方案（假设数据量较大）
- 对于书籍表（Book），如果按照出版日期进行查询较为频繁，可以根据出版日期（PublishDate）进行范围分区。例如，可以按照年份进行分区，将不同年份出版的书籍存储在不同的分区中。

-- 假设使用MySQL的分区功能
ALTER TABLE Book
PARTITION BY RANGE (YEAR(PublishDate)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN MAXVALUE
);

对于借阅记录表（BorrowRecord），如果按照借书日期进行查询较为频繁，可以根据借书日期（BorrowDate）进行范围分区。

ALTER TABLE BorrowRecord
PARTITION BY RANGE (YEAR(BorrowDate)) (
    PARTITION pb2020 VALUES LESS THAN (2021),
    PARTITION pb2021 VALUES LESS THAN (2022),
    PARTITION pb2022 VALUES LESS THAN (2023),
    PARTITION pb2023 VALUES LESS THAN (2024),
    PARTITION pb2024 VALUES LESS THAN MAXVALUE
);

在存储方面，可以选择合适的存储引擎。例如，InnoDB在事务处理和数据完整性方面表现较好，适合这种需要管理多表关系的图书馆管理系统。同时，可以根据服务器的硬件资源配置，如磁盘类型（SSD或HDD）、内存大小等，合理分配表空间和缓存设置，以提高数据库的整体性能。

任务二：数据库运维
你将负责在Ubuntu服务器上安装和配置MySQL、Redis数据库。你需要确保数据库能够高效稳定、安全地运行。
1.安装MySQL8.0，确保MySQL服务在系统启动时自动启动。
2.设置Mysql用户Root用户密码为“jiangxi”。
3.配置MySQL的字符集设置为utf8mb4、排序规则设置为utf8mb4_unicode_ci。
4.设置MySQL的最大连接数为200。
5.设置MySQL的InnoDB缓冲池大小为512M。
6.启用MySQL慢查询日志，设置阈值为2秒。
7.修改Redis的监听端口为8379。
8.启用Redis的守护进程。
9.指定Redis客户端闲置时间为100后关闭连接。
指定Redis数据存放目录路径为/var/lib/redis。

`任务二参考答案：`

在Ubuntu服务器上安装和配置MySQL 8.0
- 安装MySQL 8.0
  - 首先更新系统软件包列表：

sudo apt update

 - 然后安装MySQL 8.0服务器：

sudo apt install mysql - server - 8.0

设置MySQL服务在系统启动时自动启动
- 使用以下命令：

sudo systemctl enable mysql

设置Root用户密码为“jiangxi”
- 运行以下命令进入MySQL安全设置模式：

sudo mysql_secure_installation

 - 在交互过程中，按照提示设置密码为“jiangxi”。

配置MySQL的字符集和排序规则
- 编辑MySQL配置文件/etc/mysql/mysql.conf.d/mysqld.cnf：

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

 - 在`[mysqld]`节下添加以下内容：

character - set - server = utf8mb4
collation - server = utf8mb4_unicode_ci

 - 保存并退出文件后，重启MySQL服务：

sudo systemctl restart mysql

设置MySQL的最大连接数为200
- 编辑/etc/mysql/mysql.conf.d/mysqld.cnf文件，在[mysqld]节下添加：

max_connections = 200

 - 重启MySQL服务：

sudo systemctl restart mysql

设置MySQL的InnoDB缓冲池大小为512M
- 同样在/etc/mysql/mysql.conf.d/mysqld.cnf文件的[mysqld]节下添加：

innodb_buffer_pool_size = 512M

 - 重启MySQL服务：

sudo systemctl restart mysql

启用MySQL慢查询日志，设置阈值为2秒
- 在/etc/mysql/mysql.conf.d/mysqld.cnf文件的[mysqld]节下添加：

slow_query_log = 1
long_query_time = 2

 - 重启MySQL服务：

sudo systemctl restart mysql

在Ubuntu服务器上安装和配置Redis
- 安装Redis
  - 更新软件包列表：

sudo apt update

 - 安装Redis：

sudo apt install redis - server

修改Redis的监听端口为8379
- 编辑Redis配置文件/etc/redis/redis.conf：

sudo nano /etc/redis/redis.conf

 - 将`port 6379`修改为`port 8379`。
 - 保存并退出文件后，重启Redis服务：

sudo systemctl restart redis - server

启用Redis的守护进程
- 在/etc/redis/redis.conf文件中，将daemonize no修改为daemonize yes。
- 重启Redis服务：

sudo systemctl restart redis - server

指定Redis客户端闲置时间为100后关闭连接
- 在/etc/redis/redis.conf文件中，找到timeout设置项，将其值设置为100。
- 重启Redis服务：

sudo systemctl restart redis - server

指定Redis数据存放目录路径为/var/lib/redis
- 在/etc/redis/redis.conf文件中，找到dir设置项，将其值修改为/var/lib/redis。
- 重启Redis服务：

sudo systemctl restart redis - server

模块C：数据库查询与分析

一、模块考核点
模块分值40分。
本模块主要考察选手根据给定的任务场景、数据库表结构及字段说明，结合实际业务需求，编写对应的SQL查询语句。要求选手在准确理解任务要求的基础上，合理运用SQL语言中的查询、条件过滤、聚合函数、连接（JOIN）操作等功能，完成数据查询和结果集的生成。
二、模块任务
有一个员工employees表简况如下:

emp_no	birth_date	first_name	gender	hire_date
10001	1953-09-02	张三	M	1986-06-26
10002	1964-06-02	李四	M	1985-11-21
10003	1959-12-03	张伟	M	1986-08-28
10004	1954-05-01	王红	F	1986-12-01

1.请你编写SQL语句查找employees表里最晚入职员工的所有信息，以上例子输出如下:

10004	1954-05-01	王红	F	1986-12-01

2.请你编写SQL语句查找employees表里入职员工时间排名倒数第三的员工所有信息，以上例子输出如下:

10001	1953-09-02	张三	M	1986-06-26

`模块C参考答案：`

查找最晚入职员工的所有信息
- 在SQL中，可以使用ORDER BY对入职日期进行降序排序，然后使用LIMIT获取第一条记录（即最晚入职的员工）。假设使用MySQL数据库，查询语句如下：

SELECT *
FROM employees
ORDER BY hire_date DESC
LIMIT 1;

解释：
- ORDER BY hire_date DESC：按照入职日期（hire_date）进行降序排列，这样最晚入职的员工记录会排在最前面。
- LIMIT 1：只获取结果集中的第一条记录，也就是最晚入职的员工的所有信息。

查找入职员工时间排名倒数第三的员工所有信息
- 首先，我们可以使用子查询来计算每个员工的入职排名。然后在外部查询中选择排名为倒数第三的员工信息。假设使用MySQL数据库，查询语句如下：

SELECT *
FROM (
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY hire_date DESC) AS rank
    FROM employees
) AS subquery
WHERE rank = (
    SELECT COUNT(*) - 2
    FROM employees
);

解释：
- 在子查询中：
  - ROW_NUMBER() OVER (ORDER BY hire_date DESC) AS rank：使用窗口函数ROW_NUMBER()为每个员工按照入职日期降序分配一个排名，这个排名被命名为rank。
- 在外部查询中：
  - WHERE rank=(SELECT COUNT(*) - 2 FROM employees)：这里的COUNT(*)是计算employees表中的总行数，COUNT(*) - 2就是倒数第三的排名。通过这个条件筛选出排名为倒数第三的员工的所有信息。