数据库设计实验 || 盗梦空间

[[数据库操作实验]]

0.1. 实验名称

数据库设计实验

0.2. 实验目的

掌握数据库设计基本方法。

0.3. 实验要求

掌握数据库设计基本步骤，包括数据库概念结构设计、逻辑结构设计，数据库模式SQL 语句生成。

0.4. 实验重点和难点

实验重点：概念结构设计、逻辑结构设计。实验难点：逻辑结构设计。逻辑结构设计虽然可以按照一定的规则从概念结构转换而来，但是由于概念结构通常比较抽象，较少考虑更多细节，因此转换而成的逻辑结构还需要进一步调整和优化。逻辑结构承接概念结构和物理结构，处于核心地位，因而是数据库设计的重点，也是难点。

0.5. 实验内容

设计一个服务于“XX 大学XX 学院”的本科生教务管理数据库。需求如下：学院有若干系，每个系有若干专业和教研室，每个专业有若干班级，每个教研室有若干教师，每个班级有若干学生；

每位学生学习若干课程，每门课程有学分、学时、考试方式等性质；
每门课程由若干教师讲授，这些教师共同组成一个关于本课程的课程组，课程组的老师可来自不同的教研室或专业，课程组有一位负责教师；
每门课程有若干本参考书，其中一本作为指定教材，某本参考书可用于不同课程；
每个专业有一个培养计划，规定每门课程是必修还是选修，以及课程在哪个学期讲授；
每位学生所修的课程要有考试成绩。

0.6. 实验要求

设计数据库概念结构从上述需求中识别出相应的实体，分析每个实体的属性和码。例如：学生实体的属性包括但不限于：学号、姓名、身份证号、性别、出生日期、所属班级、入学时间、联系电话、家庭住址等。确定实体之间的一对一、一对多和多对多联系。画出实体-联系(E-R)图。
设计数据库逻辑结构按照教材第7 章中概念结构转化为逻辑结构的规则，根据上述E-R 图，将每个实体转换成一个关系，多对多的联系也转换成一个关系。
生成数据库模式SQL 语句生成MySQL 数据库管理系统的SQL 语句。
创建数据库模式执行所生成的SQL 语句，创建本科生教务管理数据库模式。

0.7. 实验结果

0.7.1. （1）关系模型

班级 (班级ID, 班级名称, 人数, 专业ID (FK))
参考书 (参考书ID, 参考书名称)
教师 (教师ID, 姓名, 教研室ID (FK), 职称)
教研室 (教研室ID, 教研室名称, 人数, 系ID (FK))
课程 (课程ID, 课程名称, 学时, 考核方式, 学分, 教授教师ID (FK))
课程_参考书 (课程ID (PK, FK), 参考书ID (PK, FK), 是否指定教材)
课程组 (课程组ID, 负责教师ID (FK), 课程ID (FK))
培养计划 (培养计划ID, 专业ID (FK), 课程ID (FK), 是否必修, 学期)
系 (系ID, 系名称, 人数, 学院ID (FK))
学生 (学生ID, 姓名, 身份证号, 性别, 出生日期, 入学日期, 联系电话, 家庭住址, 班级ID (FK))
学院 (学院ID, 学院名称, 总人数)
专业 (专业ID, 专业名称, 人数, 系ID (FK))

0.7.2. （2）E-R图

数据库E-R图

0.7.3. （3）导出SQL语句

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- 选择或创建数据库
CREATE DATABASE IF NOT EXISTS `Undergraduate_Academic_Administration_Database`;
USE `Undergraduate_Academic_Administration_Database`;

