数据库实验98235

数据库原理实验指导

实验前准备：

请设计一个企业销售管理据库，其中需要保存的信息如下：

员工信息，包括：员工编号、员工姓名、性别、所属部门、职称、到职日、生日、薪水、填表日期；

客户信息，包括：客户号，客户名称，客户住址，客户电话、邮政编码；

产品信息，包括：产品编号，产品名称；

员工和客户可以签订订单，每签订一个订单，就要保存订单信息，包括：订单编号、客户号、业务员编号、订单金额、订货日期、出货日期、发票号码。

此外，每个订单可能涉及到多种产品，每种产品可能被多个订单订购。因此需要每个订单中每类产品的销售明细，包括每种产品的销售数量、单价、订单日期；要求：

（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 */

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 */

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 */

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*/

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 */

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或m

4、员工表建立唯一索引：emp_no属性、升序；员工表建立聚集索引：emp_name属性、升序；CREATE UNIQUE INDEX emp_index ON employee(emp_no ASC);

//MYSQL不支持此类语法

5、创建视图：

①视图只含上海客户信息，即客户号、客户姓名、住址。

CREATE VIEW 上海客户信息AS

SELECT cust_id,cust_name,addr

FROM customer

WHERE addr LIKE '上海%';

②有两个基本表employee和sales，创建一个视图，该视图包含相同业务员的编号、姓名、订单号、销售总金额。

CREATE VIEW emp_sales AS

SELECT sale_id,emp_name,order_no,tot_amt

FROM employee,sales

WHERE employee.emp_no=sales.sale_id;

四、实验报告

实验二

实验名称：数据操纵（4课时）

一．实验目的

1、要求学生熟练掌握添加、修改、删除数据的操作。

2、要求学生熟练掌握数据查询操作。

二、实验环境

MS SQL SERVER 2000或者My SQL

三、实验内容与步骤

1.数据更新

①在每个表中插入若干条记录；

-- employee

INSERT 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/4/2');

INSERT employee VALUES('E0009','王阳凤','女','宣传部','经理','2012/7/13','1985/3/6',9090,'杭州','2016/4/2'); INSERT employee VALUES('E0010','王航','女','联络部','成员','2017/5/4','1997/8/15',4000,'扬州','2016/4/2'); INSERT employee VALUES('E0011','王凤','女','联络部','经理','2015/7/13','1989/3/6',9090,'扬州','2016/4/2'); INSERT employee VALUES('E0012','王立','男','联络部','副经理','2013/8/14','1989/5/19',9000,'泰州','2016/4/2'); -- customer

INSERT customer VALUES('K0001','李立','泰州','1902847477','225700');

INSERT customer VALUES('K0002','李才','苏州','1322847472','295702');

INSERT customer VALUES('K0003','王吉','苏州','1362847489','235701');

INSERT customer VALUES('K0004','王嘉','扬州','1342847477','225400');

INSERT customer VALUES('K0005','王洛','苏州','1379847477','221706');

INSERT customer VALUES('K0006','端木镇','泰州','1382847477','215709');

INSERT customer VALUES('K0007','端木释俗','北京','1262847477','225230');

INSERT customer VALUES('K0008','孙可','上海','1322847477','225245');

INSERT customer VALUES('K0009','孙立','北京','1762847477','225764');

INSERT customer VALUES('K0010','孙苏','苏州','1092347477','212403');

-- sales

INSERT sales VALUES('S0010','K0001','E0001','7477','2018/4/29','2018/5/29','201947292');

INSERT sales VALUES('S0009','K0002','E0002','6477','2018/4/22','2018/5/29','201947232');

INSERT sales VALUES('S0008','K0003','E0003','5432','2018/5/9','2018/5/29','201947235');

INSERT sales VALUES('S0007','K0004','E0004','9432','2018/4/15','2018/5/29','201956235');

INSERT sales VALUES('S0006','K0005','E0005','5432','2018/5/9','2018/5/29','201007235');

INSERT sales VALUES('S0005','K0006','E0006','8432','2018/5/19','2018/5/29','201940235');

INSERT sales VALUES('S0004','K0007','E0007','9432','2018/5/19','2018/5/29','208947205');

INSERT sales VALUES('S0003','K0008','E0008','6432','2018/5/24','2018/5/29','201947260');

INSERT sales VALUES('S0002','K0009','E0009','5890','2018/5/2','2018/5/29','201947209');

INSERT sales VALUES('S0001','K0010','E0010','10383','2018/5/7','2018/5/29','201947468');

-- product

INSERT product VALUES('C0001','短袖');

INSERT product VALUES('C0002','短裤');

INSERT product VALUES('C0003','长袖');

INSERT product VALUES('C0004','牛仔裤');

INSERT product VALUES('C0005','七分裤');

INSERT product VALUES('C0006','五分裤');

INSERT product VALUES('C0007','外套');

INSERT product VALUES('C0008','短裙');

INSERT product VALUES('C0009','连衣裙');

INSERT product VALUES('C0010','衬衫');

-- sales_item

INSERT sales_item VALUES('S0010','C0001',77,1,'2018/5/9');

INSERT sales_item VALUES('S0009','C0002',146,12,'2018/5/9');

INSERT sales_item VALUES('S0008','C0003',126,18,'2018/5/9');

INSERT sales_item VALUES('S0007','C0004',124,9,'2018/5/9');

INSERT sales_item VALUES('S0006','C0005',128,12,'2018/5/9');

INSERT sales_item VALUES('S0005','C0006',124,42,'2018/5/9');