MySQL数据库基础练习系列37、问卷调查统计分析系统

2024-06-16 12:24:21 浏览数 (1)

MySQL数据库基础练习系列目标

很多学生或者说是初学者在学习完成数据库的基础增删改查后就自认为在数据库这里就很熟悉了，但是不接触项目根本部知道需求，我这里准备了50个项目的基本需求来让大家来熟练各类项目的列信息，让大家更好的深入项目进行实战式的练习，可以让大家在后面面试的时候有更多更丰富的资历让大家可以与面试官侃侃而谈。

数据库环境

MySQL版本：5.7.31-log

数据库字符集，所有数据库通用字符集与排序规则，支持中文数据。

字符集：utf8 排序规则：utf8_general_ci

使用工具：Navicat Premium 15，可以在下面的连接中下载

https://download.csdn.net/download/feng8403000/89403778

项目名称与项目简介

问卷调查统计分析系统旨在帮助组织或企业快速创建、发布问卷，收集数据，并进行深入统计分析。系统支持自定义问卷问题类型、逻辑跳转、答案选项等，同时提供数据可视化功能，让用户能够直观地了解问卷结果，辅助决策制定。

系统的主要功能包括：

  1. 用户管理：管理问卷创建者、参与者等用户信息。
  2. 问卷管理：创建、编辑、发布、回收问卷。
  3. 数据收集：接收用户的问卷回答。
  4. 统计分析：对问卷数据进行统计、分析和可视化展示。

数据库DDL（注意创建顺序）

为了直接运行DDL语句并创建表，我们需要确保在创建含有外键约束的表之前，相关的被引用表（即外键指向的表）已经存在。所以我们在创建表的时候一定要按照一定的顺序来创建，否则就会出现没有外键关系导致的创建异常。

代码语言：javascript复制
-- 用户表
CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    password VARCHAR(255) NOT NULL COMMENT '密码',
    name VARCHAR(100) NOT NULL COMMENT '姓名',
    email VARCHAR(100) UNIQUE COMMENT '邮箱',
    gender ENUM('男', '女') NOT NULL COMMENT '性别'
);


-- 问卷表
CREATE TABLE Surveys (
    survey_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '问卷ID',
    title VARCHAR(255) NOT NULL COMMENT '问卷标题',
    description TEXT COMMENT '问卷描述',
    creator_id INT NOT NULL COMMENT '创建者ID',
    creation_date DATE NOT NULL COMMENT '创建日期',
    status ENUM('草稿', '发布', '归档') NOT NULL COMMENT '问卷状态',
    FOREIGN KEY (creator_id) REFERENCES Users(user_id)
);


-- 问题表
CREATE TABLE Questions (
    question_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '问题ID',
    survey_id INT NOT NULL COMMENT '所属问卷ID',
    question_text TEXT NOT NULL COMMENT '问题文本',
    question_type ENUM('文本', '单选', '多选', '量表') NOT NULL COMMENT '问题类型',
    order_number INT NOT NULL COMMENT '问题排序号',
    FOREIGN KEY (survey_id) REFERENCES Surveys(survey_id)
);


-- 选项表（针对单选、多选问题）
CREATE TABLE Choices (
    choice_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '选项ID',
    question_id INT NOT NULL COMMENT '所属问题ID',
    choice_text VARCHAR(255) NOT NULL COMMENT '选项文本',
    order_number INT NOT NULL COMMENT '选项排序号',
    FOREIGN KEY (question_id) REFERENCES Questions(question_id)
);


-- 回答表
CREATE TABLE Answers (
    answer_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '回答ID',
    user_id INT NOT NULL COMMENT '用户ID',
    survey_id INT NOT NULL COMMENT '问卷ID',
    question_id INT NOT NULL COMMENT '问题ID',
    answer_text TEXT COMMENT '文本类回答内容', -- 用于文本类回答
    choice_ids VARCHAR(255) COMMENT '选项ID列表，逗号分隔', -- 用于单选或多选类回答，存储选择的选项ID
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (survey_id) REFERENCES Surveys(survey_id),
    FOREIGN KEY (question_id) REFERENCES Questions(question_id)
);


-- 问卷参与记录表（可选，用于记录用户的问卷参与情况）
CREATE TABLE SurveyParticipations (
    participation_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '参与记录ID',
    user_id INT NOT NULL COMMENT '用户ID',
    survey_id INT NOT NULL COMMENT '问卷ID',
    start_time DATETIME COMMENT '开始时间',
    finish_time DATETIME COMMENT '结束时间',
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (survey_id) REFERENCES Surveys(survey_id)
);

插入数据DML（注意插入数据顺序）

插入数据的时候也要注意主外键关系，如果没有外检的情况下是没有办法插入从表数据的。

代码语言：javascript复制
INSERT INTO Users (username, password, name, email, gender) VALUES
('诸葛亮', '123', '诸葛亮', 'zhugeliang@example.com', '男'),
('孙悟空', '123', '孙悟空', 'sunwukong@example.com', '男'),
('林黛玉', '123', '林黛玉', 'lindaiyu@example.com', '女');

