SQLD: 데이터 모델과 SQL

SeongGyun Hong·2024년 10월 28일

SQLD sql 데이터 모델과 SQL 정규화

SQL

목록 보기

2/51

1. 정규화

정규화는 데이터베이스 설계에서 데이터의 중복을 최소화하고 데이터 무결성을 보장하기 위해 데이터를 구조화하는 과정이다.

제1 정규화 (1NF)

제1 정규화는 모든 속성이 원자값(Atomic Value)을 가져야 한다는 원칙을 따른다.
즉, 각 컬럼에 하나의 값만 존재해야 함

예시:

비정규화된 테이블:
| 주문번호 | 상품         |
|----------|--------------|
| Q001     | P001, P002   |
| Q002     | P003         |

1NF 적용 후:
| 주문번호 | 상품번호     |
|----------|--------------|
| Q001     | P001         |
| Q001     | P002         |
| Q002     | P003         |

제2 정규화 (2NF)

제2 정규화는 제1 정규화가 적용된 상태에서 기본 키가 아닌 모든 속성이 기본 키에 대해 완전 함수적 종속을 가져야 한다는 원칙이다.
즉, 부분적 종속을 제거해야한다.

예시:

비정규화된 테이블:
| 주문번호 | 상품번호 | 상품명  |
|----------|----------|---------|
| Q001     | P001     | 상품A   |
| Q001     | P002     | 상품B   |
| Q002     | P003     | 상품C   |

2NF 적용 후:
주문 테이블:
| 주문번호 |
|----------|
| Q001     |
| Q002     |

상품 테이블:
| 상품번호 | 상품명  |
|----------|---------|
| P001     | 상품A   |
| P002     | 상품B   |
| P003     | 상품C   |

주문_상품 테이블:
| 주문번호 | 상품번호 |
|----------|----------|
| Q001     | P001     |
| Q001     | P002     |
| Q002     | P003     |

제3 정규화 (3NF)

제3 정규화는 제2 정규화가 적용된 상태에서 기본 키가 아닌 모든 속성이 기본 키에 대해 이행적 종속을 가지지 않도록 한다.
즉, 기본 키가 아닌 칼럼이 다른 기본 키가 아닌 칼럼에 의존하는 경우를 제거해야 한다는 것이다.

예시:

비정규화된 테이블:
| 주문번호 | 상품번호 | 상품명 | 공급업체 |
|----------|----------|--------|---------|
| Q001     | P001     | 상품A  | 공급업체X |
| Q002     | P002     | 상품B  | 공급업체Y |

3NF 적용 후:
주문 테이블:
| 주문번호 |
|----------|
| Q001     |
| Q002     |

상품 테이블:
| 상품번호 | 상품명  | 공급업체 |
|----------|---------|---------|
| P001     | 상품A   | 공급업체X |
| P002     | 상품B   | 공급업체Y |

주문_상품 테이블:
| 주문번호 | 상품번호 |
|----------|----------|
| Q001     | P001     |
| Q002     | P002     |

정규화가 되지 않았을 경우의 문제점

데이터 중복 및 불일치 발생

예시:

| 학생ID | 이름    | 과목     | 점수 |
|--------|---------|----------|------|
| 101    | 홍길동  | 수학     | 90   |
| 101    | 홍길동  | 영어     | 85   |
| 102    | 이순신  | 수학     | 95   |
| 101    | 홍길동  | 수학     | 92   |  <-- 데이터 중복

홍길동의 수학 점수가 두 번 기록되었음.
어느 점수가 맞는지 모르고, 불일치가 발생할 수 있음.

업데이트 이상(Anomalies)
- 예시:
```
| 직원ID | 이름    | 부서     | 급여 |
|--------|---------|----------|------|
| 201    | 김철수  | 인사부   | 5000 |
| 202    | 박영희  | 인사부   | 5500 |
| 203    | 이민호  | 영업부   | 6000 |
```
  - 삽입 이상: 새로운 부서인 '개발부'를 추가하려면 직원 정보를 함께 삽입해야 함. 부서만 추가하는 것이 불가
  - 삭제 이상: 인사부의 모든 직원이 퇴사하면 '인사부' 정보도 함께 삭제됨.
  - 수정 이상: 인사부의 이름을 'HR부서'로 변경하려면 관련된 모든 행을 수정해야 함.

