数据库规范化.docx
《数据库规范化.docx》由会员分享，可在线阅读，更多相关《数据库规范化.docx（35页珍藏版）》请在冰点文库上搜索。
数据库规范化
一、基础概念
要理解范式，首先必须对知道什么是关系数据库，如果你不知道，我可以简单的不能再简单的说一下：
关系数据库就是用二维表来保存数据。
然后你应该理解以下概念：
实体：
现实世界中客观存在并可以被区别的事物。
比如“一个学生”、“一本书”、“一门课”等等。
值得强调的是这里所说的“事物”不仅仅是看得见摸得着的“东西”，它也可以是虚拟的，不如说“老师与学校的关系”。
属性：
教科书上解释为：
“实体所具有的某一特性”，由此可见，属性一开始是个逻辑概念，比如说，“性别”是“人”的一个属性。
在关系数据库中，属性又是个物理概念，属性可以看作是“表的一列”。
元组：
表中的一行就是一个元组。
分量：
元组的某个属性值。
在一个关系数据库中，它是一个操作原子，即关系数据库在做任何操作的时候，属性是“不可分的”。
否则就不是关系数据库了。
码：
表中可以唯一确定一个元组的某个属性（或者属性组），如果这样的码有不止一个，那么大家都叫候选码，我们从候选码中挑一个出来做老大，它就叫主码。
全码：
如果一个码包含了所有的属性，这个码就是全码。
主属性：
一个属性只要在任何一个候选码中出现过，这个属性就是主属性。
非主属性：
与上面相反，没有在任何候选码中出现过，这个属性就是非主属性。
外码：
一个属性（或属性组），它不是码，但是它别的表的码，它就是外码。
第一范式是数据库规范化中所使用的一种正规形式。
第一范式是为了要排除重复组的出现，所采用的方法是要求数据库的每个字段都只能存放单一值，而且每条记录都要能利用一个惟一的主键来加以识别。
1．不符合第一范式的情况
重复组
重复组通常会出现在会计帐上，每条记录可能有不定个数的值。
举例来说：
交易
顾客
日期
数量
Pete
Monday
19.00
-28.20
Pete
Wednesday
-84.00
Sarah
Friday
100.00
150.00
-40.00
'数量'就是所谓的重复组了，而在这种情况下这份数据就不符合第一范式。
想要消除重复组的话，只要把每笔记录都转化为单一记录即可：
交易
交易ID
顾客
日期
数量
1
Pete
Monday
19.00
2
Pete
Monday
-28.20
3
Pete
Wednesday
-84.00
4
Sarah
Friday
100.00
5
Sarah
Friday
150.00
6
Sarah
Friday
-40.00
缺乏惟一识别码
一样是在交易这个例子中，同一天同一个人买了同样的数量，这样的交易做了两次：
交易
顾客
日期
数量
Pete
Monday
19.00
Pete
Monday
19.00
如上所示，这两笔交易可以说是一模一样，也就是说如果只靠这些数据我们没有办法分辨这两笔记录。
我们之所以说它不符合第一范式，是因为上面这样的表示法欠缺一个惟一识别码，可以是一个字段，也可以是一组字段，而且可以保证在这个数据中惟一识别码不会重复出现。
要将它正规化到符合第一范式的原则只需要加入一个惟一识别码即可：
交易
交易ID
顾客
日期
数量
1
Pete
Monday
19.00
2
Pete
Monday
19.00
关系数据库里的第一范式
大多数的RDBMS（关系数据库）允许用户在定义数据表的时候不去指定主键，不过这么一来这种数据表就不符合第一范式了。
从某个角度看来，不允许重复组的出现是关系数据库表示信息的方法，RDBMS里数据表每一笔记录的每一个字段都只能有一个值。
举例来说，如果定义了一个叫做FavoriteNumber的整数字段，每一笔记录的FavoriteNumber这个字段都只会是一个整数（或是无）；这也就是说，如果设置了主键的话，理论上不可能会有任何关系数据库的数据表会违反第一范式的原则。
不过就算是在这种情况下，还是可以设计出在骨子里违反第一范式的数据表。
最简单的方法就是把多个有意义的值编码过后存进一个字段里，然后在数据表中用很多字段来表达同一个事实。
单一字段中有多个有意义的值
在单一字段中存放多个值是违反第一范式的做法，下面这个就是很好的例子，它把多个值用逗号分开来表示：
挑食列表
人
不喜欢的食物
Jim
Liver,Goat'scheese
Alice
Broccoli
Norman
Pheasant,Liver,Peas
以这样的设计看来，想要知道有什么人不喜欢某样特定的东西是很不容易的。
不过可以把这个数据表转化成下面这种符合第一范式的类型：
挑食列表
人
不喜欢的食物
Jim
Liver
Jim
Goat'scheese
Alice
Broccoli
Norman
Pheasant
Norman
Liver
Norman
Peas
用很多字段来表达同一个事实
在同一个数据表里用多个字段来表达同一个事情也是违反第一范式的：
个人数据
人
喜欢的颜色
不喜欢的食物
（1）
不喜欢的食物
（2）
不喜欢的食物（3）
Jim
Green
Liver
Goat'scheese
Alice
Fuchsia
Broccoli
Norman
Blue
Pheasant
Liver
Peas
Emily
Yellow
就算我们能确定每个人不喜欢吃的食物最多不会超过三样，这还是一个很糟的设计。
举例来说，我们想要知道所有不喜欢同一种食物的人的组合的话，这就不是件容易的事，因为食物有可能出现在任何一个字段，也就是说每一次的查询都要去检查9（3x3）组不同的字段组合。
第二范式（2NF，台湾译作第二正规化）是数据库规范化中所使用的一种正规形式。
它的规则是要求数据表里的所有数据都要和该数据表的主键有相依关系；如果有哪些数据只和主键的一部份有关的话，就得把它们独立出来变成另一个数据表。
如果一个数据表的主键只有单一一个字段的话，它就一定符合第二范式。
一个数据表符合第二范式当且仅当
它符合第一范式
所有非主键的字段都一定和主键有关
示例
有一个数据表记录了设备元件的信息，如下所示：
元件来源
元件ID
（主键）
供应商ID
（主键）
供应商名称
价格
供应商住址
65
2
StylizedParts
59.99
VA
73
2
StylizedParts
20.00
VA
65
1
ACMEIndustries
69.99
CA
这个数据表的每个值都是单一值，所以它符合第一范式。
因为同一个元件有可能由不同的供应商提供，所以得把元件ID和供应商ID合在一起组成一个主键。
主键和价格之间的关系很正确：
同一个元件在不同供应商有可能会有不同的报价，所以价格确实和主键完全相关。
另一方面，供应商的名称和住址就只和供应商ID有关，这不符合第二范式的原则。
仔细看就会发现"StylizedParts"这个名称和"VA"这个住址重复出现了两次；要是它改名了或是被其他公司并购了怎么办？
这时候最好把这些数据存到第二个数据表中：
供应商
ID
名称
住址
1
ACMEIndustries
CA
2
StylizedParts
VA
这么一来，原本的"元件来源"数据表就得要做相对应的更动：
元件来源
元件ID（主键）
供应商ID（主键）
价格
65
2
59.99
73
2
20.00
65
1
69.99
检查数据表里的每个字段，确认它们是不是都和主键完全相关，这样才能知道这个数据表是不是符合第二范式；如果不是的话，就把那些不完全相关的字段移到独立的数据表里。
接下来的步骤是要确保所有不是键的字段都和彼此没有相依关系，这就叫做第三范式。
第三范式（3NF，台湾译作第三正规化）是数据库规范化中所使用的一种正规形式，用来检验是否所有非键属性都只和候选键有相关性，也就是说所有非键属性互相之间应该是无关的。
第三范式和第二范式不同的地方在于，在第三范式里，所有的非键属性都必须和每个候选键有直接相关。
如果再对第三范式做进一步加强就成了BC范式，它所强调的重点就在于"数据间的关系是奠基在键上、以整个键为考量、而且除了键之外不考虑其他因素"。
正规定义
令：
R表一个关系；
F表维持R所需的一组功能相依性；
X表R属性的子集合；
A表R的一个属性
如果对于
这种类型的功能相依性而言，下列叙述任一为真的话，则可以称R符合第三范式：
；也就是说A是明显功能相依性
X是超键
A是R的键的一部份
任何一个具有部份相依性或是转移相依性的关系都违反了第三范式。
示例
以下面这个定义机械元件的关系为例：
机械元件
元件编号（主键）
制造商名称
制造商地址
1000
Toyota
ParkAvenue
1001
Mitsubishi
LincolnStreet
1002
Toyota
ParkAvenue
本例中制造商地址很明显地不该被列在这个关系里面，因为和元件本身比起来，制造商地址应该和制造商比较有关系；正确的做法应该是把独立成为一个新的数据表：
制造商
制造商名称（主键）
制造商地址
Toyota
ParkAvenue
Mitsubishi
LincolnStreet
然后把原本的数据表改成这样：
机械元件
元件编号（主键）
制造商名称
1000
Toyota
1001
Mitsubishi
1002
Toyota
先前那个数据表的问题在于每提到一次制造商名称就要多存一次它的地址，而这就不符合第三范式的原则。
下面提供了另一个例子：
订单（Order）
订单编号（主键）
（OrderNumber）
客户名称
（CustomerName）
单价（UnitPrice）
数量（Quantity）
小计（Total）
1000
David
$35.00
3
$105.00
1001
Jim
$25.00
2
$50.00
1002
Bob
$25.00
3
$75.00
在本例中，小计不应该放在这个数据表里面，只要把单价乘上数量就可以得到小计了；如果想要符合第三范式的话，就把小计拿掉吧（不过在做查询的时候，本来用"SELECTOrders.TotalFROMOrders"就要改成用"SELECTUnitPrice*QuantityFROMOrders"了）。
订单（Order）
订单编号（主键）
（OrderNumber）
客户名称
（CustomerName）
单价（UnitPrice）
数量（Quantity）
1000
David
$35.00
3
1001
Jim
$25.00
2
1002
Bob
$25.00
3
主键（UniqueKey）
主键（UniqueKey或PrimaryKey），又称主码。
数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。
一个数据库表只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
从技术的角度来看，PrimaryKey和UniqueKey有很多相似之处。
但还是有以下区别：
一、作为PrimaryKey的域／域组不能为null。
而UniqueKey可以。
二、在一个表中只能有一个PrimaryKey，而多个UniqueKey可以同时存在。
更大的区别在逻辑设计上。
PrimaryKey一般在逻辑设计中用作记录标识，这也是设置PrimaryKey的本来用意。
而UniqueKey只是为了保证域／域组的唯一性。
外键（Foreignkey），又称外码。
在关系数据库中，每个数据表都是由关系来连系彼此的关系，父数据表（ParentEntity）的主键会放在另一个数据表当属性建立彼此的关系，而这个属性就是外键。
例如：
学生跟老师之间是教学的关系，学生数据表会有个属性叫指导老师（FK），而这个值就是对晕到老师数据表的老师代号（PK），学生的指导老师就是外键。
候选键
在关系模型中，候选键或候选码是某个关系变量的一组属性所组成的集合，它需要同时满足下列两个条件：
这个属性集合始终能够确保在关系中能唯一标识元组
在这个属性集合中找不出合适的子集能够满足条件
（1）
满足第一个条件的属性集合称为超键，因此我们也可以把候选键定义为“最小超键”，也就是不含有多余属性的超键。
候选键的重要性是它们能够在关系中唯一标识出不同的元组，因此超键也是在设计数据库模式时需要指定的最重要的约束之一。
由于在关系模型中，每个关系都是一个集合（没有重复的元素），所以每个关系都至少有一个候选键（因为所有属性组合必然是个超键）。
但是在某些关系型数据库中表也能代表多重集，所以在每个关系中都显式地定义至少一个候选键是一条很重要的设计原则。
数据库管理系统通常都需要将每个关系中的某个候选键定义为主键，亦即这个候选键是区分不同元组时首选的识别方式，例如外键通常就是引用主键而非其他候选键。
数据类型名称
类别
大小
（字节）
数据的本质说明
Bit
Integer
1
数据的大小容易让人误解。
表中第一个bit数据类型取用一个字节，接下来的7个bit数据类型使用同一个字节。
允许null则会多使用一个字节
Bigint
Integer
8
当需要频繁使用越来越大的数值时，可以使用Bigint数据类型。
该数据类型允许使用-263~263-1内的数值，即正负460百万兆
Int
Integer
4
-2147483648~2147483647的全部数值
SmallInt
Integer
2
-32768~32767的全部数值
TinyInt
Integer
1
0~255的全部数值
Decimal或Numeric
Decimal/
Numeric
变化的
固定精度和小数位数，-1038-1~1038-1。
这两个名称意思相同
Money
Money
8
-263~263，精确到所代表货币单位的万分之一。
注意，货币单位可以是任意的，不限于美元
SmallMoney
Money
4
-214748.3648~214748.3647货币单位
Float
ApproximateNumerics
变化的
接受一个参数（如Float（20）），以确定存储大小和精度。
注意，该参数是以位为单位，而不是以字节为单位。
范围为-1.79E+308~1.79E+308
DateTime
Date/Time
8
表示日期和时间的数据类型，从1753年1月1日到9999年12月31日，精确度是1/300s
SmallDateTime
Date/Time
4
表示日期和时间的数据类型，从1900年1月1日到2079年6月6日，精确度是1min
Timestamp/rowversion
Special
Numeric
二进制数字
8
在给定的数据库中唯一的特殊值。
当对数据库执行记录插入或更新时，由数据库自己自动地设置该值——即使没有在UPDATE语句中提及timestamp列（实际上，不允许直接更新timestamp域）
UniqueIdentifier
Special
Numeric
二进制数字
16
特殊的全局唯一标识符（GUID）。
确保跨时间和空间上是唯一的
Char
Character
变化的
固定长度字符数据。
如果值的长度比设定的长度短，则将添补空格直至达到设定的长度。
数据是非Unicode字符数据。
可指定的最大长度是8000字符
VarChar
Character
变化的
可变长度的字符数据。
不会向值中填入空格。
数据是非Unicode字符数据。
可指定的最大长度是8000字符，不过，可以使用max关键字，表明该数据是非常大的字符数据（最大存储大小是231字节）
Text
Character
变化的
在SQLServer2005中是一种遗留系统支持。
使用varchar（max）替代
NChar
Unicode
变化的
固定长度Unicode字符数据。
如果值的长度比设定的长度短，则将添补空格直至达到设定的长度。
可指定的最大长度是4000字符
NVarChar
Unicode
变化的
可变长度Unicode字符数据。
不会向值中填入空格。
可指定的最大长度是4000字符，不过，可以使用max关键字，表明该数据是非常大的字符数据（最大存储大小是231字节）
Ntext
Unicode
变化的
与Text数据类型类似，在SQLServer2005中只是一种遗留系统支持。
在这里，使用nvarchar（max）。
可变长度的Unicode字符数据
Binary
Binary
变化的
固定长度的二进制数据，最大长度是8KB
VarBinary
Binary
变化的
可变长度的二进制数据，可指定的最大长度是8KB，不过，可以使用max关键字，表明该数据是一个大型数据对象LOB（最大存储大小是231字节）
Image
Binary
变化的
在SQLServer2005中是一种遗留系统支持。
使用varbinary（max）替代
Sql_variant
Other
特殊的
与VB和C++中的Variant有些类似。
本质上，是能够容纳大多数其他SQLServer数据类型的容器。
这意味着，当一个列或函数需要能够处理多种数据类型时，可以使用Sql_variant。
与VB不同，使用这种数据类型时，必须明确地将其转换为更确定的数据类型
XML
Character
变化的
用于存储XML数据的字符数据类型。
用于基于XML模式验证数据，以及特殊的面向XML的函数
聚合函数
若要汇总一定范围的数值，请使用以下函数：
函数名
语法
说明
SUM
SUM（aggregate）
返回表达式中所有值的总和。
SUM只能与包含数值的字段一起使用。
将忽略空值。
AVERAGE
AVERAGE（aggregate）
返回表达式中所有非空值的平均值（算术平均值）。
AVERAGE只能与包含数值的字段一起使用。
将忽略空值。
MAX
MAX（aggregate）
返回表达式中的最大值。
对于字符列，MAX将按照排序顺序来查找最大值。
将忽略空值。
MIN
MIN（aggregate）
返回表达式中的最小值。
对于字符列，MIN将按照排序顺序来查找最小值。
将忽略空值。
COUNT
COUNT（aggregate）
返回组中非空项的数目。
COUNT始终返回Int数据类型值。
COUNTDISTINCT
COUNTDISTINCT（aggregate）
返回组中某项的非空非重复实例数
STDev
STDEV（aggregate）
返回某项的非空值的标准偏差。
STDevP
STDEVP（aggregate）
返回某项的非空值的总体标准偏差。
VAR
VAR（aggregate）
返回某项的非空值的方差。
VARP
VARP（aggregate）
返回某项的非空值的总体方差。
条件函数
若要测试条件，请使用以下函数：
函数名
语法
说明
IF
IF（condition,value_if_true,value_if_false）
如果指定了计算结果为TRUE的条件，将返回一个值；如果指定了计算结果为FALSE的条件，则返回另一个值。
条件必须是计算结果为TRUE或FALSE的值或表达式。
如果条件为True，则Value_if_true表示返回的值。
如果条件为False，则Value_if_false表示返回的值。
IN
IN（item,set）
确定某项是否是集的成员。
Switch
Switch（condition1,value1）
对一系列表达式求值并返回与其中第一个为True的表达式相关联的表达式的值。
Switch可以有一个或多个条件/值对。
数据类型转换
若要将值从一种数据类型转换为另一种数据类型，请使用以下函数：
函数名
语法
说明
INT
DECIMAL
INT（value）
DECIMAL（value）
将值转换为整数。
将值转换为十进制数字。
FLOAT
FLOAT（value）
将值转换为float数据类型。
TEXT
TEXT（value）
将数值转换为文本。
数据类型转换函数
函数名
语法
说明
CAST（）
CAST（AS[length]）
CONVERT（）
CONVERT（[length],[,style]）
1）data_type为SQLServer系统定义的数据类型，用户自定义的数据类型不能在此使用。
2）length用于指定数据的长度，缺省值为30。
3）把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号或负号的数值。
4）TEXT类型到CHAR或VARCHAR类型转换最多为8000个字符，即CHAR或VARCHAR数据类型是最大长度。
5）IMAGE类型存储的数据转换到BINARY或VARBINARY类型，最多为8000个字符。
6）把整数值转换为MONEY或SMALLMONEY类型，按定义的国家的货币单位来处理，如人民币、美元、英镑等。
7）BIT类型的转换把非零值转换为1，并仍以BIT类型存储。
8）试图转换到不同长度的数据类型，会截短转换值并在转换值后显示“+”，以标识发生了这种截断。
9）用CONVERT（）函数的style选项能以不同的格式显示日期和时间。
style是将DATATIME和SMALLDATETIME数据转换为字符串时所选用的由SQLServer系统提供的转换样式编号，不同的样式编号有不同的输出格式。
日期和时间函数
若要显示日期或时间，请使用以下函数：
函数名
语法
说明
DATE
DATE（year,month,day）
返回给定年、月、日的上午12:
00:
00的日期时间值。
DATEONLY
DATEONLY（datetime）
从日期时间值返回年、月和日。
DATETIME
DATETIME（year,month,day,hour,minute,second）
返回给定年、月、日、小时、分钟和秒的日期时间。
YEAR
YEAR（datetime）
返回日期时间的年份值。
QUARTER
QUARTER（datetime）
返回日期时间的日历季度（1-4）
升级会员 