[데이터베이스] 데이터베이스 기본/ERD와 정규화 과정

2023년 4월 29일 184p~205p

 

4.1 데이터베이스의 기본

DB : 일정한 규칙, 규약을 통해 저장된 데이터의 모음
DBMS: 해당 DB를 제어, 관리하는 통합 시스템

데이터들은 각 DBMS마다 정의된 쿼리 언어를 사용해 삽입, 삭제, 수정, 조회를 수행할 수 있다.

DB 위에 DBMS가 존재하며, 그 위에 응용 프로그램이 있다. MySQL(DBMS)이라는 DBMS가 있고 그 위에 응용 프로그램에 속하는 Node.js, php 등에서 해당 데이터베이스 안에 있는 데이터를 끄집어내 해당 데이터 관련 로직을 구축할 수 있다.

- 데이터베이스를 사용하는 이유

데이터베이스가 존재하기 이전에는 파일 시스템을 이용하여 데이터를 관리하였다. (현재도 부분적으로 사용되고 있다.) 데이터를 각각의 파일 단위로 저장하며 이러한 일들을 처리하기 위한 독립적인 애플리케이션과 상호 연동이 되어야 한다. 이 때의 문제점은 데이터 종속성, 중복성, 데이터 무결성이다.

- 데이터베이스의 특징

1. 데이터의 독립성

• 물리적 독립성 : 데이터베이스 사이즈를 늘리거나 성능 향상을 위해 데이터 파일을 늘리거나 새롭게 추가하더라도 관련된 응용 프로그램을 수정할 필요가 없다.
• 논리적 독립성 : 데이터베이스는 논리적인 구조로 다양한 응용 프로그램의 논리적 요구를 만족시켜줄 수 있다.

2. 데이터의 무결성

여러 경로를 통해 잘못된 데이터가 발생하는 경우의 수를 방지하는 기능으로 데이터의 유효성 검사를 통해 데이터의 무결성을 구현하게 된다.

3. 데이터의 보안성

인가된 사용자들만 데이터베이스나 데이터베이스 내의 자원에 접근할 수 있도록 계정 관리 또는 접근 권한을 설정함으로써 모든 데이터에 보안을 구현할 수 있다.

4. 데이터의 일관성

연관된 정보를 논리적인 구조로 관리함으로써 어떤 하나의 데이터만 변경했을 경우 발생할 수 있는 데이터의 불일치성을 배제할 수 있다. 또한 작업 중 일부 데이터만 변경되어 나머지 데이터와 일치하지 않는 경우의 수를 배제할 수 있다.

5. 데이터 중복 최소화

데이터를 통합해서 관리함으로써 파일 시스템의 단점 중 하나인 자료의 중복과 데이터의 중복성 문제를 해결할 수 있다.

- 데이터베이스의 성능?

데이터베이스의 성능 이슈는 디스크 I/O 를 어떻게 줄이느냐에서 시작된다. 디스크 I/O 란 디스크 드라이브의 플래터(원판)을 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽는 것을 의미한다. 이 때 데이터를 읽는데 걸리는 시간은 디스크 헤더를 움직여서 읽고 쓸 위치로 옮기는 단계에서 결정된다. 즉, 디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 따라 결정된다고 볼 수 있다.

4.1.1 엔터티

엔터티는 사람, 장소, 물건 등 여러 개의 속성을 지닌 명사다.
ex) 회원 - 이름, 아이디, 주소, 전화번호

* 약한 엔터티와 강한 엔터티
A 혼자서는 존재하지 못하고 B의 존재 여부에 종속적이라면 A는 약한 엔터티 B는 강한 엔터티이다.

4.1.2 릴레이션

릴레이션은 DB에서 정보를 구분하여 저장하는 기본 단위이다. 엔터티에 관한 데이터를 데이터베이스는 릴레이션 하나에 담아 관리한다.
RDB(관계형 데이터베이스)에서는 테이블, NoSQL에서는 컬렉션이라고 한다.

