서적 "SQL 기초2" 주에서 발최

서적 "SQL 기초2" 주에서 발최

CHAPTER 3. 주위의 데이터를 테이블로 만든다

3-1. 테이블 설계란 무엇인가?

KEYWORD

       * 데이터 정규화

   * 비정규화 데이터

  * 제1정규화

  * 제2정규화

  * 제3정규화

  테이블 설계란 우리들 주변에 있는 데이터를 어떠한 형식으로 데이터베이스로 보관하면 좋을까를 생각하는 것입니다. 이 설계가 잘 되고 안 됨에 따라, 나중에 데이터의 수정이 간단하게 될지, 복잡하게 될지가 결정됩니다. ‘이렇게 설계를 하면 나중에 수정할 때 이러한 장점이나 단점이 있다’라는 점을 잘 생각하면서 배워 나가도록 합시다.

  테이블 설계를 할 때에는 정규화라는 개념을 알아두어야 합니다. 어렵게 생각할 필요는 없습니다. 우리들 자신이 가지고 있는 데이터를 데이터베이스로 원활하게 운용할 수 있도록 잘게 부수는 것이라고 생각하면 됩니다. 여기에서는 신문을 예로 들어 봅니다. 기사를 쓰는 쪽(기자)를 인간으로, 기사를 읽는 쪽(독자)를 데이터베이스로 바꾸어 생각해 봅니다.

  신문의 1페이지 전면이 모두 한자로 쓰여 있다고 상상해 봅니다. 전달하고자 하는 것들을 모두 적었다 하더라도, ‘독자로서는 이해하기 어려운 것’이 자명합니다. 좀 더 알기 쉽게 하려면 한글을 섞어서 쓰도록 합니다. 또 더욱 효과적인 방법으로는 문장 부호를 추가하거나 지면을 여러 틀로 나누어서 각각의 틀 안에 기사를 쓰면 좋을 것입니다. 단락을 나누어서 쓰는 것도 이해하기 쉬워질 것입니다. 이렇게 하면 지면 전체를 한자로 쓴 것에 비하여 훨씬 알기쉬운 신문이 될 것입니다.

  이와 같이 인간과 데이터베이스 사이에서 진행해 나가는 것을 정규화라고 부릅니다. 앞의 예에서는 ‘전면을 한자로 쓴다’ → ‘한글을 섞어 쓴다’ → ‘문장 부호를 추가한다’ → ‘지면을 분할한다’ → ‘단락을 나눈다’라는 형태로 한자 데이터를 편집해 나감으로써 좀 더 이해하기 쉬운 데이터 구조로 만드는 방법을 설명하였습니다. 이 내용을 인간이 취급하는 데이터와 데이터베이스의 관계에도 적용시켜 봅니다. 인간이 취급하는 데이터를 ‘비정규화 데이터’라고 부릅니다. 여기에서부터 진행하는 테이블 설계의 절차는 다음과 같습니다.

             비정규화 데이터 → 제1정규화 → 제2정규화 → 제3정규화

  실제로는 제4정규화, 제5정규화 등도 있지만 일반적으로는 제3정규화까지 진행합니다. 신문의 예에서는, ‘한글을 섞어서 쓰고 문장 부호가 있으면 이해하기 쉽다’라는 것이 제3정규화입니다. 이처럼 사람들이 이해하기 쉬운 데이터 구조를 RDBMS가 이해하기 쉬운(관리하기 쉬운) 구조로 바꾸어 나가기 위해서 정규화가 필요합니다. 각각의 정규형에 관해서 살펴보도록 합니다.

3-2 테이블 작성 방법과 비정규형

