DB 관련 자료

출처: http://m.blog.naver.com/cosmos115/130003011203

1. 정규화의 목적

   정규화의 목적은 자료 저장 공간의 최소화하고, DB내 데이터의 불일치 위험을
최소화하는데 있습니다. 또한 자료의 수정, 삭제에 따른 예기치 않은 오류의
최소화하여 데이터구조의 안정성 최대화하는 것이 그 목적입니다.

 

2. 정규화의 효과

   – 데이터의 중복을 제거하고 데이터의 일관성을 유지할 수 있습니다.

   – 데이터 모형의 단순화가 가능합니다.

   – 속성의 배열 검증이 가능합니다. 즉, 식별자와 속성과의 종속성 여부를 판단할 수 있습니다.

   – 데이터베이스 설계가 용이하며, 엔티티(Entity)와 관계(Relationship)의 누락을 방지할 수

     있습니다.

 

3. 정규화의 문제점

   – 정규화로 인한 테이블의 개수 증가로 인하여 Join이 많이 발생하여 응답속도의 지연이 있을

    수 있습니다

   – 특정시점의 정보를 표현하기 위해 이력관리 엔티티의 발생 및 업무규칙 수용 난이도가 증가

    할 수 있습니다

   – 데이터 공간의 비효율적인 활용이 발생할 수 있습니다. (제 3정규화 이상의 정규화를 수행

     하는 경우)따라서 대부분 현업에서는 제 3정규화까지만을 수행하고 필요에 따라 비정규화

     (Denormalization) 과정을 수행합니다.

 

1. 정규화(normalization)이란 무엇인가?
DB 설계란 자료의 중복성과 비정규성을 제거하고 검색키를 설정하기 위해 데이타베이스의 logical schema를 작성하는 것이다. 데이타베이스 설계시 가장 중요한 관심사는 릴레이션 구조를 결정하는 것이라고 할 수 있다. 데이타베이스에 어떤 릴레이션을 두고, 그 릴레이션에 어떠한 속성을 포함시킬 것인지는 데이타관리 및 사용의 성패에 큰 영향을 미친다. 바로 정규화이론은 어떠한 릴레이션 구조가 바람직한 것인지, 바람직하지 못한 릴레이션을 어떻게 분해하여야 하는지에 관한 구체적인 판단기준을 제공한다.

2. 정규화의 목적
자료정규화작업의 가장 큰 목적은 자료저장의 중복성 배제이다. 정규화이론에서는 릴레이션의 형태가 여러단계로 구분된다. 가장 기본적인 정규화조건도 만족하지 못하는 릴레이션을 비정규형, 만족하는 릴레이션을 제1정규형이라고 부른다. 조건이 점점 엄격해짐에 따라 제2, 제3, 제4, 제5정규형으로 구분된다. 정규화이론에서는 보다 높은 단계의 정규형으로 나아갈수록 보다 바람직한 릴레이션 구조를 가진다고 간주한다. 즉 높은 단계의 정규형으로 나아갈수록 데이타의 본질적 의미가 릴레이션 구조에 보다 정확히 반영되고, 데이타 중복을 줄이고, 데이타 변경시 발생하는 문제점을 방지하고, 궁극적으로 데이타 무결성(data integrity)을 제고할 수 있다고 가정된다.

3. 반드시 정규화를 해야 하는가?
정규화이론을 데이타베이스 설계시에 가이드라인으로 삼음으로써 데이타를 보다 바람직한 구조로 표현하고, 불필요한 데이타 중복을 베재하며, 데이타 처리과정에서의 제반 이상현상을 방지하고, 그 결과 데이타 무결성을 보완할 수 있게 된다. 그러나 정규화 작업이 반드시 지켜져야 할 철칙한 아니다. 때로는 정규화 이론에 어긋나는 릴레이션 구조를 선택해야 할, 실용적인 필요성이 우선하는 경우도 있게 된다. 정규화 작업은 특히 처리속도와의 trade-off관계가 심사숙고의 대상이 된다. 고도의 정규화를 추구하다보면 레코드의 종류가 늘어나고 프로그래밍도 복잡해 져서 결국은 전체적인 performance가 떨어지게 된다. 이는 양자간의 절충으로 어느정도 해결될 수 있지만 개발자와 사용자간에 깊이 생각해 보아야 할 문제인 것이다.

