数据库原理及技术实验指导书09大纲版

《数据库原理及技术实验指导书09大纲版》由会员分享，可在线阅读，更多相关《数据库原理及技术实验指导书09大纲版（20页珍藏版）》请在装配图网上搜索。

1、目 录目 录1数据库原理与实用技术实验课程简介和要求21. 实验教学的主要内容22. 实验教学重点23. 实验环境24. 实验要求2实验一 熟悉实验环境31. 实验目的32. 实验原理解析3SQL Server 2000数据库平台33. 实验内容5SQL Server 2000数据库平台54. 思考题6实验二 使用向导创建并管理数据库71. 实验目的72. 实验原理解析7SQL Server 2000数据库平台使用简介73. 实验内容9SQL Server 2000数据库平台94思考题10实验三 使用SQL语句创建并管理数据库、数据表111.实验目的112.实验原理解析113.实验内容124.

2、思考题13实验四 数据查询141.实验目的142.实验原理解析143.实验内容14实验五 存储过程与触发器17一、实验目的17二、实验内容17实验六 安全管理18一、实验目的18二、实验内容18实验七 DB2实验环境配置20实验八 创建数据库/表空间20实验九 创建和操作数据库对象20实验十 数据移动2020 / 20数据库原理与实用技术实验课程简介和要求1. 实验教学的主要内容（1） 数据库、基本表、视图、索引的建立与数据的更新；（2） 关系数据库的查询，包括单表查询、连接查询、嵌套查询等；（3） 数据库系统的实现技术，包括事务的概念及并发控制、恢复、完整性和安全性实现 机制等。2. 实验教

3、学重点本课程的实验教学重点包括：（4） 数据库、基本表、视图、索引的建立与数据的更新；（5） SQL的数据查询；（6） 恢复、完整性和安全性实现机制；（7） 设计一个小型的数据库管理系统。3. 实验环境实验采用平台：（8） Microsoft公司的SQL Server 数据库平台；（9） IBM公司的DB2数据库平台。4. 实验要求（10） 预习实验指导书有关部分，认真做好实验内容的准备，就实验可能出现的情况提前做出思考和分析；（11） 仔细观察实验过程中出现的各种现象，记录主要情况，做出必要说明和分析；（12） 认真书写实验报告。实验报告包括实验目的和要求，实验情况及其分析。对需编程的实验，

4、给出包含详细注释的源程序清单；实验一 熟悉实验环境1. 实验目的（1） 了解数据库管理系统：SQL Server。（2） 了解数据库管理系统的版本划分和安装环境要求。（3） 掌握SQL Server的安装方法。（4） 掌握SQL Server数据库服务器的注册、配置、连接等操作；熟悉SQL Server 的图形向导工具，掌握企业管理器的操作方法。2. 实验原理解析SQL Server 2000数据库平台（1） 安装SQL Server 2000系统。选择运行“安装SQL Server 2000组件”进入安装组件界面，选择“安装数据库服务器”。进入安装界面后，按照安装提示进行安装；一般需要人工进

5、行干预的有：l 选择安装类型和安装路径：安装类型有：典型安装、最小安装、和自定义安装。安装路径是指SQL Server的系统文件和数据文件的安装位置。默认情况下“安装类型”是典型安装，“安装路径”是操作系统设定的“Program Files”文件夹。你可以自行改变，初次安装最好不要改变他，按默认情况使用；l 配置启动服务的帐号：有两类用户帐号：一类是与Windows操作系统的集成帐号，一类是混合帐号。建议选择第一类帐号进行安装；（2） 安装完毕，启动MS SQL Server 2000服务。如图1-1所示。图1-1服务管理器（3） 管理SQL Server数据库服务器。如图1-2所示：图1-2

