数据库实验1

实验一 SQL Server的数据定义
实验目的：
1．掌握数据库服务器的启动、登录
2．掌握Microsoft SQL Server Management Studio的使用
3．掌握SQL SERVER的数据定义子语言
4．掌握使用SQL语句(Query Analyzer)创建、修改和删除基本表、索引
实验要求：
1．启动、登录SQL SERVER数据库服务器
2．使用SQL创建、修改和撤销数据库、基本表
实验环境及学时安排：
1．SQL SERVER2008
2．学时：4学时
实验内容：
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 varchar(20) NOT NULL No No 院系名称
Location varchar(50) NULL No No 办公地点
Leader char(8) NULL No No 系主任姓名

教师Teacher
字段名 数据类型 是否可以为空 Is PK Is FK 中文名
TNo char(8) NOT NULL Yes No 教师编号
TName char(4) NOT NULL No No 教师姓名
Sex char(2) NULL No No 性别
Prof char(5) NULL No No 职称
Mobile char(11) NULL No No 手机号
DepID Int NOT NULL No Yes 所属院系ID
Sex只能是‘男’或‘女’
Prof只能是[‘助教’，‘讲师’，‘副高’，‘正高’]
Mobile只能是11位数字，且第一个数字为1
TName具有唯一性
DepID参照Department的DepID

课程Course
字段名 数据类型 是否可以为空 Is PK Is FK 中文名
CNo char(10) NOT NULL Yes No 课程编号
CName varchar(50) NOT NULL No No 课程名称
Grade decimal(2,1) NULL No No 学分
Term smallint NULL No No 开课学期
TNo char(8) NULL No Yes 授课教师编号
DepID int NOT 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

学生 Student
字段名 数据类型 是否可以为空 Is PK Is FK 中文名
SNo char(10) NOT NULL Yes No 学号
SName char(8) NOT NULL No No 姓名
Sex char(2) NULL No No 性别
BirthDate smalldatetime NULL No No 出生日期
Class int NOT NULL No No 班级
Email varchar(100) NULL NO NO 邮箱
Sex只能是‘男’或‘女’
邮箱名中一定要含有‘@’符号

选课 SC
字段名 数据类型 是否可以为空 Is PK Is FK 中文名
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
教学 Teaching
字段名 数据类型 可空性 主键 外键 中文名
TNo char(8) NOT NULL Yes Yes 教师编号
CNo char(10) NOT NULL Yes Yes 课程编号
Term smallint NOT NULL Yes NO 开课学期
Num smallint NULL No No 选修人数
room 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 check (num >=0 and num <200),
Croom varchar(15),
constraint Teaching_PK primary key(TNo,CNo,Term))


--修改任课表，Num属性改为smallint类型，并删除Croom属性
ALTER TABLE  teaching
alter column num smallint

ALTER TABLE  teaching
drop column Croom