4. 데이타 모델링이란 무엇인가?
이와 비교하여 데이타모델링이란 시스템 분석의 한가지 방법으로서 어떤 시스템을 구성하고 있는 여러 대상과 관계를 식별하는 것을 말한다. 다시 말해 어떤 시스템에 대한 우리의 머리속에 있는 지식을 그 시스템에 관한 도식 및 언어상의 표현으로 전환시키는 것이다. 제임스 마틴의 정보공학에서는 데이타모델링을 특히 강조하고 있는데 이는 한 기업에서 사용되고 잇는 데이타형태는 그다지 변하지 않기 때문이다. 실제로 실체 형태는 경우에 따라 새로운 실체 형태를 추가하는 경우를 제외하고는 그다지 변하지 않는다. 따라서 잘 설계되기만 하면 이들 모델은 거의 변하지 않으며, 또 대체로 혼란을 초래할 변경은 피할 수도 있는 것이다.

 

5. 모델링과 정규화의 비교
데이타 모델링과 데이타정규화의 비교는 각각의 업무를 수행하는 사람들을 비교해 봄으로써 이해할 수 있다. 다시 말해 데이타모델링은 데이타관리자, 데이타정규화는 데이타베이스 설계자에 의해 수행되는 것이 보통이므로 이들의 역할을 비교해보자는 것이다. 우선 데이타베이스 설계자는 특정 데이타베이스의 설계를 맡고 있는 사람이다. 데이타베이스 설계자는 각 데이타베이스의 물리적 구조를 최대한 효율적이며 기계적 능률을 낼 수 있도록 설계하는 일을 한다. 반면 데이타관리자는 기업의 데이타를 기획하고 모델링하고 조정하는 고급업무를 책임지고 잇다. 이것은 기술적 숙련을 요하는 작업이기보다는 오히려 사업을 운영하는데 필요한 데이타를 이해하고 데이타 항목의 정의와 표현에 대하여 다양한 개인들의 의견을 일치시키는 임무라고 할 수 있다. 다시 말해 데이타에 관한 서로다른 많은 견해들을, 이상적으로는 CASE 모델링 도구를 이용하여, 충분히 표준화된 하나의 데이타모델로 합성시켜야 하는 것이다.

6. 한마디로 정리하면…
한마디로 정리, 비교하면 데이타정규화는 데이타베이스 설계시 자료의 중복성을 베제하고자 수행하는 작업으로서 수행속도와의 trade-off관계를 염두에 두고 작업을 해야하며, 데이타모델링은 기술적인 작업이라기보다 해당 기업 전반에 걸친 업무처리의 이해를 바탕에 둔 작업으로서 기업의 데이타모델을 만들고 최대한 안정되기 유지되도록 하는 작업을 말한다

 

 

정규화

정규화는 데이터베이스에 저장되는 데이터의 중복을 최소화시키는 방법이다. 정규화는 1차, 2차, 3차, 보이스-코드, 4차, 5차, 영역-키 정규화가 있는데, 실무에서는 보통 3차 정규화 또는 보이스-코드 정규화 정도까지 하기를 권장하고 있다.

1차 정규화

1차 정규화는 테이블의 각 셀은 단일 값을 갖는다는 관계형 데이터모델의 본질적 제약과 관련된 개념이다. <표 1>에서 제품번호, 단가, 수량의 속성값은 한 셀에 여러 값이 들어가 있기 때문에 관계형 데이터의 본질적 제약을 위배한 것이다.

주문번호 주문일 고객번호 고객이름 제품번호 단가 수량
0001 2003.7.15 c1 홍길동 p01
P02
350
100
1
3
0002 20037.16 c2 임꺽정 p01 350 4
00003 2003.7.17 c1 홍길동 p03 400 5

<표 1> 정규화되지 않은 테이블

주문번호 밑에 주키를 나타내는 밑줄을 꼭 그어주세요 1차 정규화를 하기 위해서는 <표 2>처럼 한 셀에 하나의 데이터만을 나타내야 한다. 이렇게 1차 정규화가 된 상태에서는 주문번호 속성이 더 이상 유일한 값을 갖지 못하므로 주문번호와 제품번호를 연결한 합성키가 1차 정규화 상태의 새로운 주 키가 된다.