KEYWORD

  * 분석

  * 비정규형

  다음의 예로 테이블을 작성해 봅니다. 여기에서는 학교 성적 관리에 관해서 생각해 보기로 합니다. 다음과 같은 시험 성적표가 있습니다.

  이처럼 개인 정보를 데이터베이스를 사용하여 관리한다고 가정합니다.

  전교생의 시험 성적표를 각 교사가 기록하고 이것을 학교 컴퓨터에 입력합니다. 물론 이 성적표를 바로 테이블로 만들 수도 있으나, 데이터베이스에는 바람직한 방법이 아닙니다. 어떠한 형식이 적합할지를 생각해 봅니다. 테이블을 설계하기 전에 시트를 잘 관찰하여 테이블 구조를 확정해야 하는데 이 작업을 분석이라고 부릅니다. 데이터의 분석 방법은 여러 가지가 있겠지만 여기에서는 이에 관해서는 언급을 하지 않습니다. 그러나 어떠한 분석 방법을 사용하든지, 테이블 설계의 첫 걸음은 어떠한 열이 있으면 이것에 데이터를 관리할 수 있을까를 고려하는 것입니다. 위의 표를 보고 어떠한 열이 있으면 좋을까를 생각해 봅니다. 표를 보면 다음과 같이 데이터를 분석할 수 있습니다.

  시험 성적표      →  테이블 이름

  최문석             →  이름

  2                    →  학년

  A                    →  학급

  본관2층            →  교실

  == 시험 결과 ==  →  테이블 구분

  국어                 →  과목

  80                    →  점수

  이경욱              →  교사

  4                     →  과목수

  72.5                 →  평균점수

  이 정보들을 자세히 보길 바랍니다. 동시에 테이블을 머리 속에 그려 봅니다. 어떠한 열이 필요할까요? 이것을 테이블로 만든다고 생각할 때 다음과 같은 열이 필요합니다.

  1. 테이블 이름

  2. 이름

  3. 학급

  4. 교실

  5. 테이블 구분

  6. 과목

  7. 점수

  8. 교사

  9. 과목수

 10. 평균점수

  이들 열을 사용하여, 바로 CREATE TABLE문을 사용해도 되지만, 여기에서 다시 한번 생각해 봅니다. 위에 들고 있는 열 후보 중에서 필요 없는 열은 없을까요?

  먼저 눈에 띄는 것은 ‘1. 테이블 이름’과 ‘5. 테이블 구분’입니다. 이 항목은 인쇄상 보기 편하기 하기 위한 것이므로 데이터로는 아무 의미가 없습니다.

  그리고 잘 생각해 보면, 필요 없는 열이 또 있습니다. 다른 값에 의해 계산할 수 있는 항목입니다. SELECT문이 계산이 가능하다는 것은 이미 아는 사실입니다. (예: SELECT 1+2 FROM DUAL;) 그렇기 때문에 계산에 의해 산출할 수 있는 것은 굳이 데이터로 입력할 필요가 없습니다. 오히려 같은 데이터를 여러 개 갖게 되므로 데이터가 중복됩니다. 예를 들어, 과목수는 과목을 세어보면 알 수 있고 평균 점수는 각 점수를 더해서 과목수로 나누면 됩니다. 이처럼 계산할 수 있는 데이터는 필요없는 데이터입니다. RDBMS 설계에서 중요한 것은 중복 데이터를 가지지 않고 데이터의 모순 없이 쉽게 변경하는 것입니다. 따라서, 다른 데이터에서 계산하여 얻을 수 있는 값은 테이블 설계 단계에서 제외합니다. 여기에서는 ‘9.과목수’와 ‘10.평균점수’가 해당됩니다. 이러한 관점에서 필요한 열을 다시 추려 보면 다음과 같습니다.

  1. 이름

  2. 학급

  3. 교실

  4. 과목

  5. 점수

  6. 교사

  열이 반 정도로 줄었습니다. 그러나 결코 정보가 빠져 있지는 않습니다. 이들 데이터 정도만 알면 본래의 표 데이터는 복원될 수 있습니다. 테이블 설계에서 중요한 것은 반드시 테이블에 보관해야 하는 데이터와 다른 데이터에서 도출 할 수 있는 데이터를 가려내는 것입니다. 표에 씌여 있는 데이터는 하나의 표현에 지나지 않으므로, 그대로 모두 테이블로 가져오면 안 된다는 점을 주의하길 바랍니다.

  그러면 이제 가려낸 열을 사용하여 다음과 같은 테이블을 작성해 봅니다.

  한 명의 학생에 대한 정보는 이 정도입니다. 다른 학생의 정보도 입력해 봅니다.

? 테이블1: 성적(비정규형)

  이렇게 작성된 테이블을 비정규형 테이블이라고 부릅니다. 비정규형 테이블은 인간에게는 보기 편하지만 효율성 있는 데이터 관리를 위해서는 적합하지 않습니다. 예를 들어 2번째 행의 데이터는 어느 학생의 성적인가를 보여주지 않습니다. 그리고 앞서 테이블에는 키 열이 존재한다고 설명한 것을 기억할 것입니다. 키 열에는 반드시 값이 있어야 합니다. 앞의 테이블은 학생의 성적을 보관하는 테이블이므로 어느 한 행을 특정 짓기 위해서는 학생 이름이 필요합니다. 그러나 학생 이름 열에 값이 들어가 있지 않는 행이 여러 개 존재하고 있습니다. 이런 이유로 이 테이블을 이대로는 RDBMS 안의 테이블에 보관 할 수 없습니다. 여기서 비정규형 테이블을 제1정규형으로 변환해 봅니다.

