Kingbase数据库实验四 数据库系统的概要、逻辑、物理设计

一、实验目的

1. 能够根据实际业务需求抽象出实体、实体的属性和实体的联系。
2. 能够抽象业务所涉及的E-R图。
3. 能够优化E-R图并形成用于数据库系统逻辑设计的全局E-R图。
4. 能够将E-R图转换为对应的关系模式。
5. 能够对关系模式进行规范化的分析和验证。
6. 能够在业务需求发生变化时正确调整关系模式。
7. 能够将关系模式图转换为相关数据库管理系统的DDL语句。
8. 能够向建立好的数据库中添加测试数据。
9. 能够根据业务需求建立相关的视图。

二、实验步骤及结果

注：sql代码为人大金仓数据库的代码
某公司因业务扩展需要开发一套电子商务系统，用于在线销售各类商品。作为数据库设计人员，通过走访与跟班作业的方式，从商品销售部和商品管理部获得了如下业务信息。

1. 商品管理部的业务信息
   商品管理部负责管理销售的各类商品。目前公司所有可供销售的商品都记录在Excel表格中。
   Excel表格中每条记录的主要内容包括：商品名称、商品类别、商品价格、生产厂家、上一次购入时间、商品的详细信息、商品的缩略图。其中，商品类别包括图书、手机、数码影像和电脑等。商品的缩略图为jpg或png类型的图片。生产厂家根据商品类型表达的含义略有差异。如果是图书类型的商品，则生产厂家表示出版社。如果是其他类型商品，生产厂家即为实际生产机构。Excel中商品记录的示例信息如教材244页图6-27所示。
2. 商品销售部的业务信息
   商品销售部负责销售各类商品并对每次销售的结果进行记录。目前公司所有销售结果都记录Excel表格中。
   Excel表格中每条记录由三部分内容构成，分别是订单的基本信息、订单的购买人信息和订单中购买的商品信息。
   订单的基本信息包括：订单编号、订单的提交时间和订单的当前状态。其中，订单编号为17位数字，前8位为当前日期，后9位为按订单提交顺序生成的编码，该编号能够唯一标识每一条销售记录；订单提交时间精确到秒；订单状态包括：已提交、已发货、已完成等。
   订单的购买人信息包括：购买人的姓名、购买人的性别、购买人的联系方式、购买人的电子邮箱。其中，购买人的联系方式统一存储了购买者的送货位置、邮政编码和购买人的手机号码。
   订单的购买商品信息包括：商品的名称、商品的类别、商品的缩略图、商品的购买数量、商品的单价（元）。上述信息需与商品管理部所记录的商品信息对应。
   Excel中购买记录的示例信息如教材245页图6-28所示。
   请完成如下实验。
   1. 根据商品管理部提供的业务信息，抽象电子商务系统中该部门的局部E-R图。要求绘制E-R图中实体、属性和实体的联系，并使用中文标注实体、属性和实体联系。
      
   2. 根据商品销售部提供的业务信息，抽象电子商务系统中该部门的局部E-R图。要求绘制E-R图中实体、属性和实体的联系，并使用中文标注实体、属性和实体联系。
   3. 审查已经绘制的E-R图，分析是否可以进行E-R图的优化工作。重点关注绘制的E-R图是否存在数据冗余、插入异常、删除异常和更新异常。
      
   4. 将两个局部E-R图整合成描述该公司电子商务系统的全局E-R图。重点关注合并过程中的各类冲突。
      

根据概要设计所得的全局E-R图，完成如下实验。

1. 根据已经绘制的全局E-R图，通过E-R图到关系模式的转换方法，将全局E-R图转换为关系模式，并注明每个模式的主键和外键。
   （1）商品表（商品编号，商品名称，商品价格，详情信息，商品图片）主键：商品编号
   （2）类别表（类别号，类别名称）主键：类别号
   （3）属于表（商品编号，类别号）主键：商品编号，类别号 外键：商品编号，类别号
   （4）会员表（会员编号，会员姓名，会员性别，会员邮箱）主键：会员编号
   （5）订购表（订单编号，商品编号，会员编号，数量，提交时间）
   主键：订单编号 外键：商品编号，会员编号
   （6）状态表（状态号，状态名）主键：状态号
   （7）操作表（订单编号，状态号）主键：订单编号，状态号 外键：订单编号，状态号
   （8）联系方式表（电话，邮编，地址）主键：电话
   （9）联系表（会员编号，电话）外键：会员编号，电话
   （10）厂家表（厂家编号，厂家名称）主键：厂家编号
   （11）生产表（商品编号，厂家编号，上架时间）
   主键：商品编号，厂家编号 外键：商品编号，厂家编号

2. 对转换后的关系模式进行优化。
   （1）商品表（商品编号，商品名称，商品价格，详情信息，商品图片，类别号）
   （2）类别表（类别号，类别名称）
   （3）订购表（订单编号，商品编号，会员编号，数量，提交时间，状态号）
   （4）状态表（状态号，状态名）
   （5）会员表（会员编号，会员姓名，会员性别，会员邮箱，电话）
   （6）联系方式表（电话，邮编，地址）
   （7）厂家表（厂家编号，厂家名称）
   （8）生产表（商品编号，厂家编号，上架时间）

3. 使用数据规范化分析方法，分析转换后的模式属于第几范式。
   3NF（第三范式）

