데이터베이스 정규화

제 1 정규화

반복되는 속성들을 다른 개체로 나누어 분리함. 즉, 또다른 개체로 판단할 수 있는 속성들을 

분리하고 각 개체의 속성들의 유일한 식별자를 가지게 함.

부모테이블의 식별자는 반드시 자식테이블의 외부키(Foreign Key)로 전이됨.

제 2 정규화

모든 속성은 식별자에 직접적으로 의존적이어야 하며 이에 해당되지 않는 속성을 분리함.

제 3 정규화

식별자 이외의 속성은 식별자가 아닌 다른 속성에 종속적이지 않아야 함.

식별자 이외의 속성에 종속적인 경우에는 분리함.

제 4 정규화

의존적인 다 대 다 관계를 제거해서 1 대 다 관계로 만들어주는 것.

제 5 정규화

관계 R가 제4정규형이고 관계 중에서 성립되는 모든 결합 종속성(join dependency)이 

그 관계의 후보 키만으로 논리적으로 함축되어 있는 경우에 그 관계를 제5정규형이라고 함.

역정규화

관리적인 측면과 성능적인 측면을 고려하여 테이블의 설계를 재구성하는 것.

정규화 단계에서 추출되었던 실체가 제거되기도 하며 분석 단계에서 언급되지 않았던 실체가

새롭게 추출될 수도 있게 된다. 이렇게 정규화 단계의 분석 결과가 무시되고 시스템과 DBMS의

장점과 주요 특징을 기반으로 설계 구조가 바뀌는 단계를 역정규화(De-Normalizaion)이라 한다. 

추가설명)

정규화의 필요성

 

1. 테이블의 삽입, 삭제, 갱신등의 과정에서 발생하는 이상현상을 방지하기 위함

2. 중복성을 최소화

3. 정보의 일관성 보장

 

정보의 일관성

하나의 정보가 처음부터 끝까지 같은 성질

 

※ DB를 정규화 시킨다는건 효율적으로 테이블을 분리하는 것이며 JOIN의 사용이

많아지는것이다.

 

※ 컬럼A가 완전하게 컬럼B를 결정할수 있다면 컬럼B는 컬럼A값에 함수적 종속관계

에 있다.

 

ex

A(과목코드) B(과목명)

A1               국어 

A2               수학

A3               영어

 

정규화의 목적

 

1. 어떠한 릴레이션이라도 데이터베이스 내에서 표현할수 있도록 만듬

2. 보다 간단한 관계연산에 기초하여 검색 알고리즘을 효과적으로 만들수 있다.

3. 릴레이션에서 비정상적인 삽입, 갱신, 삭제 등의 이상이 발생하지 않기 위해

4. 새로운 형태의 데이터가 삽입될때 릴레이션을 재구성할 필요성을 줄인다.

 

제1정규형(1NF)

 

0. 반복되는 그룹 속성을 제거한뒤 기본테이블의 기본키를 추가해 새로운 테이블을

생성하고 기존의 테이블과 1 : n의 관계를 만든다.

1. 특정 컬럼의 값이 다중값을 가지는경우 제1정규형 대상이 되며 다중값을 가지는

2. 컬럼을 따로 릴레이션으로 분리하여 최대한 중복값을 없앤다.

3. 여러 값을 가진 컬럼이 존재할 수 없다. 즉 반복되는 그룹이 존재해서는 안 된다.

4. 각 행과 열에는 하나의 값만이 올수 있다.

 

제1정규형 예제

 

예제1

고객번호        고객명 취미

1                    노무현 영화

1                    노무현 여행

2                    이화숙 등산

이런 테이블이 있다면 제1정규형으로 수정한다면

고객번호(P.K) 고객명

1                    노무현

2                    이화숙

하고 또하나

고객번호(P.K) 일련번호(P.K) 취미

1                    1                    영화

1                    2                    여행

2                    1                    등산

 

로 나누어 집니다.

 

※ 유일성을 위하여 일련번호 기본키를 추가함

 

예제2

학번,과정코드,평가코드,과정명,기간,시간,수강료,교재n 테이블존재

"반복되는 그룹 속성을 제거한뒤 기본테이블의 기본키를 추가해 새로운 테이블을

