Normalization Cheatsheet for CSAD

NFs.md

Keys

Primary Key

• serve to uniquely identify a row in the table
• build an index on Primary key to facilitate fast search and retreival

Foreign Key

• a key that is used to link 2 tables together
• table containing foreign key -> child table
• table containing primary key -> referenced or parent table

Referential integrity

• prevents invalid data from being inserted
• row in the parent table cannot be deleted if referenced
• primary key of a row in the parent table cannot be updated

Normal Forms

1NF

• Each column can only have atomic (a single) value
• Unique column name

2NF & 3NF

non-key field must provide a fact about the key and only the key

2NF

• No partial dependency
• satisfies 1NF, 2NF
• Not partial means full
• Hence, Full functional dependency

3NF

• No transistive dependency
• satisfies 1NF, 2NF, 3NF

Boyce-Codd Normal Form

• AKA 3.5NF
• satisfies {1..3}NF, 3.5NF
• Primary key for every functional dependency
• X->Y, X should be primary key

Functional Dependency

What it means

      x        ->        y
(determinant)       (dependent)

• x is a key field
• y is a non-key field
• Can read the values of y by using the value of x from a table

Partial Dependency

• non-key field is not fully dependent on primary key

Example

• Primary keys: student_id, subject_id
• subject_id -> teacher
• teacher column only dependes on subject and NOT on student

Transisitve Dependency

• non-key field depends on another non-key filed
• which doesn't depend on primary key

Example

• Primary keys: student_id, subject_id
• exam_name -> total_marks
• total_marks depends on exam_name which is NOT a primary key