《数据库技术及应用》实验指导书Word下载.docx
《《数据库技术及应用》实验指导书Word下载.docx》由会员分享，可在线阅读，更多相关《《数据库技术及应用》实验指导书Word下载.docx（23页珍藏版）》请在冰豆网上搜索。
客户ID，客户名称，联系电话，客户等级
9．职员表：
职员ID，职员姓名，职员类型
实验一SQLServer安装及管理工具使用
一、实验目的与要求
1．熟悉SQLServer的基本安装与配置过程
2．熟悉SQLServer企业管理器的主要功能、对数据库的组织和管理方法
3．熟悉SQLServer查询分析器的主要功能、对数据库的组织和管理方法
3．掌握基本表、主码等基本概念
4．掌握SQL语言的数据定义功能，要求能使用企业管理器和查询分析器两种方法来建立数据库、以及数据表。
5．掌握修改和删除表结构的方法
二、实验内容
//1．学习使用SQLServer的基本管理工具：
企业管理器和查询分析器
2．掌握SQLServer默认安装的各个数据库的主要作用
（1）master数据库
（2）model数据库
（3）msdb数据库
（4）tempdb数据库
3．使用企业管理器和查询分析器建立、删除数据库
（1）建立一个名称为supermarket的数据库
（2）查看与新生成的数据库相关的内容
（3）删除名称为supermarket的数据库
4．使用企业管理器和查询分析器建立、删除数据表
（1）在已经建立的supermarket数据库的基础上，建立以下数据表（参考附录A）：
（a）商品表commodity
（b）供应商表supplier
（c）供应表supplying
（d）采购单表stock
（e）采购明细表stockDetail
（f）销售单表sale
（g）销售明细表saleDetail
（h）客户表customer
（i）职员表Employee
//
（2）使用企业管理器查看新生成表的结构和内容
（3）分别使用企业管理器和查询分析器修改表结构SQL
（a）在商品表中加入描述商品信息的属性
（b）将客户表中的客户联系电话（CUtelephone）的数据类型改为varchar（20）
（4）分别使用企业管理器和查询分析器删除基本表
（a）使用企业管理器删除客户表
（b）使用查询分析器删除商品表
三、自我测试
1．使用企业管理器新建一个名称为“test”的数据库
2．使用查询分析器在test数据库建立以下数据表
（1）学生表：
学号，姓名，性别，出生日期，系别
（2）课程表：
课程编号，课程名称，学分
（3）选课表：
学号，课程号，成绩
3．使用查询分析器对表进行修改
（1）将学生表中的性别数据类型改为整型
（2）为学生表增加一个新的属性列，用于存放学生的专业信息
实验二数据插入、修改和删除
1．掌握SQLServer的数据更新功能，能使用INSERT、UPDATE、DELETE命令对数据表中的数据进行更新
2．掌握INSERT…SELECT和SELECT…INTO语句的使用方法
1．使用INSERT命令向基本表中插入数据（参考附录B）
（1）向供应商表中插入如下数据记录：
（2）向商品表中插入如下数据记录
（3）向职员表中插入如下数据记录
（4）向供应表中插入如下数据记录
（5）使用企业管理器分别向客户表、采购表、采购明细表、销售表、销售明细表中添加相应记录。
客户表
采购单表
采购明细表
销售单表
销售明细表
2．使用UPDATE命令修改基本表中的数据
（1）将编号为‘F70006’的商品的单价改为12.00元
（2）将名称为‘家乐福’的客户的等级改为VIP客户。
（3）将编号为‘A102’供应商供应商品的价格全部打八折
3．使用DELETE命令删除基本表中的数据
（1）删除采购明细表中的所有记录
（2）从采购单表中删除2005年以前签订的采购单
4．使用SELECT…INTO语句向表中成批插入记录
（1）将供应商‘A102’供应的商品和供应价格存到一个名字为‘A102_commodity’的表中
（2）将所有的VIP客户信息存储到一个名字为‘VIP_Customer’的表中
5．使用INSERT…SELECT语句向表中成批插入记录
（1）新建一个名称为‘achievement’的表，其属性包括销售单ID，销售单总金额，销售日期
（2）将编号为‘S0002’的销售员签订的销售单信息存储到achievement表中。
1．从采购明细表中删除所有从供应商‘A153’处采购的编号为‘F70006’商品的明细信息
2．从销售明细表中删除2005年以前签订的销售单的销售明细记录
实验三单表查询
1．掌握基本的SELECT…FROM语句的用法
2．能应用SELECT语句及其子句解决常见的查询问题
3．掌握模糊查询的使用方法
4．能应用聚集函数和GROUPBY子句进行统计查询
1．查找所有商品的名称和库存信息
2．查找‘北京’地区的供应商
3．查找所有销售人员的基本信息
4．查找签订日期在‘2005-5-1’和‘2006-3-1’之间签订的采购单的基本信息
5．查找2006年签订的所有销售单，要求显示销售单ID、供应商ID和销售单总金额
6．查找每个采购员所签订的采购单的数目，显示结果按采购单数目降序排序
7．查询每个销售员签订的销售单总金额
8．查找2005年与每个客户签订的销售单的总金额，只显示总金额大于500000万的客户ID和总金额
9．查询2005年签订的金额最大的一笔销售单，要求显示采购员ID、客户ID和销售金额
10．查询2005年与编号为‘A002’的供应商签订的所有采购单的总金额
11．查询名称中含有‘机’字的所有商品的ID、名称和库存量
12．查找第二个汉字是‘翔’的商品名称和单价。
1．查询2005年签订的销售合同总额排名前5名的销售员的ID和合同总额，要求结果按照合同总额降序排列
2．查询至少供应了3种商品的供应商的ID
3．查询在2006年至少已经与2名销售员签订了合同的客户ID
实验四表连接查询
1．掌握表连接查询的使用方法
2．掌握子查询的使用方法
3．能使用内连接、左外连接、右外连接以及交叉连接解决相关问题
4．熟悉系统函数的使用
1．查找每一个供应商供应的商品的名称，要求显示供应商名称和商品名称。
2．使用连接查询查找与姓名为‘李云’的采购员签订采购单的供应商的名称。
3．使用关键字IN完成查询，要求查找与姓名为‘李云’的采购员签订采购单的供应商的名称。
4．使用左外连接完成如下查询：
要求查找所有采购员签订的采购合同的详细信息。
5．使用左外连接完成如下查询：
查找所有客户购买的商品详细信息，要求显示客户名称，
商品名称，销售数量，商品单价，没有购买商品的客户也要显示。
6．请使用内连接完成如下查询：
查找每一个供应商供应的商品的种类，要求显示供应商名称，供应的商品的种类。
7．查找购买了编号为‘A001’的供应商供应的商品的客户名称
8．查找销售员‘王良’在2005年签订的销售合同的详细信息。
1．使用右外连接完成如下查询：
要求查找所有采购员签订的采购合同的详细信息，没有签订采购单的采购员也要显示
2．查找购买了名称为‘联想集团’的供应商供应的商品的客户名称
实验五表综合查询使用
1．能熟练应用SELECT语句及其相关字句
2．能将SELECT与系统函数、IFELSE、WHILE等语句进行综合应用并解决相关问题
1．查询所有供应‘鲜橙多’的供应商的最低报价。
2．查找销售员‘王良’在2005年签订的所有销售合同中每一类商品的总金额。
3．汇总由姓名为‘刘明’的采购员在2004年采购的‘数码相机’的总金额。
4．汇总由姓名为‘刘明’的采购员在2005年采购的各类商品的数量。
5．查找没有供应任何一类商品的供应商的名字。
6．查找在2006年各个客户购买商品的总金额，要求结果按照购买商品的总金额降序排序
7．请使用左连接完成以下查询：
查找每一个销售人员销售的商品的详细信息，要求显示销售人员姓名、销售单ID、客户姓名、商品名称、销售数量、和销售单价。
8．查找每个采购员和每个供应商签订的合同的总金额，要求显示采购员姓名、供应商名称、和签订合同的总金额。
1．查找编号为‘A002’的供应商没有供应的商品的名称
2．分别使用UNION、和INNERJOIN关键字完成以下查询：
查询由‘三高计算机公司’和‘联想集团’供应商的所有商品的商品ID。
实验六视图与索引
1．熟悉视图的概念，以及视图与表的联系和区别
2．掌握视图的定义方法
3．掌握对视图的查询操作
4．掌握对视图的更新操作，并了解视图的更新条件
5．掌握同时对视图和表进行操作的方法
6．熟悉索引的概念与作用
7．掌握索引的建立与删除操作
1．定义一个名称为‘supplying_view’的视图，要求其对应的数据是由‘天天食品公司’供应的商品ID、商品单价。
2．查询采购员‘李云’和供应商‘神州数码有限公司’签订的所有采购单的采购单ID、采购金额、签订日期，并将其建立为一个名称为‘stock_view_1’的视图。
3．建立一个名称为‘commodity_100’的视图，其对应所有的库存量小于100的商品的信息。
4．使用视图supplying_view和相关基本表查找由供应商‘天天食品公司’供应的商品ID、商品名称和商品单价。
5．使用视图‘stock_view_1’和相关基本表查询‘李云’和供应商‘神州数码有限公司’签订的所有采购单的采购明细信息，要求显示采购单ID、商品ID、商品名称、采购数量、和商品单价。
6．为supermarket数据库中的任意一个表建立索引（按升序建唯一索引号）。
7．使用DROPINDEX语句删除新建的索引
1．建立一个视图，其对应的数据是在2005年签订的销售单总额超过100万的销售员的ID和姓名。
2．使用上述建立的视图和NOTIN关键字查询在2005年签订的销售单总额没有超过100万的销售员的ID和姓名。
实验七数据库的安全及管理
一、【实验目的】
1、掌握SQLServer中数据库安全性管理的策略与方法。
二、【实验原理】
数据库安全机制
数据库的安全主要包括几个方面的内容：
用户登录系统的管理、用户使用数据库对象的管理及用户许可。
用于保护用户数据不受外部侵害。
1、认证模式
认证是值当用户访问数据库系统是，系统对该用户的帐号和口令的确认过程。
SQLServer可以识别两种类型的登录认证机制。
●SQLServer认证机制
●WindowsNT认证机制
当使用SQLServer认证机制时，SQLServer系统管理员定义SQLServer的登录帐户和口令。
当用户连接SQLServer时，必须提供登录帐户和口令。
当使用WindowsNT认证机制时，由WindowsNT帐户控制用户对SQLServer系统的访问。
这是用户不必提供SQLServer的登录帐户和口令就能连接到系统上，但是在该用户连接之前，SQLServer系统管理员必须将WindowsNT帐户定义为SQLServer的有效登录帐户（请参考WindowsNT帐户管理方法）。
用户可以使用SQLServer企业管理器来设置服务器的认证模式。
设置方法参见实验一
2、登录管理
登录帐户是基于SQLServer服务器使用的用户名，可控制用户对数据库服务器的访问。
在SQLServer中由一个特殊的登录帐户：
sa，是系统管理员的简称，它在SQLServer系统和所有数据库中有全部的权限，不能被删除。
增加登录帐户的两种方法：
（1）从WindowsNT组和用户中创建新的SQLServer登录帐户（参考WindowsNT帐户管理方法）。
（2）使用SQLServer企业管理器创建登录帐户，方法如下：
在SQLServer企业管理器选择一服务器，选择“安全性”文件夹，在“安全性”文件夹中，右击“登录”，选择“新建登录”。
在常规选项卡中输入登录名和口令；
在服务器角色选项卡中选择相应服务器角色复选框；
在数据库访问选项卡中选择要访问的数据库及指定该帐户所属的数据库角色。
最后按[确定]即可。
用刚建立的登录帐户登录服务器：
先右击服务器，选择断开，再右击服务器，选择连接。
按提示输入帐户名和口令即可登录。
如果选择连接时不提示输入帐户/口令，可选择强制用户登录时验证帐户和口令。
3、角色管理
角色可以把各个用户汇集成一个单元，以便进行许可管理。
SQLServer提供了用于通常管理工作的预定义服务器角色和数据库角色，以简化对某一个用户授予一些管理许可的工作。
可以在建立登录帐户时设置。
固定的服务器角色及许可：
常用固定服务角色如下
角色
许可
系统管理员（systemadministrator）
执行任意活动
数据库创立者（databasecreator）
创建和修改数据库
磁盘管理员（diskadministrator）
管理磁盘文件
进程管理员（processadministrator）
管理系统进程
安全管理员（securityadministrator）
管理和审核服务器登录
服务器管理员（serveradministrator）
配置服务器端设置
安装管理员（setupadministrator）
安装复制
固定的数据库角色及许可：
常用固定数据库角色及其许可
角色
许可
Public
维护全部默认的许可
Db_owner
执行任何数据库角色的操作
Db_accessadmin
增加或者删除数据库用户、组和角色
Db_dlladmin
增加、修改或者删除数据库对象
Db_securityadmin
执行语句和对象许可
Db_backupoperator
备份和恢复数据库
Db_datareader
读取任意表中的数据
Db_datawriter
增加、修改或者删除全部表中的数据
Db_denydatareader
不能读取任一表中的数据
Db_denydatawriter
不能修改任一表中的数据
4、许可管理
在SQLServer系统中，把权利定义为许可，通过实行许可管理，维护数据的有序流动。
可在用户权限中设定。
5、强制用户登录时验证用户名及口令
右击服务器，选择“编辑SQLServer注册属性”，选择“总是提示输入登录名和密码”，再重新登录即可。
三、【实验内容】
使用自己已经创建的数据库，操作并实现数据库的安全性管理。
（1）用户登录系统的管理；
（2）用户使用数据库对象的管理；
（3）用户许可管理，用于保护用户数据不受外部侵害。
实验八使用PowerDesigner设计E-R图
一、实验目的
●使用PowerDesigner软件的DataArchitect工具创建概念数据模型
二、实验内容
2.1相关知识
概念数据模型（CDM）设计是建模过程的关键阶段，此阶段把现实世界中需要保存的信息抽象成信息世界中的实体和联系，产生实体联系图，这一阶段可以为高质量的应用提供坚实的基础。
联系的基数
联系的基数可以是一对一（1:
1）、一对多（l:
N）或多对多（N:
M）三种类型。
上图（a）表示教师与课程的联系“教”是一对多的，即每个教师可以教多门课程，而每门课程只能由一个教师来教；
图中还表示了学生与课程的联系“修”，它是多对多的，即每个学生可以修多门课程，而每门课程可以有多个学生来修。
联系的强制
如果联系在某一端是必须和实体关联的，比如，每门课程只能由一个教师来教，并且，也必须有一个老师来教，那么在联系“教”中，连接实体“老师”一侧，就需要是强制的；
而每个教师可以教多门课程，也有些老师允许不教课程，那么在联系“教”的另一端，就不需要是强制的。
物理数据模型
概念数据模型建立之后，可以用PowerDesigner生成物理数据模型（PDM）。
物理数据模型考虑了数据库的物理实现，包括软件和数据存储结构。
2.2熟悉PowerDesigner环境
PowerDesigner的环境分成几个区域：
左边的树形模型浏览器用分层结构显示你的工作空间，当你在工程中加入实体和联系后，会看到工作空间中也增加了相应的资源；
右边的大片区域是工作面板，用来完成实体联系建模；
在工作面板上，漂浮有工具棒，如下图所示，其中包括“实体”工具和“联系”工具，我们在本次实验中，主要应用这两个工具。
新建一个工程：
→ConceptualDataModel，开始建立概念数据模型，也就是ER模型。
2.3实体联系（ER）模型的步骤
2.3.1定义实体的步骤如下：
1）在工具棒上单击“实体”工具，然后在工作面板上任何地方点击鼠标左键，这样，一个新的“实体”就被添加到工程中。
2）在工作面板上任何地方点击鼠标右键，可以释放“实体”工具。
3）双击增加的实体，可以弹出实体属性窗口，输入Name,Code,Label的内容。
4）点击“属性”页面，为实体增加属性，比如学生实体，可以增加“学号”、“姓名”、“年龄”、“性别”等。
5）在每个属性条目中，可以为其选择数据类型。
6）单击“确定”完成这个实体的编辑。
2.3.2定义联系的步骤如下：
1）单击工具棒上的“联系”工具，在工作面板上，将鼠标从要添加联系的两个实体的一个实体拖到另一个实体上。
2）在工作面板上任何地方点击鼠标右键，可以释放“联系”工具。
3）双击联系线，可以弹出实体属性窗口，输入Name,Code,Label的内容。
4）为联系的每个方向键入动词或者动词短语。
5）为每个方向的联系选择基数和是否强制。
2.4建立如下系统的实体联系（ER）模型
销售员主要负责将采购来的商品销售给客户并签订销售单，显然一个客户一次可能购买多种商品。
附录A建表SQL语句
--1供应商表
CREATETABLEsupplier（
Sidchar（10）PRIMARYKEY,--供应商ID
Snamechar（30）,--供应商名称
Sareachar（30）,--供应商地区
Screditint--供应商信誉等级:
1,良好；
2，一般；
3，较差
）
--2供应表
CREATETABLEsupplying（
Sidchar（10）,--供应商ID
Cidchar（10）,--商品ID
pricefloat,--商品单价
PRIMARYKEY（Sid,Cid）
--3商品表
CREATETABLEcommodity（
Cidchar（10）PRIMARYKEY,--商品ID
Cnamechar（30）,--商品名称
Cstorageint,--商品库存量
avgPricefloat--商品均价
--4采购单表
CREATETABLEstock（
CGidchar（12）PRIMARYKEY,--采购单ID
Eidchar（6）,--采购员ID
CGmoneyfloat,--采购金额
CGdateDateTime--签订日期
--5采购明细表
CREATETABLEstockDetail（
CGidchar（12）,--采购单ID
SDnumberint,--采购数量
SDpricefloat,--采购单价
PRIMARYKEY（CGid,Cid）
--6销售表
CREATETABLEsale（
SAidchar（12）PRIMARYKEY,--销售单ID
Eidchar（6）,--销售员ID
CUidchar（10）,--客户ID
SAmoneyfloat,--销售金额
SAdateDateTIme,--签订日期
--7销售明细表
CREATETABLEsaleDetail（
SAidchar（12）,--销售单ID
SDnumberint,--销售数量
SDpricefloat,--销售单价
SDdiscountfloat,--折扣
PRIMARYKEY（SAid,Cid）
--8客户表
CREATETABLEcustomer（
CUidchar（10）PRIMARYKEY,--客户ID
CUnamechar（30）,--客户姓名
CUtelephonechar（16）,--客户联系电话
CUlevelint--客户等级：
1，普通客户；
2，VIP客户
--9职员表
CREATETABLEemployee（
Eidc
升级会员 