《数据库系统原理》实验指导书(8)

数据库系统原理

实验指导书

任课教师：刘洪波、王虎寅

桂林电子科技大学 计算机科学与工程学院

2014．4

目录

本课程实验前的准备 ...................................................................................................................................... ３ 实验一 SQL Server基本使用与数据定义 ........................................................................................................ ６ 实验三 SQL查询与视图 .................................................................................................................................. １１ 实验三 数据库的安全 ..................................................................................................................................... １２ 实验四 数据库连接与访问 ............................................................................................................................. １６ 实验五 数据库综合设计（课外选做） ......................................................................................................... １７

２

本课程实验前的准备

一．SQL Server简介

SQL Server是一种基于客户/服务器的关系型数据库管理系统，它使用Transact-SQL语言在服务器和客户机之间传送请求和答复。

SQL Server使用客户/服务器体系结构把所有的工作负荷分解成在服务顺上的任务和在客户机上的任务。客户机应用程序负责商业逻辑和向用户提供数据，一般运行在一个或者多个客户机上，但是也可以运行在服务器上。服务器管理数据库和分配可用的服务器资源，例如内存、网络带宽和磁盘操作。客户机应用程序界面允许应用程序在单独的客户机上运行，并且通过网络与服务器通讯。

SQL Server可以在多种操作系统上运行。SQL Server的服务器环境可以是Windows NT

或者Windows 9x，其客户机环境可以是WindowsNT、Windows9x、第三方平台和Internet浏览器。

SQL Server包括3个服务，即MSSQLServer、SQL Server Agent和Microsoft.Distributed Transaction Coordinator（MS DTC）服务。MSSQLServer服务就是SQL Server的RDBMS（关系数据库管理系统）；SQL Server Agent服务用于管理任务、警报和操作员；MS DTC服务是分布式事务协处理器，用于保证在多个服务器之间事务同时完成或者取消，协调多服务器之间的事务一致性。本课程实验主要介绍其中的MSSQLServer服务。

在SQL Server系统中，有两种类型的数据库：系统数据库和用户数据库。系统数据库是系统提供的数据库，包含了记录系统中所有系统信息的系统表。用户数据库就是用户自己创建的数据库，包含了用户的所有数据。

系统安装之后，生成了四个系统数据库和两个样板数据库。这四个系统数据库分别是master、model、msdb和tempdb。Master数据库是最重要的系统数据库，它记录了系统中所有系统级的信息。Model数据库是一个模板数据库，它包含了用户数据库中应该包含的所有系统表的结构。当创建用户数据库时，系统自动把Model数据库中的内容拷贝到新建的用户数据库中。Msdb数据库记录了有关SQL Server Agent服务的信息，例如作业、警报、操作员等信息。Tempdb数据库是一个临时数据库，当某些操作有中间数据时，这些中间数据就保存在该数据库中。两个样板数据库是pubs和Northwind。Pubs数据库记录了一个虚构的出版公司的数据信息，Northwind数据库保存了一个虚构的贸易公司的数据信息。

一个数据库中，有许多数据对象。各对象的种类和相应描述如下表所示：

数据库对象 表 数据类型 约束 缺省 规则 索引 视图 存储过程

描述 由行和列组成，是存储数据的地方 限制输入到表中的数据类型 有主键、外键、唯一键、缺省和检查五种类型的约束 自动插入的常量值 限制表中列的取值范围 加快检索数据的方式 查看一个式者多个表的一种方式 一组预编译的SQL语句，可以完成指定的操作 ３

触发器 一种特殊类型的存储过程，当某个操作影响到它保护的数据时，它就会自动触发执行 SQL Server安装之后，其目录和文件的默认位置是：安装路径下\\Microsoft SQL Server\\MSSQL，其中几个主要目录是：BACKUP（备份文件的默认位置）、Data（数据库文件）、LOG（错误日志文件）。

SQL Server．．．．．．．．．的．详．细．介．绍．请．查．阅．SQL Server．．．．．．．．．使．用．手．册．或．帮．助．。．

二．数据库设计总体需求

完成代理商、客户、产品和彼此之间的订单数据库。详细要求如下：

