数据库实验第二部分1,2

1、徐州工程学院管理学院实验报告实验课程名称 ： 数据库原理及应用 实 验 地 点： 经济管理实验教学中心 20132014学年（一）学期 2013 年 9月至 2013 年 12 月专 业 信息管理与信息系统班 级 11信管1班 学生姓名 学 号 指导老师 蒋 秀 莲 数据库课程实验第二部分实验1 建立数据库实验名称：建立数据库。实验内容：在SQL Server环境下建立数据库和维护数据库。实验目的：理解SQL Server数据库的存储结构，掌握SQL Server数据库的建立方法和维护方法。实验方法：在SQL Server环境下用CREATE DATABASE命令建立数据库（注意：需要是系统管

2、理员用户，或者被指定dbcreator固定服务器角色的成员，或者被显式地授予CREATE DATABASE权限）实验要求：1. 根据具体要求创建如下3个数据库：（1） 创建数据库test1（使用一个数据文件和一个日志文件），要求指定一个数据文件，一个事务日志文件，并把它们安排在不同的硬盘上。（2） 创建数据库test2（使用多个数据文件和多个日志文件），要求使用3个数据文件和两个事务日志文件，主数据文件是列表中的第一个文件或者用关键字PRIMARY指定（文件扩展名：主数据文件使用.mdf，次数据文件使用.ndf，事务日志文件使用.ldf）。（3） 创建数据库test3（使用文件组），要求使用3

3、个文件组（含主文件组，即默认的文件组），每个文件组包含两个数据文件，并且每个文件组要安排在不同的硬盘上。2. 修改建立的test1数据库，为之添加一个5MB大小的新数据文件。3. 修改建立的test2数据库，将其中的主数据文件增加5MB的存储空间。4. 删除建立的数据库test1。答题如下：第1题第1小题第1题第2小题第1题第3小题第2题第3题第4题实验2 建立表和定义完整性约束实验名称：建立模式，表和定义完整性约束。实验内容：在实验1建立数据库的基础上，参照下图和下表建立数据表并定义完整性约束。 实验目的：熟练掌握表的建立和数据完整性约束的定义方法，实践DBMS提供的数据完整性功能，加深对数

4、据完整性的理解。实验方法：在实验1建立数据库的基础上用CREATE SCHEMA命令建立模式，用CREATE TABLE命令建立表并定义数据完整性约束（注意：需要有CREATE SCHEMA和CREATE TABLE的权限，或者其他具有相应权限的身份），用ALTER TABLE命令修改表结构。实验要求：1. 用CREATE SCHEMA命令建立模式（自己命名模式名称）。2. 在定义的模式下参照图3-4和表3-10使用CREATE TABLE命令建立表并定义完整性约束。仓库订购单订购单明细供应商职工3. 使用ALTER TABLE命令按如下要求修改表结构：（1） 为订购单表增加一个新的字段“完成

5、日期”，类型为日期时间型，默认是空值。（2） 为订单明细表的“数量”字段重新定义约束：大于0，小于等于1000的正整数，不允许为空值。（3） 将供应商表的供应商名字段的类型修改为varchar（30）。 实验3 数据操作和完整性约束的作用实验名称：数据操作和完整性约束的作用。实验内容：在实验2的基础上完成数据的插入操作，然后进行部分修改和删除操作，在这些操作中体会数据完整性约束的作用。实验目的：熟练掌握SQL的INSERT，UPDATE和DELETE命令，深刻理解数据完整性约束的作用以及约束时机。实验方法：在实验2的基础上首先用INSERT命令插入各个表的记录，然后使用UPDATE和DELET

6、E命令对部分记录进行修改和删除操作。实验要求：1. 自行设计各个表的记录，其中仓库表至少5条记录，职工表至少10条记录，供应商表至少5条记录、订购单表至少20条记录、订购单名细表至少50条记录。2. 使用INSERT命令完成记录的插入操作，要求分别设计若干违反实体完整性、参照完整性和用户自定义完整性约束的插入操作，并分析原因。原因：INSERT 语句与CHECK 约束CK_仓库冲突。该冲突发生于数据库test，表dbo.仓库, column 仓库号。3. 设计若干删除操作，体会执行删除操作时检查参照完整性规则的效果（要求涉及拒绝删除、空值删除和级联删除等不同的处理方式）。违反外键约束,订购单表

