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

Entity Separation and Attribute Definition

An entity is something that exists and is independent and can store data.

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

For example, teachers and students at a school meet the above conditions.

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

Here, the separated entities become tables in the relational database during the logical data modeling process, and the attributes become the columns of those tables.

Identifier and Key

Korea identifies each citizen uniquely through the resident registration number system.

At this time, the resident registration number of a resident is an identifier that identifies who the person is.

The resident registration number is unique to a specific person, so it does not overlap with other people.

When representing entities as ERDs, you should specify identifiers to uniquely identify the information.

Here, the specified identifier becomes aprimary key(Primary Key) during the logical data modeling process.

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

However, synthetically generated keys are used universally because there is no risk of changes in all situations.

All attributes that can be used as identifiers in an entity are calledcandidate keys(Candidate Key), and

attributes that can be used as identifiers other than the primary key are calledalternate keys(Alternate Key).

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

In addition, in entities that represent the affiliation between departments and employees, department numbers and employee numbers are sometimes used as keys together. This is called acomposite key(Composite Key).

Relationship Identification

A relationship means the interaction between entities, and the relationship is divided into a relationship by existence and a relationship by action.

• Relationship by Existence

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

• Relationship by Action

An employee can transfer departments through an application. This is a relationship by action.

This relationship is expressed as aforeign key(Foreign Key),join(Join) during the logical data modeling process.

Cardinality

The relationship between one entity and another entity is called cardinality. This represents the numerical relationship between each other.

This can be expressed as a 1:1 relationship (one-to-one), a 1:N relationship (one-to-many), and an N:M relationship (many-to-many) between each entity.

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

Optionality

Taking the comment system as an example, each comment must have one author, but the author may not write comments.

In this case, it has a relationship between Author (Mandatory) - Comment (Optional).

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

That is, it expresses whether an entity instance must have the data or must have it optionally.