[1과목] 데이터 모델과 SQL - 정규화, 관계와 조인의 이해, 모델이 표현하는 트랜잭션의 이해, Null 속성의 이해, 본질 식별자 vs 인조 식별자

❉ 성능 데이터 모델링
: DB 성능 향상을 목적으로 설계단계의 데이터 모델링 때부터 정규화, 반정규화, 테이블 통합, 테이블 분할, 조인구조, PK, FK 등 여러가지 성능과 관련된 사항이 데이터 모델링에 반영될 수 있도록 하는 것
--------------------------------------------------
- 분석/설계 단계에서 데이터 모델에 성능을 고려한 데이터 모델링을 수행할 경우 성능저하에 따른 재업무 비용을 최소화할 수 있음.
- 데이터의 증가가 빠를수록 성능저하에 따른 성능개선비용은 기하급수적으로 증가하게 된다.
--------------------------------------------------
❉ 성능 데이터 모델링 고려사항 순서
1. 데이터 모델링을 할 때 정규화를 정확하게 수행
2. DB 용량산정을 수행한다.
3. DB에 발생되는 트랜잭션의 유형을 파악한다. 
4. 용량과 트랜잭션의 유형에 따라 반정규화를 수행
5. 이력모델의 조정, PK/FK 조정, 슈퍼/서브타입 조정
6. 성능관점에서 데이터 모델을 검증한다.

 

Part6, 정규화

모델링 시 최대한 중복 데이터를 허용하지 않아야 저장공간의 효율적 사용과 업무 프로세스의 성능을 기대할 수 있다. 
이러한 중복 데이터를 허용하지 않는 방식으로 테이블을 설계하는 방식을 정규화라고 한다.
➡️ 반복적인 데이터를 분리하고 각 데이터가 종속된 테이블에 적절하게 배치되도록 하는 것

 

📍정규화 (DB Normalization)의 개념

• 하나의 엔터티에 많은 속성을 넣게 되면, 해당 엔터티를 조회할 때마다 많은 양의 데이터가 조회될 것이므로 최소한의 데이터만을 하나의 엔터티에 넣는 식으로 데이터를 분해하는 과정을 정규화라고 한다.
  • 정규화를 수행하면 용량을 최소화하고, 데이터 처리기능은 좋아지지만!!!! 반대로 조회성능은 저하됨 → 반정규화 하는 이유 
  • 학생, 학과, 교수 엔터티 ➡️ 학생 테이블에 어느정도까지 넣어야 할까. 학생 테이블에 굳이 교과목 정보는 넣을 필요 X (중복 위험)
• 데이터의 일관성, 최소한의 데이터 중복, 최대한의 데이터 유연성 위한 과정이라고 볼 수 있음
• 데이터의 중복을 제거하고 데이터 모델의 독립성을 확보
• 데이터 이상현상을 줄이기 위한 데이터 베이스 설계 기법
• 엔터티를 상세화하는 과정으로 논리 데이터 모델링 수행 시점에서 고려됨
• 제 1정규화부터 제 5정규화까지 존재, 실질적으로 제 3정규화까지만 수행

 

📍이상현상 (Abnormality)

• 정규화를 하지 않아 발생하는 현상 (삽입이상, 갱신이상, 삭제이상)
• 특정 인스턴스가 삽입될 때 정의되지 않아도 될 속성까지도 반드시 압력되어야 하는 (삽입 이상) 현상이 발생함 
  • ex) 만약 사원 + 부서 엔터티를 합쳐놓고, 사원번호, 사원이름, 전화번호, 부서번호, 부서명, 부서위치의 속성이 존재할 때 
    새로운 사원 값이 추가될 때 정해지지 않은 부서정보 (부서번호, 부서명, 부서위치) 모두 임의값 또는 NULL 삽입되어야함
    반대로, 부서가 새로 추가될 경우 소속사원이 없어도 사원과 관련된 모든 속성이 불필요하게 값이 다 입력되어야 함
  • 삽입이상 : 불필요한 값까지 입력해야 하는 현상
  • 삭제이상 : 부서정보만 삭제하면되는데 관련된 사원정보까지도 함께 삭제되는 현상

 

 

📍정규화 단계 

