【数据库】根据一个具体需求写出关系模式及画出E-R图并且构建一个简单完善的数据库

☞黑心萝卜三条杠☜

已于 2023-01-13 10:25:20 修改

阅读量4k

点赞数 2

分类专栏：数据库文章标签：数据库 mysql

于 2022-10-09 22:13:29 首次发布

本文链接：https://blog.csdn.net/qq_53960242/article/details/126894119

版权

数据库专栏收录该内容

8 篇文章 1 订阅

订阅专栏

本章小编将讲述如何根据一个具体的需求来规划一个简单、方便且合理的数据库，话不多说，下面就让小编带大家走进本章内容。

一、需求

假设要根据某大学计算机学院成绩管理的业务规则设计一个数据库的概念模型，这个学院的业务规则如下：

1、该学院聘用多名教师，但每一位教师只属于该学院。教师包括教工号、姓名、学历、职称、工资、电话号码信息。
2、一位教师可能讲多门课，每一门课可由多位教师讲授。课程包括课程号、课程名称、学时数和学分信息，每门课程可存在一门先行课程。
3、该学院有多名学生，且每一名学生只能属于该学院。学生包括学号、姓名、性别、出生年月、专业、籍贯、电话号码信息。
4、每一名学生可选修多门课，且一门课有多名学生选；学生所修课程的成绩包括平时成绩与期末成绩。

二、E-R图

看到上述需求不用多想，在ER图中一定存在教师、学生、课程、学院这四个实体以及实体在需求中对应的属性，至于实体之间的关系，我们可以通过需求中两个实体之间的动词来判断，如：属于、讲授、选修等。
我们就需要考虑两个实体间关系的属性是否需要或者是应该包含哪些属性。例如：授课学期这个属性，也许会被放在教师这个实体中，当成教师的属性，但是得想想：一般情况下，数据库里的记录一般是不会删除的，而老师这个角色又是每年都会授课，这就会导致每次添加教师授课记录时，还需要将教师的教师编号、姓名、学历等基本信息也都添加一次，显然，这是不符合实际需求的，在实际中，我们只要记录的是教师在某某年份上的课程，而不是完全的、再次记录教师基本信息加某年的授课记录。因此，授课学期这个属性应该放在授课这个关系中，而不是放在教师这个实体中。
在考虑完ER图中各个实体间的关系及属性后，我们还应该考虑各个实体间的代数关系，如：一对一、一对多、多对多等等。这里小编就拿教师与学院举例，在日常生活中，一个学院能够包含多个教师，但是呢一个教师属于一个学院，因此这里是一个一（学院）对多（教师）的关系。

E-R图

三、关系模式

所谓的关系模式实际上就是数据表，也就是我们建立数据库时创建的数据表。
在写关系模式时，我们首先要明确一点是：在ER图中，实体与多对多的关系都需要使用一个关系来表示，除此之外，还可以能够正确表达需求的基础上适当添加一些合理的关系模式使得该数据库更加完善。
那么如何选择一个合适的主码呢？将一个字段设置成主码的前提因素就是该字段的唯一性，像下述关系模式中的学院号、学号、教师工号等都是一个唯一的值，因此用作主码再合适不过。或者，在设置主码时不用一个字段作为主码，而是选择多个字段作为主码，如在授课表中，由于同一个老师每个学期都需要上相同或不同的课，因此在该表中教师工号与课程号都有相同的可能，那我们加上授课学期作为主码，那不就完美解决这个问题了嘛。
因此，上述ER图的关系模式给出如下：

学院（学院号，学院名称）主码：学院号
教师（教师工号，姓名，学历，年龄，职称，工资，电话，学院号）主码：教师工号外码：学院号
学生（学号、姓名、性别、出生年月、专业、籍贯、电话、学院号）主码：学号外码：学院号
课程（课程号、课程名称、课程时数、课程学分、先行课程号）主码：课程号外码：先行课程号
授课（教师工号、课程号、授课学期）主码：课程号、授课学期、教师工号外码：教师工号、课程号
选课（学号、课程号、上课学期、平时成绩、期末成绩）主码：学号、课程号、上课学期外码：学号、课程号

四、在MySQL中创建一个数据库

1、使用 create database +数据库名称创建一个数据库

# 创建一个名字为workPlace的数据库
create database workPlace;

