2013《大数据库技术及应用》实验指导书

word数据库技术与应用实验指导书实验环境1软件需求1操作系统：Windows 2000 Professional，或者Windows XP2数据库管理系统：SQL Server20004其它工具：Word2硬件需求1PC机2网络环境根本需求信息一、对某商场采购销售管理进展调研后，得到如下根本需求信息： 该商场有多名工作人员主要是采购员和销售员，主要负责从供给商处采购商品，而后将商品销售给客户。采购员主要负责根据商场的销售情况确定要采购的商品，并与供给商联系，签订采购单。销售员主要负责将采购来的商品销售给客户，显然一个客户一次可能购置多种商品。一个供给商可以向该商场供给多种商品，而一种商品也可以由多个供给商供给。 商场的管理者每个月需要对该月已采购的商品和已销售的商品进展分类统计，对采购员和销售员的业绩进展考核，对供给商和客户进展等级评定，并计算商场利润。二、E-R图三、需要建立的数据表如下1 供给商表：供给商ID，供给商名称，地区，信誉等级2 供给表：供给商ID，商品ID，商品单价3 商品表：商品ID，商品名称，商品库存量，商品均价4 采购单表：采购单ID，采购员ID，供给商ID，采购总金额，签订日期5 采购明细表：采购单ID，商品ID，采购数量，商品单价6 销售单表：销售单ID，销售员ID，客户ID，销售总金额，签订日期7 销售明细表：销售ID，商品ID，销售数量，商品单价，单价折扣8 客户表：客户ID，客户名称，联系，客户等级9 职员表：职员ID，职员，职员类型实验一SQL Server安装与管理工具使用一、实验目的与要求1 熟悉SQL Server的根本安装与配置过程2 熟悉SQL Server企业管理器的主要功能、对数据库的组织和管理方法3 熟悉SQL Server查询分析器的主要功能、对数据库的组织和管理方法3掌握根本表、主码等根本概念4掌握SQL语言的数据定义功能，要求能使用企业管理器和查询分析器两种方法来建立数据库、以与数据表。5掌握修改和删除表结构的方法二、实验容/1学习使用SQL Server的根本管理工具：企业管理器和查询分析器2掌握SQL Server默认安装的各个数据库的主要作用 1master数据库2model数据库3msdb数据库4tempdb数据库3使用企业管理器和查询分析器建立、删除数据库 1建立一个名称为supermarket的数据库 2查看与新生成的数据库相关的容 3删除名称为supermarket的数据库4使用企业管理器和查询分析器建立、删除数据表1在已经建立的supermarket数据库的根底上，建立以下数据表参考附录A： a商品表 modityb供给商表 supplierc供给表 supplyingd采购单表 stocke采购明细表 stockDetailf销售单表 saleg销售明细表 saleDetailh客户表 customeri职员表 Employee/2使用企业管理器查看新生成表的结构和容3分别使用企业管理器和查询分析器修改表结构 SQL a在商品表中参加描述商品信息的属性 b将客户表中的客户联系CUtelephone的数据类型改为varchar(20)4分别使用企业管理器和查询分析器删除根本表 a使用企业管理器删除客户表 b使用查询分析器删除商品表三、自我测试1 使用企业管理器新建一个名称为“test的数据库2 使用查询分析器在test数据库建立以下数据表1学生表：学号，性别，出生日期，系别2课程表：课程编号，课程名称，学分3选课表：学号，课程号，成绩3 使用查询分析器对表进展修改1将学生表中的性别数据类型改为整型2为学生表增加一个新的属性列，用于存放学生的专业信息实验二 数据插入、修改和删除一、实验目的与要求1掌握SQL Server的数据更新功能，能使用INSERT、UPDATE、DELETE命令对数据表中的数据进展更新2掌握INSERTSELECT和SELECTINTO语句的使用方法二、实验容1使用INSERT命令向根本表中插入数据参考附录B1向供给商表中插入如下数据记录：2向商品表中插入如下数据记录3向职员表中插入如下数据记录4向供给表中插入如下数据记录5使用企业管理器分别向客户表、采购表、采购明细表、销售表、销售明细表中添加相应记录。客户表采购单表采购明细表销售单表销售明细表2使用UPDATE命令修改根本表中的数据1将编号为F700062将名称为家乐福的客户的等级改为VIP客户。3将编号为A102 供给商供给商品的价格全部打八折3使用DELETE命令删除根本表中的数据1删除采购明细表中的所有记录2从采购单表中删除2005年以前签订的采购单4使用SELECTINTO语句向表中成批插入记录1将供给商A102供给的商品和供给价格存到一个名字为A102_modity的表中 2将所有的VIP客户信息存储到一个名字为VIP_Customer的表中5使用INSERTSELECT语句向表中成批插入记录1新建一个名称为achievement的表，其属性包括销售单ID，销售单总金额，销售日期2将编号为S0002的销售员签订的销售单信息存储到achievement表中。三、自我测试1从采购明细表中删除所有从供给商A153处采购的编号为F70006商品的明细信息2从销售明细表中删除2005年以前签订的销售单的销售明细记录实验三 单表查询一、实验目的与要求1掌握根本的SELECTFROM语句的用法2能应用SELECT语句与其子句解决常见的查询问题3掌握模糊查询的使用方法4能应用聚集函数和GROUP BY子句进展统计查询二、实验容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种商品的供给商的ID3查询在2006年至少已经与2名销售员签订了合同的客户ID实验四 表连接查询一、实验目的与要求1掌握表连接查询的使用方法2掌握子查询的使用方法3能使用连接、左外连接、右外连接以与交叉连接解决相关问题4熟悉系统函数的使用二、实验容1查找每一个供给商供给的商品的名称，要求显示供给商名称和商品名称。2使用连接查询查找与为云的采购员签订采购单的供给商的名称。3使用关键字IN完成查询，要求查找与为云的采购员签订采购单的供给商的名称。4使用左外连接完成如下查询：要求查找所有采购员签订的采购合同的详细信息。5使用左外连接完成如下查询：查找所有客户购置的商品详细信息，要求显示客户名称，商品名称，销售数量，商品单价，没有购置商品的客户也要显示。6请使用连接完成如下查询：查找每一个供给商供给的商品的种类，要求显示供给商名称，供给的商品的种类。7查找购置了编号为A001的供给商供给的商品的客户名称8查找销售员王良在2005年签订的销售合同的详细信息。三、自我测试1使用右外连接完成如下查询：要求查找所有采购员签订的采购合同的详细信息，没有签订采购单的采购员也要显示2查找购置了名称为联想集团的供给商供给的商品的客户名称实验五 表综合查询使用一、实验目的与要求1能熟练应用SELECT语句与其相关字句2能将SELECT与系统函数、IF ELSE、WHILE等语句进展综合应用并解决相关问题二、实验容1查询所有供给鲜橙多的供给商的最低报价。2查找销售员王良在2005年签订的所有销售合同中每一类商品的总金额。3汇总由为明的采购员在2004年采购的数码相机的总金额。4 汇总由为明的采购员在2005年采购的各类商品的数量。5 查找没有供给任何一类商品的供给商的名字。6 查找在2006年各个客户购置商品的总金额，要求结果按照购置商品的总金额降序排序7 请使用左连接完成以下查询：查找每一个销售人员销售的商品的详细信息，要求显示销售人员、销售单ID、客户、商品名称、销售数量、和销售单价。8查找每个采购员和每个供给商签订的合同的总金额，要求显示采购员、供给商名称、和签订合同的总金额。三、自我测试1查找编号为A002的供给商没有供给的商品的名称2分别使用UNION、和INNER JOIN关键字完成以下查询：查询由三高计算机公司和联想集团供给商的所有商品的商品ID。实验六 视图与索引一、实验目的与要求1熟悉视图的概念，以与视图与表的联系和区别2掌握视图的定义方法3掌握对视图的查询操作4 掌握对视图的更新操作，并了解视图的更新条件5 掌握同时对视图和表进展操作的方法6 熟悉索引的概念与作用7 掌握索引的建立与删除操作二、实验容1定义一个名称为supplying_view的视图，要求其对应的数据是由天天食品公司供给的商品ID、商品单价。2查询采购员云和供给商神州数码签订的所有采购单的采购单ID、采购金额、签订日期，并将其建立为一个名称为stock_view_1的视图。3建立一个名称为modity_100的视图，其对应所有的库存量小于100的商品的信息。4使用视图supplying_view和相关根本表查找由供给商天天食品公司供给的商品ID、商品名称和商品单价。5使用视图stock_view_1和相关根本表查询云和供给商神州数码签订的所有采购单的采购明细信息，要求显示采购单ID、商品ID、商品名称、采购数量、和商品单价。6为supermarket数据库中的任意一个表建立索引按升序建唯一索引号。7使用DROP INDEX语句删除新建的索引三、自我测试1建立一个视图，其对应的数据是在2005年签订的销售单总额超过100万的销售员的I D和。2使用上述建立的视图和NOT IN关键字查询在2005年签订的销售单总额没有超过100万的销售员的I D和。实验七 数据库的安全与管理一、【实验目的】1、 掌握SQL Server中数据库安全性管理的策略与方法。二、【实验原理】数据库安全机制数据库的安全主要包括几个方面的容：用户登录系统的管理、用户使用数据库对象的管理与用户许可。用于保护用户数据不受外部侵害。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系统和所有数据库中有全部的权限，不能被删除。增加登录的两种方法：（1） 从Windows NT组和用户中创建新的SQL Server登录参考Windows NT管理方法。（2） 使用SQL Server企业管理器创建登录，方法如下：在SQL Server企业管理器选择一服务器，选择“安全性文件夹，在“安全性文件夹中，右击“登录，选择“新建登录。在常规选项卡中输入登录名和口令；在服务器角色选项卡中选择相应服务器角色复选框；在数据库访问选项卡中选择要访问的数据库与指定该所属的数据库角色。最后按确定即可。 用刚建立的登录登录服务器： 先右击服务器，选择断开，再右击服务器，选择连接。按提示输入名和口令即可登录。如果选择连接时不提示输入/口令，可选择强制用户登录时验证和口令。3、 角色管理角色可以把各个用户聚集成一个单元，以便进展许可管理。SQL Server提供了用于通常管理工作的预定义服务器角色和数据库角色，以简化对某一个用户授予一些管理许可的工作。可以在建立登录时设置。固定的服务器角色与许可：常用固定服务角色如下 角 色 许 可系统管理员system administrator执行任意活动数据库创立者database creator创建和修改数据库磁盘管理员disk administrator管理磁盘文件进程管理员process administrator管理系统进程安全管理员security administrator管理和审核服务器登录服务器管理员server administrator配置服务器端设置安装管理员setup administrator安装复制固定的数据库角色与许可： 常用固定数据库角色与其许可角 色许 可Public维护全部默认的许可Db_owner执行任何数据库角色的操作Db_accessadmin增加或者删除数据库用户、组和角色Db_dlladmin增加、修改或者删除数据库对象Db_securityadmin执行语句和对象许可Db_backupoperator备份和恢复数据库Db_datareader读取任意表中的数据Db_datawriter增加、修改或者删除全部表中的数据Db_denydatareader不能读取任一表中的数据Db_denydatawriter不能修改任一表中的数据4、 许可管理在SQL Server系统中，把权利定义为许可，通过实行许可管理，维护数据的有序流动。可在用户权限中设定。5、 强制用户登录时验证用户名与口令右击服务器，选择“编辑SQL Server注册属性，选择“总是提示输入登录名和密码，再重新登录即可。三、【实验容】使用自己已经创建的数据库，操作并实现数据库的安全性管理。（1） 用户登录系统的管理；（2） 用户使用数据库对象的管理；（3） 用户许可管理，用于保护用户数据不受外部侵害。实验八 使用PowerDesigner设计E-R图一、 实验目的l 使用PowerDesigner软件的DataArchitect工具创建概念数据模型二、 实验容2.1 相关知识概念数据模型CDM设计是建模过程的关键阶段，此阶段把现实世界中需要保存的信息抽象成信息世界中的实体和联系，产生实体联系图，这一阶段可以为高质量的应用提供坚实的根底。联系的基数联系的基数可以是一对一1:1、一对多(l:N)或多对多(N: M)三种类型。上图 (a)表示教师与课程的联系“教是一对多的，即每个教师可以教多门课程，而每门课程只能由一个教师来教；图中还表示了学生与课程的联系“修，它是多对多的，即每个学生可以修多门课程，而每门课程可以有多个学生来修。 联系的强制如果联系在某一端是必须和实体关联的，比如，每门课程只能由一个教师来教，并且，也必须有一个教师来教，那么在联系“教中，连接实体“教师一侧，就需要是强制的；而每个教师可以教多门课程，也有些教师允许不教课程，那么在联系“教的另一端，就不需要是强制的。物理数据模型概念数据模型建立之后，可以用PowerDesigner生成物理数据模型PDM。物理数据模型考虑了数据库的物理实现，包括软件和数据存储结构。 2.2 熟悉PowerDesigner环境PowerDesigner的环境分成几个区域：左边的树形模型浏览器用分层结构显示你的工作空间，当你在工程中参加实体和联系后，会看到工作空间中也增加了相应的资源；右边的大片区域是工作面板，用来完成实体联系建模；在工作面板上，漂浮有工具棒，如如下图所示，其中包括“实体工具和“联系工具，我们在本次实验中，主要应用这两个工具。新建一个工程：FileNewConceptual Data Model，开始建立概念数据模型，也就是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供给商表CREATE TABLE supplier( Sid char(10) PRIMARY KEY, -供给商ID Sname char(30), -供给商名称 Sarea char(30), -供给商地区 Scredit int -供给商信誉等级:1,良好；2，一般；3，较差)-2 供给表CREATE TABLE supplying( Sid char(10), -供给商ID Cid char(10), -商品ID price float, -商品单价 PRIMARY KEY (Sid,Cid) )-3 商品表CREATE TABLE modity( Cid char(10) PRIMARY KEY, -商品ID ame char(30), -商品名称 Cstorage int, -商品库存量 avgPrice float -商品均价)-4 采购单表CREATE TABLE stock( CGid char(12) PRIMARY KEY, -采购单ID Eid char(6), -采购员ID Sid char(10), -供给商ID CGmoney float, -采购金额 CGdate DateTime -签订日期)-5 采购明细表CREATE TABLE stockDetail( CGid char(12), -采购单ID Cid char(10), -商品ID SDnumber int, -采购数量 SDprice float, -采购单价 PRIMARY KEY(CGid,Cid) )-6 销售表CREATE TABLE sale( SAid char(12) PRIMARY KEY, -销售单ID Eid char(6), -销售员ID CUid char(10), -客户ID SAmoney float, -销售金额 SAdate DateTIme, -签订日期)-7 销售明细表CREATE TABLE saleDetail( SAid char(12), -销售单ID Cid char(10), -商品ID SDnumber int, -销售数量 SDprice float, -销售单价 SDdiscount float, -折扣 PRIMARY KEY(SAid,Cid) )-8 客户表CREATE TABLE customer( CUid char(10) PRIMARY KEY, -客户ID CUname char(30), -客户 CUtelephone char(16), -客户联系 CUlevel int -客户等级：1，普通客户；2，VIP客户)-9 职员表CREATE TABLE employee( Eid char(6) PRIMARY KEY, -职员ID Ename char(8), -职员 Etype int -职员类型：1，管理人员；2，采购人员；3，销售人员)附录B 表初始化SQL语句-1 供给商表INSERT INTO supplier VALUES (A102,联想集团,1)INSERT INTO supplier VALUES (A002,三高计算机公司,1)INSERT INTO supplier VALUES (A106,玛永制衣公司,2)INSERT INTO supplier VALUES (A153,天天食品公司,2)INSERT INTO supplier VALUES (A508,统率咨询公司,3)INSERT INTO supplier VALUES (A001,神州数码,1)-2 商品表INSERT INTO modityVALUES (P0166,天翔PC机,60,5800.00)INSERT INTO modityVALUES (P9006,路由器,12,18800.00)INSERT INTO modityVALUES (PR4000,小型机,12,210000.00)INSERT INTO modityVALUES (H6880,联想1088手机,100,2400.00)INSERT INTO modityVALUES (H6122,数码相机,36,3600.00)INSERT INTO modityVALUES (S2008,财务软件,10, 8000.00)INSERT INTO modityVALUES (F70006,旺旺米饼,200,16.00)INSERT INTO modityVALUES (F72500,牛奶,1000,46.00)INSERT INTO modityVALUES (F70109,鲜橙多,600,6.00)-3 供给表INSERT INTO supplyingVALUES (A102,PR4000,210000.0)INSERT INTO supplyingVALUES (A102,P9006,18800.00)INSERT INTO supplyingVALUES (A102,P0166,5800.0)INSERT INTO supplyingVALUES (A001,H6880,2400.00)INSERT INTO supplyingVALUES (A001,H6122,3600.00)INSERT INTO supplyingVALUES (A002,S2008,8000.00)INSERT INTO supplyingVALUES (A153,F70006,16.00)INSERT INTO supplyingVALUES (A153,F72500,46.00)INSERT INTO supplyingVALUES (A153,F70109,6.00)-4 职员表INSERT INTO employeeVALUES(M0001,田明,1)INSERT INTO employeeVALUES(G0001,云,2)INSERT INTO employeeVALUES(G0002,明,2)INSERT INTO employeeVALUES(G0003,顺,2)INSERT INTO employeeVALUES(S0001,王良,3)INSERT INTO employeeVALUES(S0002,成,3)INSERT INTO employeeVALUES(S0003,君,3)-4 采购单表INSERT INTO stockVALUES(T20051018,G0001,A153,18000.00,2005-10-18)INSERT INTO stockVALUES(T20040912,G0001,A153,26000.00,2004-9-12)INSERT INTO stockVALUES(T20050501,G0002,A001,2000000.00,2005-5-1)INSERT INTO stockVALUES(T20060320,G0002,A102,26000000.00,2006-3-20)INSERT INTO stockVALUES(T20060115,G0002,A102,3600000.00,2006-1-15)INSERT INTO stockVALUES(T20040410,G0003,A002,60000.00,2004-4-10)-6 采购明细表INSERT INTO stockDetailVALUES(T20051018,F70109,400,6.00)INSERT INTO stockDetailVALUES(T20051018,F72500,650,46.00)INSERT INTO stockDetailVALUES (T20051018,F70006,200,16.00)INSERT INTO stockDetailVALUES(T20040912,F70109,200,6.00)INSERT INTO stockDetailVALUES(T20040912,F72500,350,46.00)INSERT INTO stockDetailVALUES (T20050501,H6880,100,2400.00)INSERT INTO stockDetailVALUES (T20050501,H6122,36,3600.00)INSERT INTO stockDetailVALUES(T20060320,PR4000,12,210000.00)INSERT INTO stockDetailVALUES(T20060115,P0166,60,5800.00)INSERT INTO stockDetailVALUES(T20060115,P9006,12,18800.00)INSERT INTO stockDetailVALUES(T20040410,S2008,10, 8000.00)-7 客户表INSERT INTO customerVALUES(KB001,家乐福,9,1)INSERT INTO customerVALUES(KB002,喜洋洋,3,1)INSERT INTO customerVALUES(KB003,好又多,08,1)INSERT INTO customerVALUES(KB004,沃尔玛,2,2)INSERT INTO customerVALUES(KB005,南城百货,8,2)-8 销售单表INSERT INTO saleVALUES(S20051216,S0001,KB001,18000.00,2005-12-16)INSERT INTO saleVALUES(S20051220,S0001,KB001,26000.00,2005-12-20)INSERT INTO saleVALUES(S20060101,S0002,KB002,2000000.00,2006-1-1)INSERT INTO saleVALUES(S20060315,S0002,KB003,26000000.00,2006-3-15)INSERT INTO saleVALUES(S20060115,S0002,KB003,3600000.00,2006-1-15)INSERT INTO saleVALUES(S20060417,S0003,KB004,60000.00,2004-4-17)-9 销售明细表INSERT INTO saleDetailVALUES(S20051216,F70109,400,6.00,0)INSERT INTO saleDetailVALUES(S20051216,F72500,650,46.00,0)INSERT INTO saleDetailVALUES (S20051216,F70006,200,16.00,0)INSERT INTO saleDetailVALUES(S20051220,F70109,200,6.00,0)INSERT INTO saleDetailVALUES(S20051220,F72500,350,46.00,0)INSERT INTO saleDetailVALUES(S20060315,F72500,350,46.00,0)INSERT INTO saleDetailVALUES (S20060115,H6880,100,2400.00,0)INSERT INTO saleDetailVALUES (S20060115,H6122,36,3600.00,0)INSERT INTO saleDetailVALUES(S20060115,PR4000,12,210000.00,0)INSERT INTO saleDetailVALUES(S20060115,P0166,60,5800.00,0)INSERT INTO saleDetailVALUES(S20060115,P9006,12,18800.00,0)INSERT INTO saleDetailVALUES(S20060417,S2008,10, 8000.00,0)附录C 表清空语句delete from supplierdelete from supplyingdelete from moditydelete from stockdelete from stockDetaildelete from saledelete from saleDetaildelete from customerdelete from employee26 / 26