实验一 SQL Server基本操作与数据定义语言（DDL）

实验一 SQL Server基本操作与数据定义语言（DDL）

实验目的：

1．掌握数据库服务器的启动、登录
2．掌握Microsoft SQL Server Management Studio的使用
3．掌握SQL SERVER的数据定义子语言

实验要求：

1．启动、登录SQL SERVER数据库服务器
2．使用SQL创建、修改和撤销数据库、基本表

实验环境及学时安排：

1．SQL SERVER2008
2．学时：2学时

实验内容：

1．启动SQL SERVER数据库服务器， 尝试分别使用Windows/SQL Server身份认证；
2．使用Management Studio创建基本表、视图、索引
3．使用SQL创建基本表、视图、索引

假设条件：
学校有若干个系，每个系由若干名教师和学生；每个教师可以担任若干门课程；每个学生可以同时选修多门课程。请设计某学校的教学管理的E-R模型，要求给出每个实体、联系的属性。
根据以上语义，我们可得到如下E-R图（属性未画出）：

将以上E-R图中的部分实体和联系转换为关系，得到下表：

数据字典

表名	中文名
Department	院系
Course	课程
SC	选课
Student	学生
Teacher	教师
Teaching	任课

院系 Department

字段名	数据类型	可空性	主键	外键	中文名
DepID	int	NOT	NULL	Yes	No
DName	nvarchar(20)	NOT NULL	No	No	院系名称
Location	nvarchar(50)	NULL	No	No	办公地点
Leader	char(8)	NULL	No	No	系主任编号

课程Course

字段名	数据类型	可空性	主键	外键	中文名
CNo	char(10)	NOT NULL	Yes	No	课程编号
CName	varchar(50)	NOT NULL	No	No	课程名称
Grade	samllint	NULL	No	No	学分
Term	smallint	NULL	No	No	开课学期
TNo	char(8)	NOT NULL	No	Yes	授课教师编号
DepID	int	NULL	No	Yes	开课院系编号

Grade取值范围是[1，1.5，2，2.5，3，3.5，4，4.5，5]
Term取值范围是[1，2，3，4，5，6，7，8]
TNo为外键，参照教师表中的TNo
DepID为外键，参照院系表中的DepID

选课 SC

字段名	数据类型	可空性	主键	外键	中文名
SNo	char(10)	NOT	NULL	Yes	Yes
CNo	char(10)	NOT NULL	Yes	Yes	课程编号
SDate	smalldatetime	NULL	No	No	选课日期
Score	decimal(4,1)	NULL	No	No	考试成绩

SNo参照Student表中的SNo
CNo参照Course表中的CNo
Score取值范围[0-100]，带一位小数，如80.5

学生 Student

字段名	数据类型	可空性	主键	外键	中文名
SNo	char(10)	NOT NULL	Yes	No	学号
SName	nchar(8)	NOT NULL	No	No	姓名
Sex	nchar(1)	NULL	No	No	性别
BirthDate	smalldatetime	NULL	No	No	出生日期
Class	int	NOT NULL	No	No	班级

Sex只能是‘男’或‘女’

教师Teacher

字段名	数据类型	可空性	主键	外键	中文名
TNo	char(8)	NOT NULL	Yes	No	教师编号
TName	nchar(4)	NOT NULL	No	No	教师姓名
Sex	nchar(1)	NULL	No	No	性别
Prof	nchar(5)	NULL	No	No	职称
Mobile	char(11)	NULL	No	No	手机号
DepID	Int	NOT NULL	No	Yes	所属院系ID

Sex只能是‘男’或‘女’
Prof只能是[‘助教’，‘讲师’，‘副高’，‘正高’]
Mobile只能是11位数字，且第一个数字为1
DepID参照Department的DepID

任课 Teaching

字段名	数据类型	可空性	主键	外键	中文名
TNo	char(8)	NOT NULL	Yes	Yes	教师编号
CNo	char(10)	NOT NULL	Yes	Yes	课程编号
Term	smallint	NOT NULL	Yes	NO	开课学期
Num	int	NULL	No	No	选修人数
Croom	varchar(15)	NULL	No	No	上课地点

TNo参照Teacher的TNo
CNo参照Course的CNo
Term取值范围是[1，2，3，4，5，6，7，8]
Num为不超过200的非负整数