주문번호 주문일 고객번호 고객이름 제품번호 단가 수량
0001 2003.7.15 c1 홍길동 p01 350 1
0001 2003.7.15 c1 홍길동 P02 100 3
0002 20037.16 c2 임꺽정 p01 350 4
00003 2003.7.17 c1 홍길동 p03 400 5

<표 2> 1차 정규화가 끝난 테이블

주문번호와 제품번호 밑에 주키를 나타내는 밑줄을 꼭 그어주세요

2차 정규화

2차 정규화 단계부터는 데이터의 중복을 제거하게 된다. <표 2>에서 중복된 데이터를 찾아보자. 제품번호 ‘p01’의 단가가 350이라는 정보가 두 번 중복되어 나타나고 있음을 볼 수 있다. 단가는 제품번호 속성에 종속되어 있기 때문에 제품번호가 정해지면 그 제품의 단가가 정해진다. 따라서, <표 3>처럼 종속관계에 있는 제품번호와 단가 속성을 별도의 [제품] 테이블로 분리하고, 원래의 테이블에는 제품번호 속성만 남겨두면 제품번호 ‘p01’의 단가인 350이라는 정보가 두 번 중복되는 것을 피할 수 있다.

또 다른 중복 정보는 없을까? 주문번호 ‘00001’에 종속되는 주문일, 고객번호, 고객이름이 두 번 중복해서 나타나고 있다. 마찬가지 방법으로 <표 3>처럼 주문번호와 주문일, 고객번호, 고객이름을 별도의 [주문-고객] 테이블로 분리하고, 원래의 테이블에는 주문번호 속성만 남겨두면 특정 주문번호에 대한 주문일, 고객번호, 고객이름 데이터가 두 번씩 중복되는 것을 피할 수 있다.

이 외에도 중복을 제거할 수 있는 부분이 있다. <표 3>의 [주문-고객] 테이블을 보면 고객번호 ‘c1’의 이름이 ‘홍길동’이라는 정보가 두 번 중복해서 나타나고 있다. 그러나 이것은 3차 정규화에서 처리할 부분이다. 2차 정규화는 주 키의 일부분, 즉 주문번호와 제품번호에 종속된 것만을 대상으로 하는데, 고객번호나 고객이름은 주 키를 구성하는 속성이 아니기 때문이다.

주 키의 일부분에만 종속된 상태를 부분 종속이라고 하는데, 부분 종속을 갖는 속성을 별도의 테이블로 분리한 <표 3>의 상태가 2차 정규화이다. 따라서 1차 정규화된 상태의 주 키가 하나의 속성뿐이라면 2차 정규화는 건너뛰고 바로 3차 정규화를 하면 된다. 그러나 <표 2>

처럼 1차 정규화된 상태의 주 키가 둘 이상의 속성을 가지고 있을 경우에는 2차 정규화 작업이 필요하다.

제품

제품번호 단가
p01 350
p02 100
p03 400

<표 3> 2차 정규화가 끝난 테이블

주문-제품

주문번호 제품번호 수량
00001 p01 1
00001 p02 3
00002 p01 4
00002 p03 5

주문-고객

주문번호 주문일 고객번호 고객이름
00001 2003.7.15 c1 홍길동
00002 2003.7.16 c2 임꺽정
00003 2003.7.17 c1 홍길동



3차 정규화

2차 정규화가 주 키 속성과 주 키가 아닌 속성간의 종속관계가 있을 경우 이를 별도의 테이블로 분리한 것이라면 3차 정규화는 2차 정규화가 끝난 상태에서 주 키가 아닌 속성끼리 종속관계가 있을 경우 이를 별도의 테이블로 분리한 것이다. <표 3>은 2차 정규화가 끝난 상태이기 때문에 주 키가 아닌 속성끼리 중복되는 정보가 있는지를 살펴보면 된다. 앞에서 얘기한대로 <표 3>의 [주문-고객] 테이블을 보면 고객번호 ‘c1’의 이름이 ‘홍길동’이라는 정보가 두 번 중복해서 나타나고 있다. 2차 정규화와 마찬가지 방법으로 고객번호와 고객이름을 별도의 [고객] 테이블로 분리하고 원래의 [주문-고객] 테이블에는 고객번호 속성만 남겨두면 3차 정규화가 완성된다.<표 4>는 3차 정규화까지 끝난 상태의 최종 테이블이다.

제품

제품번호 단가
p01 350
p02 100
p03 400

<표 4> 3차 정규화가 끝난 테이블

