标准化是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