产品供应与订购信息数据库设计----数据库设计

完成数据库结构设计（包括概念设计和逻辑设计）
（1）根据项目背景分析实体和实体之间的关系，画出实体关系图；
（2）将实体关系转化为实体关系模型并要求满足3NF；
（3）由关系模型确定数据库的表结构，包括必须的完整性约束及其表之间的联系；

实体关系图

数据库设计的逻辑设计

第一范式（1NF）
要求数据库表的每一列都是不可分割的原子数据项—表中的所有字段值都是不可分解的原子值

第二范式（2NF）
需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关–针对联合主键
在1NF的基础上，非码属性必须完全依赖于候选码–（在1NF基础上消除非主属性对主码的部分函数依赖）

第三范式（3NF）
需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
在2NF基础上，任何非主属性不依赖于其它非主属性–（在2NF基础上消除传递依赖）

关系模式
客户（cno ，cname，trades，caddress，clinkman，ctelephone）
产品（pno ，pname，spec，unit，untiprice）
厂家（fno ，fname，faddress，salemanger，ftelephone）
订购（cno ，pno ，amount，date）
供应（pno ，fno ，samount，sdate）

数据库实现

已知产品供应与订购的业务关系如下图：

其中，客户（client）的属性有：客户编码（cno）、客户名称（cname）、所属行业（trades）、客户地址（caddress）、客户联系人（clinkman）、客户电话（ctelephone）；
产品（product）的属性有：产品编号（pno）、产品名称（pname）、规格（spec）、单位（unit）、单价（unitprice）；
厂家（factory）的属性有：厂家编码（fno）、厂家名称（fname）、厂家地址（faddress）、销售经理（salesmanager）、厂家电话（ftelephone）。
在客户订购(order)产品中，一个客户可以订购多种产品，一种产品也可以被多个客户订购，客户订购产品时须标明订购的订购数量（amount）和订购日期（date）；在厂家供应（supply）产品中，一种产品可以由多个厂家供应，一个厂家也可供应多种产品，厂家供应产品时须标明供应日期（sdate）和供应数量（samount）。
（2）数据库结构设计概念设计：根据项目背景分析实体和实体之间的关系，画出实体关系图。
逻辑设计：
●将实体关系转化为实体关系模型并要求满足3NF。
●由关系模型确定数据库的表结构，包括必须的完整性约束及其表之间的联系。
3.数据库实现与应用
（1）创建名为“TEST_学号”的数据库；

（2）创建数据库的所有数据表；

（3）编辑数据：给每个表输入至少10个记录（均为模拟数据）；为调试后续编程，应要求每个客户订购多种产品，每种产品有多个厂家供应。客户、产品有5个记录即可，但订购、供应和厂家要有更多个记录。另外，要求同一种产品不同厂家的单价不相同。

（4）设计并实现如下对象或应用
●创建数据库表之间的关系图

●在客户表上创建关于“所属行业”列的一个索引；

●创建一个包含“所属行业”、“客户名称”、订购的“产品名称”和“订购数量”信息的视图；

●编写SQL语句插入一条客户订购产品记录，内容自定。

●编写SQL语句修改一条厂家供应产品记录，内容自定。

●编写查询程序，并将程序存为脚本文件。

①查询各客户订购的某同一种产品的总数量和平均单价。要求首先按客户分组，然后每一组内再按产品分组。

②查询各种产品的所有厂家的名称和单价，并按单价从低到高的顺序排列。

4.数据库管理与维护
●通过SQL语句添加数据库用户user01，并授予该用户对订购数据表的insert、select、update和delete权限；

●通过SQL语句实现对数据库的完全备份和还原。

MySQL语句

创建数据库

CREATE DATABASE test_0337;

创建客户表（cn）

CREATE TABLE `cn` (
  `cno` int NOT NULL,
  `cname` varchar(255) NOT NULL,
  `trades` varchar(255) NOT NULL,
  `clinkname` varchar(255) NOT NULL,
  `ctelephone` varchar(255) NOT NULL,
  `caddress` varchar(255) NOT NULL,
  PRIMARY KEY (`cno`)
) 

创建厂家表（fn）

CREATE TABLE `fn` (
  `fno` int NOT NULL,
  `fname` varchar(255) NOT NULL,
  `faddress` varchar(255) NOT NULL,
  `salemanager` varchar(255) NOT NULL,
  `ftelephone` varchar(255) NOT NULL,
  PRIMARY KEY (`fno`)
) 

创建产品表（pn）

CREATE TABLE `pn` (
  `pno` int NOT NULL,
  `pname` varchar(255) NOT NULL,
  `spec` varchar(255) NOT NULL,
  `unit` varchar(255) NOT NULL,
  `untiprice` varchar(255) NOT NULL,
  PRIMARY KEY (`pno`)
)

创建订购表（order）

CREATE TABLE `order` (
  `cno` int NOT NULL,
  `pno` int NOT NULL,
  `amount` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`cno`,`pno`),
  KEY `ofpno` (`pno`),
  CONSTRAINT `ofcno` FOREIGN KEY (`cno`) REFERENCES `cn` (`cno`),
  CONSTRAINT `ofpno` FOREIGN KEY (`pno`) REFERENCES `pn` (`pno`)
) 

创建供应表（supply）

CREATE TABLE `supply` (
  `pno` int NOT NULL,
  `fno` int NOT NULL,
  `samount` varchar(255) NOT NULL,
  `sdate` datetime NOT NULL,
  PRIMARY KEY (`pno`,`fno`),
  KEY `sffno` (`fno`),
  CONSTRAINT `sffno` FOREIGN KEY (`fno`) REFERENCES `fn` (`fno`) ON DELETE RESTRICT,
  CONSTRAINT `sfpno` FOREIGN KEY (`pno`) REFERENCES `pn` (`pno`) ON DELETE RESTRICT
)

创建“所属行业”升序序列

CREATE INDEX ik_trades ON cn(trades ASC);

创建“所属行业”“客户名称”“产品名称”“订购数量”的视图

CREATE VIEW cnv(所属行业,客户名称,产品名称,订购数量)
AS SELECT trades,cname,pname,amount FROM cn,pn,`order`
WHERE cn.cno=`order`.cno AND pn.pno=`order`.pno;

编写SQL语言插入一条客户订购产品记录

INSERT INTO `order` VALUES(5,2,130,'2022-12-04 07:59:13');

编写SQL语言修改一条厂家供应记录

UPDATE supply SET samount='123' WHERE pno=1 AND fno=7;

编写查询程序(客户分组、产品分组)

SELECT cname AS 客户名称,pname AS 产品名称,SUM(amount) AS 总数量,AVG(untiprice) AS 平均单价
FROM cn,pn,`order` WHERE cn.cno=`order`.cno AND pn.pno=`order`.pno
GROUP BY cn.cno,pname;

编写查询程序（厂家名称和单价从低到高）

SELECT pname AS 产品名称,fname AS 厂家名称, untiprice AS 单价 FROM pn,fn,supply
WHERE fn.fno=supply.fno AND pn.pno=supply.pno
ORDER BY untiprice ASC;

数据库管理和维护

添加数据库用户user01

CREATE USER user01@localhost IDENTIFIED by'123456';

给order表授予权限

GRANT SELECT,INSERT,UPDATE,DELETE ON `order` to user01@localhost;

数据备份

mysqldump -u root -p  test_0337 >F:\test.sql

数据恢复

mysql -u root -p test_0337 <F:\test.sql