3-3 제1정규형

KEYWORD

  * 제1정규형

  제1정규형으로 변환하는 작업은 테이블 안에서 반복되는 항목을 배재하는 것입니다. 따라서 테이블 안에 반복되는 항목이 없는 테이블을 제1정규형 테이블이라고 합니다.

  그러면 비정규형 테이블을 제1정규형 테이블로 변환해 봅니다. ‘테이블1’에서 반복하는 항목은 무엇인가? ‘테이블1’의 2행, 3행과 4행에는 아무것도 입력되어 있지 않지만 실제로는 1행의 데이터와 같은 것이 들어 있다고 할 수 있습니다. 즉, ‘이름’열에서 ‘교실’열까지는 반복이 존재하는 열입니다.

  테이블1은 아래와 같이 표현되기도 합니다.

  그러나 너무 복잡해 보기가 어려우므로 일반적으로 반복되는 항목을 생략하고 ‘테이블1’과 같이 만듭니다.

  이러한 이유로 ‘테이블1’에서 반복되는 항목은 ‘이름’열에서 ‘교실’열까지라는 것을 알 수 있습니다. 지금부터 ‘테이블1’을 제1정규형으로 분해해 나가겠습니다. 이때에 주의해야 할 것이 있습니다. 그것은 분해한 2개의 테이블에서 원래의 테이블을 반드시 복원할 수 있도록 분해해야 한다는 것입니다. 기계를 분해한 후 다시 조립을 못하면 큰일나듯이 테이블도 마찬가지로 분해한 테이블을 반드시 원래의 테이블로 복원할 수 있어야 합니다.

  그러면 어떻게 해야 할까요? 테이블을 분해할 때는 약간의 고려를 해 볼 필요가 있습니다. 실제로 고려해야 하는 것은 어느 열의 값이 있으면, 어느 행을 구분 지을 수 있을까라는 점입니다. 테이블1에서는 ‘이름’열과 ‘과목’열이 해당됩니다. 이 2개의 열이 있으면 특정 행을 식별 할 수 있습니다.

  다음과 같이 비정규형 테이블을 2개로 나우어 봅니다.

  학생(이름, 학급, 교실)

  시험(이름, 과목, 점수, 교사)

  여기서 밑줄이 그어져 있는 열은 키 열로 그 항목이 결정되면 행이 구분지어진다는 것을 나타냅니다. 방금 ‘테이블1’을 2개의 테이블로 분할하였습니다. 그럼, 상세하게 보기로 합니다.

  먼저, ‘테이블2’입니다. ‘이름’이 키로 되어 있어 이름이 구분됨에 따라 특정한 1행을 식별할 수 있습니다.

? 테이블2: 학생

  또 하나는 ‘테이블3’입니다. 밑줄로 나타낸 것처럼 이 테이블은 ‘이름’열과 ‘과목’열의 값을 한 조로 하여 하나의 행을 식별할 수 있습니다.

  다시 말해, ‘테이블1’을 단지 2개로 나누는 것이 아니라 분해한 것에 키 열을 추가한 후 다시 하나의 테이블로 만드는 것입니다.

? 테이블3: 시험

이름	과목	점수	교사
최문석	국어	80	이경욱
최문석	수학	70	최용호
최문석	물리	92	배정훈
최문석	사회	48	조범식
이원석	국어	80 경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다. 어린이용이며, 설치가 필요없는 브라우저 게임입니다. https://s1004games.com	이경욱
이원석	수학	55	최용호
이원석	가정	96	도희정
문장원	예술	72	이수정
문장원	사회	87	조범식

  테이블2와 테이블3을 주의해서 보시길 바랍니다. 양쪽 테이블에 포함되어 있는 열은 ‘이름’열입니다. 이 열이 양쪽에 있기 때문에 ‘테이블2’와 ‘테이블3’에서 ‘테이블1’이 복원될 수 있습니다(이것은 RDBMS의 기본!).

  이렇게 제1정규형으로 변환되었습니다.

3-4 제2정규형

