[SQLD] 1과목 데이터 모델링의 이해 - 데이터 모델과 SQL

🚀 정규화

1️⃣ 정규화(Normalization)

- 정규화는 데이터의 일관성, 최소한의 중복, 최대한의 데이터 유연성을 위한 방법이며 데이터를 분해하는 과정이다.

- 정규화는 테이블 중복을 제거하고 데이터 모델의 독립성을 확보하기 위한 방법이다.

- 정규화는 테이블을 분해해서 데이터 중복을 제거하기 때문에 모델의 유연성을 높인다.

- 정규화를 수행하면 비즈니스에 변화가 발생하여도 데이터 모델의 변경을 최소화할 수 있다.

- 정규화는 제1정규화부터 제5정규화까지 있지만, 실질적으로는 제3정규화까지만 수행한다.

- 정규화된 모델은 테이블이 분해된다. 테이블이 분해되면 직원 테이블과 부서 테이블 간에 부서코드로 조인(Join)을 수행하여 하나의 합집합으로 만들 수도 있다.

- 정규화를 수행하면 불필요한 데이터를 입력하지 않아도 되기 때문에 중복 데이터가 제거된다.

 

👉 이상현상(Anomaly)

이상현상	설명
삽입 이상 (Insertion Anomaly)	- 데이터를 추가하려고 할 때 불필요한 정보까지 함께 저장해야하는 문제 - 예) 학생과 과목 정보를 하나의 테이블에 저장하면, 아직 과목을 듣지 않는 학생을 추가할 수 없음
갱신 이상 (Update Anomaly)	- 데이터를 수정할 때 중복된 데이터가 여러 곳에 존재하면, 일부만 변경될 위험이 있음 - 예) 한 교수의 이메일이 여러 행에 중복 저장되어 있으면, 하나만 변경하면 데이터 불일치 발생
삭제 이상 (Delete Anomaly)	- 데이터를 삭제할 때 원하지 않는 정보까지 함께 삭제되는 문제 - 예) 학생이 특정 과목을 수강하지 않게 되어 데이터를 삭제하면, 그 과목 정보 자체가 사라질 수도 있음

 

 

정규화 절차

정규화 절차	설명
제1정규화	- 속성(Attribute)의 원자성을 확보한다. - 기본키(Primary)를 설정한다.
제2정규화	- 기본키가 2개 이상의 속성으로 이루어진 경우, 부분 함수 종속성을 제거(분해)한다.
제3정규화	- 기본키를 제외한 칼럼 간에 종속성을 제거한다. - 즉, 이행 함수 종속성을 제거한다.
BCNF	- 기본키를 제외하고 후보키가 있는 경우, 후보키가 기본키를 종속시키면 분해한다.
제4정규화	- 여러 칼럼들이 하나의 칼럼을 종속시키는 경우 분해하여 다중값 종속성을 제거한다.
제5정규화	- 조인에 의해서 종속성이 발생되는 경우 분해한다.

 

2️⃣ 함수적 종속성(Functional Dependency)

❶ 제1정규화 - 원자성(Atomicity)

- 각 칼럼의 값이 더 이상 나눌 수 없는 원자값(Atomic Value)이어야 함

- 즉, 하나의 셀(속성)에 여러 개의 값이 들어가면 안 됨; 중복된 데이터 방지

 

🔎 제1정규형(1NF: First Normal Form) 예시

 

✔️ 비정규형

학생ID	이름	과목
101	철수	수학, 과학
102	영희	영어, 국어

🚫 과목 속성에 여러 개의 값이 들어가 있어서 1NF 위배됨

 

✔️ 1NF 변환

학생ID	이름	과목
101	철수	수학
101	철수	과학
102	영희	영어
102	영희	국어

✅ 각 칸에 단일 값만 들어가도록 수정!

 

❷ 제2정규화 - 부분 함수 종속 제거

 

- 1NF를 만족하면서, 기본키의 일부에만 종속되는 속성이 없어야 함

- 즉, 기본키(Primary Key)의 일부분에만 의존하는 칼럼을 제거하고, 새로운 테이블로 분리해야 함

 

🔎 제2정규화 예시

 

✔️ 1NF지만 2NF 위배

학생ID	과목	교수
101	수학	김교수
101	과학	이교수
102	영어	박교수
102	국	정교수

