이 글은 데이터베이스 개론 (저자 김연희)의 내용을 개인적으로 정리하는 글임을 알립니다.

정규화의 개념과 이상 현상

정규화(Normalization)의 개념

데이터베이스를 잘못 설계하면 불필요한 데이터 중복이 발생하여 릴레이션에 대한 데이터 삽입, 수정, 삭제 연산을 수행할 때 부작용이 발생할 수 있다.

이러한 현상을 이상(anomaly) 현상이라 한다.

이상 현상을 제거하면서 데이터베이스를 올바르게 설계해나가는 과정이 정규화다.

정규화는 데이터베이스를 설계 결과물을 검증하기 위해 사용하기도 한다.

이상 현상의 종류

이상 현상에는 갱신 이상(Modification Anomaly), 삽입 이상(Insertion Anomaly), 삭제 이상(Deletion Anomaly)이 있다.

위 릴레이션은 고객들이 이벤트에 참여한 결과를 저장하고 있는 릴레이션이다.

고객은 여러 이벤트에 참여할 수 있으므로 고객 아이디만으로는 튜플을 유일하게 식별할 수 없다.

따라서 고객아이디와 이벤트번호 속성을 함께 사용하여 이벤트참여 릴레이션의 기본키를 구성한다.

삽입 이상

릴레이션에 새 데이터를 삽입하기 위해 원치 않는 불필요한 데이터도 함께 삽입해야 하는 문제를 삽입 이상이라 한다.

이 릴레이션에 새로운 고객 데이터를 삽입한다고 하면, '성원용' 고객은 참여한 이벤트가 없으므로 삽입할 수 없다.

이벤트참여 릴레이션의 기본키가 고객아이디와 이벤트 번호 속성이고, 기본키를 구성하는 속성은 널 값을 가질 수 없다는 제약이 존재하기 때문이다.

따라서 성원용 고객에 대한 데이터를 이벤트 참여 릴레이션에 삽입하려면 실제로 참여하지 않은 임시 이벤트번호를 삽입해야 하므로 이벤트 참여 릴레이션에는 삽입 이상이 발생하게 된다.

갱신 이상

릴레이션의 중복된 튜플들 중 일부만 수정하여 데이터가 불일치하게 되는 모순이 발생하는 것을 갱신 이상이라 한다.

위 이벤트참여 릴레이션에는 아이디가 apple인 고객에 대한 튜플이 3개 존재하여, 고객아이디, 고객이름, 등급 속성의 값이 중복되어 있다.

아이디가 apple인 고객의 등급이 gold에서 vip로 변경된다면, 이벤트 참여 릴레이션에서 apple 고객에 대한 튜플 3개의 등급 속성 값이 모두 수정되어야 한다.

그렇지 않고 위와 같이 2개의 튜플만 등급이 수정된다면 모순이 생겨 갱신 이상이 발생하게 된다.

삭제 이상

릴레이션에서 튜플을 삭제하면 꼭 필요한 데이터까지 함께 삭제하여 데이터가 손실되는 연쇄 삭제 현상을 삭제 이상이라 한다.

아이디가 orange인 고객이 이벤트 참여를 취소하여 이벤트참여 릴레이션에서 관련된 튜플을 삭제해야 한다면, 위 그림같이 하나의 튜플을 삭제하면 된다.

하지만 이 튜플은 해당 고객에 대한 정보인 고객아이디, 고객이름, 등급에 대한 정보도 유일하게 가지고 있다.

따라서 이 튜플이 삭제되면 이벤트 참여와 관련이 없음에도 불구하고 해당 고객에 대한 중요한 데이터도 삭제되는 삭제 이상이 발생한다.

정규화의 필요성

위 이벤트참여 릴레이션과 같은 여러 이상 현상이 발생하는 이유는 관련 없는 속성들을 하나의 릴레이션에 모아두고 있기 때문이다.

이상 현상이 발생하지 않도록 하려면, 관련 있는 속성들로만 릴레이션을 구성해야 한다.

이를 위해 필요한 것이 정규화다.

정규화는 이상 현상이 발생하지 않도록, 릴레이션을 관련이 있는 속성들로만 구성하기 위해 릴레이션을 분해하는 과정이다.

정규화 과정에서 고려해야 하는 속성들 간의 관련성을 함수적 종속성(Functional Dependency)이라고 한다.

함수 종속(FD : Functional Dependency)

하나의 릴레이션을 구성하는 속성들의 부분 집합을 X와 Y라 할 때, 어느 시점에서든 릴레이션 내의 모든 튜플에서 X 값에 대한 Y 값이 항상 하나면 "X가 Y를 함수적으로 결정한다" 또는 "Y가 X에 함수적으로 종속되어 있다"라고 한다.

이 경우, X를 결정자, Y를 종속자라고 한다.

이 고객 릴레이션에서 각 고객아이디 속성 값에 대응되는 고객이름 속성과 등급 속성의 값이 단 하나이므로, 고객아이디가 고객이름과 등급을 결정한다고 볼 수 있다.

그러므로 고객 릴레이션에서 고객이름과 등급 속성은 고객아이디 속성에 함수적으로 종속되어 있어, 고객아이디는 결정자가 되고 고객 이름과 등급은 종속자가 된다.

함수 종속관계를 판단할 때 유의할 점은, 현재 시점에 릴레이션에 포함된 속성 값만으로 판단하면 안 된다는 것이다.
릴레이션에서 속성 값은 계속 변할 수 있기 때문에 속성 자체가 가지고 있는 특성과 의미를 기반으로 판단해야 한다.

고객 릴레이션에 현재 저장되어 있는 속성 값이 아닌 속성 자체의 특성을 고려하여 함수 종속관계를 판단해야 한다.
고객 릴레이션에서 고객아이디는 고객을 구별해주는 기본키 속성이기 때문에 아이디가 같은 서로 다른 고객이 존재할 수 없다. 
그러므로 고객아이디가 정해지면 오직 하나의 고객이름과 등급이 결정된다.

일반적으로 튜플을 유일하게 구별하는 기본키와 후보키는 그 특성 때문에 릴레이션을 구성하는 다른 모든 속성들을 함수적으로 결정한다.
하지만 이러한 특성으로 인해 함수 종속관계 X →Y에서 기본키나 후보키만 결정자인 X가 될 수 있는 것은 아니다.
기본키나 후보키가 아니더라도 속성 값을 유일하게 결정하는 속성 X는 함수 종속관계에서 모두 결정자가 될 수 있다.
물론 릴레이션 내의 여러 튜플에서 속성 X 값이 같으면 이 값과 연관된 속성 Y 값도 모두 같아야 결정자로 인정받을 수 있다.

위의 이벤트참여 릴레이션에서는 고객아이디가 고객이름을 유일하게 결정한다.
고객아이디가 같으면 모든 튜플에서 고객이름이 반드시 같은 값을 가지기 때문이다.
그러므로 고객이름은 고객아이디에 종속되어 있어, 고객아이디가 결정자가 되고 고객이름이 종속자가 된다.

그리고 기본키인 [고객아이디, 이벤트번호] 속성 집합은 당첨여부 속성을 유일하게 결정한다.
아이디가 apple인 고객이 참여한 E001 이벤트의 당첨여부는 Y만 존재하기 때문이다. 그러므로 당첨여부는 [고객아이디,     이벤트번호]에 종속되어 있어, [고객아이디, 이벤트번호]가 결정자가 되고 당첨여부가 종속자가 된다.
물론 당첨여부뿐 아니라 고객이름도 기본키인[고객아이디, 이벤트번호]에 종속되어 있다.

이런 경우, 고객이름 속성이 [고객아이디, 이벤트번호] 속성 집합에 부분 함수 종속되었다고 한다.

반면 당첨여부 속성은  [고객아이디, 이벤트번호] 속성 집합에 완전 함수 종속되었다고 한다.

• 완전 함수 종속 : 릴레이션에서 속성 집합 Y가 속성 집합 X에 함수적으로 종속되어 있지만, 속성 집합 X 전체에 종속된 것인지 일부분에 종속된 것이 아님을 의미한다.
• 부분 함수 종속 :  속성 집합 Y가 속성 집합 X의 전체가 아닌 일부분에도 함수적으로 종속됨을 의미하며, 부분 함수 종속 관계가 성립하려면 결정자가 여러 개의 속성들로 구성되어 있어야 한다.

일반적으로 함수 종속이라고 하면 완전 함수 종속을 의미한다.

릴레이션에 존재하는 함수 종속 관계에서는 결정자와 종속자가 같거나, 결정자가 종속자를 포함하는 것처럼 당연한 함수 종속 관계는 고려하지 않는다.

기본 정규형과 정규화 과정

정규화의 개념과 정규형의 종류

• 정규화 : 함수 종속성을 이용하여 릴레이션을 연관성이 있는 속성들로만 구성되도록 분해해서, 이상 현상이 발생하지 않는 올바른 릴레이션으로 만들어나가는 과정

정규화의 기본 목표는 관련이 없는 함수 종속성을 별개의 릴레이션으로 표현하는 것이다.

• 정규형 : 릴레이션이 정규화된 정도를 표현한 것  

정규형은 크게 기본 정규형 과 고급 정규형으로 나뉜다. 

• 기본 정규형 : 제1정규형, 제2정규형, 제3정규형, 보이스/코드 정규형
• 고급 정규형 : 제4정규형, 제5정규형

각 정규형마다 만족시켜야 하는 제약조건이 존재한다.

릴레이션이 특정 정규형의 제약조건을 만족하면 릴레이션이 해당 정규형에 속한다고 표현한다.

정규형의 차수가 높아질수록 요구되는 제약조건이 많아지고 엄격해진다.

일반적으로 차수가 높은 정규형에 속하는 릴레이션일수록 데이터 중복이 줄어 데이터 중복에 의한 이상 현상이 발생하지 않는 바람직한 릴레이션일 수 있다.

하지만 모든 릴레이션이 제5정규형에 속해야 되는 것은 아니므로 릴레이션의 특성을 고려해서 적합한 정규형을 선택해야 한다.

제 1정규형(1NF)

제 1정규형
릴레이션에 속한 모든 속성의 도메인이 원자 값으로만 구성되어 있으면 제 1정규형에 속한다.

릴레이션이 제 1정규형에 속하려면 릴레이션에 속한 모든 속성이 더는 분해되지 않는 원자 값만 가져야 한다.

위 릴레이션에서 이벤트번호와 당첨여부 속성은 원자 값을 가지지 않는다.

즉, 다중 값을 가지는 속성을 포함하기 때문에 제 1정규형 제약조건을 만족시키지 못하므로 제 1정규형에 속하지 않는다.

관계 데이터베이스 릴레이션은 모든 속성이 원자 값을 가지는 특성이 있기 때문에 최소한 제1 정규형을 만족해야 관계 데이터베이스의 릴레이션이 될 자격이 있다고 말할 수 있다.

아래의 릴레이션은 이벤트번호와 당첨여부 속성은 원자 값을 갖도록 수정한 릴레이션이다.

수정한 릴레이션은 제 1정규형에 속하지만 불필요한 데이터 중복으로 인해 이상 현상이 발생할 수 있다.

이벤트 참여 릴레이션에는 등급과 할인율 속성의 값이 중복되어 나타나는 경우가 많다.

이처럼 불필요한 데이터가 중복되면 이상 현상이 발생할 수 있다.

이벤트참여 릴레이션은 부분 함수 종속을 포함하고 있기 때문이 이상 현상이 발생한다.

즉, 기본키인 [고객아이디, 이벤트번호]에 완전 함수 종속되지 못하고 일부분인 고객아이디에 종속되는 등급과 할인율 속성 때문이다.

따라서 부분 함수 종속이 제거되도록 이벤트참여 릴레이션을 분해해야 한다.

릴레이션을 분해하여 부분 함수 종속을 제거하면, 분해된 릴레이션들은 제2 정규형에 속하게 된다.

제 2정규형(2NF)

제 2정규형
릴레이션이 제 1정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속되면 제 2정규형에 속한다.

위와 같이 2개의 릴레이션으로 분해하면, 분해된 고객 릴레이션과 이벤트참여 릴레이션은 모두 제2정규형에 속하게 된다.
릴레이션이 둘로 분해되면서 등급과 할인율 속성에 대한 데이터 중복이 줄어듦을 확인할 수 있다.

위의 함수 종속 다이어그램에서 확인할 수 있듯이, 릴레이션 분해 과정을 통해 고객 릴레이션에는 기본키인 고객아이디와 기본키에 완전 함수 종속된 등급·할인율 속성만 존재한다. 그러므로 고객 릴레이션은 제2정규형에 속한다.

이벤트참여 릴레이션에도 기본키인 [고객아이디, 이벤트번호]와 기본키에 완전 함수 종속된 당첨여부 속성만 존재한다.
그러므로 이벤트참여 릴레이션도 제2정규형에 속한다.

정규화 과정에서 릴레이션을 분해할 때 주의할 점은, 분해된 릴레이션들을 자연 조인하여 분해 전의 릴레이션으로 다시 복원할 수 있어야 한다는 것이다.

즉, 릴레이션이 의미상 동등한 릴레이션들로 분해되어야 하고, 릴레이션을 분해했을 때 정보 손실이 발생하지 않아야 한다.

정보의 손실 없이 릴레이션을 분해하는 것을 무손실 분해라고 한다.

제 2정규형에 속하더라도 릴레이션에 이상 현상이 발생할 수 있다.

위 이벤트참여 릴레이션은 함수 종속성을 단 하나만 포함하므로 이상 현상이 발생하지 않는다.

하지만 고객 릴레이션은 부분 함수 종속성은 없지만 함수 종속성을 아직도 여러 개 포함하고 있어 이상 현상이 발생할 수 있다.

릴레이션을 분해하여 이행적 함수 종속을 제거하면, 분해된 릴레이션들은 제 3정규형에 속하게 된다.

제 3정규형(3NF)

제 3정규형
릴레이션이 제 2정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 이행적 함수 종속이 되지 않으면 제 3정규형에 속한다.

이행적 함수 종속이란 X→Y, Y→Z의 함수 종속 관계를 가지는 상황에서 X→Z가 성립하게 되는 상황을 의미한다.

제2정규형을 만족하더라도 하나의 릴레이션에 함수 종속 관계가 여러개 있고 그중에서도 이행적 함수 종속 관계를 가지게 되면 이상 현상이 발생할 수 있다.

