从0开始构建设计业务系统数据库-duidaima 堆代码

从0开始构建设计业务系统数据库

发布于 2个月前
 158 热度

 0 评论

沫离伤花
0 粉丝 25 篇博客

为什么数据库设计非常重要？数据库就像是企业的坚实支柱。可以把它们想象成把一切联系在一起的超级英雄！当这些数据库超级英雄得到精心设计和维护时，企业就能蓬勃发展。从长远来看，这就像拥有一根省时省力的魔术棒。因此，完美的数据库设计是成功的商业旅程的基础。数据库设计关乎企业成败。

让我们来深入了解一下这个项目。

一.项目概述
最近，一位企业负责人找到我，希望我帮助他们为公司构建一个全新的数据库。他们需要从零开始构建一个数据库，以高效地管理他们的业务运营。我非常乐意地接受了这个挑战，我开始设计一个强大并且是量身定制的数据库解决方案，以满足他们独特的需求。

业务数据目前保存在一个 Excel 文件中。你可以在这里下载 Excel 文件。数据快照如下。数据有 31 列和 1000 行。本项目使用的数据库管理系统是 MySQL（MySQL WorkBench）。

在审查了数据结构和帮助业务所需的步骤后，我将这些步骤细分为以下几个步骤。

二.数据库创建和数据加载
我采取的第一步是创建数据库并将数据加载到数据库中。选择的数据库名称是 db_northwind。
使用以下 SQL 脚本创建数据库。

<!-- 堆代码 duidaima.com -->
-- Dropping the database if it exists 
DROP DATABASE IF EXISTS db_northwind;

-- creating the database using character encoding of utf-8
CREATE DATABASE db_northwind DEFAULT CHARACTER SET utf8mb4;

-- to check if the database was created 
SHOW DATABASES;

-- to make the db_northwind database the active database 
USE db_northwind;

在这一阶段，用于保存业务数据的数据库已经创建，下一步就是将数据集输入数据库。
这可以通过两种不同的方式实现，分别是：
1.使用 MySQL Workbench 上的表导入向导导入数据
2.使用 MySQL Shell 上的 LOAD DATA INFILE 将数据加载到表中

要使用第二种方法，第一步是清理 Excel 文件中的数据并将其保存为 CSV 文件。

清理步骤包括：
1.确保日期类型为 “yyyy-mm-dd”。
2.确保使用正确的数字格式。不允许出现货币符号、逗号分隔符或任何符号。

完成上述步骤后，使用以下代码：

-- To drop the table if it exists
DROP TABLE IF EXISTS TblNorthwind;

-- TO create the table for the northwind data
CREATE TABLE TblNorthwind (
    orderID INT,
    customerID INT,
    employeeID INT,
    orderDate DATE,
    requiredDate DATE,
    shippedDate DATE,
    shipVia INT,
    Freight DECIMAL(10,2),
    productID INT,
    unitPrice DECIMAL(10,2),
    quantity INT,
    discount DECIMAL(4,2),
    companyName VARCHAR(255),
    contactName VARCHAR(255),
    contactTitle VARCHAR(255),
    lastName VARCHAR(255),
    firstName VARCHAR(255),
    title VARCHAR(255),
    productName VARCHAR(255),
    supplierID INT,
    categoryID INT,
    quantityPerUnit VARCHAR(255),
    product_unitPrice DECIMAL(10,2),
    unitsInStock INT,
    unitsOnOrder INT,
    reorderLevel INT,
    discontinued TINYINT,
    categoryName VARCHAR(255),
    supplier_CompanyName VARCHAR(255),
    supplier_ContactName VARCHAR(255),
    supplier_ContactTitle VARCHAR(255)
);

-- INSERTING DATA INTO THE CREATED TABLE
LOAD DATA LOCAL INFILE 'C:/Users/Data Analytics/MySQL DBA/Northwind.csv' 
          -- table path
INTO TABLE TblNorthwind
FIELDS TERMINATED BY ',' -- for a csv file
ENCLOSED BY '"' -- for the strings
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; -- ignore the headers

-- to check the table for the loaded data
SELECT *
FROM TblNorthwind;

注意：只有在用户和客户端都启用了访问权限的情况下，这个方法才有效。
我在获取访问权限时遇到了一些问题，因此我使用了表导入向导。该表已成功导入到在 MySQL 工作台上创建的 db_northwind 数据库中。

