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

1《数据库技术及应用》实验指导书2实验环境1．软件需求（1）操作系统：Win7以上（2）数据库管理系统：SQLServer2005（3）应用开发工具：JAVA、JAVAEE等（4）其它工具：Word2．硬件需求（1）PC机（2）网络环境3基本需求信息一、对某商场采购销售管理进行调研后，得到如下基本需求信息：该商场有多名工作人员（主要是采购员和销售员），主要负责从供应商处采购商品，而后将商品销售给客户。采购员主要负责根据商场的销售情况确定要采购的商品，并与供应商联系，签订采购单。销售员主要负责将采购来的商品销售给客户，显然一个客户一次可能购买多种商品。一个供应商可以向该商场供应多种商品，而一种商品也可以由多个供应商供应。商场的管理者每个月需要对该月已采购的商品和已销售的商品进行分类统计，对采购员和销售员的业绩进行考核，对供应商和客户进行等级评定，并计算商场利润。二、E-R图职员采购明细签订销售明细销售单供应商商品采购单客户明细供应包含包含单价明细签订签订签订1mn11mp1mn1np1n11m三、需要建立的数据表如下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，职员姓名，职员类型4实验一SQLServer2005使用一、实验目的与要求1.熟悉SQLServer2005中SQLServerManagementStudio的环境2.了解SQLServer2005数据库的逻辑结构和物理结构3.掌握使用向导创建和删除数据库的方法4．掌握基本表、主码等基本概念4．掌握SQL语言的数据定义功能，要求能使用向导和SQL两种方法来建立数据库、以及数据表。二、实验内容1．熟悉SQLServer2005中SQLServerManagementStudio的环境2．掌握SQLServer默认安装的各个数据库的主要作用（1）master数据库（2）model数据库（3）msdb数据库（4）tempdb数据库3．利用向导创建数据库和删除数据库设有一学籍管理系统，其数据库名为“EDUC”，初始大小为10MB，最大为50MB，数据库自动增长，增长方式是按5％比例增长；日志文件初始为2MB，最大可增长到5MB，按1MB增长。数据库的逻辑文件名为“student_data”,物理文件名为“student_data.mdf，存放路径为“E:\sql_data”。日志文件的逻辑文件名为“student_log”,物理文件名为“student_log.ldf”，存放路径为“E:\sql_data”。(1)使用向导创建上述的数据库。使用SQLServerManagementStudio（简称SSMS）创建数据库。(2)使用向导删除上面建立的数据库。用SSMS删除添加建立的数据库EDUC1步骤：1)启动SSMS,单击“连接”按钮，便可以进入【SQLServerManagementStudio】窗口。如果身份验证选择的是“混合模式”，则要输入sa的密码52)建立数据库：在“对象资源管理器”窗口，建立上述数据库EDUC。在数据库节点上右击选择新建。同时建立一个同样属性的数据库EDUC1。3)修改数据库名，数据库逻辑名，初始大小，增长方式等4)用SSMS删除添加建立的数据库EDUC1。4.使用SQL语句创建和删除数据库(1)以下是创建数据库userdb1的SQL语句，createdatabaseuserdb1on(name='userdb4_data',/*数据文件的逻辑名称,注意不能与日志逻辑同名*/6filename='d:\sql_data\userdb4.mdf',/*物理名称，注意路径必须存在*/size=5,/*数据初始长度为M*/maxsize=10,/*最大长度为M*/filegrowth=1)/*数据文件每次增长M*/logon(name=userdb4_log,filename='d:\sql_data\userdb4.ldf',size=2,maxsize=5,filegrowth=1)go//运行上诉语句建立数据库userdb1(2)根据步骤1的sql语句，写出创建数据库supermark的sql语句，并建立数据库EDUC。（3）用SQL语句删除步骤一建立的数据库userdb1。5．使用SSMS建立、删除数据表（1）在已经建立的supermarket数据库的基础上，建立以下数据表（参考附录A）：（a）商品表commodity（b）供应商表supplier（c）供应表supplying（d）采购单表stock（e）采购明细表stockDetail（f）销售单表sale（g）销售明细表saleDetail（h）客户表customer（i）职员表Employee（2）使用SSMS查看新生成表的结构和内容（3）使用SSMS修改表结构（a）在商品表中加入描述商品信息的属性（b）将客户表中的客户联系电话（CUtelephone）的数据类型改为varchar(20)（4）使用SSMS和SQL语句删除基本表（a）使用SSMS删除客户表（b）使用SQL语句删除商品表三、自我测试1．使用SQLServerManagementStudio新建一个名称为“test”的数据库2．使用SSMS或SQL语句在test数据库建立以下数据表（1）学生表：学号，姓名，性别，出生日期，系别（2）课程表：课程编号，课程名称，学分（3）选课表：学号，课程号，成绩7实验二PowerDesigner数据库建模实验目的：1.理解利用PowerDesigner进行数据库设计中概念模型CDM、物理模型PDM的设计过程；2.掌握利用PowerDesigner绘制E-R图的方法；3.学习使用物理模型PDM生成物理数据库的过程；实验内容：1.假设某公司销售业务中使用的订单格式如下：订单号：1145订货日期：09/15/2002客户名称：ABC客户电话：8141763产品编写品名价格数量金额A电源100.00202000.00B电表200.00408000.00C卡尺40.00502000.00总金额：12000.00公司的业务规定：(1)订单号是唯一的，每张订单对应一个订单号；(2)一张订单可以订购多种产品，每一种产品可以在多个订单中出现；(3)一张订单有一个客户，且一个客户可以有多张订单；(4)每一个产品编号对应一种产品的品名和价格；(5)每一个客户有一个确定的名称和电话号码。现在，我们要为该公司的销售业务设计数据库，要求如下：（1）使用PowerDesigner设计该销售系统的E-R图。（2）将该E-R图转换为关系模型，给出每个关系模式的主外码。（3）使用PowerDesigner生成物理数据库实验过程如下：（1）打开PowerDesigner系统，熟悉一下界面和工作区。（2）新建CDM，命名为OrderFile——NewModel——ConceptualDataModel,命名为Order右侧面板上面点击“Entity”按钮，在空白工作区添加一个实体，双击该实体，输入实体名“Order”及其属性。同样过程添加另外两个实体Customer和Prodcut。如下图8然后给三个实体添加联系。在面板上点击“Relationship”按钮，从Customer拖动到Order实体，双击联系，修改联系名为Submit，并将OrdertoCustomer的基数改为1。在面板上点击“Association”按钮，添加一个关联，命名为Subscribe，并给关联设置number属性，表示订购数量。用“AssociationLink”线把两个实体和关联连接起来。如下图（3）将CDM转化为PDMTools——GeneratePhysicalDataModel在弹出窗口中设置DBMS为MicrosoftSQLServer2005，然后点确定按钮，生成的PDM效果如下图9（4）使用PDM生成物理数据库需要首先连接到SQLServer数据库，为此我们要先创建ODBC数据源。开始——控制面板——性能和维护——管理工具——数据源在“文件DSN”选项卡下点“添加”，数据源驱动程序选“SQLServer”，数据源名称为order,接下来按照向导提示完成数据源文件的创建。其中服务器为本机的话，可以用小数点“.”表示，如果为其它机器，则需要输入IP地址。更改默认数据库为数据库服务器上的某个数据库，比如order,最后需要测试数据源并且测试成功。如下图有了数据源，接下来我们从PowerDesigner中连接到MicrosoftSQLServer2005数据库服务器。在PowerDesigner依次点击10Database菜单——Connect——ODBCfiledatasource，在下拉框中选中order.dsn，然后点击Connect。如下图成功连接到服务器后，我们可以生成数据库中的相应的几个表了。点击Database菜单——GenerateDatabase，然后选中Directgeneration，点确定，弹出如下窗口点“Run”，开始执行窗口中的SQL语句，执行成功后可以登录数据库查看效果，可以新建“数据库关系图”，效果如下：11至此，数据库创建完毕。12实验三数据插入、修改和删除一、实验目的与要求1．掌握SQLServer的数据更新功能，能使用INSERT、UPDATE、DELETE命令对数据表中的数据进行更新2．掌握INSERT…SELECT和SELECT…INTO语句的使用方法二、实验内容1．使用INSERT命令向基本表中插入数据（参考附录B）（1）向供应商表中插入如下数据记录：（2）向商品表中插入如下数据记录（3）向职员表中插入如下数据记录（4）向供应表中插入如下数据记录（5）使用企业管理器分别向客户表、采购表、采购明细表、销售表、销售明细表中添加相13应记录。客户表采购单表采购明细表销售单表销售明细表2．使用UPDATE命令修改基本表中的数据（1）将编号为‘F70006’的商品的单价改为12.00元14（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年以前签订的销售单的销售明细记录15实验四单表查询一、实验目的与要求1．掌握基本的SELECT…FROM语句的用法2．能应用SELECT语句及其子句解决常见的查询问题3．掌握模糊查询的使用方法4．能应用聚集函数和GROUPBY子句进行统计查询二、实验内容1．查找所有商品的名称和库存信息2．查找‘北京’地区的供应商3．查找所有销售人员的基本信息4．查找签订日期在‘2005-5-1’和‘2006-3-1’之间签订的采购单的基本信息5．查找2006年签订的所有销售单，