Normalisation of database

Normalisation (1NF 2NF 3NF BCNF)

Normalisation是发明relational model的人(Edgar Codd)提出的。旨在减少数据冗余(redundancy)。

1NF

  • Each table cell should contain a single value.
  • Each record (row) needs to be unique. (primary key) 
Key
Key可以是一个column，也可以多个columns链接而成。
Primary Key
  • A primary key cannot be NULL
  • A primary key value must be unique（一行一个不能重复）
  • The primary key values should rarely be changed
  • The primary key must be given a value when a new record is inserted.
Composite Key: a primary key with multiple columns.
 

2NF Fully Functional Dependency

  • Rule 1- Be in 1NF
  • Rule 2- Fully Functional Dependencies
Fully Functional Dependencies
每一个non-key attributes必须函数依赖于整个primary key（而不能有依赖于其子集的现象）。
函数依赖：X->Y, Y函数依赖于X, 即给定一个X，Y是X的函数 (Y有且只有一个唯一值)；
完全函数依赖：(X1, X2)->Y, Y依赖于(X1, X2), 但X1->Y or X2->Y不成立。
Forein Key
  • Can be NULL
  • Do not have to be unique
  • Can have a different name from the primary key of another table.
You will only be able to insert values into your foreign key that exist in the unique key in the parent table. This helps in referential integrity. 

3NF No Transitive Functional Dependency

  • Rule 1- Be in 2NF
  • Rule 2- Has no transitive functional dependencies
transitive functional dependencies:
superkey X->Y->Z (e.g. locationID->zipNumber->cityName)
superkey是能uniquely identify record的key（可能是多个columns的组合），而candidate key是其子集，是最少columns的superkey。

BCNF

  • Rule 1 - Be in 3NF
  • Rule 2 - for every functional dependency X->Y, X should be the super key of the table

Integrity Constraints

entity integrity
The rows (or tuples) in a relation represent entities, and each one must be uniquely identified. Hence we have the primary key that must have a unique non-null value for each row.
referential integrity
This constraint involves the foreign keys. Foreign keys tie the relations together, so it is vitally important that the links are correct. Every foreign key must either be null or its value must be the actual value of a key in another relation.
参考

posted @ 2021-02-26 19:34  jp_blog  阅读(58)  评论(0)    收藏  举报