数据库原理实验指导

数据库原理实验指导实验前准备（2课时）：请设计一个企业销售管理据库，其中需要保存的信息如下：员工信息，包括：员工编号、员工姓名、性别、所属部门、职称、到职日、生日、薪水、填表日期；客户信息，包括：客户号，客户名称，客户住址，客户电话、邮政编码；产品信息，包括：产品编号，产品名称；员工和客户可以签订订单，每签订一个订单，就要保存订单信息，包括：订单编号、客户号、业务员编号、订单金额、订货日期、出货日期、发票号码。此外，每个订单可能涉及到多种产品，每种产品可能被多个订单订购。因此需要每个订单中每类产品的销售明细，包括每种产品的销售数量、单价、订单日期；要求：（1） 给出系统的ER图（可以用word或其它画图工具，如Visioffi），要求画出所有的实体，联系，属性以及联系的类型；（2） 将ER图转换为关系模型；实验一实验名称：数据定义（2课时）一、 实验目的1、 理解数据库模式的概念，通过使用SQLSERVER企业管理器或者MySql建立数据库和基本表。模式为人事表、客户表、销售表、销售明细表、产品表。熟悉SQLSERVER企业管理器的使用，并将得到的表生成脚本，然后保存。2、 理解上述基本表之间的关系，建立关系表。3、 掌握修改表结构的基本方法4、 掌握索引和视图的创建方法二、 实验环境MSSQLSERVER或者MySql。三、 实验内容与步骤1、建立一个数据库和五张表的表结构。（1） /*员工人事表employee*/

emp_nochar(5)Notnullprimarykey员工编号emp_namechar(10)Notnull员工姓名sexchar(1)Notnull性别deptchar(4)null所属部门titlechar(6)null职称date_hireddatetimenull到职日birthdaydatetimeNull生日salaryintnull薪水addrchar(50)null住址Mod_datedatetimeDefault(getdate())操作日期(2)/*客户表customer*/cust_idchar(5)Notnullprimarykey客户号cust_namechar(20)Notnull,客户名称addrchar(40)Notnull,客户住址tel_nochar(10)Notnull,客户电话zipchar(6)null邮政编码(3)/*销售主表sales*/order_noChar(5)Notnullprimarykey订单编号cust_idchar(5)Notnull,客户号sale_idchar(5)Notnull,业务员编号tot_amtnumeric(9,2)null,默认0订单金额order_datedatetimenull,订货日期ship_datedatetimenull,出货日期invoice_nochar(10)null发票号码(4)/*销货明细表sales_item*/order_noChar(5)Notnull,primarykey订单编号prod_idchar(5)Notnull,产品编号qtyintNotnull默认0销售数量unit_pricenumeric(9,2)Notnull默认0单价order_datedatetimenull订单日期(5)/*产品名称表product*/prod_idchar(5)Notnullprimarykey产品编号prod_namechar(20)Notnull产品名称2、 建立5张表的关系图3、 修改表结构，通过SQL语句修改表约束。在表employee加入CHECK约束:输入的员工编号必须以E开头的5位数编号，性别只能为M/F。为销售主表sales中的发票编号字段建立UNIQUE约束。所有性别属性，限制取值为f或m4、 员工表建立唯一索引：emp_n。属性、升序；员工表建立聚集索引：emp_name属性、升序；5、创建视图：视图只含上海客户信息，即客户号、客户姓名、住址。有两个基本表employee和sales创建一个视图，该视图包含相同业务员的编号、姓名、订单号、销售总金额。四、实验报告实验二实验名称： 数据操纵(4课时)一、 实验目的1、 要求学生熟练掌握添加、修改、删除数据的操作。2、 要求学生熟练掌握数据查询操作。二、 实验环境MSSQLSERVER2000或者MySQL三、 实验内容与步骤数据更新在每个表中插入若干条记录；将所有员工的薪水增加100;将产品名称为题的产品的单价改为10删除所有女性员工销售记录；删除订单金额小于100000的订单。删除sale表中作废的订单(其发票号码为1000000004)。(注意：约束)数据查询查找所有经理的姓名、职称、薪水。找出姓“王”并且姓名的最后一个字为“功”的员工。查找住在上海或北京的女员工，并显示其姓名、所属部门、职称、住址。在表sale中挑出销售金额大于等于10000元订单。选取订单金额最高的前10%的订单数据。查找出职称为经理或职称为职员的女员工的信息。计算出一共销售了几种产品。显示sale_itei表中每种个别产品的订购金额总和，并且依据销售金额由大到小排列来显示出每一种产品的排行榜。计算每一产品每月的销售金额总和，并将结果按销售(月份，产品编号)排序。由sale表中查找出销售金额最高的订单。由sale表中查找出订单金额大于“E0013业务员在1996/10/15S天所接任一张订单的金额”的所有订单，并显示承接这些订单的业务员和该条订单的金额。找出公司女业务员所接的订单。找出公司中姓名相同的员工，并且依据员工编号排序相识这些员工信息。找出目前业绩未超过20000元的员工。计算公司内各个部门的工资支出总和。计算每一产品销售数量总和与平均销售单价。四、 实验报告实验三实验名称：T-SQL编程（2课时）一、 实验目的1、 掌握T-SQL编程方法。2、 掌握触发器、存储过程创建方法和原理二、 实验环境MSSQLSERVER2000以上版本三、 实验内容与步骤（1） T-SQL的流程控制语句使用编写程序完成以下功能，在查询分析器中执行程序，并记录结果。在employee表中求某个部门年龄最大和最小的员工的信息，包括：编号，姓名年龄。在employee表中先插入三条新记录，其中的dept字段的值为NULL，要求对记录进行查询时，对应的NULL值在显示时显示为“未分配”查询employee表中年龄，并根据年龄输出所属阶段（青年、中年、老年）。（2） 存储过程的创建与调用按要求完成以下功能，并记录结果。创建一个存储过程info_1完成的功能是在表employee、表customer和表sales中查询以下字段：部门、业务员编号、业务员姓名、客户姓名、订单金额、订货日期。创建一个带有参数的存储过程emp_info,该存储过程根据传入的业务员编号，在employee中查询此业务员全部的信息。创建一个带有参数的存储过程emp_age，该存储过程根据传入的业务员编号，在employee中计算此业务员的年龄，并根据程序执行结果返回不同的值，程序执行成功，返回整数0，如果执行错误，则返回错误号。执行上述存储过程删除存储过程emp_info（3） 触发器创建与调用按要求完成以下功能，在查询分析器中执行程序，并记录结果。建立一个触发器sales_item_trigger:确保sales中订单金额，等于sale_item中该订单的（销售数量*单价）的总和修改某个某个订单的“销售数量”或“单价”。查看sales表中，该订单的“订单金额”的变化。删除某个某个订单的一个明细（即一个产品）。查看sales表中，该订单的