4. 在与客户进行数据库的确认工作时，商品管理部门发现现有设计中遗漏了商品的库存信息。需要在现有商品中添加库存信息。添加库存后的商品记录如教材246页图6-29所示。请修改现有E-R图，并调整转换后的关系模式。
   
   （1）类别表（类别号，类别名称）
   （2）商品表（商品编号，商品名称，商品价格，详情信息，商品图片，类别号）
   （3）会员表（会员编号，会员姓名，会员性别，会员邮箱）
   （4）联系方式表（会员编号，电话，邮编，地址）
   （5）状态表（状态号，状态名）
   （6）厂家表（厂家编号，厂家名称）
   （7）生产表（商品编号，厂家编号，上架时间）
   （8）仓库表（商品编号，库存量）
   （9）订购表（订单编号，商品编号，会员编号，数量，提交时间，状态号）
   根据数据库系统逻辑设计所得的关系模式，完成如下实验。
   1.以KingBaseES为系统将要部署的数据库管理系统，把逻辑设计所得的关系模式转换成数据库系统的DDL语句，具体包括：数据库创建的DDL、各种实体创建的DDL和多对多联系创建的DDL等。

CREATE DATABASE Goods;
 
CREATE TABLE 类别表
(	类别号 CHAR(10) PRIMARY KEY,                 
 	类别名称 VARCHAR(10)
);
CREATE TABLE 商品表
(	商品编号 CHAR(10) primary key,                 
 	商品名称 VARCHAR(20),  
 	商品价格 INT, 
 	详情信息 VARCHAR(20),
 	类别号 char(10)
);
CREATE TABLE 会员表
( 会员编号 VARCHAR(10)PRIMARY KEY,                 
 会员姓名 VARCHAR(10),  
 会员性别 CHAR(4),
 会员邮箱 VARCHAR(20)
);
CREATE TABLE 联系方式表
( 	会员编号 VARCHAR(10) PRIMARY KEY,
	电话 VARCHAR(20),                 
  	邮编 VARCHAR(10),  
	地址 CHAR(20)
);
CREATE TABLE 状态表
( 	状态号 CHAR(10)PRIMARY KEY,                 
 	状态名 VARCHAR(10)
);
CREATE TABLE 厂家表
( 厂家编号 CHAR(10) PRIMARY KEY,                 
 厂家名称 CHAR(30)
);
CREATE TABLE 生产表
(
	商品编号 CHAR(10),
	厂家编号 CHAR(10),
	上架时间 DATE
);
CREATE TABLE 仓库表
( 	商品编号 CHAR(10)primary key,
	库存量 INT
);
CREATE TABLE 订购表
(	订单编号  VARCHAR(10),
	会员编号 VARCHAR(10),
	商品编号 CHAR(10),
	数量 INT,
	提交时间 DATE,
	状态号 CHAR(10)
);

2.向已经创建好的数据中添加测试数据，添加记录的数量不限，只需有代表性即可。

INSERT INTO "类别表"(类别号,类别名称)
VALUES	('L1','计算机'),
		('L2','数学'),
		('L3','外语') 
 
INSERT INTO "商品表"(商品编号,商品名称,商品价格,类别号)
VALUES  ('S1','数据库系统',30,'L1'),
		('S2','高等数学',27,'L2'),
		('S3','综合英语',45,'L3')
 
INSERT INTO "会员表"(会员编号,会员姓名,会员性别,会员邮箱)
VALUES  ('H1','张三','男','11588345@qq.com'),
		('H2','李兰','女','897464125@qq.com'),
		('H3','赵斌','男','465138745@qq.com')
 
INSERT INTO "联系方式表"(会员编号,电话,邮编,地址)
VALUES  ('H1','13523116489','55123','广州'),
		('H2','15388456409','55124','深圳'),
		('H3','19244729105','55125','上海')
 
INSERT INTO "状态表"(状态号,状态名)
VALUES	('Z1','已下单'),
	 	('Z2','已提交'),
	    ('Z3','已完成')
 
INSERT INTO "厂家表"(厂家编号,厂家名称)
VALUES	('C1','高等教育出版社'),
		('C2','新疆大学出版社'),
		('C3','人民邮电出版社')
 
INSERT INTO "生产表"(商品编号,厂家编号,上架时间)
VALUES	('S1','C1','2001-08-12'),
		('S2','C2','2007-12-20'),
		('S3','C3','2020-03-12')
 
INSERT INTO "仓库表"(商品编号,库存量)
VALUES	('S1',700000),
		('S2',150000),
		('S3',90000)
 
INSERT INTO "订购表"(订单编号,会员编号,商品编号,数量,提交时间,状态号)
VALUES	('D1','H1','S1',1500,'2020-03-01','Z1'),
		('D2','H1','S3',1700,'2020-03-01','Z1'),
		('D3','H2','S2',500,'2020-04-15','Z3'),
		('D4','H3','S1',800,'2019-12-09','Z3'),
		('D5','H3','S2',1000,'2020-01-15','Z2'),
		('D6','H3','S3',2000,'2020-05-20','Z1')

3.创建视图，显示每个订单的总价。

CREATE VIEW 订单总价 AS
SELECT
    订单编号,商品名称,商品价格,数量,会员姓名,电话,会员邮箱,地址, 商品价格*数量 
	As 总价
FROM 订购表,商品表,会员表,联系方式表
WHERE 订购表.商品编号=商品表.商品编号 AND 订购表.会员编号=会员表.会员编号AND 会员表.会员编号=联系方式表.会员编号