What do you know about normal forms in databases?
sobes.tech AI
Answer from AI
Normal forms (NF) are a set of rules and guidelines for designing the structure of a relational database, aimed at minimizing data redundancy and preventing anomalies during insert, update, and delete operations.
Main normal forms:
-
First Normal Form (1NF):
- Each attribute (column) must contain atomic (indivisible) values.
- No repeating groups of attributes.
- Each row must be unique (presence of a primary key).
-
Second Normal Form (2NF):
- It is in 1NF.
- Every non-key attribute must fully depend on the entire primary key (if the primary key is composite).
-
Third Normal Form (3NF):
- It is in 2NF.
- No transitive dependencies of non-key attributes on the primary key (a non-key attribute should not depend on another non-key attribute).
-
Boyce-Codd Normal Form (BCNF):
- A stricter version of 3NF.
- Every non-trivial dependency $X \to Y$ must have $X$ as a superkey.
-
Fourth Normal Form (4NF):
- It is in BCNF.
- No multi-valued dependencies.
-
Fifth Normal Form (5NF):
- It is in 4NF.
- No join dependencies.
Goals of normalization:
- Reduce data redundancy.
- Prevent anomalies (insertion, update, deletion).
- Ensure data integrity.
- Simplify the database structure.
Example:
Table with redundancy (not in 1NF):
| OrderID | Product | Price |
|---|---|---|
| 1 | Laptop, Mouse | 1000, 20 |
| 2 | Keyboard | 50 |
Normalized table (in 1NF and above):
"Orders" table:
| OrderID |
|---|
| 1 |
| 2 |
"Order Items" table:
| ItemID | OrderID | Product | Price |
|---|---|---|---|
| 1 | 1 | Laptop | 1000 |
| 2 | 1 | Mouse | 20 |
| 3 | 2 | Keyboard | 50 |
Normalization is an important step in database design, but excessive normalization can lead to increased joins during queries, which may negatively impact performance. The optimal level of normalization depends on specific requirements and trade-offs. Denormalization is sometimes used to optimize performance in certain scenarios, intentionally violating normal form rules.