○ 정규화란
/*
한 마디로 DB서버의 메모리 낭비를 낭비하지 않도록 하기 위해서
어떤 테이블을 식별자를 가지는 여러 개의 테이블로 나누는 과정
막 나누는 것이 아니라 다시 JOIN할 수 있도록 식별자를 가져야 한다!
정규화는 순차적으로 진행되어야 한다
EX) 개발자로써의 진로를 포기한 김선규 사원이
정승우 사원의 유혹에 넘어가 옥장판 영업사원으로 취업을 하게 되었다.
이 때 『거래처직원』정보를 데이터베이스화 하려고 한다.
테이블명 : 거래처직원
10byte 10byte 10byte 10byte 10byte 10byte 10byte
-----------------------------------------------------------------------------------------------
거래처회사명 회사주소 회사전화 거래처직원명 직급 이메일 핸드폰
-----------------------------------------------------------------------------------------------
농심 여의대방로 02-345-6789 명소희 대리 SO@NAVER.COM 010-1111-1111
농심 여의대방로 02-345-6789 임미영 대리 MI@NAVER.COM 010-2222-2222
농심 여의대방로 02-345-6789 조태희 부장 TE@DAUM.COM 010-3333-3333
삼양 서울소공동 02-555-8888 박기범 주임 KI@NAVER.COM 010-5555-5555
농심 강원원주로 033-99-9999 서운성 차장 UN@GMAIL.COM 010-4444-4444
:
:
(100만 이상)
(농심 본사만 100만명)
TABLE에서 이상한 점을 찾아보자!!
=>아마 99.9%사람들 모두 이상이 없다고 할 것이다.
가정) 여의대방로 농심 이라는 회사에 근무하는 거래처 직원 명단이
본사 직원만 100만 명이라고 가정한다. (한 행은 10byte 이다)
어느날 여의대방로에 위치한 농심 본사가 경기 분당으로 사옥을 이전하였다.
--UPDATE를 수행해야 할 상황이 발생하게 되었다.(이 때 구성해야 하는 쿼리문)
UPDATE 거래처직원
SET 회사주소='경기 분당', 회사전화='031-345-6789'
WHERE 거래처회사명='농심'
AND 회사주소='여의대방로';
--그러면 100만명의 회사 주소와 회사 전화 정보를 변경해야 하며
100만 개 행을 하드디스크상에서 읽어다가 메모리에 로드시켜 주어야 한다.
즉, 100만 * 70byte 를 모두 하드디스크상에서 읽어다가 메모리에 로드시켜 주어야 한다는 말이다.
--이는, 테이블의 설계가 잘못되었으므로 DB서버는 조만간 메모리 고갈로 인해 DOWN될 것이다.
(물론 H/W사양이 좋아져서 너무 과장되었다고 생각할 수 있지만 많은 사람들이 이용하는 서버라는 것을 생각하자)
--==>> 그러므로 정규화 작업에 들어간다!
■ 제 1 정규화
어떤 테이블에 반복되는 컬럼값들이 존재하면
값들이 반복되어 나오는 컬럼을 분리하여
새로운 테이블은 만들어준다.
거래처직원 → 회사 + 직원
테이블명 : 회사
테이블명 : 직원
핵심은 반복되는 컬럼을 찾는 것과 식별자를 추가OR지정해 주는 것이다!
1정규화를 진행하면 100%(예외X) 부모테이블과 자식테이블로 나뉘게 된다. (1 : 다 관계)
부모 - 참조받는 컬럼(PRIMARY KEY)
자식 - 참조하는 컬럼(FOREIGN KEY)
부모테이블의 PK는 반드시 자식테이블의 FK로 전이된다.
■ 제 2 정규화
복합 PRIMARY KEY를 가지고 있는 테이블이 대상이다. 그리고 무조건 2정규화를 진행해야 한다.
만약 1정규화를 진행했지만 단일 PRIMARY KEY만이 존재한다면 2정규화를 하지 않아도 된다.
※ 수행에 대한 전제 조건
대상 테이블이 단일 PRIMARY KEY로 구성되어 있을 경우 2 정규화는 수행하지 않는다.
단, 복합 PRIMARY KEY로 구성되어 있을 경우 반드시 2 정규화를 수행해야 한다.
테이블명 : 주문
----------------------------------------------------------------------
고객ID 제품코드 주문일자 주문수량
PK PK PK
----------------------------------------------------------------------
어떤 테이블에 PRIMARY KEY 제약조건은 최대 1개까지 설정할 수 있다.
단, 여러 컬럼을 묶어서 설정할 수 있다. → 복합 프라이머리 키
----------------------------------------------------------------------
테이블명 : 과목 → 부모 테이블
----------------------------------------------------------------------
과목번호 과목명 교수번호 교수명 강의실코드 강의실설명
PK PK
1정규화를 진행하면 부모테이블의 PK가 자식테이블의 FK로 전이된다.
----------------------------------------------------------------------
DB0101 오라클기초 21 에디슨 G309 전산실습관 3층 50석
DB0101 오라클기초 22 장영실 H402 인문과학관 4층 100석
DB0102 오라클고급 22 장영실 H402 인문과학관 4층 100석
JV0103 자바심화 25 우장춘 H402 인문과학관 4층 100석
:
----------------------------------------------------------------------
테이블명 : 점수 → 자식 테이블
학번은 PK가 될 수 없다! 다른 과목을 들을 수 있기 때문
----------------------------------------------------------------------
과목번호 교수번호 학번 학생명 점수
FK FK
PK PK
----------------------------------------------------------------------
DB0101 21 1817110 오승우 98
DB0101 21 1817116 최진규 89
:
다중 PK일때 원래는 식별자 전체에 의존적이여야 하지만
다른 하나PK에 의존적이지 않은 컬럼이 존재하다면
따르지 않은 컬럼과 다른 하나PK를 따로 테이블을 생성한다.
과목테이블에서 과목번호 / 교수번호가 과목명을 식별하는 것이 아니라
오직 과목명은 과목번호에만 의존적이므로 따로 테이블을 생성한다.
즉, 식별자가 아닌 컬럼은 식별자에게 의존적이여야 한다!
-> 이를 해결해주는 것이 2정규화
식별자가 아닌 컬럼은 식별자 전체 컬럼에 대해 의존적이여야 하는데
식별자 전체 컬럼에 대해 의존적이지 않고 식별자 일부에만 의존적이라면
이를 분리하여 새로운 테이블을 생성한다.
■ 제 3 정규화
식별자가 아닌 컬럼에 포커싱
식별자가 아닌 컬럼이 식별자가 아닌 컬럼에 의존적이라면 이를 분리하여
새로운 테이블을 생성한다.
-----------------------------------------------------------------------
테이블명 : 과목 → 부모 테이블
----------------------------------------------------------------------
과목번호 과목명 교수번호 교수명 강의실코드 강의실설명
PK PK
1정규화를 진행하면 부모테이블의 PK가 자식테이블의 FK로 전이된다.
----------------------------------------------------------------------
DB0101 오라클기초 21 에디슨 G309 전산실습관 3층 50석
DB0101 오라클기초 22 장영실 H402 인문과학관 4층 100석
DB0102 오라클고급 22 장영실 H402 인문과학관 4층 100석
JV0103 자바심화 25 우장춘 H402 인문과학관 4층 100석
:
강의실설명은 강의실코드에 의존적이다. 하지만 강의실코드와 강의실설명 모두
식별자가 아닌 컬럼이므로 제 3정규화를 통해 『강의실』이라는 테이블을 새로 생성한다.
■ 제 4 정규화
※관계의 종류
1 : 1 =
1 : 다 = 가장 바람직한 관계 (1정규화를 마친 상태)
다 : 다 = 논리적으로는 존재하지만 물리적으로는 불가능/존재하지 않는 관계
다:다 관계를 1:다의 관계로 깨뜨리는 것이 바로 4정규화이다.
학생 - 수강신청 - 강의
고객 - 주 문 - 제품
과 같은 관계를 생성하는 것이 4정규화
■ 역 정규화(비 정규화)
다시 합치는 행위
쪼개놓고 보니까 나중에 보니 합치는게
더 메모리 소모가 적을 것이라는 확신이 있을때 진행한다.
ⓐ 경우
부서 테이블(300BYTE) 사원 테이블(60*1,000,000BYTE)
-------------------- --------------------------------------------------------- + -----------
부서번호 부서명 주소 사원번호 사원명 직급 급여 입사일 부서번호 부서명
PK PK FK
----------------------------------------------------------------------------------- + -----------
10byte 10byte 10byte 10byte 10byte 10byte 10byte 10byte 10byte
10개 행 1,000,000개 행
>> 조회 결과물
-------------------------------------
부서명 사원명 직급 급여
-------------------------------------
부서 테이블과 사원테이블을 JOIN했을 때 크기
SELECT A.부서명,B.사원명,B.직급,B.급여
FROM 부서 A JOIN 사원 B
ON A.부서번호 = B.부서번호;
=> 60,000,300BYTE
역정규화를 한 부서별 사원 테이블만 읽어올 경우
(즉, 사원테이블에 부서명 컬럼을 추가한 경우)
=>70,000,000BYTE
ⓑ 경우 → 역정규화를 수행하는 것이 바람직한 상황
부서 테이블(30*500,000)BYTE 사원 테이블(60*1,000,000)BYTE
-------------------- --------------------------------------------------------- + -----------
부서번호 부서명 주소 사원번호 사원명 직급 급여 입사일 부서번호 부서명
PK PK FK
----------------------------------------------------------------------------------- + -----------
10byte 10byte 10byte 10byte 10byte 10byte 10byte 10byte 10byte
500,000개 행 1,000,000개 행
>> 조회 결과물
-------------------------------------
부서명 사원명 직급 급여
-------------------------------------
부서 테이블과 사원테이블을 JOIN했을 때 크기
SELECT A.부서명,B.사원명,B.직급,B.급여
FROM 부서 A JOIN 사원 B
ON A.부서번호 = B.부서번호;
=> 75,000,000BYTE
역정규화를 한 부서별 사원 테이블만 읽어올 경우
(즉, 사원테이블에 부서명 컬럼을 추가한 경우)
=>70,000,000BYTE
부모테이블과 자식테이블 크기가 비슷할 때 사용될 가능성이 높다.