DB-4 ERD (23/01/13)

1. ERD

(https://dbdiagram.io/home)

ERD는 Entity Relationship Diagram의 약자로,
데이터베이스 구조와 테이블간의 관계를 시각적으로 도식화한 다이어그램을 말합니다

ERD 표기 예시

• 1:1 관계 : 한 테이블의 행이 다른 테이블의 행과 일대일 관계를 가지는 것을 뜻합니다
  (주로 id나 username과 같은 계정 정보)

• N:1 관계 : 일대다 or 다대일. 하나의 행이 두개 이상의 행과 관계를 맺는 것을 의미합니다
  만약 주문 테이블과 고객 테이블이 있다면 고객 테이블에는 한 명의 고객이 한 개의 행으로 표현되지만
  주문 테이블에는 한 명의 고객이 여러 개의 주문을 할 수 있어서 각 주문 행은 고객 행과 N:1 관계를 가집니다

• M:N 관계 : 다대다 관계라고 하며, 양쪽 모두 하나 이상의 행과 연결될 수 있습니다
  예를 들어 학생 테이블에는 여러명의 학생이 있고 수업 테이블에는 여러개의 과목이 있으며
  학생들은 여러 과목을 듣고 과목들은 여러 학생들이 듣는 경우 M:N 관계가 됩니다
  이러한 관계는 중간 테이블(join table)을 통해 관리됩니다

• PK : Primary Key. 테이블의 고유 식별자로서 테이블의 각 행(row)을 구별하는데 사용됩니다
  PK는 테이블에서 값이 유일해야하며 NULL 값을 가질 수 없습니다
  
  
• FK : Foreign Key. 다른 테이블의 PK를 참조하는 외래 키로서, 테이블 간의 관계를 나타냅니다

ERD에서 PK와 FK는 테이블 간의 관계를 나타내며 데이터베이스의 정합성과 일관성을 보장해줍니다
예를 들어, 만약 고객 테이블과 주문 테이블이 있다면
고객 테이블의 PK(고객 ID)는 주문 테이블의 FK(고객 ID)로 참조되어 서로가 관계를 맺고 있음을 파악할 수 있습니다

2. 제약 조건

제약 조건이란 테이블관의 관계를 맺을 때 어떤 특정 규칙이나 조건을 적용하는 것(잘못된 데이터의 입력을 방지하기 위해)을 의미합니다

예를 들어, NOT NULL 제약 조건은 특정 컬럼에 대해 NULL 값을 허용하지 않겠다는 뜻이며
UNIQUE 제약 조건은 특정 컬럼에 대해 중복 값을 허용하지 않겠다는 것입니다
FOREIGN KEY 제약 조건은 테이블 간의 관계를 생성하고, 참조되는 테이블의 값이 존재하는지 확인하는데 사용됩니다

그 외에도 다음과 같은 제약 조건들이 있습니다

• NOT NULL : NULL값 입력 금지
• UNIQUE : 중복값 입력 금지 (NULL값은 중복 가능)
• PRIMARY KEY : NOT NULL + UNIQUE
• FOREIGN KEY : 외부 테이블의 고유 키값
• CHECK : 조건으로 설정된 값만 입력 허용

3. 관계 그리기

3-1. 제약사항 설정

외래키 추가

-- 제약사항을 생성합니다
ALTER TABLE `Board` -- 자식테이블
    ADD CONSTRAINT fk_board_userid
    FOREIGN KEY (userid) -- 자식테이블에서 부모의 식별자를 담는 필드명, 자식테이블 외래키(FK)
    REFERENCES User(userid);
    --ON DELETE
    -- ON UPDATE

-- Query OK!


SELECT * FROM information_schema.table_constraints;
-- 테이블의 모든 제약사항을 조회합니다
-- 데이터베이스명.테이블


SELECT * FROM information_schema.table_constraints
WHERE TABLE_SCHEMA = 'sample' AND TABLE_NAME = 'Board';
-- 해당 데이터베이스 속 테이블의 제약사항을 조회합니다


+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def                | sample            | PRIMARY         | sample       | Board      | PRIMARY KEY     | YES      |
| def                | sample            | fk_board_userid | sample       | Board      | FOREIGN KEY     | YES      |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+


ALTER TABLE `Board` DROP CONSTRAINT fk_board_userid;
-- 해당 제약사항을 삭제합니다

+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def                | sample            | PRIMARY         | sample       | Board      | PRIMARY KEY     | YES      |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
1 row in set (0.00 sec)

제약사항 적용하기

-- 제약사항을 생성합니다
ALTER TABLE `Board` -- 자식테이블
    ADD CONSTRAINT fk_board_userid
    FOREIGN KEY (userid) -- 자식테이블에서 부모의 식별자를 담는 필드명, 자식테이블 외래키(FK)
    REFERENCES User(userid)
    ON DELETE CASCADE;
    -- ON UPDATE CASCADE
    -- || NO ACTION ~ RESTRICT

UPDATE Board SET userid='KAKAO' WHERE id=3;

userid에 관한 제약사항때문에 실행이 불가능합니다

제약사항 설정은 테이블을 만들 때 미리 해 두어야합니다
(테이블에 필드 row가 추가되기 전에)

mysql> SELECT * FROM Board;
+----+------------+---------+---------------------+------+---------+
| id | subject    | content | register_date       | hit  | userid  |
+----+------------+---------+---------------------+------+---------+
|  1 | 게시글1    | 1234    | 2023-01-12 03:21:07 |    0 | web7722 |
|  2 | 게시글2    | 1234    | 2023-01-12 03:21:07 |    0 | web7722 |
|  3 | 게시글3    | 1234    | 2023-01-12 03:21:07 |    0 | web7722 |
|  4 | 게시글4    | 1234    | 2023-01-12 03:21:07 |    0 | web7722 |
|  5 | 게시글5    | 1234    | 2023-01-12 03:21:07 |    0 | admin   |
|  6 | 게시글6    | 1234    | 2023-01-12 03:21:07 |    0 | admin   |
+----+------------+---------+---------------------+------+---------+


DELETE FROM User WHERE userid="web7722";


mysql> SELECT * FROM Board;
+----+------------+---------+---------------------+------+--------+
| id | subject    | content | register_date       | hit  | userid |
+----+------------+---------+---------------------+------+--------+
|  5 | 게시글5    | 1234    | 2023-01-12 03:22:17 |    0 | admin  |
|  6 | 게시글6    | 1234    | 2023-01-12 03:22:17 |    0 | admin  |
+----+------------+---------+---------------------+------+--------+

Board 테이블에서도 해당 id가 쓴 게시글이 삭제된 것을 확인할 수 있습니다

ALTER TABLE `Board` -- 자식테이블
    ADD CONSTRAINT fk_board_userid
    FOREIGN KEY (userid) -- 자식테이블에서 부모의 식별자를 담는 필드명, 자식테이블 외래키(FK)
    REFERENCES User(userid)
    ON DELETE SET NULL;


    -- ON DELETE CASCADE 외래키로 연결된 레코드 모두 삭제
    -- ON DELETE RESTRICT 관련 레코드가 있으면 실행을 막음
    -- ON DELETE SET NULL 제약에 해당하는 ID가 삭제되면 관련 레코드가 NULL값이 됩니다
        -- *자식 테이블에서 FK내용이 NOT NULL이 되어서는 안됩니다




DELETE FROM User WHERE userid="web7722";

mysql> SELECT * FROM Board;
+----+------------+---------+---------------------+------+--------+
| id | subject    | content | register_date       | hit  | userid |
+----+------------+---------+---------------------+------+--------+
|  1 | 게시글1    | 1234    | 2023-01-12 03:28:41 |    0 | NULL   |
|  2 | 게시글2    | 1234    | 2023-01-12 03:28:41 |    0 | NULL   |
|  3 | 게시글3    | 1234    | 2023-01-12 03:28:41 |    0 | NULL   |
|  4 | 게시글4    | 1234    | 2023-01-12 03:28:41 |    0 | NULL   |
|  5 | 게시글5    | 1234    | 2023-01-12 03:28:41 |    0 | admin  |
|  6 | 게시글6    | 1234    | 2023-01-12 03:28:41 |    0 | admin  |
+----+------------+---------+---------------------+------+--------+        

3-2. JOIN

SELECT * 
FROM Board as A
JOIN User as B
  ON A.userid = B.userid;

+----+------------+---------+---------------------+------+---------+---------+--------+-----------+
| id | subject    | content | register_date       | hit  | userid  | userid  | userpw | username  |
+----+------------+---------+---------------------+------+---------+---------+--------+-----------+
|  1 | 게시글1    | 1234    | 2023-01-12 03:31:22 |    0 | web7722 | web7722 | 1234   | jjooo     |
|  2 | 게시글2    | 1234    | 2023-01-12 03:31:22 |    0 | web7722 | web7722 | 1234   | jjooo     |
|  3 | 게시글3    | 1234    | 2023-01-12 03:31:22 |    0 | web7722 | web7722 | 1234   | jjooo     |
|  4 | 게시글4    | 1234    | 2023-01-12 03:31:22 |    0 | web7722 | web7722 | 1234   | jjooo     |
|  5 | 게시글5    | 1234    | 2023-01-12 03:31:22 |    0 | admin   | admin   | 1234   | 관리자    |
|  6 | 게시글6    | 1234    | 2023-01-12 03:31:22 |    0 | admin   | admin   | 1234   | 관리자    |
+----+------------+---------+---------------------+------+---------+---------+--------+-----------+  


SELECT * FROM User;
+---------+--------+-----------+
| userid  | userpw | username  |
+---------+--------+-----------+
| admin   | 1234   | 관리자    |
| web7722 | 1234   | jjooo     |
+---------+--------+-----------+


SELECT * FROM Board;
+----+------------+---------+---------------------+------+---------+
| id | subject    | content | register_date       | hit  | userid  |
+----+------------+---------+---------------------+------+---------+
|  1 | 게시글1    | 1234    | 2023-01-12 03:31:22 |    0 | web7722 |
|  2 | 게시글2    | 1234    | 2023-01-12 03:31:22 |    0 | web7722 |
|  3 | 게시글3    | 1234    | 2023-01-12 03:31:22 |    0 | web7722 |
|  4 | 게시글4    | 1234    | 2023-01-12 03:31:22 |    0 | web7722 |
|  5 | 게시글5    | 1234    | 2023-01-12 03:31:22 |    0 | admin   |
|  6 | 게시글6    | 1234    | 2023-01-12 03:31:22 |    0 | admin   |
+----+------------+---------+---------------------+------+---------+


SELECT
    A.id,
    A.subject,
    A.register_date,
    A.hit,
    A.userid,
    B.username
FROM Board as A
JOIN User as B
  ON A.userid = B.userid;

+----+------------+---------------------+------+---------+-----------+
| id | subject    | register_date       | hit  | userid  | username  |
+----+------------+---------------------+------+---------+-----------+
|  1 | 게시글1    | 2023-01-12 03:31:22 |    0 | web7722 | jjooo     |
|  2 | 게시글2    | 2023-01-12 03:31:22 |    0 | web7722 | jjooo     |
|  3 | 게시글3    | 2023-01-12 03:31:22 |    0 | web7722 | jjooo     |
|  4 | 게시글4    | 2023-01-12 03:31:22 |    0 | web7722 | jjooo     |
|  5 | 게시글5    | 2023-01-12 03:31:22 |    0 | admin   | 관리자    |
|  6 | 게시글6    | 2023-01-12 03:31:22 |    0 | admin   | 관리자    |
+----+------------+---------------------+------+---------+-----------+


-- 위의 JOIN은 기본적으로 INNER JOIN입니다
-- 아래는 LEFT JOIN


SELECT
    A.id,
    A.subject,
    A.register_date,
    A.hit,
    A.userid,
    B.username
FROM Board as A
LEFT OUTER JOIN User as B
  ON A.id = B.userid;
-- LEFT OUTER JOIN은 공유 데이터가 없으면 NULL 값을 출력합니다

+----+------------+---------------------+------+---------+----------+
| id | subject    | register_date       | hit  | userid  | username |
+----+------------+---------------------+------+---------+----------+
|  1 | 게시글1    | 2023-01-12 03:31:22 |    0 | web7722 | NULL     |
|  2 | 게시글2    | 2023-01-12 03:31:22 |    0 | web7722 | NULL     |
|  3 | 게시글3    | 2023-01-12 03:31:22 |    0 | web7722 | NULL     |
|  4 | 게시글4    | 2023-01-12 03:31:22 |    0 | web7722 | NULL     |
|  5 | 게시글5    | 2023-01-12 03:31:22 |    0 | admin   | NULL     |
|  6 | 게시글6    | 2023-01-12 03:31:22 |    0 | admin   | NULL     |
+----+------------+---------------------+------+---------+----------+

3-3. ORM 사용

Sequelize로 JOIN문 구현하기

[user.model.js]

module.exports = (sequelize, Sequelize) => {
  class User extends Sequelize.Model {
    static initialize() {
      return this.init(
        {
          userid: {
            type: Sequelize.STRING(30),
            primaryKey: true,
          },
          userpw: {
            type: Sequelize.STRING(64),
            allowNull: false,
          },
          username: {
            type: Sequelize.STRING(20),
            alllowNull: false,
          },
        },
        {
          sequelize,
        }
      ); // super.init()
    }
    static associate(models) {
      this.hasMany(models.Board, {
        foreignKey: "userid",
      });
    }
  }
  User.initialize();

  return User;
};

[board.model.js]

module.exports = (sequelize, Sequelize) => {
  class Board extends Sequelize.Model {
    static initialize() {
      return this.init(
        {
          subject: {
            type: Sequelize.STRING(100),
            allowNull: false,
          },
          content: {
            type: Sequelize.TEXT,
            allowNull: false,
          },
          register_date: {
            type: Sequelize.DATE,
            allowNull: false,
            defaultValue: Sequelize.fn("now"),
          },
          hit: {
            type: Sequelize.INTEGER,
            defaultValue: 0,
          },
          userid: {
            type: Sequelize.STRING(100),
            allowNull: true,
          },
        },
        {
          sequelize,
        }
      );
    }
    static associate(models) {
      this.belongsTo(models.User, {
        foreignKey: "userid",
      });
    }
  }
  Board.initialize();
  return Board;
};

[index]

const config = require("../config")["db"];
const Sequelize = require("sequelize");

const sequelize = new Sequelize(
  config.database,
  config.username,
  config.password,
  config
);

require("./user.model")(sequelize, Sequelize);
require("./board.model")(sequelize, Sequelize);

const { models } = sequelize
// models.User.associate(models)
// models.Board.associate(models)


// sequelize에서 join을 사용하기 위한 메서드 실행
for (const key in models) {
    if (typeof models[key].associate !== "function") continue;
    models[key].associate(models)
}


(async () => {
  await sequelize.sync({ force: true });

  const {
    models: { User, Board },
  } = sequelize
  
  await User.create({ userid:"web7722", userpw:'1234', username: "jjooo"})
  await User.create({ userid:"admin", userpw:'1234', username: "관리자"})


  await Board.create({ subject:"게시글1", content:'1234', userid: "web7722"})
  await Board.create({ subject:"게시글2", content:'1234', userid: "web7722"})
  await Board.create({ subject:"게시글3", content:'1234', userid: "web7722"})
  await Board.create({ subject:"게시글4", content:'1234', userid: "web7722"})
  await Board.create({ subject:"게시글5", content:'1234', userid: "admin"})
  await Board.create({ subject:"게시글6", content:'1234', userid: "admin"})

  const board = await Board.findAll({ 
    raw: true,
    nest: true, // 가져온 유저 데이터를 객체로 변환 User: { userid: 'admin', userpw: '1234', username: '관리자' }
    include:[{ model: User }],
 })
  console.log(board)
})();

module.exports = {
  Sequelize,
  sequelize,
};


// 실행 결과
[
  {
    id: 1,
    subject: '게시글1',
    content: '1234',
    register_date: 2023-01-12T05:01:18.000Z,
    hit: 0,
    userid: 'web7722',
    User: { userid: 'web7722', userpw: '1234', username: 'jjooo' }
  },
  {
    id: 2,
    subject: '게시글2',
    content: '1234',
    register_date: 2023-01-12T05:01:18.000Z,
    hit: 0,
    userid: 'web7722',
    User: { userid: 'web7722', userpw: '1234', username: 'jjooo' }
  },
  {
    id: 3,
    subject: '게시글3',
    content: '1234',
    register_date: 2023-01-12T05:01:18.000Z,
    hit: 0,
    userid: 'web7722',
    User: { userid: 'web7722', userpw: '1234', username: 'jjooo' }
  },
  {
    id: 4,
    subject: '게시글4',
    content: '1234',
    register_date: 2023-01-12T05:01:18.000Z,
    hit: 0,
    userid: 'web7722',
    User: { userid: 'web7722', userpw: '1234', username: 'jjooo' }
  },
  {
    id: 5,
    subject: '게시글5',
    content: '1234',
    register_date: 2023-01-12T05:01:18.000Z,
    hit: 0,
    userid: 'admin',
    User: { userid: 'admin', userpw: '1234', username: '관리자' }
  },
  {
    id: 6,
    subject: '게시글6',
    content: '1234',
    register_date: 2023-01-12T05:01:18.000Z,
    hit: 0,
    userid: 'admin',
    User: { userid: 'admin', userpw: '1234', username: '관리자' }
  }
]