1．对于代理商，需要存储：代理商编号、姓名、地址、提成金额以及提成的比例。 2．每个客户需要存储：客户编号、姓名、地址、收支差额以及贷款的限额。另外还要存储客户通过的代理编号及姓名。（注：每个代理商可以代理多个客户，而每个客户只能通过一个代理商）。

3．对于产品，需要存储：产品的编号、描述信息、库存量、类别、仓库的编号以及价格。

4．每份订单需要存储：订单编号、订货日期、客户编号、姓名以及地址、通过的代理的编号。而且对于订单的每一个订货项，需要存储产品编号、描述信息、订货的数量以及协商的价钱。

说明：

（1）每份订单的客户都已经存储在客户文件中。 （2）每份订单只限于一个客户。

（3）每种产品在一份订单中最多出现一次。

（4）协商后的价钱可能和产品管理文件中的相同，但也可能不同。即允许把同一种产品按不同的价格卖给不同的客户，同时也允许修改产品的基本价格，而无须改变文件中的订货信息。

三．原始数据

1．对于代理商（代理商编号、姓名、地址、邮政编码、提成金额、提成比例） 01、联邦、东环路1号、东环路1号、541001，30000.00、40 02、惠普、东环路2号、541002、4000.00、10 03、三洋、东环路3号、541003、10000.00、30 04、联想、东环路4号、541004、100000.00、60 05、海尔、东环路5号、541005、200000.00、60

2．客户（客户编号、姓名、地址、邮政编码、收支差额、贷款限额、代理商编号） 100、张三、西环路1号、100001、10.00、100.00、02 200、李四、西环路2号、100001、-10.00、10.00、04 300、王五、西环路3号、100001、100.00、1000.00、02 400、赵六、西环路4号、100001、600.00、2000.00、01 500、洪七、西环路5号、100001、300.00、900.00、05 600、李明、西环路6号、100001、20.00、300.00、03 700、张进、西环路7号、100001、400.00、1000.00、03

3．产品（产品的编号、描述信息、库存量、类别、仓库的编号、价格） 0011、药物,单位(瓶)、1000、12、1001、40.00

４

0022、机器,单位(件)、300、3、1002、50000.00 0033、中药,单位(包)、800、12、1001、300.00 0044、软件,单位(套)、1500、10、1003、2000.00 0055、家具,单位(件)、6000、3、1002、1000.00

0066、小型机,单位(台)、10000、3、1002、200000.00

4．订单（订单编号、订货日期、客户编号） 111、2000-10-01、200 222、2000-09-01、200 333、2001-01-01、500 444、2002-02-02、300 555、2003-03-03、100

5．订货项目（订单编号、产品编号、订购数量、订购单价）

111、0033、200、280.00

222、0066、6000、150000.00 333、0033、100、280.00 444、0011、300、39.00 555、0055、5500、950.00 444、0044、1000、1900.00

５

实验一 SQL Server基本使用与数据定义

一．实验目的

1．掌握SQL SERVER中“对象资源管理器”基本使用方法； 2．熟悉数据库建模及E/R图的画法；

3．掌握SQL Server 中数据库、及数据表的建立与管理方法； 3. 掌握向表中进行数据更新的方法。

5．掌握数据的导入/导出及数据库备份/还原方法。

二．实验内容

一、SQL Server 基本使用

1．启动SQL SERVER。

从SQL Server 2005程序组中启动“SQL Server”，弹出如下对话框。 按提示执行以下三项操作：

? 选择服务器

? 选择身份验证模式（SQL Server身份验证，指定登录名和密码）

在“服务器”列表框中，选择服务器类型，选择输入相应的服务器名称，选择身份验证模式。如图1-1：点击连接。

图1-1

2．对象资源管理器

连接完成后，进入对象资源管理器界面，如图1-2

６

图1-2

3、在对象资源管理器的使用

点开服务器名称，打开树状结构，可以看见服务器上的数据库对象和其他对象。

4．创建查询

SQL Server提供了许多工具，可以用于管理服务器和客户机、开发数据库和应用程序。查询它可以通过图形界面执行SQL语句。

