数据库构建中的三范式设计（附SQL实例说明）

会撸代码的懒羊羊

于 2024-04-24 18:44:44 发布

阅读量852

点赞数 19

分类专栏： SQL 文章标签：数据库 sql oracle mysql hive

懒羊羊

本文链接：https://blog.csdn.net/kangqiao0422/article/details/138166832

版权

SQL 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

本文详细介绍了数据库设计中的第一范式、第二范式和第三范式，通过SQL实例演示如何遵循这些范式以减少数据冗余和提高一致性。还讨论了多对多、一对一和一对多关系的处理方式。

摘要由CSDN通过智能技术生成

数据库构建中的三范式（附SQL实例说明）

设计数据库时遵循三范式（1NF、2NF、3NF）是关系型数据库设计中用于减少数据冗余、提高数据一致性的理论基础。

第一范式：任何一张表必须有主键，每个字段原子性不可再分；最核心的要求

第二范式：建立在第一范式基础之上，要求所有非主键字段必须完全依赖主键，不要产生部分依赖；

第三范式：建立在第二范式基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。

个人总结：

多对多，三张表，关系表，两外键

一对多，两张表，多的表，加外键

一对一，外键唯一

以下通过代码示例来分别展示这三个范式的设计原则：

第一范式（1NF）：原子性

问题表（非1NF）：

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    CustomerAddress VARCHAR(255),
    OrderItems TEXT -- JSON格式存储多个订单项
);

在这个例子中，OrderItems列以JSON格式存储多个订单项，违反了1NF，因为一个字段包含了多个值（订单项列表），不具备原子性。

修正后的1NF表：

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT
);

CREATE TABLE OrderItems (
    ItemID INT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT,
    Price DECIMAL(10, 2)
);

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Address VARCHAR(255)
);

ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);
ALTER TABLE OrderItems ADD FOREIGN KEY (OrderID) REFERENCES Orders(OrderID);

现在，数据被分解成三个表：Orders、OrderItems和Customers。每个表的每个字段都只包含一个不可再分的值，满足了第一范式的要求。

第二范式（2NF）：消除部分依赖

问题表（非2NF）：

CREATE TABLE CustomersOrders (
    CustomerID INT,
    OrderID INT,
    CustomerName VARCHAR(100),
    CustomerAddress VARCHAR(255),
    OrderDate DATE,
    PRIMARY KEY (CustomerID, OrderID)
);

此表中，CustomerName和CustomerAddress依赖于CustomerID，而不是整个主键(CustomerID, OrderID)。这意味着当一个顾客有多个订单时，这些顾客信息会被重复存储。

修正后的2NF表：

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Address VARCHAR(255)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

将CustomersOrders表拆分为Customers和Orders两个表，每个表都具有单一主键，且非主键字段直接依赖于各自的主键，消除了部分依赖，符合第二范式。

第三范式（3NF）：消除传递依赖

问题表（非3NF）：

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    DepartmentID INT,
    DepartmentManagerID INT,
    ManagerName VARCHAR(100)
);

在此表中，ManagerName依赖于DepartmentManagerID，而DepartmentManagerID又依赖于DepartmentID。这种依赖关系是通过DepartmentID间接传递的，违反了第三范式。

修正后的3NF表：

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentManagerID INT,
    FOREIGN KEY (DepartmentManagerID) REFERENCES Employees(EmployeeID)
);

CREATE TABLE Managers (
    ManagerID INT PRIMARY KEY,
    Name VARCHAR(100)
);

ALTER TABLE Departments ADD FOREIGN KEY (DepartmentManagerID) REFERENCES Managers(ManagerID);