🚫 학생ID , 과목이 기본키인데 교수는 과목에만 의존함 → 부분 함수 종속 발생

 

✔️ 2NF 변환

학생ID	이름
101	철수
101	철수
102	영희
102	영희

[학생-과목 관계 테이블]

과목	교수
수학	김교수
과학	이교수
영어	박교수
국어	정교

[과목-교수 관계 테이블]

 

✅ 기본키의 일부분(과목)만 참조하는 속성(교수)을 별도 테이블로 분리!

 

❸ 제3정규화 - 이행적 종속 제거

- 2NF를 만족하면서, 기본키가 아닌 속성이 다른 비-키 속성(Non-key Attribute)에 의존하면 안 됨

- 즉, 기본키에만 의존해야 하고, 다른 컬럼에 의존하는 속성은 제거해야 함

 

🔎 제3정규화 예시

 

✔️ 2NF지만 3NF 위배

학생ID	이름	학과ID	학과명
101	철수	1	컴퓨터공학
102	영희	2	통계학

🚫 학과명이 학과ID에 의존함 → 이행적 종속 발생 (학생ID → 학과ID → 학과명)

 

✔️ 3NF 변환

학생ID	이름	학과ID
101	철수	1
102	영희	2

[학생 테이블]

학과ID	학과명
1	컴퓨터공학
2	통계학

[학과 테이블]

✅ 기본키가 아닌 속성(학과명)이 다른 속성(학과ID)에 의존하므로 분리!

 

❹ BCNF(Boyce-Codd Normal Form)

- 후보키가 여러 개인 경우 또는 기본키가 아닌 속성이 다른 키를 결정하는 경우 BCNF를 적용한다.

- BCNF는 복수의 후보키가 있고, 후보키들이 복합 속성이어야 하며, 서로 중첩되어야 한다.

 

✅ BCNF 정의

- BCNF는 3NF를 만족하면서, 모든 결정자가 후보키가 되어야 함

📌 결정자(Determinant)란?

- 어떤 속성이 다른 속성을 유일하게 결정할 수 있는 속성

- 예)  학번  → 학생이름 이면 학번이 결정자

 

 

🔎 BCNF가 필요한 예시

 

✔️ 3NF는 만족하지만 BCNF는 위배됨

교수ID	과목	강의실
P1	DB	101
P2	OS	102
P2	DS	103

⚡ 문제점

- 교수ID, 과목 을 기본키로 설정하면 강의실을 유일하게 결정할 수 있음.

- 하지만 "교수ID만으로도 강의실이 결정됨" (즉, 교수ID → 강의실 성립).

- 즉, 기본키가 아닌 속성(강의실)이 결정자가 되는 문제 발생 → 이는 BCNF를 위배하는 상태임.

 

 

✔️ BCNF 변환

교수ID	강의실
P1	101
P2	103

[교수-강의실 테이블]

강의실	과목
101	DB
102	OS
103	DS

[강의실-과목 테이블]

✅ 모든 결정자가 후보키가 되어 BCNF 만족!

 

 

🚨BCNF 적용 시 주의점

- BCNF를 적용하면 테이블이 많아질 수 있음

- 따라서 항상 BCNF를 적용하는 것이 아니라, 성능과 무결성을 고려하여 적절한 정규화를 선택하는 것이 중요

 

 

---

🚀 정규화와 성능

1️⃣ 정규화의 문제점

- 정규화된 데이터는 테이블을 분리하여 저장하므로, 데이터를 조회할 때 조인(Join)이 발생한다.

- 이로 인해 CPU와 메모리 사용량이 증가하며, 성능 저하가 발생할 수 있다.

 

👉 조인의 사용과 성능 문제

- 정규화된 테이블에서는 데이터를 조회하기 위해 JOIN을 사용해야 한다.

 

📌 SQL 예시 (ANSI Join)

SELECT 사원번호, 부서코드, 부서명, 이름, 전화번호, 주소
FROM 직원 INNER JOIN 부서
ON 직원.부서코드 = 부서.부서코드;

 

📌 JOIN 연산은 내부적으로 중첩 루프(Nested Loop) 방식으로 처리될 수 있다.

for(i=0; i<N; i++) {
    for(j=0; j<M; j++) {
        if(직원_부서코드[i] == 부서_부서코드[j]) { ... }
    }
}

