数据库 标准化与范式

标准化是IT数据库专业人士的戒律之一，数据建模工程师、数据库管理员和SQL开发者都必须遵守这一戒律。我们很早就了解它的原理和范式。

但是对大部分数据库进行了解发现：它们至多执行了第三范式（3NF）。很少有数据库执行了更高范式，如Boyce-Codd范式（BCNF）、第四范式（4NF）和第五范式（5NF）。那么，为什么大多数数据库设计员没有超出3NF呢？

范式简介

为了回答上述问题，了解3NF、BCNF、4NF和5NF之间的区别很重要。以下为每个范式的准确定义。

第一范式（1NF）

每个表必须有一个首要键，即最少的一组属性，它与每条记录一一对应。通过适当定义键属性和非键属性，删除重复的组（不同记录似乎需要不同次重复的数据种类）。注：每个属性必须包含单独一个值，而非一组值。

第二范式（2NF）

数据库必须满足1NF的所有要求。另外，如果一个表有一个复合键，所有属性必须与整个键相关联。而且，在表的多行之间多余重复的数据被移动一个单独的表中。

第三范式（3NF）

存储在表中的数据不得依赖表的任何域，必须唯一依赖于首要键。数据库必须满足2NF的所有要求。既依赖首要键，又依赖其它域的数据被移动到一个单独的表中。

Boyce-Codd范式（BCNF）

除对一个候选键扩展集(称作一个超级键)存在属性函数依赖外，不存在其它非平凡函数依赖。

第四范式（4NF）

除对一个候选键扩展集存在属性组函数依赖外，不存在其它非平凡多值函数依赖。如果且只有一个表符合BCNF，同时多值依赖为函数依赖，此表才符合第四范式。4NF删除了不必要的数据结构：多值依赖。

第五范式（5NF）

不得存在不遵循键约束的非平凡连接依赖。如果且只有一个表符合4NF，同时其中的每个连接依赖被候选键所包含，此表才符合第五依赖。

单值与多值依赖

我还希望你完全了解两种类型的依赖：单值依赖和多值依赖。

例如，一名仅在组织的一个部门工作的员工就属于单值依赖。这名员工可以在部门之间调动，但不能同时为两个部门工作。

在几乎每个与地址有关的数据库中，你都可以发现多值依赖的例子。通常情况下，在Programmers表中有City、State和Country这些列。这些地址可能为文本，或者在方便查找的情况下，也可能为整数值。City查找城市表、State查找州表、Country查找国家表。这种安排带来无用地址的风险问题，如芝加哥、纽约、加拿大。这是因为这里的依赖是多值依赖。

完全标准化的版本可能会把State列移动到城市表，把country列移动到国家表，在Programmers表中只留下City列。我们可以建立一个连接三个表的查询，并提前执行这个查询，这样用户就可以根据斯普林菲尔德（Springfield）伊利诺斯州（IL）、斯普林菲尔德，马萨诸塞州（MA）和斯普林菲尔德，俄勒冈州（OR）选择合适的城市。

再看一个更复杂的多值依赖实例。某个程序员可能精通几门语言并拥有几项认证。每项认证需要精通一门或几门语言，每门语言又与一项或几项认证有关。当程序员学会了一门新的语言时，她可能有资格拥有一项或几项新的认证。我们如何才能确定哪个程序员有资格获得哪项认证呢？

列表A与B建立所需的表并加入几个样本行。列表C和D为查询表的脚本，查找有资格取得认证的程序员。这两个查询只有在顺序方面有所不同——列表C按认证顺序排列结果，而列表D按程序员顺序排列结果。