차수	내용	정규형 (분해된 테이블의 구조)	종속 종류
1차 정규화	같은 성격, 내용 컬럼이 연속될 때 컬럼 제거, 테이블 생성	모든 속성은 반드시 하나의 값을 가져야 한다.	컬럼이 원자성을 갖도록 분리
2차 정규화	PK 복합키 구성일 때 부분적 함수 종속 관계 테이블 분리	엔터티의 일반속성은 ★주식별자 전체★에 종속이어야 한다.	완전 함수 종속
3차 정규화	PK가 아닌 일반 컬럼에 의존하는 컬럼 분리	엔터티의 일반속성 간에는 서로 종속적이지 않다.	이행적 종속★을 없애도록 분리

 

 

1. 제 1 정규화 (1NF)

• 테이블을 컬럼이 원자성(한 속성이 하나의 값을 갖는 특성)을 갖도록 테이블을 분해하는 단계
• 쉽게 말해 하나의 행과 컬럼이 반드시 한 값만 입력되도록 행을 분리하는 단계
• ex) 구매 테이블의 제 1 정규화 : 
  상품에 여러 값이 있으므로 이를 여러 인스턴스로 분해

➡️ 구매상품 컬럼에 2개 값이 입력되어 있는데, 굳이 컬럼을 2개로 나누는게 아니라 한 컬럼에 하나씩 들어가도록 !!

 

➡️ 이런식의 컬럼이 테이블이 어떻게 분리되면 좋겠느냐, 최종 컬럼의 수가 몇개가 될 것이냐 : 구매품목/구매품목가격 => 2개 컬럼!

 

 

2. 제 2 정규화 (2NF)

• 제 1 정규화를 진행한 테이블에 대해 완전 함수 종속을 만들도록 테이블을 분해
  • 완전 함수 종속이란, 기본키를 구성하는 모든 컬럼의 값이 다른 컬럼을 완전히 결정짓는 상태
  • 기본키의 부분집합이 다른 컬럼과 1:! 대응 관계를 갖지 않는 상태를 의미
  • 즉 PK (Primary Key)가 2개 이상일 때 발생하며 PK의 일부와 종속되는 관계가 있다면 분리한다.
• ex) 수강이력 테이블의 제 2 정규화
  (학생번호+강의명) = 기본키,  BUT 강의명에 의해서만 강의실이 결정 → 완전 함수 종속성 위배 ❌ 부분 함수 종속성을 가짐 ⭕️
  ➡️ PK와 부분함수 종속성을 갖는 컬럼을 각각 (PK로부터) 다른 테이블로 분해

학생은 여러 강의를 들을 수 있음 → 학생&강의명 = PK

 

3. 제 3 정규화 (3NF)

• 제 2 정규화를 진행한 테이블에 대해 이행적 종속★을 없애도록 테이블을 분리
  • 이행적 종속성이란, A → B, B → C 의 관계가 성립할 때, A → C가 연결적으로 성립되는 것을 말함
  • (A,B) 와  (B,C) 로 분리하는 것이 제 3 정규화
• ex) 구매 테이블 제 3정규화
  • 고객번호에 의해 상품명이 결정, 상품명에 의해 가격이 결정됨
  • 고객번호에 의해서 구매가격도 결정되는 것이기 때문에~ (고객이 상품을 사야 그에 매칭되는 가격이 결정되니까~)
  • 따라서 (고객번호, 상품명)과 (상품명, 가격)으로 분리하는 것이 제 3 정규화!

분리하지않으면, 상품명을 바꿔야 하는경우 가격도 함께 바꿔야 하지만, 분리하면 한 테이블에서만 수정하면되니까 효율성 UP!

 

 

• ex) 학생 테이블 제 3정규화
  
  • 학번에 따라 과목(전공)이 결정되고, 과목에 따라 교수가 결정됨
  • 이때, 학번이 달라지면 교수도 달라지므로 학번은 교수도 결정한다고 할 수 있다.
  • 따라서 학번~전공 // 전공~교수 로 분리할 필요가 있음 

 

• ex) 계좌번호 제 3정규화
  • 계좌번호에 의해 관리점이 결정, 관리점에 의해 관리점코드가 결정됨
  • 그러면? 계좌번호에 의해서 관리점 코드도 결정되는 것이기 때문에~ 
  • 이때 PK인 두 속성을 분리해서~ 관리점 컬럼을 삭제하고, 따로 관리점 테이블로 분리하면서 이때 결정권자인 관리점 코드가 따라간다.

 

❉ 결정자와 종속관계 : 만약 A 속성이 B 속성의 값을 결정하게 되면, 이 때 A는 B의 결정자라고 하며, 반대로 B는 A에 종속된다고 표현함

 