7、中找到子记录空值删除4. 设计若干更新操作，体会执行更新操作时检查实体完整性规则、参照完整性规则和用户自定义完整性规则的效果。违反检查约束违反外键约束5. 将x城市仓库的职工的工资提高10%。6. 用UPDATE语句完成订购单表金额字段的计算（提示：某张订购单的金额为该订购单所有订购项目（订购单明细）单价乘以数量的总和）。7. 设计23个删除操作，它需要另一个表中的数据作为删除条件（例如，按指定的供应商名称删除订购单记录）。根据指定的供应商名称删除订购单记录根据指定的员工号删除订购单记录根据指定的订购单号删除订购单明细记录8. 在实验报告中给出具体的记录和设计的操作，并对各种数据完整性检查，给

8、出具体的分析和讨论。表一 仓库表二 订购单表三 订购单明细表四 供应商表五 职工实验4 数据查询实验名称：使用SQL SECLECT语句的数据查询操作。实验内容：在实验2和3的基础上完成规定的查询操作。实验目的：熟练掌握SQL SECLECT命令。实验方法：在实验2和3的基础上使用SQL SECLECT命令完成实验要求中所规定的各种查询。实验要求： 用SQL SECLECT命令完成以下查询（在完成查询时将题目中的X，Y等根据实验3产生的数据变化）： （1）至（12）是一般简单查询 （1）从职工表中查询所有工资值（考虑去掉和不去掉重复值两种情况）。 -去除重复 -查询所有 （2）查询仓库表中的全

9、部信息。 （3）查询工资多于x元的职工号。 （4）查询哪些仓库有工资多于y元的职工。 （5）给出在仓库m或n(m,n是仓库号)工作，并且工资少于z元的职工号。（6）查询工资在xy元范围内的职工信息（xy）。（7）从供应商表中查询出全部公司的信息（不要工厂或其他供应商的信息，即供应商名最后两个汉字是“公司”的记录）。（8）查询城市不是a的全部仓库信息。（9）查询尚未确定供应商的订购单（即订购单的供应商号为空值的记录）。（10）查询已经确定了供应商的订购单信息。（11）按职工的工资升序列出全部职工信息。（12）先按仓库号升序，再按工资降序列出全部职工信息。（13）（21）使用连接查询（13）查询工

10、资多于x元的职工号和其所在的城市。（14） 查询工作在面积大于s的仓库的职工号以及这些职工工作所在的城市。 （15） 根据职工表列出上一级经理及其职工（被其领导）的清单（即列出谁领导谁的清单）。 （16） 列出仓库表和职工表的广义笛卡尔积的运算结果。 （17） 根据仓库表和职工表的广义笛卡尔积得到仓库表和职工表进行自然连接的结果。 （18）（21）分别用内连接、左连接、右连接和全连接完成，分析它们的效果（18）查询供应商-订购单信息，查询结果中包括供应商号、供应商名、订购单号和订购日期这4个字段。（19） 查询供应商-订购单信息，查询结果中包括供应商号、供应商名、订购单号和订购日期这4个字段，

11、要求结果中列出所有供应商信息（包括目前没有订购单的）。 （20） 查询供应商-订购单信息，查询结果中包括供应商号、供应商名、订购单号和订购日期这4个字段，要求结果中能够反映目前没有确定供应商的订购单情况。 （21）查询供应商-订购单信息，查询结果中包括供应商号、供应商名、订购单号和订购日期这4个字段，要求结果中既能反应目前没有订购单的供应商信息，又能反映目前没有确定供应商的订购单情况。（22）（31）使用嵌套查询（注意使用ANY，All，EXISTS）（22）查询哪些城市至少有一个仓库的职工的工资为x元。（23） 查询所有职工的工资都多于x元的仓库的信息。 （24） 查询和职工a同样工资的所有