创建查询后在其中可以交互式地输入和执行各种SQL语句，并且可以在一个窗口中同时查看SQL语句和其结果集；可以在同时执行多个SQL语句，也可以执行脚本文件中的部分语句；

打开方法：创建SQL查询分。如下图1-3所示，可在工具栏中选择要操作的数据库（如master），并在查询窗口中输入相应的SQL语句（select * from sysfiles），点击工具条的绿色小三角运行你输入的SQL语句。此时在查询窗口的下部会显示执行结果。

７

图1-3

查询结果的显示方式，可通过查询菜单设置：文本显示结果、以表格显示结果或结果保存为文件。

5．查看数据库及浏览表中记录 查看已建立的数据库

在对象资源管理器中,单击服务器左侧的+号，选择“数据库”选项，可以看到在数据库

服务器中已建立的数据库。

浏览表中记录

⑴ 选择某数据库并选择数据库中的“表”选项 。

⑵ 选择要浏览的表，按右键并选择：打开表/返回所有行 ，即可浏览表中记录。

８

二、数据库的建立与管理

1．数据库建模

依据数据库设计总体需求及原始数据，在实验前完成数据库的建模工作（写出预习报告）。具体要求如下：

(1)．设计订单作为联系的E/R图。 (2)．设计订单作为实体的E/R图。 (3)．标出E/R图中各实体的键码。

(4)．将订单作为实体的E/R图转换为关系模型。

3．使用图形界面创建数据库和表

使用图形界面（对象资源管理器）并依据内容1中的关系模型创建数据库和表。

创建数据库

右击“数据库”选项，点击“新建数据库”，在弹出对话框中输入你要创建的数据库名(例如mybatabase)后直接按确定键。这时系统就会创建一个名为mydatabse的数据库。 创建表

⑴ 在数据库中。右击“表”选项。

⑵ 选择“新建表” ，输入：列名、数据类型、长度等，并存盘。

4．使用查询分析创建表 ⑴. 点击创建查询；

⑵. 在出现的“查询窗口”中选择你要操作的数据库，然后就可以在下面的编辑框中输

入SQL语句建立相关表。

⑶. 点击工具条的绿色小三角（或按F5）就可以运行你输入的SQL语句。

5．数据录入

[1] 直接打开表进行数据录入；

[2] 使用SQL语句进行数据插入、删除及修改（写出相关语句）。

6．数据库的备份与还原

数据库的备份：

⑴ 运行对象资源管理器，右击你所要备份的数据库，选择“任务”中的“备份数据库”。

９

⑵ 输入备份数据库的名称，点击“添加”后输入备份数据库存放的目录和文件名。 ⑶ 按确定按钮，备份完成。

数据库的还原：

一旦数据库遭到破坏，可用备份的数据库进行恢复。 ⑴ 运行对象资源管理器，右击“数据库”，在“任务”中选“还原数据库”。

⑶ 在“常规”页中输入恢复的数据库名，选择“从设备” 还原，点击“选择设备”后，

再点击“添加”按钮选择你所要恢复的数据库备份文件名。 ⑷ 点击确定按钮完成恢复。

如果原有数据库被删除，恢复异常。则可先建立一个同名数据库，再在数据库上进行强制还原即可。

１０

实验二 SQL查询与视图

一．实验目的：

1．掌握SQL语言中SELECT语句的多种查询方式。 2．掌握对表建立与删除索引的方法。 3．掌握聚集函数的使用方法。 4．掌握集合查询方法。

5. 掌握视图的创建与查询方法。

二．实验内容：

1．建立与删除［索引］的方法。

在各表中，分别按代理商编号、客户编号、产品编号及订单编号建立索引。

2．单表查询，

① 从产品表中查询现有产品的库存量。

② 从客户表中查询“王五”的地址及代理商编号。 ③ 从代理商表中查询代理商“惠普”的提成金额。

④ 从订货项目表中查询编号为“444”的订单所订购的商品编号及数量。⑤ 分别求代理商和客户的总数。

⑥ 从订货项目表中，查询编号为0033的产品定货总数量。

3．多表查询

① 查询编号为300的客户通过的代理商的姓名和地址。

② 查询产品编号为0033、订货数量大于100的客户编号及其姓名。

