实验问题描述：

图1 教学管理系统E-R图

某学院有若干专业，每个专业有若干学生；学院每年每学期都开设有若干门课程；每门课程有多个学生选修，每个学生每学期可以同时选

修多门课程，每个学生对于同一门课程可以多次选修，但每学期只能选修1次，课程选修成绩以综合成绩记录。请设计某学院简单的教学

管理系统的E-R模型，要求给出每个实体、联系的属性。根据以上语义，我们可得了上图1如示的教学管理系统E-R图（属性未画出）。

根据学院提供的学生选课及其他的部分数据，将上图1所示的E-R图中的部分实体和联系转换为关系，得到如下4个关系模式和相应的数据

字典描述。

A、关系模式

⑴ 专业设置：包括专业编号、国家专业编号、专业名称、专业英文名称、学制、培养层次、授予学位、院系编号、院系名称；

⑵ 学生情况：包括学号、姓名、性别、出生日期、民族、籍贯、政治面貌、校区、生源地、入学日期、年级、班级名称、专业编号；

⑶ 课程设置：包括学年、学期、课程编号、课程名称、学分、学时数、课程类别1、课程类别2、环节类别、考核方式；

⑷ 学生选课：包括学号、课程编号、学年、学期、综合成绩。

B、数据字典

表
表名	中文名
Major	专业设置
Student	学生情况
Course	课程设置
SelectCourse	学生选课

专业设置：Major
字段名	数据类型	可空性	主键	外键	默认值	字段含义
majorNo	CHAR(4)	NOT NULL	Yes	No		专业代码
GBMajorNo	CHAR(6)	NOT NULL	No	No		国家专业编号
majorName	VARCHAR(60)	NOT NULL	No	No		专业名称
enMajorName	VARCHAR(100)	NULL	No	No		英文名称
lengthSchool	TINYINT	NOT NULL	No	No	4	学制
eduLevel	CHAR(6)	NOT NULL	No	No	本科	培养层次
ddegree	CHAR(12)	NOT NULL	No	No		授予学位
departmentNo	CHAR(2)	NOT NULL	No	No		院系代码
department	VARCHAR(40)	NOT NULL	No	No		院系名称

约束条件：

• 专业代码为4位数字字符且不能为0000
• 国家专业编号为6位数字字符且不能为000000
• 院系代码为2位数字字符且不能为00

学生情况：Student
字段名	数据类型	可空性	主键	外键	默认值	字段含义
sno	CHAR(12)	NOT NULL	Yes	No		学生学号
sname	CHAR(16)	NOT NULL	No	No		学生姓名
sex	CHAR(2)	NOT NULL	No	No	男	性别
birthday	DATE	NOT NULL	No	No		出生日期
nationality	CHAR(16)	NULL	No	No	汉族	民族
native	CHAR CHAR(24)	NULL	No	No	东莞市	籍贯
political	CHAR(12)	NULL	No	No	共青团员	政治面貌
district	CHAR(12)	NOT NULL	No	No	松山湖校区	院系代码
studentSource	VARCHAR(24)	NULL	No	No		生源地
enterYear	DATE	NOT NULL	No	No		入学日期
schoolYear	CHAR(12) TINYINT	NOT NULL NOT NULL	No	No		年级
class	CHAR(24)	NOT NULL	No	No		班级
majorNo	CHAR(4)	NOT NULL	No	Yes		专业代码

约束条件：

• 学生学号为12位数字字符，第1位为2，其它位为数字且最后2位不能是00
• 性别只能是‘男’或‘女’；
• 专业代码参照表Major。

课程设置：Course
字段名	数据类型	可空性	主键	外键	默认值	字段含义
schoolYear	CHAR**（12****）TINYINT**	NULL NOT NULL	Yes	No		学年
semester	TINYINT	NOT NULL	Yes	No		学期
courseNo	CHAR(6)	NOT NULL	Yes	No		课程代码
courseName	VARCHAR(50)	NOT NULL	No	No		课程名称
credit	NUMERIC(3,1)	NOT NULL	No	No		学分
creditHourse	TINYINT	NOT NULL	No	No		学时
courseType1	CHAR(16)	NOT NULL	No	No		课程类别
courseType2	CHAR(16)	NULL	No	No		课程性质
cegmentType	CHAR(16)	NULL	No	No		环节类别
examineWay	CHAR(16)	NOT NULL	No	No		考核方式

约束条件：

• 学年取值范围是[1，2，3，4]
• 学期取值范围是[1，2，3，4，5，6，7，8]
• 课程代码为6位数字字符且不能为000000
• 学分取值范围是[ 0.5，1，1.5，2，2.5，3，3.5，4，4.5，5]
• 学年、学期和课程代码一起构成主码。