问题：
1．使用Management Studio创建一个以自己拼音姓名命名的数据库；
2．用SQL语句（create database）创建数据库JiaoXue（语法见帮助文档，今后所有操作均在该数据库下完成。）
2．在JiaoXue数据库下，使用Management Studio创建Teacher和Department表；
3．在JiaoXue数据库下，用SQL语句创建其余表及其上的完整性约束；
4.．使用SQL语句先将Teaching表的Num属性改为smallint类型，然后删除Croom属性；
5．用SQL语句删除Teaching表的定义；

相关代码

Create database JiaoXue
on primary
(name    =  USERDBFile3,
filename ='C:\Program Files\Microsoft SQL Server\MSSQL\Data\USERDBFile3.mdf',
size=10MB,
maxsize=100MB,
filegrowth=10MB),
(name = USERDBFile4,
filename='C:\Program Files\Microsoft SQL Server\MSSQL\Data\USERDBFile4.ndf',
size =10MB,
maxsize=100MB,
filegrowth=10%)
log on
(name =USERDBLog2,
filename='C:\Program Files\Microsoft SQL Server\MSSQL\Data\USERDBLog.2df',
size =10MB,
maxsize=50MB,
filegrowth=5MB)
go 



--切换到自己的数据库下
use JiaoXue
go 

--创建院系表
create table  department
(DepID int NOT NULL primary key,
DName nvarchar(20) NOT NULL,
Location nvarchar(50),
Leader char(8))
go

--创建教师表
create table Teacher
(Tno char(8) NOT NULL primary key,
TName nchar(4) NOT NULL,
Sex nchar(1)constraint CK_sex check(sex ='男' or sex='女') ,
Prof nchar(5)constraint CK_prof check(prof='助教' or prof='讲师'or prof='副高'or prof='正高'),
Mobile char(11)constraint CK_mobile check (mobile LIKE '1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
DepID int NOT NULL
constraint DepID_FK references department(DepID)
ON DELETE NO ACTION
ON UPDATE CASCADE,)


go

--创建学生表
create table student
(SNo char(10)  NOT NULL primary key,
SName nchar(8) NOT NULL,
Sex nchar(1)constraint C_sex check(sex ='男' or sex='女') ,
BirthDate smalldatetime,
Class int NOT NULL)
go

--创建课程表
create table Course
(CNo char(10) NOT NULL primary key,
CName varchar(50) NOT NULL,
Grade smallint constraint CK_grade check(grade =1 or grade =1.5 or grade =2 or grade =2.5 or grade =3 or grade =3.5 or grade =4 or grade =4.5 or grade =5) ,
Term smallint constraint CK_term check(term =1 or term =2 or term=3 or term=4 or term =5 or term =6 or term=7 or term=8),
DepID int,
TNo char(8) NOT NULL,
constraint Tno_FK foreign key(TNo) references teacher(TNo)
ON DELETE NO ACTION
ON UPDATE CASCADE,
constraint DepID_FK_C  foreign key(DepID) references department(DepID)
)
go

--创建选课表
create table sc
(SNo char(10) NOT NULL,
CNo char(10) NOT NULL,
SDate smalldatetime,
Score Decimal(4,1) check (score >0 and score <100),
constraint Cno_FK foreign key(CNo)
references Course(CNo),
constraint Sno_FK foreign key(SNo)
references Student(SNo)
ON delete no action
on update cascade,
constraint SC_PK primary key(SNo,CNo))
go

--创建任课表
create table teaching
(TNo char(8) NOT NULL foreign key references teacher(tno),
CNo char(10) NOT NULL foreign key references course(cno),
term smallint NOT NULL constraint C_term check(term =1 or term =2 or term=3 or term=4 or term =5 or term =6 or term=7 or term=8) ,
Num int constraint C_num check (num >=0 and num <200),
Croom varchar(15),
constraint Teaching_PK primary key(TNo,CNo,Term))


--修改任课表，Num属性改为smallint类型，并删除Croom属性

ALTER TABLE  teaching
drop constraint C_num

ALTER TABLE  teaching
alter column num smallint

ALTER TABLE  teaching
add constraint C_num check (num >=0 and num <200)



ALTER TABLE  teaching
drop column Croom