数据库实验八：水果商店综合

一、实验要求

1.根据实验一~五描述其应用需求（可根据题来说明）
2.设计并绘出此数据库的E-R图
3.将E-R图转换成关系模式
4.针对已给定的数据库及应用需求，提出优化或改进措施并实现（至少三项）。

二、实验内容

1.应用需求

实验一：创建数据库fruitshop及用户、水果、订单等相关信息的数据表，用以展示数据及进行导入导出等操作。
实验二：利用简单查询和多表查询，可快速从数据库中查找到所需要的信息。
实验三：能够根据实际需要创建不同的用户并授以不同的权限，方便系统管理；且能针对不同级别的用户定义不同的视图，以保证系统的安全性。
实验四：创建存储过程、游标，通过属性匹配，在表中实现自动操作。
实验五：添加触发器对表中数据进行操作，免于手工启动，由事件来触发，更加灵活方便，且保证了数据完整性。

2.E-R图

3.关系模式

客户（客户编号，客户名，客户地址，城市，邮编，联系人，电子邮箱）
订单（订单号，订单项，购入数量，单价）
水果（水果编号，水果名，价格，库存数量）
供应商（供应商编号，供应商名，城市，邮编，电话）
提交（订购日期，客户编号，订单号）
订购（订单号，订单项，水果编号）
提供（供应商编号，水果编号）

4.功能改进

①查询当前销量最好的水果，考虑下次增加其进货量。
源码：

SELECT* FROM(
	SELECT f_id,sum(quantity) FROM orderitems
	GROUP BY f_id
	ORDER BY sum(quantity) DESC)s LIMIT 1

运行截图：

②限制每一位客户每次购买的水果不能超出库存量。
源码：

CREATE TRIGGER lim_fruits BEFORE INSERT ON fruits FOR EACH ROW 
	BEGIN
		DECLARE f_quantity INT;
		SELECT quantity INTO f_quantity FROM fruits WHERE fruits.f_id=new.f_id;
		IF f_quantity<new.quantity THEN
			SIGNAL SQLSTATE 'HY000' SET message_text='购买数量不得超过商品库存！';
		END IF;

	END

运行截图：

③在customers表中直接展示客户已购买订单得总价格。
源码：

ALTER TABLE customers
ADD ex_price DECIMAL(8,2)

CREATE PROCEDURE add_price()
	BEGIN
	DECLARE ex_num INT;
	DECLARE ex_price decimal(8,2); 
	DECLARE ex_update CURSOR FOR SELECT sum(pay) AS ex_sum FROM orders;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET ex_num=1;
	SET ex_num=0;

OPEN ex_update;
FETCH next FROM ex_update INTO ex_price;
WHILE (ex_num=0) DO
	UPDATE customers SET ex_price =(SELECT sum(pay)AS ex_sum FROM orders
	WHERE orders.c_id = customers.c_id);

FETCH next FROM ex_update INTO ex_price;

END WHILE;

CLOSE ex_update;
END

运行截图：