[CS Study] 4. 데이터베이스 (2)

# 본 글은 인하대학교 단풍나무숲 CS 스터디에 작성한 내용을 그대로 가져온 글입니다.

4. 데이터베이스

목차

2. ERD와 정규화 과정
3. 트랜잭션과 무결성

2. ERD와 정규화 과정

1. ERD

Entity-Relation Diagram의 약자로, DB를 설계할 때 가장 기본적인 테이블 (relation) 간 관계를 정의하는 다이어그램입니다.

• 관계형 DB를 표시하기에 매우 유리
• 비정형 DB (NoSQL)에는 적합하지 않다.

2. 정규화 (Normalisation)

정의: 릴레이션 간의 종속 관계를 바로잡아 중복 데이터를 제거하고, 데이터 정합성 (integrity)을 만족하도록 릴레이션을 여러 개로 나누는 과정을 뜻합니다.
예를 들어, 회원 정보 테이블도 사용자 닉네임을 가지고 있고, 게시글 테이블, 댓글 테이블도 각자 사용자 닉네임을 가지고 있는데, 사용자 닉네임이 변경된다면? 정규화가 제대로 되어 있지 않은 상태에서는 모든 테이블에 기록된 닉네임을 하나하나 수정해야 합니다.

정규형의 원칙이란,

• 같은 의미를 표현하는 릴레이션이지민 좀 더 좋은 구조로 만들어야 하고,
• 자료의 중복성이 감소해야 하고,
• 독립적인 관계는 별개의 릴레이션으로 표현이 가능해야 합니다.

정규형에는 제1정규형, 제2정규형, 제3정규형, BCNF, 제4정규형, 제5정규형이 존재합니다.

3. 제1정규형

정의: 모든 도메인이 더 이상 분해될 수 없는 원자값 (atomic)으로만 구성되어 있는 상태를 제1정규형이라고 합니다.

commentId	userName	comment
1	planPt	{아 ㅋㅋ}, {링컨}

위와 같은 테이블은 제1정규화 진행이 되어 있지 않습니다. comment라는 속성에 값이 2개 이상 존재하는 경우가 존재합니다.
아래와 같이 제1정규화를 진행할 수 있습니다.

commentId	userName	comment
1	planPt	아 ㅋㅋ
2	planPt	링컨

4. 제2정규형

3. 트랜잭션과 무결성

정의: 제1정규형을 만족하며, 부분 함수의 종속성을 제거한 형태를 제2정규형이라고 합니다.

postId	creatorId	contents	commentId	commentViews
1	KAKAO00001	안녕	1	23
2	GOOGL00001	등업좀요	2	21
3	KAKAO00002	히어로즈 오브 더 스톰	3	37
4	NAVER00001	네카라쿠배	4	132

위와 같은 릴레이션이 존재할 때, PK는 postId입니다.
각 글은 하나의 작성자, 하나의 내용, 여러 개의 댓글을 가집니다. 한 댓글은 하나의 조회수를 가집니다.
creatorId, contents는 postId에 의존하지만, commentViews는 commentId에 의존합니다. 따라서 위의 릴레이션은 부분 함수의 종속성이 존재하므로, 제2정규형을 만족하지 못합니다.
제2정규화를 통해 아래와 같이 분리할 수 있습니다.

postId	creatorId	contents
1	KAKAO00001	안녕
2	GOOGL00001	등업좀요
3	KAKAO00002	히어로즈 오브 더 스톰
4	NAVER00001	네카라쿠배

commentId	postId	commentViews
1	1	23
2	2	21
3	3	37
4	4	132

5. 제3정규형

정의: 제2정규형을 만족하며, 이행적 함수 종속을 제거하면 제3정규형이 됩니다.
이행적 함수 종속이란, A->B이고 B->C일 때, A->C가 성립하는데, 이때 'C가 A에 이행적 함수 종속이다'라고 합니다.

studentId	deptId	deptPhoneNo
12121212	CSE	0321234567
12124571	CSE	0321234567
12259223	EE	0329182052
12204801	PHY	0328239182

위와 같은 릴레이션이 있을 때, 학생(A)은 학과(B)를 가지고, 학과(B)는 학과 전화번호(C)를 가집니다. 이 때 학생 A가 속한 학과의 전화번호는 C가 되는데요. 따라서 이행적 함수 종속이 존재합니다.

제3정규형을 만족하지 못하는 릴레이션은 아래와 같은 이상이 나타날 수 있습니다.

• 갱신 이상: 학과에 속한 학생들의 전화번호가 중복으로 나타나며, 갱신 시 모든 전화번호를 수정하지 않는다면 갱신 이상이 발생합니다.
• 삭제 이상: 만약 특정 학과에 속한 학생이 단 한 명 뿐인데, 해당 학생이 자퇴하면 해당 학생이 속한 학과에 대한 정보도 삭제됩니다.
• 삽입 이상: 학과는 존재하지만, 학생은 존재하지 않는 경우, 학생 ID (studentId)가 PK이기 때문에 학과에 대한 정보를 삽입할 수 없습니다.