学生选课：SelectCourse
字段名	数据类型	可空性	主键	外键	默认值	字段含义
sno	CHAR(12)	NOT NULL	Yes	Yes		学号
schoolYear	CHAR(12) TINYINT	NOT NULL NOT NULL	Yes	Yes		学年
semester	TINYINT	NOT NULL	Yes	Yes		学期
courseNo	CHAR(6)	NOT NULL	Yes	Yes		课程代码
score	NUMERIC(6,2)	NULL	No	No		综合成绩

约束条件：

• 综合成绩为百分制成绩
• 学号、学年、学期和课程代码一起构成主码。
• 学号参照表student
• 学年、学期和课程代码一起参照表course

设计任务：

1. 使用DDL相应的语句完成数据定义操作

(1)用SQL语句（create database）创建数据库，数据库的名字为每位同学自己的拼音姓名和三位学号，例如，xiaojie001

SQL语句如下：

#系统：window10
#SQL语言：MySQL8.0
#创建数据库Lingxue
CREATE DATABASE Lingxue;

(2).在所创建的数据库下，用SQL语句（create table）创建所有的表，只建立主键，先不建立外键。

SQL语句如下：

#使用数据库lingxue
USE lingxue;

#创建Major表
CREATE TABLE Major
(
	majorNo	CHAR(4)	PRIMARY KEY,
	GBMajorNo CHAR(6) NOT NULL,
	majorName VARCHAR(60) NOT NULL,
	enMajorName VARCHAR(100) NULL,
	lengthSchool TINYINT NOT NULL DEFAULT 4,
	eduLevel CHAR(6) NOT NULL DEFAULT '本科',
	ddegree CHAR(12) NOT NULL,
	departmentNo CHAR(2) NOT NULL,
	department VARCHAR(40) NOT NULL
);

#添加约束
#专业代码为4位数字字符且不能为0000 
#国家专业编号为6位数字字符且不能为000000
#院系代码为2位数字字符且不能为00
ALTER TABLE Major
ADD CHECK (majorNo REGEXP '[0-9][0-9][0-9][0-9]' AND majorNo NOT LIKE '0000');
ALTER TABLE Major
ADD CHECK (GBMajorNo REGEXP '[0-9][0-9][0-9][0-9][0-9][0-9]' 
AND majorNo NOT LIKE '000000');
ALTER TABLE Major
ADD CHECK (departmentNo REGEXP '[0-9][0-9]' AND departmentNo NOT LIKE '00');

#创建Students表
CREATE TABLE Students
(
	sno CHAR(12),
	sname CHAR(16),
	sex CHAR(2),
	birthday DATE,
	nationality CHAR(16),
	native CHAR,
	political CHAR(12),
	district CHAR(12),
	studentSource VARCHAR(24),
	enterYear DATE,
	schoolYear CHAR(12),
	class CHAR(24),
	majorNo	CHAR(4)
);
#设置主键
ALTER TABLE `Students` ADD PRIMARY KEY ( `sno` );

#添加约束
ALTER TABLE Students
ADD CHECK (sno REGEXP '[2][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
AND sno NOT LIKE '%00');
ALTER TABLE Students
ADD CHECK (sex in ('男','女'));

#创建Course表
CREATE TABLE Course
(
	schoolYear CHAR(2) NULL,
	semester TINYINT NOT NULL,
	courseNo CHAR(6) NOT NULL,
	courseName VARCHAR(50) NOT NULL,
	credit NUMERIC(3,1) NOT NULL,
	creditHourse TINYINT NOT NULL,
	courseType1	CHAR(16) NOT NULL,
	courseType2	CHAR(16) NULL,
	cegmentType CHAR(16) NULL,
	examineWay CHAR(16) NOT NULL
);
#设置主键
ALTER TABLE Course ADD PRIMARY KEY (schoolYear,semester,courseNo);

#增加约束
#学年取值范围是[1，2，3，4]
#学期取值范围是[1，2，3，4，5，6，7，8]
#课程代码为6位数字字符且不能为000000
#学分取值范围是[ 0.5，1，1.5，2，2.5，3，3.5，4，4.5，5]
ALTER TABLE Course
ADD CHECK (schoolYear >= 1 AND schoolYear <= 4);
ALTER TABLE Course
ADD CHECK (semester >= 1 AND semester <= 8);
ALTER TABLE Course
ADD CHECK (courseNo REGEXP '[0-9][0-9][0-9][0-9][0-9][0-9]' 
AND courseNo NOT LIKE'000000');
ALTER TABLE Course
ADD CHECK (credit in ('0.5','1','1.5','2','2.5','3','3.5','4','4.5','5'));

#创建SelectCourse表
CREATE TABLE SelectCourse
(
	sno CHAR(12) NOT NULL,
	schoolYear CHAR(12) NOT NULL,
	semester TINYINT NOT NULL,
	courseNo CHAR(6) NOT NULL,
	score NUMERIC(6,2) NULL,
	CONSTRAINT SelectCoursePK PRIMARY KEY (sno,schoolYear,semester,courseNo)
);