在这里插入图片描述
2、使用create table +表名+字段（字段名字段类型字段的限制修饰）
在学校表中，我们将学校的编码设置成主码，将学校名称设置成唯一值的限制，这样即使学校名称不为主码也能保证该字段不出现重复的值。

#创建学校表
CREATE TABLE `school` (
  `schoolcode` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学院编码',
  `schoolname` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学院名称',
  PRIMARY KEY (`schoolcode`) USING BTREE,
  UNIQUE KEY `schoolName` (`schoolname`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

在这里插入图片描述

在学生表中，我们将学号设置成主码，将学院编号设置成外码，该外码参照的是学校表。

# 创建一个学生表
CREATE TABLE `student` (
  `sno` varchar(20) NOT NULL COMMENT '学号',
  `sname` varchar(15) NOT NULL COMMENT '姓名',
  `ssex` varchar(10) DEFAULT NULL COMMENT '性别',
  `stime` date DEFAULT NULL,
  `location` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '籍贯',
  `major` varchar(20) NOT NULL COMMENT '专业',
  `sphonenumber` varchar(11) DEFAULT NULL COMMENT '电话',
  `schoolcode` varchar(20) NOT NULL COMMENT '学院编号',
  PRIMARY KEY (`sno`) USING BTREE,
  KEY `schoolcode` (`schoolcode`),
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`schoolcode`) REFERENCES `school` (`schoolcode`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

在这里插入图片描述
在教师表中，我们将教职工号设置成主码，将学校编码设置成参照学校表的外码。

# 创建教师表
CREATE TABLE `teacher` (
  `id` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '教职工号',
  `name` varchar(20) NOT NULL COMMENT '教师名字',
  `education` varchar(255) DEFAULT NULL,
  `title` varchar(20) DEFAULT NULL COMMENT '教师职称',
  `salary` float DEFAULT NULL COMMENT '教师工资',
  `age` varchar(0) DEFAULT NULL COMMENT '教师年龄',
  `phonenumber` varchar(11) DEFAULT NULL COMMENT '教师电话',
  `schoolcode` varchar(20) NOT NULL COMMENT '学院编码',
  PRIMARY KEY (`id`,`schoolcode`),
  KEY `schoolcode` (`schoolcode`),
  KEY `id` (`id`),
  CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`schoolcode`) REFERENCES `school` (`schoolcode`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

在这里插入图片描述
在课程表中，我们将课程编号设置成主码，而先行课程号设置成参照自己的外码。

CREATE TABLE `course` (
  `id` varchar(20) NOT NULL COMMENT '课程编号',
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程名称',
  `time` varchar(10) NOT NULL COMMENT '课程时数',
  `score` varchar(10) NOT NULL COMMENT '课程学分',
  `beforeid` varchar(20) DEFAULT NULL COMMENT '先行课程号',
  PRIMARY KEY (`id`),
  KEY `course_ibfk_1` (`beforeid`),
  CONSTRAINT `course_ibfk_1` FOREIGN KEY (`beforeid`) REFERENCES `course` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

在这里插入图片描述

在选课表中，将课程编号、学号、上课时间三者设置成一个联合主键，再将学号设置成参照学生表的外码，将课程编号设置成参照课程表的外码

#选课表
CREATE TABLE `sc` (
  `cid` varchar(20) NOT NULL COMMENT '课程编号',
  `time` varchar(20) NOT NULL COMMENT '上课时间',
  `sno` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学号',
  `usualgrade` int DEFAULT NULL COMMENT '课程成绩',
  `examgrade` int DEFAULT NULL COMMENT '期末成绩',
  PRIMARY KEY (`sno`,`cid`,`time`),
  KEY `cid` (`cid`),
  CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`cid`) REFERENCES `course` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

在这里插入图片描述
在授课表中，将课程编号、授课时间、教职工号三者设置成一个联合主键，将课程编号设置成参照课程表的外码，将教职工号设置成参照教师表的外码。

#授课表
CREATE TABLE `tc` (
  `cid` varchar(20) NOT NULL COMMENT '课程编号',
  `time` varchar(20) NOT NULL COMMENT '授课时间',
  `tid` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '教职工号',
  PRIMARY KEY (`tid`,`cid`,`time`),
  KEY `cid` (`cid`),
  CONSTRAINT `tc_ibfk_1` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tc_ibfk_2` FOREIGN KEY (`cid`) REFERENCES `course` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;