- 테이블과 컬렉션

DB는 크게 관계형 데이터베이스인 MySQL과 NoSQL 데이터베이스로 나눌 수 있다.

관계형 : 레코드- 테이블- 데이터베이스의 구조
NoSQL(MongoDB) : 도큐먼트 - 컬렉션 - 데이터베이스의 구조

4.1.3 속성

릴레이션에서 관리하는 구체적이며 고유한 이름을 갖는 정보이다.
예를 들어, 차라는 엔터티의 속성에는 차 넘버, 색깔, 차종 등이 있으며 이 중 서비스의 요구 사항을 기반으로 관리해야 할 필요가 있는 속성들만 엔터티의 속성이 된다.

4.1.4 도메인

릴레이션에 포함된 각 속성들이 가질 수 있는 값의 집합이다.
성별이라는 속성이 있다면, 이 속성이 가질 수 있는 값은 [남, 여]라는 집합이 된다.

4.1.5 필드와 레코드

https://velog.io/@jkj94627/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4

회원이란 엔터티는 member라는 테이블로 속성인 이름, 아이디 등을 갖고 있으며 name, ID, address 등의 필드를 가진다.
테이블에 쌓이는 행 단위의 데이터를 레코드(튜플)이라고 한다.

책이라는 엔터티를 정의하고 테이블을 만들어보자.

속성 : 제목, 가격, ISBN, 저자, 출판년도, 장르, 생성 일시, 업데이트 일시

id	title	author_id	publishing_year	genre	created_at	updated_at
1	책 제목	123	2020/09/01	액션	2020/05/01	2020/09/01

보통 속성을 컬럼으로 사용하며 영어 이름이 매핑해서 사용하고, 속성에 타입을 지정한다.

CREATE TABLE book(
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(255),
author_id INT,
publishing_year VARCHAR(255),
genre VARCHAR(255),
created_at DATETIME,
updated_at DATETIME,
PRIMARY KEY(id)
);

 

DB에 넣어 테이블로 만들기 위해서는 속성에 맞는 타입을 정의해야 한다.

* 필드 타입

데이터 베이스의 각 필드가 가지고 있는 타입에는 다양한 타입들이 있다.

숫자타입 : TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 등이 있다.

날짜타입 : DATE, DATETIME, TIMESTAMP 등이 있다.

- DATE: 날짜만 있는 값으로 3바이트의 용량을 지원한다.
- DATETIME: 날짜와 시간 부분을 모두 포함하는 값으로 8바이트의 용량을 지원한다.
(1000-01-01 00:00:00 ~ 9999:-12-31 23:59:59 까지 지원)
- TIMESTAMP: 날짜와 시간 부분을 모두 포함하는 값으로 4바이트의 용량을 지원한다.
(1970-01-01 00:00:01 ~ 2038:-01-19 03:14:07 까지 지원)

문자타입 : CHAR, VARCHAR,TEXT,BLOB ENUM, SET등이 있다.

CHAR: 테이블을 생성할 때 선언한 길이로 고정이 됩니다. 0~255 사이의 값으로 지정할 수 있다.
VARCHAR: 가변 길이의 문자열로 길이는 0~65535 사이의 값으로 지정할 수 있다. 입력된 데이터에 따라서 용량을 가변시켜 저장할 수 있다.

지정된 형태에 따라서 저장된 CHAR의 경우 검색에 유리하고, 검색을 별로 하지 않고 유동적인 길이를 가진 데이터는 VARCHAR로 저장하는 것이 좋다.

TEXT: 큰 문자열 저장에 주로 사용하며 주로 게시판의 본문을 저장할 대 이용한다.
BLOB: 이미지, 동영상 등 큰 데이터 저장에 사용하지만, 보통 이미지를 저장하는 방법으로는 S3를 이용해서 저장하며 VARCHAR를 이용해서 경로를 지정해준다.
ENUM: 하나만 선택하는 단일 선택만 가능하고 ENUM 리스트에 없는 잘못된 값을 삽입하면 빈 문자열이 대신 삽입이 된다. 리스트에는 최대 65536개의 요소를 넣을 수 있다.
SET: ENUM과 비슷하지만 여러 개의 데이터를 선택할 수 있고 비트단위의 연산을 할 수 있다. 이 리스트에는 최대 64개의 요소를 넣을 수 있다.