따라서 이행적 함수 종속이 발생하지 않도록 릴레이션을 분해해야 한다. 

보이스/코드 정규형(BCNF : Boyce/Codd Nomal Form)

보이스/코드 정규형(BCNF)
릴레이션의 함수 종속 관계에서 모든 결정자가 후보키이면 보이스/코드 정규형에 속한다. 

제3정규형까지 마치고 나면 하나의 릴레이션에 여러개의 후보키가 존재할 수도 있는데, 이 경우에은 제3정규형까지 모두 만족하더라도 이상 현상이 발생할 수 있다.

따라서 후보키가 여러개 있을때 발생할 수 있는 이상 현상을 방지하기 위해 보이스/코드 정규형을 하게 된다.따라서 제3정규형의 릴레이션에서 기본키가 유일한 후보키이며 함수 종속 관계어서도 유일한 결정자가 되도록 릴레이션을 분해한다.

제4정규형과 제5정규형

고급 정규형으로 분류되는 제4정규형은 릴레이션이 보이스/코드 정규형을 만족하면서, 함수종속이 아닌 다치 종속을 제거해야 만족할 수 있다.

그리고 제5정규형은 릴레이션이 제4정규형을 만족하면서 후보키를 통하지 않는 조인 종속을 제거해야 만족할 수 있다.

다만 오히려 제5정규형까지 만족할 때까지 분해할 경우 비효율적일 때가 많다. 

정규화 과정 정리

릴레이션을 분해하여 모든 속성의 도메인이 원자 값으로만 구성되도록 하면 제1정규형이 된다.

제1정규형에 속하는 릴레이션에서 부분 함수 종속을 제 거하여 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속되면 제2정규형이 된다.

제2정규 형 릴레이션에서 이행적 함수 종속을 제거하면 제3정규형이 된다.

제3정규형 릴레이션에서 후보키가 아닌 결정자를 제거하면 보이스/코드 정규형이 된다.

이 글은 데이터베이스 개론 (저자 김연희)의 내용을 개인적으로 정리하는 글임을 알립니다.

데이터데이스 설계 단계

데이터베이스 설계는 사용자들의 요구 사항을 고려하여 데이터베이스를 생성하는 과정이다.

사용자가 데이터베이스를 실제로 사용하면 구조를 변경하기 어렵기 때문에 설계 과정에서부터 품질 좋은 데이터베이스를 생성해야 한다.

품질 좋은 데이터베이스를 평가하는 기준은 실제로 사용하는 구성원들의 요구사항을 만족하는지가 대표적인 기준이 된다.

관계 데이터 모델을 기반으로 두고 데이터베이스를 설계할 때는 두 가지 방법을 주로 사용한다.

• E-R 모델과 릴레이션 변환 규칙을 이용한 데이터베이스 설계
• 정규화를 이용한 데이터베이스 설계

이 글에서는 E-R 모델과 릴레이션 변환 규칙을 이용한 데이터베이스 설계를 다룬다.

E-R 모델과 릴레이션 변환 규칙을 이용한 데이터베이스 설계는 아래와 같은 5단계로 진행된다.

하지만 그림처럼 한 방향으로만 순서대로 진행되지는 않는다.

설계 과정 중에 오류를 발견하여 변경이 필요하면 이전 단계로 되돌아가 설계 내용을 변경할 수도 있다.

1단계 : 요구 사항 분석

요구 사항 분석 단계에서는 조직의 구성원들이 데이터베이스를 사용하는 용도를 파악한다.

즉 데이터베이스를 사용해 실제 업무를 처리하는 사용자에게서 필요한 데이터의 종류와 처리 방법 같은 다양한 요구사항을 수집한다.

2단계 : 개념적 설계(가장 중요)

개념적 설계 단계에서는 요구 사항 분석 단계에서 파악한 사용자의 요구 사항을 개념적 데이터 모델을 이용해 표현한다.

개념적 데이터 모델은 개발에 사용할 DBMS의 종류에 독립적이면서, 중요한 데이터 요소와 데이터 요소 간의 관계를 표현할 때 사용한다.

일반적으로 개념적 데이터 모델은 E-R 모델을 많이 사용하는데, E-R 모델은 중요한 데이터 요소와 데이터 요소 간의 관계를 E-R 다이어 그램으로 표현한다.

사용자의 요구 사항을 분석한 결과를 E-R 다이어그램으로 표현하는 것이 개념적 설계 단계에서 수행하는 주요 작업이다.

E-R 다이어그램과 같이 개념적 데이터 모델로 표현한 결과물을 개념적 구조 또는 개념적 스키마라고 한다.

3단계 : 논리적 설계 단계

논리적 설계 단계에서는 개발에 사용할 DBMS에 적합한 논리적 데이터 모델을 이용해 개념적 설계 단계에서 생성한 개념적 구조를 기반으로 논리적 구조를 설계한다.

DBMS의 종류에 따라 여러 모델을 사용할 수 있는데, 일반적으로 관계 데이터 모델을 많이 사용한다.

그러므로 관계 데이터 모델을 사용한다면 개념적 설계 단계에서 생성한 E-R 다이어그램을 릴레이션(테이블) 스키마로 변환하여 DBMS가 처리할 수 있도록 하는 것이 논리적 설계 단계에서 수행하는 주요 작업이다.

논리적 설계 단계에서 E-R 다이어그램을 릴레이션 스키마로 변환하는 작업을 논리적 모델링 또는 데이터 모델링이라 한다.

릴레이션 스키마와 같이 논리적 데이터 모델로 표현된 결과물을 논리적 구조 또는 논리적 스키마라고 한다.

4단계 : 물리적 설계

물리적 설계 단계에서는 논리적 설계 단계에서 생성된 논리적 구조를 기반으로 물리적 구조를 설계한다.

데이터베이스의 물리적 구조는 데이터베이스를 저장 장치에 실제로 저장하기 위한 내부 저장 구조와 접근 경로 등을 의미한다.

그러므로 물리적 설계 단계에서는 저장 장치에 적합한 저장 레코드와 인덱스의 구조 등을 설계하고, 저장된 데이터와 인덱스에 빠르게 접근하게 할 수 있는 탐색 기법 등을 정의한다.

데이터베이스를 실제로 구축할 컴퓨터 시스템의 저장 장치와 운영체제의 특성을 고려하여, 효율적인 성능을 지원하면서도 사용할 DBMS로 구현이 가능한 물리적인 구조를 설계하는 것이 물리적 설계 단계에서 수행하는 주요 작업이다.

물리적 설계의 결과물인 물리적 구조를 내부 스키마 또는 물리적 스키마라고 한다.

5단계 : 구현

이전 설계 단계의 결과물을 기반으로 DBMS에서 SQL로 작성한 명령문을 실행하여 데이터베이스를 실제로 생성한다.

이때 사용되는 SQL문은 테이블이나 인덱스 등을 생성할 때 사용되는 데이터 정의어(DDL)이다.

요구 사항 분석

데이터베이스에 대한 사용자들의 요구 사항을 수집하고 분석하여, 개발할 데이터베이스의 용도를 명확히 파악하는 게 이 단계의 목표이다.

그리고 분석한 사용자의 요구 사항의 내용을 요구 사항 명세서로 작성하여 이후 설계 단계에서 기초 자료로 활용한다.

개념적 설계

요구 사항 분석 단계의 결과물을 개념적 데이터 모델을 이용하여 표현한다.

• 개념적 데이터 모델 : 요구 사항에 대해 분석한 결과를 바탕으로 중요한 데이터를 추출하고 데이터 요소 간의 관계를 파악한 것

이 단계에서는 DBMS의 종류는 상관이 없다.

이 단계에서 주요 작업은 요구 사항 분석 결과를 기반으로 현실 세계에서 중요한 데이터 요소인 개체를 추출한 후 개체 간의 관계를 결정하여 이를 E-R 다이어그램으로 표현하는 것이다.

• 개념적 모델링 : 사용자의 요구 사항을 개념적 데이터 모델로 변환하는 작업
• 개념적 스키마(개념적 구조) : ERD와 같이 개념적 데이터 모델로 표현된 개념적 설계의 결과물

E-R 모델을 이용해 개념적 모델링을 하려면 먼저 E-R 모델의 핵심 요소인 개체를 추출하고 그다음 각 개체의 주요 속성과 키 속성을 선별하고, 개체 간의 관계를 결정해야 한다.

개체, 속성, 관계를 선별하는 작업이 모두 완료되면 그 결과를 ERD로 표현한다.

개체와 속성 추출

개체는 현실 세계에서 어떤 조직을 운영하는 데 꼭 필요한 사람, 사물과 같이 구별되는 모든 것을 의미한다.

요구 사항 명세서에서 개체를 추출할 때는, 먼저 명세서의 명사를 추출한다.

단, 조직의 업무 처리와 관련이 적은 일반적이고 광범위한 의미의 명사는 제외한다.

위 그림에서의 파란색 글씨로 된 것들이 명사이다.(중복된 명사는 제외)

추출된 모든 명사가 개체가 되는 것은 아니다.

명사 중에서 개체와 속성으로 분류되는 단어도 존재하기 때문에, 추출한 명사를 개체와 속성으로 정확히 분류하는 작업이 필요하다.

이렇게 개체와 속성을 분류하였으면 ERD로 나타내기 위해 도형으로 변환한다.

속성에 밑줄이 그어져 있다면, 그 속성은 해당 개체의 키 속성을 의미한다.

관계 추출

개체와 속성을 추출하고 나면 개체 간의 관계를 결정할 수 있다.

관계는 개체 간의 의미 있는 연관성이다.

일반적으로 관계는 요구 사항을 표현한 문장에서 동사로 표현한다.

단, 조직의 업무 처리와 관련하여 개체 간의 연관성을 의미 있게 표현한 동사만 선택하고, 의미가 같은 동사가 여러 개이면 대표 동사 하나만 선택한다.

'주문할 수 있다'는 회원 개체와 상품 개체가 맺는 관계를 설명하므로 이를 통해 회원 개체와 상품 개체가 맺고 있는 주문 관계를 추출할 수 있다.

회원 한 명이 여러 상품을 주문할 수 있고, 하나의 상품을 여러 회원이 주문할 수 있다고 했으므로 회원 개체와 상품 개체가 맺는 주문 관계는 다대다(n:m) 관계가 된다.

회원이 상품을 반드시 주문해야 하는 것은 아니므로 회원 개체는 주문 관계에 있어서 선택적으로 참여한다고 볼 수 있다.

그리고 회원이 주문 하지 않은 상품이 존재할 수 있으므로 상품 개체도 주문 관계에 선택적으로 참여한다고 볼 수 있다.

'공급할 수 있다'는 상품 개체와 제조업체 개체가 맺는 관계를 설명하므로 이를 통해 상품 개체와 제조업체 개체가 맺고 있는 공급 관계를 추출할 수 있다.

하나의 상품은 제조업체 하나가 공급하고, 제조업체 하나는 여러 상품을 공급할 수 있다고 했으므로 제조업체 개체와 상품 개체가 맺는 공급 관계는 일대다(1:n)가 된다.

제조업체가 상품을 공급하면 상품은 무조건 존재하므로 상품 개체는 공급 관계에 필수적으로 참여한다고 볼 수 있다.

그리고 상품을 공급하지 않는 제조업체도 존재할 수 있으므로 제조업체 개체는 공급 관계에 선택적으로 참여한다고 볼 수 있다.

'작성할 수 있다'는 회원 개체와 개시글 개체가 맺는 관계를 설명하므로 이를 통해 회원 개체와 개시글 개체가 맺고 있는 작성 관계를 추출할 수 있다.

회원 한 명이 게시글을 여러 개 작성할 수 있고, 게시글 하나는 한 명의 회원만 작성할 수 있다고 했으므로 회원 개체와 게시글 개체가 맺는 작성 관계는 일대다(1:n)가 된다.

회원이 게시글을 반드시 작성해야 하는 것은 아니므로 회원 개체는 작성 관계에 선택적으로 참여한다고 볼 수 있다.

그리고 게시글은 회원이 작성하면 무조건 존재 하므로 게시글 개체는 작성 관계에 필수적으로 참여한다고 볼 수 있다.

ERD에서 관계는 마름모로 표현하고, 사각형으로 표현된 개체와 선으로 연결한다.

그리고 일대일, 일대다, 다대다 관계는 선 위에 레이블로 표시한다.

필수적으로 참여하는 개체는 개체와 관계를 이중선으로 연결한다.

ERD 작성

한빛 마트의 데이터베이스에 대한 요구 사항 명세서에 추출한 개체, 속성, 관계를 하나의 ERD로 표현한 결과는 아래와 같다.

즉, 개념적 설계 단계의 결과물인 개념적 스키마다.

논리적 설계

논리적 설계 단계에서는 DBMS에 적합한 논리적 데이터 모델을 이용해서, 개념적 설계 단계에서 생성한 개념적 스키마를 기반으로 논리적 스키마를 설계한다.

즉, DBMS에 독립적인 개념적 스키마를 기반으로 하여 개발에 사용할 DBMS가 처리할 수 있는 데이터베이스의 논리적 구조를 설계하는 것이 논리적 설계 단계의 목표다.

일반적으로 모델은 관계 데이터 모델을 많이 사용한다.

ERD를 관계 데이터 모델의 릴레이션 스키마, 즉 테이블 스키마로 변환하는 작업을 한다.

• 논리적 모델링 : 논리적 설계 단계에서 E-R 다이어그램을 릴레이션 스키마로 변환하는 작업
• 논리적 구조 또는 논리적 스키마  : 릴레이션 스키마와 같이 논리적 데이터 모델로 표현된 결과물

릴레이션 스키마 변환 규칙

릴레이션 스키마 대신 간단히 릴레이션이라는 용어를 주로 사용하겠다.

규칙1 : 모든 개체는 릴레이션으로 변환한다.

개체의 이름을 릴레이션의 이름으로 하고, 개체가 가진 속성도 릴레이션의 속성으로 그대로 변환한다.

단, 개체가 가지고 있는 속성이 복합 속성인 경우에는 복합 속성을 구성하고 있는 단순 속성만 릴레이션의 속성으로 변환한다.

개체가 가지고 있는 키 속성은 릴레이션의 기본키로 변환한다.

규칙 2 : 다대다(n:m) 관계는 릴레이션으로 변환한다.