4. 제 BCNF (Boyce-Codd Normal Form) 정규화, 보이스-코드 정규형

• 모든 결정자가 후보키가 되도록 테이블을 분해하는 것 (결정키가 후보키가 아닌 다른 컬럼에 종속되면 안됨)

 

5. 제 4 정규화

• 여러 컬럼들이 하나의 컬럼을 종속시키는 경우 분해하여 다중값 종속성을 제거

 

6. 제 5 정규화

• 조인에 의해서 종속성이 발생되는 경우 분해

 

❉ 시험에는 나오지 않지만, 데이터베이스 이해에 있어서 중요한 내용
[반정규화=역정규화 De-Normalization의 개념]
(또 너무 세세하게 분리되면, 조인이 너무 잦아질 수 있음! 오히려 분해한걸 다시 합치는~)
- 데이터베이스의 성능 향상을 위해 데이터 중복을 허용하고, 조인을 줄이는 데이터베이스 성능 향상 방법
- 시스템의 성능 향상, 개발 및 운영의 단순화를 위해 정규화된 데이터 모델을 중복, 통합, 분리하는 데이터 모델링 기법
- 조회 (SELECT) 속도를 향상시키지만, 데이터 모델의 유연성은 낮아짐
- 비정규화 : 정규화를 수행하지 않음을 의미
- 조회 시 디스크 I/O가 많거나 경로가 멀어 조인에 의한 성능 저하를 막기 위해 수행
- 일반적으로 정규화 시 입력/수정/삭제 성능이 향상되며, 반정규화 시 조인성능이 향상된다! ★

[반정규화 수행 케이스]
- 정규화에 충실하여 종속성, 활용성은 향상되지만 수행속도가 느려지는 경우에
- 다량의 범위를 자주 처리해야 하는 경우 (A,B 테이블은 같이 쓰는게 오히려 이점이 있는 경우)
- 특정 범위의 데이터만 자주 처리하는 경우
- 요약/집계 정보가 자주 요구되는 경우

 

---

Part7, 관계와 조인의 이해

📍관계 (Relationship)의 개념

• 엔터티의 인스턴스 사이의 논리적인 연관성 (인스턴스끼리는 페어링)
• 엔터티의 정의, 속성 정의 및 관계 정의에 따라서도 다양하게 변할 수 있음
• 관계를 맺는다는 의미는 부모 엔터티의 식별자를 자식에 상속하고, 상속된 똑같은 속성을 매핑키(조인키)로 활용
  → 부모 & 자식을 연결함

• 부모가 먼저 존재하는것, 부서가 먼저 존재해야 그안에 들어갈 사원이 생기는거죠.
• 부모 = 부서 테이블, 자식 = 사원 테이블
• 부서 테이블의 기본키 부서번호를 상속해주고. 나중에 이 두 테이블을 조인할 때 부서번호를 매핑키로 사용!

 

📍관계의 분류

• 관계는 존재에 의한 관계와 행위에 의한 관계로 분류
• 존재관계는 엔터티 간의 상태를 의미, 존재하게 함
  • ex) 사원 엔터티는 부서 엔터티에 소속
• 행위 관계는 엔터티 간의 어떤 행위가 있는 것을 의미
  • ex) 주문은 고객이 주문할 때 발생

 

📍관계를 도출할 때 확인해야하는 사항

• 2개의 엔터티 사이에 관심있는 연관규칙이 존재하는가?
• 2개의 엔터티 사이에 정보의 조합이 발생하는가?
• 업무 기술서, 장표에 관계연결에 대한 규칙이 서술되어있는가?
• 업무 기술서, 장표에 관계열결을 가능케 하는 동사(Verb)가 있냐?

 

 

📍조인의 의미

• 데이터는 중복을 피하기 위해 정규화에 의해 테이블 분리를 한다.
• 분리되면서 두 테이블은 서로 관곌를 맺게 됐고, 다시 이 두 테이블을 업무적으로 동시에 출력하거나 참조하기 위해서 다시 데이터를 연결해야하는데, 그 과정을 '조인'이라고 함!

사진에서는 관리점 코드로 두 테이블을 다시 연결할 수 있겠음

SELECT A.계좌번호, B.관리점
  FROM 계좌 A, 관리점 B
 WHERE A.관리점코드 = B.관리점코드
   AND A.계좌번호 = '100111'

 

 

📍계층형 데이터 모델 (셀프 조인)

