客户订购登记管理系统（数据库）

一、功能概述

    1. 订单处理部负责处理客户的订单和库存。

    2. 如果一个客户可以有一份或多份订单，每份订单可以订购一种或多种商品。每份订单有一张发票，客户可以通过多种方式来支付货款，例如支票、信用卡或者现金。处理这个客户订购登记的职工的名字也要记录下来。

    3. 部门工作人员负责整理订单并根据库存情况处理订单。如果订单上的产品在库存中有，就可以直接发货，发货方式也有多种；如果订单上的产品在库存中没有，就不需要登记订购的产品或者订购其他产品。

效果展示：

二、概念设计

局部E-R图

    1.职工与订单的发货关系以及订单与发票的开具关系的局部E-R图，如图局部E-R图。

     2.客户与职工的接待关系，客户与订单的支付关系，订单与商品的订购关系的局部E-R图

全局E-R图

三、逻辑结构设计

将上述的全局E-R图转换为关系模式

商品（商品编号， 商品名，商品单价， 商品库存， 订单号）

主码： 商品编号

客户（客户号， 客户姓名， 客户电话， 客户地址，职工编号）

主码：客户号

订单（订单号， 出单日期， 商品数量， 客户号，职工编号）

主码：订单号

职工（职工编号， 职工姓名， 职工岗位）

主码： 职工编号

发票（发票编号， 开具日期， 金额， 支付方法， 订单号）

主码： 发票编号

关系模式评价：属于2NF，订单号->客户号， 客户号->职工编号，订单号->职工编号， 存在传递依赖。

优化:

商品（商品编号， 商品名，商品单价， 商品库存， 订单号）

主码： 商品编号

客户（客户号， 客户姓名， 客户电话， 客户地址，职工编号）

主码：客户号

订单（订单号， 出单日期， 商品数量， 客户号）

主码：订单号

职工（职工编号， 职工姓名， 职工岗位）

主码： 职工编号

发票（发票编号， 开具日期， 金额， 支付方法， 订单号）

主码： 发票编号

四、物理结构设计

根据上述关系模式创建数据库，创建数据表，插入适当的数据

创建数据库

CREATE DATABASE CP

创建数据表

--1.建立职工表(包含职工编号（主码），职工姓名，职工岗位)
CREATE TABLE administrator(
    ad_num char(10) PRIMARY KEY,
    ad_name char(20),
    ad_job char(20)
)

--2.建立客户表
CREATE TABLE customer(
    cus_num char(10) PRIMARY KEY,
    cus_name VARCHAR(20) NOT NULL,
    cus_tel VARCHAR(11),
    cus_address VARCHAR(20),
    ad_num char(10),
    FOREIGN KEY (ad_num) REFERENCES administrator (ad_num)
)

--3.建立订单表
CREATE TABLE cp_order(
    order_num VARCHAR(10) PRIMARY KEY,
    order_date DATE,
    cus_num CHAR(10),
    goods_totalnum INT,
    FOREIGN KEY (cus_num) REFERENCES customer (cus_num)
)

--4.建立商品表
CREATE TABLE goods(
    goods_num char(10) PRIMARY KEY,
    goods_name VARCHAR(20) NOT NULL,
    goods_price FLOAT NOT NULL,
    goods_storenum int NOT NULL,
    order_num VARCHAR(10),
    FOREIGN KEY (order_num) REFERENCES cp_order(order_num)
)

--5.建立发票表
CREATE TABLE invoice(
    inv_num char(10) PRIMARY KEY,
    inv_date DATE,
    inv_price FLOAT,
    inv_payway char(20),
    order_num varchar(10),
    FOREIGN KEY (order_num) REFERENCES cp_order(order_num)
)

插入表数据

--1.插入员工表数据。
INSERT INTO administrator VALUES
('2021001','张三','CEO'),
('2021002','李四','CFO'),
('2021003','王五','员工'),
('2021004','赵六','员工'),
('2021005','李七','员工')

--2.插入客户表数据
INSERT INTO customer VALUES
('001','小一','10251375582','台湾1号','2021001'),
('002','小二','10255375583','台湾2号','2021001'),
('003','小三','14257375584','台湾3号','2021001'),
('004','小四','18254375585','台湾4号','2021002'),
('005','小五','17254375586','台湾5号','2021002'),
('006','老六','14254375587','台湾6号','2021003'),
('007','小七','13259375588','台湾7号','2021004'),
('008','小八','12256375589','台湾8号','2021005')

--3.插入订单表数据
INSERT INTO cp_order VALUES
('H001','2021-10-30','001',8),
('H002','2021-10-30','002',500),
('H003','2021-10-31','003',100),
('H004','2021-11-11','004',60),
('H005','2021-11-1','005',10),
('H006','2021-11-11','006',500),
('H007','2021-11-11','007',100),
('H008','2021-11-11','008',150)

--4.插入商品表数据
INSERT INTO goods VALUES
('A001','假发',99.99,5,'H001'),
('A002','假牙',999.99,3,'H001'),
('A005','假药',0.99,500,'H002'),
('D040','脱发膏',8.8,100,'H003'),
('S011','生发剂',12.5,60,'H004'),
('G001','护发素',20,10,'H005'),
('W006','牙刷',2.5,500,'H006'),
('W007','毛巾',10.5,100,'H007'),
('W008','牙膏',15.5,50,'H008'),
('W009','杯子',6.6,100,'H008')

--5.插入发票表数据
INSERT INTO invoice VALUES
('F001','2021-10-30',3499.65,'欠账','H001'),
('F002','2021-10-30',495,'危心支付','H002'),
('F003','2021-10-31',880,'吱富包支付','H003'),
('F004','2021-11-11',750,'危心支付','H004'),
('F005','2021-11-1',200,'危心支付','H005'),
('F006','2021-11-11',1250,'吱富包支付','H006'),
('F007','2021-11-11',1050,'吱富包支付','H007'),
('F008','2021-11-11',1435,'银行卡支付','H008')

--6.更新数据内容：订单若超过1000元优惠总价格的3%，若超过5000元优惠总价格的5%。
--订单价格小于1000
UPDATE invoice
SET fin_price = inv_price
WHERE inv_price<1000

--订单价格在1000和5000之间
UPDATE invoice
SET fin_price = inv_price * (1-0.03)
WHERE inv_price>=1000 AND inv_price <5000

--订单价格在5000以上
UPDATE invoice
SET fin_price = inv_price * (1-0.05)
WHERE inv_price >= 5000

创建视图

--1.可选商品视图(库存商品要大于0)
CREATE VIEW available_goods (商品名,库存数量,商品单价) AS 
SELECT goods_name,goods_storenum,goods_price
FROM goods
WHERE goods_storenum>0

--2.订单的商品信息视图（商品号，商品名，商品单价，选购数量）
CREATE VIEW order_goods(商品号,商品名,商品单价,选购数量)AS 
SELECT goods.goods_num,goods_name,goods_price,cp_order.goods_totalnum
FROM goods INNER JOIN cp_order ON goods.order_num = cp_order.order_num

--3.每种产品的订购情况视图
CREATE VIEW goods_order(商品号,商品名,订单号) AS 
SELECT goods_num,goods_name,order_num
FROM goods

--4.已开具发票的视图，其中包含每个订单的执行情况。
CREATE VIEW invoice_order (发票号,订单日期,支付情况,订单号) AS 
SELECT inv_num AS 发票号,inv_date AS 订单日期,inv_payway AS 支付情况,order_num AS 订单号
FROM invoice