아래와 같이 정규화를 진행할 수 있습니다.

studentId	deptId
12121212	CSE
12124571	CSE
12259223	EE
12204801	PHY

deptId	deptPhoneNo
CSE	0321234567
EE	0329182052
PHY	0328239182

6. BCNF (Boyce-Code Normal Form)

정의: 제3정규형이며, 결정자가 후보키가 아닌 함수 종속 관계를 제거하여, 릴레이션 내의 함수 종속 관계에서 모든 결정자가 후보키인 상태를 말합니다.

한 교수가 단 하나의 수업만을 맡는다고 가정했을 때, 아래와 같은 릴레이션을 봅시다.

studentId	subjectId	professorId
12121212	CSE40001	223901
12121212	CSE40032	229192
12223344	CSE40001	223901
12141617	CSE40101	228902
12345687	CSE50001	220001

위 릴레이션은 제3정규형을 만족합니다. 이행적 함수 종속이 존재하지 않습니다.
이 릴레이션 내에서 결정자(determinant)는 [학번, 과목명], [학번, 담당교수]가 후보키가 됩니다. 하지만 과목명은 담당교수만으로 특정할 수 있습니다. 담당교수만으로는 후보키가 될 수 없습니다.
즉, 결정자가 후보키가 아닌 것입니다.

BCNF를 만족하지 못하는 릴레이션은 아래와 같은 이상이 나타날 수 있습니다.

• 갱신 이상: 교수 A가 맡는 과목이 변경되면, 릴레이션 내의 모든 과목을 수정해야 합니다. 그렇지 않는다면 갱신 이상이 발생합니다.
• 삭제 이상: 특정 과목을 듣는 학생이 단 한 명 뿐이고, 이 학생이 강의를 포기한다면, 해당 강의를 담당하는 교수에 대한 정보까지 함께 삭제됩니다.
• 삽입 이상: 수강생이 하나도 없는 과목에 대한 정보를 추가할 수 없습니다.

아래와 같이 정규화를 진행할 수 있습니다.

studentId	subjectId
12121212	CSE40001
12121212	CSE40032
12223344	CSE40001
12141617	CSE40101
12345687	CSE50001

subjectId	professorId
CSE40001	223901
CSE40032	229192
CSE40101	228902
CSE50001	220001

7. 정규형의 장점과 단점

정규형의 단계까 올라갈수록 중복 데이터가 제거되고, 데이터 정합성이 잘 지켜지게 됩니다. 사용자 정보를 담은 릴레이션 안에서만 이름, 이메일, 주소 등의 정보를 관리하고 다른 릴레이션에서는 사용자 고유 ID를 통해 조회한다면, 여기저기서 데이터를 다 입력하고 수정할 필요가 없지요.
하지만 정규형의 단계가 올라갈수록 더 많은 테이블로 나누기 때문에, 단점도 발생합니다. 릴레이션 간 join 연산을 반복하며 성능 상에서 크게 손해를 볼 수도 있고, 릴레이션 간의 관계를 잘 설계해야 하므로 구조 파악이 어려워질 수 있습니다. 따라서 보통 제4, 제5정규형은 잘 사용되지 않습니다.

3. 트랜잭션과 무결성

트랜잭션의 정의

'논리적인 작업을 수행하기 위한 연산의 단위' 를 트랜잭션이라고 부릅니다.

자사가 플랜이에게 돈을 보냅니다. 어떤 과정이 일어날까요?

• 자사의 통장 잔고를 조회하여, 보내려는 금액 이상이 존재하는지 확인한다.
• 자사의 통장 잔고에서 보내려는 금액만큼의 값을 뺀다.
• 플랜이의 통장 잔고에 받는 금액만큼을 더한다.

위의 세 과정이 일어난다고 가정합니다. 만약 도중에 연결 불안정 등의 문제가 발생하면 어떤 일이 일어날 수 있을까요?

• 자사의 통장 잔고에서 돈이 빠져나가고, 플랜이의 통장에 돈이 입금되지 않는다.
• 자사의 통장 잔고를 조회하고 보내려는 금액만큼을 빼려는 사이에 자사가 탄산 음료를 사 먹는다. 남은 잔고는 보내려는 금액보다 적은데, 앞에서 확인할 때는 보내려는 금액 이상이었으므로 통장 잔고에서 금액을 그대로 뺀다.

논리적인 작업 하나를 수행하기 위한 1개 이상의 연산을 묶어서 트랜잭션이라고 부릅니다. 위와 같은 경우가 발생하는 것을 방지하기 위해 존재하며, 트랜잭션의 특징 4개를 ACID (Atomicity, Consistency, Isolation, Durability)라고 부릅니다.

원자성 (Atomicity)

