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

目录
一、功能概述
二、概念设计
部分E-R图
​编辑
全局E-R图
三、逻辑构造设计
四、物理构造设计
创建数据库
创建数据表
插入表数据
创建视图
一、功能概述

        1. 订单处置部负责处置客户的订单和库存。
        2. 假设一个客户可以有一份或多份订单，每份订单可以订购一种或多种商品。每份订单有一张发票，客户可以通过多种方式来支付货款，例如支票、信誉卡或者现金。处置这个客户订购登记的职工的名字也要记录下来。
        3. 部门工作人员负责整理订单并根据库存情况处置订单。假设订单上的产品在库存中有，就可以直接发货，发货方式也有多种；假设订单上的产品在库存中没有，就不需要登记订购的产品或者订购其他产品。
效果展示：

二、概念设计

部分E-R图

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

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

全局E-R图

三、逻辑构造设计

将上述的全局E-R图转换为关系形式
商品（商品编号， 商品名，商品单价， 商品库存， 订单号）
主码： 商品编号   
客户（客户号， 客户姓名， 客户电话， 客户地址，职工编号）
主码：客户号     
订单（订单号， 出单日期， 商品数量， 客户号，职工编号）
主码：订单号   
职工（职工编号， 职工姓名， 职工岗位）
主码： 职工编号
发票（发票编号， 开具日期， 金额， 支付方法， 订单号）
主码： 发票编号   
关系形式评价：属于2NF，订单号->客户号， 客户号->职工编号，订单号->职工编号， 存在传送依赖。
优化:
商品（商品编号， 商品名，商品单价， 商品库存， 订单号）
主码： 商品编号   
客户（客户号， 客户姓名， 客户电话， 客户地址，职工编号）
主码：客户号   
订单（订单号， 出单日期， 商品数量， 客户号）
主码：订单号   
职工（职工编号， 职工姓名， 职工岗位）
主码： 职工编号
发票（发票编号， 开具日期， 金额， 支付方法， 订单号）
主码： 发票编号
四、物理构造设计

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

1. CREATE DATABASE CP

复制代码

创建数据表

1. --1.建立职工表(包含职工编号（主码），职工姓名，职工岗位)
2. CREATE TABLE administrator(
3.         ad_num char(10) PRIMARY KEY,
4.         ad_name char(20),
5.         ad_job char(20)
6. )
8. --2.建立客户表
9. CREATE TABLE customer(
10.         cus_num char(10) PRIMARY KEY,
11.         cus_name VARCHAR(20) NOT NULL,
12.         cus_tel VARCHAR(11),
13.         cus_address VARCHAR(20),
14.         ad_num char(10),
15.         FOREIGN KEY (ad_num) REFERENCES administrator (ad_num)
16. )
18. --3.建立订单表
19. CREATE TABLE cp_order(
20.         order_num VARCHAR(10) PRIMARY KEY,
21.         order_date DATE,
22.         cus_num CHAR(10),
23.         goods_totalnum INT,
24.         FOREIGN KEY (cus_num) REFERENCES customer (cus_num)
25. )
27. --4.建立商品表
28. CREATE TABLE goods(
29.         goods_num char(10) PRIMARY KEY,
30.         goods_name VARCHAR(20) NOT NULL,
31.         goods_price FLOAT NOT NULL,
32.         goods_storenum int NOT NULL,
33.         order_num VARCHAR(10),
34.         FOREIGN KEY (order_num) REFERENCES cp_order(order_num)
35. )
37. --5.建立发票表
38. CREATE TABLE invoice(
39.         inv_num char(10) PRIMARY KEY,
40.         inv_date DATE,
41.         inv_price FLOAT,
42.         inv_payway char(20),
43.         order_num varchar(10),
44.         FOREIGN KEY (order_num) REFERENCES cp_order(order_num)
45. )

复制代码

插入表数据