KEYWORD

    * 제2정규형

  제2정규형이란 연결 키가 있는 테이블을 찾아내어 연결 키의 일부 열에만 존재하고 있는 열을 도출하는 것을 말합니다. 연결 키가 없는(하나의 열만으로 기본 키를 만들고 있는) 테이블은 대상에서 제외됩니다.

  앞의 ‘테이블2’는 기본 키가 하나인 ‘이름’열만으로 구성되어 있으므로 이미 제2정규형의 조건을 만족하고 있습니다. 그렇기 때문에 ‘테이블2’를 제2정규형으로 바꿔줄 필요가 없습니다.

  연결 키의 일부 열에만 존재하고 있는 열이란 어떤 것일까요? 예를 들어, 다음의 ‘소프트 아이스크림’테이블을 살펴봅니다.

? 소프트 아이스크림

  ‘소프트 아이스크림’ 테이블은 연결 키를 가지고 있어서 용기와 맛이 결정되면 특정행이 도출되는 테이블입니다. 이 테이블을 잘 보면 ‘주원료’열의 값(딸기, 메론, 초콜릿, 바나나)은‘맛’ 열의 값(딸기맛, 메론맛, 초콜릿맛, 바나나맛)에는 존재하고, ‘용기’ 열 (컵, 콘)에는 존재하지 않습니다.

  다시 말해, 연결 키를 구성하는 열(맛, 용기)의 일부인 열(맛)에만 존재하는 열(주원료)을 도출해내는 작업이 제2정규화입니다.

  ‘가격’열은 연결 키를 구성하는 열(맛,용기) 모두에 존재하므로 도출하려는 열의 대상이 되지 않습니다. 그렇기 때문에 제2정규화로 도출하려는 열은 ‘소프트 아이스크림’ 테이블 열에서 ‘주원료’열이 됩니다.

  그러면 ‘테이블3’을 살펴봅니다. ‘테이블3’은 연결 키(‘이름’ 열, ‘과목’ 열)를 가지고 있는 테이블이므로 제2정규형으로 변형할 필요가 있는지를 음미할 대상이 됩니다.

  ‘점수’ 열은 무엇에 의해 구분되는가를 생각해 보면, ‘이름’열과 ‘과목’열이라는 것을 알 수 있습니다. 즉, ‘점수’열은 ‘학생’열과 ‘과목’열의 양쪽(연결 키의 전체 열)에 종속되어 있으므로 어떠한 작업도 할 필요가 없습니다.

  다음로 ‘교사’열을 생각해 봅니다. 교사가 어디에 종속되어 있는지 생각해 볼 때 과목이 결정되면 대응하는 교사가 자동으로 결정된다는 것을 알 수 있습니다. 학생이 누구인지에는 관계없습니다. 곧 ‘교사’열의 값은 ‘과목’열의 값(연결 키의 일부 열)에만 종속되어 있으므로 분해할 필요가 있다는 것을 알 수 있습니다. 다음고 같이 분해해 봅니다.

? 테이블4: 시험2

  ‘점수’열은 그대로 남김니다.

? 테이블5: 교사

  이와 같이, 교사는 과목이 결정되면 자동으로 구분됩니다. ‘테이블4’와 ‘테이블5’를 잘 살펴봅니다. ‘과목’열이 양쪽 테이블에 들어 있으므로 ‘테이블4’와 ‘테이블5’에서 ‘테이블3’을 복원할 수 있습니다.

3-5 제3정규형

KEYWORD

  * 제3정규형

  제3정규형은 기본 키 항목 이외의 열에 종속되어 있는 열을 도출하는 것입니다. 즉, 기본 키 열 이외의 열에 종속되어 있는 열이 없는 테이블입니다.

  ‘테이블4’의 ‘점수’열을 봅니다. ‘점수’열은 기본 키 열(‘이름’열, ‘과목’열)에 종속(기본 키 열이 결정되면 자동으로 결정됩니다)되므로 이미 제3정규형이 되어 있습니다. 다음으로 ‘테이블5’를 봅니다. 기본 키 이외의 열은 ‘교사’열입니다. 그러나 ‘교사’열은 기본 키 열(과목)에 종속되므로 ‘테이블5’도 이미 제3정규형이 되어 있습니다.

  앞의 ‘테이블2’를 다시 살펴봅시다.

? 테이블2: 학생

  ‘학급’열을 보면, ‘학급’열의 값은 기본 키 열(이름)이 결정됩니다. 그럼 ‘교실’ 열은 어떤가요? ‘교실’열은 학급이 결정되면 자동으로 결정됩니다. 즉, 기본 키 이외의 열에 종속되어 있다는 것을 알 수 있습니다. ‘테이블2’를 제3정규형으로 분해해 보면 아래와 같이 됩니다.

