《数据库原理实验》指导书文档格式.docx
《《数据库原理实验》指导书文档格式.docx》由会员分享，可在线阅读，更多相关《《数据库原理实验》指导书文档格式.docx（24页珍藏版）》请在冰豆网上搜索。
4）对运行情况所作的分析
5）本次调试程序取得的经验。
（三）教学重点与难点：
1.数据库及表的建立与使用
2.简单查询
3.连接查询
4.嵌套查询
5.数据库更新
6.视图的定义和查询
7.安全性控制
8.事务的建立和使用
9.存储过程的使用
10.触发器的使用
11.数据转换
实验一数据库查询
课程名称：
数据库原理实验
实验类型：
验证型
数据库查询
4学时
实验目的：
使学生掌握SQLServerQueryAnalyzer的使用方法，加深对SQL和T-SQL语言的查询语句的理解。
熟练掌握表的基本查询，连接查询和嵌套查询，以及掌握数据排序和数据分组的操作方法。
实验原理：
SELECT[ALL|DISTINCT]<
目标列表达式>
[，<
]…
FROM<
表名或视图名>
[WHERE<
条件表达式>
]
[GROUPBY<
列名1>
[HAVING<
]]
[orderby<
列名2>
[ASC|DESC]]；
实验方法：
将查询需求用T-SQL语言表示；
在SQLServerQueryAnalyzer的输入区中输入T-SQL查询语句；
设置QueryAnalyzer的结果区为StandardExecute（标准执行）或ExecutetoGrid（网格执行）方式；
发布执行命令，并在结果区中查看查询结果；
如果结果不正确，要进行修改，直到正确为止。
实验内容：
1.查询所有的Germany国家的员工.
2.查询出生地为London的员工
3.查询所有7号顾客签订的订单.
4.查询所有FirstName为‘Robert’的顾客签订的订单.
5.查看所有订单
6.查询所有没有按时发货的订单
7.查找哪些顾客的订单在规定时间前没有发货。
给出顾客名，电话
8.查找所有已经订购了货物但尚未发货的顾客信息（分别用连接，in和exists实现）
9.查看每个员工签订的订单的总金额，按照金额由小到大排序（分别用连接，in和exists实现）
10.假设运费要顾客自己承担，计算每个顾客为自己的订单付出的金额
11.查询所有与1号和9号都签过订单的顾客
12.查询签订订单金额超过100000美元的员工姓名
13.查询员工FirstName为Robert的所有同事
14.查询已经现已经退休的职工，假设退休年龄为60，给出他的姓名，工作年限，住址，电话。
附录1：
可能用到的函数
获取系统日期：
getdate（）
计算两个日期的时间差：
DateDiff（Datepart,startDate,enddate）.datepart是规定了应在日期的哪一部分计算差额的参数,取值有：
日期部分
缩写
year
yy,yyyy
quarter
qq,q
Month
mm,m
dayofyear
dy,y
Day
dd,d
Week
wk,ww
Hour
hh
minute
mi,n
second
ss,s
millisecond
ms
附录2：
数据库结构
RelationSchemas
Employees（）
ColumnName
ISNull
DataType
MaxLength
Description
Key
EmployeeID
No
int
Yes
LastName
varchar
20
FirstName
Title
YES
30
TitleOfCourtesy
25
BirthDate
datetime
HireDate
Address
60
City
Region
PostalCode
Country
HomePhone
24
Extension
Photo
image
2147483647
Notes
ntext
1073741823
17
ReportsTo
Orders（）
ProductID
CustomerID
nchar
OrderDate
RequiredDate
ShippedDate
Quantity
smallint
Discount
real
折扣掉的百分比
Freight
money
运费
ShipName
40
ShipAddress
ShipCity
ShipRegion
ShipPostalCode
ShipCountry
Customers（）
char
CompanyName
ContactName
ContactTitle
Phone
Fax
Products（）
ProductName
QuantityPerUnit
UnitPrice
单价
UnitsInStock
存储量
实验二数据库定义
数据库原理
综合型
数据库定义
2学时
（1）使学生掌握E_R图的设计方法，并能将E_R图转换成关系模式；
（2）熟悉SQLDDL和DML语言，能够完成基本的表结构的定义、修改；
（3）掌握数据库完整性约束定义
1.将E_R图转换为关系的方法：
⏹一个实体转换为一个关系模式，实体的属性就是关系的属性，实体的码就是关系的码
⏹一个1:
1联系可以转换为一个独立的关系模式，也可以与任意对应的关系模式合并
n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并
⏹一个m:
n联系转换为一个关系模式
⏹三个或三个以上实体间的一个多元联系可以转换为一个关系模式
2.定义库：
CreateDATABASE<
数据库名>
3.表
⏹定义表
CREATETABLE<
表名>
（<
列名>
<
数据类型>
[列级完整性约束]
[列级完整性约束]][，<
表级完整性约束>
]）；
⏹添加列
ALTERTABLE<
ADD<
<
类型>
⏹修改列
ALTERCOLUMN<
⏹删除列
DROPCOLUMN<
假设某公司的业务规则如下：
（1）公司下设几个部门，如技术部、财务部、市场部等。
（2）每个部门承担多个工程项目，每个工程项目属于一个部门。
（3）每个部门有多名职工，每一名职工只能属于一个部门。
（4）一个部门可能参与多个工程项目，且每个工程项目有多名职工参与施工，一个职工可以参与多个项目。
根据职工在工程项目中完成的情况发放酬金。
（5）工程项目有工程号、工程名两个属性；
部门有部门号、部门名称、办公电话和办公地点等属性；
职工有职工号、姓名、性别属性，出生日期；
问题：
1．根据上述规则设计E-R模型;
2．将E-R模型转换成关系数据模型；
3．利用SQL语句定义上述关系，要求：
（1）为每个关系定义主码和外码；
（2）职工姓名、部门名称不能取空值，并且部门名称是唯一的；
（3）职工的性别只能取“男”和“女”，默认值为“男”；
（4）部门号具有类似“B01”的形式，即每个部门号的首字符为“B”，其余两个字符为数字。
（5）酬金不低于2000元。
4．修改部门号的约束，改为“B001”的形式，每个部门号的首字符为“B”，其余三个字符为数字。
5．在原有关系的基础上，增加一个新列，用于记录每名职工参加项目的起始时间。
注意：
请认真选择属性的数据类型和精度。
实验三数据更新
数据更新
一．实验目的和要求：
掌握用SQL语句实现数据的插入、修改和删除。
二．实验内容：
建立一个商店的数据库store，记录顾客及其购物情况，由下面三个表组成：
商品（商品号，商品名，单价，商品类别，供应商）；
顾客（顾客号，姓名，住址）；
购买（顾客号，商品号，购买数量）；
三．试用SQL语言完成下列功能：
1．建表，在定义中要求声明：
（1）每个表的主外码；
（2）顾客的姓名和商品名不能为空值；
（3）单价必须大于0；
（4）购买数量必须在0到20之间；
2．往表中插入数据：
部门（M01，佳洁士，8.00，牙膏，宝洁；
M02，高露洁，6.50，牙膏，高露洁；
M03，洁诺，5.00，牙膏，联合利华；
M04，舒肤佳，3.00，香皂，宝洁；
M05，夏士莲，5.00，香皂，联合利华；
M06，雕牌，2.50，洗衣粉，纳爱斯
M07，中华，3.50，牙膏，联合利华；
M08，汰渍，3.00，洗衣粉，宝洁；
M09，碧浪，4.00，洗衣粉，宝洁；
）
顾客（C01，Dennis，海淀；
C02，John，朝阳；
C03，Tom，东城；
C04，Jenny，东城；
C05，Rick，西城；
购买（C01，M01，3；
C01，M05，2；
C01，M08，2；
C02，M02，5；
C02，M06，4；
C03，M01，1；
C03，M05，1；
C03，M06，3；
C03，M08，1；
C04，M03，7；
C04，M04，3；
C05，M06，2；
C05，M07，8；
注：
商品表数据（9）,顾客表数据（5）,购买表数据（5）
2.完成下列查询：
（1）求购买了供应商"
宝洁"
产品的所有顾客；
（2）求购买的商品包括了顾客"
Dennis"
所购买商品的顾客（姓名）；
（3）求牙膏卖出数量最多的供应商。
3.将所有的牙膏商品单价增加10%。
4.删除从未被购买的商品记录。
实验报告：
1.实验各项内容实现的过程。
2.实验结果分析。
实验四视图和索引
视图和索引
（1）掌握视图的定义、查询和更新；
（2）掌握索引的定义；
（3）掌握利用索引优化查询效率；
1．视图
⏹定义视图
CREATEVIEW<
视图名>
[（<
[，<
]…）]
AS<
子查询>
[WITHCHECKOPTION]；
⏹删除视图
DROPVIEW<
2．索引（注意：
索引不能修改）
⏹定义索引
CREATE[UNIQUE][CLUSTER]INDEX<
索引名>
ON<
（<
[<
次序>
][，<
]]…）；
⏹删除索引
DROPINDEX<
1、在实验1中用到的Commerce数据库中完成如下问题：
（1）为表Employees在Title列上添加一个升序索引;
（2）为表orders添加索引v1，索引键OrderDate升序,ShippedDate降序;
（3）设计一个视图View1，用于查询每个员工签订的订单总数;
（4）设计视图View2，查询ShipCountry为Canada，且订购货物数量超过30件的订单信息
（5）在View2的基础上设计视图View3，查询ShipCountry为Canada，且订购货物数量超过30件的顾客信息;
（6）删除视图View2，测试View3是否仍有效;
2、测试索引
实验五数据安全性
数据安全性
本实验的目的是通过实验使学生加深对数据安全性的理解，并掌握SQLServer中有关用户、角色及操作权限的管理方法。
⏹添加登录用户
sp_addlogin<
用户名>
[,<
口令>
][,<
缺省登录数据库>
]
⏹为数据库添加用户
sp_grantdbaccess<
登录名>
⏹授权
GRANT<
权限>
权限>
]…
[ON<
对象名>
TO<
用户>
用户>
[WITHGRANTOPTION];
⏹回收权限
REVOKE<
[CASCADE];
⏹触发器
触发器是一种特殊类型的存储过程，当使用下面的一种或多种数据修改操作在指定表中对数据进行修改时，触发器会生效：
UPDATE、INSERT或DELETE。
触发器可以查询其它表，而且可以包含复杂的SQL语句。
它们主要用于强制复杂的业务规则或要求。
触发器还有助于强制引用完整性，以便在添加、更新或删除表中的行时保留表之间已定义的关系。
（1）在SQLServerEnterpriserManager中，设置SQLServer的安全认证模式，实现对SQLServer的用户和角色管理，设置和管理数据操作权限。
（2）在QueryAnalyzer中使用T-SQL设计管理用户权限。
具体内容包括：
１．基本操作实验
（1）在SQLServerEnterpriserManager中为所属的SQL服务器设置WindowsNT安全认证模式，然后使用两种不同的身份验证方式登录QueryAnalyzer。
（2）在SQLServerQueryAnalyzer中建立登录用户u1,u2,u3,u4，并使其成为commerce数据库的用户。
（3）把对products表的查询权限授予用户u1，并允许其将权限转授给其它用户。
验证u1是否获得相应权限。
（4）用户u1将查询products表的权限授予用户u2。
验证u2是否获得相应权限。
（5）将对orders表的查询权限授予所有用户。
（6）建立用户u5，并验证u4和u5是否获得查询orders表的权限。
（7）回收u4查询orders表的权限，并验证。
实验六T-SQL编程
设计型
T_SQL编程
实验目的和要求：
1.掌握变量的定义及赋值、数据显示及IF、WHILE、CASE逻辑控制语句。
2.掌握简单子查询、IN子查询及EXISTS子查询的用法，并能应用T-SQL进行综合查询。
创建学员成绩数据库stu。
1、学员信息表如下：
试编写SQL语句查找李文才的左右同桌。
2、学员成绩表如下：
统计并显示本班笔试平均分，如果平均分在70以上，显示“成绩优秀“，并显示前三名学员的考试信息；
如果在70以下，显示“本班成绩较差“，并显示后三名学员的考试信息。
3、本次考试成绩较差，假定要提分，确保每人笔试都通过。
提分规则很简单，先每人都加2分，看是否都通过，如果没有全部通过，每人再加2分，再看是否都通过，如此反复提分，直到所有人都通过为止。
4、采用美国的ABCDE五级打分制来显示笔试成绩。
A级:
90分以上
B级：
80－89分
C级:
70－79分
D级：
60－69分
E级：
60分以下
5、统计本次考试的缺考情况
6、比较笔试平均分和机试平均分，较低者进行循环提分，但提分后最高分不能超过97分。
加分后重新统计通过情况
7、统计通过率
实验七存储过程编程
存储过程编程
实验目的
掌握存储过程的创建及调用。
实验内容
有学员成绩数据库stu，其中学员信息表stuinfo和学生成绩表stumarks如下：
1、请创建存储过程，查看本次考试平均分以及未通过考试的学员名单
2、修改上题：
由于每次考试的难易程度不一样，每次笔试和机试的及格线可能随时变化（不再是60分），这导致考试的评判结果也相应变化。
（提示：
使用带输入参数的存储过程）
3、修改上题：
返回未通过考试的学员人数。
使用带输出参数的存储过程）
4、编写存储过程，显示男生或女生的名单（性别要求通过参数输入）。
格式为：
序号
学号
姓名
用带有游标的存储过程）
实验八触发器编程
触发器编程
理解触发器的工作原理，掌握如何使用inserted表和deleted表及如何创建：
INSERT、UPDATE、DELETE触发器。
建立银行业务数据库bankdb，其中，帐户信息表（bank）存放帐户的信息，交易信息表（transInfo）存放每次的交易信息。
1、当向交易信息表（transInfo）中插入一条交易信息时，自动更新对应帐户的余额。
2、当删除交易信息表时，要求自动备份被删除的数据到表backupTable中。
3、跟踪用户的交易，交易金额超过20000元，则取消交易，并给出错误提示。
实验报告
1、实验各项内容实现的过程。
2、实验结果分析。
实验九事务与死锁
升级会员 