4.1.6 관계

데이터베이스에 존재하는 여러 테이블끼리는 서로 관계가 있다.

• 1:1 관계 - 한 레코드와 다른 테이블의 한 레코드가 연결된 경우
  1:1 관계는 한 테이블을 두 개의 테이블로 나누어 테이블의 구조를 더 이해하기 쉽게 만들어 준다.
  ex) 결혼
• 1:N 관계 - 한 테이블의 한 레코드와 다른 테이블의 여러 레코드가 연결된 경우
  ex) 쇼핑몰에서 한 유저가 여러 개의 상품을 구매한다면 1:N 으로 관계를 맺을 수 있다.
  하나도 구매하지 않는 상황도 있을 수 있으므로 0도 포함되는 화살표를 통해 표현해야 한다.
• N:M 관계 - 한 테이블의 여러 레코드와 다른 테이블의 여러 레코드가 연결된 경우
  N:N(다대다) 관계를 위해 스키마를 디자인할 때에는, Join 테이블을 만들어 관리한다. 1:N(일대다) 관계와 비슷하지만, 양방향에서 다수의 레코드를 가질 수 있다.
  서로의 PK를 자신의 외래키 컬럼으로 갖고 있어야 한다. 일반적으로, N:M관계는 두 테이블의 대표키를 컬럼으로 갖는 또다른 테이블을 생성해서 관리한다(조인 테이블).
  ex) 학생과 스터디가 참여 관계가 존재할 때 학생은 여러 개의 스터디에 참여할 수 있고 스터디도 여러 명의 학생이 참여할 수 있다.

번외로, 스스로를 참조할 수도 있다 - self referencing

4.1.7 키

테이블 간 관계를 명확히하고, 검색, 정렬 시 레코드를 명확히 구분할 수 있는 속성이다.

유일성 : Key로 하나의 Tuple을 유일하게 식별할 수 있음
최소성 : 꼭 필요한 속성으로만 구성

- 기본키 Primary Key : 유일성과 최소성을 만족하는 키

• 자연키 : 중복되는 레코드들이 나오는 속성을 제외하고 자연스레 남는 키
• 인조키 : 인위적으로 기본키를 생성하는 것 ex) auto increment, uuid

- 외래키 Foreign Key : 다른 릴레이션의 기본키를 그대로 참조하는 속성의 집합
- 후보키 Candidate Key : 기본키가 될 수 있는, 유일성과 최소성을 만족하는 키
- 대체키 Alternate Key : 후보키가 두 개 이상일 경우 어느 하나를 기본키로 지정하고 남은 후보키
- 슈퍼키 Super Key : 유일성은 만족하지만, 최소성은 만족하지 못하는 키

---

4.2 ERD와 정규화 과정

ERD(Entity Relationship Diagram)는 데이터베이스를 구축할 때 가장 기초적인 뼈대 역할을 하며, 릴레이션 간의 관계들을 정의한 것이다.

- ERD의 중요성

시스템의 요구 사항을 기반으로 작성되며 이 ERD를 기반으로 데이터베이스를 구축한다. 이후에도 디버깅 또는 비즈니스 프로세스 재설계가 필요한 경우에도 설계도 역할을 담당하기도 한다.
하지만 ERD는 관계형 구조로 표현할 수 있는데 데이터를 구성하는 데 유용할 수 있지만 비정형 데이터를 충분히 표현할 수 없다는 단점이 있다.

* 비정형 데이터 : 비구조화 데이터를 말하며, 미리 정의된 데이터 모델이 없거나 미리 정의된 방식으로 정리되지 않은 정보로 말한다.

