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

1《数据库技术及应用》实验指导书2实验环境1．软件需求（1）操作系统：Windows2000Professional，或者WindowsXP（2）数据库管理系统：SQLServer2000（3）应用开发工具：Delphi7.0（4）其它工具：Word2．硬件需求（1）PC机（2）网络环境3基本需求信息一、对某商场采购销售管理进行调研后，得到如下基本需求信息：该商场有多名工作人员（主要是采购员和销售员），主要负责从供应商处采购商品，而后将商品销售给客户。采购员主要负责根据商场的销售情况确定要采购的商品，并与供应商联系，签订采购单。销售员主要负责将采购来的商品销售给客户，显然一个客户一次可能购买多种商品。一个供应商可以向该商场供应多种商品，而一种商品也可以由多个供应商供应。商场的管理者每个月需要对该月已采购的商品和已销售的商品进行分类统计，对采购员和销售员的业绩进行考核，对供应商和客户进行等级评定，并计算商场利润。二、E-R图职员采购明细签订销售明细销售单供应商商品采购单客户明细供应包含包含单价明细签订签订签订1mn11mp1mn1np1n11m三、需要建立的数据表如下1．供应商表（supplier）：供应商ID，供应商名称，地区，信誉等级2．供应表（supplying）：供应商ID，商品ID，商品单价43．商品表（commodity）：商品ID，商品名称，商品库存量，商品均价4．采购单表（stock）：采购单ID，采购员ID，供应商ID，采购总金额，签订日期5．采购明细表（stockDetail）：采购单ID，商品ID，采购数量，商品单价6．销售单表（sale）：销售单ID，销售员ID，客户ID，销售总金额，签订日期7．销售明细表（saleDetail）：销售ID，商品ID，销售数量，商品单价，单价折扣58．客户表（customer）：客户ID，客户名称，联系电话，客户等级9．职员表（Employee）：职员ID，职员姓名，职员类型6实验一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）分别使用企业管理器和查询分析器修改表结构（a）在商品表中加入描述商品信息的属性（b）将客户表中的客户联系电话（CUtelephone）的数据类型改为varchar(20)（4）分别使用企业管理器和查询分析器删除基本表（a）使用企业管理器删除客户表（b）使用查询分析器删除商品表三、自我测试1．使用企业管理器新建一个名称为“test”的数据库72．使用查询分析器在test数据库建立以下数据表（1）学生表：学号，姓名，性别，出生日期，系别（2）课程表：课程编号，课程名称，学分（3）选课表：学号，课程号，成绩3．使用查询分析器对表进行修改（1）将学生表中的性别数据类型改为整型（2）为学生表增加一个新的属性列，用于存放学生的专业信息8实验二使用PowerDesigner设计E-R图一、实验目的使用PowerDesigner软件创建概念数据模型二、实验内容2.1相关知识概念数据模型（CDM）设计是建模过程的关键阶段，此阶段把现实世界中需要保存的信息抽象成信息世界中的实体和联系，产生实体联系图，这一阶段可以为高质量的应用提供坚实的基础。联系的基数联系的基数可以是一对一（1:1）、一对多(l:N)或多对多(N:M)三种类型。上图(a)表示教师与课程的联系“教”是一对多的，即每个教师可以教多门课程，而每门课程只能由一个教师来教；图中还表示了学生与课程的联系“修”，它是多对多的，即每个学生可以修多门课程，而每门课程可以有多个学生来修。联系的强制如果联系在某一端是必须和实体关联的，比如，每门课程只能由一个教师来教，并且，也必须有一个老师来教，那么在联系“教”中，连接实体“老师”一侧，就需要是强制的；而每个教师可以教多门课程，也有些老师允许不教课程，那么在联系“教”的另一端，就不需要是强制的。物理数据模型概念数据模型建立之后，可以用PowerDesigner生成物理数据模型（PDM）。物理数据模型考虑了数据库的物理实现，包括软件和数据存储结构。2.2熟悉PowerDesigner环境9PowerDesigner的环境分成几个区域：左边的树形模型浏览器用分层结构显示你的工作空间，当你在工程中加入实体和联系后，会看到工作空间中也增加了相应的资源；右边的大片区域是工作面板，用来完成实体联系建模；在工作面板上，漂浮有工具棒，如下图所示，其中包括“实体”工具和“联系”工具，我们在本次实验中，主要应用这两个工具。EntityAssociationAssociationLinkRelationship新建一个工程：File→New→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)为联系的每个方向键入动词或者动词短语。105)为每个方向的联系选择基数和是否强制。2.4建立如下系统的实体联系（ER）模型该商场有多名工作人员（主要是采购员和销售员），主要负责从供应商处采购商品，而后将商品销售给客户。采购员主要负责根据商场的销售情况确定要采购的商品，并与供应商联系，签订采购单。销售员主要负责将采购来的商品销售给客户并签订销售单，显然一个客户一次可能购买多种商品。一个供应商可以向该商场供应多种商品，而一种商品也可以由多个供应商供应。商场的管理者每个月需要对该月已采购的商品和已销售的商品进行分类统计，对采购员和销售员的业绩进行考核，对供应商和客户进行等级评定，并计算商场利润。11实验三数据插入、修改和删除一、实验目的与要求1．掌握SQLServer的数据更新功能，能使用INSERT、UPDATE、DELETE命令对数据表中的数据进行更新2．掌握INSERT…SELECT和SELECT…INTO语句的使用方法二、实验内容1．使用INSERT命令向基本表中插入数据（参考附录B）（1）向供应商表中插入如下数据记录：（2）向商品表中插入如下数据记录（3）向职员表中插入如下数据记录（4）向供应表中插入如下数据记录（5）使用企业管理器分别向客户表、采购表、采购明细表、销售表、销售明细表中添加相12应记录。客户表采购单表(stock)采购明细表销售单表销售明细表(saleDetail)2．使用UPDATE命令修改基本表中的数据（1）将编号为‘F70006’的商品的单价改为12.00元13（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年以前签订的销售单的销售明细记录14实验四单表查询一、实验目的与要求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种商品的供应商的ID3．查询在2006年至少已经与2名销售员签订了合同的客户ID15实验五表连接查询一、实验目的与要求1．掌握表连接查询的使用方法2．掌握子查询的使用方法3．能使用内连接、左外连接、右外连接以及交叉连接解决相关问题4．熟悉系统函数的使用二、实验内容1．查找每一个供应商供应的商品的名称，要求显示供应商名称和商品名称。2．使用连接查询查找与姓名为‘李云’的采购员签订采购单的供应商的名称。3．使用关键字IN完成查询，要求查找与姓名为‘李云’的采购员签订采购单的供应商的名称。4．使用左外连接完成如下查询：要求查找所有采购员签订的采购合同的详细信息。5．使用左外连接完成如下查询：查找所有客户购买的商品详细信息，要求显示客户名称，商品名称，销售数量，商品单价，没有购买商品的客户也要显示。6．请使用内连接完成如下查询：查找每一个供应商供应的商品的种类，要求显示供应商名称，供应的商品的种类。7．查找购买了编号为‘A001’的供应商供应的商品