6、 服务器属性配置页面（4） 使用“企业管理器”（SQL Server Enterprise Manager）。如图1-3所示：图1-3 企业管理器界面（5） 使用“查询分析器”，如图1-4所示：图1-4 查询分析器（6） 使用过程中可参看“SQL Server_联机丛书”。3. 实验内容SQL Server 2000数据库平台1. 安装SQL Server 2000系统（请同学实验课后自己练习）。2. 查看安装Microsoft SQL Server 2000的目录结构。找到电脑上安装数据库程序的路径，如图1-12：查看并记录下列主要文件夹的含义：1）BACKUP： 2）Binn： 3）Dat

7、a： 4）LOG： 5）Install： 6）JOBS： 图1-123. 启动SQL Server数据库服务。记录你所启动的SQL Server服务器名是什么？该服务器的启动策略是什么，在操作系统启动时自动启动或手工启动？4. 使用“企业管理器”（1） 了解企业管理器的界面、菜单结构以及各项功能；（2） 了解“数据库服务器”的属性配置；记录你所使用的SQL Server服务器采用的“内存”配置是什么配置方式？“安全性”配置中采用的身份验证是什么方式？启动SQL Server服务采用的账户是什么？（3） 了解“数据库”的属性配置；（4） 初步了解数据库、表、视图、用户、安全管理，复制等概念；（5

8、） 浏览pubs数据库结构。记录该数据库当前占用的存储空间是多大？该数据库的存储路径是什么？该数据库中包含哪些数据表？5. 使用“查询分析器”（1） 了解查询分析器有什么作用；（2） 掌握在查询分析器中选择不同的数据库、查看数据库的结构的方法；（3） 查询分析器的初步使用：在分析器中输入语句：use pubs select * from authors 运行以上语句，并记录结果（可截图）。4. 思考题1、 SQL SERVER数据库由哪两类文件组成？这些文件的扩展名分别是什么？作用分别是什么？2、 SQL Server默认创建了哪些数据库，这些数据库的作用分别是什么？3、 查询分析器的作用是什

9、么？可以有几种启动查询分析器的方法？实验二 使用向导创建并管理数据库1. 实验目的（1） 理解数据库的概念。（2） 熟悉Microsoft SQL Server 2000基本操作。（3） 掌握SQL Server系统中“企业管理器”的使用方法；能够熟练使用企业管理器管理数据库、数据表。（4） 了解由已经生成的数据库产生SQL脚本的方法。2. 实验原理解析数据库是由数据库管理系统统一管理和控制的数据集合。在创建与管理数据库时，主要应考虑以下几个方面的问题：（1） 标识符命名规则数据库、数据表、数据表中的列等都需要命名，首先应掌握不同数据库管理系统（SQL Server或IBM DB2）的命名规则

10、。（2） 数据库存储规划主要规划数据库的大小是多少？它在物理磁盘上由哪些磁盘文件组成，这些磁盘文件的存储路径是什么？磁盘文件名是什么？不同的磁盘文件有什么不同的作用？（3） 数据库的访问权限控制数据表定义了数据库中所存数据的逻辑组织结构。主要应考虑数据表的设计（将在数据库设计部分重点讲解），数据表中列的定义，列的数据类型的定义。不同的数据库管理系统（SQL Server或IBM DB2）有不同的数据类型定义。SQL Server 2000数据库平台使用简介（1） 使用企业管理器创建并管理数据库l 创建数据库：在“数据库”选项上点右键弹出菜单，选择“新建数据库”l 管理数据库：选中要管理的数据库

11、，右键单击弹出菜单，选择“属性”，如图2-1，图2-2所示。l 删除数据库：选中要删除的数据库，右键单击弹出菜单，选择“删除”。（慎重删除）注意：认真学习数据库属性中，各选项的含义和作用。图2-1 数据库操作选项 图2-2 数据库属性（2） 使用企业管理器创建并管理数据库启动企业管理器，展开要使用的数据库，选择“表”选项，出现如图2-3所示：l 新建表：右键单击“表”选项，选择“新建表”；l 管理表：右键单击“表”选项，选择“设计表”；l 删除表：右键单击“表”选项，选择“删除”；l 查看表中的数据：右键单击“表”选项，选择“打开表”，返回表中所有行；图2-3 使用“企业管理器”管理数据表3.

