DB 이상현상, 정규화 과정

[주제]

- DB 이상현상과 정규화 (feat. JOIN)

 

[환경]

- Database RDB(Oracle, MS-SQL, MySQL 등..)

※ RDB(Relational Database) – 관계형 데이터베이스

 

[Preview]

1.     Data를 저장하는 DB는 테이블 형식으로 엑셀처럼 저장하게 됩니다.

2.     Data는 테이블의 구성에 따라 조회, 입력, 수정 등의 성능에 대해 크게 영향을 미칩니다.

3.     이번 시간에는 테이블 구성에 따른 이상현상 발생과 이상현상 해결책인 정규화, 정규화 된 테이블을 연결하는 JOIN에 대해서 공유하고자 합니다.

 

[이상현상(Anomaly)]

1.     우리는 회사 임직원의 정보를 DB 테이블에 저장하고자 합니다.

2.     회사 임직원 정보는 성명, 부서, 직급 등으로 구성하여 저장하고자 합니다.

3.     우리는 테이블에 대해 저장할 때 아래와 같이 간단하게 저장할 수 있습니다.

  

 

4.     위의 구성은 간단하나, DB에서는 이상현상을 유발하게 하는 구성입니다.

5.     이상현상이란 테이블에 데이터를 입력, 수정, 삭제 시 데이터 불일치, 중복, 손실 등이 일어나는 현상을 말합니다.

6.     이상현상에서는 크게 삽입이상, 삭제이상, 갱신이상으로 나눌 수 있습니다.

7.     삽입이상: 테이블에 데이터를 추가 시 불필요한 열을 값들도 삽입해야 하는 현상

      A.     성명에서 박철수만 추가하려고 했으나, 부서 및 직급, 자격증도 추가해야 하는 상황

 

8.     삭제이상: 테이블에 데이터를 삭제 시 다른 열의 값들도 삭제되는 현상
       A.     성명에서 김철수만 삭제하려고 하였으나, 인사팀과 사원 직급, 자격증까지 삭제, 그로 인해 회사에 인사팀과 사원 직급 등이 있는 것을 확인 할 수 없음

 

 

9.     갱신이상: 중복 저장되어 있는 값 중 하나만 갱신하고 나머지는 갱신하지 않아 데이터 불일치
       A.     PMP를 가진 박영수를 프로젝트팀으로 발령하였으나, 정보관리기술사 행의 데이터는 갱신되지 않음

 

 

10.  여러가지 정보를 한 테이블에 표현하려고 한 것이 문제이며, 각 요소 간의 연관되어 있는 것 들로만 구성(성명-부서, 성명-직급, 성명-자격증, 부서-직급-자격증은 연관 불필요) 해야 합니다. 그것을 정규화라고 합니다.  

 

 

[정규화(Normalization)]

1.     앞서 말한 것처럼 정규화는 이상현상을 제거하는 과정입니다. 여러가지 정보(릴레이션)을 분해함으로써 하나의 릴레이션(관계)은 하나의 종속성을 갖게 하는 것을 목표로 합니다.

2.     종속성이란 예를 들어 사번 à 이름과 매칭되고, (이름, 과목) à 성적에 매칭할 수 있습니다. 그렇다면 이름은 사번에 종속되고, 성적은 (이름, 과목)에 종속된다고 합니다.

3.     이렇듯, 각 속성들 간의 종속성을 분석해 하나의 종속성이 하나의 관계로 표현하도록 분해하는 과정을 정규화라 하며 정규화가 진행될수록 이상현상은 감소하게 됩니다.

