[SQLD] 정규화, 반정규화

 

내가 보려고 헷갈리는 거 정리한 것

---

 

1. 정규화

데이터의 정합성(데이터의 정확성과 일관성을 유지하고 보장)하기 위해 엔터티를 작은 단위로 분리하는 과정이다. 정규화를 할 때 데이터 조회 성능은 보장 못하지만 입력, 수정, 삭제 성능은 일반적으로 향상된다고 볼 수 있다. 

 

내가 예전에 정리한거

[Database] 정규화 쉽게 이해하기, 제1정규형, 제2정규형, 제3정규형, BCNF

 

1) 제1정규형 위반

 - 한 칸에 , 로 항목 여러개 들어 간거

 - 한 줄에 가족1, 가족2, 가족3 처럼 유사한 속성 반복 그룹 형태를 띄는거
 - 도메인이 원자값 아님

 

https://sewonzzang.tistory.com/51

 

 

2) 제2정규형 위반 

- 일단 PK가 복합키인데 일반 속성이 그 중 하나에만 종속 

- `부분함수종속`

- 엔터티의 모든 일반 속성은 반드시 모든 주식별자에 종속되어야 하는데 주식별자가 복합식별자인 경우 일반 속성이 주식별자의 일부에만 종속될 수 있다. 

 

https://code-lab1.tistory.com/48

 

3) 제3정규형 위반 

- 주식별자가 아닌 모든 속성 간에는 서로 종속될 수 없다

- `이행적 함수 종속`

- 일반 속성들간에 종속

 

---

 

2. 반정규화

데이터의 조회 성능을 향상시키기 위해 데이터간의 중복을 허용하거나 데이터를 그룹핑하는 과정이다. 조희 성능은 향상 될 수 있으나 입력, 수정, 삭제 성능은 저하 될 수 있다. 

정규화 -> 입력, 수정, 삭제 성능 UP
반정규화 -> 조회 성능 UP

 

❌ 모델 설계과정에서 정규화와 반정규화는 동시에 일어난다. X

=> 반정규화는 모든 정규화를 마친 후 성능상 이슈가 있을 때만 고려하는 것이 원칙이다.

 

⭕ 반정규화를 하면 데이터 무결성이 저해된다고 볼 수 있다. O

 

---

 

반정규화 종류

(1) 테이블 반정규화

 - 테이블 병합 (1:1관계, 1:M관계, 슈퍼서브타입 병합)

 - 테이블 분할

  :: 수직 분할 = 속성분할

  :: 수평 분할 = 인스턴스 분할, 파티셔닝

 - 테이블 추가 (중복/통계/이력/부분 테이블 추가)

 

(2) 컬럼 반정규화 

 - 중복 컬럼 추가

 - 파생 컬럼 추가

 - 이력테이블 컬럼 추가

 

(3) 관계 반정규화(중복관계 추가)

 

**종속관계추가는 반정규화의 종류가 아님

**슈퍼/서브타입 테이블 분리는 반정규화의 종류가 아님 

 

---

다음 데이터 모델이 어떤 정규화 대상인지 고르시오.

 

[주문]

주문번호 상품코드

주문일시

주문수량

회원번호

회원명

 

PK가 복합이라고 무조건 2차정규화 대상 아님.

회원명(일반속성)이 회원번호(일반속성)에 종속되었으므로 3차 정규화 대상이다.

 

---

 

 

테이블 병합에 대한 설명

 

⭕ 1:M 관계에서의 테이블 병합은 1쪽에 해당하는 테이블의 속성 개수가 적어야 유리하다.

⭕ 테이블의 관계가 강결합이어서 같이 조회되는 경우가 대부분일 경우 비정규화를 고려해야 한다.

⭕ 지나친 JOIN으로 인해 성능의 문제가 있을 경우 수행한다.

---

테이블 수직 분할에 대한 설명

 

⭕ 테이블의 일부 속성을 별도의 테이블로 분리하는 것이다.

⭕ 테이블을 수직 분할하면 한 개의 블록에 더 많은 인스턴스를 저장 할 수 있게 된다.

⭕ 속성의 사용 빈도나 속성값이 NULL 여부를 기준으로 분할 할 수 있다.

❌ 테이블을 수직분할 하는 경우 1:1 관계 내지 1:M관계가 형성 될 수 있다. 

 => 1:1관계 테이블이 2개 생긴다.

---

테이블 수평 분할에 대한 설명

 

⭕ 테이블의 특정 인스턴스를 별도의 테이블로 분할하는 것이다.

⭕ DB의 파티셔닝 기법을 주로 이용한다.

⭕ 테이블 수평분할을 할 경우 관계가 없는 다수의 테이블이 생성된다.

❌ 테이블 수평 분할의 기준은 기간이다. 

 => 주로 기간이지만 다른 속성이 될 수도 있다. 

---

테이블을 추가하는 경우

- 특정 범위의 많은 데이터를 자주 처리해야 하는 경우

- 정규화로 인한 성능 감소가 발생한 경우

- 미리 계산된 값을 저장해둠으로써 애플리케이션 성능을 향상 시키려는 경우

---

데이터모델링 순서

1. 데이터 모델에 맞게 `정규화`를 수행한다.

2. 데이터베이스의 `용량 및 트랜잭션 유형`을 파악한다.

3. 데이터베이스의 성능을 고려하여 `반정규화`를 수행한다.

4. PK/FK 등을 조정하여 `인덱스`를 반영함으로써 성능을 향상시킨다.

