实验内容：
实验问题描述：
某学院有若干专业，每个专业有若干学生；学院每年每学期都开设有若干门课程；每门课程有多个学生选修，每个学生每学期可以同时选修多门课程，每个学生对于同一门课程可以多次选修，但每学期只能选修1次，课程选修成绩以综合成绩记录。请设计某学院简单的教学管理系统的E-R模型，要求给出每个实体、联系的属性。根据以上语义，我们可得了上图1如示的教学管理系统E-R图（属性未画出）。
根据学院提供的学生选课及其他的部分数据，将上图1所示的E-R图中的部分实体和联系转换为关系，得到如下4个关系模式和相应的数据字典描述。
A、关系模式
⑴ 专业设置：包括专业编号、国家专业编号、专业名称、专业英文名称、学制、培养层次、授予学位、院系编号、院系名称；
⑵ 学生情况：包括学号、姓名、性别、出生日期、民族、籍贯、政治面貌、校区、生源地、入学日期、年级、班级名称、专业编号；
⑶ 课程设置：包括学年、学期、课程编号、课程名称、学分、学时数、课程类别1、课程类别2、环节类别、考核方式；
⑷ 学生选课：包括学号、课程编号、学年、学期、综合成绩。
B**、数据字典**
约束条件：
专业代码为4位数字字符且不能为0000
国家专业编号为6位数字字符且不能为000000
院系代码为2位数字字符且不能为00
约束条件：
学生学号为12位数字字符，第1位为2，其它位为数字且最后2位不能是00
性别只能是‘男’或‘女’；
专业代码参照表Major。
约束条件：
学年取值范围是[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]
学年、学期和课程代码一起构成主码。
约束条件：
综合成绩为百分制成绩
学号、学年、学期和课程代码一起构成主码。
学号参照表student
学年、学期和课程代码一起参照表course。
设计任务：
1．用SQL语句（create database）创建数据库，数据库的名字为每位同学自己的后三位学号和拼音姓名，例如，xiaojie101。
SQL语句如下：
--创造数据库ABCREATE DATABASE ABON -- 定义第一个逻辑设备(默认为主逻辑设备)及其数据文件( NAME=ABDB,-- 数据文件的逻辑文件名(即别名)FILENAME='路径\ABDB.mdf', -- 物理(磁盘)文件名 SIZE=2, MAXSIZE=10, FILEGROWTH=1 ) LOG ON -- 定义日志逻辑设备及其日志文件( NAME=ABLog,-- 日志文件的逻辑文件名(即别名)FILENAME='路径\ABLog.ldf', -- 日志(磁盘)文件名 SIZE=1, MAXSIZE=5, FILEGROWTH=1 );
2．在所创建的数据库下，用SQL语句（create table）创建所有的表、对应的完整性约束。
SQL语句如下：
--使用数据库lingxueUSE AB;--创建Major表CREATE TABLE Major(majorNoCHAR(4) CONSTRAINT MajorPK PRIMARY KEY CHECK (majorNo LIKE '[0-9][0-9][0-9][0-9]' AND majorNo NOT LIKE '0000'),--专业代码 GBMajorNo CHAR(6) NOT NULL CHECK (GBMajorNo LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]' AND GBMajorNo NOT LIKE '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 departmentNo NOT LIKE '00'),--院系代码department VARCHAR(40) NOT NULL, --院系名称);--#创建Students表CREATE TABLE Students(sno CHAR(12) NOT NULL CHECK (sno LIKE '[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'), --学生学号sname CHAR(16) NOT NULL, --学生姓名sex CHAR(2) NOT NULL DEFAULT '男' CHECK (sex in ('男','女')), --性别birthday DATE NOT NULL, --出生日期nationality CHAR(16) DEFAULT '汉族' , --民族native CHAR(24) NULL DEFAULT '东莞市', --籍贯political CHAR(12) NULL DEFAULT '共青团员', --政治面貌district CHAR(12) NOT NULL DEFAULT '松山湖校区', --院系代码studentSource VARCHAR(24), --生源地enterYear DATE NOT NULL, --入学日期schoolYear TINYINT NOT NULL, --年级class CHAR(24) NOT NULL, --班级majorNoCHAR(4) NOT NULL CHECK(majorNo LIKE '[0-9][0-9][0-9][0-9]' AND majorNo NOT LIKE '0000'),--专业代码/*添加约束*/CONSTRAINT StudentsPK PRIMARY KEY (sno), --添加主键CONSTRAINT StudentsMajorFK FOREIGN KEY(majorNo) REFERENCES Major(majorNo),--添加外键);--创建Course表CREATE TABLE 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 NOT LIKE '000000'), --课程代码courseName VARCHAR(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, --学时courseType1CHAR(16) NOT NULL, --课程类别courseType2CHAR(16) NULL, --课程性质cegmentType CHAR(16) NULL, --环节类别examineWay CHAR(16) NOT NULL, --考核方式CONSTRAINT CoursePK PRIMARY KEY (schoolYear,semester,courseNo), --添加主键);--创建SelectCourse表CREATE TABLE SelectCourse(sno CHAR(12) NOT NULL CHECK (sno LIKE '[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'), --学号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 NOT LIKE '000000'), --课程代码score NUMERIC(6,2) NULL CHECK(score >= 0 AND score <= 100), --综合成绩CONSTRAINT SelectCoursePK PRIMARY KEY (sno,schoolYear,semester,courseNo), --添加主键CONSTRAINT SelectCourseSnoFK FOREIGN KEY(sno) REFERENCES Students(sno),--添加外键CONSTRAINT SelectCourseFK FOREIGN KEY(schoolYear,semester,courseNo) REFERENCES Course(schoolYear,semester,courseNo),--添加外键)
3．用SQL的导入导出工具，将已经整理好的对应Excel表的数据全部导入到对应的数据库表中。
结果截图如下：
--查看插入表的数据select * from Major select * from Studentsselect * from Courseselect * from SelectCourse
Major
Student
Course
SelectCourse
4．用SQL语句，在Student表中增加一条记录，记录的各属性取值对应本人的实际情况。
SQL语句如下：
--插入xx专业,如果major表没有自己的专业INSERT INTO Major VALUES('专业代码 ','国家专业编号 ','专业名称','英文名称','学制' ,'培养层次 ','授予学位 ','院系代码','院系名称 ');--插入本人信息INSERT INTO Students VALUES ('学号','姓名','性别','出生日期 ','汉族','籍贯',' 政治面貌','院系代码','生源地','入学日期','年级','班级','专业代码');
5．用SQL语句，将数据表Student、Course和SelectCourse的schoolYear字段值改为3。
SQL语句如下：
--删除外键约束ALTER TABLE SelectCourse DROP CONSTRAINT SelectCourseFK;--修改字段值UPDATE Students SET schoolYear = 3; UPDATE Course SET schoolYear = 3; UPDATE SelectCourse SETschoolYear = 3; --添加外键约束ALTER TABLE SelectCourseADD CONSTRAINT SelectCourseFK FOREIGN KEY (schoolYear,semester,courseNo) REFERENCES Course(schoolYear,semester,courseNo);
6．用SQL语句，删除Student表中新增的对应于本人的记录。
SQL语句如下：
-- 删除本人信息DELETE FROM Students WHERE sno = 'sno';
