Post

[Database] (쉬운 코드) SQL 개념 및 데이터베이스 정의 방법

쉬운 코드 강의 「데이터베이스」 내용을 바탕으로, SQL의 개념과 SQL로 데이터베이스를 정의하는 내용을 정리한 포스트 입니다.

Posted Updated
By Seojin Park
9 min read
[Database] (쉬운 코드) SQL 개념 및 데이터베이스 정의 방법

SQL

SQL(Structured Query Language)는 현업에서 쓰이는 relational DBMS의 표준 언어로 DDL + DML + VDL을 다루는 종합적인 database 언어이다.

SQL의 주요 용어는 다음과 같다.

relational data modelSQL
relationtable
attributecolumn
tuplerow
domaindomain

SQL에서 relation은 데이터 모델에서의 relation과 개념이 다르다. SQL의 relation은 multiset(=bag) of tuples 이다. 이는 중복된 tuple을 허용한다는 것이다.

SQL은 RDBMS의 표준 언어이지만 실제 구현에 강제가 없기 때문에 RDBMS마다 제공하는 SQL의 스펙이 조금씩 다르다.

SQL로 DB 정의

IT 회사 관련 RDB를 만들어보자.

  • 부서, 사원, 프로젝트 관련 정보들을 저장할 수 있는 관계형 데이터베이스를 만들자.
  • 사용 RDBMS : MySQL(InnoDB)

InnoDB: MySQL에서 제공하는 여러 DB 엔진 중 하나로 가장 많이 사용함

데이터 베이스 정의

  • DB 생성 : CREATE DATABASE company;
  • 생성 DB 보기 : SHOW DATABASES;
  • 현재 선택 DB 확인 : SELECT database();
  • 사용할 DB 선택 : USE company;
  • DB 삭제 : DROP DATABASE company;

DATABASE vs SCHEMA : MySQL에서는 DATABASE와 SCHEMA가 같은 뜻을 의미한다.

  • CREATE DATABASE company = CREATE SCHEMA company
  • 다른 RDBMS에서는 의미가 다르게 쓰임
    • i.g. PostgreSQL에서는 SCHEMA가 DATABASE의 namespace를 의미

table 정의하기

  • DEPARTMENT : id(pirmary key), name, leader_id
    • leader_id → id(EMPLOYEE)
  • EMPLOYEE : id(pirmary key), name, birth_date, sex, position, salary, dept_id
    • dept_id → id(DEPARTMENT)
  • PROJECT : id(pirmary key), name, leader_id, start_date, end_date
    • leader_id → id(DEPARTMENT)
  • WORKS_ON : empl_id(pirmary key), proj_id(pirmary key)
    • empl_id → id(EMPLOYEE)
    • proj_id → id(PROJECT)

table을 만드는 코드는 다음과 같다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

create table DEPARTMENT(
    id  INT PRIMARY KEY,
    name    VARCHAR(20) NOT NULL    UNIQUE,
    leader_id   INT,
);

create table EMPLOYEE(
    id  INT PRIMARY KEY,
    name    VARCHAR(30) NOT NULL,
    birth_date DATE,
    sex CHAR(1) CHECK (sex in ('M', 'F')),
    position    VARCHAR(10),
    salary  INT DEFAULT 5000000,
    dept_id INT,
    FOREIGN KEY (dept_id) references DEPARTMENT (id)
        on delete SET NULL on update CASCADE,
    CHECK (salary >= 5000000)  
);

이 때, attribute를 정의할 때 data type도 같이 정의를 하는데 이 떄, 사용하는 data type의 종류는 아래와 같다.

Key constraints : PRIMRY KEY

data type 뿐 만 아니라 PRIMARY KEY도 같이 정의한다.

  • primary key : table의 tuple을 식별하기 위해 사용, 하나 이상의 attribute(s)로 구성
  • primary key는 중복된 값을 가질 수 없으며 NULL도 값으로 가질 수 없다.
  • primary key를 정의하는 방법은 2가지가 있다.

    • attribute 하나로 구성될 때

      1
2
3

        create table PLAYER(
      id  INT PRIMARY KEY
  );

    • attribute 하나 이상으로 구성될 때

      1
2
3
4
5
6

        create table PLAYER(
      team_id VARCHAR(12),
      back_number INT,
      ...
      PRIMARY KEY (team_id, back_number)
  );

Key constraints : UNIQUE

  • UNIQUE로 지정된 aatribute(s)는 중복된 값을 가질 수 없다.
  • 단, NULL은 중복을 허용할 수도 있다(RDBMS 마다 다름)
  • 선언하는 방법은 두가지가 있다.
    • attribute 하나로 구성될 때
    1
2
3
4

      create table PLAYER(
      id  INT UNIQUE
      ...
  );
    • attribute 하나 이상으로 구성될 때
    1
2
3
4
5
6

      create table PLAYER(
      team_id VARCHAR(12),
      back_number INT,
      ...
      UNIQUE (team_id, back_number)
  );

Key constraints : NOT NULL

  • attribute가 NOT NULL로 지정되면 해당 attribute는 NULL을 값으로 가질 수 없다.

