![translation](https://cdn.durumis.com/common/trans.png)
This is an AI translated post.
Logical Data Modeling
- Writing language: Korean
- •
-
Base country: All countries
- •
- Information Technology
Select Language
Summarized by durumis AI
- Logical data modeling is the process of transforming a conceptual data model into the relational database paradigm based on mapping rules.
- It explains how to handle 1:1, 1:N, and N:M relationships in relational databases, the method of creating intermediate tables, and the importance of normalization, providing a detailed explanation of the three stages (1NF, 2NF, 3NF).
- It clearly presents the objectives and principles of each normalization stage, particularly detailing the process of eliminating transitive dependencies in the third normalization.
Unlike requirements analysis and conceptual data modeling, logical data modeling is a more mechanical process.
The process mainly involves converting the ERD, a conceptual data modeling output, to conform to the relational database paradigm based on mapping rules.
Mapping Rules
- Rectangular entities are converted to tables.
- Circular attributes are converted to columns.
- Relationships are converted to PK or FK.
First of all, it is easier to express tables without FKs.
Relationships
- Express the relationships between tables considering cardinality and optionality.
- Express the relationships in order from the simplest relationship, the 1:1 relationship, to the 1:N relationship and then the N:M relationship.
1:1 Relationship Handling
In a 1:1 relationship, the dependency relationship between the two tables is examined and FK is set.
They can be seen as parent and child tables.
1:N Relationship Handling
In a 1:N relationship, N refers to 1, so FK is set to N.
N:M Relationship Handling
In order to handle N:M relationships in relational databases, a middle table (also called a mapping table or a join table) is created and expressed.
The important point here is that the cardinality and optionality of both tables referenced by the mapping table must be expressed.
Normalization
Wikipedia - Database Normalization
It is the process of transforming unrefined tables into tables suitable for relational databases.
The normalization process used industrially is up to the third normalization process, and normalization processes beyond that are mainly used academically.
Normalization should be done sequentially, one step at a time.
First Normal Form (1NF)
- Columns belonging to the table should have only atomic values.
Looking at the main principle of the first normalization, it is hard to understand what it means, but it simply means that each column should have only one value.
If there are multiple values in one column, that is, if it is not atomic, it can be difficult to join with SQL statements and can cause various problems.
Second Normal Form (2NF)
- It must satisfy the first normalization.
- Partial dependency must be eliminated.
If there are duplicate values in the table rows, find the column that the row depends on and separate it.
Third Normal Form (3NF)
- It must satisfy the first normalization.
- It must satisfy the second normalization.
- Transitive dependency must be eliminated.
The term transitive dependency is very difficult to understand. From what I understand, if there are one or more values in a particular table that implicitly imply the identifier of another table (excluding FK, of course), it seems to be called a transitive dependency.