1. 정규화
🌙 정규화는 테이블을 재구성하여 논리적 구조를 개선하는 절차
🌙 불필요하게 중복되는 데이터를 제거하여 저장 공간을 절약하고 전체적인 성능을 향상시킴
🌙 실무에서는 주로 1~ 3 정규화까지 사용
1) 제 1 정규화
👾 제 1 정규화는 필드의 데이터를 원자화(Atomicity)함
👾 필드에는 단일값을 저장하고 필드끼리는 독립적이어야 함
📌 다음 테이블은 직원의 신상과 맡은 프로젝트를 저장
|
이름
|
월급
|
주소
|
프로젝트
|
|
김상형
|
650
|
이천시
|
홍콩 수출건, TV광고건, 매출 분석건
|
|
문종민
|
480
|
대구시
|
경영 혁신안 작성, 대리점 계획
|
|
권성직
|
625
|
안동시
|
노조 협상건
|
🔎 월급, 주소 필드에 단일값을 저장, 상호 아무런 연관이 없기 때문에 두필드는 원자성을 확보
🔎 프로젝트는 한 직원이 여러 건을 맡을 수 있어 콤마로 구분하여 나열
➡️ 저장의 목적은 달성했지만 원자성이 없어 문제가 발생
* 프로젝트 개수에 제한이 없어 필드 길이를 충분히 할당 해야 함
신입사원은 당장 맡은 프로젝트가 없지만 앞으로 맡을 프로젝트를 위해 최대 길이만큼 미리 확보해야 하니 저장 공간을 낭비
* 단일값이 아니어서 = 연산자로 비교할 수 없으며 WHERE project LIKE '%홍콩수출건%' 식으로 부분 문자열을 검색해야 함
* 수정도 문제가 됨
🔎 김상형 직원이 담당하는 TV 광고건을 문종민 직원에게 이관하려면 중간에 낀 문자열을 빼 뒤쪽에 추가하는 문자열 조작을 해야 함
➡️ 구분자인 콤마까지 정확히 추출해야 함, UPDATE, DELETE 쿼리로는 이런 조작이 불가능
➡️ 필드값은 더 이상 나눌 수 없는 단일값(Scalar)이어야 관리하기 편함
⬇️
💡 프로젝트 필드를 여러 개 선언하고 각 필드에 프로젝트 하나를 저장하면 어떨까?
|
이름
|
월급
|
주소
|
프로젝트1
|
프로젝트2
|
프로젝트3
|
|
김상형
|
650
|
이천시
|
홍콩 수출건
|
TV광고건
|
매출 분석건
|
|
문종민
|
480
|
대구시
|
경영 혁신안 작성
|
대리점 계획
|
NULL
|
|
권성직
|
625
|
안동시
|
노조 협상건
|
NULL
|
NULL
|
🔎 이 구조는 원자성은 확보했지만 직원 한명당 프로젝트를 3개까지만 담당해야 한다는 제한이 생김
🔎 핵심은 1) 낭비없이 얼마든지 2) 많은 프로젝트를 저장할 수 있어야 함
➡️ 이런 경우 정석이 바로 제 1 정규화이며 다음과 같이 두 개의 테이블로 분할
① 직원 테이블에는 이름, 월급, 주소 등의 직원 정보만 두고 프로젝트 필드는 제거
② 프로젝트 테이블을 따로 만들어 누가 어떤 프로젝트를 담당하는지 기록 (1:다 관계)
🔎 양쪽 테이블은 각자의 기본키가 있음. 직원 테이블은 이름이 PK이며 프로젝트 테이블은 일련번호를 PK로 정의
🔎 프로젝트는 PK 외에 담당자를 가리키는 별도의 필드가 더있음
💡 이처럼 외부 테이블에 대한 참조를 가지는 키를 외래키(Foreign Key)라고 부르며 양 테이블의 연결고리 역할을함
➡️ 직원과 프로젝트는 담당자 외래키로 연결되며 두 테이블 간에 "담당한다"는 관계가 성립
➡️ 담당자 외래키가 없다면 직원과 프로젝트는 완전히 따로 놀며 누가 어떤 프로젝트를 맡고 있는지 알 방법이 없음
👾 레코드는 얼마든지 자유롭게 늘릴 수 있어 담당 프로젝트 개수에 제한 x
👾 신규 프로젝트는 새 레코드를 삽입(INSERT)하고, 완료되었으면 삭제(DELETE)하고, 수정(UPDATE)하면 됨
📌 김상형 직원이 담당하는 TV 광고건을 문종민 직원에게 이관건은 다음 쿼리 하나로 해결
UPDATE tProject SET 담당자='문종민' WHERE 프로젝트='TV 광고건';📌 특정 직원이 맡은 프로젝트를 조사하려면 외래키를 직원명으로 검색
SELECT 프로젝트명 FROM tProject WHERE 담당자= '김상형';
🌙 관계를 맺는 테이블은 누가 업무의 중심인가에 따라 부모, 자식 관계를 형성
📌 업무의 주체가 되는 쪽을 부모 또는 마스터(Master)라고 하며 반대쪽을 자식 또는 디테일(Detail)이라고 함.
📌 위의 예시 경우 직원이 프로젝트를 수행하므로 직원 테이블이 마스터이고 프로젝트 테이블이 디테일
🌙 정규화에 의해 테이블을 분할하면 양쪽의 정보를 조합하여 얻기는 조금 번거로워짐
➡️ 하지만 분할된 테이블에서 관련 정보를 검색하는 서브쿼리(SubQuery)나 조인(Join) 등의 방법으로 해결 가능
2) 제 2 정규화
🌙 제 2 정규화는 일반 필드를 모두 기본키에 종속시키는 것
➡️ 종속된다는 것은 기본키를 알면 나머지 정보도 알 수 있다는 뜻
📌 tCity 테이블에서 기본키인 도시명과 나머지 일반 필드는 종속 관계
📌 일반 필드끼리는 종속 관계가 없으며 상호 독립적. 독립적이라는 것은 각자 따로 논다는 뜻.
➡️ 경기도에 있다고 해서 광역시는 아니며 면적을 안다고 해서 인구를 알 수는 없음
📌 tCity는 모든 필드가 기본키에 종속적이므로 제 2 정규화를 만족
💡 만약 프로젝트 테이블을 다음과 같이 만들었다면?
|
프로젝트 번호
|
프로젝트
|
담당자
|
주소
|
월급
|
|
1
|
홍콩 수출건
|
김상형
|
이천시
|
650
|
|
2
|
TV 광고건
|
김상형
|
이천시
|
650
|
|
3
|
매출 분석건
|
김상형
|
이천시
|
650
|
|
4
|
경영혁신안
|
문종민
|
대구시
|
480
|
|
5
|
대리점 계획
|
문종민
|
대구시
|
480
|
|
6
|
노조 협상건
|
권성직
|
안동시
|
625
|
🔎 프로젝트 번호에 따라 프로젝트 이름과 담당자가 결정되므로 이들은 종속 관계
🔎 담당자의 주소나 월급은 프로젝트와는 직접적인 상관이 없어 이 테이블에 있을 필요가 없음
➡️ 프로젝트 테이블에 담당자만 기록해 두면 나머지 정보는 직원 테이블에서 언제든지 조사할 수 있음
📌 단일 기본키에서는 이런 실수가 금방 눈에 띄지만 복합키에서는 헷갈리는 상황 발생
📌 모든 필드는 복합키 전체에 종속되어야 하며 일부에만 종속되서는 안됨
💡 다음 테이블은 주문 처리 내역을 기록하는 주문 테이블
|
고객명
|
주문번호
|
주문 일자
|
품목
|
개수
|
주소
|
회원등급
|
|
보라돌이
|
1
|
5월 5일
|
하드디스크
|
1
|
춘천
|
우수
|
|
보라돌이
|
2
|
5월 6일
|
노트북
|
2
|
춘천
|
우수
|
|
뚜비
|
1
|
5월 7일
|
팥빙수
|
1
|
이천
|
신입
|
|
뚜비
|
2
|
5월 8일
|
카네이션
|
2
|
이천
|
신입
|
|
뚜비
|
3
|
5월 15일
|
수박
|
5
|
이천
|
신입
|
|
보라돌이
|
3
|
5월 28일
|
분유
|
1
|
춘천
|
우수
|
📌 고객 한명이 여러 건의 주문을 낼 수 있어 고객명과 주문번호를 복합키로 설정하여 어떤 고객이 몇 번째 낸 주문이라는 식으로 구분
📌 주문일자나 품목, 개수는 주문 내역인 고객명+주문번호에 종속적
➡️ 누가 몇 번째 낸 주문인가에 따라 날짜, 품목, 개수가 결정됨
📌 주소와 회원등급은 복합키가 아닌 고객명에 종속되어 있으며 누가 주문을 냈는가에 따라 결정
➡️ 주문번호가 다르다고 해서 주소나 등급이 바뀌는 것은 아님
👾 이 구조에서 주소, 회원등급 필드는 테이블의 기억 장소를 불필요하게 낭비
👾 기본키 전체에 종속되지 않는 필드는 별도의 테이블로 분리하여 제 2 정규화를 함
[ 주문 테이블 ]
|
고객명
|
주문번호
|
주문 일자
|
품목
|
개수
|
|
보라돌이
|
1
|
5월 5일
|
하드디스크
|
1
|
|
보라돌이
|
2
|
5월 6일
|
노트북
|
2
|
|
뚜비
|
1
|
5월 7일
|
팥빙수
|
1
|
|
뚜비
|
2
|
5월 8일
|
카네이션
|
2
|
|
뚜비
|
3
|
5월 15일
|
수박
|
5
|
|
보라돌이
|
3
|
5월 28일
|
분유
|
1
|
[ 고객 테이블 ]
|
고객명
|
주소
|
회원등급
|
|
보라돌이
|
춘천
|
우수
|
|
보라돌이
|
춘천
|
우수
|
|
뚜비
|
이천
|
신입
|
|
뚜비
|
이천
|
신입
|
|
뚜비
|
이천
|
신입
|
|
보라돌이
|
춘천
|
우수
|
👾 주문 내역 테이블에는 복합키에 종속적인 필드만 포함했으며 고객 정보는 별도의 테이블을 구성
👾 한 고객이 수천 번의 주문을 낸다 하더라도 주소, 회원등급 정보는 딱 하나씩만 있으면 됨
3) 제 3 정규화
🌙 제 3 정규화는 기본키가 아닌 일반 필드끼리 서로 종속되지 않도록 하는 것
📌 일반 필드끼리는 독립적이어야 한다는 규칙
💡 다음은 직원 테이블에 직원이 가입한 사내 동호회 관련 필드를 추가한 것
|
직원명
|
월급
|
동호회
|
방번호
|
회원수
|
|
이인재
|
700
|
독서
|
203
|
32
|
|
김영석
|
680
|
독서
|
203
|
32
|
|
김연경
|
430
|
게임
|
306
|
18
|
|
조기봉
|
690
|
독서
|
203
|
32
|
|
차일환
|
320
|
게임
|
306
|
18
|
📌 월급과 가입한 동호회 이름은 기본키인 직원명에 종속적
📌 동호회의 방번호와 회원수는 기본키가 아닌 동호회 필드에 종속적
📌 같은 동호회에 가입한 직원끼리는 방번호, 회원수 등의 중복 정보를 가짐
💡 이 구조의 문제점
① 기억 장소가 낭비
· 만약 동호회 회장 이름, 설립년도, 예산 등의 상세 정보가 추가되고 동호회별로 회원수가 수십명이라면 낭비가 더 심해짐
② 이 테이블의 엔티티는 직원이지 동호회가 아니다.
· 미가입 직원의 동호회 관련 정보는 모두 NULL일 수 밖에 없음
· 회원이 없는 신규 동호회는 표현할 방법이 없음
③ 중복 정보를 변경하기 번거로움
· 예를 들어 독서 동호회가 203호에서 505호로 이사갔다면 이 동호회에 속한 모든 회원의 정보를 다 수정해야 함
④ 정보의 불일치가 발생할 수 있음
· 같은 독서 동호회인데도 이인재 직원의 총 회원수는 32로 기록하고 김영석 직원의 총 회원수는 31로 기록할 수도있음
💡 이런 문제가 발생하는 이유는 분석 단계부터 엔티티 추출에 문제가 있었기 때문
➡️ 문제를 해결하려면 정규화를 통해 테이블을 분할.
📌 월급, 동호회는 기본키인 사원명에 종속적
📌 반면 일반 필드인 월급과 동호회는 아무 상관이 없으며 독립적 따라서 이 둘의 관계는 정규화를 위반 x
📌 동호회와 방번호, 회원수의 관계는 종속적. 동호회를 알면 방번호나 회원수도 알 수 있음
➡️ 일반 필드끼리는 독립적이어야 하는데 그렇지 않아 중복이 발생
➡️ 이럴 경우 직원 테이블과 동호회 테이블을 따로 분리하여 중복을 제거
[직원 테이블]
|
직원명
|
월급
|
동호회
|
|
이인재
|
700
|
독서
|
|
김영석
|
680
|
독서
|
|
김연경
|
430
|
게임
|
|
조기봉
|
690
|
독서
|
|
차일환
|
320
|
게임
|
[동호회 테이블]
|
동호회
|
방번호
|
회원수
|
|
독서
|
203
|
32
|
|
독서
|
203
|
32
|
|
게임
|
306
|
18
|
|
독서
|
203
|
32
|
|
게임
|
306
|
18
|
👾 직원 테이블에는 소속 동호회의 이름만 기록하고 각 동호회에 대한 정보는 별도의 테이블로 분리
👾 직원 테이블의 동호회 필드는 동호회 테이블을 참조하는 외래키
👾 직원 테이블의 월급과 동호회는 기본키인 직원명에 종속적
👾 새로 분할한 동호회 테이블에서 방번호와 회원수는 기본키인 동호회에 종속적
💡 둘 다 3 정규화가 완료 ➡️ 중복으로 인한 모든 문제가 해결
💡 동호회 정보가 딱 하나여서 기억 장소의 낭비가 없고 데이터의 불일치가 발생할 여지 x
💡 가입 회원이 없는 신규 동호회도 표현가능
2. 역정규화
👩🏻💻 정규화 과정을 거치면 중복이 제거되고 구조가 치밀해짐
👩🏻💻 양쪽 정보를 조합하여 읽을 때는 여러 테이블을 읽어야 하므로 속도는 느려짐
👩🏻💻 프로그래밍의 대원칙 중 하나는 속도와 크기는 항상 반비례
👩🏻💻 SQL은 조인(Join)이라는 효율적인 기능을 제공하여 속도 감소가 심하지 않음
💡 정규화를 너무 과하게 하여 이중 삼중의 조인이 필요하면 성능 저하가 발생하는데, 이럴 때는 의도적으로 데이터를 중복시켜 속도 향상을 꾀하기도 한다. 원칙을 반대로 적용하여 속도 향상을 위해 용량상의 이득을 포기하는 것
➡️ 이를 정규화의 반대 개념인 역정규화라고 하는데 구현 단계의 튜닝 과정에서 주로 수행
ex. 제 2 정규화에서 예를 든 주문 정보 테이블에서 배송 주소를 구하려면 주문자를 먼저 찾고 고객 테이블에서 주소를 조사하는 번거로운 과정을 거쳐야 함. 이럴 때 주소 필드를 주문 내역 테이블에 다시 포함시키면 곧바로 배송지 주소를 구할 수 있음
[ 내용 참고 : IT 학원 강의 ]
'Database > MySQL' 카테고리의 다른 글
| [MySQL] JDBC 정의, 로딩, 프로그램 개발 절차 (0) | 2024.02.25 |
|---|---|
| [MySQL] 데이터베이스 모델링 | 참조 무결성 (1) | 2024.02.25 |
| [MySQL] 데이터베이스 모델링 | 진행단계, 분류, 엔티티, 관계 (0) | 2024.02.25 |
| [MySQL] 제약조건 | 일련번호, 시퀀스, AUTO_INCREMENT (0) | 2024.02.24 |
| [MySQL] 제약조건 | 식별자, 기본키, 복합키, 유니크, 체크 (1) | 2024.02.24 |