目錄

 

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