ERD에 있는 다대다 관계를 하나의 릴레이션으로 변환한다.

관계의 이름을 릴레이션의 이름으로 하고, 관계의 속성도 릴레이션의 속성으로 그대로 변환한다.

단, 관계를 맺고 있는 개체가 무엇인지 중요하므로, 관계를 맺고 있는 개체들을 규칙 1에 따라 변환한 후 이 릴레이션들의 기본키를 관계 릴레이션에 포함시키고 외래키로 지정한다.

그리고 이 외래키들을 조합하여 관계 릴레이션의 기본키로 지정한다.

외래키로 지정할 때는 가져온 기본키들의 이름이 같을 경우 하나는 이름을 변경해야 한다. 한 릴레이션에 있는 속성은 이름이 모두 달라야 하기 때문이다.

하지만 속성의 이름만 달라질 뿐 속성의 도메인은 변하지 않으므로 외래키로 사용하는 데 문제가 발생하지 않는다.

규칙 3 : 일대다(1:n) 관계는 외래키로 표현한다.

ERD에 있는 일대다 관계는 릴레이션으로 변환하지 않고 외래키로만 표현한다.

단, 약한 개체가 참여하는 일대다 관계는 일반 개체가 참여하는 경우와 다르게 처리해야 하므로 규칙 3을 아래와 같이 세부 규칙으로 나누어 적용한다.

규칙 3-1 : 일반적인 일대다 관계는 외래키로 표현한다.

일반 개체들이 참여하는 일대다 관계는 릴레이션으로 변환하지 않고 외래키로만 표현한다.

관계를 맺고 있는 개체들은 규칙 1에 따라 변환한 릴레이션 중에서, 일대다 관계의 1측 개체 릴레이션의 기본키를 가져와 n 측 개체 릴레이션에 포함시키고 외래키로 지정한다.

관계의 속성들도 n측 개체의 릴레이션에 포함시킨다.

단, 외래키나 관계의 속성을 포함시킬 때 해당 릴레이션의 원래 속성과 이름이 같으면 이름을 변경해야 한다.

만약 n측 개체의 릴레이션의 기본키를 가져와 1측 개체 릴레이션에 외래키로 포함시키면 해당 외래키가 다중 값을 가져 릴레이션의 특성을 위반하게 된다.

그러므로 반드시 1측 개체 릴레이션의 기본키를 n 측 개체 릴레이션의 외래키로 지정해야 한다.

규칙 3-2 : 약한 개체가 참여하는 일대다 관계는 외래키를 포함해서 기본키로 지정한다.

약한 개체가 참여하는 일대다 관계도 릴레이션으로 변환하지 않고 외래키로만 표현한다.

이때, 일대다 관계의 1측 개체 릴레이션의 기본키를 가져와 n 측 개체 릴레이션에 포함시키고 외래키로 지정한다.

관계의 속성들도 n측 개체 릴레이션에 포함시킨다.

일반 개체들이 참여하는 일대다 관계와 다른 점은, 외래키가 포함된 릴레이션에서 이 외래키를 포함하여 기본키를 지정해야 한다는 점이다.

즉, n측 개체 릴레이션이 가지고 있던 키 속성과 외래키 속성을 조합하여 기본키로 지정한다.

약한 개체는 강한 개체에 따라 존재 여부가 결정되는 만큼 강한 개체의 기본키를 이용해 식별하는 것이다.

그러므로 강한 개체인 1측 개체의 릴레이션의 기본키를 포함해서 약한 개체의 기본키를 지정한다.

규칙 4 : 일대일(1:1) 관계를 외래키로 표현한다.

ERD에 있는 일대일 관계도 일대다 관계처럼 릴레이션으로 변환하지 않고 외래키로만 표현한다.

이때, 데이터의 중복을 피하려면 개체가 관계에 참여하는 특성에 따라 약간 다르게 처리해야 하므로 규칙 4를 아래와 같이 3개의 세부 규칙으로 나누어 적용한다.

규칙 4-1 : 일반적인 일대일 관계는 외래키를 서로 주고받는다.

일반적인 일대일 관계는 릴레이션으로 변환하지 않고 외래키로만 표현한다.

관계를 맺는 개체들을 규칙 1에 따라 변환한 릴레이션들이 서로의 기본키를 주고받아 이를 외래키로 지정한다.

이때, 관계가 가지는 속성들은 관계에 참여하는 개체를 변환한 릴레이션에 모두 포함시킨다.

규칙 4-2 : 일대일 관계에 필수적으로 참여하는 개체의 릴레이션만 외래키를 받는다.

일대일 관계를 맺고 있는 두 개체 중 관계에 필수적으로 참여하는 개체에 대응하는 릴레이션에만 외래키를 포함시킨다.

즉, 관계에 필수적으로 참여하는 개체에 해당하는 릴레이션이 선택적으로 참여하는 개체에 해당하는 릴레이션의 기본키를 받아 외래키로 지정한다.

이때, 관계가 가지고 있는 속성들도 관계에 필수적으로 참여하는 개체에 해당하는 릴레이션에 함께 포함시킨다.

관계에 선택적으로 참여하는 개체에 해당하는 릴레이션이 외래키를 가지면 관계를 표현하는데 문제는 없지만 관계에 선택적으로 참여하기 때문에 릴레이션이 실제로 구축되고 난 후에 외래키로 지정된 속성에는 널 값이 저장되는 경우가 많을 것이다.

그러므로 관계에 반드시 참여하는 개체에 대응하는 릴레이션이 외래키를 가지도록 하는 것이 좋다.

위 그림은 남자는 꼭 결혼해야 하고 여자는 결혼하지 않아도 되는 법이 있는 나라에서 작성될 수 있는 ERD이다.

규칙 4-3 : 모든 개체가 일대일 관계에 필수적으로 참여하면 릴레이션 하나로 합친다.

일대일 관계를 맺고 있는 두 개체가 모두 관계에 필수적으로 참여한다면 그만큼 관련성이 있는 개체라는 의미다.

그러므로 두 개체에 해당하는 두 릴레이션을 하나로 합쳐 표현한다.

관계의 이름을 릴레이션의 이름으로 사용하고, 관계에 참여하는 두 개체의 속성들도 관계 릴레이션에 모두 포함시킨다.

그리고 두 개체 릴레이션의 키 속성을 조합하여 관계 릴레이션의 기본키로 지정한다.

규칙 5 : 다중 값 속성은 릴레이션으로 변환한다.

관계 데이터 모델의 릴레이션에서는 다중 값을 가지는 속성을 허용하지 않는다.

그러므로 ERD에 있는 다중 값 속성은 그 속성을 가지고 있는 개체에 해당하는 릴레이션이 아닌 별도의 릴레이션을 만들어 포함시킨다.

새로 만들어진 릴레이션에는 ERD에서 다중 값 속성으로 표현된 속성뿐 아니라 그 속성을 가지고 있는 개체에 해당하는 릴레이션의 기본키를 가져와 포함시키고 이를 외래키로 지정한다.

새로 만들어진 릴레이션의 이름은 자유롭게 정하고, 기본키는 다중 값 속성과 외래키를 조합하여 지정한다.

기타 고려 사항

기본 변환 규칙에서는 다대다 관계만 릴레이션으로 변환하였지만 일대일, 일대다 관계도 릴레이션으로 변환할 수 있다.

특히, 속성이 많은 관계는 관계 유형에 상관없이 릴레이션으로 변환하는 것을 고려할 수 있다.

릴레이션 스키마 변환 규칙을 이용한 논리적 설계

논리적 설계를 위해 ERD를 릴레이션 스키마로 변환할 때는 변환 규칙을 순서대로 적용하면 된다.

해당되지 않는 규칙은 제외하고 다음으로 넘어간다.

먼저 규칙 1에 따라 4개의 개체를 개별 릴레이션으로 변환한다.

규칙 1을 적용한 결과에 규칙 2를 적용한다.

규칙 2는 다대다 관계를 릴레이션으로 변환하는 것이다.

규칙 2에 따라 회원 개체와 상품 개체가 참여하는 주문 관계를 릴레이션으로 변환해야 한다.

이제 규칙 2를 적용한 결과에 계속해서 규칙 3을 적용해야 한다.

규칙 3은 일대다 관계를 외래키로 표현하는 것이다.

한 빛 마트의 ERD에는 일대다 관계인 공급 관계와 작성 관계가 존재한다.

주문 릴레이션이 새로 추가된 것을 확인할 수 있다.

이제 규칙 3까지 적용한 결과에 규칙 4와 규칙 5를 적용할 차례다.

그런데 한빛 마트의 ERD에는 일대일 관계가 없으므로 규칙 4를 적용할 필요가 없다.

그리고 다중 값 속성도 없으므로 규칙 5도 적용할 필요가 없다.

그러므로 한빛 마트 ERD는 논리적 모델링 과정을 통해 5개의 릴레이션 스키마로 변환된다.

릴레이션 스키마에 대해 속성의 데이터 타입과 길이, 널 값 허용 여부, 기본값, 제약조건 등을 결정하는 것도 논리적 설계 단계에 수행하는 작업이다.

물리적 설계와 구현

• 물리적 설계 단계: 저장 장치에 적합한 저장 레코드와 인덱스의 구조 등을 설계하고, 저장된 데이터와 인덱스에 빠르게 접근하게 할 수 있는 탐색 기법 등을 정의
• 구현 단계 : DBMS에서 SQL로 작성한 명령문을 실행하여 데이터베이스를 실제로 생성

이 글은 데이터베이스 개론 (저자 김연희)의 내용을 개인적으로 정리하는 글임을 알립니다.

관계 데이터 모델의 개념

관계 데이터 모델의 기본 용어

Entiti, Table, Relation

엔터티 = 테이블 = 릴레이션으로 통칭해서 사용되기도 한다.

DB의 설계 단계에서는 엔터티(Entity), DBMS로 구현되는 단계에서는 테이블(Table), 개념 단계에서 엔터티 간 연관관계를 릴레이션(Relation)이라고 한다.

엔티티 > 테이블 > 릴레이션 순으로 보면된다.
모든 릴레이션은 테이블이지만, 모든 테이블이 릴레이션인 것은 아니다.
모든 테이블은 엔티티이지만, 모든 엔티티가 테이블인것은 아니다.

관계 데이터 모델에서는 하나의 개체에 관한 데이터를 릴레이션(relation) 하나에 담아 데이터 베이스에 저장한다.

관계형 데이터베이스에서의 "릴레이션"은 데이터베이스 테이블의 구조를 설명하는 데 사용되는 개념이다.
릴레이션 (Relation): 관계형 데이터베이스에서 릴레이션은 테이블을 나타낸다.
테이블은 행과 열로 이루어진 데이터 구조이다. 각 테이블은 여러 개의 열(속성 또는 필드)과 이에 해당하는 데이터 레코드(행)로 구성된다.
이러한 행과 열의 집합은 릴레이션으로 간주된다.

Attribute (열)

릴레이션의 열(column)을 속성 또는 애트리뷰트(attribute)라고 부른다.

각 속성은 서로 다른 이름을 이용해 구별한다.

릴레이션은 파일 시스템에서의 파일, 속성은 해당 파일의 필드(field)에 대응하는 개념이다.

Tuple (행)

릴레이션의 행을 튜플(tuple)이라 부른다.

튜플은 개체의 인스턴스다.

튜플은 파일 시스템에서의 파일의 레코드(record)에 대응하는 개념이다.

Domain

속성 하나가 가질 수 있는 모든 값의 집합을 해당 속성의 도메인(domain)이라 한다.

예를 들어 학년 속성의 데이터 타입이 정수형이고 해당 속성에서 취할 수 있는 값의 범위가 1~4까지 라면, 1~4라는 범위는 해당 속성에 지정된 정수형의 모든 범위가 아니라 일부분이므로 사용자는 1~4까지의 범위를 해당 속성의 도메인으로 정의해서 사용할 수 있다는 의미이다.

즉, 도메인은 각 속성이 가질 수 있도록 허용된 값들의 집합이다.

테이블의 컬럼 값을 구성할 때 값의 범위, 데이터타입, 제약사항 등을 설정하는데 그 범위 값의 설정을 도메인이라 생각하면 된다.

NULL 값

릴레이션에 있는 특정 튜플의 속성 값을 모르거나, 적합한 값이 없는 경우에는 널이라는 특별한 값을 사용할 수 있다.

널 값은 특정 속성에 해당되는 값이 없음을 나타내므로 숫자 0이나 공백 문자와는 다르다.

Degree (차수)

하나의 릴레이션에서 속성의 전체 개수를 릴레이션의 차수라고 한다.

모든 릴레이션은 최소 1 이상의 차수를 유지해야 한다.

릴레이션의 차수는 일반적으로 자주 변하지 않는다는 정적인 특징이 있다.

Cardnality(카디널리티)

하나의 릴레이션에서 튜플의 전체 개수를 릴레이션의 카디널리티라고 한다.

튜플이 없는 릴레이션이 존재할 수도 있다.

릴레이션의 카디널리티는 일반적으로 자주 변한다는 동적인 특징이 있다.

릴레이션과 데이터베이스의 구성

관계 데이터 모델에서 릴레이션은 릴레이션 스키마와 릴레이션 인스턴스로 구성되어 있다.

릴레이션 스키마

릴레이션 스키마(relation schema)는 릴레이션의 이름과 릴레이션에 포함된 모든 속성의 이름으로 정의하는 릴레이션의 논리적 구조다.

릴레이션 스키마는 DBMS가 내부적으로 데이터 정의어를 이용해 정의하지만, 일반적으로는 아래와 같은 형태로 쉽게 표현한다.

릴레이션 스키마는 릴레이션 내포(relation intension)라고 부른다.

릴레이션 인스턴스

릴레이션 인스턴스(relation instance)는 어느 한 시점에 릴레이션에 존재하는 튜플들의 집합이다.

릴레이션 인스턴스에 포함된 튜플은 릴레이션 스키마에서 정의하는 각 속성에 대응하는 실제 값으로 구성되어 있다.

릴레이션 인스턴스를 보면 현재 릴레이션의 실제 내용을 쉽게 파악할 수 있다.

릴레이션 인스턴스는 간단히 릴레이션이라 부르기도 하고 릴레이션 외연(relation extension)이라고도 부른다.

데이터베이스 스키마와 데이터베이스 인스턴스