1. --1.插入员工表数据。
2. INSERT INTO administrator VALUES
3. ('2021001','张三','CEO'),
4. ('2021002','李四','CFO'),
5. ('2021003','王五','员工'),
6. ('2021004','赵六','员工'),
7. ('2021005','李七','员工')
9. --2.插入客户表数据
10. INSERT INTO customer VALUES
11. ('001','小一','10251375582','台湾1号','2021001'),
12. ('002','小二','10255375583','台湾2号','2021001'),
13. ('003','小三','14257375584','台湾3号','2021001'),
14. ('004','小四','18254375585','台湾4号','2021002'),
15. ('005','小五','17254375586','台湾5号','2021002'),
16. ('006','老六','14254375587','台湾6号','2021003'),
17. ('007','小七','13259375588','台湾7号','2021004'),
18. ('008','小八','12256375589','台湾8号','2021005')
20. --3.插入订单表数据
21. INSERT INTO cp_order VALUES
22. ('H001','2021-10-30','001',8),
23. ('H002','2021-10-30','002',500),
24. ('H003','2021-10-31','003',100),
25. ('H004','2021-11-11','004',60),
26. ('H005','2021-11-1','005',10),
27. ('H006','2021-11-11','006',500),
28. ('H007','2021-11-11','007',100),
29. ('H008','2021-11-11','008',150)
31. --4.插入商品表数据
32. INSERT INTO goods VALUES
33. ('A001','假发',99.99,5,'H001'),
34. ('A002','假牙',999.99,3,'H001'),
35. ('A005','假药',0.99,500,'H002'),
36. ('D040','脱发膏',8.8,100,'H003'),
37. ('S011','生发剂',12.5,60,'H004'),
38. ('G001','护发素',20,10,'H005'),
39. ('W006','牙刷',2.5,500,'H006'),
40. ('W007','毛巾',10.5,100,'H007'),
41. ('W008','牙膏',15.5,50,'H008'),
42. ('W009','杯子',6.6,100,'H008')
44. --5.插入发票表数据
45. INSERT INTO invoice VALUES
46. ('F001','2021-10-30',3499.65,'欠账','H001'),
47. ('F002','2021-10-30',495,'危心支付','H002'),
48. ('F003','2021-10-31',880,'吱富包支付','H003'),
49. ('F004','2021-11-11',750,'危心支付','H004'),
50. ('F005','2021-11-1',200,'危心支付','H005'),
51. ('F006','2021-11-11',1250,'吱富包支付','H006'),
52. ('F007','2021-11-11',1050,'吱富包支付','H007'),
53. ('F008','2021-11-11',1435,'银行卡支付','H008')
55. --6.更新数据内容：订单若超越1000元优惠总价格的3%，若超越5000元优惠总价格的5%。
56. --订单价格小于1000
57. UPDATE invoice
58. SET fin_price = inv_price
59. WHERE inv_price<1000
61. --订单价格在1000和5000之间
62. UPDATE invoice
63. SET fin_price = inv_price * (1-0.03)
64. WHERE inv_price>=1000 AND inv_price <5000
67. --订单价格在5000以上
68. UPDATE invoice
69. SET fin_price = inv_price * (1-0.05)
70. WHERE inv_price >= 5000

复制代码

创建视图

1. --1.可选商品视图(库存商品要大于0)
2. CREATE VIEW available_goods (商品名,库存数量,商品单价) AS
3. SELECT goods_name,goods_storenum,goods_price
4. FROM goods
5. WHERE goods_storenum>0
7. --2.订单的商品信息视图（商品号，商品名，商品单价，选购数量）
8. CREATE VIEW order_goods(商品号,商品名,商品单价,选购数量)AS
9. SELECT goods.goods_num,goods_name,goods_price,cp_order.goods_totalnum
10. FROM goods INNER JOIN cp_order ON goods.order_num = cp_order.order_num
12. --3.每种产品的订购情况视图
13. CREATE VIEW goods_order(商品号,商品名,订单号) AS
14. SELECT goods_num,goods_name,order_num
15. FROM goods
17. --4.已开具发票的视图，其中包含每个订单的执行情况。
18. CREATE VIEW invoice_order (发票号,订单日期,支付情况,订单号) AS
19. SELECT inv_num AS 发票号,inv_date AS 订单日期,inv_payway AS 支付情况,order_num AS 订单号
20. FROM invoice

复制代码