实验问题描述：
某学院有若干专业，每个专业有若干学生；学院每年每学期都开设有若干门课程；每门课程有多个学生选修，每个学生每学期可以同时选修多门课程，每个学生对于同一门课程可以多次选修，但每学期只能选修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．用SQL语句（create database）创建数据库，数据库的名字为每位同学自己的后三位学号和拼音姓名，例如，xiaojie101。
SQL语句如下：
CREATE DATABASE xiaojie101
ON -- 定义数据逻辑设备(默认为主逻辑设备)及其数据文件
( NAME=xiaojie101DB, -- 数据文件的逻辑文件名(即别名)
FILENAME='e:\xiaojieDB\xiaojieDB.mdf', -- 物理(磁盘)文件名
SIZE=3,
MAXSIZE=10,
FILEGROWTH=1 )
LOG ON -- 定义日志逻辑设备及其日志文件
( NAME=ScoreLog, -- 日志文件的逻辑文件名(即别名)
FILENAME='e:\xiaojieDB\xiaojieLog.ldf', -- 日志(磁盘)文件名
SIZE=3,
MAXSIZE=10,
FILEGROWTH=1 )
2．在所创建的数据库下，用SQL语句（create table）创建所有的表、对应的完整性约束。
SQL语句如下：
CREATE TABLE Major( -- Major 专业设置
majorNo char(4) NOT NULL , --专业代码
CHECK (majorNo like '[0-9][0-9][0-9][0-9]' AND majorNO!='0000'), --专业代码约束
GBMajorNo char(6) NOT NULL , --国家专业代码
CHECK (GBMajorNo like '[0-9][0-9][0-9][0-9][0-9][0-9]' AND GBMajorNO!='000000'), --国家专业代码约束
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 , --院系代码
CHECK (departmentNo like '[0-9][0-9]' AND Department!='00'), --院系代码约束
department varchar(40) NOT NULL, --院系名称
CONSTRAINT MajorPK PRIMARY KEY(majorNO) --主键约束
)
CREATE TABLE Student( -- Student 学生情况
sno char(12) NOT NULL , --学生学号
CHECK (sno like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' AND sno like '2%'
AND sno!='----------00'), --学号约束
sname char(16) NOT NULL, --学生姓名
sex char(2) NOT NULL default '男', --性别
CHECK (sex in ('男','女')), --性别约束
birthday Date NOT NULL, --出生日期
nationality char(16) NULL default '汉族', --民族
native char(24) NULL default '东莞市', --籍贯
political char(12) NULL default '共青团员', --政治面貌
district char(12) NOT NULL default '松山湖校区', --校区名称
studentSource varchar(24) NULL, --生源地
enterYear Date NOT NULL, --入学日期
schoolYear tinyint NOT NULL, --年级
class char(24) NOT NULL, --班级
majorNo char(4) NOT NULL, --专业代码
CONSTRAINT snoPK PRIMARY KEY (sno), --主键约束
CONSTRAINT majorFK FOREIGN KEY (majorNo) --外键约束
REFERENCES Major(majorNo),
)
CREATE TABLE Course( -- Course 课程设置
schoolYear tinyint NOT NULL , --学年
CHECK (schoolYear>=1 AND schoolYear<=4), --学年约束
semester tinyint NOT NULL, --学期
CHECK (semester>=1 AND semester<=8), --学期约束
courseNo char(6) NOT NULL, --课程代码
CHECK (courseNo like '[0-9][0-9][0-9][0-9][0-9][0-9]' AND courseNo!='000000'), --课程代号约束
courseName char(50) NOT NULL, --课程名称
credit numeric(3,1) NOT NULL, --学分
CHECK (credit in (0.5,1,1.5,2,2.5,3,3.5,4,4.5,5)), --学分约束
creditHourse tinyint NOT NULL, --学时
courseType1 char(16) NOT NULL, --课程类别
courseType2 char(16) NULL, --课程性质
cegmentType char(16) NULL, --环节类别
examineWay char(16) NOT NULL, --考核方式
CONSTRAINT CoursePK PRIMARY KEY (schoolYear,semester,courseNo) --主键约束
)
CREATE TABLE SelectCourse( -- SelectCourse 学生选课
sno char(12) NOT NULL , --学号
schoolYear tinyint NOT NULL , --学年
semester tinyint NOT NULL, --学期
courseNo char(6) NOT NULL, --课程代码
score numeric(6,2) NULL, --综合成绩
CHECK (score>=0 AND score<=100), --成绩约束
CONSTRAINT SelectCoursePK PRIMARY KEY (sno,schoolYear,semester,courseNo), --主键约束
CONSTRAINT snoFK FOREIGN KEY (sno) --学生外键约束
REFERENCES Student(sno),
CONSTRAINT schoolYearFK FOREIGN KEY (schoolYear,semester,courseNo) --课程外键约束
REFERENCES Course(schoolYear,semester,courseNo)
)
3．用SQL的导入导出工具，将已经整理好的对应Excel表的数据全部导入到对应的数据库表中
4. 用SQL语句，在Student表中增加一条记录，记录的各属性取值对应本人的实际情况。
SQL语句如下：
INSERT INTO Student VALUES('201700000001','肖捷','男','1966-11-18','汉族','邵阳市','民进会员',
'松山湖校区','邵阳','1985-9-1',4,'1985级计算机应用班','0402')
5. 用SQL语句，将数据表Student、Course和SelectCourse的schoolYear字段值改为3。
SQL语句如下：
UPDATE Student SET schoolYear=3
--特别说明：修改具有外键约束关系表的相关属性值，必须先删除外键约束，否则出错！
ALTER TABLE SelectCourse DROP CONSTRAINT schoolYearFK --先删除外键约束规则
UPDATE Course SET schoolYear=3 --否则不能修改！
UPDATE SelectCourse SET schoolYear=3 --否则不能修改！
ALTER TABLE SelectCourse ADD CONSTRAINT schoolYearFK --再重新增加外键约束
FOREIGN KEY(schoolYear,semester,CourseNo)
REFERENCES Course(schoolYear,semester,CourseNo)
6. 用SQL语句，删除Student表中新增的对应于本人的记录。
SQL语句如下：
DELETE FROM Student WHERE sno='201700000001'
方案二：选择原始有问题的数据和原始数据字典要求（都是黑色字体）：
1. 使用DDL相应的语句完成数据定义操作
(1). 用SQL语句（create database）创建数据库，数据库的名字为每位同学自己的拼音姓名和三位学号，例如，xiaojie001。
SQL语句如下：
CREATE DATABASE xiaojie000
ON -- 定义数据逻辑设备(默认为主逻辑设备)及其数据文件
( NAME=xiaojie000DB, -- 数据文件的逻辑文件名(即别名)
FILENAME='e:\xiaojieDB\xiaojieDB000.mdf', -- 物理(磁盘)文件名
SIZE=3,
MAXSIZE=10,
FILEGROWTH=1 )
LOG ON -- 定义日志逻辑设备及其日志文件
( NAME=ScoreLog, -- 日志文件的逻辑文件名(即别名)
FILENAME='e:\xiaojieDB\xiaojieLog000.ldf', -- 日志(磁盘)文件名
SIZE=3,
MAXSIZE=10,
FILEGROWTH=1 )
(2). 在所创建的数据库下，用SQL语句（create table）创建所有的表，只建立主键，先不建立外键。
SQL语句如下：
CREATE TABLE Major( -- Major 专业设置
majorNo char(4) NOT NULL , --专业代码
CHECK (majorNo like '[0-9][0-9][0-9][0-9]' AND majorNO!='0000'), --专业代码约束
GBMajorNo char(6) NOT NULL , --国家专业代码
CHECK (GBMajorNo like '[0-9][0-9][0-9][0-9][0-9][0-9]'
AND GBMajorNO!='000000'), --国家专业代码约束
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 , --院系代码
CHECK (departmentNo like '[0-9][0-9]' AND Department!='00'), --院系代码约束
department varchar(40) NOT NULL, --院系名称
CONSTRAINT MajorPK PRIMARY KEY(majorNO) --主键约束
)
CREATE TABLE Student( -- Student 学生情况
sno char(12) NOT NULL , --学生学号
CHECK (sno like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' AND sno like '2%'
AND sno!='----------00'), --学号约束
sname char(16) NOT NULL, --学生姓名
sex char(2) NOT NULL default '男', --性别
CHECK (sex in ('男','女')), --性别约束
birthday Date NOT NULL, --出生日期
nationality char(16) NULL default '汉族', --民族
native char NULL default '东莞市', --籍贯
political char(12) NULL default '共青团员', --政治面貌
district char(12) NOT NULL default '松山湖校区', --校区名称
studentSource varchar(24) NULL, --生源地
enterYear Date NOT NULL, --入学日期
schoolYear char(12) NOT NULL, --年级
class char(24) NOT NULL, --班级
majorNo char(4) NOT NULL, --专业代码
CONSTRAINT snoPK PRIMARY KEY (sno), --主键约束
)
CREATE TABLE Course( -- Course 课程设置
schoolYear char(12) NOT NULL , --学年
CHECK (schoolYear>='1' AND schoolYear<='4'), --学年约束
semester tinyint NOT NULL, --学期
CHECK (semester>=1 AND semester<=8), --学期约束
courseNo char(6) NOT NULL, --课程代码
CHECK (courseNo like '[0-9][0-9][0-9][0-9][0-9][0-9]' AND courseNo!='000000'), --课程代号约束
courseName char(50) NOT NULL, --课程名称
credit numeric(3,1) NOT NULL, --学分
CHECK (credit in (0.5,1,1.5,2,2.5,3,3.5,4,4.5,5)), --学分约束
creditHourse tinyint NOT NULL, --学时
courseType1 char(16) NOT NULL, --课程类别
courseType2 char(16) NULL, --课程性质
cegmentType char(16) NULL, --环节类别
examineWay char(16) NOT NULL, --考核方式
CONSTRAINT CoursePK PRIMARY KEY (schoolYear,semester,courseNo) --主键约束
)
CREATE TABLE SelectCourse( -- 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, --综合成绩
CHECK (score>=0 AND score<=100), --成绩约束
CONSTRAINT SelectCoursePK PRIMARY KEY (sno,schoolYear,semester,courseNo), --主键约束
)
(3)．用SQL语句把数据表Students、Course和SelectCourse的schoolYear字段数据类型改为TINYINT，取值范围为（1,2,3,4）。把数据表Students的native字段类型改为char(24)。
SQL语句如下：
A. 修改Course的schoolYear字段
ALTER TABLE Course DROP CONSTRAINT CoursePK --先删除主键约束
ALTER TABLE Course DROP CONSTRAINT CK__Course__schoolYe__0DAF0CB0 --再删除检查约束
ALTER TABLE Course ALTER COLUMN schoolYear TINYINT NOT NULL --修改数据类型
ALTER TABLE Course ADD CONSTRAINT CoursePK
PRIMARY KEY (schoolYear,semester,courseNo) --增加主键约束
ALTER TABLE Course ADD CONSTRAINT CK__Course__schoolYear
CHECK (schoolYear>=1 AND schoolYear<=4) --增加检查约束
B. 修改Student的schoolYear字段与native字段
ALTER TABLE Student ALTER COLUMN schoolYear TINYINT NOT NULL --修改数据类型
ALTER TABLE Student ALTER COLUMN native char(24) NULL --修改数据类型
ALTER TABLE Student ADD CONSTRAINT CK__Student__schoolYear
CHECK (schoolYear>=1 AND schoolYear<=4) --增加检查约束
C. 修改SelectCourse的schoolYear字段
ALTER TABLE SelectCourse DROP CONSTRAINT SelectCoursePK --先删除主键约束
ALTER TABLE SelectCourse ALTER COLUMN schoolYear TINYINT NOT NULL --修改数据类型
ALTER TABLE SelectCourse ADD CONSTRAINT SelectCoursePK
PRIMARY KEY (sno,schoolYear,semester,courseNo) --增加主键约束
ALTER TABLE SelectCourse ADD CONSTRAINT CK_SelectCourse_schoolYear
CHECK (schoolYear>=1 AND schoolYear<=4) --增加检查约束
(4)．用SQL的导入导出工具，将已经整理好的对应Excel表的数据全部导入到对应的数据库表中。
结果截图如下：
A. 导入Course表
由于数据源文件Cuorse3.xlsx中的schoolYear的值为2017，Course表中的约束条件，先全部修改为1，然后再导入。
B. 导入Major表
C. 导入Student表
由于数据源文件Student2.xlsx中的schoolYear的值为2007，Course表中的约束条件，先全部修改为1，然后再导入。
D. 导入SelectCourse表
由于数据源文件SelectCourse.xlsx中的schoolYear的值为2017，Course表中的约束条件，先全部修改为1，然后再导入。
(5)．用SQL语句对Student表、SelectCourse表增加外键
A. Student表的majorNo属性参照表Major表中的majorNo属性。
B. SelectCourse表的SNo属性参照表Student表中的SNo属性。
C. SelectCourse表的courseNo属性参照表Course表中的courseNo属性。
D. SelectCourse表的schoolYear属性参照表Course表中的schoolYear属性。
E. SelectCourse表的semester属性参照表Course表中的semester属性。
SQL语句如下：
--A. Student表的majorNo属性参照表Major表中的majorNo属性。
--先删除Student表中MajorNo未在Major表中出现的元组
DELETE FROM Student
WHERE MajorNo NOT IN
( SELECT majorNo
FROM Major)
--再建立Student表的MajorNo属性参照表Major表中的MajorNo属性的外键约束
ALTER TABLE Student ADD CONSTRAINT StudentFK FOREIGN KEY(MajorNo)
REFERENCES Major(MajorNo)
--B. SelectCourse表的SNo属性参照表Student表中的SNo属性。
--先删除SelectCourse表中SNo未在Student表中出现的元组
DELETE FROM SelectCourse
WHERE sno NOT IN
( SELECT SNo
FROM Student)
--再建立SelectCourse表的SNo属性参照表Student表中的SNo属性
ALTER TABLE SelectCourse ADD CONSTRAINT SelectCourseFK FOREIGN KEY(SNo) --增加外键约束
REFERENCES Student(SNo)
--特别说明：建立外键约束关系，必须先删除违反外键约束的数据记录才能建立，否则出错！
C. SelectCourse表的courseNo属性参照表Course表中的courseNo属性。
D. SelectCourse表的schoolYear属性参照表Course表中的schoolYear属性。
E. SelectCourse表的semester属性参照表Course表中的semester属性。
--先删除SelectCourse表中courseNo未在Course表中出现的元组
--先删除SelectCourse表中schoolYear未在Course表中出现的元组
--先删除SelectCourse表中semester未在Course表中出现的元组
DELETE FROM SelectCourse
WHERE CourseNo NOT IN
(
SELECT curseNo
FROM Course
)
DELETE FROM SelectCourse
WHERE schoolYear NOT IN
(
SELECT schoolYear
FROM Course
)
DELETE FROM SelectCourse
WHERE semester NOT IN
(
SELECT semester
FROM Course
)
--再建立SelectCourse表的schoolYear,semester,CourseNo属性参照表Course表中的schoolYear,semester,CourseNo属性
ALTER TABLE SelectCourse ADD CONSTRAINT SelectCourseFK1
FOREIGN KEY(schoolYear,semester,CourseNo) --增加外键约束
REFERENCES Course(schoolYear,semester,CourseNo)
2. 使用DML相应的语句完成数据更新操作
(1)．用SQL语句将数据表Student、Course和SelectCourse的schoolYear字段值改为3。
SQL语句如下：
UPDATE Student SET schoolYear=3
--特别说明：修改具有外键约束关系表的相关属性值，必须先删除外键约束，否则出错！
ALTER TABLE SelectCourse DROP CONSTRAINT SelectCourseFK1 --先删除外键约束规则
UPDATE Course SET schoolYear=3 --否则不能修改！
UPDATE SelectCourse SET schoolYear=3 --否则不能修改！
ALTER TABLE SelectCourse ADD CONSTRAINT SelectCourseFK1 --再重新增加外键约束
FOREIGN KEY(schoolYear,semester,CourseNo)
REFERENCES Course(schoolYear,semester,CourseNo)
(2)．用SQL语句，在Student表中增加一条记录，记录的各属性取值对应本人的实际情况。
SQL语句如下：
INSERT INTO Student VALUES('201700000001','肖捷','男','1966-11-18','汉族','邵阳市','民进会员',
'松山湖校区','邵阳','1985-9-1',4,'1985级计算机应用班','0402')
(3)．用SQL语句，删除Student表中新增的对应于本人的记录。
SQL语句如下：
DELETE FROM Student WHERE sno='201700000001'
(4)．选做题：用SQL语句，完成对以下表的数据删除操作。
A. 删除Student表中记录的majorNo值不在Major表中出现的相应记录。
B. 删除SelectCourse表中记录的courseNo值不在Course表中出现的相应记录。
C. 删除SelectCourse表中记录的sno值不在Student表中出现的相应记录。
SQL语句如下：
DELETE FROM Student
WHERE MajorNo NOT IN
( SELECT majorNo
FROM Major)
DELETE FROM SelectCourse
WHERE courseNo NOT IN
( SELECT courseNo
FROM Course)
DELETE FROM SelectCourse
WHERE sno NOT IN
( SELECT SNo
FROM Student)
2180
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