저장 공간 낭비

예시:

| 고객ID | 이름    | 주소       | 전화번호     |
|--------|---------|------------|--------------|
| 301    | 장보고  | 서울시 강남구 | 010-1234-5678 |
| 302    | 유관순  | 서울시 강남구 | 010-8765-4321 |
| 303    | 안중근  | 서울시 강남구 | 010-5678-1234 |

'서울시 강남구' 주소가 반복 저장되어 불필요한 저장 공간을 차지

ER(개체관계형) 모델링에서 정규화를 중요하게 다루는 이유

데이터 무결성 보장

정규화를 통해 중복 데이터를 제거하여 데이터의 무결성을 보장

예시: 각 학생의 점수를 별도의 테이블로 분리하여 중복을 방지

학생 테이블:
| 학생ID | 이름    |
|--------|---------|
| 101    | 홍길동  |
| 102    | 이순신  |

성적 테이블:
| 학생ID | 과목   | 점수 |
|--------|--------|------|
| 101    | 수학   | 90   |
| 101    | 영어   | 85   |
| 102    | 수학   | 95   |

효율적인 데이터 관리

정규화를 통해 데이터를 체계적으로 분리하여 관리

예시: 직원과 부서를 분리하여 각 테이블에서 독립적으로 관리

직원 테이블:
| 직원ID | 이름    | 부서ID |
|--------|---------|--------|
| 201    | 김철수  | 1      |
| 202    | 박영희  | 1      |
| 203    | 이민호  | 2      |

부서 테이블:
| 부서ID | 부서명  |
|--------|--------|
| 1      | 인사부  |
| 2      | 영업부  |

데이터 일관성 유지

정규화를 통해 데이터가 일관성 있게 유지

예시: 고객과 주소를 분리하여 주소를 일관성 있게 유지

고객 테이블:
| 고객ID | 이름    | 주소ID |
|--------|---------|--------|
| 301    | 장보고  | 1      |
| 302    | 유관순  | 1      |
| 303    | 안중근  | 1      |

주소 테이블:
| 주소ID | 주소         |
|--------|-------------|
| 1      | 서울시 강남구 |

이렇게 정규화를 통해 데이터의 중복과 불일치를 방지하고, 데이터 삽입, 삭제, 수정 시 발생할 수 있는 문제를 최소화하며, 저장 공간을 효율적으로 사용할 수 있기에 정규화는 매우 중요하다.

2. NVL

NVL 함수는 Oracle 데이터베이스에서 NULL 값을 다른 값으로 대체하는 데 사용됨.

NULL 속성의 이해

NULL은 데이터베이스에서 값이 없음을 나타내는 특수한 값.
NULL은 "알 수 없음" 또는 "존재하지 않음"을 의미하며, 이를 처리하는 방법이 중요

IS NULL 조건식

IS NULL 조건식은 특정 컬럼의 값이 NULL인지 확인할 때 사용

예시:

SELECT * FROM 테이블명 WHERE 컬럼명 IS NULL;

IS NULL 조건식과 연산자

IS NULL 조건식은 비교 연산자(=, <>, > 등)와 함께 사용할 수 없음.
대신 IS NULL 또는 IS NOT NULL을 사용해야 함

예시:

SELECT * FROM 테이블명 WHERE 컬럼명 IS NOT NULL;

집계 함수와 NULL

집계 함수는 NULL 값을 무시하고 연산을 수행.
그러나 모든 값이 NULL인 경우 결과는 NULL이 됨.

SUM 집계 함수 안에 인자로 전부 NULL이 들어가는 경우

SELECT SUM(컬럼명) FROM 테이블명 WHERE 조건식;
-- 모든 값이 NULL인 경우 결과는 NULL

NVL 함수로 가공한 상태에서의 집계함수의 사용은 인덱스 스캔 효율성이 떨어진다.

NVL 함수는 NULL 값을 대체하므로 인덱스를 사용할 때 효율성이 떨어질 수 있다.