일반적으로 데이터베이스는 릴레이션 여러 개로 구성된다.

데이터베이스의 전체 구조를 의미하는 데이터베이스 스키마는 데이터베이스를 구성하는 릴레이션의 스키마를 모아놓은 것이다.

즉, 특정 데이터베이스 스키마를 설계한다는 것은 모든 필요한 릴레이션의 스키마를 모두 정의한다는 뜻이다.

데이터베이스 인스턴스는 어느 한 시점에서 데이터베이스에 저장된 데이터 내용의 전체 집합을 의미한다.

즉, 데이터베이스를 구성하는 모든 릴레이션의 인스턴스를 모아놓은 것이다.

릴레이션의 특성

튜플의 유일성

하나의 릴레이션에는 동일한 튜플이 존재할 수 없다.

하나의 릴레이션에 똑같은 튜플이 있으면 안 되고, 모든 튜플에는 다른 튜플과 구별되는 유일한 특성이 있어야 한다.

릴레이션을 튜플의 모임인 집합의 개념으로 이해한다면, 하나의 집합에 동일한 원소가 존재할 수 없다는 특성과 연관 지어 생각할 수 있다.

튜플을 유일하게 구별하기 위해 선정하는 속성(또는 속성들의 모임)을 키(key)라고 부른다.

튜플의 무순서

하나의 릴레이션에서 튜플 사이의 순서는 무의미하다.

튜플 순서가 바뀐다고 다른 릴레이션이 될 수 없고, 순서와 상관없이 튜플 내용이 같아야 같은 릴레이션이다.

데이터베이스는 위치가 아닌 내용으로 검색되므로 튜플의 순서는 중요하지 않다.

릴레이션에는 튜플이 삽입 순서에 따라 저장되지만, 효율적인 처리를 위해 튜플의 순서를 임의로 바꾸기도 한다.

속성의 무순서

하나의 릴레이션에서 속성 사이의 순서는 무의미하다.

속성은 순서가 바뀌어도 다른 릴레이션이 될 수 없고, 순서와 상관없이 같은 속성들로 구성되어 있어야 같은 릴레이션이다.

속성의 원자성

모든 속성 값은 더는 분해할 수 없는 하나의 값, 즉 원자 값만 가질 수 있다.

하나의 속성은 여러 개의 값, 즉 다중 값을 가질 수 없다.

위와 그림과 같은 고객 릴레이션은 회사원, 학생과 같이 값이 여러 개인 직업 속성을 포함하므로 관계 데이터 모델의 릴레이션으로 적합하지 않다.

물론 현실에서는 직업이 둘 이상인 고객이 존재할 수 있지만, 관계 데이터 모델은 이런 복잡한 개념을 배제하고 릴레이션을 단순한 구조로 정의하고자 하는 특징이 있어 다중 값을 허용하지 않는다.

키의 종류

튜플을 유일하게 구별하기 위해 모든 속성을 이용하는 것보다 일부 속성만 이용하는 것이 효율성을 높일 수 있다.

릴레이션에 포함된 튜플들을 유일하게 구별해주는 역할은 속성 또는 속성들의 집합인 키가 담당한다.

키는 관계 데이터 모델에서 중요한 제약조건을 정의한다.

• 유일성 : 하나의 릴레이션에서 키로 지정된 속성 값은 튜플마다 달라야 한다는 특성
• 최소성 : 꼭 필요한 최소한의 속성들로만 키를 구성하는 특성

관계 데이터 모델에서는 키를 아래와 같이 슈퍼키, 후보키, 기본키, 대체키, 외래키의 다섯 가지로 분류할 수 있다.

슈퍼키(Super Key)

슈퍼키는 유일한 특성을 만족하는 속성 또는 속성들의 집합이다.

유일성은 키가 갖추어야 하는 기본 특성으로, 하나의 릴레이션에서 키로 지정된 속성 값은 튜플마다 달라야 한다는 의미다.

즉, 키 값이 같은 튜플은 존재할 수 없다.

후보키(Candidate Key)

후보키는 유일성과 최소성을 만족하는 속성 또는 속성들의 집합이다.

최소성은 꼭 필요한 최소한의 속성들로만 키를 구성하는 특성이다.

그러므로 하나의 속성으로 구성된 키는 당연히 최소성을 만족한다.

기본키(Primary Key)

릴레이션에서 튜플을 구별하기 위해 여러 개의 후보키를 모두 사용할 필요는 없다.

데이터베이스 설계자나 관리자는 여러 후보키 중에서 기본적으로 사용할 키를 반드시 선택해야 하는 데 이것이 기본 키다.

만약, 후보키가 1개만 존재한다면 당연히 해당 후보키를 기본키로 선택해야 하겠지만 여러 개일 경우에는 데이터베이스 사용 환경을 고려하여 적합한 것을 기본키로 선택하면 된다.

선택한 기본키는 속성 이름에 밑줄을 그어 표현한다.

후보키 중에서 기본키를 선택하는 기준은 아래와 같다.

• 널 값을 가질 수 있는 속성이 포함된 후보키는 기본키로 부적합하다.
• 값이 자주 변경될 수 있는 속성이 포함된 후보키는 기본키로 부적합하다.
• 단순한 후보키를 기본키로 선택한다.

대체키(Alternate Key)

대체키는 기본키로 선택되지 못한 후보키다.

대체키는 기본키를 대신할 수 있지만 기본 키가 되지 못하고 탈락한 이유가 있을 수 있다.

외래키(Foreign Key)

외래키는 어떤 릴레이션에 소속된 속성 또는 속성 집합이 다른 릴레이션의 기본키가 되는 키다.

즉, 다른 릴레이션의 기본키를 그대로 참조하는 속성의 집합이 외래키다.

외래키는 릴레이션들 사이의 관계를 올바르게 표현하기 위해 필요하다.

외래키가 다른 테이블의 대체키를 참조하는 것도 가능하다.
기본키로 선택받지 못했지만 유일성과 최소성을 만족하는 대체키를 참조하더라도 관련 있는 튜플을 구분할 수 있기 때문이다.

하나의 릴레이션에는 외래키가 여러 개 존재할 수도 있다.

외래키를 기본키로 사용할 수도 있고 외래키를 포함하여 기본키를 구성할 수도 있다.

외래키가 다른 릴레이션의 기본키를 참조하는 키라고 정의했지만 반드시 다른 릴레이션을 참조할 필요는 없다.

참조하는 릴레이션과 참조되는 릴레이션이 같을 수도 있다.

즉, 외래키 자신이 속한 릴레이션의 기본키를 참조하도록 외래키를 정의할 수도 있다.

외래키는 다른 릴레이션의 기본키를 참조하지만 이 릴레이션에서는 기본키가 아니기 때문에 널 값을 가질 수 있다.

관계 데이터 모델의 제약 

관계 데이터 모델에서 정의하고 있는 기본 제약 사항은 키와 관련한 무결성 제약조건이다.

무결성은 데이터에 결함이 없는 상태, 즉 데이터가 정확하고 유효하게 유지된 상태를 말한다.

무결성 제약조건의 주요 목적은 데이터 베이스에 저장된 데이터의 무결성을 보장하고, 데이터베이스의 상태를 일관되게 유지하는 것이다.

데이터베이스가 삽입, 삭제, 수정 연산으로 상태가 변하더라도 무결성 제약조건은 반드시 지켜져야 한다.

관계 데이터 모델이 기본으로 포함하고 있는 무결성 제약조건에는 개체 무결성 제약조건과 참조 무결성 제약조건이 있다.

데이터 베이스의 상태를 일관성 있게 유지하기 위해서는 두 가지를 모두 만족시켜야 한다.

개체 무결성 제약조건(Entity Integrity Constraint)

개체 무결성 제약 조건은 기본키를 구성하는 모든 속성은 널 값을 가지면 안 된다는 규칙이다.

아래의 고객 릴레이션은 개체 무결성 제약조건을 위반한 예가 된다.

그러므로 이 상태의 릴레이션은 실제로 존재할 수 없다.

개체 무결성 제약조건을 만족시키려면 새로운 튜플이 삽입되는 연산과 기존의 튜플의 기본키 속성 값이 변경되는 연산이 발생할 때 기본키에 널 값이 포함되는 상황에서는 연산의 수행을 거부하면 된다.

새로운 튜플(레코드)이 삽입되는 경우
기본키 속성에 널(null) 값이 포함된 새로운 행을 추가하려고 할 때, 데이터베이스는 이 작업을 거부해야 한다.
즉, 기본키는 널 값을 허용하지 않으므로 새로운 레코드가 추가될 때 기본키 속성에는 반드시 유효한 값이 포함되어야 한다.

기존의 튜플(레코드)의 기본키 속성 값이 변경되는 경우
이미 존재하는 레코드의 기본키 속성 값이 널(null)이거나 변경될 때, 이 변경 작업은 거부되어야 한다.
기본키는 해당 레코드를 고유하게 식별하는 데 사용되므로, 기본키 속성은 항상 유효한 값을 가져야 하며, 변경되는 과정에서 널 값이 들어가면 안 된다.

즉, 개체 무결성을 유지하기 위해서는 기본키 속성에 널(null) 값을 허용하지 않고, 새로운 레코드 삽입 또는 기존 레코드의 기본키 속성 값 변경 시에 이를 감지하고 거부하는 것이 중요하다.

이것은 일반 사용자가 직접 수행하기보다는 DBMS가 자동으로 수행하므로 새로운 릴레이션을 생성할 때마다 기본키를 어떤 속성들로 구성할 것인지 DBMS에게 알려주면 된다.

참조 무결성 제약조건(Referential Integrity Constraint)

개체 무결성 제약조건이 기본키에 대한 규칙으로 각 릴레이션마다 적용된다면, 참조 무결성 제약조건은 외래키에 대한 규칙으로 연관된 릴레이션들에 적용된다.

참조 무결성 제약조건이란 외래키는 참조할 수 없는 값을 가질 수 없다는 규칙이다.

외래키는 다른 릴레이션의 기본키를 참조하는 속성이고 릴레이션 간의 관계를 표현하는 역할을 한다.

그런데 외래키가 자신이 참조하는 릴레이션의 기본키와 상관이 없는 값을 가지게 되면 두 릴레이션을 연관시킬 수 없으므로 외래키 본래의 의미가 없어진다.

그러므로 외래키는 자신이 참조하는 릴레이션에 기본키 값으로 존재하는 값, 즉 참조 가능한 값만 가져야 한다.

아래의 그림은 참조 무결성 제약조건을 위반한 예이다.

외래키가 널 값을 가진다고 해서 참조 무결성 제약조건을 위반했다고 말할 수 없다.

위 그림에서 주문고객 속성 값이 널이라는 것은 주문한 고객이 누구인지 모를 뿐, 고객 릴레이션에 존재하지 않는 고객이 주문한 것으로 판단하기는 어렵기 때문이다.

또한 참조 릴레이션에 존재하는 튜플을 삭제하는 연산은 참조 무결성 제약 조건을 위반하지 않는 경우에만 수행한다.

위 그림을 예로 들면, 고객 릴레이션의 apple이라는 아이디를 가진 고객을 삭제해 버리면 주문 릴레이션의 주문고객의 apple가 null로 바뀌어 버린다. 이는 참조 무결성 제약조건을 위반한 것이다.

마지막으로 참조 릴레이션에 존재하는 기본키의 속성 값이 변경될 때 참조 무결성 제약 조건을 위반하지 않는지 확인해야 한다.

위 그림을 예로 들면, 고객 릴레이션의 기본키인 고객 아이디 속성의 값을 바꾸면 주문 릴레이션에 원래의 속성 값으로 남아 있게 된다. 이는 참조 무결성 제약조건을 위반한 것이다.

이럴 때는 변경 연산을 수행하지 않거나, 주문 릴레이션에 남아 있는 관련 튜플에서 주문고객 속성의 값을 새로운 값으로 함께 변경해야 참조 무결성 제약조건을 만족시킬 수 있다.

이 글은 데이터베이스 개론 (저자 김연희)의 내용을 개인적으로 정리하는 글임을 알립니다.

데이터 모델링과 데이터 모델의 개념

데이터 모델링

현실 세계에 존재하는 데이터를 컴퓨터 세계의 데이터베이스로 옮기는 변환 과정을 데이터 모델링이라 한다.

현실 세계의 데이터를 컴퓨터 세계의 데이터베이스로 한 번에 옮기기는 쉽지 않다.

• 추상화 : 현실 세계의 데이터 중에서 중요한 데이터를 선별하는 작업

사람의 머릿속에 있는 현실 세계의 데이터 중에 중요한 데이터를 찾아 개념 세계로 옮기는 단계와 이를 컴퓨터 세계에 저장하는 구조를 결정해서 표현하는 단계로 나누어 진행한다.

1. 개념적 모델링 : 현실 세계의 데이터 중에 중요한 데이터를 찾아 개념 세계로 옮기는 단계
2. 논리적 모델링 : 개념 세계의 데이터를 데이터베이스에 저장할 구조를 결정하고 이 구조로 표현하는 작업

일반적으로 개념적 모델링과 논리적 모델링을 명확히 구분하지는 않고 합쳐서 데이터 모델링이라 부른다.

데이터 모델링은 데이터베이스 설계의 핵심 과정이다.

데이터 모델

데이터 모델링을 쉽게 할 수 있도록 도와주는 도구가 있는데 이것이 바로 데이터 모델이다.

• 데이터 모델 : 현실 세계의 데이터 구조를 컴퓨터 세계의 데이터 구조로 기술하는 도구

데이터 모델은 데이터 모델링의 결과물을 표현하는 도구로, 개념적 데이터 모델과 논리적 데이터 모델이 있다.

• 개념적 데이터 모델 : 사람의 머리로 이해할 수 있도록 현실 세계를 개념적 데이터로 모델링하여 데이터베이스의 개념적 구조로 표현하는 도구
• 논리적 데이터 모델 : 개념적 구조를 논리적 데이터 모델링하여 데이터베이스의 논리적 구조로 표현하는 도구

일반적으로 데이터 모델은 데이터 구조, 연산, 제약조건으로 구성된다.

데이터 모델에서는 보통 데이터 구조를 강조하지만, 적용 가능한 연산과 제약조건도 이해할 필요가 있다.

데이터 구조

