이번에는 예제를 통해 키에 대해 좀 더 상세히 알아보고,
이상현상에 대해 자세히 다뤄보고자 한다.
* 이전에 키에 대해 정리한 글은 아래 링크를 확인해주세요.
https://bio-logisch.tistory.com/14
[SQL] 데이터베이스 제약조건 "키" - 기본키, 외래키, 슈퍼키, 후보키, 대체키
키에 대해 이해하려면 먼저 유일성과 최소성이라는 단어를 이해해야 한다. 유일성은 중복값 없이 단 하나만 존재한다는 의미이다. 하나의 키값만으로도 하나의 튜플을 식별할 수 있어야 한다.
bio-logisch.tistory.com
예제 1
- 키의 조합 : 아이디, 이름, 비밀번호, 나이, 등급, 가입날짜 모두 가능하다.
(키의 조합은 각 컬럼 하나가 될 수도 있고 키가 될 수 있는 여러가지 조합 모두를 말한다)
- 슈퍼키
슈퍼키는 유일성을 만족해야 한다.
(최소성을 만족하지 않음)
아이디를 키로 만들어서 유일성을 만족한다면,
아이디&비밀번호, ... , 아이디&이름&비밀번호&나이 와 같이 여러 개의 컬럼을 묶어서
키를 지정하면 유일성을 만족하는 조합이 가능하다.
- 후보키
후보키는 유일성과 최소성을 만족해야 한다.
예제에서는 후보키로는 아이디가 유일하게 가능하다.
왜 후보키로 아이디가 유일하게 가능하냐면 유니크한 정보값이기 때문이다.
아이디 외에 다른 데이터는 모두 중복될 가능성이 있다.
- 기본키
기본키는 유일성과 최소성을 만족해야 한다.
후보키 중에서 하나를 선택한다. 지금 예제에서는 후보키가 하나이므로 아이디를 선택해야 한다.
(참고로 위에 키는 1~3. 순서대로 뽑는다)
예제 2
- 키의 조합 : 아이디, 이름, 비번, 나이, 등급, 가입날짜, 주민번호
- 슈퍼키 : 아이디, 아이디&이름, 아이디&비밀번호, 아이디&나이, 아이디&등급, 아이디&가입날짜, 아이디&이름&비번, ...
- 후보키 : 아이디, 주민번호
- 기본키(후보키 중에서 하나 선정) : 아이디
보통 더 중요한 정보는 기본키로 설정하지 않으므로
주민번호를 키로 설정하는 것은 가능은 하지만 바람직하지 않다.
이 경우에는 하나의 컬럼만 인덱스를 지정하고자 하므로
아이디를 키로 지정하는 것이 낫다.
예제 3
자동차를 등록하는 테이블마다 기본키를 선정하시오.
| 순번 | 소유자아이디 | 자동차번호 | 소유자이름 | 자동차종류 | 등록일 |
- 후보키 : 순번, 자동차번호
(유니크한 정보로는 순번과 자동차번호가 있다)
- 기본키 : 순번
(후보키에서 하나만 정해야 하므로 순번으로 정한다.
자동차 번호가 순번보다 더 중요한 정보값이므로 키로 선정한다면
순번이 조금 더 낫다. 자동차 번호로 해도 되긴 하다.)
*** 테이블을 구성하는 속성에 unique, not null의 제약조건을 설정하고
인덱스를 형성하여 관리하므로 검색속도 향상에 이점을 가질 수 있다.
이론적으로는 어떤 걸 키로 정할지 정해지지 않아서
명령어 select를 가지고 어떤 인덱스가 검색이 빨리 되는지
속도를 체크해서 인덱스를 선정할 필요가 있다.
이 외에도 여러 방법을 동원하여 키를 선정하려는 노력이 필요하다.
예제3)에서 기본키를 순번으로 지정하긴 했지만
실제로 사람들이 검색할 때 순번이 아니라 자신의 자동차번호로 검색하는 경우가 더 많다고 한다면
후보키에 순번과 자동차번호가 있고 기본키를 골라야하는 경우 인덱스를 자동차번호로
지정하는 것이 더 합리적일 수 있다.
(즉, 키를 선정할 때 여러가지 관점에서 고민해야한다.)
예제 4
| 순번 | 소유자아이디 | 자동차번호 | 소유자이름 | 자동차종류 | 등록일 |
| 1 | aaa | 8754 | 홍길동 | 승용 | 2023-08-30 |
| 2 | bbb | 8759 | 일지미 | 승합 | 2023-08-31 |
| 3 | ccc | 7777 | 이지미 | 승용 | 2023-09-01 |
| 4 | aaa | 5555 | 홍길동 | 승합 | 2023-09-02 |
| 5 | ddd | 7799 | 홍길동 | 승합 | 2023-09-03 |
* 아이디가 같으면 같은사람이라고 가정한다. 아이디가 aaa인 홍길동이 차가 2대라고 생각하기.
이 테이블에는 어떤 문제가 있을까?
- 중복문제가 발생한다는 점이다!
여기서 중복이 문제가 되는 이유는 이어서 설명해보고자 한다.
그렇다면 중복으로 인해 발생하는 문제를 어떻게 해결할 수 있을까?
즉, 어떻게 하면 중복을 최소화할 수 있을까?
어떻게 해결할 수 있는지 살펴보려면 먼저 이상현상에 대해 알아봐야 한다.
이상현상 : 현실 세계의 값과 데이터베이스에 저장된 값이 일치하지 않는 경우를 말하며,
크게 3가지 종류가 있다. (삽입이상, 삭제이상, 수정이상(=갱신이상))
이상현상은 정규화 과정을 통해 해결한다.
정규화란 관계형 데이터 모델에서 데이터의 중복성을
제거하여 이상현상을 방지하고, 데이터의 일관성과
정확성을 유지하기 위해 무손실 분해하는 과정이다.
1) 삽입이상
튜플을 삽입할 때 무결성이 깨질 수 있다.
어떤 사람은 삽입이상을 튜플 삽입 시 특정 속성에 해당하는 값이 없어 NULL값을
입력해야하는 현상을 말한다고 정의했다. NULL값이 발생하거나 불필요한 정보를
함께 저장하지 않고서는 어떤 정보를 저장하는 것이 불가능한 상태에 해당한다.
2) 삭제이상
특정 튜플이 삭제할 때 삭제되면 안되는 튜플까지 연쇄 삭제된 경우에 해당한다.
aaa 아이디로 탈퇴했는데 '홍길동'이라는 소유자 이름으로 삭제했다.
그러면 위 표에서 순번1번, 4번 데이터만 삭제되어야 하는데 5번 데이터까지 모두 사라지는 오류가 생긴다.
홍길동 이름으로 삭제가 되면 5번 튜플 데이터인 7799자동차 정보가 손실될 수 있다.
필요한 정보를 함께 삭제하지 않고서는 어떤 정보도 삭제하는 것이 불가능한 상태를 의미한다.
3) 수정이상(=갱신이상)
특정 데이터의 값이 변경하는데 변경되어서는 안되는 다른 데이터 값도
같이 변경된 경우에 해당한다. (수정 시 데이터의 일관성이 깨지는 현상이 발생함)
aaa 아이디의 이름을 개명해서 홍길동에서 홍길수로 바꿨다. 수정을 소유자 이름 기준으로
수정하면 5번 튜플의 7799자동차 소유주 이름도 홍길수로 바뀌어버려서 오류가 생긴다.
(홍길동이라는 이름의 회원이 여러 명 있다면 문제가 발생할 수 있다.)
다시 테이블로 돌아와서 어떻게 해결해야 할까?
내가 찾은 답은, 위의 테이블을 두 개의 테이블로 분리한다는 것이다.
| 순번 | 소유자아이디 | 자동차번호 | 소유자이름 | 자동차종류 | 등록일 |
| 1 | aaa | 8754 | 홍길동 | 승용 | 2023-08-30 |
| 2 | bbb | 8759 | 일지미 | 승합 | 2023-08-31 |
| 3 | ccc | 7777 | 이지미 | 승용 | 2023-09-01 |
| 4 | aaa | 5555 | 홍길동 | 승합 | 2023-09-02 |
| 5 | ddd | 7799 | 홍길동 | 승합 | 2023-09-03 |
테이블을 쪼개면 중복을 최소화 시킬 수 있다. 최소화라고 표현한 이유는,
중복이 절대 발생하지 않도록 하는 것은 데이터가 쌓이면 쌓일수록 매우 어렵기 때문이다.
그런 이유로 중복을 없애는 것이 아니라 최소화하는 방향으로 개선해보고자 했다.
테이블을 쪼개는 과정을 정규화(Normalization)라 한다.(테이블을 정규화시켜야한다.)
다른 누군가(부모 릴레이션)를 참조하고 있는 특징을 가지는게 외래키인데
아래 테이블 A에서 소유자 아이디가 제약조건 foreign key(외래키)에 해당한다.
자동차 번호를 기본키로 보고자 한다.
자동차는 한 대당 하나의 번호만 가질 수 있으니 기본키로 지정하는 것이 적절하기 때문이다.
테이블A에 있는 외래키(소유자아이디)가 부모 릴레이션(테이블 B)에 있는 소유자아이디를 참조하는 것이다.
여기서 부모 릴레이션은 소유자 정보 테이블을 말한다.
아래와 같이 테이블을 두 개로 분리시킬 때 유의할 점은,
부모 릴레이션이 자식 릴레이션보다 먼저 생성되어야 한다는 점이다!
자식 릴레이션이 부모 릴레이션을 참조하기 때문에 부모 릴레이션이 없다면
참조할 대상이 없다는 의미이므로 오류가 발생한다.
테이블 A >> 자식 릴레이션
| 기본키 | 외래키 | |||
| 순번 | 자동차번호 | 자동차종류 | 등록일 | 소유자 아이디 |
| 1 | 8754 | 승용 | 2023-08-30 | aaa |
| 2 | 8759 | 승합 | 2023-08-31 | bbb |
| 3 | 7777 | 승용 | 2023-09-01 | ccc |
| 4 | 5555 | 승합 | 2023-09-02 | aaa |
| 5 | 7799 | 승합 | 2023-09-03 | ddd |
테이블 B >> 부모 릴레이션(소유자 정보 테이블)
| 소유자 아이디 | 소유자이름 |
| aaa | 홍길동 |
| bbb | 일지미 |
| ccc | 이지미 |
| ddd | 홍길동 |
'DB > SQL - Oracle' 카테고리의 다른 글
| [SQL] DML(데이터 조작어) - insert, update, delete (0) | 2024.02.02 |
|---|---|
| [SQL] DDL(데이터 정의어) - create, drop (0) | 2024.02.02 |
| [SQL] 데이터베이스 제약조건 "키" - 기본키, 외래키, 슈퍼키, 후보키, 대체키 (1) | 2024.02.01 |
| [SQL] 데이터 무결성의 종류 - 도메인, 개체, 참조 무결성 (0) | 2024.02.01 |
| [SQL] SQL과 RDBMS (관계형 DBMS), DBMS 유형 정리 (0) | 2024.01.28 |