三.数据规范化
观察导入的数据后，我注意到数据已经去规范化。这就引出了下一步，数据规范化。规范化涉及根据指定的属性将数据组织成更大数据模型的一部分。数据库规范化的主要目的是消除冗余数据、减少数据修改错误并简化查询过程。我用于规范化数据的 SQL 技术是 CTAS（CREATE TABLE AS SELECT）方法。

用于数据规范化的部分脚本如下所示：

-- TO CREATE THE CUSTOMER TABLE
CREATE TABLE IF NOT EXISTS customers AS
SELECT DISTINCT customerID,
    companyName,
       contactName,
       contactTitle       
FROM TblNorthwind;

-- To check if the table was created and the data was loaded
SELECT *
FROM customers;

-- TO CREATE THE CATEGORIES TABLE
CREATE TABLE IF NOT EXISTS categories AS 
SELECT DISTINCT categoryID,
    categoryName
FROM TblNorthwind
ORDER BY categoryID;

-- To check if the table was created and the data was loaded
SELECT *
FROM categories;

该表被细分为以下实体：
客户
类别
供应商
产品
订单
员工

四.数据库图表设计
原始表被分成 6 个表，分别是客户表、类别表、供应商表、产品表、订单表和员工表。下一步是设计 ER（实体关系）图。ER 图使用符号表示实体、属性和关系，有助于说明数据库中实体之间的关系。使用 MySQL 的反向工程功能，我创建了下面的 ER 图。

上图显示了数据库中实体之间的关系，也显示了适合 ER 图设计的表约束。

五.表格修改
实体的 ER 图已经创建，表格约束也已指定。下一步是修改表，以适应 ER 图中指定的约束。本节主要介绍表更改。

首先是更改客户表的特征。

-- FOR THE CUSTOMERS TABLE

ALTER TABLE customers 
CHANGE COLUMN customerID customerID CHAR(5) NOT NULL ,
CHANGE COLUMN companyName companyName VARCHAR(255) NOT NULL ,
CHANGE COLUMN contactName contactName VARCHAR(255) NOT NULL ,
CHANGE COLUMN contactTitle contactTitle VARCHAR(255) NOT NULL ,
ADD PRIMARY KEY (customerID);
;

-- TO VERIFY IF THE CHANGES WERE MADE
DESCRIBE customers;；

更改类别表的特征：

-- FOR THE CATEGORIES TABLE

ALTER TABLE categories
MODIFY categoryID INT AUTO_INCREMENT PRIMARY KEY, -- For Category ID
MODIFY categoryName VARCHAR(255) NOT NULL UNIQUE -- For Category Name
;

-- TO VERIFY IF THE CHANGES WERE MADE
DESCRIBE categories;

要查看此步骤的完整文档，请看：
https://github.com/Ebuka456/Database-Design
六.创建视图、触发器和存储过程
至此，企业的数据库已经建立。
企业所有者提出了三个要求：
1.创建一个视图，显示每个员工的销售数量和收入
2.在产品表上创建一个触发器，在订单生成后自动删除库存产品的单位数
3.编写一个存储过程，检查某些产品是否需要补货，并列出需要补货的产品清单

任务 1：创建视图
创建一个视图，显示每个员工销售的产品数量和收入情况：

-- Creating the View for the task
CREATE OR REPLACE VIEW employee_record AS
SELECT e.employeeID,
    e.full_name AS "Full Name",
       e.employees_title AS "Title",
    SUM(o.quantity) AS "Quantity Sold",
       CONCAT( '$',
    ROUND(SUM(o.unitPrice * o.quantity), 2)
          ) AS Revenue
FROM employees e JOIN
  orders o 
     ON e.employeeID = o.employeeID
GROUP BY e.employeeID,
   e.full_name,
         e.employees_title
ORDER BY SUM(o.unitPrice * o.quantity) DESC
;

-- To test the view if it works
SELECT *
FROM employee_record;

视图的输出如下所示：

任务 2：创建触发器
在产品表上创建触发器，在下订单后自动删除库存产品的单位数：

-- To fist drop the Trigger if it exists
DROP TRIGGER update_products;

-- To create the trigger
DELIMITER //
CREATE TRIGGER update_products
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE products
SET unitsInStock = products.unitsInStock - NEW.quantity
WHERE productID = NEW.productID;
END //
DELIMITER ;

