(DB실무) Part0-Precourse-Conceptual Data Modeling

Main ideas & lessons

1) What “data modeling” is and where it fits

• Data modeling is described as a database design process.
• The overall modeling/design work is split into two stages:
  • Conceptual modeling (covered in this lecture)
  • Logical modeling (promised for the next lecture)
• A database system design process is described as 4 main stages:
  1. Requirements analysis
  2. Conceptual design
  3. Logical design
  4. Physical design (implementation considerations)

2) Why databases are necessary (vs. systems that don’t need one)

Databases are needed especially when:

• There is a lot of data (e.g., transactions, purchase records, course history).
• Many users access simultaneously (banks, online shopping, reservations).
• Databases enable efficient storage + retrieval + management rather than hardcoded logic.

Examples used to explain necessity:

• Banking transactions require persistent records.
• Hotel/airline reservations need stored reservation info.
• Traffic lights require remembering current state, but that can be handled by circuits rather than a DB.
• Digital door locks: the “state” (password correctness) is small-scale logic, not massive data management.

3) Database system components: DB, DBMS, and queries (SQL)

• A database stores:
  • Data values (e.g., student records)
  • Metadata / schema information (described structure)
• A DBMS (Database Management System) is the software that:
  • Receives user queries (often in SQL)
  • Uses metadata to understand structure
  • Retrieves/updates the required data
• SQL is presented as the standard language to access DBs:
  • SQL statements are categorized by function:
    • DDL: define/describe schema (tables, relationships, columns)
    • DML: insert/update/delete/query data (most used by general users)
    • Others (not focused now): transaction/security-style statements (e.g., DCL/TCL)

4) Relationship between data, schema, and instances

• Schema:
  • The structure of the database (tables, data types, constraints/specifications)
  • Defined during design; intended to change less frequently
• Instance:
  • The actual stored data at a specific time
  • Changes frequently as events occur
• A notion akin to a snapshot is mentioned: capturing the data state at a moment.

5) Conceptual modeling using Entity-Relationship (ER) model

This lecture focuses on conceptual modeling using the ER model, also referred to as:

• Entity-Relationship Model (ER/E-R model)
• Notation involving entities, relationships, and attributes

Diagramming uses shapes (and connectors) to represent:

• Entities (e.g., Student, Department)
• Relationships (e.g., “takes”, “belongs to”)
• Attributes (e.g., student ID, department name)
• Keys/identifiers
• Cardinality/correspondence counts

---

Detailed methodology / instructions (how to do ER modeling in this course)

A) Overall workflow for conceptual modeling (ER modeling)

Use a staged approach consistent with the video’s “recommended order”:

1. Requirements analysis
   • Extract important “things” to manage (candidate entities)
   • Extract relationships implied by the job description
2. Extract entities first
   • Identify major entities (common nouns) and discard items that only represent context or a single fixed value.
3. Extract attributes for each entity
   • Determine which properties belong to the entity.
4. Determine relationships between entities
   • Use hints from requirements (e.g., “employee supervises employee”, “employee participates in project”)
5. Derive keys/identifiers
   • Ensure each strong entity can be uniquely identified
   • Consider weak entities if identification requires additional info.
6. Add cardinality/correspondence numbers
   • Use “1, n, m” types (one-to-one, one-to-many, many-to-many)
   • For relationships, decide how many instances can correspond.
7. Refine until the ERD is consistent

B) Recommended ER modeling “practice order” for term projects (explicitly stated)

The lecturer repeatedly recommends this order for drawing ERD:

• Extract the entity first
• Draw relationships after entities
• Then draw attributes
• Check for keys / weak entities
• Fix dotted/solid-notational issues
• Finish with cardinalities

C) Choosing entities vs not entities (a key judgment rule)

To decide if something becomes an entity, the video emphasizes:

• If the “thing” can have multiple records in general cases, it’s a candidate entity.
• If it is always a single fixed value for the system’s scope, it may not be an entity.
  • Example logic: “School” might be a single record for Kookmin University system, but not if multiple schools are supported.

D) Handling attributes in ER model