주문-제품

주문번호 제품번호 수량
00001 p01 1
00001 p02 3
00002 p01 4
00002 p03 5

주문-고객

주문번호 주문일 고객번호
00001 2003.7.15 c1
00002 2003.7.16 c2

고객

고객번호 고객이름
c1 홍길동
c2 임꺽정

보이스-코드 정규화
3차 정규화가 끝난 상태에서도 여러 개의 후보 키(주 키가 될 수 있는 속성)가 존재하는 경우에는 속성간의 종속이 남아있을 수 있다. 이를 제거한 것이 보이스-코드 정규화이다. <표 4>에서는 후보 키가 여러 개 있는 테이블이 없으므로 보이스-코드 정규화를 추가적으로 할 필요가 없다.

 

 

비정규화 (Denomalization)

경우에 따라서는 나누어 놓은 테이블을 성능 향상의 목적으로 다시 합쳐야 할 경우가 생길 수도 있습니다. 나누어 놓은 테이블들을 함께 연결해서 사용하려면 JOIN이라는 방법을 사용(부하가 걸림), 따라서 지나치게 자주 JOIN을 사용한다면 차라리 테이블을 다시 합치는 것에 대해 고려해 보아야 합니다.

꼭 테이블을 합치는 것만이 비정규화는 아닙니다. 아래와 같은 경우에도 비정규화에 해당됩니다.

1. 그룹에 대한 합계와 같은 값을 미리 계산하여 테이블에 저장해 둡니다. (일반적으로 트리거가 필요하게 됩니다.)

예) 판매 테이블에 새로운 데이터가 변경될 때 마다 제품 테이블의 총 판매 수량 컬럼이 변경됩니다.

2. 한 테이블에서 자주 사용되는 행(레코드)들과 그렇지 않은 행들을 분리하여 두개의 테이블로 둡니다. (이럴 때는 UNION으로 다시 연결 시킬 수 있습니다.)

예) 고객 테이블에 200만 건의 고객이 있습니다. 그중 약 10%에 해당하는 20만명 정도의 고객들만이 자주 거래를 하고 나머지 고객들은 1년이 넘게 거래가 없다고 하면, 이 두 부류를 두개의 테이블로 나누어 관리합니다.

3. 다른 테이블에 의존적이지만 자주 JOIN에서 사용되는 컬럼을 중복하여 테이블 안에 하나 더 만듭니다.

출처: http://l2j.co.kr/986 [L2J with DuraBoys]

=====================================================================

Isolation Level의 필요성

DBMS를 혼자서 사용한다면 문제가 없겠지만, 대부분의 프로그램이 여러명이 사용하고 있으며 상대방이 변경된 부분이 반영이 안되거나 혹은 반영이 이상하게 되어서 화면상의 데이터가 꼬이는 문제등이 발생하게 된다. 내가 실행하고 있는 이 트랜잭션 안에서 중간에 다른 트랜잭션의 결과가 반영이 되어, 치명적인 연산 버그등이 발생하게 되면 이미 사용할 수 없는 수준으로 폐기처리 해야 할 것이다.

예를 들어, A라는 값이 1000이라는 값이 있고, B라는 값이 2000이라는 값이 있는데 처음 시작했을 때, A+B를 해서 3000이라는 값을 저장하였다. 그러나 다른 트랜잭션이 A의 값을 1500으로 변경하여, 마지막에 한번 더 A+B를 해서 3500으로 변경이 되었다면 처음과 마지막의 연산값이 틀려서 문제가 발생할 것이다.

이러한 문제를 방지하기 위해서는 고립화 수준(Isolation Level)을 올려야 한다.

Isolation Level의 개념 및 특징

가. ACID 속성을 보장하기 위한 Isolation Level의 개념

– 트랜잭션 실행 중 중간 연산 결과가 다른 트랜잭션으로 접근 불가하도록하는 고립성을 유지하기 위한 데이터를 허용하는 수준

– 트랜잭션에서 일관성이 없는 데이터를 허용하도록 하는 수준

나. Isolation Level의 특징

  • ANSI / ISO SQL Standard : SQL-92 표준에서 4단계로 표준화, DBMS 벤더들이 준수
  • ACID 보장 : 트랜잭션의 속성인 ACID를 만족하기 위한 설정
  • Locking 기반 : 각각의 설정은 Locking 기술을 기반으로 Isolation Level을 조정