12、 实验内容SQL Server 2000数据库平台1. 使用企业管理器创建如下数据库创建一个名为Employee的数据库，该数据库保存在D盘，数据库的主数据文件逻辑名称为employee_data，物理文件名为Employee.mdf，初始大小为10MB，最大尺寸为50MB，增长速度为10%；数据库的日志文件逻辑名称为employee_log，物理文件名为Employee.ldf，初始大小为5MB，最大尺寸为25MB，增长速度为1MB。2. 使用企业管理器创建Employee数据库表。在Employee数据库中创建一张雇员信息表person，一张月薪表salary和一个部门表departmen

13、t。 所创建表的结构如下所示：表1 雇员信息表person字段名数据类型字段长度是否允许为空说明PnoChar6Not null工人编号，主键PnameVarchar10Not null姓名SexChar2Not null性别BirthdayDatetime8Null出生日期ProfVarchar10Null职称DeptnoChar4Notnull部门代码，外键（参照department表）表2 月薪表salary字段名数据类型字段长度是否允许为空说明PnoChar6Not null(工人编号、month)，主键baseDec5Null基本工资（要求base=300）BonusDec5Null

14、奖金(要求bonus=50)MonthIntNot null月份Fact实发工资=基本工资+奖金表3 部门表department字段名数据类型字段长度是否允许为空说明DeptnoChar4Not null部门代码，主键DnameVarchar10Not Null部门名称注：以下的实验项目都采用此Employee数据库。3. 练习生成Employee数据库脚本“employee.sql”，并分析和记录下脚本信息。4. 使用企业管理器生成Employee数据库表的关系图，并记录。5. 在企业管理其中，直接执行删除表月薪表salary中的Fact列的操作，看是否能执行这一操作。4思考题1、在数据库s

15、alary表中要求base不小于300，bonus不小于50如何实现。2、在数据库雇员信息表person中要求SEX列值只能为“男”或者“女”，如何实现。3、SQL Server数据库和DB2数据库对象的关系各有何区别？建数据表时有何不同？实验三 使用SQL语句创建并管理数据库、数据表1. 实验目的（1） 掌握查询分析器的使用方法。（2） 掌握TSQL语句的基本使用。（3） 熟练掌握DDL语句。（4） 熟练掌握DML(Insert, Delete, Update)语句。2. 实验原理解析SQL的数据定义包括基本表的定义、索引的定义和视图的定义三部分。这里的“定义”包含创建（create）、取消

16、(drop)、更改(modify)三部分内容。本实验主要介绍表的定义，索引和视图的定义将在后续实验中介绍。表的创建：CREATE TABLE;表的取消：DROP TABLE;表的更改：ALTER TABLE;(1) SQL的基本数据类型SQL在定义表各个属性时，要求指明其中的数据类型和长度，不同的DBMS支持的数据类型不完全一样。表3-1列举了Microsoft SQL Server支持主要数据类型。表3-1 Microsoft SQL Server支持主要数据类型序号数据类型名称定义标识说 明1整数类型TINYINT其值按1个字节存储2整数类型SMALLINT其值按2个字节存储3整数类型IN

17、T,INTEGER其值按4个字节存储4实数类型REAL其值按4个字节存储5实数类型FLOAT其值按8个字节存储6长度为n的字符类型CHARACTER(n)OR CHAR(n)1个字符占1个字节7最大长度为n的变长字符类型VARCHAR(n)所占空间与实际字符数有关8日期时间类型DTAETIME默认格式为MM-DD-YYYY,HH:MM:AM/PM9时间戳TIMESTAMP更新或插入一行时，系统自动记录的日期时间类型(2) SQL语句用CREATE语句来创建基本表，一般形式为： CREATE TABLE ( , , , ) 其中，内的内容时可选项，是所要定义的基本表名称。(3) SQL语句用AL

