实验目的:
1.掌握数据库服务器的启动、登录
2.掌握Microsoft SQL Server Management Studio的使用
3.掌握SQL SERVER的数据定义子语言
4.掌握使用SQL语句(Query Analyzer)创建、修改和删除基本表、索引
5.掌握对正确或者错误数据导入数据库表
实验要求:
1.启动、登录SQL SERVER数据库服务器
2.使用SQL创建、修改和撤销数据库、基本表
实验环境及学时安排:
1.SQL SERVER2008
2.学时:4学时
实验内容:
1.启动SQL SERVER数据库服务器, 尝试分别使用Windows/SQL Server身份认证;
2.使用Management Studio创建基本表、视图、索引
3.使用SQL创建基本表、视图、索引
4. 掌握对正确或者错误数据导入数据库表
假设条件:
学校有若干个院系,每个院系由若干名教师和学生;每个教师可以担任若干门课程;每个学生可以同时选修多门课程。请设计某学校的教学管理的E-R模型,要求给出每个实体、联系的属性。
根据以上语义,设计如下E-R图(属性未画出)、6个关系模式及相当的数据字典描述。
数据字典
表 | |
表名 | 中文名 |
Department | 院系 |
Course | 课程 |
SC | 选课 |
Student | 学生 |
Teacher | 教师 |
Teaching | 任课 |
院系 Department | |||||
字段名 | 数据类型 | 是否可以为空 | Is PK | Is FK | 中文名 |
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位数字
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 | 班级 |
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 | int | NULL | No | No | 选修人数 |
room | varchar(15) | 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;
SQL语句如下:
Create database JiaoXue
3.在JiaoXue数据库中使用Management Studio创建Teacher和Department表;
结果截图如下:
//创建department表
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)
4.在JiaoXue数据库中使用SQL语句创建其余表及其上的完整性约束;
SQL语句如下:
create table Course
(
CNo char(10) NOT NULL primary key,--课程编号
CName varchar(50) NOT NULL,--课程名称
Grade decimal(2,1) NULL 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 NULL check(term =1 or term =2 or term=3 or term=4 or term =5 or term =6 or term=7 or term=8),--开课日期
TNo char(8) NOT NULL,--授课教室编号
DepID int NOT NULL,--开课院系编号
constraint Tno_FK foreign key(TNo) references Teacher(TNo),
constraint DepID_FK_C foreign key(DepID) references Department(DepID)
)
create table Student
(
SNo char(10) NOT NULL primary key,--学号
SName char(8) NOT NULL,--姓名
Sex char(2) NULL constraint C_sex check(sex ='男' or sex='女'),--性别
BirthDate smalldatetime NULL,--出生日期
Class int NOT NULL,--班级
Email varchar(100) NULL,--邮箱
)
create table SC
(
SNo char(10) NOT NULL ,--学生学号
CNo char(10) NOT NULL,--课程编号
SDate smalldatetime NULL,--选课日期
Score decimal(4,1) NULL check (score >0 and score <100) ,--考试成绩
constraint Cno_FK foreign key(CNo)
references Course(CNo),
constraint Sno_FK foreign key(SNo)
references Student(SNo),
constraint SC_PK primary key(SNo,CNo)
)
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 NULL constraint C_num check (num >=0 and num <200),--选修人数
room varchar(15) NULL,--上课地点
croom varchar(15) NULL,--答疑地点
constraint Teaching_PK primary key(TNo,CNo,Term)
)
5.使用SQL语句先将Teaching表的Num属性改为smallint类型,然后再删除Croom属性;
SQL语句如下:
//将Teaching表的Num属性改为smallint类型
ALTER TABLE teachingdrop constraint C_num
ALTER TABLE teaching
alter column num smallint
ALTER TABLE teaching
add constraint C_num check (num >=0 and num <200)
//删除Croom属性
ALTER TABLE Teachingdrop column croom
6.用SQL语句删除Teaching表的定义。
SQL语句如下:
Drop TABLE Teaching
7. 用SQL的导入导出工具,将已经整理好的对应Excel表的数据全部导入到对应的数据库表
方法: