数据库标准系统概论实验.docx
- 文档编号:15951356
- 上传时间:2023-07-09
- 格式:DOCX
- 页数:47
- 大小:514.18KB
数据库标准系统概论实验.docx
《数据库标准系统概论实验.docx》由会员分享,可在线阅读,更多相关《数据库标准系统概论实验.docx(47页珍藏版)》请在冰点文库上搜索。
数据库标准系统概论实验
数据库系统概论实验指导
(第六版)
计算机学院
2010/09
改版履历
版数
改版日期
改版者
内容
第一版
2005/08
李建良
新做成
第二版
2005/10
李宁
1)增加作业题部分
2)增加实验报告模板
第三版
2006/09
李宁
1)增加评分标准
2)增加数据备份还原
第四版
2007/09
李宁
根据实验次数重新调整实验内容
第五版
2008/09
李宁
根据第四版教材的变更进行了如下变动:
1)实验指导中涉及的教材页码
2)实验二的第8,9实验内容
第六版
2010/09
李宁
1)根据实验环境的变更修正相关实验内容(SQL2000->SQL2008)
2)根据实验次数调整实验内容
1.实验概要
1.1.实验说明
Ø内容:
本课程实验分5次完成,每次完成一部分。
具体内容参考本指导的后半部分。
Ø成绩
总成绩为100分,每次实验100分,最后取平均分。
评分标准如下:
项目
具体要求
分值
考勤
正常出勤或请假(无故缺席者0分)
10
预习
报告(无固定格式,纸版或电子版均可)
10
实验
实验过程结果的正确性(※)
60
报告
实验相关文档的正确性,整洁性
20
总分
100
Ø实验报告
每次实验需提交电子版的实验报告(最后一次实验需提交设计文档,源程序等相关资料)。
每次实验结束时,将写好的实验报告,提交给各班辅导老师。
如果确有困难没有完成的情况下,课后自己完成之后提交到辅导老师的邮箱里。
由辅导老师根据课堂上机实验检查状况和实验报告的内容给出每次实验的成绩。
实验报告的内容包括:
实验内容、实验步骤、程序源码、运行结果(可以是程序的输出,也可以是运行画面的抓屏,抓屏图片要尽可能的小,否则文件太大)。
每份实验报告是一个WORD文档。
实验报告命名规则如下:
DBx(实验次数)_XXXXXX(学号)_姓名
例如:
052978的学生的第一次实验报告文件名:
DB1_052978_李宁
注意:
请每个人保存好自己的实验报告的电子版,直到该门课测试成绩公布之后。
1.2.实验环境和配置
SQLServer2008(MicrosoftSQLServer2008ExpressWithAdvanceService)
1.3.上机要求
●上机之前,请做好预习,需要写一个简单的预习报告,格式不限。
●严格遵守实验室的各项规定。
2.实验1:
数据库/表的基本操作和表级约束
2.1.目的和要求
1.了解SQLServer数据库的逻辑结构和物理结构,基本数据类型以及基本使用方法。
2.学会使用图形用户界面以及命令行方式(SQL语言)创建数据库和表。
3.学会使用不同的方法备份和还原数据库。
4.掌握表级约束的定义、使用和删除方法。
2.2.实验准备
1.要明确能够创建数据库的用户必须是系统管理员,或是被授权可以使用CREATEDATABASE语句的用户。
2.创建数据库必须要确定数据库名、所有者(即创建数据库的用户)、数据库大小(最初的大小、最大的大小、是否允许增长及增长的方式)和存储数据的文件。
3.了解常用的创建数据库和表的方法。
4.了解数据库备份和还原的基本方法。
5.了解表级约束的定义、使用和删除方法。
2.3.实验内容
1.利用图形用户界面创建,备份,删除和还原数据库和数据表(30分,每小题5分)
●数据库和表的要求
(1)依据课本P127(第四版教材,下同)的第三题,创建一个名为SPJ的数据库,初始大小为10MB,最大为50MB,数据库自动增长,增长方式是按5%比例增长;日志文件初始为2MB,最大可增长到5MB,按1MB增长。
数据库的逻辑文件名和物理文件名均采用默认值。
(2)数据库SPJ包含供应商表,零件表,工程项目表,供应情况表。
具体每张表的定义以及数据参看课本P74页的第五题。
●完成以下具体操作:
(1)创建的SPJ数据库。
(2)在SPJ数据库中分别创建上述的四张表(只输入一部分数据示意即可)。
(3)备份SPJ数据库。
(4)删除已经创建的工程项目表(J表)。
(5)删除SPJ数据库。
(6)还原刚才删除的SPJ数据库。
2.利用SQL语言创建和删除数据库和数据表(30分,每小题5分)
●数据库和表的要求
(1)创建用于学生信息的数据库,数据库名为Student,初始大小为20MB,最大为100MB,数据库自动增长,增长方式是按10M兆字节增长;日志文件初始为2MB,最大可增长到5MB,按1MB增长。
数据库的逻辑文件名和物理文件名,日志文件名请自定义。
usemaster
ifexists(select*fromsysdatabaseswherename='student10')
dropdatabasestudent10
createdatabasestudent10
on
(
name='student10',
filename='F:
\hyk\student10.mdf',
size=20,
maxsize=100,
filegrowth=10
)
logon
(
name='student10_log',
filename='F:
\hyk\student10.idf',
size=2,
maxsize=5,
filegrowth=1
)
--sysdatabases是系统视图,if语句里面的条件是布尔型
--mdf是主文件idf是日志文件
(2)数据库Student包含学生信息,课程信息和学生选课的信息。
包含下列3个表:
S:
学生基本信息表;C:
课程基本信息表;SC:
学生选课信息表。
各表的结构以及数据如下所示:
表2.1学生基本信息表(表名:
S)
sno
sname
ssex
sbirth
Sdept
95001
李勇
男
1986/1/1
CS
95002
刘晨
女
1985/2/1
IS
95003
王敏
女
1886/10/4
MA
95004
张立
男
1985/6/8
IS
表2.2课程基本信息表(表名:
C)
cno
cname
cpno
Ccredit
1
数据库
2
4
2
数学
3
3
信息系统
1
4
4
操作系统
5
3
5
数据结构
6
3
6
C语言
2
表2.3学生选课信息表(表名:
SC)
sno
cno
grade
95001
1
92
95001
2
85
95001
3
87
95002
2
78
95002
3
84
●完成以下实际操作:
(1)用T-SQL语句创建数据库Student。
用T-SQL语句创建上述的三张表,各个字段的数据类型请自己确定,每张表只要有主键约束即可,不要其他约束。
不用输入数据,只要表的结构就可以。
createtables
(snochar(20)primarykey,
snamechar(20),
ssexchar(20),
sbirthchar(20),
sdeptchar(20)
)
createtablec
(cnochar(20)primarykey,
cnamechar(20),
cpnochar(20),
ccreditchar(20)
)
createtablesc
(snochar(20),
cnochar(20),
gradechar(20),
primarykey(sno,cno)
)
--可在创建表的时候加入各种约束
用T-SQL语句备份数据库Student。
--usemaster
execsp_addumpdevice'disk','student10back','f:
\hyk\student10.bak';
backupdatabasestudent10tostudent10back;
--exec是什么意思
--备份介质,逻辑名称,备份文件实体
(2)用T-SQL语句删除创建的表。
(3)用T-SQL语句删除创建的数据库。
--()删除数据库
dropdatabasestudent10
(4)用T-SQL语句还原刚才备份的数据库Student
--()恢复数据库
restoredatabasestudent10fromstudent10back
--注意语句使用的逻辑名称来恢复
3.利用图形用户界面对上题中创建的Student库的S表中,增加以下的约束和索引。
(25分,每小题5分)
(1)将学号(sno)设置为主键,主键名为pk_sno。
(2)为性别(ssex)添加默认约束(默认值),其值为“男”。
(3)为姓名(sname)添加唯一约束(唯一键),约束名为uk_sname。
(4)为出生日期(sbirth)添加CHECK约束,约束名为ck_sbirth,其检查条件为:
出生日期大于1986年1月1日。
(5)为sbirth创建一个降序排列的UNIQUE索引,索引名为IX_Sbirth。
4.用SQL语言为Student数据库中的SC表添加外键约束,要求如下:
将sno,cno设
置为外键,其引用表为分别是S表和C表,外键名称分别为fk_sno,fk_cno。
(2分)
altertablesc
addconstraintfk_sno
foreignkey(sno)referencess(sno)
altertablesc
addconstraintfk_cno
foreignkey(cno)referencesc(cno)
altertables
addconstraintpk_sno
primarykey(sno)
ALTERTABLEs
addCONSTRAINTDF_s_ssex
default'male'--如何设置默认值
--改变表的属性,sno两边要加括号
--
5.利用图形用户界面根据上面的外键定义画出由S,C,SC表构成的数据库关系图。
(3分)
6.用SQL语言删除S表中所创建的约束或者索引(第3小题中的
(1)-(5))。
(10分,每小题2分)
ALTERTABLEs
DROPCONSTRAINTPK_sno
ALTERTABLEs
DROPCONSTRAINTDF_s_ssex
dropindexix_sbirth
ons
ALTERTABLEs
DROPCONSTRAINTuk_sname
ALTERTABLEs
DROPCONSTRAINTck_sbirth
--check约束,唯一键,默认值的设定
3.实验2:
库级约束和基本表的数据操作
3.1.目的和要求
1.掌握规则的创建、使用和删除方法。
2.掌握默认对象的创建、使用和删除方法。
3.学会在图形用户界面中对表进行插入、修改和删除数据操作。
4.学会使用T-SQL语句对表进行插入、修改和删除数据操作。
5.掌握SELECT语句的各种查询语句。
3.2.实验准备
1.了解规则/默认对象的创建和删除的方法。
2.了解绑定规则/默认对象的具体使用方法。
3.了解图形用户界面中对表的各种操作方法。
4.了解T-SQL中用于对表数据进行插入(INSERT)、修改(UPDATE)和删除(DELETE)命令的用法。
5.了解SELECT语句的各种查询语句。
3.3.实验内容
1.利用SQL语言创建,验证和删除规则。
(10分,每小题5分)
(1)创建一个ssex_rule规则,将其绑定到S表的ssex性别字段上(请验证该规则生效,保证了输入的性别只能是“男”或者“女”)。
Createrulessex_rule
AS
@ssexIN('男','女')
--带有@的变量时什么意思?
EXECsp_bindrule'ssex_rule','s.ssex'
--绑定规则的系统存储过程
(2)删除ssex_rule规则(注意:
规则已绑定到ssex表的性别字段上,需要先解除原有的绑定)。
2.利用SQL语言创建,验证和删除默认值。
(10分,每小题5分)
(1)创建一个ssex_def默认对象,将其绑定到S表的ssex性别字段上,使其默认值为“男”(请验证该默认值生效)。
(2)ssex_def(注意:
默认对象已绑定到S表的ssex性别字段上)。
CREATEDEFAULTssex_defAS'男'--创建默认对象
EXECsp_bindefault'ssex_def','S.ssex'--绑定
droprulessex_rule
--对列的限制可以采用规则绑定和表级约束两种方法来实现
3.在图形用户界面中对表中的数据进行更新操作。
(6分,每小题2分)
(1)按照P75页所示数据,输入四张表中的数据。
(2)修改S表的任意一条数据
(3)删除S表的任意一条数据。
4.用T-SQL语句对表中的数据进行更新操作。
(12分,每小题3分)
(1)P127页5题的8-11小题。
--8.把全部红色的零件改成蓝色
--SQL代码:
updateP
setCOLOR='蓝'
whereCOLOR='红'
--9.由s5供给J4的零件P6改为由S3供应
--SQL代码:
updateSPJ
setSNO='S3'
wheresno='s5'andjno='J2'andpno='P6'
--10.从供应商关系中删除S2的记录,并从相应的供应关系中删除相应的记录
--SQL代码:
delete
fromS
wheresno='s2'
delete
fromspj
wheresno='s2'
--(会因为外键约束而中断)
--11.将(s2,j6,p4,200)插入供应情况关系
--SQL代码:
insert
intospj(sno,jno,pno,qty)
values('s2','j6','p4','200')
5.用T-SQL语句完成下面的数据查询(52分,每小题4分)
(1)P127页第4题。
(5道小题)
--1.求供应工程J1零件的供应商号码sno:
--SQL代码:
SELECTdistinctsno
fromspj
wherejno='j1'
--2.求供应工程J1零件p1的供应商号码sno:
--SQL代码:
SELECTsno
fromspj
wherejno='j1'andpno='p1'
--3.求供应工程j1零件为红色的供应商号码sno:
--SQL代码:
SELECTspj.sno
fromspj,p
wherep.COLOR='红'and
p.pnO=spj.pnoand
spj.jno='j1'
--4.求没有使用天津供应商生产的红色零件的工程号jno:
--SQL代码:
SELECTdistinctspj.jno
fromspj
except
SELECTdistinctspj.jno
fromspj
whereEXISTS
(SELECT*
FROMS,P
WHERES.CITY='天津'
andp.color='红'
andspj.pno=p.pno
andspj.sno=s.sno
)
--5.求至少使用了供应商s1所供应的全部零件的工程号jno:
--SQL代码:
selectdistinctjno
fromspjspjx
wherenotexists
(select*
fromspjspjy
wherespjy.sno='s1'and
notexists
(
select*
fromspjspjz
wherespjx.jno=spjz.jno
andspjz.pno=spjy.pno
)
)
第二种比较简单的代码:
selectjno
fromspj
wherepnoin
(selectdistinctpno
fromspj
wheresno='s1')
groupbyjno
havingcount(distinctpno)=
(selectcount(distinctpno)
fromspj
wheresno='s1')
(2)P127页第5题的1-7小题。
(7道小题)
(1)找出全部供应商所在城市和姓名
SQL代码:
selectsno,sname,city
froms
运行结果:
(2)找出所有零件的名称颜色和重量
SQL代码:
selectpno,pname,color,weight
fromp
运行结果:
(3)找出使用供应商S1所供应零件的工程号码
SQL代码:
selectdistinctjno
fromspj
wherespj.sno='s1'
运行结果
(4)找出工程项目J2使用的各种零件的名称及数量
SQL代码:
selectpname,sum(qty)
fromspj,p
wherespj.jno='j2'andp.pno=spj.pno
groupbypname
运行结果:
(5)找出上海厂商所供应的所用零件的号码
SQL代码:
selectpno
Fromspj
wherespj.snoin
(selectdistinctsno
froms
wheres.city='上海'
)
运行结果:
(6)找出使用上海产的零件的工程名称
SQl语句:
selectdistinctjname
fromspj,j
wherespj.jno=j.jnoandspj.snoin
(selectdistinctsno
froms
wheres.city='上海'
)
运行结果:
(7)找出没有使用天津产的零件的工程号码
SQl语句:
selectjno
fromspj
except
selectdistinctjno
fromspj
wherespj.snoin
(selectdistinctsno
froms
wheres.city='天津'
)
5.查询这样的工程:
供给该工程的零件P1的平均供应量大于供给工程J1的任何一种零件的最大供应量。
selectjno
fromspj
wherepno='p1'
groupbyjno
havingavg(qty)>
(selectmax(qty)
fromspj
wherespj.jno='j1')
6.针对实验一创建的Student数据库进行下面的数据查询(10分,每小题5分)
(1)求不选修C语言课程的学生学号。
selectsno
froms
except
selectsno
fromsc
wherecno=
(selectcno
fromc
wherecname='c语言')
(2)求这样的学生姓名:
该学生选修了全部课程并且其中一门课在90分以上。
selectsname
froms
wheresnoin
(selectsno
fromsc
wheresno
in
(
selectsno
froms
wherenotexists
(select*
fromc
wherenotexists
(select*
fromsc
wheresno=s.sno
andcno=o)
)
)
groupbysno
havingmax(grade)>90
)
4.实验3:
视图操作和安全性控制
4.1.目的和要求
1.掌握使用图形用户界面和SQL语言创建,操作和删除视图的方法。
2.掌握SQLServer中的安全性相关的登录名,角色以及用户的创建以及使用方法。
3.学会使用T-SQL语句对数据库和表操作的灵活控制功能。
4.2.实验准备
1.了解与视图相关的各种SQL语句。
2.了解登录名,角色以及用户的创建以及使用方法。
3.了解T-SQL语句在对数据库和表的控制权限相关命令(GRANT/REVOKE)的用法。
4.3.实验内容
1.在Student数据库中,利用图形用户界面,创建一个选修了数据库课程并且是1986年出生的学生的视图,视图中包括学号,性别,成绩这三个信息。
(5分)
操作工程:
右击“视图”,将相关的表添加进去,然后指定视图所依附的表间的连接关系,最后指出视图的输出(即视图的各个属性列),操作界面如下图所示:
2.用两种不同的SQL语句创建课本128页第11题中要求的视图(视图名:
V_SPJ)(6分,每种方法3分)。
方法一:
createviewV_SP
as
selectsno,pno,qty
fromspj
wherespj.jnoin
(selectjno
fromj
wherej.jname='三建')
方法二:
createviewV_SPJ
as
selectsno,pno,qty
fromspj,j
wherej.jno=spj.jnoand
j.jname='三建'
3.用SQL语句完成课本128页第11题中对视图V_SPJ的查询(4分,每小题2分)。
语句:
selectpno,sum(qty)总量
fromV_SPJ
groupbypno
运行界面:
语句:
select*
fromV_SPJ
wheresno='s1'
运行界面:
4.用T-SQL语句操作视图的数据。
(15分,每题5分)
(1)给视图V_SPJ中增加一条数据(基本表中有插入的数据即可)。
insert
intoV_SPJ
values('s5','j3',900)
说明:
必须将主键约束去掉并且允许为空值以后才可插入
(2)修改视图V_SPJ中的任意一条数据的供应数量。
updateV_SPJ
setqty=111
wheresno='s1'andpno='p1'
(3)删除视图V_SPJ中的任意一条数据。
deleteV_SPJ
wheresno='s1'andpno='p1'andaty=111
说明:
必须添加触发器
5.在图形用户界面中创建新登录名以及用户。
(20分,每题1
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 标准 系统 概论 实验