18、TER语句增加新的列，或修改列的数据类型，取消完整性约束。更改的过程将列作为一个对象来处理。ALTER语句更改表的语句一般格式如下：ALTER TABLEADD完整性约束条件DROP CONSTRAINT 完整性约束名ALTER COLUMN(4) 取消表的语句格式：DROP TABLE(5) SQL语句对数据库进行数据插入操作，一般使用INSERT,其一般格式如下：INSERTINTO ,VALUES()|DBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则。(6) 对数据的修改，可以使用UPDATE语句，一般形式是：UPDATESET=表达式,=表达式WHERE(7) 数据

19、删除的SQL语句DELETEFROMWHEREDBMS在执行修改、删除语句时会检查所插元组是否破坏表上已定义的参照完整性约束。3. 实验内容1、 用TSQL语句创建并管理数据库“Employee数据库”，数据库要求见实验二。记录创建数据库的SQL语句。2、 修改数据库：将“Employee数据库”的数据库最大容量更改为无限制（UNLIMITED），然后将“Employee数据库”的日志文件增长方式改为2MB。记录SQL语句。3、 用TSQL语句在“Employee数据库”创建数据表，数据表的结构见实验二。记录创建表的SQL语句。4、 修改表结构：将雇员信息表person中，Prof的字段长度改

20、为15。记录SQL语句。5、 向表中添加记录，使用Insert Into 语句分别向四张表中添加符合表结构属性的数据记录，要求每张表至少4条记录，并显示所添加的记录数据。6、 向雇员信息表person中添加记录PnoPnameSexBirthdayProfDeptno000001田林男1982-4-7初级001000002郭达男1953-2-14高级001000003王琳女1984-8-25初级002000004王辉男1984-5-11初级002000005罗大右男1968-12-10高级002000006蔡琴女1978-4-20中级003完成如下操作： （1）、修改表中记录：将“王辉”的部门

21、修改为“003”； （2）、删除记录：删除表中性别为“女”的员工记录； （3）、删除表：将“person”从“Employee数据库”中删除。4. 思考题如数据库表中存在如下记录：表department中数据DeptnoDname001营销部002财务部表person中的数据PnoPnameSexBirthdayProfDeptno000001田林男1982-4-7初级001000002郭达男1953-2-14高级001000003王琳女1984-8-25初级002现执行： DELETE FROM department WHERE Depton=“001”，执行结果如何？为什么？实验四 数据查

22、询1. 实验目的（1）理解T-SQL语言的使用。（2）熟练掌握数据查询语句。（3）掌握合计函数的使用。2. 实验原理解析SQL提供了SQL映像语句用于数据库查询。下面是一个查询语句的一般形式：SELECT ALL|DISTINCT 别名，,FROM , WHERE GROUP BY , HAVING ORDER BY ASC|DESC , ASC|DESCCOMPUTE 3. 实验内容1、CAP数据库的查询（1）建立CAP数据库，输入C、A、P、O四张表；（2）完成课后习题：3.2 (b)Retrieve aid values of agents who receive the maximum

23、 percent commission.3.5 Consider the problem to find all (cid, aid) pairs where the customer does not place an order through the agent. This can be accomplished with the Select statementselect cid, aid from customers c. agents a where not exists (select * from orders x where x.cid = c.cid and x.aid

24、=a.aid) ;Is it possible to achieve this result using the NOT IN predicate in place of the NOT EXISTS predicate with a single Subquery? With more than one Subquery? Explain your answer and demonstrate any equivalent form by execution.3.8(a)Write a Select statement with no WHERE clause to retrieve all

