本文的示例代码参考normalization
目录
Startup
vim startup.sql
DROP DATABASE IF EXISTS normalization;
CREATE DATABASE IF NOT EXISTS normalization;
USE normalization;
DROP TABLE IF EXISTS `startup`;
CREATE TABLE `startup` (
`Full Names` VARCHAR(64) NOT NULL,
`Physical Address` VARCHAR(64) NOT NULL,
`Movies Rented` VARCHAR(64) NOT NULL,
`Saluation` VARCHAR(64) NOT NULL
);
INSERT INTO `startup` VALUES
('Janet Jones','First Street Plot No 4','Pirates of the Caribbean, Clash of the Titans','Ms.'),
('Robert Phil','3rd Street 34','Forgetting Sarah Marshal, Daddy\'s Little Girls','Mr.'),
('Robert Phil','5th Avenue','Clash of the Titans','Mr.');
source ./startup.sql;
select * from startup;
+-------------+------------------------+------------------------------------------------+-----------+
| Full Names | Physical Address | Movies Rented | Saluation |
+-------------+------------------------+------------------------------------------------+-----------+
| Janet Jones | First Street Plot No 4 | Pirates of the Caribbean, Clash of the Titans | Ms. |
| Robert Phil | 3rd Street 34 | Forgetting Sarah Marshal, Daddy's Little Girls | Mr. |
| Robert Phil | 5th Avenue | Clash of the Titans | Mr. |
+-------------+------------------------+------------------------------------------------+-----------+
问题: 'Movies Rented'列有多个值
1NF
1NF(First Normal Form): 每一列只包含单一值 即所有属性都是不可分的基本数据项
vim 1nf.sql
USE normalization;
DROP TABLE IF EXISTS `1nf`;
CREATE TABLE `1nf` (
`Full Names` VARCHAR(64) NOT NULL,
`Physical Address` VARCHAR(64) NOT NULL,
`Movies Rented` VARCHAR(64) NOT NULL,
`Saluation` VARCHAR(64) NOT NULL
);
INSERT INTO `1nf` VALUES
('Janet Jones','First Street Plot No 4','Pirates of the Caribbean','Ms.'),
('Janet Jones','First Street Plot No 4','Clash of the Titans','Ms.'),
('Robert Phil','3rd Street 34','Forgetting Sarah Marshal','Mr.'),
('Robert Phil','3rd Street 34','Daddy\'s Little Girls','Mr.'),
('Robert Phil','5th Avenue','Clash of the Titans','Mr.');
source ./1nf.sql;
select * from 1nf;
+-------------+------------------------+--------------------------+-----------+
| Full Names | Physical Address | Movies Rented | Saluation |
+-------------+------------------------+--------------------------+-----------+
| Janet Jones | First Street Plot No 4 | Pirates of the Caribbean | Ms. |
| Janet Jones | First Street Plot No 4 | Clash of the Titans | Ms. |
| Robert Phil | 3rd Street 34 | Forgetting Sarah Marshal | Mr. |
| Robert Phil | 3rd Street 34 | Daddy's Little Girls | Mr. |
| Robert Phil | 5th Avenue | Clash of the Titans | Mr. |
+-------------+------------------------+--------------------------+-----------+
什么是Primary Key
- Primary Key标识表数据唯一性
Primary Key值唯一
Primary Key值不可为空
Primary Key值不可更改
Primary Key值自动生成在插入数据后
什么是Partial Functional Dependency
- Primary Key(X, Y) -> (Z) 但是Primary Key(X, Y)的真子集(X)or(Y) -> (Z) 则(Z)部分函数依赖于Primay Key(X, Y)
2NF
2NF(Second Normal Form): 满足1NF且没有Partial Functional Dependency 即每个非主属性都完全函数依赖于Primary Key
什么是Full Functional Dependency
- Primary Key(X, Y) -> (Z) 且不存在Primary Key(X, Y)的真子集(X)or(Y) -> (Z) 则(Z)完全函数依赖于Primay Key(X, Y)
vim 2nf.sql
USE normalization;
DROP TABLE IF EXISTS `2nf_t1`;
CREATE TABLE `2nf_t1` (
`Membership ID` INT(10) NOT NULL,
`Full Names` VARCHAR(64) NOT NULL,
`Physical Address` VARCHAR(64) NOT NULL,
`Saluation` VARCHAR(64) NOT NULL
);
INSERT INTO `2nf_t1` VALUES
(1,'Janet Jones','First Street Plot No 4','Ms.'),
(2,'Robert Phil','3rd Street 34','Mr.'),
(3,'Robert Phil','5th Avenue','Mr.');
DROP TABLE IF EXISTS `2nf_t2`;
CREATE TABLE `2nf_t2` (
`Membership ID` INT(10) NOT NULL,
`Movies Rented` VARCHAR(64) NOT NULL
);
INSERT INTO `2nf_t2` VALUES
(1,'Pirates of the Caribbean'),
(1,'Clash of the Titans'),
(2,'Forgetting Sarah Marshal'),
(2,'Daddy\'s Little Girls'),
(3,'Clash of the Titans');
select * from 2nf_t1;
+---------------+-------------+------------------------+-----------+
| Membership ID | Full Names | Physical Address | Saluation |
+---------------+-------------+------------------------+-----------+
| 1 | Janet Jones | First Street Plot No 4 | Ms. |
| 2 | Robert Phil | 3rd Street 34 | Mr. |
| 3 | Robert Phil | 5th Avenue | Mr. |
+---------------+-------------+------------------------+-----------+
select * from 2nf_t2;
+---------------+--------------------------+
| Membership ID | Movies Rented |
+---------------+--------------------------+
| 1 | Pirates of the Caribbean |
| 1 | Clash of the Titans |
| 2 | Forgetting Sarah Marshal |
| 2 | Daddy's Little Girls |
| 3 | Clash of the Titans |
+---------------+--------------------------+
什么是Transitive Functional Dependency
- Primary Key(X) -> (Y) 且(Y) -> (Z) 则(Z)传递函数依赖于Primay Key(X)
3NF
3NF(Third Normal Form): 满足2NF且没有Transitive Functional Dependency 即非主属性不依赖于其它非主属性
vim 3nf.sql
USE normalization;
DROP TABLE IF EXISTS `3nf_t1`;
CREATE TABLE `3nf_t1` (
`Membership ID` INT(10) NOT NULL,
`Full Names` VARCHAR(64) NOT NULL,
`Physical Address` VARCHAR(64) NOT NULL,
`Saluation ID` INT(10) NOT NULL
);
INSERT INTO `3nf_t1` VALUES
(1,'Janet Jones','First Street Plot No 4',2),
(2,'Robert Phil','3rd Street 34',1),
(3,'Robert Phil','5th Avenue',1);
DROP TABLE IF EXISTS `3nf_t2`;
CREATE TABLE `3nf_t2` (
`Membership ID` INT(10) NOT NULL,
`Movies Rented` VARCHAR(64) NOT NULL
);
INSERT INTO `3nf_t2` VALUES
(1,'Pirates of the Caribbean'),
(1,'Clash of the Titans'),
(2,'Forgetting Sarah Marshal'),
(2,'Daddy\'s Little Girls'),
(3,'Clash of the Titans');
DROP TABLE IF EXISTS `3nf_t3`;
CREATE TABLE `3nf_t3` (
`Saluation ID` INT(10) NOT NULL,
`Saluation` VARCHAR(64) NOT NULL
);
INSERT INTO `3nf_t3` VALUES
(1,'Mr.'),
(2,'Ms.'),
(3,'Mrs.'),
(4,'Dr.');
select * from 3nf_t1;
+---------------+-------------+------------------------+--------------+
| Membership ID | Full Names | Physical Address | Saluation ID |
+---------------+-------------+------------------------+--------------+
| 1 | Janet Jones | First Street Plot No 4 | 2 |
| 2 | Robert Phil | 3rd Street 34 | 1 |
| 3 | Robert Phil | 5th Avenue | 1 |
+---------------+-------------+------------------------+--------------+
select * from 3nf_t2;
+---------------+--------------------------+
| Membership ID | Movies Rented |
+---------------+--------------------------+
| 1 | Pirates of the Caribbean |
| 1 | Clash of the Titans |
| 2 | Forgetting Sarah Marshal |
| 2 | Daddy's Little Girls |
| 3 | Clash of the Titans |
+---------------+--------------------------+
select * from 3nf_t3;
+--------------+-----------+
| Saluation ID | Saluation |
+--------------+-----------+
| 1 | Mr. |
| 2 | Ms. |
| 3 | Mrs. |
| 4 | Dr. |
+--------------+-----------+
Summary
范式数据库中每个事实数据会出现且只出现一次 相反 反范式数据库中信息是冗余的可能存储在多个地方
- 范式数据库优点
很少或没有重复数据 数据占用空间更小 更新操作更少更快
很少或没有重复数据 查询数据更少需要DISTINCT或者GROUP BY
- 范式数据库缺点
因为数据存在多张表 查询数据需要关联 且会使某些索引策略失效