The lecture provides attribute classification and modeling rules:

1) Single-value vs multi-value (multi occurrence)

• Attributes that can have multiple values for one entity (e.g., multiple hobbies) are treated as multi-valued.

2) Simple vs composite attributes

• Simple attribute: cannot be naturally split (e.g., age as a single value)
• Composite attribute: can be broken down (e.g., address into city/district/detail)

3) Derived attributes (induced from other attributes)

• Some attributes need not be stored because they can be computed:
  • Age derived from birth date/resident registration number
  • Total/average scores derived from component scores
• In diagrams, derived attributes are marked distinctly (the video mentions dotted-style indication).

---

Key conceptual content (what ER elements mean)

1) Entity and relationship definitions

• Entity: a meaningful piece of information (physical or conceptual), often a noun:
  • Examples: Student, Department, Project, Course
• Relationship: the connection between entities:
  • Examples: student takes course, employee belongs to department, department manages project

2) Attributes

• Attributes are properties possessed by an entity (or sometimes a relationship).
• The video stresses that not all properties belong to entities; some properties belong specifically to the relationship itself.

---

Cardinality / correspondence (how many instances connect)

The video explains correspondence as cardinality in ER:

• 1-to-1: one entity instance corresponds to exactly one other instance
• 1-to-n: one maps to many
• m-to-n: many maps to many
• It also introduces:
  • Binary relationship (most common)
  • Mentions higher-order relationships (ternary etc.) but notes they’re harder and not common in practice for this class context.

---

Relationship attributes: “attributes of the relationship” vs “attributes of entities”

A major modeling lesson:

• Sometimes a property depends on the pair (entity1, entity2), i.e., belongs to the relationship instance, not either entity alone.

Example:

• An employee’s start date for a department:
  • If you attach start date to the Employee entity directly, it becomes ambiguous because the employee can work in multiple departments.
  • Correct modeling: attach start date to the relationship “Employee works-for Department”.

The same reasoning is applied to:

• Many-to-many participation attributes (e.g., weekly working hours per employee-per-project).

---

Keys and identifiers (core for ER-to-relational mapping)

1) Key attribute (identifier) definition

• A key attribute (identifier) uniquely identifies every instance of an entity.
• The lecture highlights:
  • Names alone are usually not unique
  • IDs/registration numbers usually are unique
  • Uniqueness is across all possible instances/snapshots.

2) Composite keys

• When no single attribute uniquely identifies, combine attributes:
  • Example: (team name, jersey number) identifies a player within a context.

3) Weak entities and partial keys

• A weak entity is an entity that cannot be uniquely identified by its own attributes.
• It requires identification through:
  • A strong entity it depends on
  • Plus a partial key (attributes that partially identify)
• The video’s main weak-entity case: Dependents depend on an Employee identifier (e.g., dependent identity isn’t unique without the employee’s resident registration number).

4) Primary key terminology nuance

• The video notes terminology differences between:
  • Conceptual ER “key” / “ID”
  • Logical design “primary key”
• Primary key in logical design must exist and be unique per table row.

---

Entity generalization (Inheritance-like modeling)

The lecture covers generalization as:

• When multiple subtypes share common attributes:
  • Example: Student → Graduate / Undergraduate / High school / Retaker
• Common attributes go in the parent/general entity.
• Unique attributes belong to the child entities.
• Diagrammatically, inherited attributes flow from parent to children (the lecture uses “ISA-like” structure in explanation).

---

Mentions of ER modeling complexity (and how to manage it)

• The lecturer emphasizes:
  • ER modeling is partly art, not purely science.
  • There may be multiple valid ERD designs depending on interpretation.
  • Practical training and repeated practice are necessary.

---

Video closing / course guidance

• The lecturer asks students to:
  • Practice ER modeling repeatedly
  • Compare their ERD against provided solutions later (online class/Q&A)
  • Ask questions on which modeling is better/consistent
• The lecturer states that conceptual modeling content is heavy and must be understood before logical modeling.

---

Speakers / sources featured

• Kim Nam-gyu (the presenter/lecturer)