Easy to Learn Data Modeling: From Basics to Practical Table Design (DASP/DAP)

Course Introduction: Have you ever felt frustrated during the table design process?

• What is covered in the course

• Who is this course for? (Feat. Developers and novice modelers who need DB design)

• Lecture Curriculum

• Prerequisite knowledge for taking the course

  
  

How to improve work efficiency and development productivity

• What is data modeling?

• We are not managing ERDs.

• If you design it this way, you will suffer later.

Checking my understanding of data modeling

• Quiz 1: True/False Questions

• Quiz 2: Matching the number of tables

Steps from requirements analysis to table creation

• Data modeling steps explained through a toy project

• Requirements Analysis/Definition

• Setting Subject Areas

• Conceptual Data Modeling

• Logical Data Modeling

• Physical Data Modeling

• Can the data modeling stages be simplified?

Data Modeling Notations (Barker, IE)

• Barker/IE Notation for Data Modeling

• Entity Notation

• Attribute Notation

• Identifier Notation

• Relationship Notation

• Subtype notation

Data Modeling Tools (ERwin DA# ERDCloud)

• ERwin DA# ERDCloud

• ERDCloud Practice

What is an Entity?

• Entity Concept

• Table? Entity? What's the difference?

How to derive entities from requirements

• How should entities be derived?

• The core of data modeling: 'Entity Derivation'

• [Practice] Following the Entity Derivation Process

Checking if entities have been 'properly' identified

• Whether a clear meaning has been assigned to the entity

• Whether it is an object that requires management

• Whether it forms a set

• Whether it is dependent on the business process

• Whether it possesses independence

• Whether entities were derived based on each individual screen

By classifying entities, you can understand what kind of data is being stored

• Entities also have distinct characteristics.

• The secret to excellent table design: Classifying entities by their characteristics

• Benefits of Entity Classification

What is an Attribute

• Concept of Attributes

• Components of an attribute

Attribute classification that makes query development easier

• Reasons for classifying attributes

• Basic Attributes

• Relationship Attributes

• Derived Attributes/Redundant Attributes

• System Attributes

Special attribute design that many developers miss

• Special Attributes

• Multivalued Attribute

• Composite Attribute

• Exclusive Attributes

• Code Attributes

Attribute Derivation Method

• Secrets to finding the attributes you truly need

• [Practice] Following Along with Attribute Derivation

What is an Identifier

• Concept of Identifiers

• Characteristics of Identifiers

• Classification of Identifiers

Artificial identifiers frequently used as 'OO Number'

• Natural Identifiers and Surrogate Identifiers (feat. Real Subject and Dummy Subject)

• Identifiers commonly used by entity type

• Should we use natural identifiers or surrogate identifiers?

Identifier Selection Method

• [Practice] Following the Identifier Selection Process

• Precautions when selecting identifiers

Various stories related to identifiers

• What if we unconditionally use artificial identifiers?

• Product Number Attribute Formats (000000121 vs GOD000121 vs 121)

• The habit of thinking about case data

What is a Relationship?

• Concept of Relationships

• Relationships and Joins

Components of a Relationship (Relationship Degree, Relationship Optionality, Relationship Name)

• Components of a Relationship

• Relationship Cardinality (1:1 / 1:M / M:N)

• Relationship Optionality (Optional / Mandatory)

• Relationship Name

The secret to connecting relationships easily

• The hidden meaning behind relationship lines

• Dependent Relationship

• Referential Relationship

• Identifying and Non-identifying Relationships

• Summary of Dependent/Reference and Identifying/Non-identifying Relationships

Tricky relationships encountered in practice (Multiple, Recursive, Exclusive, BOM)

• Various Relationships

• Multiple Relationships

• Recursive Relationship (= Circular Relationship)

• Exclusive Relationship (= Arc Relationship)

• BOM Relationship

Relationship Derivation Method

• [Practice] Following Relationship Derivation 1

• [Practice] Following Relationship Derivation 2

Various stories related to relationships

• Do we need Foreign Key (FK) constraints? (feat. My company doesn't create FKs)

• Cases where you don't need to connect relationship lines

• Connecting relationships for performance enhancement

Subtype and Supertype

• Concepts of Subtype/Supertype

• Subtype/Supertype Characteristics

In what situations should subtypes be used?

• Reasons for using subtypes

• Situations where subtypes should be derived

How to Derive Subtypes

• [Practice] Following the Subtype Derivation Process

• Precautions when deriving subtypes

Creating tables for entities containing subtypes

• 3 types of table creation for entities including subtypes

• Type 1. Subtype Structure

• Type 2. Integrated Table Structure

• Type 3. Individual Table Structure

• Criteria for selecting table creation types

What is Normalization

• Concept of Normalization

• Normalization: Why should we do it?

• Advantages and Disadvantages of Normalization

First Normal Form (1NF) - Let's separate duplicates!

• Definition of 1st Normal Form (1NF)

• Problems when 1st Normal Form (1NF) is not applied

• [Practice] Shall we improve it?

2nd Normal Form (2NF) - Ensure full functional dependency on the entire primary key!

• Definition of 2nd Normal Form (2NF)

• Problems when 2nd Normalization is not performed

• [Practice] Shall we improve it?

3rd Normalization - Are there dependencies between non-identifier attributes?

• Definition of 3rd Normal Form (3NF)

• Problems when 3rd Normal Form (3NF) is not applied

• [Practice] Shall we improve it?

What is De-normalization?

• Concept of Denormalization

• Essential! Checklist before performing denormalization

• Advantages and disadvantages of denormalization

3 Denormalization Methods for Performance Improvement

• Creating duplicate columns

• Creating Duplicate Tables

• Table Partitioning

Common Code

• What is a common code?

• Reasons for using codes

• Precautions when designing common codes

Common Code Design Method

• [Practice] Common Code Design Method 1

• [Practice] Common Code Design Method 2

• Common Code VS Individual Code

History Management

• What is History?

• History management is essential in OO situations.

• [Practice] History Design Method 1

• [Practice] History Design Method 2