• 자기 자신끼리 관계가 발생
• 즉, 하나의 엔터티 내의 인스턴스끼리 계층구조를 가지는 경우를 말함
• 계층 구조를 갖는 인스턴스끼리 연결하는 조인을 셀프조인이라 함. (같은 테이블을 여러번 조인)

MGR은 상위관리자 번호, 근데 그 상위관리자도 직원번호를 가지고 있음 EMPNO

SELECT E1.NAME AS 사원이름,
       E2.NAME AS 매니저이름
  FROM EMP E1, EMP E2
 WHERE E1.MGR = E2.EMPNO

 

 

📍상호배타적 관계

• 두 테이블 중 하나만 가능한 관계를 말함
• ex) 주문 엔터티에는 개인 or 법인번호 둘 중 하나만 상속될 수 있음 ➡️ 상호배타적 관계
  개인일때는 개인고객번호, 법인고객일때는 법인번호
  즉, 주문은 개인고객이거나 법인고객 둘 중 하나의 고객만이 가능

 

 

---

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

📍트랜잭션이란

• 하나의 연속적인 업무 단위를 말함
• 트랜잭션에 의한 관계는 필수적인 관계 형태를 가짐
• 하나의 트랜잭션에는 여러 SELECT, INSERT, DELETE, UPDATE 등이 포함될 수 있음
• 계좌 이체를 예로 들면? 
  • 이때, 2번과 3번 과정이 동시에 수행되어야 한다. 즉, 모두 성공하거나 모두 취소되어야함 (All or Nothing) ➡️ 이런 특성을 갖는 연속적인 업무 단위를 트랜잭션이라고 한다.
  • 주의점 ① A고객 잔액 차감과 B 고객 잔액 가산이 서로 독립적으로 발생하면 안됨 → 각각의 INSERT 문으로 개발되면 안됨
  • 주의점 ② 부분 COMMIT 불가 → 동시 COMMIT 또는 ROLLBACK 처리 

[A고객이 B고객에게 100만원을 이체하려고 한다]
STEP 1) A고객의 잔액이 100만원 이상인지 확인
STEP 2) 이상이면, A 고객 잔액을 -100 UPDATE
STEP 3) B 고객 잔액에 +100 UPDATE

 

 

📍필수적, 선택적 관계와 ERD

• 두 엔터티의 관계가 서로 필수적일 때 하나의 트랜잭션을 형성
• 두 엔터티가 서로 독립적 수행이 가능하다면 선택적 관계로 표현

	IE 표기법	바커 표기법
구분	원을 사용하여 필수적 관계과 선택적 관계를 구분	실선과 점선으로 구분
필수적 관계	필수적 관계에는 원을 그리지 않는다 X	필수적 관계는 관계선을 실선으로 표기 ㅡㅡㅡㅡ
선택적 관계	선택적 관계에는 관계선 끝에 원을 그린다. O	선택적 관계는 관계선을 점선으로 표기 • • • •

 

 

 

---

Part9, Null 속성의 이해

📍NULL 이란

• DBMS 에서 아직 정해지지 않은 값을 의미
• 0과 빈문자열("")과는 다른 개념
• 모델 설계 시 각 컬럼별로 NULL 을 허용할 지를 결정 (Nullable Column)
• NULL 값은 어떤 숫자와 크기를 비교할 수 없음!!
• NULL = NULL 연산의 결과는 둘다 미지의 수 이므로 False 혹은 unknown 이다.
• 오라클에서 '' 같이 입력했을 때, 공백 데이터가 입력되는게 아니라 오라클에서는 NULL로 입력된다.
• 서비스번호 컬럼의 모든 레코드가 '001' 과 같은 숫자형식으로 입력되어 있어야 오류가 발생하지 않는다.

 

📍NULL 의 특성

- 컬럼끼리 연산할 때 NULL을 포함하면 결과는 NULL / SUM(COL2) + SUM(COL3) = NULL
- 레코드끼리 가로로 더하면 NULL을 제외하고 나머지 숫자들만 계산함  / SUM(COL2+COL3) = 90
=====================================
- NULL 값과의 수치연산은 NULL 값을 리턴한다. NULL + 14 = NULL
- NULL 값과의 비교연산은 거짓(FALSE)를 리턴한다.

 

1. NULL 을 포함한 연산 결과는 항상 NULL

• 처음에는 NULL값이 포함된 행은 결과가 NULL(좌)
• NULL이면 0으로 치환하고 연산하면 결과 나옴(우)

 

 

2. 집계함수는 NULL 을 제외한 연산 결과 리턴

