데이터 모델과 SQL
정규화
모델링 시 최대한 중복 데이터를 허용하지 않아야 저장공간의 효율적 사용과 업무 프로세스의 성능을 기대할 수 있다. 이러한 중복 데이터를 허용하지 않는 방식으로 테이블을 설계하는 방식을 정규화라고 한다.
정규화(DB Normalization)의 개념
하나의 엔터티에 많은 속성을 넣게 되면, 해당 엔터티를 조회할 때마다 많은 양의 데이터가 조회될 것이므로 최소한의 데이터만을 하나의 엔터티에 넣는 식으로 데이터를 분해하는 과정을 정규화라고 한다. 데이터의 일관성, 최소한의 데이터 중복, 최대한의 데이터 유연성을 위한 과정. 데이터의 중복을 제거하고 데이터 모델의 독립성을 확보한다. 데이터 이상현상을 줄이기 위한 데이터 베이스 설계 기법. 엔터티를 상세화하는 과정으로 논리 데이터 모델링 수행 시점에서 고려된다. 제 1정규화부터 제 5정규화까지 존재하지만, 실질적으로는 제 3정규화까지만 수행한다.
이상현상(Abnormality)
정규화를 하지 않아 발생하는 현상.( 삽입이상 , 갱신이상, 삭제이상) 특정 인스턴스가 삽입될 때 정의되지 않아도 될 속성까지도 반드시 입력되어야 하는 현상(삽입이상)이 발생한다. ex. 만약 '사원+부서' 엔터티를 합쳐 놓고 '사원번호', '사원이름', '전화번호', '부서번호', '부서명', '부서위치'의 속성이 존재할 때 새로운 사원 값이 추가되면 정해지지 않은 부서정보(부서번호, 부서명, 부서위치) 모두 임의값 또는 NULL 값이 삽입되어야 한다. 반대로 부서가 새로 추가될 경우 소속 사원이 없어도 사원과 관련된 모든 속성이 불필요하게 값이 입력되어야 한다.
불필요한 값까지 입력해야 되는 현상을 삽입이상, 그 외 갱신이상, 삭제이상이 발생할 수 있다. ex. 부서 정보만 삭제하면 되는데 관련된 사원 정보까지도 함께 삭제되는 현상(삭제이상).
정규화 단계
- 제 1정규화 (1NF)
테이블의 컬럼이 원자성(한 속성이 하나의 값을 갖는 특성)을 갖도록 테이블을 분해하는 단계.
쉽게 말해 하나의 행과 컬럼의 값이 반드시 한 값만 입력되도록 행을 분리하는 단계. - 제 2정규화 (2NF)
제 1정규화를 진행한 테이블에 대해 완전 함수 종속을 만들도록 테이블을 분해한다.
완전 함수 종속이란, 기본키를 구성하는 모든 컬럼의 값이 다른 컬럼을 결정짓는 상태를 말한다.
기본키의 부분 집합이 다른 컬럼과 1:1 대응 관계를 갖지 않는 상태를 의미.
즉, PK(Primary Key)가 2개 이상일 때 발생하며 PK의 일부와 종속되는 관계가 있다면 분리한다. - 제 3정규화 (3NF)
제 2정규화를 진행한 테이블에 대해 이행적 종속을 없애도록 테이블을 분리.
이행적 종속성이란, A→B, B→C 관계가 성립할 때, A→C가 성립되는 것을 말한다.
(A,B)와 (B,C)로 분리하는 것이 제 3정규화.
결정자와 종속관계: 만약 A속성이 B속성의 값을 결정하게 되면, 이 때 A는 B의 결정자라고 하며 B는 A에 종속된다라고 표현한다.
- BCNF(Boyce-Codd Normal Form) 정규화
모든 결정자가 후보키가 되도록 테이블을 분해하는 것.(결정자가 후보키가 아닌 다른 컬럼에 종속되면 안된다.) - 제 4정규화 (4NF)
여러 컬럼들이 하나의 컬럼을 종속시키는 경우 분해하여 다중값 종속성을 제거. - 제 5정규화 (5NF)
조인에 의해서 종속성이 발생되는 경우 분해.
반정규화(=역정규화)의 개념
데이터베이스의 성능 향상을 위해 데이터 중복을 허용하고 조인을 줄이는 데이터베이스 성능 향상 방법. 시스템의 성능 향상, 개발 및 운영의 단순화를 위해 정규화된 데이터 모델을 중복, 통합, 분리하는 데이터 모델링 기법. 조회(SELECT) 속도를 향상시키지만, 데이터 모델의 유연성은 낮아진다.
비정규화는 정규화를 수행하지 않음을 의미한다.
반정규화(=역정규화) 수행 케이스
정규화에 충실하여 종속성, 활용성은 향상되지만 수행 속도가 느려지는 경우. 다량의 범위를 자주 처리해야 하는 경우. 특정 범위의 데이터만 자주 처리하는 경우. 요약 / 집계 정보가 자주 요구되는 경우.
관계(Relationship)의 개념
엔터티의 인스턴스 사이의 논리적인 연관성. 엔터티의 정의, 속성 정의 및 관계 정의에 따라서도 다양하게 변할 수 있다. 관계를 맺는다는 의미는 부모의 식별자를 자식에 상속하고, 상속된 속성을 매핑키(조인키)로 활용한다는 뜻이다. → 부모, 자식을 연결.
관계(Relationship)의 분류
관계는 존재에 의한 관계와 행위에 의한 관계로 분류
- 존재 관계는 엔터티 간의 상태를 의미한다. ex. 사원 엔터티는 부서 엔터티에 소속.
- 행위 관계는 엔터티 간의 어떤 행위가 있는 것을 의미. ex. 주문은 고객이 주문할 때 발생.
조인의 의미
결국 데이터의 중복을 피하기 위해 테이블은 정규화에 의해 분리된다. 분리되면서 두 테이블은 서로 관계를 맺게 되고, 다시 이 두 테이블의 데이터를 동시에 출력하거나 관계가 있는 테이블을 참조하기 위해서는 데이터를 연결해야 하는데 이 과정을 조인이라 한다.
계층형 데이터 모델
자기 자신끼리 관계가 발생. 즉, 하나의 엔터티 내의 인스턴스끼리 계층 구조를 가지는 경우를 말한다. 계층 구조를 갖는 인스턴스끼리 연결하는 조인을 셀프조인이라 한다.(같은 테이블을 여러 번 조인.)
상호배타적 관계
두 테이블 중 하나만 가능한 관계. ex. 주문 엔터티에는 개인 또는 법인번호 둘 중 하나만 상속 가능. → 상호배타적 관계
트랜잭션이란
하나의 연속적인 업무 단위. 트랜잭션에 의한 관계는 필수적인 관계 형태를 가진다. 하나의 트랜잭션에는 여러 SELECT, INSERT, DELETE, UPDATE 등이 포함될 수 있다. ex. 계좌이체를 보면, A고객이 B고객에게 100만원을 이체한다.
- A 고객의 잔액이 100만원 이상인지 확인
- 이상이면, A고객 잔액을 -100만원 UPDATE
- B 고객 잔액에 +100만원 UPDATE
이 경우 2. 와 3. 은 동시에 수행되어야 한다. 즉, 모두 성공하거나 모두 취소되어야 한다. 이런 특성을 연속적인 업무단위. 즉, 트랜잭션이라고 한다.
- 주의할 점
서로 독립적으로 발생하면 안된다. → 각각의 INSERT문으로 개발되면 안된다.
부분 COMMIT 불가 → 동시 COMMIT 또는 ROLLBACK 처리
필수적, 선택적 관계와 ERD
두 엔터티의 관계가 서로 필수적일 때 하나의 트랜잭션 형성. 두 엔터티가 서로 독립적 수행이 가능하다면 선택적 관계로 표현.
- IE 표기법
원( O )를 사용하여 필수적 관계와 선택적 관계 구분.
필수적 관계에서는 원을 그리지 않는다.
선택적 관계에서는 관계선 끝에 원을 그린다. - Barker 표기법
실선과 점선으로 구분.
필수적 관계는 관계선을 실선으로.
선택적 관계는 관계선을 점선으로. (점선은 IE표기법 원의 반대방향에 표기)
NULL이란
DBMS에서 아직 정해지지 않은 값을 의미. 0과 빈문자열('')과는 다른 개념. 모델 설계 시 각 컬럼별로 NULL을 허용할 지를 결정(Nullable Column).
NULL의 특성
- NULL 을 포함한 연산 결과는 항상 NULL. 예를 들어 NULL 값으로 표기된 데이터와 데이터를 합산하면 NULL 이 나온다. 따라서 값을 구하고 싶은 경우, NULL을 0으로 치환 후 연산한다.
- 집계함수는 NULL 을 제외한 연산 결과를 리턴.
COUNT, SUM, AVG, MIN, MAX 등의 함수는 항상 NULL을 무시한다.
NULL의 ERD 표기법
- IE표기법에서는 NULL 허용여부를 알 수 없다!
- Barker표기법에서는 속성 앞에 동그라미( O )가 NULL허용 속성을 의미한다. (속성 앞에 별(*) 이 붙는 경우 NULL을 입력할 수 없다.)
식별자 구분(대체 여부에 따른)
- 본질식별자: 업무에 의해 만들어지는 식별자. (꼭 필요한 식별자)
- 인조식별자: 인위적으로 만들어지는 식별자(꼭 필요하지는 않지만 관리의 편의성 등 이유로 인위적으로 만들어지는 식별자). 본질식별자가 복잡한 구성을 가질 때 인위적으로 생성. 주로 각 행을 구분하기 위한 기본키로 사용되며 자동으로 증가하는 일련번호 같은 형태.
인조식별자는 다음의 단점을 가진다.
1. 중복 데이터 발생 가능성 → 데이터 품질 저하
2. 불필요한 인덱스 생성 → 저장공간 낭비 및 AML 성능 저하.
인덱스는 원래 조회 성능을 향상시키기 위한 객체이며, 인덱스는 DML(INSERT/UPDATE/DELETE) 시 INDEX SPLIT 현상으로 인해 성능이 저하된다.