12、职工信息。 （25） 查询哪些城市的仓库向地址为d的供应商发出了订购单。 （26） 查询地址为x的供应商目前有y城市仓库的订购单的供应商的名称（分别用嵌套查询和连接查询完成，分析各自的效率）。（27） 查询有职工的工资大于或等于x仓库中任何一名职工的工资的仓库号。 （28） 查询有职工的工资大于或等于x仓库中所有职工的工资的仓库号。 （29） 列出每个职工经手的具有最高金额的订购单信息。 （30） 查询目前还没有职工的仓库的信息。 （31） 查询目前至少已经有一个职工的仓库的信息。 （32）（42）汇总和分组查询（32）查询目前仓库所在城市的数目。（33） 查询目前需要支付的职工工资的总数。

13、（34） 查询x和y两个城市的仓库职工的工资总和。 （35） 查询所有职工的工资都大于x元的仓库的平均面积。 （36） 查询工资大于x元的职工所经手的订购单的最高金额。 （37） 查询每个仓库的职工的平均工资。 （38） 查询每个仓库的订购单的最高金额、最低金额和平均金额。 （39） 查询至少有5个订购单项目的每张订购单的平均金额。 （40）列出职工全部记录并计算各仓库的平均工资和工资小计，最后给出全体职工的平均工资和工资总和（使用COMPUTEBY和COMPUTE）。（41）列出全部订购单明细并计算各订购单的金额，最后给出所有订购单的平均金额和金额总计（使用COMPUTEBY和COMPUTE

14、）。（42）列出全部订购单记录并计算所有订购单的平均金额和金额总计（使用COMPUTE）。实验5 视图的应用实验名称：视图的建立和应用。实验内容：在实验2和3的基础上定义视图，并在视图上完成查询、插入、更新和删除操作。实验目的：掌握视图定义和应用的方法，体会视图和基本表的异同之处。实验方法：在实验2和3的基础上用CREATE VIEW命令定义视图，然后使用SELECT命令完成查询，使用INSERT，UPDATE和DELETE命令分别完成插入，更新和删除操作。实验要求：1. 在实验2建立的基本表的基础上，按如下要求进行设计和建立视图：（1）基于单个表按投影操作定义视图。（2） 基于单个表按选择操

15、作定义视图。 （3）基于单个表按选择和投影操作定义视图。 （4）基于多个表根据连接操作定义视图。 （5）基于多个表根据嵌套查询定义视图。 （6）定义含有虚字段的视图。 2. 分别在定义的视图上设计一些查询（包括基于视图和基本表的连接或嵌套查询）。3. 在不同的视图上分别设计一些插入、更新和删除操作，分情况讨论哪些操作可以成功完成，哪些操作不能完成，并分析原因。4. 在实验报告中要给出具体的视图定义要求和操作要求，并针对各种情况做出具体的分析和讨论。实验6 游标实验名称：游标的设计和使用。实验内容：在SQL Server环境下使用T-SQL的游标功能完成指定的操作。实验目的：理解和掌握游标的使用

16、方法。实验方法：在SQL Server环境下使用游标完成指定的操作。实验要求：1. 定义一个游标，其内容是按单价升序列出的全部订购单以及其明细信息（基于实验2建立的表和实验3插入的数据）。2. 逐一读出游标中的记录，并显示。实验7 存储过程实验名称：存储过程的设计和使用。实验内容：在SQL Server环境下设计、创建并执行存储过程。实验目的：理解和掌握数据库存储过程的创建和调用方法。实验方法：在SQL Server环境下，按要求创建和调用存储过程完成指定的操作。实验要求：1. 建立如下存储过程（基于实验2建立的表和实验3插入的数据）。（1）查询工资范围在x元到y元范围内的职工信息。说明：l

17、该存储过程有两个参数；l 要求查询的职工信息包括仓库号、所在城市、职工号和工资。（2）查询在某年某月某日之后签订的订购单的有关供应商信息。说明：l 以订购单中的订购日期作为参数；l 分别按订购单号显示有关供应商信息。（3）更新操作，将某个仓库的订购单的订购日期统一修改为一个指定日期（参数为仓库号和新的订购日期）。2. 在客户端以存储过程和输入SQL语句的方式分别执行相同的查询或操作，比较使用和不使用存储过程的区别。实验8 触发器实验名称：DML触发器的设计和应用。实验内容：在SQL Server环境下设计，创建DML触发器，并设定相关操作使触发器运行。实验目的：理解和掌握数据库中触发器的创建方