#增加check约束
#综合成绩为百分制成绩
ALTER TABLE SelectCourse
ADD CHECK (score >= 0 AND score <= 100);

(3)用SQL语句把数据表Students、Course和SelectCourse的schoolYear字段数据类型改为TINYINT，取值范围为（1,2,3,4）。把数据表Students的native字段类型改为char(24)

SQL语句如下：

#修改字段'native'和'schoolYear'类型
ALTER TABLE Students MODIFY COLUMN native CHAR(24);
ALTER TABLE Students MODIFY COLUMN schoolYear TINYINT;
#修改字段'schoolYear'
ALTER TABLE Course MODIFY COLUMN schoolYear TINYINT;
#修改字段'schoolYear'
ALTER TABLE SelectCourse MODIFY COLUMN schoolYear TINYINT;

(4)用SQL的导入导出工具，将已经整理好的对应Excel表的数据全部导入到对应的数据库表中

结果截图如下：

顺序：Major、Students、Course、SelectCourse

(5)用SQL语句对Student表、SelectCourse表增加外键

• Student表的majorNo属性参照表Major表中的majorNo属性。
• SelectCourse表的SNo属性参照表Student表中的SNo属性。
• SelectCourse表的courseNo属性参照表Course表中的courseNo属性。
• SelectCourse表的schoolYear属性参照表Course表中的schoolYear属性。
• SelectCourse表的semester属性参照表Course表中的semester属性。

SQL语句如下：

#添加外键约束
#Student表的majorNo属性参照表Major表中的majorNo属性
ALTER TABLE Students
ADD FOREIGN KEY (majorNo)
REFERENCES Major(majorNo);

#添加外键约束
#SelectCourse表的SNo属性参照表Student表中的SNo属性
#SelectCourse表的courseNo属性参照表Course表中的courseNo属性
#SelectCourse表的schoolYear属性参照表Course表中的schoolYear属性
#SelectCourse表的semester属性参照表Course表中的semester属性
ALTER TABLE SelectCourse
ADD FOREIGN KEY (sno)
REFERENCES Students(sno);

ALTER TABLE SelectCourse
ADD FOREIGN KEY (schoolYear,semester,courseNo) 
REFERENCES Course(schoolYear,semester,courseNo);

2. 使用DML相应的语句完成数据更新操作

(1)用SQL语句将数据表Student、Course和SelectCourse的schoolYear字段值改为3。

SQL语句如下：

#删除外键约束
ALTER TABLE SelectCourse DROP FOREIGN KEY selectcourse_ibfk_2;
#修改字段值
UPDATE Students SET schoolYear = 3; 
UPDATE Course SET schoolYear = 3; 
UPDATE SelectCourse SET schoolYear = 3; 
#添加外键约束
ALTER TABLE SelectCourse
ADD FOREIGN KEY (schoolYear,semester,courseNo) 
REFERENCES Course(schoolYear,semester,courseNo);

(2)．用SQL语句，在Student表中增加一条记录，记录的各属性取值对应本人的实际情况。

SQL语句如下：

#插入xx专业
INSERT INTO Major VALUES('3000','083900','xx专业','xx Major',4,'x科','x学','10','xx学院');
#插入本人信息
INSERT INTO Students VALUES ('200000000001','xxx','男','1900-01-01','汉族','中国','群众','xxx校区','中国','1918-09-01',2,'xx1班','3000');

(3)用SQL语句，删除Student表中新增的对应于本人的记录

SQL语句如下：

DELETE FROM Students
WHERE sno='200000000001';

(4)用SQL语句，完成对以下表的数据删除操作

• 删除Student表中记录的majorNo值不在Major表中出现的相应记录
• 删除SelectCourse表中记录的courseNo值不在Course表中出现的相应记录
• 删除SelectCourse表中记录的sno值不在Student表中出现的相应记录

SQL语句如下：

#删除Students表中majorNo不存在Major的数据
DELETE 
FROM Students
WHERE majorNo NOT IN (SELECT majorNo FROM Major);

#删除SelectCourse表中sno不存在Students的数据
DELETE 
FROM SelectCourse
WHERE sno NOT IN (SELECT sno FROM Students);

#删除SelectCourse表中courseNo不存在Students的数据
DELETE 
FROM SelectCourse
WHERE courseNo NOT IN (SELECT courseNo FROM Course);

语句如下：

#删除Students表中majorNo不存在Major的数据
DELETE 
FROM Students
WHERE majorNo NOT IN (SELECT majorNo FROM Major);

#删除SelectCourse表中sno不存在Students的数据
DELETE 
FROM SelectCourse
WHERE sno NOT IN (SELECT sno FROM Students);

#删除SelectCourse表中courseNo不存在Students的数据
DELETE 
FROM SelectCourse
WHERE courseNo NOT IN (SELECT courseNo FROM Course);