数据库《实验一 SQL Server数据库及对象的设计》

实验内容：

实验问题描述：

某学院有若干专业，每个专业有若干学生；学院每年每学期都开设有若干门课程；每门课程有多个学生选修，每个学生每学期可以同时选修多门课程，每个学生对于同一门课程可以多次选修，但每学期只能选修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语句如下：

--创造数据库AB
CREATE DATABASE AB
ON    -- 定义第一个逻辑设备(默认为主逻辑设备)及其数据文件
      (  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语句如下：

--使用数据库lingxue
USE AB;

--创建Major表
CREATE TABLE Major
(
	majorNo	CHAR(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, --班级
	majorNo	CHAR(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, --学时
	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), --添加主键
);

--创建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 Students
select * from Course
select * 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 
SET
	schoolYear = 3; 

--添加外键约束
ALTER TABLE SelectCourse
ADD CONSTRAINT SelectCourseFK FOREIGN KEY (schoolYear,semester,courseNo) REFERENCES Course(schoolYear,semester,courseNo);

6． 用SQL语句，删除Student表中新增的对应于本人的记录。

SQL语句如下：

-- 删除本人信息
DELETE FROM Students WHERE sno = 'sno';