归一化传递依赖 [英] normalization transitive dependencies

问题描述

我需要有关规范化的帮助。我在理解如何在数据库项目中完成3NF时遇到麻烦。这是
1NF

I need help with normalization. I am having trouble understanding how to complete the 3NF on my database project. Here is the 1NF

归一化表

Donut ID(PK)
Donut Name
Description
Unit Price
Donut Order ID
Qty
CustomerID
Last Name
First Name 
Last Name 
Street Address
Apt
City
State
Zip
Home Phone
Mobile Phone
Other Phone
Order Date 
Special Notes

2NF
甜甜圈桌

DonutID (PK)
Donut Name
Description
Unit Price

销售订单表

Sales OrderID (PK)
CustomerID
Last Name
First Name 
Last Name 
Street Address
Apt
City
State
Zip
Home Phone
Mobile Phone
Other Phone
Order Date
Special Notes 

销售订单行项目表

Sales Order (PK)(FK)
Dount ID (PK)(FK)
Qty

我的问题是摆脱3NF中的传递依赖。我将在第四个表中使用什么属性，以便没有多余的内容或没有主键的情况下彼此依赖？任何方向将不胜感激。

My problem is getting rid of the transitive dependencies in 3NF. What attribute would I use in my fourth table so nothing is redundant or depending on eachother without the primary key? Any direction would be greatly appreciated.

推荐答案

销售订单表对客户名称具有传递依赖关系和地址。如果仔细观察，您会发现每个订单将包含给定客户的完整地址和姓名信息，即使该信息可能不会从一个订单更改为下一个订单也是如此。为了解决这个问题，您可以将此信息移到新的 Customer 表中，该表将具有以下字段：

The Sales Order table has a transitive dependency on the customer's name and address. If you look closely, you will see that each order will include the full address and name information for a given customer, even though that information probably isn't changing from one order to the next. To remedy this, you can move this information to a new Customer table which would have these fields:

客户表

CustomerID (PK)
Last Name
First Name 
Last Name 
Street Address
Apt
City
State
Zip
Home Phone
Mobile Phone
Other Phone

然后，销售订单表将变为：

销售订单表

Sales OrderID (PK)
Order Date
CustomerID (FK)
Special Notes

请注意，订单日期可以保留在销售订单 表中，因为从概念上讲，它代表每个订单发生时的时间戳，对于该特定订单而言是唯一的。

Note that the order date can remain in the Sales Order table because conceptually it represents a timestamp when each order occurred, unique to that particular order.

这篇关于归一化传递依赖的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！