任务 3：编写存储过程
编写一个程序来检查某个产品是否需要补货，并列出需要补货的产品：

-- Creating a stored procedure

DELIMITER $$
CREATE PROCEDURE getRestock_products ( IN product_name VARCHAR(255))
BEGIN
-- TAKING THE LOWER CASE OF THE INPUT PARAMETER
SET product_name = LOWER(product_name);
-- To check if a certain product needs to be restocked
SELECT productName,
    CASE WHEN unitsInStock < reorderLevel THEN "Restock Level reached"
       WHEN unitsInStock = reorderLevel THEN "On Restock Level"
       WHEN unitsInStock - reorderLevel <= 5 THEN "Close to Restock Level"
       ELSE "Above Restocked Level"
       END AS "Restock Status"
FROM products
WHERE LOWER(productName) LIKE CONCAT('%', product_name, '%');

-- Products that needs to be restocked
SELECT productID,
    productName,
       unitsInStock,
       reorderLevel
FROM products
WHERE unitsInStock < reorderLevel;
END $$
DELIMITER ;

-- To test if the procedures worked, to check if the product that contains
-- "che" has reached restocked level
CALL getRestock_products("che");

该过程的输出如下所示：

从上面的图片中，我们可以看到所有列出的产品都含有 “che”。在所有产品中，Chef Anton's Gumbo Mix 是唯一处于补货状态的产品。这意味着供应商应该准备供应更多的产品。

该程序还列出了所有急需补货的产品，如下图所示：

七.用户管理和权限
任务：创建两个用户并赋予他们访问数据库的权限。第一个用户 “TuckerReilly” 将是一名 DBA，应获得完整的数据库管理员权限。第二个用户 “EllaBrody” 是分析员，只需要读取权限。设计数据库还涉及用户管理、授予和撤销用户权限。这将使用 SQL 命令 GRANT 和 REVOKE 来完成。

用户创建和用户权限使用以下命令完成的。

-- To check for the existing privileges granted
SHOW GRANTS FOR 
root@localhost -- username@hostname
;

-- USER CREATION

-- FOR THE FIRST USER NAMED "TuckerRelly"
CREATE USER IF NOT EXISTS
'TuckerRelly'@'localhost'
IDENTIFIED BY 'user_password';

-- FOR THE SECOND USER NAMED "EllaBrody"
CREATE USER IF NOT EXISTS
'EllaBrody'@'localhost'
IDENTIFIED BY 'user_password';

-- ASSIGNING USER PRIVILEGES

-- FOR THE FIRST USER, PRIVILEGES ARE FULL DATABASE ADMINISTRATOR
GRANT ALL PRIVILEGES
ON db_northwind.*
TO 'TuckerRelly'@'localhost';

-- FOR THE SECOND USER, PRIVILEGE IS ONLY THE SELECT STATEMENT
GRANT 
 SELECT
ON db_northwind.*
TO 'EllaBrody'@'localhost';

八.数据库备份
备份数据库的主要目的是在特定时间点创建其数据和结构的副本。这个过程涉及对整个数据库或其中选定部分进行快照，然后将这个副本存储在安全的位置。

可用于备份数据库的位置包括：
1.本地服务器
2.外置硬盘或 USB 驱动器
3.网络附加存储（NAS）
4.云存储服务：将备份存储在基于云的存储解决方案中，如 Amazon S3、Google Cloud Storage、Microsoft Azure Blob Storage 或其他类似的服务。云存储具有可扩展性、可访问性和灾难恢复等优点。

该数据库使用 MySQL 本地实例数据导出功能备份到硬盘上。

项目完整文档：
https://github.com/Ebuka456/Database-Design

总的来说，数据库对每个企业都非常重要。它可以作为一个集中存储库，组织和存储与客户、产品、订单等相关的大量重要数据。

有了数据库，企业可以轻松跟踪和管理库存，确保在客户需要时随时提供产品。此外，数据库还能确保数据的安全性和完整性，防止敏感信息遭到未经授权的访问或丢失。它可以进行可靠的备份，防止潜在的灾难或系统故障。

 用户评论

MySQL技术
 92 成员 |  172 话题
+我要提问 +随便写写

可能感兴趣的话题

MariaDB，MySQL和PostgreSQL数据库选择的问题

记一次MySQL性能优化记录

关于Mysql 的on duplicate key update操作，导致主键不连续自增的问题

如何处理单一日志文件太大不好清理的问题？