数据库原理与应用实验指导书.doc

实验1 数据库与表的创建与管理
1. 实验目的
(1) 掌握启动和停止数据库服务。
(2) 熟练掌握使用SQL Server Management Studio创建和删除数据库的方法。
(3) 熟练掌握使用SQL Server Management Studio创建和管理数据表的方法.
2. 实验环境
操作系统：WINDOWS XP/2000/2003/vista
数据库管理系统：SQL SERVER 2005
3. 实验内容
(1) 启动数据库服务.
(2) 利用SQL Server Management Studio创建数据库：Warehouse1。
（3) 在Warehouse数据库中创建3张表:Receipt、Product、Storage。
（4) 在Receipt、Product、Storage表中输入数据.
4. 实验步骤
(1) 启动SQL Server服务。在“开始”菜单上,依次点击“程序”|“Microsoft SQL Server 2005”｜“配置工具”｜“SQL Server Configuration Manager"选项.选择“SQL Server（MSSQLSERVER)”,单击鼠标右键后，选择“启动".
(2) 在SQL Server Management Studio窗口中选择“数据库”文件夹,选择“新建数据库”命令，输入新建数据库的名字Warehouse1，单击“确定”按钮.
(3) 在Warehouse1数据库中建立表Receipt、Product、Storage,各表结构如下所示：
表3—4 Receipt表结构
列名
说明
数据类型
是否为主码
ReceiptID
入库单编号
字符型，长度为10
是
ReceiptDate
入库日期
日期时间型
否
Vendor
供应商名称
字符型，长度为50
否
Remark
备注
文本型
否
表3—5 Product表结构
列名
说明
数据类型
是否为主码
ProductID
商品编号
字符型,长度为10
是
ProductName
商品名称
字符型,长度为20
否
Unit
计量单位
字符型，长度为10
否
Price
商品价格
定点精度型,精度为10，小数点后保留2位小数
否
ProductionDate
生产日期
字符型，长度为50
否
表3-6 Storage表结构
列名
说明
数据类型
是否为主码
ReceiptID
入库单编号
字符型，长度为10
是
ProductID
商品编号
字符型,长度为10
是
Quantity
入库数量
短整型
否
(4) 在SQL Server Management Studio窗口中打开“表”对象,选择刚才创建的Recipt表。
(5) 右击鼠标，选择“打开表”命令，打开表的数据记录窗口。
(6) 输入如表3-7所示的Recipt数据记录。
表3—7 Receipt表数据
ReceiptID
ReceiptDate
Vendor
Remark
R081008001
2008-10-8
杭州娃哈哈
无
R081124015
2008-11—24
沈阳桃李面包
无
R090120124
2009—1—20
蒙牛乳业
无
R090409327
2009—4—09
广东喜之郎
无
(7) 输入如表3—8所示的Product数据记录.
表3-8 Product表数据
ProductID
ProductName
Unit
Price
ProductionDate
P001
娃哈哈纯净水
瓶
2。00
2008—9—16
P002
娃哈哈运动饮料
瓶
3.00
2008—9—20
P003
娃哈哈营养快线
瓶
3.50
2009—9-10
P004
蒙牛早餐奶
盒
2.50
2009-1—10
P005
蒙牛纯牛奶
袋
1.50
2009—1-8
P006
桃李面包
袋
4.50
2008—11—22
P007
喜之郎果冻
袋
18.00
2009—2—15
P008
德芙巧克力
袋
55.00
2009-3-28
(8) 输入如表3-9所示的Storage数据记录。
表3—9 Storage表数据
ReceiptID
ProductID
Quantity
R081008001
P001
50
R081008001
P002
35
R081008001
P003
22
R081124015
P006
85
R090120124
P004
63
R090120124
P005
68
R090409327
P007
26
(9) 向Receipt表插入Dealer（经手人)列，数据类型为字符型，长度为10，保存该表。
(10) 删除Receipt表的Dealer列,保存该表.
(11) 删除Receipt表。
(12) 删除Warehouse1数据库。
5. 思考题
（1) 有几种方法设置开机启动SQL Server服务?
（2） 在为数据库创建表之前应该考虑哪些问题？
(3） 创建数据库和数据表时需要注意哪些问题？

实验2 SQL语言实验：数据定义
1. 实验目的
(1) 熟悉各种数据类型。
(2) 能够运用数据定义语言（DDL）正确创建和管理数据库和数据表。
(3) 掌握6种常用的数据完整性约束。
2. 实验环境
操作系统:WINDOWS XP/2000/2003/vista
数据库管理系统：SQL SERVER 2005
3. 实验内容
（1） 利用数据定义语言创建数据库:Warehouse2。
(2) 利用数据定义语言在Warehouse数据库中创建3张表：Receipt、Product、Storage。
4. 实验步骤
(1) 打开SQL Server Management Studio窗口，在工具栏上单击“新建查询"命令,打开查询窗口。
(2) 使用CREATE DATABASE语句新建数据库，名为Warehouse2。
(3) 使用CREATE TABLE语句在Warehouse2数据库中建立表Receipt、Product、Storage，各表结构如下所示：
表4—14 Receipt表结构
列名
说明
数据类型
约束
ReceiptID
入库单编号
字符型，长度为10
主码
ReceiptDate
入库日期
日期时间型
非空
Vendor
供应商名称
字符型，长度为50
非空
Remark
备注
文本型

表4—15 Product表结构
列名
说明
数据类型
约束
ProductID
商品编号
字符型，长度为10
主码
ProductName
商品名称
字符型,长度为20
取值惟一
Unit
计量单位
字符型，长度为10
默认值为“件"
Price
商品价格
定点精度型，精度为10，小数点后保留2位小数
取值大于0
ProductionDate
生产日期
字符型,长度为50
非空
表4—16 Storage表结构
列名
说明
数据类型
约束
ReceiptID
入库单编号
字符型，长度为10
主码，引用Receipt的外码
ProductID
商品编号
字符型，长度为10
主码，引用Product的外码
Quantity
入库数量
短整型
取值大于0
(4) 使用ALTER TABLE语句修改Receipt表，具体要求如下：
① 插入Dealer(经手人）列,数据类型为整型。
② 插入Checker（审核人）列，数据类型为字符型，长度为10。
③ 将Dealer列的数据类型改为字符型，长度为10。
④ 删除Dealer列。
⑤ 删除Checker列.
(5) 使用CREATE INDEX语句为Receipt表在ReceiptDate列上建立降序排列索引I_Date.
(6) 删除Receipt表的I_Date索引。
(7) 删除Receipt表.
(8) 删除Warehouse2数据库。
5. 思考题
（1） 用sql语句创建表时需要注意哪些问题?
(2） 如何修改表的结构？
（3） 删除索引时需要注意哪些问题？

实验3 SQL语言实验：数据操纵
1. 实验目的
(1) 熟练掌握数据查询语句的基本用法。
(2) 熟练掌握数据更新语句的基本用法。
2. 实验环境
操作系统：WINDOWS XP/2000/2003/vista
数据库管理系统:SQL SERVER 2005
3. 实验内容
(1) 利用数据操纵语言（DML)完成对Warehouse1数据库中基本表的数据查询。
（2） 利用数据操纵语言（DML）完成对Warehouse1数据库中基本表的数据更新。
4. 实验步骤
(1) 打开SQL Server Management Studio窗口，在工具栏上单击“新建查询"命令，打开查询窗口。
(2) 简单查询。
① 查询全体商品的商品编号和名称.
② 查询全体商品的详细记录。
③ 查询Storage表中的所有商品编号（消除取值相同的行)。
④ 查询供应商为“杭州娃哈哈”的所有入库单信息.
⑤ 查询入库数量大于40件的入库明细信息。
⑥ 查询商品价格在10~20（包括10和20）的商品信息。
⑦ 查询供应商为“杭州娃哈哈”、“蒙牛乳业”、“广东喜之郎”的入库单信息.
⑧ 查询商品名称最后三个字是“纯净水"的商品的名称及其价格.
⑨ 备注为空的入库单信息。
⑩ 查询生产日期在2009年，并且价格高于2元的商品编号、名称及其价格。
⑪ 查询所有商品信息，查询结果按商品价格降序排列.
⑫ 查询2008年入库的入库单编号、入库日期及供应商名称，查询结果按供应商降序排列，同一供应商的入库单按入库日期升序排列。
⑬ 统计所有入库单的总数量.
⑭ 查询商品编号为“P001”的入库总数量.
⑮ 查询商品表中的最高价格、最低价格以及所有商品的平均价格。
(3) 连接查询与嵌套查询。
① 查询每张入库单及其入库商品的情况，列出入库单编号、入库日期、供应商名称、入库商品名称、入库数量。
② 查询与“P001”（注:商品编号）同年生产的商品信息。
③ 查询“杭州娃哈哈”供应的所有商品的编号、名称、单价。
④ 查询“桃李面包”的所有入库明细，列出入库单编号、入库日期、入库数量。
⑤ 统计入库商品种类超过2种入库单编号及入库日期.
(4) 数据更新。
① 向基本表Receipt中插入如下入库单元组:

ReceiptID
ReceiptDate
Vendor
Remark
R081008001
2008-10-8
杭州娃哈哈

R090120124
2009-1-20
蒙牛乳业

② 将入库单编号为“R081008001”的供应商修改为“沈阳桃李面包”。
③ 将Storage表中所有的入库数量增加10件。
④ 将“蒙牛乳业”供应的所有商品的入库数量改为零。
⑤ 删除入库单编号为“R090120124”的入库单记录.
⑥ 从Storage表中，把“杭州娃哈哈”供应的“娃哈哈纯净水”商品的入库记录删除。
⑦ 删除Storage表中的所有入库明细记录.
5. 思考题
(1) 创建查询时，应遵循的基本原则有哪些?
(2) 能够在WHERE中使用的运算符有哪些？各运算符的功能是什么？
(3) 对于表Storage，分别执行DROP TABLE Storage和DELETE FROM Storage两条命令，结果有什么不同?

实验4 视图的创建与使用
1. 实验目的
(1) 熟练掌握创建视图语句的基本用法。
(2) 熟练掌握查询、更新和删除视图语句的基本用法.
2. 实验环境
操作系统:WINDOWS XP/2000/2003/vista
数据库管理系统：SQL SERVER 2005
3. 实验内容
（1） 利用SQL语言在Warehouse1数据库上创建视图。
（2) 利用SQL语言完成对所创建视图的查询、更新以及删除操作.
4. 实验步骤
(1) 打开SQL Server Management Studio窗口，在工具栏上单击“新建查询”命令，打开查询窗口.
(2) 创建视图。
① 建立2009年生产的商品视图。
② 建立2009年生产的商品视图,输出其商品编号、名称、价格。
③ 建立2009年生产的商品视图，输出其商品编号、名称、价格，并且更换列名。
④ 修改Product表的结构，增加Remark（备注）一列，再执行上述3组语句，查看结果。
⑤ 建立供应商为“杭州娃哈哈"供应2009年生产的商品的入库单视图。
⑥ 将商品编号及其平均入库数量建立一个视图。
⑦ 建立一个反映入库单年份的视图。
(3) 查询视图。
① 查询价格高于10元的2009年生产的商品信息。
② 查询平均入库数量超过50件的商品信息。
③ 统计每年的入库单数量。
(4) 更新视图。
① 向2009年生产的商品视图中插入一条记录,其中商品编号为P011，名称为亲亲果冻，计量单位为袋，价格为8元，生产日期为2009-5-16。
② 通过如下两组SQL语句，建立2008年的入库单视图.然后分别向两视图中插入一条2009年入库的入库单记录，查看执行结果.
CREATE VIEW Receipt_2008_1
AS
SELECT ReceiptID, Vendor， Remark FROM Receipt
WHERE Year（ReceiptDate) = ‘2008’
CREATE VIEW Receipt_2008_2
AS
SELECT ReceiptID, Vendor， Remark FROM Receipt
WHERE Year(ReceiptDate) = ‘2008’
WITH CHECK OPTION
③ 将2009年生产的商品视图中的“亲亲果冻”的商品价格改为10元。
④ 删除价格高于20元的2009年生产的商品信息。
(5) 删除视图.
删除视图Receipt_2008_1和Receipt_2008_2。
5. 思考题
(1) 创建视图需要注意哪些问题？
(2） 哪些视图不允许更新？
(3） 视图与基本表之间有什么关系？
实验5 数据库设计
（一)实验目的与要求
1．熟悉E—R模型的基本概念和图形的表示方法。
2．掌握将现实世界的事物转化成E—R图的基本技巧。
3．熟悉关系数据模型的基本概念。
4．掌握将E-R图转化为关系表的基本技巧。
（二)实验环境
SQL SERVER 2000
（三)准备工作
安装好SQL SERVER2000
（四）实验内容
现需要开发一个数据库，对所有学生社团进行统一管理。每个社团都会从社团成员中选出一名学生作为负责人，每个社团有一个指导教师。学生可以自愿选择参加一个或多个社团,每个社团也由多名学生组成。凡参加社团的学生都以学号识别。各社团的信息包括社团名称、成立日期、指导老师姓名、负责人、活动地点等。社团成员的信息包括学号、姓名、性别、所在系等.
1．根据需求确定实体，属性和联系。
2．将实体，属性和联系转化为E-R图.
3．将E—R图转化为表
（五）实验步骤
1．根据需求提炼出实体
2．确定实体间的联系
3．生成多个分E—R图
4．合并分E-R图，生成初步E-R图
5．确定各个实体和联系的属性
6．消除冗余，设计基本E-R图
7．把E—R图转换为关系模型，并进行优化
8．设计用户的子模式
9．把关系模式转换为物理表
10．写出创建表的语句,并在SQL SERVER中实现