-- 假设用户ID 1 是 '诸葛亮', 用户ID 2 是 '孙悟空', 用户ID 3 是 '林黛玉'
INSERT INTO Surveys (title, description, creator_id, creation_date, status) VALUES
('用户满意度调查', '关于我们的产品用户满意度的调查', 1, '2023-01-01', '发布'),
('员工工作体验问卷', '收集员工对公司的工作体验反馈', 2, '2023-02-01', '草稿'),
('客户服务质量调查', '评估我们的客户服务质量和效果', 3, '2023-03-01', '归档');

-- 假设问卷ID 1 是 '用户满意度调查', 问卷ID 2 是 '员工工作体验问卷', 问卷ID 3 是 '客户服务质量调查'
INSERT INTO Questions (survey_id, question_text, question_type, order_number) VALUES
(1, '您对我们的产品整体满意度如何？', '量表', 1),
(1, '您认为我们产品的哪些方面需要改进？', '文本', 2),
(2, '您对公司的福利待遇满意吗？', '单选', 1),
(2, '您认为公司的工作氛围如何？', '文本', 2),
(3, '您对我们客服的响应速度满意吗？', '单选', 1),
(3, '您希望我们的客服在哪些方面提升？', '文本', 2);

-- 假设问题ID 2 是 '您对公司的福利待遇满意吗？', 问题ID 6 是 '您对我们客服的响应速度满意吗？'
INSERT INTO Choices (question_id, choice_text, order_number) VALUES
(2, '非常满意', 1),
(2, '一般', 2),
(2, '不满意', 3),
(6, '非常满意', 1),
(6, '比较满意', 2),
(6, '一般', 3),
(6, '不满意', 4);

-- 假设用户ID 1 是 '诸葛亮', 用户ID 2 是 '孙悟空', 问卷ID 1 是 '用户满意度调查'
INSERT INTO Answers (user_id, survey_id, question_id, answer_text, choice_ids) VALUES
(1, 1, 1, '5分（满分）', NULL), -- 量表问题，假设5分是满分
(1, 1, 2, '希望产品界面更加友好', NULL), -- 文本问题
(2, 1, 1, '3分', NULL),
(2, 1, 2, '功能需要更完善', NULL),
(1, 2, 2, '工作氛围很融洽', NULL), -- 假设这个问题没有选项
(2, 2, 2, '有一些问题需要改进', NULL),
(1, 3, 1, '2', NULL), -- 假设选项ID从1开始
(1, 3, 2, '希望客服更加专业', NULL),
(2, 3, 1, '3', NULL);
INSERT INTO SurveyParticipations (user_id, survey_id, start_time, finish_time) VALUES
(1, 1, '2023-01-02 10:00:00', '2023-01-02 10:30:00'),
(2, 1, '2023-01-02 11:00:00', '2023-01-02 11:15:00');

遵循的数据库三范式

数据库建表的三范式（3NF，Third Normal Form）是关系型数据库设计的基本原则，用于确保数据库结构的逻辑性和减少数据冗余。这三个范式是逐步细化的，每一个范式都是在前一个范式的基础上建立的。下面我将详细解释这三个范式：

第一范式（1NF, First Normal Form）

定义：

  1. 列不可分割，即数据库表的每一列都是不可分割的原子数据项。
  2. 每一列都是不可再分的最小数据单元（也称为最小的原子单元）。

解释：

  • 在第一范式中，主要关注的是列的原子性。也就是说，表中的每一列都应该只包含一个值，而不能包含集合、数组或其他复合数据类型。
  • 例如，如果有一个“地址”列，它包含了街道、城市、省份和国家等信息，那么这就违反了第一范式。应该将这个“地址”列拆分成多个独立的列，如“街道”、“城市”、“省份”和“国家”。

第二范式（2NF, Second Normal Form）

定义：

  1. 满足1NF。
  2. 非主键列必须完全依赖于主键，而不能只依赖于主键的一部分（针对复合主键而言）。

解释：

  • 第二范式建立在第一范式的基础上，主要关注于主键与非主键列之间的依赖关系。
  • 在第二范式中，一个表只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
  • 如果表中的某一列只与复合主键的一部分有关，那么它就不应该存在于这个表中，而应该被分离出去形成另外一张新表。

第三范式（3NF, Third Normal Form）

定义：

  1. 满足2NF。
  2. 非主键列必须直接依赖于主键，不能存在传递依赖。即非主键列必须直接依赖于整个主键，而不能依赖于主键的一部分。

解释：

  • 第三范式是在第二范式的基础上进一步细化的。它主要关注于消除传递依赖，即非主键列不应该依赖于主键的某一部分，而应该直接依赖于整个主键。
  • 如果存在传递依赖，那么应该考虑将这个非主键列分离出去，形成新的表，并通过主键或外键与原表进行关联。
数据库 mysql 基础 数据 系统

0 人点赞