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

实验1 数据库与表的创建与管理1. 实验目的(1) 掌握启动和停止数据库服务。(2) 熟练掌握使用SQL Server Management Studio创建和删除数据库的方法。(3) 熟练掌握使用SQL Server Management Studio创建和管理数据表的方法.2. 实验环境操作系统：WINDOWS XP/2000/2003/vista数据库管理系统：SQL SERVER 20053. 实验内容(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,各表结构如下所示：表34 Receipt表结构列名 说明 数据类型 是否为主码ReceiptID入库单编号字符型，长度为10是ReceiptDate入库日期日期时间型否Vendor供应商名称 字符型，长度为50否Remark备注文本型否表35 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数据记录。表37 Receipt表数据ReceiptIDReceiptDateVendorRemarkR0810080012008-10-8杭州娃哈哈无R0811240152008-1124沈阳桃李面包无R0901201242009120蒙牛乳业无R0904093272009409广东喜之郎无(7) 输入如表38所示的Product数据记录.表3-8 Product表数据ProductIDProductNameUnitPriceProductionDateP001娃哈哈纯净水瓶2。002008916P002娃哈哈运动饮料瓶3.002008920P003娃哈哈营养快线瓶3.5020099-10P004蒙牛早餐奶盒2.502009-110P005蒙牛纯牛奶袋1.5020091-8P006桃李面包袋4.5020081122P007喜之郎果冻袋18.002009215P008德芙巧克力袋55.002009-3-28(8) 输入如表3-9所示的Storage数据记录。表39 Storage表数据ReceiptIDProductIDQuantityR081008001P00150R081008001P00235R081008001P00322R081124015P00685R090120124P00463R090120124P00568R090409327P00726(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 20053. 实验内容（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，各表结构如下所示：表414 Receipt表结构列名 说明 数据类型 约束ReceiptID入库单编号字符型，长度为10主码ReceiptDate入库日期日期时间型非空Vendor供应商名称 字符型，长度为50非空Remark备注文本型表415 Product表结构列名 说明数据类型约束ProductID商品编号 字符型，长度为10主码ProductName商品名称 字符型,长度为20取值惟一Unit计量单位字符型，长度为10默认值为“件Price商品价格 定点精度型，精度为10，小数点后保留2位小数取值大于0ProductionDate生产日期字符型,长度为50非空表416 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 20053. 实验内容(1) 利用数据操纵语言（DML)完成对Warehouse1数据库中基本表的数据查询。（2） 利用数据操纵语言（DML）完成对Warehouse1数据库中基本表的数据更新。4. 实验步骤(1) 打开SQL Server Management Studio窗口，在工具栏上单击“新建查询命令，打开查询窗口。(2) 简单查询。 查询全体商品的商品编号和名称. 查询全体商品的详细记录。 查询Storage表中的所有商品编号（消除取值相同的行)。 查询供应商为“杭州娃哈哈”的所有入库单信息. 查询入库数量大于40件的入库明细信息。 查询商品价格在1020（包括10和20）的商品信息。 查询供应商为“杭州娃哈哈”、“蒙牛乳业”、“广东喜之郎”的入库单信息. 查询商品名称最后三个字是“纯净水的商品的名称及其价格. 备注为空的入库单信息。 查询生产日期在2009年，并且价格高于2元的商品编号、名称及其价格。 查询所有商品信息，查询结果按商品价格降序排列. 查询2008年入库的入库单编号、入库日期及供应商名称，查询结果按供应商降序排列，同一供应商的入库单按入库日期升序排列。 统计所有入库单的总数量. 查询商品编号为“P001”的入库总数量. 查询商品表中的最高价格、最低价格以及所有商品的平均价格。(3) 连接查询与嵌套查询。 查询每张入库单及其入库商品的情况，列出入库单编号、入库日期、供应商名称、入库商品名称、入库数量。 查询与“P001”（注:商品编号）同年生产的商品信息。 查询“杭州娃哈哈”供应的所有商品的编号、名称、单价。 查询“桃李面包”的所有入库明细，列出入库单编号、入库日期、入库数量。 统计入库商品种类超过2种入库单编号及入库日期.(4) 数据更新。 向基本表Receipt中插入如下入库单元组:ReceiptIDReceiptDateVendorRemarkR0810080012008-10-8杭州娃哈哈R0901201242009-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 20053. 实验内容（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_1AS SELECT ReceiptID, Vendor， Remark FROM Receipt WHERE Year（ReceiptDate) = 2008CREATE VIEW Receipt_2008_2AS SELECT ReceiptID, Vendor， Remark FROM Receipt WHERE Year(ReceiptDate) = 2008WITH CHECK OPTION 将2009年生产的商品视图中的“亲亲果冻”的商品价格改为10元。 删除价格高于20元的2009年生产的商品信息。(5) 删除视图. 删除视图Receipt_2008_1和Receipt_2008_2。5. 思考题(1) 创建视图需要注意哪些问题？ (2） 哪些视图不允许更新？(3） 视图与基本表之间有什么关系？实验5 数据库设计（一)实验目的与要求1熟悉ER模型的基本概念和图形的表示方法。2掌握将现实世界的事物转化成ER图的基本技巧。3熟悉关系数据模型的基本概念。4掌握将E-R图转化为关系表的基本技巧。（二)实验环境SQL SERVER 2000（三)准备工作安装好SQL SERVER2000（四）实验内容现需要开发一个数据库，对所有学生社团进行统一管理。每个社团都会从社团成员中选出一名学生作为负责人，每个社团有一个指导教师。学生可以自愿选择参加一个或多个社团,每个社团也由多名学生组成。凡参加社团的学生都以学号识别。各社团的信息包括社团名称、成立日期、指导老师姓名、负责人、活动地点等。社团成员的信息包括学号、姓名、性别、所在系等. 1根据需求确定实体，属性和联系。2将实体，属性和联系转化为E-R图.3将ER图转化为表（五）实验步骤1根据需求提炼出实体2确定实体间的联系3生成多个分ER图4合并分E-R图，生成初步E-R图5确定各个实体和联系的属性6消除冗余，设计基本E-R图7把ER图转换为关系模型，并进行优化8设计用户的子模式9把关系模式转换为物理表10写出创建表的语句,并在SQL SERVER中实现 实验6 数据库安全1. 实验目的(1) 掌握如何管理数据库用户。(2) 掌握如何管理数据库角色以及如何为数据库角色添加和删除用户。(3) 熟练掌握如何授予和剥夺权限。2. 实验环境操作系统：WINDOWS XP/2000/2003/vista数据库管理系统：SQL SERVER 20053. 实验内容利用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) 利用数据库用户属性窗口中“安全对象”选项卡完成上述的权限授予操作。