MySQL练习——教学系统数据库设计

目录

 

1. 教学系统概述

2. 数据库结构设计

3. 教学系统测试

 

---

1. 教学系统概述

根据大学生教学系统的原型设计出如下的ER关系图，主要来练习数据库系统的搭建：

上图一共包含五个实体，分别是学生，教师，课程，院系，行政班级：

1. 其中学生和课程的关系是多对多，即一个学生可以选择多门课程，而一个课程又有多个学生选择。每个学生的每门课程都有一个成绩，所以选课表中应该有成绩字段。
2. 课程和教师是多对一关系，即一个教师只教一门课程，而一个课程又由多位老师教授。
3. 教师和院系是一对多的关系，即一个教师只属于一个院系，而一个院系可以聘请多位老师。
4. 行政班级和院系是多对一的关系，即一个院系有多个行政班级，而一个行政班级只属于一个院系。
5. 学生和行政班级是多对一的关系，即一个行政班级有多个学生，而一个学生只属于一个行政班级。

2. 数据库结构设计

根据以上分析利用MySQLWorkbench软件构建成E-R模型：

 设计完成后，直接导出SQL执行脚本：

创建脚本如下：

-- -----------------------------------------------------
-- Table `course`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `course` ;

CREATE TABLE IF NOT EXISTS `course` (
  `cou_id` INT NOT NULL ,
  `cou_name` VARCHAR(45) NULL ,
  `cou_score` CHAR(2) NULL ,
  `cou_info` TEXT NULL ,
  `cou_limit` INT NULL ,
  PRIMARY KEY (`cou_id`)  )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `department`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `department` ;

CREATE TABLE IF NOT EXISTS `department` (
  `dep_id` INT NOT NULL ,
  `dep_name` VARCHAR(45) NULL ,
  `dep_info` TEXT NULL ,
  PRIMARY KEY (`dep_id`)  )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `class`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `class` ;

CREATE TABLE IF NOT EXISTS `class` (
  `cls_id` INT NOT NULL ,
  `dep_id` INT NULL ,
  PRIMARY KEY (`cls_id`)  )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `student`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `student` ;

CREATE TABLE IF NOT EXISTS `student` (
  `stu_id` INT NOT NULL ,
  `stu_name` VARCHAR(45) NULL ,
  `stu_grade` CHAR(2) NULL ,
  `stu_sex` CHAR(2) NULL ,
  `cls_id` INT NULL ,
  `stu_hobby` VARCHAR(45) NULL ,
  `stu_birth` DATE NULL ,
  PRIMARY KEY (`stu_id`)  )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `choose_course`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `choose_course` ;

CREATE TABLE IF NOT EXISTS `choose_course` (
  `stu_id` INT NOT NULL ,
  `cou_id` INT NOT NULL ,
  `stu_cou_score` INT NULL ,
  PRIMARY KEY (`stu_id`, `cou_id`)  )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `techer`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `teacher` ;

CREATE TABLE IF NOT EXISTS `teacher` (
  `tec_id` INT NOT NULL ,
  `tec_name` VARCHAR(45) NULL ,
  `dep_id` INT NULL ,
  PRIMARY KEY (`tec_id`)  )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- ALTER Table FOREIGN KEY
-- -----------------------------------------------------
ALTER TABLE student ADD CONSTRAINT fk_student_class FOREIGN KEY (cls_id) REFERENCES class (cls_id);
ALTER TABLE teacher ADD CONSTRAINT fk_teacher_department FOREIGN KEY (dep_id) REFERENCES department (dep_id);
ALTER TABLE class ADD CONSTRAINT fk_classes_department FOREIGN KEY (dep_id) REFERENCES department (dep_id);
ALTER TABLE choose_course ADD CONSTRAINT fk_choose_course_course FOREIGN KEY (cou_id) REFERENCES course (cou_id);
ALTER TABLE choose_course ADD CONSTRAINT fk_choose_course_student FOREIGN KEY (stu_id) REFERENCES student (stu_id);

 将创建脚本导入Navicat中，添加数据，最终导出结果如下：