18、法，体会触发器执行的条件和作用。实验方法：在SQL Server环境下，按要求设计DML触发器，并使用相关操作使触发器运行。实验要求：1. 为职工表建立一个更新触发器，当修改工资值超过6%时给出警示信息。2. 为订购单表建立一个插入和更新触发器，约束规则是：当订购单金额小于3000时必须使用本地供应商（发出订购单的仓库和供应商在同一个城市），如果不满足要求，则拒绝操作，并给出错误信息。3. 设计并执行相关的插入操作和更新操作，体会DML触发器的效果和作用。实验9 用户管理和权限管理实验名称：用户管理和权限管理。 实验内容：在SQL Server环境下完成数据库的用户管理、角色管理和操作权限管理

19、。实验目的：理解和体会数据库安全性的内容，加强对数据库管理系统的安全管理功能的认识。实验方法：在SQL Server环境下分别以系统管理员、数据库管理员、对象拥有者和普通用户的身份完成所要求的各项操作。实验要求：1. 以系统管理员身份完成如下实验：（1）建立3个不同名称的注册用户。（2）使用ALTER LOGIN命令对建立的注册用户作不同的修改。（3）建立一个数据库管理员用户。2. 以数据库管理员身份完成如下实验：（1）根据已有的注册用户建立几个当前数据库的用户（部分用户可以指定默认模式等）。（2）使用ALTER USER命令修改部分用户设置。（3）建立若干角色，部分角色指定其他用户管理。（4

20、）授权一些用户可以创建表等数据库对象。（5）完成角色管理及其他授权管理。3. 以下实验由若干学生组成一组共同完成：（1）每个用户有建立对象的权限，各自建立自己的对象（如表和视图等）。（2）各用户名之间就表或视图的查询、修改、删除、插入等互相授权，在授权过程中体会GRANT命令中WITH GRANT OPTION短语的作用。（3）分情况收回权限，并体会REVOKE命令中GRANT OPTION FOR和CASCADE短语的作用。4. 在实验报告中要给出具体的操作要求和过程，并针对各种情况做出具体的分析和讨论。实验10 事务管理和灾难恢复实验名称：事务管理及备份与恢复实验内容：在SQL Serve

21、r环境下完成事务管理的基本实验，完成数据库的备份和恢复操作。实验目的：理解和体会数据库事务管理的内容，掌握数据库备份和恢复的基本方法，加强对数据库管理系统的事务管理功能的认识。实验方法：事务 在SQL Server环境下实验事务管理的基本内容；掌握数据库备份和恢复的基本方法。实验要求：1. 软故障后的恢复。（1）设计事务及事务处理的程序。（2）模拟软故障的情形，使设计的事务及事务处理程序的各个分支都可以执行到。（3）体会事务的原子性和一致性。2. 硬故障后的恢复。（1）针对一个具体的数据库设计一个备份恢复策略。（2）作数据库全备份和若干增量备份（每次备份后都有一些数据操作）。（3）模拟一个硬故

22、障（假设数据库损坏）。（4）备份当前日志。（5）恢复数据库。3. 在实验报告中要给出具体的操作要求和过程，并针对各种情况做出具体的分析和讨论。实验11 并发事务控制实验名称:并发事务管理及控制实验内容: 在SQL Server环境下完成封锁，死锁和设置隔离级别等基本实验。实验目的: 理解和体会数据库事务管理的内容，掌握设计并发事务的基本方法，加强对数据库管理系统的事务管理功能的认识。实验方法: 若干人一组在SQL Server环境下同时操作共享数据，设计各种情形进行实验，深刻体会并发控制的方法和重要性。实验要求：1.若干学生一组共同完成本实验。2.设计一组操作产生“脏”读问题，然后通过封锁避免“脏”读问题。3.设计一组操作产生不可重复读问题，然后通过封锁避免不可重复读问题。4.设计一组操作产生丢失更新问题，然后通过封锁避免丢失更新问题。5.设计一组产生死锁的操作，再利用相同顺序法和一次封锁法有效避免死锁。6.设计一个相对完整的应用，使用设置隔离级别来进行并发控制，