What is ERD?

ERD(Entity Relationship Diagram) is a visual way to show how entities in a database are related to each other. Entities represent objects or concepts, like customers or orders, and relationships explain how these entities interact. ERDs help in planning the structure of a database and understanding how data flows between different parts.

Types of Relationships Between Entities

Zero One

Zero One means that an entity can have zero or exactly one related instance of another entity.

In an ERD, this type of relationship is used to show that an entity may have one or no instance associated with it. For example, if we consider an employee and a parking spot, not all employees might have a designated parking spot, but if they do, it’s only one.

Zero N

Zero N means that an entity can have zero, one, or many related instances of another entity. This relationship is more flexible because it allows for multiple associations. For example, a customer might place multiple orders or none at all.

One Only

One Only means that an entity must have exactly one instance of a related entity. This relationship is stricter because it does not allow for zero or multiple instances. For example, every order must be associated with one and only one customer.

One N

One N means that an entity must have at least one related instance but can also have many. This type of relationship shows that there is a minimum of one instance required, but there can be more. For example, a teacher might have to teach at least one class, but they can teach many classes.

Types of Cardinality

Cardinality in ERD (Entity Relationship Diagram) refers to the number of instances of one entity that can or must be associated with an instance of another entity. It defines the relationship between entities in terms of how many times one entity can relate to another. Cardinality helps to specify whether an entity can be linked to none, one, or many instances of another entity.

One-to-One (1:1)

One instance of an entity is related to exactly one instance of another entity. For example, An employee has one ID badge, and one ID badge belongs to one employee.

One-to-Many(1:N)

One instance of an entity is related to many instances of another entity. For example, A manager can oversee many employees, but each employee reports to one manager.

Many-to-One(N:1)

Many-to-One means that many instances of one entity are related to exactly one instance of another entity. This is a common relationship in databases where multiple records are tied to a single instance. For example, many employees can belong to one department.

Many-to-Many(N:N)

Many instances of one entity can relate to many instances of another entity.

For example, An employee can participate in many projects, and each project can have many employees.

Zero-to-One / Zero-to-Many

These describe optional relationships where an entity may have zero or one/many relationships.

Zero-to-One

For example, An employee may have one assigned mentor, but not all employees have a mentor.

Zero-to-Many

For example, A department may have many projects, but it can also have no projects at all.

What is DDL?

DDL(Data Definition Language) is a language used to define the structure of a database. It is used to create and modify tables, indexes, views, and schemas in the database. DDL deals with the structure of data, not the data itself. For example, Creating a new table or adding a new column to an existing table.

CREATE TABLE board
(
  ...
) COMMENT '게시물 테이블';

ALTER TABLE board
  ADD CONSTRAINT FK_user_TO_board
    FOREIGN KEY (writer_email)
    REFERENCES user (email);

What is DCL?

DCL(Data Control Language) is a language used to manage permissions in a database. It controls who can access and perform actions in the database. For example, Giving permission to a specific user to access a table, or revoking their access.

GRANT SELECT, UPDATE, DELETE, INSERT
ON board.*
TO 'developer'@'*';

What is DML?

DML (Data Manipulation Language) is a language used to manipulate data in the database. The main tasks of DML are to perform operations such as querying, inserting, updating, and deleting data. For example, you can retrieve specific data from a table or add new data to a table.

SELECT *
FROM board_list
INNER JOIN user AS U
ON B.writer_email = U.email
WHERE board_number = 1;