sql-server - 验证非键列依赖和传递依赖
问题描述
我正在设计我的第一个数据库，并且正在尝试遵循高达 3NF 的规范化规则。我使用以下定义进行规范化：
- 1NF：没有重复的组，并且确定了一个主键。
- 2NF：部分密钥依赖被移除。所有非键列都完全依赖于主键。
- 3NF：传递依赖和非关键依赖被移除。
该表用于存储有关宠物食品的信息。这是任何规范化之前的表的基本示例：
╔════════════╦═════════╦═════════════╦════════════╦═══════════╦═══════════════╦═══════╗
║ Brand ║ Flavor ║ Animal Type ║ Breed Size ║ Age Group ║ Ingredients ║ Price ║
╠════════════╬═════════╬═════════════╬════════════╬═══════════╬═══════════════╬═══════╣
║ Purina ║ Chicken ║ Dog ║ Small ║ Adult ║ Chicken, Salt ║ 18.99 ║
╠════════════╬═════════╬═════════════╬════════════╬═══════════╬═══════════════╬═══════╣
║ BlueWilder ║ Chicken ║ Dog ║ Small ║ Adult ║ Chicken, Salt ║ 18.99 ║
╚════════════╩═════════╩═════════════╩════════════╩═══════════╩═══════════════╩═══════╝
按照上面的 1NF 定义，我添加了一个主键列。在这种情况下，唯一的问题是在每条记录包含多个条目的成分列中：
╔═════════╦════════════╦═════════╦═════════════╦════════════╦═══════════╦═════════════╦═══════╗
║ Food ID ║ Brand ║ Flavor ║ Animal Type ║ Breed Size ║ Age Group ║ Ingredients ║ Price ║
╠═════════╬════════════╬═════════╬═════════════╬════════════╬═══════════╬═════════════╬═══════╣
║ 1 ║ Purina ║ Chicken ║ Dog ║ Small ║ Adult ║ Chicken ║ 18.99 ║
╠═════════╬════════════╬═════════╬═════════════╬════════════╬═══════════╬═════════════╬═══════╣
║ 1 ║ Purina ║ Chicken ║ Dog ║ Small ║ Adult ║ Salt ║ 18.99 ║
╠═════════╬════════════╬═════════╬═════════════╬════════════╬═══════════╬═════════════╬═══════╣
║ 2 ║ BlueWilder ║ Chicken ║ Dog ║ Small ║ Adult ║ Chicken ║ 18.99 ║
╠═════════╬════════════╬═════════╬═════════════╬════════════╬═══════════╬═════════════╬═══════╣
║ 2 ║ BlueWilder ║ Chicken ║ Dog ║ Small ║ Adult ║ Salt ║ 18.99 ║
╚═════════╩════════════╩═════════╩═════════════╩════════════╩═══════════╩═════════════╩═══════╝
由于成分列，现在有一个重复组。为了解决这个问题，我创建了两个单独的表；一个成分表，以及一个将成分表和食物表连接在一起的关联表：
╔═══════════════╦════════════╗
║ Ingredient ID ║ Ingredient ║
╠═══════════════╬════════════╣
║ 1 ║ Chicken ║
╠═══════════════╬════════════╣
║ 2 ║ Salt ║
╚═══════════════╩════════════╝
╔═════════╦═══════════════╗
║ Food ID ║ Ingredient ID ║
╠═════════╬═══════════════╣
║ 1 ║ 1 ║
╠═════════╬═══════════════╣
║ 1 ║ 2 ║
╠═════════╬═══════════════╣
║ 2 ║ 1 ║
╠═════════╬═══════════════╣
║ 2 ║ 2 ║
╚═════════╩═══════════════╝
现在，我可以从食物表中删除成分列：
╔═════════╦════════════╦═════════╦═════════════╦════════════╦═══════════╦═══════╗
║ Food ID ║ Brand ║ Flavor ║ Animal Type ║ Breed Size ║ Age Group ║ Price ║
╠═════════╬════════════╬═════════╬═════════════╬════════════╬═══════════╬═══════╣
║ 1 ║ Purina ║ Chicken ║ Dog ║ Small ║ Adult ║ 18.99 ║
╠═════════╬════════════╬═════════╬═════════════╬════════════╬═══════════╬═══════╣
║ 2 ║ BlueWilder ║ Chicken ║ Dog ║ Small ║ Adult ║ 18.99 ║
╚═════════╩════════════╩═════════╩═════════════╩════════════╩═══════════╩═══════╝
在这一点上，我不确定如何进行。
我的思路是这样的：如果任何非主键列改变了它的值，Food ID 一定是不同的。例如，品牌、风味、品种大小、年龄组、成分和价格都可以相同。但是如果动物类型是狗，然后变成猫，那肯定是不同的记录。这适用于所有非主键列。按照这个思路，所有的非主键列都是完全依赖主键的，不存在传递依赖。
我的思路正确吗？根据我上面列出的定义，2NF 和 3NF 是否已经满足？
解决方案
为了节省屏幕空间，我将重命名属性：
- 品牌：
BRD - 味道：
FLA - 动物：
ANM - 品种大小：
SIZ - 年龄阶层：
AGP - 成分：
ING - 价格：
PRI
方法一
规范化，就像教科书一样。
步骤 1.1
由于成分中的重复组，表中的值不是关系，因此该表不代表关系变量，因此不在1NF 中。解决方案是确保成分列 ( ING)每行只有一种成分 - 正如您所做的那样。但是，没有添加新属性（没有新 ID）。现在我们有（在 1NF 中）：
R {BRD, FLA, ANM, SIZ, AGP, ING, PRI}
整个标题是关键。
步骤 1.2
来自FDs：
{BRD, FLA} -> {ANM}{BRD, FLA} -> {SIZ}{BRD, FLA} -> {AGP}{BRD, FLA} -> {PRI}
通过对 FD 和 Heath 定理应用联合规则：
R1 {BRD, FLA, ANM, SIZ, AGP, PRI}
KEY {BRD, FLA}
R2 {BRD, FLA, ING}
KEY {BRD, FLA, ING}
完毕。就是这样，如果我得到FDs正确的。
两者都在 BCNF 中，我有信心说 R1 在 5NF 中，R2 在 6NF 中。
方法二
数据库设计是谓词设计。
不是大多数教科书中的正式标准化方法，而是一种导致表格处于高 NF（5NF、6NF）的设计方法。
第一步，问题（业务领域）使用简单的谓词和相关的约束来表达。然后可以使用逻辑用自然语言来推理这个问题。
一个简单的谓词不能在不丢失信息的情况下被分解，它的匹配relvar 在 6NF 中。
第二步，可以组合这些简单的谓词（和匹配的关系变量），确保不引入冗余和逻辑错误的可能性，即矛盾。一旦完成，relvars（表）预计将在 5NF 中。
步骤 2.1
使用简单的谓词和匹配 6NF 相关变量来描述问题。不要用语言表达约束（它会变得冗长），只是陈述它们。
-- Brand BRD exists.
--
brand {BRD}
PK {BRD}
-- Flavor FLA exists.
--
flavor {FLA}
PK {FLA}
-- Animal type ANM exists.
--
animal {ANM}
PK {ANM}
-- Breed size SIZ exists.
--
bsize {SIZ}
PK {SIZ}
-- Age group AGP exists.
--
age {AGP}
PK {AGP}
-- Ingredient ING exists.
--
ingredient {ING}
PK {ING}
-- Pet food with flavor FLA made by brand BRD
-- is for animal type ANM.
--
food {BRD, FLA, ANM}
PK {BRD, FLA}
FK1 {BRD} REFERENCES brand {BRD}
FK2 {FLA} REFERENCES flavor {FLA}
FK3 {ANM} REFERENCES animal {ANM}
-- Pet food with flavor FLA made by brand BRD
-- is recommended for breed size SIZ.
--
food_bsize {BRD, FLA, SIZ}
PK {BRD, FLA}
FK1 {BRD, FLA} REFERENCES
food {BRD, FLA}
FK2 {SIZ} REFERENCES bsize {SIZ}
-- Pet food with flavor FLA made by brand BRD
-- is recommended for breed age group AGP.
--
food_age {BRD, FLA, AGP}
PK {BRD, FLA}
FK1 {BRD, FLA} REFERENCES
food {BRD, FLA}
FK2 {AGP} REFERENCES age {AGP}
-- Pet food with flavor FLA made by brand BRD
-- is priced at PRI Euros per unit.
--
price {BRD, FLA, PRI}
PK {BRD, FLA}
FK {BRD, FLA} REFERENCES
food {BRD, FLA}
-- Pet food with flavor FLA made by brand BRD
-- contains ingredient ING.
--
recipe {BRD, FLA, ING}
PK {BRD, FLA, ING}
FK1 {BRD, FLA} REFERENCES
food {BRD, FLA}
FK2 {ING} REFERENCES ingredient {ING}
步骤 2.2
只需查看键，我们就可以看到food、food_bsize、food_age和price表可以组合在一起。
-- Pet food with flavor FLA made by brand BRD
-- is for animal type ANM, recommended for
-- breed size SIZ, breed age group AGP; priced
-- at PRI Euros per unit.
--
food_ {BRD, FLA, ANM, SIZ, AGP, PRI}
PK {BRD, FLA}
FK1 {BRD} REFERENCES brand {BRD}
FK2 {FLA} REFERENCES flavor {FLA}
FK3 {ANM} REFERENCES animal {ANM}
FK4 {SIZ} REFERENCES bsize {SIZ}
FK5 {AGP} REFERENCES age {AGP}
如果我们决定不保留定义域的前六个表，那么最终结果与第一种方法一样：
food_ {BRD, FLA, ANM, SIZ, AGP, PRI}
PK {BRD, FLA}
recipe {BRD, FLA, ING}
PK {BRD, FLA, ING}
FK {BRD, FLA} REFERENCES food_
{BRD, FLA}
但是，在实际项目中，您可能需要品牌、风味、动物、bsize和年龄表来约束域。规范化中没有规定您应该拥有它们的规则。
此外，并非所有属性都可能同时已知，因此您不太可能将所有food、food_bsize、food_age和price表合并到food_中。这将取决于业务流程和属性的可选性。
添加 ID
添加代理键 (ID) 与规范化无关。您可能出于其他原因需要添加它们，请查看此示例。
笔记：
All attributes (columns) NOT NULL
KEY = PK or AK
PK = Primary Key
AK = Alternate Key (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key
我总是使用第二种方法进行设计。从未见过在某种正式的 DB 课程之外使用的第一个；和寻求帮助的人。出于某种原因，超出我的理解，方法 1在方法 2之前的主题“数据库设计”下的数据库课程中讲授。大多数学校根本不教授方法2。去搞清楚。