선언하는 방법은 다음과 같다.

1
2
3
4
5

create table Student(
    ...
    phone_number INT NOT NULL UNIQUE,
    ...
);

attribute DEFAULT

  • attribute의 default 값을 정의할 때 사용
  • 새로운 tuple을 저장할 때 해당 attribute에 대한 값이 없다면 default 값으로 저장

선언 방법은 다음과 같다.

1
2
3
4
5

create table Orders(
    ...
    menu varchar(15) DEFAULT "짜장면"
    ...
);

CHECK constraint

  • attribute의 값을 제한하고 싶을 때 사용

CHECK를 선언하는 방법은 두가지가 있다.

  • attribute 하나로 구성될 때

    1
2
3
4

      create table EMPLOYEE(
      ...
      age INT CHECK (age >= 20)
  );

  • attribute 하나 이상으로 구성될 때

    1
2
3
4
5
6

      create table PROJECT(
      start_date  DATE,
      end_date    DATE,
      ...
      CHECK (start_date < end_date)
  )

Referential integrity constraint : FOREIGN KEY

  • attribute(s)가 다른 table의 primary key나 unique key를 참조할 때 사용

선언하는 방법은 다음과 같다.

1
2
3
4
5
6
7
8

create table Employee(
    ...
    dept_id INT,
    FOREIGN KEY (dept_id)
        references DEPARTMENT(id)
        on delete reference_option
        on update reference_option
)

이떄 다른 table에서 키에 변화가 생길 때 어떻게 처리를 할 것인지를 reference_option이라고 한다.

reference_option설명
CASCADE참조값의 삭제/변경을 그대로 반영
SET NULL참조값이 삭제/변경 시 NULL로 변경
RESTRICT참조값이 삭제/변경되는 것을 금지
NO ACTIONRESTRICT와 유사
SET DEFAULT참조값이 삭제/변경 시 default 값으로 변경

constraint 이름 명시하기

  • 이름을 붙이면 어떤 constraint을 위반했는지 쉽게 파악할 수 있다.

  • constraint를 삭제하고 싶을 때 해당 이름으로 삭제 가능

    1
2
3

     create table TEST(
     age INT CONSTRAINT age_over_20  CHECK (age > 20)
 );
  • CONSTRAIN age_over_20 이름을 붙였을 떄, 만약 위반되면 Check constraint 'age_over_20' is violated. 라고 에러 메시지가 출력
  • CONSTRAIN age_over_20 이름을 생략했을 때, 만약 위반되면 Check constraint 'test_chk_1' is violated. 라고 에러 메시지가 출력
    • 만약 ‘test_chk_1’의 의미를 알고 싶다면 show case test_chk_1 를 입력하면 알 수 있음

나머지 테이블 PROJECT, WORK_ON을 정의하면 다음과 같다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

create table PROJECT(
    id  INT PRIMARY KEY,
    name    VARCHAR(20) NOT NULL UNIQUE,
    leader_id INT,
    start_date  DATE,
    end_date    DATE,
    FOREIGN KEY (leader_id) references EMPLOYEE(id)
        on delete SET NULL on update CASCADE,
    CHECK (start_date <end_date)
);

create table WORKS_ON(
    empl_id INT,
    proj_id INT,
    PRIMARY KEY (empl_id, proj_id),
    FOREIGN KEY (empl_id) references EMPLOYEE(id)
        on delete CASCADE on update CASCADE,
    FOREIGN KEY (proj_id) references PROJECT(id)
        on delete CASCADE on update CASCADE
);

위의 코드처럼 총 4개의 테이블을 선언하였다. 이때 DEPARTMENT 테이블에 하나의 문제점이 있다. 바로 leader_id에 FOREIGN KEY를 걸어주지 않은 것이다. 그 이유는 DEPARTMENT 테이블을 만들 당시에는 EMPLOYEE 테이블이 없었기 때문이다.

그렇다면 모든 테이블을 만든 이후에 테이블의 스키마를 바꿀려면 어떻게 해야할까?

이러한 경우에 사용되는 SQL이 바로 ALTER TABLE이다.

이를 코드로 구현하면 아래와 같다.

1
2
3
4

ALTER TABLE department ADD FOREIGN KEY (leader_id)
    REFERENCES employee(id)
    on update CASCADE
    on delete SET NULL;

ALTER TABLE은 table의 schema를 변경하는데 사용되며 다양한 종류가 있다(필요에 따라 사용하는 RDBMS의 문서를 참조).

이 떄 주의할 점은 이미 서비스 중인 table의 schema를 변경하는 것이라면 변경 작업 때문에 서비스의 백엔드에 영향이 없을 지 검토한 후에 변경하는 것이 중요하다.

💡database 구조를 정의할 때 중요한 점

만들려는 서비스의 스펙과 데이터 일관성, 편의성, 확장성 등등을 종합적으로 고려하여 DB 스키마를 적절하게 정의하는 것이 중요하다.

CS, Database
CS Database SQL
This post is licensed under CC BY 4.0 by the author.