? 테이블6: 학생                        

? 테이블7: 위치

  이렇게 해서, 기본 키가 아닌 모든 열은 기본 키에만 종속된 형이 되어 제3정규형이 되었습니다. ‘테이블6’, ‘테이블7’은 양쪽에 ‘학급’열이 들어 있으므로 이 2개의 테이블에서 ‘테이블2’를 복원할 수 있습니다.

  ‘테이블6’, ‘테이블7’은 다음과 같이 될 수도 있습니다.

  상호 어느 쪽에도 속해있다고 말할 수 없을 때는 어느 것이든 분해할 수 있습니다.

그럼 지금까지의 분해를 정리해 봅시다.

  처음으로 비정규형 테이블을 2개로 분해해 보았습니다. 위의 그림은 ‘학생’테이블의 분해와 동시에 제2정규형 조건을 충족하는 테이블이 되었다는 것을 나타냅니다. ‘시험’테이블은 제1정규형이므로, 제2정규형 분해를 진행하여 ‘시험2’테이블과 ‘교사’테이블로 분해했다는 것을 보여줍니다. ‘시험2’테이블과 ‘교사’테이블은 제2정규형으로 분해됨과 동시에 제3정규형의 조건을 충족하는 테이블이 되었다는 것을 알 수 있습니다.

  다음은 4개의 테이블 정의를 정리해 본 것입니다.

  시험2(이름, 과목, 점수)

  교사(과목, 교사)

  학급(이름, 학급)

  위치(학급, 교실)

  이들의 테이블 관계를 도식으로 표현하면, 아래와 같습니다.

  여기에서 CREATE TABLE문을 작성하면 다음과 같습니다.

  단, 이들 정의는 예제일 뿐이므로 실제로는 애플리케이션에 맞추어 적용하면 됩니다. 애플리케이션의 예로 NOT NULL 제약을 엄밀하게 설정하고, 문자열에 기본 키를 붙이지 않고, 테이블에 'ID'열을 추가하고 테이터에 관리용 ID(학생 번호, 학급 번호, 직원 번호 등)를 부여해 이것을 기본 키 또는 참조 키로 할 수 있습니다.

  이렇게 ‘성적’테이블을 4개의 제3정규형 테이블로 분해할 수 있습니다. 그리고 이들 4개의 테이블을 조합하면, ‘성적’테이블로 복원 할 수 있습니다. 이처럼 기본 키 열과 기본 키에만 종속되어 있는 열로만 구성되어 있는 테이블로 분해함으로써, 중복되는 데이터가 없는 효율적인 테이블을 완성할 수 있습니다.

  이렇게 테이블의 CREATE TABLE문은 여러 가지로 생각할 수 있습니다. 우리 주위에 있는 데이터를 분석하고 비정규형으로 정리하여 정규화를 진행시키고 CREATE TABLE문을 작성할 수 있을 때까지 위 과정들을 체험합니다.

  앞서 말했듯이 제4정규형, 제5정규형 등 좀 더 정밀한 정규형이 있으나, 일반적으로 제3정규형까지 정규형화하면 충분하다고 할 수 있습니다. 그리고 경우에 따라서 시스템의 효율성을 고려하여 제3정규형을 없애고 일부러 제2정규형으로 돌아가기도 합니다.

  좀더 자세한 내용은 다른 책을 참조하도록 하고, 여기서는 다음 내용을 기억하기를 바랍니다.

  1. 우리들 주변에 있는 데이터는 비정규형인 것이 많으므로 바로 테이블화 해서는 안 된다.

  2. 꼭 보관할 필요가 있는 데이터가 무엇인지를 생각한다(계산으로 나오는 값은 열로 만들지 않는다 등).

  3. 열 상호간의 종속 관계를 충분히 고려한다.

  4. 정규형의 조건에 따라서 차근차근 데이터를 제3정규형으로 분해해 나간다.

  5. 분해할 때에는 원래의 테이블을 복원할 수 있는지를 고려한다.

  6. CREATE TABLE문을 생각할 때에 새로운 열(ID열)을 도입하면 설계가 편하고 보기좋게 될 수 있다.

  그리고 각각의 정규화는 다음과 같은 관계에 놓여 있습니다. 제3정규형은 제2정규형 조건을 충족하고, 제2정규형은 제1정규형 조건을 충족합니다. 테이블 설계나 작성시에 이들의 관계를 잘 이해하도록 합니다.