2025年大数据分析师职业技能测试卷：数据仓库设计与实施试题.docx

2025年大数据分析师职业技能测试卷：数据仓库设计与实施试题

考试时间：______分钟总分：______分姓名：______

一、关系型数据库设计

要求：请根据以下业务场景，设计一个关系型数据库模型，并说明每个表的结构和字段。

1.业务场景：某在线图书销售平台，包含用户、图书、订单、评论等实体。

（1）用户表（User）

字段：

-UserID（用户ID，主键，自增）

-Username（用户名，唯一）

-Password（密码）

-Email（邮箱）

-PhoneNumber（手机号）

-RegisterDate（注册日期）

（2）图书表（Book）

字段：

-BookID（图书ID，主键，自增）

-BookName（书名）

-Author（作者）

-CategoryID（分类ID，外键）

-Price（价格）

-Stock（库存）

-PublishDate（出版日期）

（3）分类表（Category）

字段：

-CategoryID（分类ID，主键，自增）

-CategoryName（分类名称）

（4）订单表（Order）

字段：

-OrderID（订单ID，主键，自增）

-UserID（用户ID，外键）

-OrderDate（订单日期）

-TotalAmount（订单总额）

（5）评论表（Comment）

字段：

-CommentID（评论ID，主键，自增）

-UserID（用户ID，外键）

-BookID（图书ID，外键）

-CommentContent（评论内容）

-CommentDate（评论日期）

2.请根据上述表结构，编写SQL语句实现以下功能：

（1）查询所有图书的平均价格。

（2）查询所有图书的分类名称和对应图书数量。

（3）查询订单ID为1001的订单详情。

（4）查询用户名为Tom的用户所购买的图书名称和作者。

二、数据仓库设计

要求：请根据以下业务场景，设计一个数据仓库模型，并说明每个表的结构和字段。

1.业务场景：某在线教育平台，包含课程、教师、学生、成绩等实体。

（1）课程表（Course）

字段：

-CourseID（课程ID，主键，自增）

-CourseName（课程名称）

-TeacherID（教师ID，外键）

-CourseDescription（课程描述）

-CourseDuration（课程时长）

（2）教师表（Teacher）

字段：

-TeacherID（教师ID，主键，自增）

-TeacherName（教师姓名）

-TeacherTitle（教师职称）

-DepartmentID（部门ID，外键）

（3）学生表（Student）

字段：

-StudentID（学生ID，主键，自增）

-StudentName（学生姓名）

-ClassID（班级ID，外键）

-EnrollmentDate（入学日期）

（4）班级表（Class）

字段：

-ClassID（班级ID，主键，自增）

-ClassName（班级名称）

-DepartmentID（部门ID，外键）

（5）成绩表（Grade）

字段：

-GradeID（成绩ID，主键，自增）

-StudentID（学生ID，外键）

-CourseID（课程ID，外键）

-Score（分数）

2.请根据上述表结构，编写SQL语句实现以下功能：

（1）查询所有课程的平均时长。

（2）查询所有教师所在的部门名称和教师数量。

（3）查询所有学生的班级名称和入学日期。

（4）查询某学生（假设StudentID为1001）在某课程（假设CourseID为1001）的分数。

四、数据仓库ETL过程设计

要求：请设计一个数据仓库ETL过程，包括数据抽取、转换和加载（ETL）的步骤，并说明每个步骤的具体操作。

1.数据抽取

（1）从源系统（如数据库、日志文件等）中抽取用户表、图书表、订单表、评论表的数据。

（2）从源系统抽取课程表、教师表、学生表、成绩表的数据。

2.数据转换

（1）清洗数据，去除重复、错误或不完整的数据。

（2）转换数据格式，如日期格式、货币格式等。

（3）计算数据，如计算订单总额、图书分类数量等。

（4）数据合并，将相关数据表合并为一个数据集。

3.数据加载

（1）将清洗和转换后的数据加载到数据仓库的对应表中。

（2）根据业务需求，创建数据仓库的汇总表和维度表。

（3）将数据加载到汇总表和维度表中。

五、数据仓库查询优化

要求：请针对以下查询场景，提出数据仓库查询优化的策略。

1.查询场景：查询所有图书的平均价格，并按分类名称排序。

优化策略：

（1）在图书表上创建价格索引。

（2）在分类表上创建分类名称索引。

（3）使