• 개념적 데이터 모델에서 데이터 구조 : 현실 세계를 개념 세계로 추상화했을 때 어떤 요소로 이루어져 있는지를 표현하는 개념적 구조
• 논리적 데이터 모델에서 데이터 구조 : 데이터를 어떤 모습으로 저장할 것인지를 표현하는 논리적 구조
• 데이터 구조는 자주 변하지 않고 정적이라는 특징이 있다.

연산

• 데이터 구조에 따라 개념 세계나 컴퓨터 세계에서 실제로 표현된 값들을 처리하는 작업
• 값이 연산에 의해 계속 변경될 수 있으므로 동적이라는 특징이 있다.

제약조건

• 데이터 무결성을 유지하기 위함
• 구조적 측면의 제약 사항과 연산을 적용하는 경우 허용할 수 있는 의미적 측면의 제약 사항이 있다.

데이터 모델링과 데이터 모델을 아파트 건축에 비교하면

• 개념적 모델링  : 요구사항을 반영하여 설계도를 그리는 과정
• 개념적 데이터 모델  : 설계도를 그릴 때 사용하는 방법이나 도구
• 논리적 데이터 모델링 : 설계도를 토대로 모델하우스를 만드는 과정
• 논리적 데이터 모델  : 모델하우스를 만들 때 사용하는 방법이나 도구

보통 데이터 모델링과 논리적 데이터 모델링을 통틀어 데이터베이스 설계라고 한다.

데이터 모델링 과정을 통해 논리적 구조가 결정되면, 컴퓨터 저장 장치에 실제로 저장되는 형태를 의미하는 물리적 구조로 변환하는 작업을 통해 현실 세계의 데이터를 컴퓨터 세계의 데이터로 저장한다.

• 물리적 데이터 모델링 : 컴퓨터 저장 장치에 실제로 저장되는 형태를 의미하는 물리적 구조로 변환하는 작업

개념적 데이터 모델링과 논리적 데이터 모델링 작업을 지원하는 다양한 데이터 모델이 존재하는데, 사용하는 데이터 모델에 따라 현실 세계를 표현하는 개념적 구조나 논리적 구조의 모습이 달라진다.

개념적 데이터 모델 중 대표적으로 많이 사용되는 것이 개체-관계 모델(E-R Model : Entity-Relationship Model)이다.

개념적 데이터 모델
(개체-관계(Entity-Relationship) 모델)

개체-관계 모델이란 개체(entity)와 개체 간의 관계(Relationship)를 이용해 현실 세계를 개념적 구조로 표현하는 방법이다.

현실 세계를 개체-관계 모델을 이용해 개념적으로 모델링하여 그림으로 표현한 것을 개체-관계 다이어그램(ERD:Entity-Relationship Diagram)이라고 한다.

개체(Entity)

개체는 현실 세계에서 조직을 운영하는 데 꼭 필요한 사람이나 사물과 같이 구별되는 모든 것을 의미한다.

예를 들어, 서점을 개념적으로 모델링할 때 중요 데이터를 가지고 있는 사람인 고객과 중요 데이터를 가지고 있는 사물인 책이 개체가 된다.

개체는 사람과 사물처럼 물리적으로 존재하는 것만을 의미하지 않는다.

개념이나 사건처럼 개념적으로 존재하는 것도 개체가 될 수 있다.

예를 들어, 학교 운영에 필요한 데이터를 가지고 있는 학과나 과목은 물리적으로 존재하지 않지만 반드시 필요한 개념이기 때문에 개체가 될 수 있다.

개체는 다른 개체와 구별되는 이름을 가지고 있고, 각 개체만의 고유한 특성이나 상태, 즉 속성을 하나 이상 가지고 있다.

• 개체 타입  : 개체를 고유한 이름과 속성들로 정의한 것
• 개체 인스턴스(어커런스) : 개체를 구성하고 있는 속성이 실제로 값을 가짐으로써 실체화된 개체
• 개체 집합 : 특정 개체 타입에 대한 개체 인스턴스들을 모아 놓은 것

개체와 속성은 파일 구조에서 레코드(row)와 필드(column) 용어에 대응된다.

개체 타입은 레코드 타입에, 개체 인스턴스는 레코드 인스턴스에 대응한다.

ERD에서 개체를 사각형으로 표현하고 사각형 안에 개체의 이름을 표기한다.

속성(Attribute)

속성은 개체가 가지고 있는 고유한 특성이다.

속성은 그 자체만으로는 의미가 없지만 관련 있는 속성들을 모아 개체를 구성하면 하나의 중요한 의미를 표현할 수 있다.

속성은 일반적으로 의미 있는 데이터의 가장 작은 논리적 단위로 인식된다.

ERD에서 속성은 타원으로 표현하고, 타원 안에 속성의 이름을 표기한다.

속성은 아래와 같이 다양한 기준으로 분류할 수 있다.

단일 값 속성과 다중 값 속성

• 단일 값 속성 : 특정 개체를 구성하는 속성 값이 하나
• 다중 값 속성 : 특정 개체를 구성하는 속성이 여러개(ERD에서 이중 타원으로 표현)

단순 속성과 복합 속성

• 단순 속성 : 의미를 더 분해할 수 없는 속성, 즉 의미가 하나
• 복합 속성 : 의미를 분해할 수 있어 값이 여러 개의 의미를 포함(예를 들어 생년월일은 년, 월, 일로 이뤄짐)

유도 속성과 저장 속성

-유도 속성

• 값이 별도로 저장되는 것이 아니라 기존의 다른 속성 값에서 유도되어 결정되는 속성
  예를 들어, 책 개체를 구성하는 가격과 할인율 속성으로 계산되는 판매 가격 속성이 유도 속성
• 유도 속성은 그때마다 계산되므로 값을 따로 저장할 필요가 없다.
• 유도 속성은 ERD상에서 점선 타원으로 표현

-저장 속성

가격과 할인율 같은 유도 속성을 계산하는 데 사용되는 속성

널 속성

• 아직 결정되지 않았거나 모르는 값을 의미
• 해당되는 값이 없는, 즉 존재하지 않는 값의 경우도 널 값
• 널 값은 아직 값을 갖지 않은 것이므로 공백과 0과는 다르다.

키 속성

개체 집합에 존재하는 각 개체 인스턴스들을 식별하는 데 사용

• 유일성 : 하나의 키값으로 튜플을 유일하게 식별할 수 있는 성질
• 최소성 : 키를 구성하는 속성들 중 꼭 필요한 최소한의 속성들로만 키를 구성하는 성질

슈퍼 키(Super Key)

각 행을 유일하게 식별할 수 있는 속성들의 집합이다.

서로 구분만 할 수 있다면 '슈퍼키'라고 할 수 있다.

• 학번 : 학번만 가지고 학생들을 구분할 수 있으므로 슈퍼키가 맞다.
• 주민등록번호 : 주민등록번호만 가지고도 학생들을 구분할 수 있으므로 슈퍼키가 맞다.
• 이름 : 이름은 서로 같을 수 있기 때문에 구분하지 못하므로 슈퍼키가 아니다.
• 생년월일 : 생년월일 역시 서로 같을 수 있기 때문에 슈퍼키가 아니다.
• 이름, 생년월일 : 이름과 생년월일의 조합으로 학생들을 구분할 수 있으므로 슈퍼키가 맞다.

후보키 (Candidate Key)

각 행을 유일하게 식별할 수 있는 "최소한의" 속성들의 집합이다.

서로 구분할 수 있으면서 불필요한 속성들이 없어야 '후보키'라고 할 수 있다.

• 학번 : 학생들을 구분할 수 있고 속성이 하나 뿐이므로 후보키가 맞다.
• 주민등록번호 : 학생들을 구분할 수 있고 속성이 하나 뿐이므로 후보키가 맞다.
• 이름 : 이름은 서로 같을 수 있기 때문에 후보키가 아니다.
• 생년월일 : 생년월일 역시 서로 같을 수 있기 때문에 후보키가 아니다.
• 이름, 생년월일 : 학생들을 구분할 수는 있으나 [학번] 하나 또는 [주민등록번호] 하나만 가지고도 구분할 수 있다.
  따라서 속성 2개를 조합할 필요가 없으므로 후보키가 아니다.

기본키 (Primary Key)

최소성을 가진다.

후보키들 중에서 하나를 메인으로 선택한 키다.

ERD 상에서 밑줄을 그어 나타낸다.

• [학번]을 메인으로 선택했으므로 [학번]이 기본키가 된다.

대체키 (Alternate Key)

기본키를 제외한 나머지 후보키들을 의미한다.

• [학번]을 메인으로 선택했으므로 [학번] 이 외에 [주민등록번호]가 대체키가 된다.

외래키 (Foreign Key)

한 테이블이 다른 테이블의 기본키를 참조해서 테이블 간의 관계를 만드는 것을 의미한다.

• 취미 테이블의 [학번]은 학생 테이블의 [학번]을 참조해서 학생마다 취미가 무엇인지를 나타내고 있으므로 외래키이다.

유니크키(Unique Key, Unique Index)

값 중복을 허용하지 않는다.

NULL값을 허용한다.

테이블에서 여러 개 생성 가능하다.

관계(Relationship)

관계는 개체와 개체가 맺고 있는 의미 있는 연관성이다.

관계는 개체 집합들 사이의 대응 관계, 즉 매핑(mapping)을 의미한다.

업무 처리에 대한 요구 사항을 개체들을 이용해 하나의 문장으로 만들었을 때 동사에 해당하는 것이 관계이다.

고객 개체와 책 개체 사이의 '고객이 책을 구매한다'에서 구매가 관계이다.

관계도 개체처럼 속성을 가질 수 있다.

관계를 맺음으로써 발생하는 중요한 데이터들이 관계의 속성이 된다.

관계는 ERD 상에서 마름모로 표현한다.

관계의 유형(Mapping Cardinality)

관계도 다양한 기준에 따라 분류할 수 있다.

중요하게 활용되는 분류 기준은 매핑 원소의 수, 즉 매핑 카디널리티다.

매핑 카디널리티를 기준으로 일대일, 일대다, 다대다 라는 세 가지 유형으로 분류할 수 있다.

일대일(1:1) 관계

개체 A의 각 개체 인스턴스가 개체 B의 개체 인스턴스 하나와 관계를 맺을 수 있고, 개체 B의 각 개체 인스턴스도 개체 A의 개체 인스턴스 하나와 관계를 맺을 수 있다면 두 개체의 관계는 일대일 관계다.

일대다(1:N) 관계

개체 A와 각 개체 인스턴스는 개체 B의 개체 인스턴스 여러 개와 관계를 맺을 수 있지만, 개체 B의 각 개체 인스턴스는 개체 A의 개체 인스턴스 하나와만 관계를 맺을 수 있다면 두 개체는 일대다 관계다.

다대다(N:M) 관계

개체 A의 각 개체 인스턴스가 개체 B의 개체 인스턴스 여러 개와 관계를 맺을 수 있고, 개체 B의 각 개체 인스턴스가 개체 A의 개체 인스턴스 여러 개와 관계를 맺을 수 있다면 두 개체는 다대다 관계다.

관계의 참여 특성(Mapping Optionality)

개체 A와 B 사이의 관계에서, 개체 A의 모든 개체 인스턴스가 관계에 반드시 참여해야 된다면 개체 A가 관계에 '필수적 참여한다' 또는 '전체 참여한다'라고 한다.

• 모든 고객이 책을 반드시 구매해야 한다는 제약조건이 있다. -> 고객 개체가 구매 관계에 필수적 참여
• 필수적 참여 관계는 ERD 상에서 이중선으로 표현

개체 A의 개체 인스턴스 중 일부만 관계에 반드시 참여해야 된다면 개체 A가 관계에 '부분적 참여한다' 또는 '선택적 참여한다'라고 한다.

• 모든 고객이 책을 반드시 구매해야 한다는 제약조건이 없다. -> 고객 개체가 구매 관계에 선택적 참여

관계의 종속성

개체 B가 독자적으로는 존재할 수 없고 다른 개체 A의 존재 여부에 의존적이라면, 개체 B가 개체 A에 종속되어 있다고 한다.

이는 개체 A가 존재해야 개체 B가 존재할 수 있고, 개체 A가 삭제되면 개체 B도 함께 삭제되어야 함을 의미한다.

• 약한 개체 : 다른 개체의 존재 여부에 의존적인 개체
• 강한 개체 : 다른 개체의 존재 여부를 결정하는 개체

강한 개체와 약한 개체는 일반적으로 일대다의 관계이며, 약한 개체는 강한 개체와의 관계에 필수적으로 참여한다는 특징이 있다.

약한 개체는 자신이 지닌 속성만으로는 식별이 어려워 일반적으로 강한 개체의 키를 포함하여 키를 구성한다.

약한 개체는 이중 사각형으로 표현하고 약한 개체가 강한 개체와 맺는 관계는 이중 마름모로 표현한다.

ERD(Entity-Relationship Diagram)

ERD는 개체-관계 모델을 이용해 현실 세계를 개념적으로 모델링한 결과물을 그림으로 표현한 것이다.

ERD는 기본적으로 개체를 표현하는 사각형, 개체 간의 관계를 표현하는 마름모, 개체나 관계의 속성을 표현하는 타원, 각 요소를 연결하는 연결선으로 구성된다.

그리고 일대일, 일대다, 다대다 관계를 레이블로 표기한다.

논리적 데이터 모델

논리적 데이터 모델의 개념과 특성

개체-관계 모델은 현실 세계를 사람들의 머릿속에 그릴 수 있는 개념적인 구조로 모델링하는 데 사용하므로 어떤 DBMS로 데이터베이스를 구축하든 상관이 없다.

하지만, ERD로 표현한 개념적인 구조를 데이터베이스에 표현하는 형태를 결정하는 논리적인 데이터 모델링에서는 DBMS 종류가 중요하다.

DBMS에 따라 ERD로 표현된 개념적 구조를 데이터베이스에 어떤 형태로 저장할지를 논리적으로 표현하는데, 이러한 논리적인 구조를 논리적 데이터 모델이라 한다.

논리적 데이터 모델은 논리적 데이터 모델링의 결과물이고, 사용자가 생각하는 데이터베이스의 모습 또는 구조다.

• 스키마 : 논리적 데이터 모델로 표현된 데이터 베이스의 논리적 구조, 데이터베이스 내에서 데이터가 어떤 구조로 저장되는지를 나타낸다.
  