25、 customer cids and the maximum money each spends on any product. Label the columns of the resulting table: eid, MAXSPENT.(b)Write a query to retrieve the AVERAGE value (over all customers) of the MAXSPENT of query (a)3.11 (b)We say that a customer x orders a product y in an average quantity A if A i

26、s avg(qty) for all orders rows with cid = x and pid = y. Is it possible in a single SQL statement to retrieve cid values of customers who order all the products that they receive in average quantities (by product) of at least 300?(f)Get pid values of products that are ordered by all customers in Dal

27、las.(j)Use a single Update statement to raise the prices of all products warehoused in Duluth or Dallas by 10%. Then restore the original values by rerunning the procedure that you originally used to create and load the products table.(l)Write an SQL query to get aid and percent values of agents who

28、 take orders from all customers who live in Duluth. The aid values should be reported in order by decreasing percent. (Note that if percent is not retrieved in the select list, we cannot order by these values.)2、Employee数据库的查询（1）向表中插入数据要求：按下表所示，向表person, salary, department中插入数据表person中的数据PnoPnameSex

29、BirthdayProfDeptno000001田林男1982-4-7初级001000002郭达男1953-2-14高级001000003王琳女1984-8-25初级002000004王辉男1984-5-11初级002000005罗大右男1968-12-10高级002000006蔡琴女1978-4-20中级003000007李艳女1960-5-14高级001000008刘德华男1971-2-19高级003000009黎明男1973-10-9中级002表salary中的数据PnoBaseBonusFactMonth0000012100500=base+bonus10000023000600100

30、000328004501000004250034010000053200490100000627006001000007400020010000082800350100000927604401000001240060020000022800400200000328603502000004254032020000053200480200000627003002000007400025020000082800300200000927604802000001220050030000023100400300000328006503000004250054030000053200400300000627

31、006803000007410040030000082000330300000927604203表department中数据DeptnoDname001营销部002财务部003人事部004采购部（2）将职工编号为000006的员工3月份基本工资增加为3000，奖金增加到800。（3）员工000009已经离开公司，将该员工的数据删除（4）简单条件查询 查询person表中所有不重复的职称。 查询具有高级职称的女员工信息 查询职工姓名为黎明的员工数据 查询各部门的实发工资总数（5）复杂条件查询 查询平均工资高于3000的部门名和对应的平均工资。 查询1月份实发工资比平均实发工资高的员工姓名和实发工

32、资额。 查询2月份实发工资比一月高的员工姓名。 利用sql语句将1，2，3月累积的员工的实发工资按降序排序请记录各项内容实现的语句和结果。实验五 存储过程与触发器一、实验目的（1）理解存储过程的概念、了解存储过程的类型（2）掌握创建存储过程的方法（3）掌握执行存储过程的方法（4）理解触发器的功能及工作原理。（5）掌握创建、更改、删除触发器的方法。二、实验内容1、使用不带参数的存储过程（1）创建一个存储过程，查询person表中所有不重复的职称。（2）执行存储过程2、带输入参数的存储过程（1）创建一个存储过程，按照姓名查询person表中的员工信息。（2）执行存储过程，查询名为黎明的员工数据3、

33、带输入/输出参数的存储过程（1）创建一个存储过程，使其能够根据员工姓名，查询员工工资。（考虑到员工不存在时给出提示信息）（2）执行存储过程4、使用触发器（1）创建一个触发器trig_update，返回对person进行更新操作后，被更新的记录条数（2）执行触发器（3）修改触发器trig_update，除返回被更新的记录条数外，再返回学生的所有基本信息5、使用触发器的两个特殊表：插入表（inserted）和删除表（deleted）。（1）在person上创建触发器ins_del_sample，在插入、删除或更新操作后，分别从inserted表和deleted表中查询员工所有信息。（请同学们在做删