- 정규화

가장 큰 목표는 테이블 간 중복된 데이터를 허용하지 않는 것이다. 중복된 데이터를 만들지 않으면, 무결성을 유지할 수 있고, DB 저장 용량 또한 효율적으로 관리할 수 있다.

목적

• 데이터의 중복을 없애면서 불필요한 데이터를 최소화시킨다.
• 무결성을 지키고, 이상 현상을 방지한다.
• 테이블 구성을 논리적이고 직관적으로 할 수 있다.
• 데이터베이스 구조를 확장에 용이해진다.

정규화에는 여러가지 단계가 있지만, 대체적으로 1~3단계 정규화까지의 과정을 거친다.

# 이상현상 (Anomaly)

이상현상이란, 테이블 내의 데이터들이 불필요하게 중복되어 테이블을 조작할 때 발생되는 데이터 불일치 현상으로, 테이블을 잘못 설계하여 삽입, 삭제, 갱신할 때 오류가 발생하게 되는 것이다.

이상현상에는 크게 3가지가 있으며, 정규화를 통해서 이상현상들을 해결할 수 있다.

멘토	이름	스터디
라라	기영	cs
라라	기영	디자인 패턴
라라	이서	cs

기본 키 속성은(멘토, 이름)이다.

1. 삽입 이상
릴레이션에 새 데이터를 삽입하기 위해 원치 않는 불필요한 데이터도 함께 삽입해야 하는 문제이다.

ex)새로운 코스 일원이 생겨 데이터를 삽입한다.
아직 멘토가 배정되지 않았지만 기본 키 속성 중 멘토가 있기 때문에 임시 멘토를 배정해야 하므로 삽입 이상이 생긴다.

2. 갱신 이상
중복된 튜플중 일부만 수정하여 데이터가 불일치하게 되는 문제이다.

ex)기영의 멘토가 라라에서 → 미미로 변경되었다.
기영에 대한 튜플이 2개가 존재하여 2개의 멘토를 변경해야 하지만 1개만 변경되면 서로 다른 멘토를 가지는 모순이 생긴다.

3. 삭제 이상
튜플을 삭제하면서 필요한 데이터까지 같이 삭제되는 문제이다.

ex)이서가 cs 스터디를 그만둬 튜플을 삭제하면 이서의 멘토와 이름 데이터까지 삭제되어 원치 않는 손실이 발생한다.

# 함수 종속성 (Functional Dependency)

어떠한 튜플에서 속성 X의 값이 하나로 정해지고 그에 따라 Y 값이 항상 하나면 Y가 X에 종속되어 있다고 한다.

X를 결정자 Y를 종속자라고 하며, X → Y 로 표현한다.

주민번호	이름	성별
123456-123456	홍길동	남

주민번호 값이 정해지면 이름과 성별은 하나의 값으로 대응된다.
이름과 성별 속성이 주민번호 속성에 종속되어 있으며, 주민번호가 결정자, 이름과 성별이 종속자가 된다.

결정자는 속성 집합이 될 수도 있다.

이름	스터디	참여여부
홍길동	cs	Y

참여 여부가 (이름, 스터디)에 종속되어있다.

1. 완전 함수 종속
종속자 Y가 결정자 X의 모든 속성에 종속된 것을 말한다. 결정자가 단일 속성이면 종속자 Y는 당연하게 완전 함수 종속이다.
결정자가 속성 집합일 경우에는 일부분에만 종속될 수 있다.

이름	수강생 아이디	스터디아이디	참여여부
홍길동	gd12	123	Y

기본 키 속성 집합 (수강생 아이디, 스터디 아이디)에서 스터디 참여 여부는 속성 집합 전체에 종속되어 있다.

2. 부분 함수 종속
기본 키 속성 집합(수강생 아이디, 스터디 아이디)에서 수강생 이름은 기본 키 속성 집합 중 수강생 아이디에만 종속되어 있다.

