数据库实验98235.doc

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

三、实验内容与步骤1、建立一个数据库和五张表的表结构1）/*员工人事表employee */emp_nochar(5)Not nullprimary key员工编号emp_namechar(10)Not null员工姓名sexchar(1)Not null性别deptchar(4)null所属部门titlechar(6)null职称date_hireddatetimenull到职日birthdaydatetimeNull生日salaryintnull薪水addrchar(50)null住址Mod_datedatetimeDefault(getdate())操作日期Create datebase sale;create table employee( emp_no char(5) Not null primary key, emp_name char(10) Not null, sex char(1) Not null, dept char(4) null, title char(6) null, date_hired datetime null, birthday datetime null, salary int null, addr char(50) null, Mod_date datetime Default 0, constraint c1 check(性别 in('男','女')) );（2）/*客户表customer */cust_idchar(5)Not nullprimary key客户号cust_namechar(20)Not null,客户名称addrchar(40)Not null,客户住址tel_nochar(10)Not null,客户zipchar(6)null邮政编码create TABLE customer( cust_id char(5) Not null primary key, cust_name char(20) Not null, addr CHAR(40) Not null, tel_no char(10) Not null, zip char(6) null);（3）/*销售主表sales */order_noChar(5)Not nullprimary key订单编号cust_idchar(5)Not null,客户号sale_idchar(5)Not null,业务员编号tot_amtnumeric(9,2)null,默认0订单金额order_datedatetimenull,订货日期ship_datedatetimenull,出货日期invoice_nochar(10)null发票号码create TABLE sales( order_no char(5) Not null primary key, cust_id char(5) Not null, sale_id CHAR(5) Not null, tot_amt numeric(9,2) null, order_date datetime null, ship_date datetime null, invoice_no char(10) null);（4）/*销货明细表 sales_item*/order_noChar(5)Not null,primary key订单编号prod_idchar(5)Not null,产品编号qtyintNot null默认0销售数量unit_pricenumeric(9,2)Not null默认0单价order_datedatetimenull订单日期create TABLE sales_item( order_no char(5) Not null , prod_id char(5) Not null, primary key(order_no,prod_id), qty INT Not null, unit_price numeric(9,2) Not null, order_date datetime null, CONSTRAINT FK_1 FOREIGN key(order_no) REFERENCES sales(order_no), CONSTRAINT FK_2 FOREIGN key(prod_id) REFERENCES product(prod_id));（5）/*产品名称表product */prod_idchar(5)Not nullprimary key产品编号prod_namechar(20)Not null产品名称create TABLE product( prod_id char(5) Not null primary key, prod_name char(20) Not null );2、建立5张表的关系图3、修改表结构，通过SQL语句修改表约束。

① 在表employee加入CHECK约束：输入的员工编号必须以E开头的5位数编号，性别只能为M/F//check对于MySQL不起作用，只能在插入数据的基础上修改use sale;ALTER TABLE employee add CHECK(sex LIKE 'E%');UPDATE employee SET sex='F'WHERE sex='女';UPDATE employee SET sex='M'WHERE sex='男';②为销售主表sales中的发票编号字段建立UNIQUE约束ALTER TABLE sales MODIFY invoice_no CHAR(10) UNIQUE NULL;③所有性别属性，限制取值为f或m4、 员工表建立唯一索引：emp_no属性、升序；员工表建立聚集索引：emp_name属性、升序；CREATE UNIQUE INDEX emp_index ON employee(emp_no ASC);//MYSQL不支持此类语法5、创建视图：① 视图只含上海客户信息，即客户号、客户姓名、住址CREATE VIEW 上海客户信息 ASSELECT cust_id,cust_name,addrFROM customerWHERE addr LIKE '上海%';②有两个基本表employee和sales，创建一个视图，该视图包含相同业务员的编号、姓名、订单号、销售总金额。

CREATE VIEW emp_sales ASSELECT sale_id,emp_name,order_no,tot_amtFROM employee,salesWHERE employee.emp_no=sales.sale_id;四、实验报告实验二实验名称： 数据操纵（4课时）一． 实验目的1、要求学生熟练掌握添加、修改、删除数据的操作2、要求学生熟练掌握数据查询操作二、实验环境MS SQL SERVER 2000或者My SQL三、实验内容与步骤1.数据更新①在每个表中插入若干条记录；-- employeeINSERT employee VALUES('E0001','赵三','男','销售部','经理','2013/3/4','1992/3/4',8000,'杭州','2013/4/2');INSERT into employee values('E0002','赵四','M','销售部','成员','2017/3/5','1994/2/3',2500,'泰州','2018/4/2');INSERT employee VALUES('E0003','钱四','男','销售部','组长','2015/3/4','1991/3/4',8000,'杭州','2015/4/2');INSERT employee VALUES('E0004','钱行','男','后勤部','经理','2014/8/4','1981/9/8',10000,'杭州','2015/4/2');INSERT employee VALUES('E0005','欧阳泽明','男','后勤部','组长','2016/8/4','1986/7/8'7000,'杭州','2017/4/2');INSERT employee VALUES('E0006','欧阳凤','女','后勤部','成员','2012/5/4','1989/3/5',7000,'杭州','2016/4/2');INSERT employee VALUES('E0007','欧阳峰','男','宣传部','副经理','2013/5/4','1989/3/9',9000,'泰州','2016/4/2');INSERT employee VALUES('E0008','欧阳创正','男','宣传部','成员','2017/8/8','1999/7/25',4000,'泰州','2016/。