- N : 직원 테이블의 행 개수

- M: 부서 테이블의 행 개수

- 데이터가 증가하면 비교 횟수가 기하급수적으로 증가하여 성능이 저하됨.

 

2️⃣성능 최적화 방법

❶ 인덱스 활용

- 데이터베이스 옵티마이저가 INDEX를 사용하면 성능이 향상될 수 있다.

 

❷ 반정규화(De-normalization)

- 성능 개선을 위해 자주 사용하는 데이터를 한 테이블에 저장하여 JOIN을 최소화하는 방식

- 중복 데이터가 생길 수 있지만, 조회 성능은 향상됨.

 

❸ 캐싱(Cache)활용

-  반복적인 조회를 줄이기 위해 캐싱 시스템 도입

 

 

---

🚀 반정규화(De-Normalization)

1️⃣ 반정규화(De-Normalization)

- 데이터베이스의 성능 향상을 위하여, 데이터 중복을 허용하고 조인을 줄이는 데이터베이스 성능 향상 방법이다.

- 반정규화는 조회(SELECT) 속도를 향상하지만, 데이터 모델의 유연성은 낮아진다.

 

2️⃣ 반정규화를 수행하는 경우

👉 반정규화 절차

반정규화 절차	설명
대상 조사 및 검토	- 데이터 처리 범위, 통계성 등을  확인해서 반정규화 대상을 조사한다.
다른 방법 검토	- 반정규화를 수행하기 전에 다른 방법이 있는지 검토한다. - 예) 클러스터링 뷰, 인덱스 튜닝, 응용 프로그램, 파티션 등을 검토한다.
반정규화 수행	- 테이블, 속성, 관계 등을 반정규화 한다.

 

📌 클러스터링(Clustering)

- 클러스터링 인덱스라는 것은 인덱스 정보를 저장할 때 물리적으로 정렬해서 저장하는 방법이다.

- 따라서 조회 시에 인접 블록을 연속적으로 읽기 때문에 성능이 향상된다.

 

3️⃣ 반정규화 기법

❶ 계산된 칼럼 추가

- 자주 사용하는 계산식의 결과를 미리 저장하여 조회 속도를 향상

- 예) 상품 테이블에 할인된 가격을 미리 저장하여 실시간 계산을 줄임

 

❷ 테이블 수직 분할

- 하나의 테이블을 자주 사용하는 칼럼과 그렇지 않은 칼럼으로 분리

- 예) 고객 테이블에서 이름, 연락처는 자주 사용되지만, 주문 내역은 덜 사용되므로 분리

 

 

❸ 테이블 수평 분할

- 데이터가 많아질 경우, 범위에 따라 여러 개의 테이블로 나눔

- 예)  2024년 주문과 2025년 주문을 각각의 테이블로 저장하여 조회 성능 개선

 

📌 파티션(Partition) 기법

- 대량 데이터를 여러 개의 물리적인 파티션으로 나누어 저장

- 레인지 파티셔닝(Range Partitioning): 날짜나 숫자 범위를 기준으로 분할

- 해시 파티셔닝(Hash Partitioning): 특정 해시 값을 기준으로 균등하게 분배

- 리스트 파티셔닝(List Partitioning): 특정 값(예: 지역)에 따라 분할

- 컴포지트 파티셔닝(Composite Partitioning): 여러 기준을 조합하여 분할

 

📌 파티션 테이블의 장점

- 데이터 검색 속도 향상: 필요한 파티션만 조회하면 되므로 속도가 빨라짐

- 백업 및 유지보수 용이: 특정 파티션만 백업하거나 관리할 수 있음

- 대량 데이터 처리 최적화: 인덱스 크기를 줄이고, 쿼리 성능을 개선

 

❹ 테이블 병합

- 여러 개의 작은 테이블을 하나의 테이블로 합쳐 JOIN을 최소화

- 예) 고객 테이블과 회원 정보 테이블을 하나로 통합하여 조회 성능 개선

- 슈퍼 타입과 서브 타입 관계가 발생하면 테이블을 통합하여 성능을 향상

 

📌 Super Type과 Sub Type

- Super Type(슈퍼 타입): 공통 속성을 가진 상위 개념의 테이블

- Sub Type(서브 타입): 특정 속성을 추가로 가지는 하위 개념의 테이블