5. 데이터 모델의 성능을 `검증`한다.

---

NULL 연산에 대한 설명

⭕ NULL이 포함된 집계 함수는 NULL을 제외하고서 계산된다.

⭕ NULL이 포함된 사칙연산의 결과는 항상 NULL이다.

❌ WHERE COL IS NULL 조건은 WHERE COL = NULL 조건과 같다.

⭕WHERE COL IS NOT NULL 조건은 COL이 NULL이 아닌 행만 출력하는 조건이다.

WHERE COL IS NOT NULL 조건은 COL 값이 NULL인 행을 반환하지만 COL = NULL 의 결과는 항상 False 이다. 그래서 아무런 행도 반환하지 않는다. 

 

NULL은 값이 아니기 때문에 직접 비교할 수 없다. NULL은 값이 없거나 정의되지 않은 상태를 나타내므로 NULL을 다른 값과 비교할 때는 `일반적인 비교 연산자(=)`를 사용할 수 없다. 예를 들어, COL = NULL은 `컬럼의 값이 NULL과 같다`는 조건인데, NULL은 값이 없는 상태이기 때문에 그 자체로 값이 없다는 것에 대한 비교를 할 수 없기 때문에 이 조건은 항상 false가 된다.

 

-- 잘못된 비교 (항상 false)
SELECT * FROM table WHERE COL = NULL;  -- 항상 false

-- 올바른 비교
SELECT * FROM table WHERE COL IS NULL;  -- COL이 NULL인 경우
SELECT * FROM table WHERE COL IS NOT NULL;  -- COL이 NULL이 아닌 경우

 

---

다음 중 결과값이 가장 작은 SQL은 무엇인가?

 

[SAMPLE 테이블]

COL1	COL2
10	NULL
NULL	15
30	25

 

1. SELECT COUNT(COL1)*10 FROM SAMPLE;

2. SELECT SUM(COL1+COL2)/4 FROM SAMPLE;

3. SELECT SUM(COL2)/2 FROM SAMPLE;

4. SELECT AVG(COL1) FROM SAMPLE;

 

나머지는 집계함수로 데이터 중 NULL인 행이 제외되어 계산된다. (20)
2. 의 경우는 사칙연산이 포함되어 값이 다르다.

10+NULL=NULL, NULL+15=NULL이므로 결국 (35+25)/4 = 13.75가 된다.

 

---

❌상품 엔터티의 최근 상품가격 속성은 파생 컬럼에 해당한다.

 

상품 엔터티의 최근 상품가격 속성은 성능 향상을 위해 중복 컬럼을 추가한 것이다.

 

파생 컬럼이란?

파생 컬럼이란 다른 컬럼의 값을 바탕으로 계산된 컬럼이다. 이 컬럼은 실제로 저장되는 값이 아니라, 다른 값들의 조합이나 계산 결과로 실시간으로 동적으로 계산된 값이다. 일반적으로 이 컬럼은 데이터베이스에 저장되지 않고, 쿼리할 때마다 계산된다. 

 

예) 연봉 계산

SELECT 기본급 + 보너스 AS 연봉 FROM 직원;

 

예) 나이 계산

SELECT DATEDIFF(CURDATE(), 생년월일) / 365 AS 나이 FROM 회원;

 

예) 할인가격 계산

SELECT 정가 * (1 - 할인율) AS 할인 가격 FROM 상품;

---

 

p.72

 

18. 상품에 대한 가격 이력 정보를 상품 가격 이력 엔터티로 분류하여 저장하고 있다. 하지만 상품에 대한 정보를 조회할 때마다 상품가격 이력 엔터티에서 해당 상품의 최신 가격을 같이 조회해 와야 해서 성능 저하가 우려되는 상황이다. 다음 설명 중 가장 적절한 것은?

 

1. 파생컬럼을 추가한다.

2. 상품 엔터티와 상품가격 이력 엔터티를 통합한다.

3. 이력 테이블에 컬럼을 추가한다.

4. 통계 테이블을 추가한다.

 

'왜 파생컬럼을 추가한다'가 답이 아닌가? (chatGPT)

파생 컬럼을 추가한다는 것은 상품 가격 이력 테이블에서 상품의 최신 가격을 동적으로 계산하여, 상품 엔터티에 가격 컬럼을 추가하는 방법입니다. 그러나 파생 컬럼은 주로 다른 값들을 계산하거나 조합하는 데 사용되며, 기본적으로 실시간으로 계산된 값을 나타냅니다. 그런데 최신 가격은 고정된 값으로, 계산된 값을 저장하는 것이 아니라 실제로 저장된 최신 값을 가져와야 합니다.
따라서 상품 가격 이력 테이블에서 최근 가격을 파생 컬럼으로 계산하는 방법은 적절하지 않습니다. 최신 가격은 계산이 아닌 조회를 통해 직접적으로 저장된 값이어야 하므로 파생 컬럼은 적합한 해결책이 아닙니다.

---

p.72

 

20. 다음 중 괄호 안에 알맞은 말은 무엇인가?

테이블 반정규화는 성능 향상을 목적으로 수행되지만 일반저긍로 데이터의 무결성을 깨트릴 위험성을 가지고 있다. 하지만 (_______)는 데이터의 무결성을 깨뜨릴 위험 없이 데이터 처리의 성능을 향상시킬 수 있는 반정규화 기법이다.

 

답: `중복 관계 추가`