③ 查询每个客户的订单，显示客户编号、客户名、订单编号及订货日期。④ 查询所有2000年以后订货项目。

⑤ 查询提成金额为100000以上（含100000）的代理商所对应的客户。

4、嵌套查询

分别用带有IN谓词的子查询和带比较运算符的子查询实现以下操作 ① 查询与“刘晨”在同一个系学习的学生。 ② 查询选修了课程名为“操作系统”的学生。

5．视图的建立与查询

① 建立含有订单号及拥有该订单的客户编号和代理商号三个字段的视图。 ② 查询所有订单的客户和代理商。

③ 建立一个选修了课程1或者选修了课程2的学生视图。 ④ 查询选修课程1的所有学生。

１１

实验三 数据库的安全

一．实验目的

1．掌握SQL Server 中数据库安全性管理的策略与方法。

一．实验目的

1．掌握SQL Server 中数据库安全性管理的策略与方法。

二．实验内容

数据库的安全主要包括几个方面的内容：安全模式,SQL Server服务器安全, SQL Server

登录帐号, 角色, SQL Server用户和许可等内容. 安全模式包括如下内容: (1) 安全规划:

(2) 选择安全的形式:指选择用户登录的登录认证方式.

1．认证模式

认证是指当用户访问数据库系统时，系统对该用户的帐户和口令的确认过程。SQL Server可以识别两种类型的登录认证机制。

● SQL Server认证机制 ● Windows NT认证机制

当使用SQL Server认证机制时，SQL Server系统管理员定义SQL Server的登录帐户和口令。当用户连接SQL Server时，必须提供登录帐户和口令。

当使用Windows NT认证机制时，由Windows NT帐户控制用户对SQL Server系统的访问。这时用户不必提供SQL Server的登录帐户和口令就能连接到系统上，但是，在该用户连接之前，SQL Server系统管理员必须将Windows NT帐户定义为SQL Server的有效登录帐户（请参考Windows NT帐户管理方法）。

用户可以使用SQL Server企业管理器来设置服务器的认证模式。设置方法参见实验一。

2．登录管理

登录帐户是基于SQL Server服务器使用的用户名，可控制用户对数据库服务器的访问。在SQL Server中有一个特殊的登录帐户：sa，是系统管理员的简称，它在SQL Server系统和所有数据库中有全部的权限，不能被删除。

设置sa帐号密码: 单击相应服务器属下的 “Login”;右单击右窗格的用户名,选择 “属性”.

增加登录帐户的两种方法：

一是：从Windows NT组和用户中创建登录帐户和创建新的SQL Server登录帐户（参考Windows NT帐户管理方法）。

二是：使用SQL Server对象资源管理器创建登录帐户，方法如下： 在SQL Server对象资源管理器选择一服务器，选择“安全性”文件夹，在“安全性”文件夹中，右击“登录”，选择“新建登录”，则出现如下窗口：

１２

在常规选项卡中输入登录名和口令；在服务器角色选项卡中选择相应服务器角色复选框；在数据库访问选项卡中选择要访问的数据库及指定该帐户所属的数据库角色。最后按[确定]即可。

用刚建立的登录帐户登录服务器：

先右击服务器，选择断开，再右击服务器，选择连接。按提示输入帐户名和口令即可登录。如果选择连接时不提示输入帐户/口令，可选择强制用户登录时验证帐户和口令（参见5.）

(3)配置安全角色

角色：SQL Server2000服务器操作和数据库访问许可的管理单位，角色可以把各个用户汇集成一个单元，以便进行许可管理。SQL Server提供了用于通常管理工作的预定义服务器角色、和数据库角色，以简化对某一个用户授予一些管理许可的工作。可在建立登录帐户时设置。

系统管理员给适当用户分配相应的角色是SQL Server服务器和数据库安全的关键之一. SQL Server2005服务器系统管理员或数据库所有者在设置数据库访问许可时,应先创建新的角色并将访问许可集中授予角色,然后再将需要拥有这一许可权限的用户加入到角色中, 加入到角色中的所有用户自动具有角色所拥有的访问许可权限.