(이때, 주의할점 : 집계 계산할때 NULL값으로 0으로 처리하는게 아니라 just '무시'해버린다는 것!!)

• SUM, AVG, MIN, MAX 등의 함수는 항상 NULL 을 무시한다.
• SAL 컬럼에는 NULL 값이 없고, COMM 컬럼에는 4개만 정상

 

 

• NULL 값을 포함한 컬럼 평균연산결과
  • AVG : NULL을 무시하고, 나머지 10개의 평균만 리턴 
  • SUM() / COUNT(*) : 총합을 총 행 개수인 14로 나눈 값

 

 

📍NULL 의 ERD 표기법

• IE 표기법 ; NULL 허용여부를 알 수 없음
• 바커 표기법 ; 속성 앞에 동그라미가 NULL 허용 속성을 의미함
  • O : NULL 허용
  • # : NULL 허용하지 않음

 

 

 

 

---

Part10, 본질 식별자 vs 인조 식별자

📍식별자 구분 (대체 여부에 따른)

1. 본질 식별자

• 업무에 의해 만들어지는 식별자 (=꼭 필요한 식별자)

2. 인조 식별자

• 인위적으로 만들어지는 식별자 (=꼭 필요하진 않지만, 관리의 편의성 등의 이유로 인위적으로 만들어지는 식별자)
• 본질 식별자가 복잡한 구성을 가질 때 인위적으로 생성
• 주로 각 행을 구분하기  위한 기본키로 사용되며 자동으로 증가하는 일련번호 같은 형태임
• ex) 주문과 주문이력에 대한 엔터티 설계과정
  • 주문 = 주문번호, 고객번호
  • 주문이력 = 어떤 상품을 몇개 언제 어디에 배송해야하냐

• 주문상세 테이블 설계시 다음과 같은 식별자를 고려할 수 있다.
  • 1) PK : 주문번호 + 상품번호로 설계
    • 주문을 하면, 주문번호와 상품번호가 필요하므로 본질식별자가 된다.
    • 그러나, PK가 "주문번호 + 상품번호"이면, 하나의 주문번호로 같은 상품의 주문결과를 저장할 수 없게 된다.
      (실제로 쇼핑하다보면 동일한 장바구니에 A상품 5개 구매 후 또다시 A 상품을 3개 추가주문할 수도 있기 때문)
   

• 2) PK : 주문번호 + 주문순번 (새로운 컬럼 생성)
  • 하나의 주문에 여러 상품에 대한 주문결과 저장가능 → 주문 순번으로 인해 구분함
  • 그러나, 매 주문마다 주문순번을 정학 위해서 상품의 주문 횟수를 세어야 한다는 점이 매우 불편 (순서대로 1,2,3 입력)
   

 

• 3) PK : 주문상세번호 (인조식별자 생성)
  • 주문상세번호로 각 주문이력을 구분하기 때문에 같은 주문의 같은 상품이력이 저장될 수 있음
  • 주문상세번호만이 주식별자이므로 나머지 정보들이 불필요하게 중복저장될 위험 발생
  • 실제 업무와 상관없는 주문상세번호를 주식별자로 생성하면 쓸모없는 index가 생성됨 (PK 생성 시 자동 unique index 생성)
   

1번 2번은 완전 동일한건데,

 

❉ 따라서, 인조식별자는 다음과 같은 단점을 가지게 된다
1. 중복 데이터 발생 가능성 → 데이터 품질 저하
2. 불필요한 인덱스 생성 → 저장공간 낭비 및 DML 성능 저하

(인덱스는 원래 조회성능을 향상시키기 위한 객체이며,
 인덱스는 DML(INSERT, UPDATE, DELETE)시 INDEX SPLIT 현상으로 인해 성능이 저하된다.)
→ 데이터가 입력될수록 데이터가 변경될때마다 인덱스가 변경되고 데이터가 변경되다보니, 인덱스 스필릿이 발생할 수 있다는 뜻

 

 

❉ 순차적으로 수행되는 작업 A와  B가 반드시 모두 수행되거나 모두 수행되지 않아야 한다고 할때,
- A와 B는 하나의 트랜잭션으로 묶여 처리되어야 한다.
- A와 B 모두 수행한 후 커밋commit을 해줘야 한다.
- A와 B에 주어진 조건은 트랜잭션의 원자성에 해당한다.
- A까지만 수행되고 시스템 장애가 발생했다면, A를 undo 해야한다.