## Pages

### Explain the various Normal Forms

First Normal Form (1NF) – Eliminate Repeating Groups: A relation R is said to be in First Normal Form (1NF) if and only if all the attributes of the relation R are atomic in nature. A table (relation) is in 1NF if
• There are no duplicate rows in the table
• Each cell is single-valued (i.e., there are no repeating groups or arrays)
• Entries in a column (attribute, field) are of the same kind
Note: Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.

Second Normal Form (2NF) – Eliminate Redundant Data: A relation R is said to be in Second Normal Form (2NF) if and only if
• It is in the First Normal Form (1NF), and
• No partial dependency exists between non-key attributes and key attributes
Note: If an attribute depends on only a part of the multi-valued key, remove it to a separate table.

Third Normal Form (3NF) – Eliminate Columns Not Dependant On Key: A relation R is said to be in Third Normal Form (3NF) if and only if
• It is in Second Normal Form (2NF)
• No transitive dependency exists between non-key attributes and key attributes
Note: If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key.

Boyce-Codd Normal Form (BCNF): A table is in BCNF if it is in 3NF and if every determinant is a candidate key. A 3NF relation is almost always in BCNF. However, the following conditions define some situations when a 3NF relation may not be in BCNF:
• The candidate keys are composite
• There are more than one candidate keys in the relation
• There are some common attributes in the relation
Note: If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.

Fourth Normal Form (4NF) – Isolate Independent Multiple Relationships: A table is in 4NF if it is in BCNF and if it has no multi-valued dependencies.

Note: No table may contain two or more 1:n or n:m relationships that are not directly related.

Fifth Normal Form (5NF) – Isolate Semantically Related Multiple Relationships: A table is in 5NF, also called "Projection-Join Normal Form" (PJNF), if it is in 4NF and if every join dependency in the table is a consequence of the candidate keys of the table.

Note: There may be practical constrains on information that justify separating logically related many-to-many relationships.

Domain-Key Normal Form (DKNF): A table is in DKNF if every constraint on the table is a logical consequence of the definition of keys and domains. It is a model which is free from all modification anomalies.

Summary of the normalization steps:

 Input Relation Transformation Output Relation All Relations Eliminate variable length record. Remove multi-attribute lines in table. 1NF 1NF Remove dependency of non-key attributes on part of a multi-attribute key. 2NF 2NF Remove dependency of non-key attributes on other non-key attributes. 3NF 3NF Remove dependency of an attribute of a multi attribute key on an attribute of another (overlapping) multi-attribute key. BCNF BCNF Remove more than one independent multi-valued dependency from relation by splitting relation. 4NF 4NF Add one relation relating attributes with multi-valued dependency. 5NF