常用固定服务器角色如下（通过“服务器角色”设置）

１３

服务器角色 Bulkadmin Dbcreator Diskadmin Processadmin Securityadmin Serveradmin Setupadmin 含义（许可） 可以运行 BULK INSERT 语句 创建、更改、删除和还原任何数据库 管理磁盘文件 可以终止在数据库引擎 实例中运行的进程 可以管理登录名及其属性。具有 GRANT、DENY 和 REVOKE 服务器和数据库级别的权限。此外，还可以重置 SQL Server 登录名的密码 可以更改服务器范围的配置选项和关闭服务器 可以添加和删除链接服务器，并可以执行某些系统存储过程（如 sp_serveroption） 在SQL Server中进行任何活动。该角色的权限跨越所有其他固定服务器角Sysadmin 色。默认情况下，Windows BUILTIN\\Administrators 组（本地管理员组）的所有成员都是 sysadmin 固定服务器角色的成员

固定的数据库角色及许可（通过“用户映射-指定数据库-数据库角色成员”） 数据库角色 Public db_owner db_accessadmin db_datareader db_datawriter db_ddladmin db_securityadmin db_backoperator db_denydatareader db_denydatawriter 含义（许可） 维护全部默认的许可 可以执行数据库中技术所有动作的用户 可以添加、删除用户的用户 可以查看所有数据库中用户表内数据的用户 可以添加、修改或删除所有数据库中用户表内数据的用户 可以在数据库中执行所有DDL操作的用户 可以管理数据库中与安全权限有关所有动作的用户 可以备份数据库的用户(并可以发布DBCC和CHECKPOINT语句，这两个语句一般在备份前都会被执行) 不能看到数据库中任何数据的用户 不能改变数据库中任何数据的用户

创建角色方法:用户只能创建数据库角色,只在其所在的数据库中有效,对其他数据库无效.

右单击 “角色”

授权：授予用户，用户组以及角色某种或某些许可权限，权限：一种对数据库对象的操作权

力。

用户登录到SQL Server服务器后，其所充当的角色和用户的许可，就决定了它们对数据库对象所能执行的操作。添加角色到固有角色，角色就具有相应固有角色所具有的授权。 方法：右键单击需将角色添加到其属下的固有角色。

“属性”---按“添加”. 创建SQL Server用户方法：右键单击需将创建角色的数据库属下的的 “用户”. SQL Server用户只能用于访问数据库，不能用于登录

4．许可管理

许可的种类：对象许可，数据库许可和固有角色的隐含许可

１４

在SQL SERVER 系统中，把权利定义为许可，通过实行许可管理，维护数据的有序流动。可在用户权限中设定。

设置对象许可的方法：右键单击需设置许可的对象（以数据表为例） -----“属性”---单击相关项目

设置语句许可的方法：右键单击需设置语句许可的数据库名 -----“属性”---单击相关项目 授权

SELECT 授予、废除或拒绝对该对象的 SELECT 权限。 INSERT 授予、废除或拒绝对该对象的 INSERT 权限。 UPDATE 授予、废除或拒绝对该对象的 UPDATE 权限。 DELETE 授予、废除或拒绝对该对象的 DELETE 权限。 EXEC 授予、废除或拒绝对该对象的 EXECUTE 权限。 DRI 授权、废除或拒绝对该对象的声明引用完整性权限。

实验完成规划实施某部门SQL Server安全策略 具体要求如下： 1. 部门总监（1人）：作为系统管理人员，允许任何操作。 名称：

服务器角色： 数据库角色： 权限：

2. 服务器登录安全管理员（1人）：可设置和管理用户登录帐号，授权访问订单数据库。 3. 数据库维护人员（1人）：可对订单数据库进行任何操作。 4. 数据录入人员（2人）：可进行数据录入，不能创建与修改表结构及其它授权等操作。 5. 订单管理人员（2人）：能对订单数据库中的订单表和项目表进行更新，其它表仅能查询。

6. 客户管理人员（2人）：能对订单数据库中的代理商表和客户表进行更新，其它表

注意：以上人员的登录帐号和密码统一规划设置。

１５

本文来源：https://www.bwwdw.com/article/9zmg.html