/*
Navicat MySQL Data Transfer

Source Server         : local_mysql
Source Server Version : 50626
Source Host           : localhost:3306
Source Database       : mytest

Target Server Type    : MYSQL
Target Server Version : 50626
File Encoding         : 65001

Date: 2020-10-25 15:01:50
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for choose_course
-- ----------------------------
DROP TABLE IF EXISTS `choose_course`;
CREATE TABLE `choose_course` (
  `stu_id` int(11) NOT NULL,
  `cou_id` int(11) NOT NULL,
  `stu_cou_score` int(11) DEFAULT NULL,
  PRIMARY KEY (`stu_id`,`cou_id`),
  KEY `fk_choose_course_course` (`cou_id`),
  CONSTRAINT `fk_choose_course_course` FOREIGN KEY (`cou_id`) REFERENCES `course` (`cou_id`),
  CONSTRAINT `fk_choose_course_student` FOREIGN KEY (`stu_id`) REFERENCES `student` (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of choose_course
-- ----------------------------
INSERT INTO `choose_course` VALUES ('1001', '30010001', '80');
INSERT INTO `choose_course` VALUES ('1001', '30010002', '90');
INSERT INTO `choose_course` VALUES ('1002', '30050001', '70');
INSERT INTO `choose_course` VALUES ('1003', '30020001', '95');

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `cls_id` int(11) NOT NULL,
  `dep_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`cls_id`),
  KEY `fk_classes_department` (`dep_id`),
  CONSTRAINT `fk_classes_department` FOREIGN KEY (`dep_id`) REFERENCES `department` (`dep_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('2020001', '3001');
INSERT INTO `class` VALUES ('2020002', '3002');
INSERT INTO `class` VALUES ('2020003', '3003');
INSERT INTO `class` VALUES ('2020004', '3004');
INSERT INTO `class` VALUES ('2020005', '3005');
INSERT INTO `class` VALUES ('2020006', '3006');

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cou_id` int(11) NOT NULL,
  `cou_name` varchar(45) DEFAULT NULL,
  `cou_score` char(2) DEFAULT NULL,
  `cou_info` text,
  `cou_limit` int(11) DEFAULT NULL,
  PRIMARY KEY (`cou_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('30010001', '计算机组成原理', '3', '讲解计算机组成原理知识', '50');
INSERT INTO `course` VALUES ('30010002', 'C++程序设计', '5', 'C++程序设计与实践', '80');
INSERT INTO `course` VALUES ('30020001', '管理学', '2', '管理相关知识', '40');
INSERT INTO `course` VALUES ('30030001', '机械加工与设计', '2', '介绍机械加工原理以及设计基础', '50');
INSERT INTO `course` VALUES ('30040001', '自动控制原理', '4', '自动控制原理相关知识', '50');
INSERT INTO `course` VALUES ('30050001', '化工理论', '3', '化工基础理论知识', '60');

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `dep_id` int(11) NOT NULL,
  `dep_name` varchar(45) DEFAULT NULL,
  `dep_info` text,
  PRIMARY KEY (`dep_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES ('3001', '计算机学院', '计算机相关课程');
INSERT INTO `department` VALUES ('3002', '经管学院', '经济管理课程');
INSERT INTO `department` VALUES ('3003', '机械学院', '机械加工制造课程');
INSERT INTO `department` VALUES ('3004', '自动化学院', '自动化原理课程');
INSERT INTO `department` VALUES ('3005', '化工学院', '化工课程');
INSERT INTO `department` VALUES ('3006', '材料学院', '材料与分子课程');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `stu_id` int(11) NOT NULL,
  `stu_name` varchar(45) DEFAULT NULL,
  `stu_grade` char(2) DEFAULT NULL,
  `stu_sex` char(2) DEFAULT NULL,
  `cls_id` int(11) DEFAULT NULL,
  `stu_hobby` varchar(45) DEFAULT NULL,
  `stu_birth` date DEFAULT NULL,
  PRIMARY KEY (`stu_id`),
  KEY `fk_student_class` (`cls_id`),
  CONSTRAINT `fk_student_class` FOREIGN KEY (`cls_id`) REFERENCES `class` (`cls_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1001', '李明', '大三', '男', '2020001', '游泳', '2000-06-14');
INSERT INTO `student` VALUES ('1002', '肖潇', '大一', '女', '2020005', '音乐', '2000-05-02');
INSERT INTO `student` VALUES ('1003', '张默', '大二', '男', '2020002', '足球', '2020-10-30');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tec_id` int(11) NOT NULL,
  `tec_name` varchar(45) DEFAULT NULL,
  `dep_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`tec_id`),
  KEY `fk_teacher_department` (`dep_id`),
  CONSTRAINT `fk_teacher_department` FOREIGN KEY (`dep_id`) REFERENCES `department` (`dep_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('20001', '韩明', '3006');
INSERT INTO `teacher` VALUES ('20002', '陆函', '3004');
INSERT INTO `teacher` VALUES ('20003', '黄勤', '3002');
INSERT INTO `teacher` VALUES ('20004', '高潇潇', '3001');
INSERT INTO `teacher` VALUES ('20005', '李晓飞', '3003');

3. 教学系统测试

查找学生id为1001所选课程：

mysql> select cou_name from course where cou_id in (select cou_id from choose_course where stu_id=1001);
+----------------+
| cou_name       |
+----------------+
| 计算机组成原理 |
| C++程序设计    |
+----------------+
2 rows in set

查找学生id为1001所在院系、课程、分数：

mysql> select a.dep_name,a.dep_info,b.cou_name,c.stu_cou_score,d.stu_name from department as a ,course as b ,
choose_course as c , student as d,class as e where d.stu_id=1001
 and a.dep_id=e.dep_id and d.cls_id=e.cls_id and d.stu_id=c.stu_id and c.cou_id=b.cou_id;
+------------+----------------+----------------+---------------+----------+
| dep_name   | dep_info       | cou_name       | stu_cou_score | stu_name |
+------------+----------------+----------------+---------------+----------+
| 计算机学院 | 计算机相关课程 | 计算机组成原理 |            80 | 李明     |
| 计算机学院 | 计算机相关课程 | C++程序设计    |            90 | 李明     |
+------------+----------------+----------------+---------------+----------+
2 rows in set