4.     정규화에는 여러 단계가 있으며, 과정에 대해 알아보도록 하겠습니다.
       A.     1차 정규화

                    i.         각 컬럼에는 하나의 속성(원자값)만 가진다.

            

            

                   ii.         과목 컬럼에서 한 컬럼에 (운영체제, DB), (C, C++) 이렇게 들어가 있습니다.

                  iii.         하나의 컬럼은 하나의 값만 가져야 합니다.

            

       B.      2차 정규화

                    i.         1차 정규형를 만족한다, 모든 컬럼은 기본키 중 특정 컬럼만 종속되면 안된다.

            

            

                   ii.         기본키가 (학생번호, 과목)인 상태에서 성적은 두 기본키가 있어야 하지만, 지도교수는 과목만 있어도 존재하기 때문에 학생번호가 필요 없습니다.

                  iii.         이렇듯 기본키가 복합 키인 상태에서 하나의 키만 필요한 연결이 있다면 부분 종속이 되기 때문에 2차 정규화를 진행해야 합니다.

                  iv.         만약 컬럼이 지도교수가 아닌 출석률이라면 2차 정규화를 만족했을 것입니다.

                   v.         기본키에 대해 컬럼은 완전 종속이 되어야 합니다.

            

          
       C.      3차 정규화

                    i.         2차 정규형를 만족한다, 이행 종속성을 없애야 한다.

            

            

                   ii.         고객 ID에는 등급 매겨지고, 등급에 따라 일정 할인율이 제공됩니다. 그러나 고객ID와 할인율은 직접적 관련이 없는데, 이행 종속으로 인해 고객이 얼만큼 할인 받는 지 알 수 있게 됩니다.

                  iii.         즉, 이행 종속이란 고객IDà등급이고, 등급à할인율이면 고객IDà할인율에 종속되어지는 것을 말합니다.

                  iv.         이행종속은 제거해야 합니다.

            

           
       D.     BCNF (Boyce-Codd Normal Form)

                    i.         3차 정규형을 만족한다. 기본키가 모든 컬럼을 결정해야한다.

            

            

                   ii.         이전 2차 정규화에서는 과목당 한 명의 지도교수 이기에, 과목à지도교수가 가능했지만 같은 과목에 여러 지도교수로 인해, (학생번호, 과목)을 지정해야 지도교수를 알 수 있습니다. [(학생번호, 과목) à 지도교수]

                  iii.         컬럼은 원자값(1차)이며, 기본키가 부분 종속되는게 없고(2차), 이행 종속이 없으므로(3차) 3차 정규형까지 만족하고 있습니다.

                  iv.         그러나 기본키가 아닌 지도교수가 지도교수à과목으로 종속될 수 있습니다.

                   v.         이처럼 기본키가 아닌 컬럼이 기본키 행세(결정자)를 해서는 안됩니다. 

                  vi.         기본키 행세를 하는 컬럼을 아래와 같이 없앨 수 있습니다. (학생번호, 과목) à 지도교수도 가능하지만, 학생번호à지도교수 구성하고 JOIN으로 과목을 연결할 수 있기 때문에 구성변경이 가능합니다.  

            

          

         
        E.      4차 정규형 이상

                    i.         4차 정규형(다치 종속), 5차 정규형(조인 종속)도 있지만 보통 DBMS 운영 시 BCNF까지만 운영하는 경우가 대부분입니다.

                   ii.         그 이유는 정규화가 진행될수록 발생하는 단점들이 있기 때문입니다.

 

5.     정규화의 장단점
       A.     장점 – 이상현상 제거, 테이블 구조 변경 용이, 연동된 프로그램 영향 최소화
       B.      단점 – JOIN이 많아지므로 조회 성능이 느려짐(DB의 성능은 조회가 90% 이상 차지)
       C.      정규화가 진행될수록 분할된 테이블은 많아지며, 그로 인해 테이블 조회하기 위해서는 분할된 테이블을 다시 연결해서 조회함으로써 성능이 느리게 됩니다.
       D.     정규화로 인해 분해된 테이블에서 필요한 데이터를 조회하기 위해 연결하는 기능을 JOIN이라고 합니다.

 

 

(Feat. JOIN)

1.     JOIN에 대해서는 간단히 설명 드리도록 하겠습니다.

2.     아래의 두 개의 테이블에서 각 학생번호들이 듣는 과목을 검색하려면 어떻게 해야 할까요? 각 테이블의 지도교수 컬럼에서 값은 값인 경우를 연결하면 됩니다.

 

 

3.     이처럼 두 개 이상의 릴레이션 테이블에 대해 연관된 컬럼을 결합하여, 새로운 값을 조회하는 방법을 JOIN이라고 합니다.

4.     A.TABLE JOIN B.TABLE ON A.지도교수 = B.지도교수, 즉 A의 지도교수와 B.의 지도교수에 값을 연결한다는 뜻입니다.

5.     그럼 연결하기 위한 값 검색은 아래와 같습니다.
       A.     A.지도교수의 김자바 값을 B.지도교수의 김자바~조씨샵까지 비교
       B.      A.지도교수의 박플플 값을 B.지도교수의 김자바~조씨샵까지 비교
       C.      A.지도교수의 오자바 값을 B.지도교수의 김자바~조씨샵까지 비교
       D.     A.지도교수의 값을 다 확인할 때까지 반복

6.     모든 값을 검색하기 위해 반복이 되며 A.지도교수 * B.지도교수 개수만큼 실행됩니다.

7.     여기서 필요한 것이 이전 칼럼에 공유했던 인덱스이며, 인덱스 설정 시 빠른 속도를 보이게 됩니다.

8.     위의 방식은 JOIN 중 Nested Loops Join 방식이며, 그 외에도 인덱스가 없을 시 JOIN 하는 컬럼을 Sort(정렬)하는 Sort merge Join, 해시 함수를 사용하는 Hash Join 등이 있습니다.

9.     정규화로 인한 성능 문제를 해결하기 위해 분해된 것을 다시 합치는 반정규화(역정규화) 방식도 있습니다.

10.  운영 환경 및 성능을 분석하고 적절히 적용하는 것이 필요합니다.