实验6 数据库安全
1. 实验目的
(1) 掌握如何管理数据库用户。
(2) 掌握如何管理数据库角色以及如何为数据库角色添加和删除用户。
(3) 熟练掌握如何授予和剥夺权限。
2. 实验环境
操作系统：WINDOWS XP/2000/2003/vista
数据库管理系统：SQL SERVER 2005
3. 实验内容
利用SQL Server Management Studio完成对Warehouse数据库的安全管理。
4. 实验步骤
(1) 打开SQL Server Management Studio窗口,设置服务器身份验证模式为“SQL Server和Windows身份验证模式”.
(2) 创建登录账户User1,User2，User3，User4.
(3) 新建Warehouse数据库用户User1，User2,User3，User4，并将其分别关联到User1，User2，User3,User4登录账户中。
(4) 新建Warehouse数据库角色Role，并将用户User1，User2添加到该角色中。
(5) 打开查询窗口，利用数据控制语言(DCL）练习权限的授予与收回.
① 将查询Receipt表的权限授予所有用户。
② 将查询Product表的权限授予用户User3。
③ 将查询Storage表和修改入库数量的权限授予用户User4，并允许将此权限再授予其他用户。
④ 将向Product表插入数据的权限授予角色Role.
⑤ 收回所有用户对Receipt表的查询权限。
⑥ 将用户User4对入库数量的修改权限收回。
⑦ 收回角色Role对Product表的插入数据权限.
(6) 删除Warehouse数据库用户User3，User4。
(7) 删除Warehouse数据库角色Role。
5. 思考题
（1) 新建数据库用户时需要注意哪些问题？
（2) 登录账户、数据库用户与数据库角色之间有什么关系？
(3) 利用数据库用户属性窗口中“安全对象”选项卡完成上述的权限授予操作。