-- ========================================= -- Create table template -- ========================================= USE SQLTips2005 GO CREATE Schema Certs GO -- with the schema created, we can now add the following tables to the schema: CREATE TABLE Certs.Programmers ( ProgrammerID int IDENTITY(1,1) NOT NULL, Name varchar(50) NOT NULL, DateHired datetime NOT NULL, CONSTRAINT PK_ProgrammerID PRIMARY KEY(ProgrammerID) ) GO CREATE TABLE Certs.Languages ( LanguageID int IDENTITY(1,1) NOT NULL, LanguageName varchar(20) NOT NULL, CONSTRAINT PK_LanguageID PRIMARY KEY(LanguageID) ) GO CREATE TABLE Certs.Certifications ( CertificationID int IDENTITY(1,1) NOT NULL, CertificationName varchar(50) NOT NULL, CONSTRAINT PK_Certifications PRIMARY KEY(CertificationID) ) GO CREATE TABLE Certs.ProgammerLanguages ( ProgrammerID int NOT NULL, LanguageID int NOT NULL, CONSTRAINT PK_ProgrammerLanguageID PRIMARY KEY (ProgrammerID, LanguageID) ) GO CREATE TABLE Certs.CertificationLanguages ( CertificationID int NOT NULL, LanguageID int NOT NULL, NumberOfLanguages int NOT NULL, CONSTRAINT PK_CertificationLanguageID PRIMARY KEY (CertificationID, LanguageID) ) GO CREATE TABLE Certs.ProgrammerCertifications ( ProgrammerID int NOT NULL, CertificationID datetime NOT NULL, CONSTRAINT PK_ProgrammerCertificationID PRIMARY KEY (ProgrammerID, CertificationID) ) GO

列表A

-- Inserts for Languages table INSERT INTO [SQLTips2005].[Certs].[Languages]([LanguageName]) VALUES ('C#') INSERT INTO [SQLTips2005].[Certs].[Languages]([LanguageName]) VALUES ('VB.NET') INSERT INTO [SQLTips2005].[Certs].[Languages]([LanguageName]) VALUES ('SQL') INSERT INTO [SQLTips2005].[Certs].[Languages]([LanguageName]) VALUES ('Python') INSERT INTO [SQLTips2005].[Certs].[Languages]([LanguageName]) VALUES ('Delphi') INSERT INTO [SQLTips2005].[Certs].[Languages]([LanguageName]) VALUES ('Ruby') -- Inserts for Programmers table INSERT INTO [SQLTips2005].[Certs].[Programmers] ([Name],[DateHired]) VALUES ('Arthur', '01/01/2006 12:00:00 AM') INSERT INTO [SQLTips2005].[Certs].[Programmers] ([Name],[DateHired]) VALUES ('Billy', '02/01/2006 12:00:00 AM') INSERT INTO [SQLTips2005].[Certs].[Programmers] ([Name],[DateHired]) VALUES ('Kenny', '03/01/2006 12:00:00 AM') -- Inserts into Certifications table INSERT INTO [SQLTips2005].[Certs].[Certifications] ([CertificationName] ,[NumberOfLanguages]) VALUES ('C1',1) INSERT INTO [SQLTips2005].[Certs].[Certifications] ([CertificationName] ,[NumberOfLanguages]) VALUES ('C2',3) INSERT INTO [SQLTips2005].[Certs].[Certifications] ([CertificationName] ,[NumberOfLanguages]) VALUES ('C3',4) -- Inserts into ProgrammerCertifications table INSERT INTO [SQLTips2005].[Certs].[ProgammerLanguages] ([ProgrammerID], [LanguageID]) VALUES (1, 1) INSERT INTO [SQLTips2005].[Certs].[ProgammerLanguages] ([ProgrammerID], [LanguageID]) VALUES (1, 3) INSERT INTO [SQLTips2005].[Certs].[ProgammerLanguages] ([ProgrammerID], [LanguageID]) VALUES (1, 4) INSERT INTO [SQLTips2005].[Certs].[ProgammerLanguages] ([ProgrammerID], [LanguageID]) VALUES (2, 1) INSERT INTO [SQLTips2005].[Certs].[ProgammerLanguages] ([ProgrammerID], [LanguageID]) VALUES (2, 2) INSERT INTO [SQLTips2005].[Certs].[ProgammerLanguages] ([ProgrammerID], [LanguageID]) VALUES (2, 3) INSERT INTO [SQLTips2005].[Certs].[ProgammerLanguages] ([ProgrammerID], [LanguageID]) VALUES (3, 2) INSERT INTO [SQLTips2005].[Certs].[ProgammerLanguages] ([ProgrammerID], [LanguageID]) VALUES (3, 3) INSERT INTO [SQLTips2005].[Certs].[ProgammerLanguages] ([ProgrammerID], [LanguageID]) VALUES (3, 4) INSERT INTO [SQLTips2005].[Certs].[ProgammerLanguages] ([ProgrammerID], [LanguageID]) VALUES (3, 5)