# 정규화 과정

1. 제 1정규형

테이블 컬럼이 원자값(하나의 값)을 갖도록 테이블을 분리시키는 것을 말한다.

• 어떤 릴레이션에 속한 모든 도메인이 원자값만으로 되어 있어야한다.
• 모든 속성에 반복되는 그룹이 나타나지 않는다.
• 기본키를 사용하여 관련 데이터의 각 집합을 고유하게 식별할 수 있어야 한다.

ID	이름	과목	성취도
1	홍길동	일반 수학1	{90%, 10%}
2	김철수	공업 수학1	{10%, 5%}

성취도에서 두 개 이상의 값을 가지는 반복집합이 있으므로 제 1정규형 조건에 위배된다.
아래의 표처럼 반복 집합을 제거하면 제 1정규형을 만족한다.

ID	이름	과목	성취도
1	홍길동	일반 수학1	90%
1	홍길동	일반 수학1	10%
2	김철수	공업 수학1	10%
2	김철수	공업 수학1	5%

제 1정규형에는 이상 현상이 존재한다. 이상현상이 발생하는 이유는, 기본키가 아닌 속성들이 기본키에 완전 함수 종속되지 못하고 부분 함수 종속되어 있기 때문이다. 즉, 기본키의 일부 속성에만 의존하고 있다는 의미이다.

2. 제 2정규형

테이블의 모든 컬럼이 완전 함수적 종속을 만족해야 한다.

조금 쉽게 말하면, 테이블에서 기본키가 복합키(키1, 키2)로 묶여있을 때, 두 키 중 하나의 키만으로 다른 컬럼을 결정지을 수 있으면 안된다.

기본키의 부분집합 키가 결정자가 되어선 안된다는 것

ID	이름	과목	성취도
1	홍길동	일반 수학1	90%
1	홍길동	일반 수학1	10%
2	김철수	공업 수학1	10%
2	김철수	공업 수학1	5%

기본키가 {이름, 과목}인데, 'ID' 속성이 기본키의 부분적인 속성 즉 '이름'에만 종속되므로, 다음과 같이 분해한다.

ID	이름
1	홍길동
2	김철수

이름	과목	성취도
홍길동	일반 수학1	90%
홍길동	일반 수학1	10%
김철수	공업 수학1	10%
김철수	공업 수학1	5%

기본키인 {이름, 과목}과 완전 종속된 릴레이션과 {이름, 과목}에 따른 성취도 릴레이션으로 분리하였다.

제 2정규형에도 이상현상이 존재한다. 여전히 이상현상이 발생하는 이유는 '이행적 함수 종속성' 때문이다.
이행적 함수 종속성은 속성이 A->B이고, B->C이면서 A->C의 관계가 있는 것을 말한다.

3. 제 3정규형

제 2정규형이면서, 이행적 함수 종속성을 제거한 정규형이다.

• 릴레이션이 2NF에 만족한다.
• 기본키가 아닌 속성들은 기본키에 의존한다.

유저 ID	등급	할인율
홍철	플레	30%
범수	다이아	50%
가영	마스터	70%

유저 아이디가 등급을 결정하고, 등급이 할인율을 결정하면 유저 아이디가 할인율을 결정할 수 있다.

유저 ID -> 등급, 등급 -> 할인율, 그렇다면 유저 ID -> 할인율

다음과 같이 분리한다.

유저 ID	등급
홍철	플레
범수	다이아
가영	마스터

등급	할인율
플레	30%
다이아	50%
마스터	70%

4. 보이스/코드 정규형

제 3정규형이고, 결정자가 후보키가 아닌 함수 종속 관계를 제거하여 릴레이션의 함수 종속 관계에서 모든 결정자가 후보키인 상태를 말한다. 즉, 제 3정규형을 강화시킨 개념이다.

* Determinant 결정자 :  주어진 릴레이션에서 다른 속성를 고유하게 결정하는 하나 이상의 속성