- 상속 개념을 적용하여 테이블을 설계할 때 사용됨

 

👉 슈퍼 타입 및 서브 타입 변환 방법

변환 방법	설명
OneToOne Type	- 슈퍼 타입과 서브 타입을 개별 테이블로 도출하고 1:1 관계를 설정 - 테이블의 수가 많아서 조인이 많이 발생하고 관리가 어려움
Plus Type	- 서브 타입의 칼럼을 슈퍼 타입에 추가하여 하나의 테이블로 통합 - 조인이 발생하고 관리가 어려움
Single Type	- 하나의 테이블에 모든 서브 타입을 포함하되, NULL 허용 칼럼을 활용 - 조인 성능이 좋고 관리가 편하지만, 입출력 성능이 나쁨

 

 

 

---

🚀 관계와 조인의 이해

1️⃣ 관계(Relationship)와 조인(Join)

- 관계는 테이블 간의 논리적인 연결을 의미하며, 조인은 이를 실제 SQL에서 구현하는 방식

- 1:1 관계, 1:N 관계, N:M 관계를 조인으로 표현

- SQL에서 INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN 등을 사용하여 테이블을 연결

 

2️⃣ 계층형 구조 표현

-  트리(Tree) 구조를 관계형 데이터베이스에서 표현하는 방법

- 계층 구조는 부모-자식 관계로 구성되며, 일반적으로 자기 참조(Self-Referencing)를 사용

- id와 parent_id 컬럼을 활용하여 트리 형태를 구성할 수 있음

 

👉 셀프 조인

- 같은 테이블을 두 번 사용하여 스스로 조인하는 방식

- 주로 계층 구조(예: 직원 테이블에서 상사와 직원 관계)를 표현할 때 활용됨

- ON 절에서 동일한 테이블의 다른 별칭을 사용하여 조인을 수행함.

 

 

---

🚀 모델이 표현하는 트랜잭션의 이해

1️⃣ 식별관계(Identification Relationship)

- 자식 테이블의 기본 키(PK)가 부모 테이블의 기본 키를 포함하는 경우

- 외래 키(FK)가 기본 키의 일부로 사용

- 부모가 삭제되면 자식도 삭제되는 특징

- 예제: 주문(Orders) 테이블과 주문 상세(OrderDetails) 테이블 관계

 

2️⃣ 비식별 관계(Non-Identification Relationship)

- 자식 테이블이 부모 테이블의 기본 키를 포함하지 않는 경우

- 외래 키(FK)가 기본 키와 별개로 존재

- 부모가 삭제되어도 자식 테이블의 데이터는 남아 있을 수 있음

- 예제: 사용자(User) 테이블과 게시글(Post) 테이블 관계

 

📌 강한 개체(Strong Entity)와 약한 개체(Weak Entity)

- 강한 개체: 기본 키(PK)를 자체적으로 가질 수 있는 독립적인 엔터티 - 회원(Member), 상품(Product)

- 약한 개체: 기본 키를 부모 엔터티에서 상속받아야 하는 엔터티 - 주문 상세OrderDetails), 계약서(Contract)

 

 

---

🚀 Null 속성의 이해

- NULL은 값이 없는 상태를 의미하며, 0이나 공백과 다름

- NVL() 함수를 사용해서 NULL을 0 또는 다른 값으로 대체해서 사용

 

1️⃣ Null과 연산을 수행하면 항상 NULL

- NULL + 10 → NULL

- NULL = '값' → FALSE (NULL과 비교는 항상 UNKNOWN)

 

2️⃣ 집계함수는 NULL 값을 제외

- COUNT(*) → NULL 포함

- COUNT(컬럼명) → NULL 제외

- SUM(컬럼명) → NULL 제외

 

---

🚀 본질 식별자와 인조 식별자

1️⃣ 본질 식별자(Natural Key)

- 데이터의 실제 의미를 반영하는 자연적인 키

- 예) 주민등록번호, 이메일 주소, 학번 등

- 문제점: 값이 변할 가능성이 있음

 

2️⃣ 인조 식별자(Surrogate Key)

- 데이터베이스에서 자동으로 생성하는 키 (예: AUTO_INCREMENT)

- 예제: id(1, 2, 3, ...)

- 장점: 변하지 않고 빠르게 검색 가능