Conceptual Data Modeling

Conceptual data modeling is the process of separating related information into entity units and expressing the relationships between entities using an ERD.

Entity Separation and Attribute Definition

An entity refers to something that exists independently and can store data.

Also, an attribute refers to the data that an entity possesses.

For example, teachers and students in a school satisfy the above conditions.

Here, a teacher is an entity that includes data such as name, age, and subject, and a student is also an entity that includes data such as name, grade, age, and student ID.

In this case, the separated entities become tables in a relational database during the logical data modeling process, and the attributes become the columns of that table.

Identifier and Key

South Korea uses the Resident Registration Number system to uniquely identify each citizen.

In this case, a resident's Resident Registration Number is the identifier that allows you to identify who that person is.

Since the Resident Registration Number uniquely identifies a specific person, it does not overlap with other people.

When expressing entities in ERD, you must also designate an identifier to uniquely identify the corresponding information.

The designated identifier here becomes the Primary Key during the logical data modeling process.

If there is no naturally unique attribute that can be designated as an identifier, an Artificial Key is used.

However, artificially generated keys are generally used because there is no risk of change in all situations.

All attributes that can be used as identifiers in an entity are called Candidate Keysand

attributes that can be used as identifiers other than the primary key are called Alternate Keys.

And these alternate keys are good for indexing to improve performance.

In addition, in an entity representing the affiliation between a department and an employee, there are cases where the department number and employee number are used together as a key. This is called a Composite Key.

Relationship Understanding

A relationship refers to the interaction between entities, and relationships are divided into existence-based relationships and action-based relationships.

• Existence-based Relationship

For example, in a company, an employee cannot exist without a department, so it is an existence-based relationship.

• Action-based Relationship

An employee can transfer departments through an application. At this time, it is an action-based relationship.

This relationship is expressed as a Foreign Key, Join etc. during the logical data modeling process.

Cardinality

The relationship between one entity and another entity is called cardinality. This expresses a numerical relationship between each other.

This is also expressed as a 1:1 relationship (one-to-one), a 1:N relationship (one-to-many), and an N:M (many-to-many) relationship.

Among the above relationships, the N:M relationship cannot be expressed in a relational database, so it is expressed by creating an intermediate table.

Optionality

Using a comment system as an example, each comment must have one author, but the author can choose not to write a comment.

In this case, it has an author (Mandatory)-comment (Optional) relationship.

Mandatory is called Mandatory, and optional options are called Optional.

In other words, it expresses whether an instance of an entity must have the corresponding data or whether it must have it optionally.