생성하고 기존의 테이블과 1 : n의 관계를 만든다."

 

조건

1. 하나의 학번은 여러개의 과정을 들을수 있다.

2. 과정명,기간,시간,수강료,교재n 는 과정코드에 종속적이다.

3. 하나의 과정에 교재는 여러개이다.

 

반복되는 교재속성을 따로분리를 시켜서

"학번(PK), 과정코드(PK), 평가코드, 기간, 시간, 수강료"의 수강정보테이블을 하나 만들수

있습니다.

"과정코드(PK),교재번호(PK),교재" 이렇게 교재정보테이블을 만들수 있습니다.

 

제2정규형(2NF)

 

어떤 릴레이션이 제1정규형이고 키가 아닌 컬럼은 기본 키 전체에 의존적이여야 한다.

기본키의 일부분에 의존적이어서는 안 된다.

제2정규형 예제

 

예제1

사번(p.k) 프로젝트번호(p.k) 부서 프로젝트역할 고과율

1 a 전산 팀장 A

1 b 전산 조원 C

1 c 전산 부팀장 B

2 c 경리 팀장 A

3 c 기획 팀장 A

이 테이블의 기본키는 사번과 프로젝트번호 인데 부서 컬럼은 사번에만 의존적이다.

이것은 2정규화를 거치면

 

사번(p.k) 프로젝트번호(p.k) 프로젝트역할 고과율

1 a 팀장 A

1 b 조원 C

1 c 부팀장 B

2 c 팀장 A

3 c 팀장 A

요렇게 하고 부서 테이블을 다시 따로 나눈다.

 

사번 부서

1 전산

2 경리

3 기획

 

예제2

"복합키에 전체적으로 의존하지 않는 속성들을 제거한다."

 

1정규화에서 나온 수강정보테이블을 보자면 "학번,과정코드,평가코드,기간,시간,수강료"가

있습니다.

여기에서 위에서 말한 "하나의 학번은 여러개의 과정을 들을수 있다." 를 보면 key는 학번과

과정코드가 됩니다.

 

여기에서 과정코드에 의존적인 컬럼을 살펴보자면 기간,시간,수강료가 될수 있습니다.

2정규화에 의해서 분리한다면 학번,과정코드,평가코드의 수강정보테이블이 하나 나옵니다.

그리고 과정코드,기간,시간,수강료의 과정정보테이블이 하나 나옵니다.

그리고 1정규화에 나온 교재정보테이블이 있죠

 

제3정규형(3NF)

 

오떤 릴레이션이 제2정규형이고 키가아닌 컬럼은, 다른 키가 아닌 컬럼에 의존적이어서는 안

된다.

 

제3정규형 예제

 

사번(p.k) 프로젝트번호(p.k) 프로젝트역할 고과율

1 a 팀장 A

1 b 조원 C

1 c 부팀장 B

2 c 팀장 A

3 c 팀장 A

요 테이블을 다시 보면 고과율은 프로젝트 역할에 의존적인걸 알수 있습니다.

이것을 또 3정규화 거치면

사번(p.k) 프로젝트번호(p.k) 프로젝트역할

1 a 팀장

1 b 조원 

1 c 부팀장 

2 c 팀장 

3 c 팀장 

프로젝트역할 고과율

팀장 A

조원 C

부팀장 B

 

제2정규형와 제3정규형의 관계

 

2NF ->(프로젝션) 3NF

2NF <-(조인) 3NF

 

제4정규화(4NF)

 

2정규화 된 테이블은 다대다 관계를 가질수 없다.

 

제5정규화(4NF)

 

실무에서 많이 사용하지 않는다. 이론에서만 다룬다.

 

역정규화(중복 데이타가 최대한 생길수 있는 설계)

- 역정규화 테이블 구조에서는 한테이블에서 데이타를 조회한다.

- 역정규화로 테이블이 구성되면 JOIN 연산자를 많이 사용하지 않게 된다.

 

CREATE TABLE MEMBER

(

 ID VARCHAR2(10),

 NAME VARCHAR2(10), (중복데이타발생)

 PASS VARCHAR2(10) (중복데이타발생)

);

 

