normalization

normalization

• Unnormalized form (UNF) - 1NF
  Remove repeating groups
  (eliminate repeating fields/eliminate non-atomic attributes/rows uniquely identified)

  step 1.split non-atomic attributes, remove grouping attributes

  • possible anomaly
    • modification anomaly
    • insertion anomaly
    • deletion anomaly

  step 2: specify FD (functional dependencies)
  step 3: determine PK,
  step 4: determine types of FD

• First normal form (1NF) - 2NF
  Remove partial dependencies
  make sure non-key attributes are fully FD on PK

• Second normal form (2NF) -3NF
  remove transitive dependencies
  make sure non-key attributes can not be determined by another non-key attributes

• Third normal form (3NF) - BCNF (Boyce/Codd normal form)
  all determinants are candidate key (includes pk)

FD functional dependency
B is functional dependent on A
denoted A - B
A is determinate of B\
A is associated with exactly one value of B

如何决定是否 FD: A-B
必须A中的相同的值对应相同的B中相同的值，而A中不同的值可以对应B中同样的值。
A- B
2 4
3 4
7 8
3 4

key - minimal to uniquely identify all others attributes