요구 사항은 다음과 같다.

• 각 수강명에 대해 한 학생은 오직 한 강사의 강의만 수강한다.
• 각 강사는 한 수강명만 담당한다.
• 한 수강명은 여러 강사가 담당할 수 있다.

(학번, 수강명) 또는 (학번, 강사)가 후보키가 된다. 만약 범석이라는 강사가 '롤'이라는 수강명을 담당한다고 했을 때, 이를 삽입 시 학번이 NULL이 되는 문제점이 발생한다.

또한 이 릴레이션은 다음과 같은 함수 종속 다이어그램을 가진다.

같은 강의를 다른 강사가 가르칠 수도 있어서 수강명-> 강사 종속은 성립하지 않는다.
하지만 강사가 어떤 강의를 가르치는지는 알 수 있으므로 강사-> 수강명 종속이 성립한다.

이처럼 후보키 집합이 아닌 속성이 결정자가 되어버린 상황을 BCNF를 만족하지 않는다고 한다.
즉, 강사 속성이 결정자이지만 후보키가 아니므로 이 강사 속성을 분리해야 한다.

학번	강사
20201	큰돌
20202	재엽
20203	큰돌
20204	가영

수강명	강사
코딩테스트	큰돌
MEVN	재엽
MEVN	가영

- 정규화의 장점

• 데이터베이스 변경 시 이상 현상(Anomaly) 제거 
  위에서 언급했던 각종 이상 현상들이 발생하는 문제점을 해결할 수 있다.
• 데이터베이스 구조 확장 시 재 디자인 최소화
  정규화된 데이터베이스 구조에서는 새로운 데이터 형의 추가로 인한 확장 시, 그 구조를 변경하지 않아도 되거나 일부만 변경해도 된다. 이는 데이터베이스와 연동된 응용 프로그램에 최소한의 영향만을 미치게 되며 응용프로그램의 생명을 연장시킨다.
• 사용자에게 데이터 모델을 더욱 의미있게 제공
  정규화된 테이블들과 정규화된 테이블들간의 관계들은 현실 세계에서의 개념들과 그들간의 관계들을 반영한다.

- 정규화의 단점

릴레이션의 분해로 인해 릴레이션 간의 연산(JOIN 연산)이 많아진다. 이로 인해 질의 응답 시간이 느려질 수 있다. 

- 어떠한 상황에서 정규화를 진행해야 하는가? 단점에 대한 대응책은?

조회를 하는 SQL 문장에서 조인이 많이 발생하여 성능저하가 나타나는 경우, 반정규화를 적용하는 전략이 필요하다.

< 반정규화(De-normalization, 비정규화) >
반정규화는 정규화된 엔티티, 속성, 관계를 시스템의 성능 향상 및 개발과 운영의 단순화를 위해 중복 통합, 분리등을 수행하는 데이터 모델링 기법 중 하나이다. 일반적으로 조회에 대한 처리 성능이 중요하다고 판단될 때 부분적으로 반정규화를 고려하게 된다.

무엇이 반정규화의 대상이 되는가?
1) 자주 사용되는 테이블에 액세스하는 프로세스의 수가 가장 많고, 항상 일정한 범위만을 조회하는 경우
2) 테이블에 대량 데이터가 있고 대량의 범위를 자주 처리하는 경우, 성능 상 이슈가 있을 경우
3) 테이블에 지나치게 조인을 많이 사용하게 되어 데이터를 조회하는 것이 기술적으로 어려울 경우

반정규화 과정에서 주의할 점은?
반정규화를 과도하게 적용하다 보면 데이터의 무결성이 깨질 수 있다. 또한 입력, 수정, 삭제의 질의문에 대한 응답 시간이 늦어질 수 있다.

 

정규화 쉽게 이해하기)

https://rebro.kr/160

[DB] 8. 정규형 (1NF, 2NF, 3NF, BCNF)

https://www.youtube.com/watch?v=Y1FbowQRcmI