트랜잭션 내의 내용들이 하나의 단위 (atomic)로 묶여서, 전체가 수행되거나 전체가 수행되지 않아야 한다는 속성을 가리킵니다. 위의 예시에서, 자사가 플랜이에게 돈을 보내줄 때 일어나는 세 가지 연산이 모두 실행되거나, 모두 실행되지 않아야 하는 성질입니다.
여기서 commit과 rollback이라는 개념이 등장합니다. 모든 작업이 성공적으로 수행되면 해당 작업을 commit하여 실제로 반영하고, 작업 중 문제가 발생하면 앞으로 일어날 작업을 rollback 합니다. DBMS는 commit과 rollback을 통해 트랜잭션의 원자성을 보장해 줍니다.

@Service
public Class MemberService {

    ...

    @Transactional
    public HashMap<String, String> updateClassMember(Map<String, Object> param) throws Exception {
        ...
    }
}

Spring 프레임워크에서는 Service 계층에서 @Transactional이라는 어노테이션을 통해 메소드 내의 로직의 원자성을 보장합니다.

지속성 (Consistency)

일관성이라고도 합니다.

'허용된 방식'으로만 데이터를 변경할 수 있는 성질을 의미합니다. 즉 DB에 기록되는 모든 데이터는 여러가지 조건과 규칙을 만족해야 합니다.
이를 'Correct State'라고 하는데, 트랜잭션 이전의 데이터가 모두 Correct State였다면, 트랜잭션 이후의 데이터도 모두 Correct State여야 합니다.

격리성 (Isolation)

각 트랜잭션의 수행은 서로 독립적이야 함을 의미합니다.
아까 언급한 예시 중에서, 자사의 금액을 조회하고 자사로부터 금액을 빼 오는 사이에 다른 트랜잭션이 수행되서 자사의 잔고 값이 변했다면, 격리성을 해치는 일입니다.
작업이 수행되는 다른 작업이 끼어들지 못하도록 하는 개념이며, 격리 단계가 총 4개 존재합니다.

• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable

각 단계에 대한 설명은 후술합니다.

지속성 (Durability)

성공적으로 수행된 트랜잭션은 DBMS가 관리하는 한 영원히 반영되어야 하는 성질을 뜻합니다.
만약 DB에 장애가 발생해도, DBMS는 자체적으로 복구하는 능력을 가지고 있으며, 이를 통해 지속성을 만족합니다.

• 체크섬: 오류 정정을 통해 송신된 자료의 무결성을 보호
• 저널
  링: 변경 사항 commit 이전에 로깅하는 것
  • 호돌맨 예시
• 롤백

트랜잭션의 격리 4단계 정의

앞서 언급한 바와 같이, 트랜잭션은 4단계의 격리 수준을 가집니다. 이는 ANSI 표준으로 정의되어 있습니다. 언급한 순서 상, 아래로 내려갈수록 격리성이 상승하지만, 그만큼 동시성이 내려갑니다. 반대로 위로 올라갈수록 동시성은 상승하지만, 격리성은 내려갑니다.

격리 수준	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	O	O	O
Read Committed		O	O
Repeatable Read			O
Serializable

Read Uncommitted

가장 격리성이 낮고 동시성이 강한 단계입니다.
앞에서 트랜잭션의 원자성을 언급할 때 commit의 개념에 대해 다루었습니다. Read uncommitted 단계에서는 commit이 아직 되지 않은 값을 읽습니다 (dirty read).
데이터 무결성을 해칠 가능성이 높으므로 가능하면 권장하지 않는 방법이며, 일부 특수한 상황에서만 사용합니다.

Read Committed

가장 대중적으로 사용하는 격리 단계입니다. MySQL, PostgreSQL 등에서 기본으로 사용합니다. Commit이 완료된 데이터를 읽습니다.
어떤 트랜잭션이 접근한 행을 다른 트랜잭션이 수정할 수 있습니다. 이때문에 트랜잭션 A가 같은 값을 반복해서 읽었을 때, 다른 결과를 얻을 수 있습니다 (non-repeatable read).

Repeatable Read

하나의 트랜잭션이 수정한 행을 다른 트랜잭션이 수정할 수 없도록 막아 줍니다.
하지만 새로운 행이 추가되는 것은 막지 않기 때문에, 트랜잭션이 미처 모르던 행이 추후에 발견될 수 있습니다.

SELECT COUNT(*) FROM USER A
WHERE A.age >= 12

한 트랜잭션에서 위와 같은 쿼리를 두 번 호출했는데, 그 사이에

INSERT INTO USER (
    userId,
    userFirstName,
    userLastName,
    userAge,
    userEmail
) VALUES (
    AUTO_INCREMENT,
    'Kuyho',
    'Chung',
    15,
    'kchung1995@gmail.com'
)

이라는 쿼리를 호출하면, 앞의 쿼리의 값은 두 번째 호출 시 첫 번째 호출보다 1 더 큰 값을 얻을 것입니다.
이것을 Phantom read라고 합니다 (한 트랜잭션 내에서 동일한 쿼리를 보냈을 때 해당 조회 결과가 다름).

Serializable

가장 격리성이 높고 동시성이 낮은 단계입니다.
트랜잭션이 수행되기 전후에 lock을 걸어 버리기 때문에, 성능 손해도 크고 deadlock도 자주 발생합니다.