2023. 1. 15. 23:09ㆍData science/Database
A systematic analysis of a schema to
- Eliminate redundancy
- Eliminate data modification problems
- Simplify queries on the DB
- possibly) improve efficiency
Problems with INSERTING
- If there are columns which doesn't include data: You have to fill them up with NULL, which is not recommended. (waste of memory and inefficient)
Problems with UPDATING
- If the one of value needs to be updated: If that value belongs to more than one place, you need to update more than one time. (Multiple rows to be updated)
- If there are one more columns to be added, then the entire table to be updated(Structure to be revised)
Problems with DELETING
- If you need to delete one value, but if the entire row will be deleted, you will lose other data as well.
To avoid these kinds of problems, normalization is very critical steps of database model design for the traditioanl dadabase.
Normalization
If the condition doesn't meet, then the table to be splitted and move on to the next stage. Usually tables are splitted well enough at the stage of 3NF, thus 3.5NF ~ is just for information here.
| Stage | Condition |
| 1st Normal form - 1NF | each row uniquely identified by PK (no duplication in same column) each attribute represent atomic data (no dupliction in same row) on repeating column (no duplication in tuple) |
| 2nd Normal form - 2NF | if it is already in 1NF every non-key column is dependent on the PK (all column should dependson PK) -> bridging table can be introduced |
| 3rd Normal form - 3NF | if it is already in 1NF and 2NF each column depends on the PK non-transitively(if one column depends on both PK and another, split them) |
| Beyond 3rd Normal form (Boyce-Codd Normal form, 3.5NF) |
if it is 3NF, table can have only one candidate key |
| 4NF | if it is BCNF, No attribute depends on more than one other |
| 5NF | if it is 4NF, Cannot split into more tables without loss of data |
| 6NF | if it is 5NF, only on attribute per table |
And go back to the "A systematic analysis of a schema to" and check all of them are meet!
'Data science > Database' 카테고리의 다른 글
| Understanding Database 8 - NoSQL + Python Basic (0) | 2023.01.16 |
|---|---|
| Understanding database 7 - NoSQL (0) | 2023.01.16 |
| Understanding Database 5 - Advanced SQL Queries (Stored Procedure, Trigger, Views) (0) | 2023.01.15 |
| Understanding Database 4 - Entity Relationship(ER) Model (0) | 2023.01.15 |
| Understanding Database 3 - Relational Algebra (관계대수) (0) | 2023.01.15 |