数据库标准化与范式

和通数据库htsjk.Com2019-03-13 15:16 来源:未知阅读:9590 评论 414 热度3

标签：数据库范式数据库标准化

数据库标准化与范式

标准化是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，并在查看两个查询前研究一下它们。看看你能否找到解决办法，生成有资格取得认证的程序员列表。看了我的解决方案后，想想你自己是否还有更好的方法。

如何才足够标准化？

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

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

(

本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。

返回首页

笔试面试那件小事（数据库-范式

评论暂时关闭