스키마는 DBMS에 따라 달라진다.

일반적으로 많이 사용되는 논리적 데이터 모델은 관계 데이터 모델로, 데이터베이스의 논리적 구조가 2차원 테이블(표) 형태이다.

관계 데이터 모델이 제안되기 전에는 계층 데이터 모델과 네트워크 데이터 모델이 주로 사용되었다.

관계 데이터 모델

아래의 포스팅에서 다룹니다.

2023.12.08 - [데이터베이스/데이터베이스 개론] - [DB 개론] 관계 데이터 모델

이 글은 혼자 공부하는 SQL(저자 : 우재남)의 책과 유튜브 영상을 참고하여 개인적으로 정리하는 글임을 알립니다.

인덱스(index)는 데이터를 빠르게 찾을 수 있도록 도와주는 도구로, 실무에서는 현실적으로 인덱스 없이 데이터베이스 운영이 불가능하다.

인덱스에는 두 가지 종류가 있다.

• 클러스터형 인덱스(Clustered Index)
• 보조 인덱스(Secondary Index)

인덱스의 개념

책을 예로 들어보면 책의 내용 중 'UNIQUE'에 대해서 찾아보고 싶다면 제일 뒤에 수록되어 있는 찾아보기를 찾아보는 것이다.

찾아보기는 ABC 또는 가나다 순으로 이미 정렬되어 있어 'U' 부분을 살펴보면 쉽게 'UNIQUE' 단어를 찾을 수 있고, 단어 옆에 본문의 페이지 번호가 적혀 있어서 원하는 내용으로 빨리 이동할 수 있다.

책 뒤의 찾아보기는 색인, 인덱스라고도 부른다.

실무에서 운영하는 테이블에서는 인덱스의 사용 여부에 따라 성능 차이가 날 수 있다.

대용량의 테이블일 경우에는 더욱 그러하다.

이것이 인덱스를 사용하는 이유이다.

인덱스의 문제점

인덱스는 적절히 사용해야 효과가 극대화된다.

필요 없는 인덱스를 만들면 데이터베이스가 차지하는 공간만 늘어나고, 오히려 인덱스를 이용해서 데이터를 찾는 것이 전체 테이블을 찾는 것보다 느려진다.

똑똑한 MySQL
데이터베이스에 인덱스를 생성해 놓아도, 인덱스를 사용해서 검색하는 것이 빠를지 아니면 전체 테이블을 검색하는 것이 빠를지 MySQL이 알아서 판단한다.
만약 인덱스를 사용하지 않는다면 사용하지도 않는 인덱스를 만든 것이므로 공간 낭비를 한 셈이다.

인덱스의 장점과 단점

장점

• SELECT 문으로 검색하는 속도가 매우 빨라진다.
• 적은 처리량으로 요청한 결과를 얻게 되어 여유가 생기고 추가로 더 많은 처리를 할 수 있다.
  즉, 결과적으로 전체 시스템의 성능이 향상된다.

단점

• 인덱스도 공간을 차지해서 데이터베이스 안에 추가적인(테이블 크기의 약 10%) 공간이 필요하다.
• 처음에 인덱스를 만드는 데 시간이 오래 걸릴 수 있다.
• SELECT가 아닌 데이터의 변경 작업(INSERT, UPDATE, DELETE)이 자주 일어나면 오히려 성능이 나빠질 수 있다.

인덱스의 종류

인덱스에는 두 가지 종류가 있다.

• 클러스터형 인덱스(Clustered Index)
• 보조 인덱스(Secondary Index)

클러스터형 인덱스는 영어사전과 같고, 보조 인덱스는 책의 뒤에 찾아보기가 있는 일반적인 책과 같다.

보조 인덱스는 찾아보기에서 해당 단어를 찾은 후에 옆에 표시된 페이지를 펼쳐야 실제 찾는 내용이 있는 것을 말한다.

클러스터형 인덱스는 영어사전처럼 책의 내용이 이미 알파벳 순서대로 정렬되어 있는 것이다.

자동으로 생성되는 인덱스

인덱스는 테이블의 컬럼 단위에 생성되며, 하나의 컬럼에는 하나의 인덱스를 생성할 수 있다.

하나의 컬럼에 여러 개의 인덱스를 생성할 수도 있고, 여러 개의 컬럼을 묶어서 하나의 인덱스를 생성할 수도 있지만 그런 경우는 드물다.

컬럼이 기본키(PK)로 지정되어 있다면 해당 컬럼은 자동적으로 클러스터형 인덱스가 된다.

즉, 해당 열을 기준으로 데이터가 자동으로 정렬된다.

CREATE TABLE table1  (
    col1  INT  PRIMARY KEY,
    col2  INT,
    col3  INT
);
SHOW INDEX FROM table1;

SHOW INDEX 문을 사용하면 인덱스 정보를 알 수 있다.

Key_name 부분을 보면 PRIMARY라고 써져 있다. 이는 기본 키로 설정해서 클러스터형 인덱스라는 뜻이다.

Column_name이 col1로 설정되어 있다는 것은 col1 열에 인덱스가 만들어져 있다는 것이다.

Non_Unique는 '고유하지 않다'라는 의미이다. 즉, 중복이 허용되냐는 뜻이다.

Non_Unique가 0이라는 것은 false, 반대로 1은 true이다. 결론적으로 이 인덱스는 중복이 허용되지 않는 인덱스이다.

고유 인덱스
고유 인덱스(Unique index)는 인덱스의 값이 중복되지 않는다는 의미고, 단순 인덱스(Non-Unique index)는 인덱스의 값이 중복되어도 된다는 의미이다.
Primary Key나 Unique Key로 지정하면 값이 중복되지 않으므로 고유 인덱스가 생성된다. 그 외의 인덱스는 단순 인덱스로 생성된다.

기본키와 더불어 고유키(Unique Key)도 인덱스가 자동으로 생성된다.

컬럼이 고유 키로 지정되어 있다면 해당 컬럼은 자동적으로 보조 인덱스가 된다.

클러스터형 인덱스와 다르게 보조 인덱스는 자동으로 정렬되지 않는다. 그냥 순서대로 삽입될 뿐이다.

CREATE TABLE table2  (
    col1  INT  PRIMARY KEY,
    col2  INT  UNIQUE,
    col3  INT  UNIQUE
);
SHOW INDEX FROM table2;

Key_name에 컬럼 이름(col2, col3 등)이 쓰여 있는 것은 보조 인덱스라고 보면 된다.

고유 키 역시 중복값을 허용하지 않기 때문에 Non_Unique가 0으로 되어 있다.

고유 키를 여러 개 지정할 수 있듯이 보조 인덱스도 여러 개 만들 수 있다.

인덱스의 내부 작동원리

클러스터형 인덱스와 보조 인덱스는 모두 내부적으로 균형 트리로 만들어진다.

균형 트리의 개념

균형 트리는 나무를 거꾸로 표현한 자료 구조로, 트리에서 제일 상단의 뿌리를 루트, 줄기를 중간, 끝에 달린 앞을 리프라고 부른다.

균형 트리 구조에서 데이터가 저장되는 공간을 노드(Node)라고 한다.
루트 노드(Root Node)는 노드의 가장 상위 노드를 말한다. 모든 출발은 루트 노드에서 시작된다. 
리프 노드(Leaf Node)는 제일 마지막에 존재하는 노드를 말한다. 
루트 노드와 리프 노드 사이에 있는 노드를 중간 노드(Internal Node)라고 한다.

그림에선 각 노드에 데이터가 최대 4개 들어가는 것으로 표현했지만 실제로는 훨씬 많은 데이터가 들어간다.

노드라는 용어는 개념적인 설명에서 주로 나오는 용어이며, MySQL에서는 페이지(Page)라고 부른다.

페이지는 최소한의 저장 단위로, 16 KByte 크기를 가진다.

인덱스를 사용하지 않고 전체 테이블 검색을 사용한 것을 그림으로 나타내면 아래와 같다.

MMM을 찾는다고 가정

전체 테이블 검색에서는 3페이지 만에 원하는 것을 찾았다.

인덱스를 사용하여 균형 트리에서 검색을 한다고 가정하면, 균형 트리는 무조건 루트 페이지부터 검색한다.

인덱스를 사용한 균형트리 검색에서는 2페이지 만에 원하는 것을 찾았다.

AAA -> FFF -> LLL -> LLL -> MMM 이렇게 5건의 데이터를 읽어서 원하는 것을 찾았는데, 몇 건의 데이터를 읽었느냐는 중요하지 않다. 몇 개의 페이지를 읽었느냐가 효율성을 판단한다.

균형 트리의 페이지 분할

인덱스는 균형 트리로 이루어져 있기 때문에 SELECT의 속도를 향상할 수 있다. 
다만 인덱스를 구성하면 데이터 변경 작업(INSERT, UPDATE, DELETE) 시 성능이 나빠진다. 
특히 INSERT 작업이 일어날 때 더 느리게 입력될 수 있다.
이유는 페이지 분할이라는 작업이 발생하기 때문인데, 페이지 분할이란 새로운 페이지를 준비해서 데이터를 나누는 작업을 말한다.
페이지 분할이 일어나면 MySQL이 느려지고, 너무 자주 일어나게 되면 성능에 큰 영향을 미친다.

이전 그림에서 III 데이터가 새로 삽입되었다고 가정하면 균형 트리는 아래와 같이 변경된다.

이 상태에서 GGG가 추가적으로 삽입된다고 하면, 두 번째 리프 페이지에서 더 이상 빈 공간이 없기 때문에 페이지 분할이 일어난다.

이 상태에서 PPP와 QQQ가 들어온다고 가정하자.

PPP가 들어올 때는 별 일이 일어나지 않지만, QQQ가 들어올 때 네 번째 리프 페이지에 빈칸이 없어서 또다시 페이지 분할 작업이 일어난다.

페이지 분할 후에 추가된 다섯 번째 리프 페이지를 루트 페이지에 등록을 하려고 하니, 루트 페이지도 이미 꽉 차서 루트 페이지도 페이지 분할을 해야 한다.

여기서 새로 생긴 페이지는 루트 페이지가 되고, 기존의 루트 페이지는 중간 페이지가 된다.

결국 QQQ 하나를 입력하기 위해서 3개의 새로운 페이지가 할당되고 2회의 페이지 분할이 되었다.

데이터 하나를 입력하기 위해 많은 일이 일어난 것이다.

어떤 INSERT는 빠르게 실행되고, 다른 INSERT는 느리게 실행되는 이유가 위와 같은 과정에 있다.

인덱스의 구조

클러스터형 인덱스 구조

보조 인덱스의 구조

인덱스의 실제 사용

인덱스 생성, 제거와 사용

인덱스 생성

인덱스를 생성하기 위해서는 CREATE INDEX 문을 사용해야 한다.
UNIQUE는 중복이 안 되는 고유 인덱스를 만드는 것인데, 생략하면 중복이 허용된다.
CREATE UNIQUE로 인덱스를 생성하려면 기존에 입력된 값들에 중복이 있으면 안 된다.
그리고 인덱스를 생성한 후에 입력되는 데이터와도 중복될 수 없으니 신중해야 한다.
ASC 또는 DESC는 인덱스를 오름차순 또는 내림차순으로 만들어준다.
기본은 ASC로 만들어지며, 일반적으로 DESC로 만드는 경우는 거의 없다.

CREATE [UNIQUE] INDEX 인덱스_이름
    ON 테이블_이름 (열_이름) [ASC | DESC]

CREATE INDEX idx_member_addr ON member (addr); --주소로 단순 보조 인덱스 생성
    
SHOW INDEX FROM member;--테이블에 생성된 인덱스를 보여줌

CREATE UNIQUE INDEX idx_member_name ON member (mem_name); --멤버 이름으로 고유 보조 인덱스 생성

위 쿼리는 문제점이 있다.

멤버 이름으로 고유 보조 인덱스를 생성하기 때문에 동명이인은 멤버 테이블에 입력이 될 수 없기 때문이다.

따라서 현재 중복된 값이 없다고 무조건 설정하면 안 되며, 절대로 중복되지 않는 컬럼(주민등록번호, 학번, 이메일 주소 등)에만 UNIQUE 옵션을 사용해서 인덱스를 생성해야 한다.

ANALYZE TABLE member; -- 지금까지 생성한 인덱스를 실제로 적용
SHOW INDEX FROM member; --테이블에 생성된 인덱스를 보여줌

인덱스를 생성한 후에 ANALYZE TABLE문을 실행해 줘야 실제로 적용된다.

인덱스 실제 사용

CREATE INDEX idx_member_mem_number ON member (mem_number); --멤버의 수로 단순 보조 인덱스 생성
ANALYZE TABLE member; -- 인덱스 적용

인덱스를 실질적으로 활용하기 위해서는 WHERE 문을 사용해야 한다.

MySQL의 경우 WHERE 문에 작성된 조건에 따라 그 효율성을 먼저 따져, 만약 인덱스 검색의 효율이 더 좋다면 인덱스 검색을, 그렇지 않다면 테이블 전체 검색을 수행한다.

SELECT mem_id, mem_name, addr FROM member WHERE mem_name = '에이핑크';

--멤버 수가 7 이상인 그룹의 이름, 수를 출력
SELECT mem_name, mem_number FROM member WHERE mem_number >= 7;

WHERE 문을 사용하지 않으면 Full Table Scan을 하기 때문에 인덱스를 만든 이유가 없어진다.

SELECT * FROM member;

또한 되도록 WHERE 절에 작성된 열에는 연산과 같은 가공을 하지 않는 것이 바람직하다.

SELECT mem_name, mem_number FROM member 
 WHERE mem_number*2 >= 14; --전체 테이블 스캔, 이렇게 사용 X
    
SELECT mem_name, mem_number FROM member 
 WHERE mem_number >= 14/2; --인덱스 사용해서 스캔, 이렇게 사용 O

WHERE 절에 작성된 열 이름에 연산과 같은 가공을 하면 Full Table Scan이 된다.

인덱스 제거

클러스터형 인덱스와 보조 인덱스가 섞여 있을 때는 보조 인덱스를 먼저 제거하는 것이 좋다.

보조 인덱스는 어떤 것을 먼저 제거해도 상관없다.

• 보조 인덱스 삭제

DROP INDEX idx_member_mem_name ON member;
DROP INDEX idx_member_addr ON member;
DROP INDEX idx_member_mem_number ON member;