34、除操作时，注意备份）（2）执行触发器。思考执行插入、删除和更新操作后返回的表有什么区别？6、使用系统存储过程查看触发器（与存储过程的使用类似）（1）显示触发器trig_update的一般信息（2）显示触发器trig_update的源代码（3）显示person上所有的依赖关系（4）显示触发器trig_update所引用的对象实验六 安全管理一、实验目的（1）理解数据库安全性的重要性（2）理解SQL Server2000对数据库数据的保护体系（3）理解登录帐户、数据库用户、角色、许可的概念（4）掌握管理帐户、用户、角色、许可的方法二、实验内容1.设置安全认证模式SQL Server 2000有两种

35、登录验证机制：Windows身份验证和混合身份验证。修改方法：在企业管理器中单击右键选择“属性”，在属性窗口中选择“安全性”，如下：即可进行身份验证方式的修改。2、创建和管理登录帐户（创建登录帐户只能由系统管理员完成）（1）用企业管理器创建、查看、删除SQL Server登录帐户（2）用T-SQL语句创建、查看、删除SQL Server登录帐户1）使用系统存储过程sp_addlogin创建一个登录帐户ABC，密码为123，使用的默认数据库为pubs2）使用系统存储过程sp_helplogins查看SQL Server登录帐户3）使用系统存储过程sp_droplogin删除SQL Server登

36、录帐户ABC（3）管理Windows登录到SQL Server系统的帐户1）使用系统存储过程sp_grantlogin将一个Windows帐户添加到SQL Server系统2）使用系统存储过程sp_denylogin阻止一个Windows帐户登录到SQL Server系统3）使用系统存储过程sp_revokelogin删除Windows帐户到SQL Server系统的登录3、创建与管理数据库用户用户是基于数据库的名称，是和登录帐户相关联的，只有数据库所有者及数据库系统管理员才有执行系统存储过程sp_grantdbaccess的权力。（1）使用企业管理器创建、查看、删除数据库用户（2）用T-SQ

37、L语句创建、查看、删除数据库用户1）使用系统存储过程sp_grantdbaccess为数据库pubs的登录帐户ABC创建一个数据库用户db_ABC2）使用系统存储过程sp_helpuser查看数据库用户3）使用系统存储过程sp_revokedbaccess删除数据库中的用户db_ABC4、创建与管理角色在SQL Server中，角色有两种：服务器角色和数据库角色。服务器角色是服务器级的一个对象，只能包含登录名。数据库角色是数据库级的一个对象，只能包含数据库用户名而不能是登录名。服务器角色：（1）使用企业管理器管理固定服务器角色包括为某一登录帐户指定服务器角色、收回某登录帐户的服务器角色。（2）

38、使用T-SQL语句管理固定服务器角色1）使用系统存储过程sp_addsrvrolemember指定登录帐户ABC为固定服务器角色sysadmin2）使用系统存储过程sp_dropsrvrolemember收回登录帐户ABC的固定服务器角色sysadmin数据库角色：（1）使用企业管理器管理数据库角色包括为数据库角色添加成员、删除数据库角色的某个成员（2）使用T-SQL语句管理数据库角色1）使用系统存储过程sp_addrolemember将数据库用户db_ABC指定为数据库角色db_accessadmin2）使用系统存储过程sp_droprolemember收回数据库用户db_ABC的数据库角色

39、db_accessadmin5、管理许可（1）使用企业管理器管理许可1）设置表authors对数据库用户的许可2）设置数据库用户db_ABC对表的许可（2）使用T-SQL语句管理数据库角色1）创建一个数据库用户ABC2，并授予数据库创建表及视图的许可（授权语句GRANT）2）授予用户ABC2对数据库中表authors的查询、删除权限3）撤消用户ABC2对数据库pubs创建表及视图的许可。4）撤消用户ABC2对数据库中表authors的查询、删除权（REVOKE）5）拒绝用户ABC2对数据库中表authors的查询、删除权（DENY）（注意：REVOKE与DENY的区别）实验七 DB2实验环境配置实验八 创建数据库/表空间实验九 创建和操作数据库对象实验十 数据移动