정규화
정규화(DB Normalization)의 개념
- 최소한의 데이터만을 하나의 엔터티에 넣는식으로 데이터를 분해하는 과정
- 데이터의 일관성, 최소한의 데이터 중복, 최대한의 데이터 유연성을 위한 과정이라 볼 수 있음
- 데이터 중복을 제거하고 데이터 모델의 독립성을 확보
- 데이터 이상현상을 줄이기 위한 데이터서베이스 설계 기법
- 엔터티를 상세화하는 과정으로 논리 데이터 모델링 수행 시점에서 고려됨
- 제 1정규화부터 제 5정규화까지 존재, 실질적으로는 제 3 정규화까지만 수행
- 이상현상(Abnormality)
- 정규화를 하지 않아 발생하는 현상(삽입이상, 갱신이상, 삭제이상)
- 특정 인스턴스가 삽입 될때 정의되지 않아도 될 속성까지도 반드시 입력되어야하는(삽입이상) 현상이 발생함
ex) 만약 사원 + 부서 엔터티를 합쳐 놓고 사원번호, 사원이름, 전화번호, 부서번호, 부서명, 부서위치의 속성이 존재할 때
새로운 사원 값이 추가될 때 정해지지 않은 부서정보(부서번호, 부서명, 부서위치) 모두 임의값 또는 NULL 삽입되야함.
반대로 부서가 새로 추가 될 경우 소속 사원이 없어도 사원과 관련된 모든 속성이 불필요하게 값이 입력되어야 함
- 불필요 한 값까지 입력해야하는 현상을 삽입이상, 그 외 갱신이상, 삭제이상이 발생할 수 있음
ex) 부서 정보만 삭제하면 되는데 관련된 사원 정보까지도 함께 삭제되는 현상(삭제이상)
정규화 단계
1. 제 1정규화(1NF)
- 테이블이 컬럼 원자성을 갖도록 테이블을 분해하는 단계
- 쉽게 말해 하나의 행과 컬럼의 값이 반드시 한값만 입력되도록 행을 분리하는 단계
예시) 구매 테이블의 제 1정규화
상품에 여러 값이 있으므로 이를 여러 인스턴스로 분해
홍길동과 박길동은 구매상품이 두 값이 입력되어 있으므로 이를 각각 두행으로 분리하는 작업
2. 제 2정규화(2NF)
- 제 1정규화를 진행한 테이블에 대해 완전 함수 종속을 만들도록 테이블을 분해
- 완전 함수 종속이란, 기본키를 구성하는 모든 컬럼의 값이 다른 컬럼을 결정짓는 형태
- 기본키의 부분 집합이 다른 컬럼과 1:1 대응 관계를 갖지 않는 상태를 의미
- PK(Primary Key)가 2개 이상일 때 발생, PK와 종속되는 관계가 있다면 분리한다.
예시) 수강이력 테이블의 제 2정규화
기본키(학생번호 + 강의명)중, 강의명에 의해 강실이 결정 -> 완전 함수 종속성 위배
(부분 함수 종속성을 가짐)
- > PK와 부분 함수 종속성을 갖는 컬럼을 각각 다른 테이블로 분해
수강이력에서는 한 학생이 여러 강의를 수강할 수 있기 때문에 주식별자는 학생번호만으로 불가능(유일성 불만족) 따라서 학생번호와 강의명과 결합되어 주식별자가 되어야 한다(한 학생이 같은 강의는 수강할 수 없다고 가정) 이 때, 주식별자의 부분집합인 강의명에 의해 강의실이 달라지는 1 대 1 대응관계를 갖는것을 완전 함수 종속성 위배, 같은 말로 부분 함수 종속 고나계라고 하는데, 제 2 정규화는 이러한 부분 함수 종속성을 깨는 것을 목표로 한다. 따라서 주식별자를 분리할 수 없으니 주식별자는 수강이력에 그대로 있고 문제가 되는 강의실 컬럼을 주식별자와 분리
3. 제 3 정규화(3NF)
- 제 2정규화를 진행한 테이블에 대해 이행적 종속을 없애도록 테이블을 분리
- 이행적 종속성이란 A -> B, B -> C의 관계가 성립할 때, A -> C가 성립되는 것을 말함
- (A,B)와 (B,C)로 분리하는 것이 제 3규화
예시) 구매 테이블 제 3 정규화
고객번호에 의해 상품명이 결정, 상품명에 의해 가격이 결정되는데
고객번호에 의해서도 구매 가격이 결정됨(고객이 상품을 결정하면 그에 매칭되는 가격이 결정되는 구조이므로) 따라서 (고객번호 + 상품명)과 (상품명 + 가격)으로 분리하는 것이 제 3 정규화
이 경우 테이블을 분리하지 않으면, 구매 테이블에서 상품명을 변경해야 하는 상황이 발생할 경우 그 때마다 구매 테이블에서도 가격을 변경해야 한다. 하지만 제 3규화를 진행하여 테이블을 분리하게 되면, 구매 테이블에서의 상품명만 변경하면 되므로 업데이트 비효율성이 줄어듬
4. BCNF(Boyce-Codd Normal Form) 정규화
- 모든 결정자가 후보키가 되도록 테이블을 분해하는 것(결정자가 후보키가 아닌 다른 컬럼에 종속되면 안됨)
5. 제 4정규화
- 여러 컬럼들이 하나의 컬럼을 종속시키는 경우 분해하여 다중값 종속성을 제거
6. 제 5 정규화
- 조인에 의해서 종속성이 발생되는 경우 분해
- 반정규화=역정규화(De-Normalization)의 개념
- 데이터베이스의 성능 향상을 위해 데이터 중복을 허용하고 조인을 줄이는 데이터베이스 성능 방법
- 시스템의 성능 향상, 개발 및 운영의 단순화를 위해 정규화된 데이터 모델을 중복, 통합, 분리하는 데이터 모델링 기법
- 조회(SELECT) 속도를 향상시키지만, 데이터 모델의 유연성은 낮아짐
* 비정규화는 정규화를 수행하지 않음을 의미
- 반정규화 수행 케이스
- 정규화에 충실하여 종속성, 활용성은 향상되지만 수행 속도가 느려지는 경우
- 다량의 범위를 자주 처리하는 경우
- 특정 범위의 데이터만 자주 처리하는 경우
- 요약/집계 정보가 자주 요구되는 경우
관계와 조인의 이해
- 관계(Relationship)의 개념
- 엔터티의 인스턴스 사이의 논리적인 연관성
- 엔터티의 정의, 속성 정의 및 관계 정의에 따라서도 다양하게 변할 수 있음
- 관계를 맺는다는 의미는 부모의 식별자를 자식에 상속하고, 상속된 속성을 매핑(조인키)로 활용
-> 부모, 자식을 연결함
- 관계의 분류
- 관계는 존재에 의한 관계와 행위에 의한 관계로 분류
- 존재 관계는 엔터티 간의 상태를 의미
ex) 사원 엔터티는 부서엔 터티에 소속
- 행위 관계는 엔터티 간의 어떤 행위가 있는 것을 의미
ex) 주문은 고객이 주문할 때 발생
- 조인의 의미
- 데이터의 중복을 피하기 위해 테이블은 정규화에 의해 분리되지만 두 테이블은 서로 관계를 맺게 되고, 다시 이 테이블의 데이터를 동시에 출력하거나 관계가 있는 테이블을 참조하기 위해서는 데이터를 연결하는 과정을 조인이라고
사원 테이블의 '홍길동'의 부서명이 궁금하다
SELECT A.이름, B.부서명 FROM 사원 A
INNER JOIN 부서 B ON A.부서번호 = B.부서번호
WHERE A.이름 = '홍길동';
- 계층형 데이터 모델
- 자기 자신끼리 관계가 발생. 즉, 하나의 엔터티 내의 인스턴스 끼리 계층 구조를 가지는 경우를 말함
- 계층 구조를 갖는 인스턴스끼리 연결하는 조인을 셀프조인이라 함
ex) 직원 테이블의 상사 이름을 알고 싶을 때
상사의 이름은 없고 상사의 아이디만 나와있다. Employee의 직원이 상사 번호랑 매칭된다.
셀프 조인 쿼리
SELECT
e1.Employee AS EmployeeID,
e1.Name AS EmployeeName,
e2.Employee AS ManagerID,
e2.Name As ManagerName
FROM
Employee e1
LEFT JOIN
Employee e2
ON
e1.ManagerID = E2.Employee;
직원의 ManagerID와 다른 행의 Employee ID를 매칭한다.
쿼리 실행 결과
- 상호배타적 관계
- 두 테이블 중 하나만 가능한 관계를 말함
ex) 주문 엔터티 주문 엔터티에는 개인 또는 법인번호 둘 중 하나만 상속 될 수 있음 => 상호배타적 관계
즉, 주문은 개인고객이거나 법인고객 둘 중 하나의 고객만이 가능
트랜잭션
- 하나의 연속적인 업무 단위를 말함
- 트랜잭션에 의한 관계는 필수적인 관계 형태를 가짐
- 하나의 트랜잭션에는 여러 SELECT, INSERT, DELETE, UPDATE 등이 포함될 수 있음
ex) A고객이 B고객에게 100만 원을 이체하려고 가정
1. A고객의 잔액이 100만원 이상인지 확인
2. TRUE면 A고객 잔액을 -100 UPDATE
3. B 고객 잔액에 +100 UPDATE
이 때, 2번과 3번 과정이 동시에 수행되어야 한다. 즉 모두 성공하거나 모두 취소돼야 함 (All or Nothing)
=> 이런 특성을 갖는 연속적인 업무 단위를 트랜잭션이라고 한다.
- 주의할 점
1. A고객 잔액 차감과 B고객 잔액 가산이 서로 족립적으로 발생하면 안됨
=> 각각의 INSERT 문으로 개발되면 안됨
2. 부분 COMMIT 불가
=> 동시에 COMMIT 또는 ROLLBACK
- 필수적, 선택적 관계와 ERD
- 두 엔터티의 관계가 서로 필수적일 때 하나의 트랜잭션을 형성
- 두 엔터티가 서로 독립적 수행이 가능하다면 선택적 관계로 표현
IE 표기법)
- 원을 사용하여 필수적 관계와 선택적 관계를 구분
- 필수적 관계에는 원을 그리지 않는다.
- 선택적 관계에는 관계선 끝에 원을 그린다.
바커표기법)
- 실선과 점선으로 구분
- 필수적 관계는 관계선을 실선으로 표기
- 선택적 관계는 관계선을 점선으로 표기
NULL
- DBMS 에서 아직 정해지지 않은 값을 의미
- 0과 빈문자열('')과는 다른 개념
- 모델 설계 시 컬럼별로 NULL을 허용할 지를 결정(Nullable Column)
- NULL의 특성
1. NULL을 포함한 연산 결과는 항상 NULL
EMPLOYEES 테이블
SAL과 COM의 합산한 TOTAL_COMPENSATION 컬럼 추가
SELECT
EMP_ID,
NAME,
SAL,
COM,
SAL + COM AS TOTAL_COMPENSATION
FROM
EMPLOYEES;
결과
TOTAL_COMPENSATION 컬럼의 값은 SAL이나 COM 컬럼에 NULL이 존재할 경우 NULL을 반환
연산 결과를 내고 싶으면 치환한 후 연산 필수
수정 된 쿼리
SELECT
EMP_ID,
NAME,
SAL,
COM,
COALESCE(SAL, 0) + COALESCE(COM, 0) AS TOTAL_COMPENSATION
FROM
EMPLOYEES;
COALESCE 함수는 첫 번째로 NULL 이 아닌 값을 반환한다.
COALESCE( NULL, NULL, NULL, 0) => 0을 반환
따라서 SAL이나 COM이 NULL일 경우 0으로 반환
결과
2. 집계함수는 NULL을 제외하고 연산 결과 리턴
- sum, avg, min, max 등의 함수 항상 NULL을 무시한다.
EMPLOYEES 테이블
COUNT(*)은 모든 행(NULL포함)의 수를 반환한다.
SELECT COUNT(*) AS TOTAL_ROWS FROM EMPLOYEES;
결과
SELECT AVG(SAL) FROM EMPLOYESS;
SELECT SUM(SAL) FROM EMPLOYESS;
SELECT MAX(SAL) FROM EMPLOYESS;
1. (5000 + 6000 + 4500) / 3 = 5566.67
2. (5000 + 6000 + 4500) = 15500
3. 6000
AVG(SAL)은 NULL값을 제외하고 SAL의 평균을 반환
SUM(SAL)은 NULL값을 제외하고 SAL의 합계를 반환
MAX(SAL)은 NULL값을 제외하고 최대값을 찾아 반환
가끔, 전체의 평균을 구하고 싶은데 AVG는 NULL을 무시하기 때문에 NULL을 제외한 행을 평균을 계산한다.
NULL을 무시하지 않은 평균을 구하고 싶을때
SELECT SUM(SAL)/COUNT(*) FROM EMPLOYEES:
NULL을 무시하는 SUM 함수와 NULL을 포함시키는 행의 수를 세는 COUNT(*)을 적절히 이용하면 됨
NULL을 ERD 표기법
- IE표기법에서는 NULL허용여부를 알 수 없음
- 바커 표기법에서는 속성앞에 동그라미가 NULL 허용 속성을 의미함
본질식별자 VS 인조식별자
- 식별자 구분(대체 여부에 따른)
1. 본질식별자
- 업무에 의해 만들어지는 식별자(꼭 필요한 식별자)
2. 인조식별자
- 인위적으로 만들어지는 식별자(꼭 필요하지 않지만 관리의 편이성 등의 이유로 인위적으로 만들어지는 식별자)
- 본질 식별자가 복잡한 구성을 가질 때 인위적으로 생성
- 주로 각 행을 구분하기 위한 기본키로 사용, 자동으로 증가하는 일련번호 같은 형태
예제) 주문과 상세에 대한 엔터티 설계 과정
주문이 들어오면 주문 엔터티에는 (주문번호 + 고객번호)를 저장, 이 때 PK는 주문번호
주문상세에는 각 주문별로 어떤 상품이, 언제, 몇 개 주문됐는지를 기록
주문상세 테이블 설계 시 다음과 같은 식별자를 고려할 수 있다.
1. PK : 주문번호 + 상품번호로 설계
- 주문을 하면 주문번호와 상품번호가 필요하므로 본질식별자(주문번호 + 상품번호)가 된다
- 하지만 PK가 주문번호 + 상품번호이면 하나의 주문번호로 같은 상품의 주문결과를 저장할 수 없게 된다.
=> 실제로 쇼핑을 하다보면 동일한 장바구니에 A 상품을 5개 주문했는데, 또 다시 A상품을 3개 추가로 주문하기도 함
2. PK : 주문번호 + 주문순번(주문순번이라는 컬럼을 생성)
- 하나의 주문에 여러 상품에 대한 주문 결과 저장 가능 -> 주문순번으로 인해 구분함
=> 매 주문마다 동일한 상품 주문 시 주문순번을 정하기 위해 상품의 주문 횟수를 세야한다는 점이 매우 불편, 즉, 사과를 총 3 번 구매 하였으니 주문순번은 1,2,3 순서대로 입력돼야 함
3.PK : 주문 상세번호(인조식별자 생성)
- 주문상세번호로 각 주문이력을 구분하기 때문에 같은 주문의 같은 상품이력이 저장될 수 있음
- 주문상세번호만이 주식별자이므로 나머지 정보들이 불필요하게 중복 저장될 위험 발생
- 실제 업무와 상관없는 주문상세번호를 주식별자로 생성하면 쓸모없는 index가 생성됨(PK 생성 시 자동 unique index 생성)
=> 따라서 인조식별자는 다음의 단점을 가지게 된다.
1. 중복 데이터 발생 가능성 -> 데이터 품질 저하
2. 불필요한 인덱스 생성 -> 저장공간 낭비 및 DML 성능 저하
** 인덱스는 원래 조회 성능을 향상시키기 위한 객체이며, DML(INSERT, UPDATE, DELETE)시 INDEX SPLIT 현상으로 인해 성능이 저하된다.
'SQLD' 카테고리의 다른 글
| SQLD 1과목 정리 (1) (0) | 2024.05.12 |
|---|