CREATE TABLE `班级`  (
  `班级ID` int NOT NULL,
  `班级名称` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `人数` int NULL DEFAULT NULL,
  `专业ID` int NULL DEFAULT NULL,
  PRIMARY KEY (`班级ID`) USING BTREE,
  INDEX `专业ID`(`专业ID` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

CREATE TABLE `参考书`  (
  `参考书ID` int NOT NULL,
  `参考书名称` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`参考书ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

CREATE TABLE `教师`  (
  `教师ID` int NOT NULL,
  `姓名` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `教研室ID` int NULL DEFAULT NULL,
  `职称` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`教师ID`) USING BTREE,
  INDEX `教研室ID`(`教研室ID` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

CREATE TABLE `教研室`  (
  `教研室ID` int NOT NULL,
  `教研室名称` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `人数` int NULL DEFAULT NULL,
  `系ID` int NULL DEFAULT NULL,
  PRIMARY KEY (`教研室ID`) USING BTREE,
  INDEX `系ID`(`系ID` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

CREATE TABLE `课程`  (
  `课程ID` int NOT NULL,
  `课程名称` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `学时` int NULL DEFAULT NULL,
  `考核方式` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `学分` int NULL DEFAULT NULL,
  `教授教师ID` int NULL DEFAULT NULL,
  PRIMARY KEY (`课程ID`) USING BTREE,
  INDEX `教授教师ID`(`教授教师ID` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

CREATE TABLE `课程_参考书`  (
  `课程ID` int NOT NULL,
  `参考书ID` int NOT NULL,
  `是否指定教材` tinyint(1) NULL DEFAULT NULL,
  PRIMARY KEY (`课程ID`, `参考书ID`) USING BTREE,
  INDEX `参考书ID`(`参考书ID` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

CREATE TABLE `课程组`  (
  `课程组ID` int NOT NULL,
  `负责教师ID` int NULL DEFAULT NULL,
  `课程ID` int NULL DEFAULT NULL,
  PRIMARY KEY (`课程组ID`) USING BTREE,
  INDEX `负责教师ID`(`负责教师ID` ASC) USING BTREE,
  INDEX `课程ID`(`课程ID` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

CREATE TABLE `培养计划`  (
  `培养计划ID` int NOT NULL,
  `专业ID` int NULL DEFAULT NULL,
  `课程ID` int NULL DEFAULT NULL,
  `是否必修` tinyint(1) NULL DEFAULT NULL,
  `学期` int NULL DEFAULT NULL,
  PRIMARY KEY (`培养计划ID`) USING BTREE,
  INDEX `专业ID`(`专业ID` ASC) USING BTREE,
  INDEX `课程ID`(`课程ID` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

CREATE TABLE `系`  (
  `系ID` int NOT NULL,
  `系名称` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `人数` int NULL DEFAULT NULL,
  `学院ID` int NULL DEFAULT NULL,
  PRIMARY KEY (`系ID`) USING BTREE,
  INDEX `学院ID`(`学院ID` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

CREATE TABLE `学生`  (
  `学生ID` int NOT NULL,
  `姓名` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `身份证号` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `性别` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `出生日期` date NULL DEFAULT NULL,
  `入学日期` date NULL DEFAULT NULL,
  `联系电话` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `家庭住址` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `班级ID` int NULL DEFAULT NULL,
  PRIMARY KEY (`学生ID`) USING BTREE,
  INDEX `班级ID`(`班级ID` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

CREATE TABLE `学院`  (
  `学院ID` int NOT NULL,
  `学院名称` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `总人数` int NULL DEFAULT NULL,
  PRIMARY KEY (`学院ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

CREATE TABLE `专业`  (
  `专业ID` int NOT NULL,
  `专业名称` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `人数` int NULL DEFAULT NULL,
  `系ID` int NULL DEFAULT NULL,
  PRIMARY KEY (`专业ID`) USING BTREE,
  INDEX `系ID`(`系ID` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

ALTER TABLE `班级` ADD CONSTRAINT `班级_ibfk_1` FOREIGN KEY (`专业ID`) REFERENCES `专业` (`专业ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `教师` ADD CONSTRAINT `教师_ibfk_1` FOREIGN KEY (`教研室ID`) REFERENCES `教研室` (`教研室ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `教研室` ADD CONSTRAINT `教研室_ibfk_1` FOREIGN KEY (`系ID`) REFERENCES `系` (`系ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `课程` ADD CONSTRAINT `课程_ibfk_1` FOREIGN KEY (`教授教师ID`) REFERENCES `教师` (`教师ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `课程_参考书` ADD CONSTRAINT `课程_参考书_ibfk_1` FOREIGN KEY (`课程ID`) REFERENCES `课程` (`课程ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `课程_参考书` ADD CONSTRAINT `课程_参考书_ibfk_2` FOREIGN KEY (`参考书ID`) REFERENCES `参考书` (`参考书ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `课程组` ADD CONSTRAINT `课程组_ibfk_1` FOREIGN KEY (`负责教师ID`) REFERENCES `教师` (`教师ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `课程组` ADD CONSTRAINT `课程组_ibfk_2` FOREIGN KEY (`课程ID`) REFERENCES `课程` (`课程ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `培养计划` ADD CONSTRAINT `培养计划_ibfk_1` FOREIGN KEY (`专业ID`) REFERENCES `专业` (`专业ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `培养计划` ADD CONSTRAINT `培养计划_ibfk_2` FOREIGN KEY (`课程ID`) REFERENCES `课程` (`课程ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `系` ADD CONSTRAINT `系_ibfk_1` FOREIGN KEY (`学院ID`) REFERENCES `学院` (`学院ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `学生` ADD CONSTRAINT `学生_ibfk_1` FOREIGN KEY (`班级ID`) REFERENCES `班级` (`班级ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `专业` ADD CONSTRAINT `专业_ibfk_1` FOREIGN KEY (`系ID`) REFERENCES `系` (`系ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;

0.8. 实验总结

总的来说，这次实验没有第一次实验繁琐，但是这次实验较第一次实验而言难度有所提高，在设计数据库时要考虑的东西有很多，要确保数据库尽可能少的有数据冗余，避免更新异常、插入异常和删除异常.在这个实验中，首先进行数据库的概念结构设计，找出与教务管理系统相关的实体，明确每个实体的属性和主键。然后确定实体之间的关系，绘制实体-联系(E-R)图，以更好地理解数据之间的关系。
我主要遇到了以下几点问题：

1.首先是不知道该用什么工具去设计，在查阅资料、在B站学习后我选择使用navicat去设计数据库。

2.在确定关系模型时，非常容易造成数据冗余。比如当一个实体与另一个通过各自的摸一个属性关联时，就非常容易造成数据冗余。数据冗余可能导致更新异常、插入异常和删除异常，因此在设计过程中要注意规范化数据库，确保每一处信息只在数据库中存储一份。这可以通过分解表、建立关联等手段来实现。

3.在用navicat导出的SQL文件后，我发现重新运行该SQL文件时会报错。后来才发现是因为navicat导出的SQL文件并不会定义和创建模式或者数据库，所以要事先创建数据库后再运行该SQL文件，或者人为对navicat导出的SQL文件做出一些修改，即在导出的SQL文件开头加上数据库定义和创建语句：

-- 选择或创建数据库
CREATE DATABASE IF NOT EXISTS `Undergraduate_Academic_Administration_Database`;
USE `Undergraduate_Academic_Administration_Database`;

通过这次实验，我提高了数据库设计的能力，更好地理解了数据库的概念和结构，为今后的数据库设计和管理工作打下了坚实的基础。