• 클러스터형 인덱스 삭제(기본키 제약조건 삭제)

ALTER TABLE member DROP PRIMARY KEY;

위 쿼리는 오류가 발생한다. 해당 테이블의 기본키가 buy 테이블의 외래키이기 때문에 제약 조건에 위배된다.

그러므로 기본 키를 제거하기 전에 외래 키 관계를 제거해야 한다.

테이블에는 여러 개의 외래 키가 있을 수 있다.

그래서 먼저 외래 키의 이름을 알아내야 한다.

information_schema 데이터베이스의 referential_constraints 테이블을 조회하면 외래 키의 이름을 알 수 있다.

SELECT table_name, constraint_name
 FROM information_schema.referential_constraints
 WHERE constraint_schema = 'market_db';

외래 키의 이름을 알았으니 외래 키를 먼저 제거하고 기본키를 제거하면 된다.

ALTER TABLE buy DROP FOREIGN KEY buy_ibfk_1;
ALTER TABLE member  DROP PRIMARY KEY;

인덱스를 제거한다고 데이터의 내용이 바뀌는 것은 아니다.

지금 인덱스를 제거한 것은 찾아보기를 제거하고 영어사전을 순서가 섞인 단어장으로 변경한 것이며, 내용은 그대로이다.

이 글은 혼자 공부하는 SQL(저자 : 우재남)의 책과 유튜브 영상을 참고하여 개인적으로 정리하는 글임을 알립니다.

뷰는 데이터베이스 개체 중에 하나이다.

모든 데이터베이스 개체는 테이블과 관련이 있지만, 특히 뷰는 테이블과 아주 밀접하게 연관되어 있다.

뷰는 한 번 생성해 놓으면 테이블이라고 생각해도 될 정도로 사용자들의 입장에서는 테이블과 거의 동일한 개체로 취급한다.

뷰는 테이블처럼 데이터를 가지고 있지 않다.

뷰의 실체는 SELECT 문으로 만들어져 있기 때문에 뷰에 접근하는 순간 SELECT가 실행되고 그 결과가 화면에 출력되는 방식이다.

뷰와 테이블의 관계는 바로 가기 아이콘과 실제 프로그램의 관계와 유사하다.

뷰는 단순 뷰와 복합 뷰로 나뉜다.

• 단순 뷰 : 하나의 테이블과 연관된 뷰
• 복합 뷰 : 2개 이상의 테이블과 연관된 뷰

복합 뷰는 주로 두 테이블을 조인한 결과를 뷰로 만들 때 사용한다.

CREATE VIEW v_complex
 AS
 SELECT B.mem_id, M.mem_name, B.prod_name, M.addr
 FROM buy B
 INNER JOIN member M
 ON B.mem_id = M.mem_id;

복합 뷰는 읽기 전용이다. 복합 뷰를 통해 테이블에 데이터를 입력/수정/삭제할 수 없다.

뷰의 개념

뷰의 작동

사용자가 뷰에 접근하는 방식은 아래의 그림과 같다.

사용자는 뷰를 테이블이라고 생각하고 접근한다.

그러면 데이터베이스가 뷰 안에 있는 SELECT를 실행해서 그 결과를 사용자에게 보내주므로 사용자 입장에서는 1번과 4번만, 즉 뷰에서 모두 처리된 것으로 이해한다.

뷰는 기본적으로 읽기 전용으로 사용되지만, 뷰를 통해서 원본 테이블의 데이터를 수정할 수도 있다.

하지만 무조건 가능한 것은 아니고 몇 가지 조건을 만족해야 한다.

뷰를 사용하는 이유

뷰를 만들면 테이블과 동일하게 접근이 가능하다.

테이블 대신 굳이 뷰를 사용하는 방법은 아래와 같다.

• 보안에 도움이 된다.

뷰에서 사용자의 아이디, 이름, 주소 등 중요하지 않은 정보만 보여주고 민감한 정보인 연락처, 주민등록번호등은 보여주지 않을 수 있다.

데이터베이스에서 보안은 상당히 중요하다.

root 계정은 모든 권한이 있는 관리자로 못하는 작업이 없다.

테이블의 생성, 삭제는 물론 테이블의 데이터를 마음대로 조작할 수 있는 막강한 권한이 있다.

은행을 예로 들면, 은행도 데이터베이스의 테이블에 정보를 저장한다.

통장 테이블에 고객의 예금을 관리한다고 가정하면 은행에서 모든 직원에게 root의 권한을 부여한다면 고의든 실수든 고객의 예금을 마음대로 사용할 수 있다.

아니면 고객의 중요한 정보(통장 비밀번호 등)를 마음대로 볼 수 있다.

이러한 사고를 방지하기 위해 중요 관계자가 아닌 사람에게는 중요한 정보를 보여주지 못하도록 권한을 제한하고, 뷰에만 접근할 수 있도록 권한을 준다면 이러한 문제를 쉽게 해결할 수 있다.

• 복잡한 SQL을 단순하게 만들 수 있다.

아래의 쿼리문은 물건을 구매한 회원들에 대한 SQL이다.

SELECT B.mem_id, M.mem_name, B.prod_name, M.addr, 
 CONCAT(M.phone1, M.phone2) '연락처' 
 FROM buy B
 INNER JOIN member M
 ON B.mem_id = M.mem_id;

내용이 길고 좀 복잡하다. 만약 이 쿼리를 자주 사용해야 한다면 상당히 골치 아플 것이다.

하지만 이 SQL을 아래와 같이 뷰로 생성해 놓고 사용자들은 해당 뷰에만 접근하도록하면 복잡한 SQL을 입력할 필요가 없다.

CREATE VIEW v_memberbuy AS
 SELECT B.mem_id, M.mem_name, B.prod_name, M.addr, 
 CONCAT(M.phone1, M.phone2) '연락처' 
 FROM buy B
 INNER JOIN member M ON B.mem_id = M.mem_id;

이렇게 뷰를 한번 만들어 놓으면 v_memberbuy를 테이블이라고 생각하고 접근하면 된다.

필요하면 WHERE절도 사용할 수 있다.

SELECT * FROM v_memberbuy WHERE mem_name = '블랙핑크';

뷰의 기본 생성

이러한 데이터베이스에서 회원 테이블에서 멤버 아이디, 멤버 이름, 멤버 주소만 보고 싶다면 아래의 쿼리문을 작성하면 된다.

SELECT mem_id, mem_name, addr FROM member;

뷰로 멤버 아이디, 멤버 이름, 멤버 주소만 보고 싶다면 아래의 쿼리문을 작성하면 된다.

즉, v_member뷰로는 mem_id, mem_name, addr칼럼만 볼 수 있는 것이다.

CREATE VIEW v_member AS SELECT mem_id, mem_name, addr FROM member;

이제 뷰의 모든 컬럼의 값들을 보려면 아래와 같이 컬럼을 작성하면 된다.

SELECT * FROM v_member;

필요한 열만 보거나 조건식을 넣을 수 있다.

SELECT mem_name, addr FROM v_member WHERE addr IN ('서울', '경기');

뷰의 실제 작동

뷰의 실제 생성, 수정, 삭제

뷰 생성

기본적인 뷰를 생성하면서 뷰에 사용될 열 이름을 테이블과 다르게 지정할 수도 있다.

이는 별칭을 사용하면 되는데, 중간에 띄어쓰기 사용이 가능하다. 별칭은 열 이름 뒤에 작은따옴표 또는 큰따옴표로 묶어주고, 형식상 AS를 붙여준다.

AS를 붙이면 코드가 명확해 보이는 장점이 있다.(다르게 말하면, 별칭을 붙일 때는 AS를 안 붙여도 된다.)

단, 뷰를 조회할 때는 열 이름에 공백이 있으면 백틱(`)으로 묶어줘야 한다.(키보드 느낌표 왼쪽에 존재, (')가 아닌 (`))

CREATE OR REPLACE VIEW v_viewtest1
 AS
 SELECT B.mem_id 'Member ID', M.mem_name 'Member Name', 
 B.prod_name "Product Name", 
 CONCAT(M.phone1, M.phone2) "Office Phone" 
 FROM buy B
 INNER JOIN member M
 ON B.mem_id = M.mem_id;

이제 뷰를 조회하면

SELECT  DISTINCT `Member ID`, `Member Name`, `Product Name`, `Office Phone` FROM v_viewtest1; -- 백틱을 사용

뷰 수정

뷰의 수정은 ALTER VIEW 구문을 사용하며, 열 이름에 한글을 사용해도 된다.

ALTER VIEW v_viewtest1
 AS
 SELECT B.mem_id '회원 아이디', M.mem_name AS '회원 이름', 
 B.prod_name "제품 이름", 
 CONCAT(M.phone1, M.phone2) AS "연락처" 
 FROM buy B
 INNER JOIN member M
 ON B.mem_id = M.mem_id;

SELECT  DISTINCT `회원 아이디`, `회원 이름` FROM v_viewtest1;  -- 백틱을 사용

뷰 삭제

뷰의 삭제는 DROP VIEW를 사용한다.

DROP VIEW v_viewtest1;

뷰의 정보 확인

CREATE OR REPLACE VIEW v_viewtest2
AS SELECT mem_id, mem_name, addr FROM member;

CREATE OR REPLACE VIEW
뷰를 생성할 때 CREATE VIEW는 기존에 뷰가 있으면 오류가 발생하지만
CREATE OR REPLACE VIEW는 기존에 뷰가 있어도 덮어쓰는 효과를 내기 때문에 오류가 발생하지 않는다.
즉, DROP VIEW와 CREATE VIEW를 연속적으로 작성한 효과를 갖는다.

DRSCRIBE문으로 기존 뷰의 정보를 확인할 수 있다.

DESCRIBE v_viewtest2;

뷰도 테이블과 동일하게 정보를 보여준다.

하지만 PRIMARY KEY등의 정보는 확인되지 않는다.

뷰를 조회할때는 제약조건 등의 정보는 조회가 안된다.
반면에 테이블을 조회할 때는 제약조건 등의 정보가 조회가 된다.

SHOW CREATE VIEW
SHOW CREATE VIEW 문으로 뷰의 소스코드도 확인할 수 있다.
SHOW 결과는 뷰를 생성할 때보다 복잡하게 나온다.
복잡해 보이지만 핵심적인 코드는 생성할 때 사용한 코드와 동일하다.

SHOW CREATE VIEW v_viewtest2;​

뷰를 통한 데이터의 수정/삽입/삭제

수정

뷰를 통해 테이블의 데이터를 수정할 수도 있다.

UPDATE v_member SET addr = '부산' WHERE mem_id='BLK' ;

삭제

평균 키가 167 이상인 뷰를 생성하는 쿼리는 아래와 같다.

CREATE VIEW v_height167 AS SELECT * FROM member WHERE height >= 167 ;

평균 키가 167 이상만 조회되었다.

뷰에서 키가 167이하인 데이터를 삭제하는 쿼리는 아래와 같다.

DELETE FROM v_height167 WHERE height <= 167;

167 이하인 데이터를 삭제하고 뷰를 조회해 보면 정상적으로 삭제된 것을 확인할 수 있다.

실제 member 테이블에서는 키가 167인 회원의 키가 삭제되었다.

(필자는 이후 다시 삭제된 데이터를 복구하였다.)

삽입

INSERT INTO v_member(mem_id, mem_name, addr) VALUES('BTS','방탄소년단','경기') ;

이 쿼리는 오류가 발생한다.

이유는 뷰가 참조하는 테이블의 컬럼 중에서 특정한 컬럼이 NOT NULL로 설정되어 있어서 반드시 입력해주어야 하기 때문이다.

만약 뷰를 통해서 참조 테이블에 값을 입력하고 싶다면, 뷰에 해당 컬럼을 포함하도록 뷰를 재정의 하거나, 참조 테이블에서 해당 컬럼의 속성을 NULL로 바꾸거나 기본값을 지정해야 한다.

평균 키가 167 이상인 뷰를 생성하는 쿼리는 아래와 같다.

CREATE VIEW v_height167 AS SELECT * FROM member WHERE height >= 167 ;

뷰에서 키가 167 미만인 데이터를 입력하는 쿼리는 아래와 같다.

INSERT INTO v_height167 VALUES('TRA','티아라', 6, '서울', NULL, NULL, 159, '2005-01-01') ;

평균 키가 167이상인 데이터만 조회하는 뷰인데 167 미만의 데이터를 넣는다는 것은 논리적으로 올바르지 못하다.

따라서 뷰에는 추가가되지 않았지만, 뷰의 참조 테이블인 member 테이블에는 추가가 되었다.

키가 167이상인 뷰이므로 167 이상의 데이터만 입력되도록 하는 것이 논리적으로 바람직하다.

이럴 때 예약어 WITH CHECK OPTION을 통해 뷰에 설정된 값의 범위가 벗어나는 값은 입력되지 않도록 할 수 있다.

ALTER VIEW v_height167 AS SELECT * FROM member WHERE height >= 167
 WITH CHECK OPTION ;

이후 키가 167미만의 데이터를 입력하면 오류가 발생한다.

뷰가 참조하는 테이블의 삭제

뷰가 참조하는 테이블을 삭제하면 뷰를 사용할 수 없게 된다.

DROP TABLE IF EXISTS buy, member;

SELECT * FROM v_height167;

위 쿼리를 날리면 당연히 참조하는 테이블이 삭제되었기 때문에 조회할 수 없다는 오류를 마주하게 된다.

뷰가 조회되지 않으면 CHECK TABLE 문으로 뷰의 상태를 확인해 볼 수 있다.

이 글은 혼자 공부하는 SQL(저자 : 우재남)의 책과 유튜브 영상을 참고하여 개인적으로 정리하는 글임을 알립니다.

테이블을 만들 때는 테이블의 구조에 필요한 제약조건을 설정해줘야 한다.

기본 키(Primary Key)는 학번, 아이디, 사번 등과 같은 고유한 번호를 의미하는 column에 지정한다.

외래 키(Foreign Key)는 기본키와 연결되는 column에 지정한다.

이메일, 휴대폰 번호와 같이 중복되지 않는 열에는 고유 키(Unique)를 지정할 수 있다.

회원의 평균 키를 넣는다고 가정할 때, 당연히 평균 키는 2m를 넘지 않을 것이다. 이때 실수로 200cm을 입력하는 것을 방지하는 제약 조건이 체크(Check)이다.