CREATE TABLE BOARD

(

 ID VARCHAR(10), (중복데이타발생)

 NAME VARCHAR2(10), (중복데이타발생)

 CONTENT VARCHAR2(10) (중복데이타발생)

);

 

사용자명으로 검색방법

SELECT * FROM BOARD WHERE NAME LIKE '이준식%';

 

정규화(종복 데이타를 최대한 없애는 설계)

- 정규화 테이블 구조에서는 여러테이블에서 서브쿼리, 조인으로 데이타를 조회한다.

- 정규화로 테이블이 구성되면 JOIN 연산자를 많이 사용하게 된다.

 

CREATE TABLE MEMBER

(

 ID VARCHAR2(10),

 NAME VARCHAR2(10), (중복데이타발생)

 PASS VARCHAR2(10) (중복데이타발생)

);

 

CREATE TABLE BOARD

(

 ID VARCHAR(10), (중복데이타발생)

 CONTENT VARCHAR2(10) (중복데이타발생)

);

 

사용자명으로 검색방법

SELECT * FROM BOARD WHERE ID IN( SELECT ID FROM MEMBER WHERE NAME LIKE '이준

식%' );

SELECT * FROM BOARD A INNER JOIN MEMBER B ON A.ID = B.ID WHERE B.NAME LIKE '이

준식%';

SELECT * FROM BOARD A INNER JOIN MEMBER B ON A.ID = B.ID AND B.NAME LIKE '이준

식%';

 

출처 : www.phpschool.com

 

성적 관리 원장

 

학번 이름  선택과목 담당교수

0001 이준  자료구조 노일순1

0002 이영               

0003 이민  전산영어 노일순2

 

위의 테이블 설계의 문제점은 학번이 0002 인 레코드의 선택과목, 담당교수의 값이 널값이라

는것이다. 레코드가 많아지면 공간의 낭비가 크게 발생하게 된다. 위의 테이블을 아래와 같이

정규화(중복제거,빈값제외)한다.

 

학생 원장

 

학번 이름 

0001 이준

0002 이영               

0003 이민

 

선택과목원장

 

학번 선택과목 담당교수

0001 자료구조 노일순1

0003 전산영어 노일순2

 

위와 같이 정규화(중복제거,빈값제외) 과정을 통하면 공간에 대한 낭비가 없어진다.

위에서 학생원장은 부모테이블이며 선택과목원장은 자식테이블이다.

학생원장의 학번은 PK 이며 선택과목원장의 학번은 FK 이다.

 

선택과목원장의 학번값에는 학생원장의 학번값에 꼭 포함되어야 하며 널값도 포함될수 없

다.

만약 이를 어겼을경우에는 참조무결성에 위배된다고 말하며 잘못된 구조를 가진것이다.

[출처] 정규화|작성자 천상잎새

[출처] [데이터베이스] 정규화에 관한 간단한 정리|작성자 엉클퐝

규화의 필요성

 

1. 테이블의 삽입, 삭제, 갱신등의 과정에서 발생하는 이상현상을 방지하기 위함

2. 중복성을 최소화

3. 정보의 일관성 보장

 

정보의 일관성

하나의 정보가 처음부터 끝까지 같은 성질

 

※ DB를 정규화 시킨다는건 효율적으로 테이블을 분리하는 것이며 JOIN의 사용이

많아지는것이다.

 

※ 컬럼A가 완전하게 컬럼B를 결정할수 있다면 컬럼B는 컬럼A값에 함수적 종속관계

에 있다.

 

ex

A(과목코드) B(과목명)

A1               국어 

A2               수학

A3               영어

 

정규화의 목적

 

1. 어떠한 릴레이션이라도 데이터베이스 내에서 표현할수 있도록 만듬

2. 보다 간단한 관계연산에 기초하여 검색 알고리즘을 효과적으로 만들수 있다.

3. 릴레이션에서 비정상적인 삽입, 갱신, 삭제 등의 이상이 발생하지 않기 위해

4. 새로운 형태의 데이터가 삽입될때 릴레이션을 재구성할 필요성을 줄인다.

 

제1정규형(1NF)

 

0. 반복되는 그룹 속성을 제거한뒤 기본테이블의 기본키를 추가해 새로운 테이블을

생성하고 기존의 테이블과 1 : n의 관계를 만든다.

1. 특정 컬럼의 값이 다중값을 가지는경우 제1정규형 대상이 되며 다중값을 가지는

2. 컬럼을 따로 릴레이션으로 분리하여 최대한 중복값을 없앤다.

3. 여러 값을 가진 컬럼이 존재할 수 없다. 즉 반복되는 그룹이 존재해서는 안 된다.

4. 각 행과 열에는 하나의 값만이 올수 있다.

 

제1정규형 예제

 

예제1

고객번호        고객명 취미

1                    노무현 영화

1                    노무현 여행

2                    이화숙 등산

이런 테이블이 있다면 제1정규형으로 수정한다면

고객번호(P.K) 고객명

1                    노무현

2                    이화숙

하고 또하나

고객번호(P.K) 일련번호(P.K) 취미

1                    1                    영화

1                    2                    여행

2                    1                    등산

 

로 나누어 집니다.

 

※ 유일성을 위하여 일련번호 기본키를 추가함

 

예제2

학번,과정코드,평가코드,과정명,기간,시간,수강료,교재n 테이블존재

"반복되는 그룹 속성을 제거한뒤 기본테이블의 기본키를 추가해 새로운 테이블을

생성하고 기존의 테이블과 1 : n의 관계를 만든다."

 

조건

1. 하나의 학번은 여러개의 과정을 들을수 있다.

2. 과정명,기간,시간,수강료,교재n 는 과정코드에 종속적이다.

3. 하나의 과정에 교재는 여러개이다.

 

반복되는 교재속성을 따로분리를 시켜서

"학번(PK), 과정코드(PK), 평가코드, 기간, 시간, 수강료"의 수강정보테이블을 하나 만들수

있습니다.

"과정코드(PK),교재번호(PK),교재" 이렇게 교재정보테이블을 만들수 있습니다.

 

제2정규형(2NF)

 

어떤 릴레이션이 제1정규형이고 키가 아닌 컬럼은 기본 키 전체에 의존적이여야 한다.

기본키의 일부분에 의존적이어서는 안 된다.

제2정규형 예제

 

예제1

사번(p.k) 프로젝트번호(p.k) 부서 프로젝트역할 고과율

1 a 전산 팀장 A

1 b 전산 조원 C

1 c 전산 부팀장 B

2 c 경리 팀장 A

3 c 기획 팀장 A

이 테이블의 기본키는 사번과 프로젝트번호 인데 부서 컬럼은 사번에만 의존적이다.

이것은 2정규화를 거치면

 

사번(p.k) 프로젝트번호(p.k) 프로젝트역할 고과율

1 a 팀장 A

1 b 조원 C

1 c 부팀장 B

2 c 팀장 A

3 c 팀장 A

요렇게 하고 부서 테이블을 다시 따로 나눈다.

 

사번 부서

1 전산

2 경리

3 기획

 

예제2

"복합키에 전체적으로 의존하지 않는 속성들을 제거한다."

 

1정규화에서 나온 수강정보테이블을 보자면 "학번,과정코드,평가코드,기간,시간,수강료"가

있습니다.

여기에서 위에서 말한 "하나의 학번은 여러개의 과정을 들을수 있다." 를 보면 key는 학번과

과정코드가 됩니다.

 

여기에서 과정코드에 의존적인 컬럼을 살펴보자면 기간,시간,수강료가 될수 있습니다.

2정규화에 의해서 분리한다면 학번,과정코드,평가코드의 수강정보테이블이 하나 나옵니다.

그리고 과정코드,기간,시간,수강료의 과정정보테이블이 하나 나옵니다.

그리고 1정규화에 나온 교재정보테이블이 있죠

 

제3정규형(3NF)

 

오떤 릴레이션이 제2정규형이고 키가아닌 컬럼은, 다른 키가 아닌 컬럼에 의존적이어서는 안

된다.

 

제3정규형 예제

 

사번(p.k) 프로젝트번호(p.k) 프로젝트역할 고과율

1 a 팀장 A

1 b 조원 C

1 c 부팀장 B

2 c 팀장 A

3 c 팀장 A

요 테이블을 다시 보면 고과율은 프로젝트 역할에 의존적인걸 알수 있습니다.

이것을 또 3정규화 거치면

사번(p.k) 프로젝트번호(p.k) 프로젝트역할

1 a 팀장

1 b 조원 

1 c 부팀장 

2 c 팀장 

3 c 팀장 

프로젝트역할 고과율

팀장 A

조원 C

부팀장 B

 

제2정규형와 제3정규형의 관계

 

2NF ->(프로젝션) 3NF

2NF <-(조인) 3NF

 

제4정규화(4NF)

 

2정규화 된 테이블은 다대다 관계를 가질수 없다.

 

제5정규화(4NF)

 

실무에서 많이 사용하지 않는다. 이론에서만 다룬다.

 

역정규화(중복 데이타가 최대한 생길수 있는 설계)

- 역정규화 테이블 구조에서는 한테이블에서 데이타를 조회한다.

- 역정규화로 테이블이 구성되면 JOIN 연산자를 많이 사용하지 않게 된다.

 

CREATE TABLE MEMBER

(

 ID VARCHAR2(10),

 NAME VARCHAR2(10), (중복데이타발생)

 PASS VARCHAR2(10) (중복데이타발생)

);

 

CREATE TABLE BOARD

(

 ID VARCHAR(10), (중복데이타발생)

 NAME VARCHAR2(10), (중복데이타발생)

 CONTENT VARCHAR2(10) (중복데이타발생)

);

 

사용자명으로 검색방법

SELECT * FROM BOARD WHERE NAME LIKE '이준식%';

 

정규화(종복 데이타를 최대한 없애는 설계)

- 정규화 테이블 구조에서는 여러테이블에서 서브쿼리, 조인으로 데이타를 조회한다.

- 정규화로 테이블이 구성되면 JOIN 연산자를 많이 사용하게 된다.

 

CREATE TABLE MEMBER

(

 ID VARCHAR2(10),

 NAME VARCHAR2(10), (중복데이타발생)

 PASS VARCHAR2(10) (중복데이타발생)

);

 

CREATE TABLE BOARD

(

 ID VARCHAR(10), (중복데이타발생)

 CONTENT VARCHAR2(10) (중복데이타발생)

);

 

사용자명으로 검색방법

SELECT * FROM BOARD WHERE ID IN( SELECT ID FROM MEMBER WHERE NAME LIKE '이준

식%' );

SELECT * FROM BOARD A INNER JOIN MEMBER B ON A.ID = B.ID WHERE B.NAME LIKE '이

준식%';

SELECT * FROM BOARD A INNER JOIN MEMBER B ON A.ID = B.ID AND B.NAME LIKE '이준

식%';

 

출처 : www.phpschool.com

 

성적 관리 원장

 

학번 이름  선택과목 담당교수

0001 이준  자료구조 노일순1

0002 이영               

0003 이민  전산영어 노일순2

 

위의 테이블 설계의 문제점은 학번이 0002 인 레코드의 선택과목, 담당교수의 값이 널값이라

는것이다. 레코드가 많아지면 공간의 낭비가 크게 발생하게 된다. 위의 테이블을 아래와 같이

정규화(중복제거,빈값제외)한다.

 

학생 원장

 

학번 이름 

0001 이준

0002 이영               

0003 이민

 

선택과목원장

 

학번 선택과목 담당교수

0001 자료구조 노일순1

0003 전산영어 노일순2

 

위와 같이 정규화(중복제거,빈값제외) 과정을 통하면 공간에 대한 낭비가 없어진다.

위에서 학생원장은 부모테이블이며 선택과목원장은 자식테이블이다.

학생원장의 학번은 PK 이며 선택과목원장의 학번은 FK 이다.

 

선택과목원장의 학번값에는 학생원장의 학번값에 꼭 포함되어야 하며 널값도 포함될수 없

다.

만약 이를 어겼을경우에는 참조무결성에 위배된다고 말하며 잘못된 구조를 가진것이다.

[출처] 정규화|작성자 천상잎새

출처] [Oracle] 데이터 모델링|작성자 Premium