이유:
NVL 함수를 사용하면 인덱스 스캔이 아닌 테이블 풀 스캔이 발생할 가능성이 높아지기 때문임.
이는 인덱스가 NVL 함수의 결과를 포함하지 않기 때문.
인덱스는 원래 데이터만 가지고 있고, NVL 함수를 쓰면 원래 데이터가 아닌 변환된 데이터를 사용하게 되기에 인덱스를 제대로 사용하지 못하고 테이블 전체를 훑어야 함. 따라서 인덱스 스캘 효율이 떨어지게 됨.

3. 본질 식별자

엔터티에는 데이터를 유일하게 식별할 수 있는 속성이 반드시 존재해야 하며, 이를 식별자라고 한다.
식별자를 통해 그 엔터티의 본질을 규정하게 되므로 식별자 정의는 매우 중요

예시:
주문상세 엔터티의 식별자를 주문번호 + 상품번호로 정의한다면, 한 주문 내에서 여러 상품 선택은 가능하나 해당 상품은 한번만 선택할 수 있다고 규정하는 것

INSERT INTO 주문상세 (주문번호, 상품번호) VALUES ('Q001', 'P001');
INSERT INTO 주문상세 (주문번호, 상품번호) VALUES ('Q001', 'P001'); -- 불가

트랜젝션의 4가지 특징

트랜젝션은 데이터베이스 관리 시스템(DBMS)에서 하나의 작업 단위로 수행되는 일련의 연산들을 의미한다.
트랜젝션은 반드시 다음 네 가지 특징(ACID)을 가진다.

원자성 (Atomicity)

트랜젝션의 모든 연산은 모두 성공적으로 수행되거나, 전혀 수행되지 않은 것처럼 처리되함.
즉, 트랜젝션 내의 모든 작업이 완벽히 수행되지 않으면 아무런 작업도 수행되지 않은 상태로 복귀해야 해야만 함.

일관성 (Consistency)

트랜젝션이 완료된 후 데이터베이스는 항상 일관성 있는 상태를 유지해야 함.
일관성 규칙은 데이터베이스의 무결성을 유지하기 위한 제약 조건을 의미

고립성 (Isolation)

트랜젝션은 서로 독립적으로 수행되어야 하고, 하나의 트랜젝션이 완료되기 전에 다른 트랜젝션이 해당 트랜젝션의 중간 결과를 참조해서는 안 됨.

지속성 (Durability)

트랜젝션이 성공적으로 완료되면 그 결과는 영구적으로 데이터베이스에 저장되어야 함.
시스템 오류가 발생하더라도 완료된 트랜젝션의 결과는 보존되어야 할 것.

AVG

AVG의 경우 SUM / COUNT 와 동일하다.

SELECT AVG(수당) FROM 테이블명;

SELECT SUM(수당) / COUNT(수당) FROM 테이블명;

다만, COUNT(수당)이 0인 경우, 즉 수당 칼럼의 모든 값이 NULL인 경우에는 분모가 0이 되고 SUM(수당)이 NULL을 반환하므로 계산이 불가하며 이 경우 AVG(수당)은 NULL을 반환함.

다만,NULL이 아닌 값을 0으로 나누면 에러가 발생한다.

SeongGyun Hong

헤매는 만큼 자기 땅이다.

이전 포스트

SQLD: 데이터 모델링의 이해

다음 포스트

SQLD: 데이터 모델과 SQL

SQL

1. 정규화

제1 정규화 (1NF)

제2 정규화 (2NF)

제3 정규화 (3NF)

정규화가 되지 않았을 경우의 문제점

ER(개체관계형) 모델링에서 정규화를 중요하게 다루는 이유

2. NVL

NULL 속성의 이해

IS NULL 조건식

IS NULL 조건식과 연산자

집계 함수와 NULL

SUM 집계 함수 안에 인자로 전부 NULL이 들어가는 경우

NVL 함수로 가공한 상태에서의 집계함수의 사용은 인덱스 스캔 효율성이 떨어진다.

3. 본질 식별자

트랜젝션의 4가지 특징

원자성 (Atomicity)

일관성 (Consistency)

고립성 (Isolation)

지속성 (Durability)

AVG

SQLD: 데이터 모델링의 이해

SQLD: SQL 기본(1)

0개의 댓글