국내에서 서비스하는 프로그램을 만든다고 하면, 회원 테이블에 국적은 대부분이 대한민국일 것이다. 이러한 경우에는 국적이 대한민국으로 기본값(Default)을 설정할 수 있다.

또한, 값을 꼭 입력해야 한다면 NOT NULL 제약 조건을 설정할 수도 있다.

제약 조건(Constraint)의 기본 개념과 종류

제약조건은 데이터의 무결성을 지키기 위해 제한하는 조건이다.

데이터의 무결성이란 데이터에 결함이 없음을 의미한다.

MySQL에서 제공하는 대표적인 제약조건은아래와 같다.

• PRIMARY KEY 제약조건
• FOREIGN KEY 제약조건
• UNIQUE 제약조건
• CHECK 제약조건
• DEFAULT 정의
• NULL 값 허용

기본키 제약조건

테이블에는 많은 행 데이터가 있다. 이 중에서 데이터를 구분할 수 있는 식별자를 기본 키라고 부른다.

기본 키에 입력되는 값은 중복될 수 없으며, NULL 값이 입력될 수 없다.

대부분의 테이블은 기본 키를 가져야 한다. 물론, 기본 키가 없어도 테이블 구성이 가능하지만 실무에서 사용하는 테이블에는 기본 키를 설정해야 중복된 데이터가 입력되지 않는다.

또한 기본 키로 생성한 것은 자동으로 클러스터형 인덱스가 생성된다.

마지막으로 하나의 테이블에서는 기본 키를 1개만 가질 수 있다.

어떠한 열에 기본키를 설정해도 문법상 문제는 없으나 테이블의 특성을 가장 잘 반영하는 열을 선택해야 한다.

기본키 지정하는 방법

기본키를 지정하는 방법은 세 가지가 있다.

• 첫 번째 방법

CREATE TABLE member 
( mem_id  CHAR(8) NOT NULL PRIMARY KEY, 
  mem_name    VARCHAR(10) NOT NULL, 
  height      TINYINT UNSIGNED NULL
);

• 두 번째 방법

CREATE TABLE member 
( mem_id  CHAR(8) NOT NULL, 
  mem_name    VARCHAR(10) NOT NULL, 
  height      TINYINT UNSIGNED NULL,
  PRIMARY KEY (mem_id)
);

• 세 번째 방법(테이블을 만든 후, 제약조건 추가)

CREATE TABLE member 
( mem_id  CHAR(8) NOT NULL, 
  mem_name    VARCHAR(10) NOT NULL, 
  height      TINYINT UNSIGNED NULL
);
ALTER TABLE member ADD CONSTRAINT PRIMARY KEY (mem_id);

기본키에 이름 지정하기
기본 키는 별도의 이름이 없으며, DESCRIBE 명령으로 확인하면 그냥 PRI로만 나온다.
필요하다면 기본 키의 이름을 직접 지어줄 수 있다.
예를 들어, PK_member_mem_id와 같은 이름을 붙여주면, 이름 만으로도 'PK가 member 테이블의 mem_id열에 지정됨'이라고 이해할 수 있다.

CREATE TABLE member 
( mem_id  CHAR(8) NOT NULL, 
  mem_name    VARCHAR(10) NOT NULL, 
  height      TINYINT UNSIGNED NULL,
  CONSTRAINT PRIMARY KEY PK_member_mem_id (mem_id)
);​

외래 키 제약조건

외래 키 제약조건은 두 테이블 사이의 관계를 연결해주고, 그 결과 데이터의 무결성을 보장해 주는 역할을 한다.

외래 키가 설정된 열은 꼭 다른 테이블의 기본키와 연결된다.

회원 테이블과 구매 테이블이 바로 대표적인 기본 키- 외래 키 관계이다.

여기서 기본 키가 있는 회원 테이블을 기준 테이블이라고 부르며, 외래 키가 있는 구매 테이블을 참조 테이블이라고 부른다.

구매 테이블의 아이디(FK)는 반드시 회원 테이블의 아이디(PK)로 존재한다.

쇼핑몰 데이터베이스에 제품을 구매한 기록이 있는 사람은 쇼핑몰 회원이라는 의미이다. 그러므로 구매한 기록은 있으나 구매한 사람이 누군지 모르는 심각한 일은 절대 발생하지 않는다.

구매 테이블의 데이터는 모두 누가 구매했는지 확실히 알 수 있는, 무결한 데이터가 되는 것이다.

또 하나 기억해야할 것은 참조 테이블이 참조하는 기준 테이블의 열은 반드시 기본 키나, 고유 키로 설정되어 있어야 한다.

테이블을 삭제하는 순서
회원 테이블과 구매 테이블은 기본 키-외래 키로 연결되어 있다.
만약, 구매 테이블이 있는데 회원 테이블을 삭제하면 이는 무결성 제약조건에 위배된다.
예를 들어, 구매 테이블에 있는 특정한 회원의 정보를 알고 싶어도 회원 테이블이 삭제되었기 때문에 알 수 있는 방법이 없다. 이러한 경우는 무결한 데이터라고 할 수 없다.
따라서 기본 키-외래 키 관계로 연결된 테이블은 외래 키가 설정된 테이블을 먼저 삭제해야 한다.

회원이 아닌데 구매 테이블을 구매할 수 있는가?
회원 테이블과 구매 테이블은 PK-FK 관계이다.
즉, 회원이 아닌데 구매 테이블에 회원이 입력될 수 없다.
이는 외래키 제약조건에 위반되기 때문이다.

외래키 지정하는 방법

외래키를 설정하는 방법은 두 가지가 있다.

외래 키의 형식은 FORIGN KEY(열_이름) REFERENCES 기준_테이블(열_이름) 이다.

• 첫 번째 방법

CREATE TABLE buy 
(  num         INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
   mem_id      CHAR(8) NOT NULL, 
   prod_name     CHAR(6) NOT NULL, 
   FOREIGN KEY(mem_id) REFERENCES member(mem_id)
);

• 두 번째 방법(테이블 생성 후, 제약조건 추가)

CREATE TABLE buy 
(  num         INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
   mem_id      CHAR(8) NOT NULL, 
   prod_name     CHAR(6) NOT NULL
);
ALTER TABLE buy ADD CONSTRAINT FOREIGN KEY(mem_id) REFERENCES member(mem_id);

기준 테이블의 열이 변경될 경우

만약, 회원 테이블의 BLK가 물품을 2건 구매한 상태에서 회원 아이디를 PINK로 변경하면 두 테이블의 정보가 일치하지 않게 된다.

이 그림을 코드로 확인하면 아래와 같다.

SELECT M.mem_id, M.mem_name, B.prod_name 
 FROM buy B
 INNER JOIN member M
 ON B.mem_id = M.mem_id;

내부 조인을 사용해서 물품 정보 및 사용자 정보를 확인해 보면, 결과가 정상적으로 나왔다.

하지만, BLK의 아이디를 PINK로 변경하면 아래처럼 오류가 발생한다.

PK-FK로 맺어진 후에는 기준 테이블의 열 이름이 변경되지 않는다.

열 이름이 변경되면 참조 테이블의 데이터에 문제가 발생하기 때문이다.

지금은 회원 테이블의 BLK가 물건을 구매한 기록이 존재하기 때문에 변경할 수 없는 것이다.
만약, BLK가 구매한 적이 없다면(구매 테이블에 데이터가 없다면) 회원 테이블의 BLK는 변경 가능하다.

삭제를 시도해 보아도, 같은 오류로 삭제되지 않는다.

기준 테이블의 열 이름이 변경될 때 참조 테이블의 열 이름이 자동으로 변경될 수 있도록 하는 방법이 있다.

ON UPDATE CASCADE문을 사용하면 된다.

또한 기준 테이블의 데이터가 삭제되면 참조 테이블의 데이터도 삭제될 수 있도록 하는 방법도 존재한다.

ON DELETE CASCADE문을 사용하면 된다.

buy 테이블을 삭제하고 다시 만든 뒤, 아래의 제약조건을 추가해 준다.

ALTER TABLE buy
 ADD CONSTRAINT 
 FOREIGN KEY(mem_id) REFERENCES member(mem_id)
 ON UPDATE CASCADE
 ON DELETE CASCADE;

이제 회원 테이블의 BLK를 PINK로 변경하면 오류 없이 잘 변경이 된다.

다시 내부 조인을 사용해서 물품 정보 및 사용자 정보를 확인해 보면, 기준 테이블과 참조 테이블의 아이디가 모두 변경된 것을 확인할 수 있다.

PINK가 탈퇴한 것으로 가정하고 기준 테이블에서 삭제해도 오류 없이 잘 실행되는 것을 확인할 수 있다.

구매 테이블을 확인하면 아무것도 없다.

기타 제약조건

고유 키 제약조건

고유 키 제약조건은 '중복되지 않는 유일한 값'을 입력해야 하는 조건이다.

기본 키 제약조건과 거의 비슷하지만, 차이점은 고유 키 제약조건은 NULL값을 허용한다는 점이다.

또한 기본 키는 테이블에 1개만 설정해야 하지만, 고유 키는 여러 개를 설정해도 된다.

DROP TABLE IF EXISTS buy, member;
CREATE TABLE member 
( mem_id  CHAR(8) NOT NULL PRIMARY KEY, 
  mem_name    VARCHAR(10) NOT NULL, 
  height      TINYINT UNSIGNED NULL,
  email       CHAR(30)  NULL UNIQUE
);

이렇게 기존에 있던 테이블을 삭제하고 고유 키(이메일)를 갖는 멤버 테이블을 새로 만든 후

INSERT INTO member VALUES('BLK', '블랙핑크', 163, 'pink@gmail.com');
INSERT INTO member VALUES('TWC', '트와이스', 167, NULL);
INSERT INTO member VALUES('APN', '에이핑크', 164, 'pink@gmail.com');

같은 이메일을 중복으로 삽입하면 아래와 같이 오류를 내뿜는다.

체크 제약조건

체크 제약조건은 입력되는 데이터를 점검하는 기능을 한다.

예를 들어 평균 키에 마이너스 값이 입력되지 않도록 하거나, 연락처의 국번에 02, 031, 041, 055 중 하나만 입력되도록 할 수 있다.

DROP TABLE IF EXISTS member;
CREATE TABLE member 
( mem_id  CHAR(8) NOT NULL PRIMARY KEY, 
  mem_name    VARCHAR(10) NOT NULL, 
  height      TINYINT UNSIGNED NULL CHECK (height >= 100),
  phone1      CHAR(3)  NULL
);

이렇게 height에 check 제약조건으로 키가 100 이상이 되도록 설정한 후

INSERT INTO member VALUES('BLK', '블랙핑크', 163, NULL);
INSERT INTO member VALUES('TWC', '트와이스', 99, NULL);

키가 100 미만이면 아래와 같은 오류를 내뿜는다.

이유는 체크 제약조건에 위배되었기 때문이다.

필요하다면 테이블을 만든 후에 ALTER TABLE 문으로 제약 조건을 추가해도 된다.

ALTER TABLE member
 ADD CONSTRAINT 
 CHECK  (phone1 IN ('02', '031', '032', '054', '055', '061' )) ;

이렇게 제약조건을 설정하면 02, 031, 032, 054, 055, 061 이외의 phone1 값이 들어오면 오류를 내뿜게 된다.

INSERT INTO member VALUES('TWC', '트와이스', 167, '02');
INSERT INTO member VALUES('OMY', '오마이걸', 167, '010');

기본값 정의

기본값 정의는 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해 놓는 방법이다.

예를 들어, 키를 입력하지 않는다면 기본적으로 160이라고 입력되도록 하고 싶다면 아래와 같이 정의한다.

DROP TABLE IF EXISTS member;
CREATE TABLE member 
( mem_id  CHAR(8) NOT NULL PRIMARY KEY, 
  mem_name    VARCHAR(10) NOT NULL, 
  height      TINYINT UNSIGNED NULL DEFAULT 160,
  phone1      CHAR(3)  NULL
);

ALTER TABLE문을 사용해서 기본값을 정의하려면 아래처럼 쿼리를 작성한다.

ALTER TABLE member ALTER COLUMN phone1 SET DEFAULT '02';

이제 데이터를 입력하면

INSERT INTO member VALUES('RED', '레드벨벳', 161, '054');
INSERT INTO member VALUES('SPC', '우주소녀', default, default);

default가 들어간 열은 height과 phone1이다.

기본 값을 정의했기 때문에 height에는 160이 들어가고, phone1에는 02가 들어간다.

널 값 허용

NULL 값을 허용하지 않으면 생략하거나 NULL을 사용하고, 허용하지 않으려면 NOT NULL을 사용한다.

다만, PRIMARY KEY가 설정된 열에는 NULL 값이 있을 수 없으므로 생략하면 자동으로 NOT NULL이 적용된다.

NULL값은 아무것도 없다는 의미이다. 공백(' ')이나 0과는 다르다.

이 글은 혼자 공부하는 SQL(저자 : 우재남)의 책과 유튜브 영상을 참고하여 개인적으로 정리하는 글임을 알립니다.

테이블은 표 형태로 구성된 2차원 구조로, 행과 열로 구성되어 있다.

행은 row나 recode라고 부르며, 열은 column 또는 field라고 부른다.

테이블을 생성하기 전에 테이블의 구조를 정의해야 한다.

데이터 형식을 활용해서 각 열에 가장 적합한 데이터 형식을 지정한다.

회원 테이블 생성 SQL

CREATE TABLE member -- 회원 테이블
( mem_id        CHAR(8) NOT NULL PRIMARY KEY,
  mem_name      VARCHAR(10) NOT NULL, 
  mem_number    TINYINT NOT NULL, 
  addr          CHAR(2) NOT NULL,
  phone1        CHAR(3) NULL,
  phone2        CHAR(8) NULL,
  height        TINYINT UNSIGNED NULL, 
  debut_date    DATE NULL
);

구매 테이블 생성 SQL

CREATE TABLE buy 
(  num         INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
   mem_id      CHAR(8) NOT NULL, 
   prod_name     CHAR(6) NOT NULL, 
   group_name     CHAR(4) NULL ,
   price         INT UNSIGNED NOT NULL,
   amount        SMALLINT UNSIGNED  NOT NULL ,
   FOREIGN KEY(mem_id) REFERENCES member(mem_id)
);