병행제어 미처리 시 문제 유형(직렬성 위반 내용)

1) 부정판독(Dirty Read)

– Commit 되지 않은 데이터를 읽을 수 있음

– 다른 트랜잭션에 의해 수정됐지만 아직 커밋되지 않은 데이터를 읽는 것

– 트랜잭션 T1이 특정 행(ROW)의 갱신을 수행하고 난 후, T2가 그 행을 검색한 뒤 T1이 취소된다고 가정하면 트랜잭션 T2는 더 이상 존재하지 않는, 그리고 결코 존재하지 않았던 행을 본 것이 됨

2) 비반복판독(Nonrepeatable Read)

– Commit 된 데이터만 읽음

– 최초 읽은 값고 다시 읽었을 때의 값이 다를 수 있음

– 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하여 두 쿼리 결과가 다르게 나타나는 현상

– 트랜잭션 T1이 한 행을 검색하고 T2가 그 행을 갱신한 뒤 T1이 동일한 행을 다시 검색한다고 가정하면, 트랜잭션 T1은 “동일” 한 행을 두 번 검색한 것이지만 두 개의 다른 값을 보게 됨

3) 가상 판독(Phantom Read)

– 특정 범위 값을 읽고 나서 다시 읽었을 때 없었던 새로운 행을 읽을 수 있음

– 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫 번째 쿼리에서 없던 유령(Phantom) 레코드가 두 번째 쿼리에서 나타나는 현상

– 트랜잭션 T1이 특정 조건을 만족하는 모든 행을 검색한다고 하자. 그런 다음 트랜잭션 T2는 동일한 조건을 만족하는 새로운 행을 삽입한다고 할 때 트랜잭션 T1이 검색 요구를 반복한다면, 이전에는 존재하지 않았던 한 행을 보게 됨

Isolation Level의 유형

1) Read Uncommitted

– 트랜잭션에서 처리중인, 아직 COMMIT 되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용

– SELECT 문장 수행하는 경우 해당 데이터에 Shared Lock이 걸리지 않는 Level

– 어떤 사용자가 A라는 데이터를 B라는 데이터로 변경하는 동안 다른 사용자는 B라는 아직 완료되지 않은(Uncommitted 혹은 Dirty) 데이터 B를 읽을 수 있음

– 직렬성 위반 : Dirty Read, Nonrepeatable Read, Phantom Read

2) Read Committed

– 트랜잭션이 COMMIT 되어 확정된 데이터만 읽는 것을 허용

– 대부분의 DBMS가 사용하는 Default Isolation Level (Mysql 은 Repeatable Read가 Default)

– 다른 사용자가 완료한(Committed) 데이터만 읽음

– DBMS별로 Shared Lock이 걸리거나, 걸리지 않을 수 있음

– 직렬성 위반 : Nonrepeatable Read, Phantom Read

3) Repeatable Read

– 선행 트랜잭션이 읽은 데이터는 트랜잭션이 종료될 때까지 후행 트랜잭션이 갱신, 삭제하는 것 불허

– 트랜잭션이 완료될 때까지 SELECT 문장이 사용하는 모든 데이터에 Shared Lock이 걸림

– 다른 사용자는 그 영역에 해당되는 데이터에 대한 수정이 불가능함

– 직렬성 위반 : Phantom Read

4) Serializable

– 선행 트랜잭션이 읽은 데이터를 후행 트랜잭션이 갱신하거나 삭제하지 못할 뿐 아니라, 중간에 새로운 레코드를 삽입하는 것도 막아줌

– Repeatable Read 수준에서 범위에 대한 Shared Lock 추가

– 직렬성 위반 : 발생불가

Isolation Level 수준과 비일관성 현상

– 특별히 리소스에 대한 배타적 접근이 요구되는 경우 적절한 Isolation을 선정하여 사용하는 것이 필요함

Consistency(일관성)와 Concurrency(병행성, 동시성)

※ 트랜잭션 고립화 수준을 높이면 일관성은 향상 되지만 더 넓은 범위의 Lock을 더 오랫동안 유지하는 방식으로 동시성을 저하

출처: http://needjarvis.tistory.com/161 [자비스가 필요해]

 

정규표현식 쉡 활용 ..

http://m.blog.naver.com/sol9501/70108310435

 

 

 

Advertisements