Logical Data Modeling

Compared to requirement analysis and conceptual data modeling, logical data modeling is a more mechanical process.

The process mainly involves converting the ERD, which is the output of conceptual data modeling, into a relational database paradigm based on the mapping rule.

Mapping Rule

• Rectangular entities are converted into tables.
• Circular attributes are converted into columns.
• Relationships are converted into PK or FK.

It is generally easier to express tables without FKs first.

Relationship

• Express the relationship between tables considering cardinality and optionality.
• Express the relationships in the order of the simplest 1:1 relationship, followed by the 1:N relationship, and then the N:M relationship.

Handling 1:1 Relationship

In a 1:1 relationship, examine the dependency relationship between the two tables and set the FK.

They can be viewed as parent and child tables.

Handling 1:N Relationship

In a 1:N relationship, since 1 is referenced by N, set the FK in N.

Handling N:M Relationship

To handle an N:M relationship in a relational database, an intermediate table (also called a mapping table or a junction table) is created and expressed.

Importantly, the cardinality and optionality of both tables referenced based on the mapping table should be expressed.

Normalization

It is the process of transforming an unrefined table into a form suitable for a relational database.

The normalization process used in industry is up to the 3rd Normal Form, and normalization processes beyond that are mainly used academically.

Normalization should be performed sequentially, one step at a time.

1st Normal Form (1NF)

• Columns belonging to a table must have only atomic values.

Looking at the fundamental principle of the 1st Normal Form, it may be difficult to understand what it means, but it simply means that each column must have only one value.

If multiple values are contained within a single column, i.e., it is not atomic, it can be difficult to join using SQL statements and can cause various problems.

2nd Normal Form (2NF)

• Must satisfy the 1st Normal Form.
• Partial dependencies must be eliminated.

If there are duplicate values among the rows of a table, find the column on which the row depends and separate it.

3rd Normal Form (3NF)

• Must satisfy the 1st Normal Form.
• Must satisfy the 2nd Normal Form.
• Transitive dependencies must be eliminated.

The term "transitive dependency" is quite difficult to understand. From my understanding, if there is one or more values in a specific table that implicitly represent the identifier of another table (excluding FKs, of course), it seems that it is called a transitive dependency.