列表B

SELECT    Certs.ProgammerLanguages.ProgrammerID, Certs.Programmers.Name, Certs.CertificationLanguages.CertificationID, Certs.Certifications.CertificationName, Certs.Certifications.NumberOfLanguages, Count(*) AS CertLanguageCount FROM      Certs.CertificationLanguages     LEFT OUTER JOIN Certs.ProgammerLanguages ON Certs.CertificationLanguages.LanguageID = Certs.ProgammerLanguages.LanguageID INNER JOIN Certs.Certifications ON Certs.Certifications.CertificationID = Certs.CertificationLanguages.CertificationID INNER JOIN Certs.Programmers ON Certs.ProgammerLanguages.ProgrammerID = Certs.Programmers.ProgrammerID GROUP BY  Certs.CertificationLanguages.CertificationID, Certs.Certifications.CertificationName, Certs.ProgammerLanguages.ProgrammerID, Certs.Programmers.Name, Certs.Certifications.NumberOfLanguages HAVING    Count(*)>= Certs.Certifications.NumberOfLanguages ORDER BY  Certs.ProgammerLanguages.ProgrammerID, Certs.CertificationLanguages.CertificationID

列表C

SELECT    Certs.CertificationLanguages.CertificationID, Certs.Certifications.CertificationName, Certs.ProgammerLanguages.ProgrammerID, Certs.Programmers.Name, Certs.Certifications.NumberOfLanguages, Count(*) AS CertLanguageCount FROM      Certs.CertificationLanguages     LEFT OUTER JOIN Certs.ProgammerLanguages ON Certs.CertificationLanguages.LanguageID = Certs.ProgammerLanguages.LanguageID INNER JOIN Certs.Certifications ON Certs.Certifications.CertificationID = Certs.CertificationLanguages.CertificationID INNER JOIN Certs.Programmers ON Certs.ProgammerLanguages.ProgrammerID = Certs.Programmers.ProgrammerID GROUP BY  Certs.CertificationLanguages.CertificationID, Certs.Certifications.CertificationName, Certs.ProgammerLanguages.ProgrammerID, Certs.Programmers.Name, Certs.Certifications.NumberOfLanguages HAVING    Count(*)>= Certs.Certifications.NumberOfLanguages ORDER BY  Certs.CertificationLanguages.CertificationID, Certs.ProgammerLanguages.ProgrammerID

列表D

我建议你运行列表A和B，并在查看两个查询前研究一下它们。看看你能否找到解决办法，生成有资格取得认证的程序员列表。看了我的解决方案后，想想你自己是否还有更好的方法。

如何才足够标准化？

确实，每个改进步骤都可能影响到总体性能。我看到有些标准化执行到荒谬的程度。在我最近参与的一个项目中，甚至还有一个性别表，好像这个列表随时会发生改变似的！

最终，如何执行标准化要由你自己来做决定，不过在决定之前，最好要全面了解各种范式以及没有执行相关范式的风险。

转载地址：http://blog.itpub.net/47598/viewspace-211706