티스토리 뷰
관계형 데이터베이스 설계
설계 1이 더 좋다! 설계2의 경우 불필요한 데이터 중복이 있다. 설계 1은 데이터 중복이 있으나 필요한 데이터 중복이다.
- 설계1
- 학생 = {학번, 이름, 주소}, PK = {학번}
- 수강 = {학번, 과목번호, 성적}, PK = {학번, 과목번호}, FK = {학번}
- 설계2
- 학생_수강 = {학번, 과목번호, 이름, 주소, 성적}
- PK = {학번, 과목번호}
불필요한 중복의 문제점
- 디스크 공간을 낭비한다.
- Update가 아주 복잡해진다.
불필요한 중복이 생겼을 때의 해결책
- 스키마 나누기(Decomposition)
- Decomposition 할 때 고려할 점들은?
- 가짜 투플을 만들지 않는 나누기(필수), 함수적 종속을 유지하는 나누기(선택)
- 가짜 투플: 나뉜 두 테이블을 자연 조인했을 때 나온 결과 테이블에 나누기 전에 있던 테이블에는 없던 투플이 나오는 경우 이 투플을 가짜 투플이라고 한다.
가짜 투플이 생성되는 경우
- R={학번, 이름, 주소, 과목번호, 성적}
- FD1: {학번, 과목번호} -> {성적}
- FD2: {학번} -> {이름, 주소}
- 테이블을 두개로 나누면
- R1={학번, 이름, 주소}
- FD2: {학번} -> {이름, 주소}
- R2={학번, 과목번호, 성적}
- FD1: {학번, 과목번호} -> {성적}
- R1|X|R2
- 가짜 투플이 만들어지지 않는다!
가짜 투플이 생성되지 않는 경우
- R={학번, 이름, 주소, 과목번호, 성적}
- FD1: {학번, 과목번호} -> {성적}
- FD2: {학번} -> {이름, 주소}
- 테이블을 두개로 나누면
- R1={학번, 이름, 주소}
- FD2: {학번} -> {이름, 주소}
- R2={이름, 과목번호, 성적}
- R1|X|R2
- 가짜 투플이 만들어진다!
함수적 종속
- 가장 많이 쓰는 정규형인 BCNF와 3NF의 정의에 쓰인다.
- 정규형을 정의하는데 있어 함수적 종속을 사용한다.
- 관계형 스키마 R
- 수퍼 키 개념을 일반화한 것이다. = 수퍼 키는 함수적 종속 개념의 특수한 경우이다.
- a, b가 속성의 집합이라고 하면, 테이블의 인스턴스 r에 있는 모든 투플의 짝 t1, t2에 대하여
- t1.a=t2.a 이면, t1.b=t2.b라면, 그 스키마에 함수적 종속 a->b가 있다고 말한다.
- b가 전체 속성 집합이면 a가 수퍼키가 된다.
- b는 a에 종속된다.
- 수퍼키: 개체를 식별할 수 있는 키
Trival FD
- 뜻이 없는 함수적 종속
- a->b에서 오른쪽이 왼쪽의 부분 집합인 함수적 종속을 가리킨다.
- 자세히 분석하지 않더라도, 모든 테이블에서 늘 유효한 함수적 종속을 말한다.
함수적 종속 집합의 울타리 SFD(a set of FDs)가 주어질 때
- SFD에 포함되어 있지 않지만 유효한 함수적 종속 집합들을 이끌어낼 수 있다.
- 그렇게 이끌어낼 수 있는 함수적 종속들은 묵시적으로 SFD에 포함되어 있다고 생각한다.
- Closure of a SFD(SFD+): 가능한 모든 함수적 종속들의 집합
규범적 덮개
- 일종의 표준 SFD
- 예) 학생 테이블의 SFD의 경우 표현할 수 있는 방법이 여러개이다. 이중에서 뭐가 좋을까?
- {{학번}->{이름, 이수학점, 평점}}
- {{학번}->{이름}, {학번}->{이수학점}, {학번}->{평점}}
군더더기 속성
- 주어진 함수적 종속 집합에서 어떤 함수적 종속의 왼쪽 또는 오른쪽에 있는 속성 집합에서 어떤 속성을 빼더라도 SFD+가 바뀌지 않을 때 그 속성을 군더더기 속성이라고 한다.
- 예) 학생 테이블의 경우 {{학번}->{학번, 이름, 이수학점, 평점}}으로 나타낼 경우, 오른쪽 속성 집합에 있는 학번은 군더더기 속성이 된다.
✖️ 주어진 SFD에 대한 규범적 덮개(SFDc)는 다음 조건을 만족해야 한다.
- SFD+ = (SFDc)+이다
- SFDc에 있는 어떤 함수적 종속에도 군더더기 속성이 없다.
- SFDc에 있는 모든 함수적 종속의 왼쪽은 유일하다.
규범적 덮개 만드는 과정
- 기본 키를 찾은 후, 기본 키 -> 나머지 속성 집합으로 이루어진 함수적 종속을 만든다. 이걸 FD1이라고 하자.
- 기본 키의 진부분집합이 왼쪽인 함수적 종속을 찾은 후, FD1에서 군더더기 속성을 제거한다.
- 나머지 속성 집합의 진부부집합이 왼쪽인 함수적 종속을 찾은 후, FD에서 군더더기 속성을 제거한다.
함수적 종속 찾기 실습
뜻이 있는 함수적 종속들을 찾아보자. 규범적 덮개 형태로 정리해 보자.
수강 신청 DB
일반적인 대학교에서의 수강 신청 관련 DB를 다음과 같이 설계하였다.
- 학생_수강_과목 = {학번, 이름, 과목번호, 과목이름, 연락처, 성적등급}, PK = {학번, 과목번호}
FD1 : {학번, 과목번호} -> {이름, 과목이름, 연락처, 성적등급}
FD2 : {학번} -> {이름, 연락처}
FD1' : {학번, 과목번호} -> {과목이름, 성적등급}
FD3 : {과목번호}->{과목이름}
FD1'' : {학번, 과목번호} -> {성적등급}
규범적 덮개: FD1'', FD2, FD3
✖️ FD1은 규범적 덮개에서는 빠지지만 울타리에서는 빠지는게 아니다.
온라인 쇼핑몰
일반적인 온라인 쇼핑몰에서의 주문 관련 DB를 다음과 같이 설계하였다.
단, 한 번 주문할 때 상품은 한 종류만 주문할 수 있다고 가정한다.
- 회원_주문_상품 = {주문번호, 회원번호, 회원이름, 회원주소, 회원연락처, 상품번호, 상품이름, 주문개수, 배송연락처, 배송주소}, PK={주문번호}
FD1 : {주문번호}->{회원번호, 회원이름, 회원주소, 회원연락처, 상품번호, 상품이름, 주문개수, 배송연락처, 배송주소}
FD2 : {회원번호}->{회원이름, 회원주소, 회원연락처}
FD3 : {상품번호}->{상품이름}
FD1' : {주문번호}->{회원번호, 상품번호, 상품이름, 주문개수, 배송연락처, 배송주소}
------- 회원번호가 있어야 함. FD2의 오른쪽에 있는 애들만 빼야 함.
규범적 덮개: FD1', FD2, FD3
FD1 : {주문번호}->{회원번호, 회원이름, 회원주소, 회원연락처, 상품번호, 상품이름, 주문개수, 배송연락처, 배송주소}
FD2 : {회원번호}->{회원이름, 회원주소, 회원연락처}
FD1' : {주문번호}->{회원번호, 상품번호, 상품이름, 주문개수, 배송연락처, 배송주소}
FD3 : {상품번호}->{상품이름}
FD1'' : {주문번호}->{회원번호, 상품번호, 주문개수, 배송연락처, 배송주소}
규범적 덮개: FD1'', FD2, FD3
학생, 학과, 지도교수
이 학교는 학생들이 복수전공을 신청할 수 있다.
학생들이 복수전공을 신청할 경우 지도교수는 학과별로 한 명씩 배정된다.
교수는 하나의 학과에만 소속되어 있다.
이 상황을 저장하기 위해 다음과 같은 속성들로 만들어진 테이블이 주어졌다.
- {학번, 학생이름, 학생연락처, 학생주소, 학과번호, 학과이름, 학과전화번호, 교수번호, 교수이름, 교수연락처}
- 기본키 찾기(이 테이블의 레코드는 한 학생이 하나의 전공을 신청할 때마다 하나씩 증가한다.)
PK: {학번, 학과번호}
FD1: {학번, 학과번호}->{학생연락처, 학생주소, 학과이름, 학과전화번호, 교수번호, 교수이름, 교수연락처}
FD2: {학번}->{학생연락처, 학생주소}
FD1': {학번, 학과번호}->{학과이름, 학과전화번호, 교수번호, 교수이름, 교수연락처}
FD3: {학과번호}->{학과이름, 학과전화번호}
FD1'': {학번, 학과번호}->{교수번호, 교수이름, 교수연락처}
FD4: {교수번호}->{교수이름, 교수연락처}
FD1''': {학번, 학과번호}->{교수번호}
규범적덮개: FD1''', FD2, FD3, FD4
전통적인 관계형 모델
이상(Anomaly)
- 테이블에서 일부 속성들의 종속으로 인해 데이터의 중복이 발생하고 이 중복으로 인해 테이블 조작 시 문제가 발생하는 현상
- 삽입 이상
- 삭제 이상
- 갱신 이상
| 학번 | 과목번호 | 성적 | 학년 |
| 100 | C413 | A | 4 |
| 100 | E412 | A | 4 |
| 200 | C123 | B | 3 |
| 300 | C312 | A | 1 |
| 300 | C324 | C | 1 |
- 삽입 이상
- 수강 테이블에 신입생(학번: 400, 학년: 1)을 삽입할 때 이상이 발생
- 과목번호가 없으므로 삽입할 수 없음
- 삭제 이상
- 수강 테이블에서 학번이 200인 학생이 과목번호 C123의 등록을 취소하고자 하는 경우 삭제 이상이 발생
- 과목번호만 삭제되어야 하는데 학년 정보까지 삭제된다.
- 갱신 이상
- 학번이 300인 학생의 학년을 1에서 2로 갱신하고자 하는 경우, 하나의 튜플만 갱신하면 갱신 이상이 발생
정규형과 정규화
정규화의 개념
- 테이블의 속성들이 상호 종속적인 관계를 갖는 특성을 이용하여 테이블을 무손실 분해하는 과정으로, 정규화의 목적은 가능한 한 중복을 제거하여 삽입, 갱신, 삭제 이상의 발생 가능성을 줄이는 것이다.
- 정규형: 1NF, 2NF, 3NF, BCNF, 4NF, 5NF
- 보통 제3정규형이나 BCNF까지 가면 괜찮게 됐다고 판단함.
- 예제 테이블: 주문 목록
- 제 1정규형
- 테이블 R에 속한 모든 속성의 도메인이 원자 값만으로 되어 있는 정규형, 따라서 주문 목록은 제1정규형이 아니다.
- 함수적 종속 PK->A이 있고, B는 PK와 A를 제외한 속성들의 집합일 때, PK와 A로 이루어진 테이블과 PK와 B로 이루어진 테이블로 분해한다.
- 따라서 주문 목록은 다음과 같이 분해된다.
- 제 2정규형
- 테이블 R이 제1정규형이고, 기본 키가 아닌 모든 속성이 기본 키에 대하여 완전 함수적 종속을 만족하는 정규형, 따라서 부분 함수적 종속을 완전히 제거해야 한다.
- 제품 테이블의 함수적 종속
- {제품번호}->{제품명, 재고수량}
- 제품 테이블의 경우 기본키인 {제품번호}에 의해 다른 속성들이 모두 결정되고, 기본 키의 진부분집합이 존재하지 않으므로 부분 함수적 종속이 없다.(따라서 제2정규형이다.)
- 제품 주문 테이블의 함수적 종속
- FD1: {주문번호, 제품번호}->{고객번호, 주소, 주문수량}
- FD2: {주문번호} -> {고객번호, 주소}
- FD3: {고객번호} -> {주소}
- FD2의 왼쪽이 기본키의 진부분집합이므로 부분 함수적 종속이 존재한다. 따라서 제2정규형이 아니다.
✖️ 완전함수적종속: 기본 키만이 다른 속성들의 값을 결정
- 제 3정규형
- 테이블 R이 제2정규형이고, 기본키가 아닌 모든 속성이 기본키에 대하여 이행적 함수적 종속을 만족하지 않는 정규형, 따라서 이행적 함수적 종속을 완전히 제거해야 한다.
- 이행적 함수적 종속: A->B이고, B->C일 때 A->C를 만족하는 함수적 종속
- 제품 테이블의 함수적 종속
- {제품번호}->{제품명, 재고수량}
- 주문 목록 테이블의 함수적 종속
- {주문번호, 제품번호}->{주문수량}
- 제품 테이블과 주문 목록 테이블의 경우 기본키에 대해 다른 속성들이 모두 결정되고, 기본키를 제외한 다른 속성들 간에 함수적 종속이 존재하지 않으므로 이행적 함수적 종속이 없다.(따라서 제3정규형이다.)
- 주문 테이블의 함수적 종속
- FD1: {주문번호}->{고객번호, 주소}
- FD2: {고객번호}->{주소}
- {주문번호}->{고객번호}이고 {고객번호}->{주소}이므로 {주문번호}->{주소}라는 이행적 함수적 종속이 존재한다. 따라서 제3정규형이 아니다.
- 제3정규화 원리
- 모든 함수적 종속에서 불필요한 속성을 제거한다. 이후, 각각의 함수적 종속들로 이루어진 릴레이션을 만든다. 만약 원래 릴레이션의 후보키를 포함하는 릴레이션이 없으면 후보키로 이루어진 릴레이션을 만든다. 따라서 주문은 다음과 같이 분해된다.
- 불필요한 속성이 제거된 함수적 종속들
- FD1 : {주문번호} -> {고객번호}
- FD2: {고객번호} -> {주소}
- 보이스-카드 정규형
- 테이블 R에서 모든 결정자가 후보키인 정규형
- 불필요한 속성이 없는 모든 함수적 종속의 오니쪽이 모두 후보키
- 제3정규형에 후뵈키가 여러 개 존재하고 후보키들이 서로 중첩되어 나타나는 경우 적용 가능하다.
- 아래 예제 테이블에 존재하는 함수적 종속
- FD1: {학번, 과목명}->{담당교수}
- FD2: {담당교수}->{과목명}
- 부분적 함수적 종속도 없고 이행적 함수적 종속도 없다. 따라서 제3정규형이다.
- 부분적 함수적 종속이 없는 이유가 이해가 안돼서 챗지피티한테 물어봄.
- 수강 교수 테이블은 제3정규형이지만 FD2의 왼쪽이 후보키가 아니므로 보이스-카드 정규형이 아니다.
- 보이스-카드 정규화 원리
- 보이스-카드 정규형 규칙에 어긋나는 함수적 종속의 왼쪽을 A, 오른쪽을 B, 나머지 속성 집합을 C라고 할 때, A와 B로 이루어진 릴레이션과 A와 C로 이루어진 릴레이션으로 분해한다. 따라서 수강 교수는 다음과 같이 분해된다.
그 외의 정규형
- 제4정규형: 테이블 R에 다중값 종속 A->>B가 존재할 경우 R의 모든 속성이 A에 함수적 종속 관계를 만족하는 정규형
- 제5정규형: 테이블 R의 모든 조인 종속(JD)이 R의 후보키를 통해서만 성립되는 정규형
- 역정규화: 정규화로 분해가 너무 많이 되어서 조인이 너무 많이 필요해서 성능이 떨어진다면 오히려 테이블을 다시 합치는게 낫다. 이것을 역정규화라고 한다.
외우는법: 두부이걸다줘
| 도(메인이 원자값) | 제1정규화 |
| 부(분적 함수 종속 제거) | 제2정규화 |
| 이(행적 함수 종속 제거) | 제3정규화 |
| 결(정자이면서 후보키가 아니면 제거) | BCNF |
| 다(치 종속 제거) | 제4정규화 |
| 조(인 종속성 이용) | 제5정규화 |
역정규화
- 시스템의 성능 향상, 개발 및 운영의 편의성 등을 위해 정규화된 데이터 모델을 통합, 중복, 분리하는 과정, 의도적으로 정규화 원칙을 위배하는 행위
- 역정규화를 수행하면 시스템 성능이 향상되고 관리 효율성은 증가하지만 데이터의 일관성 및 정합성이 저해될 수 있다.
- 과도한 역정규화는 오히려 성능을 저하시킬 수도 있다.
- 역정규화를 위해서는 사전에 시스템의 목적을 데이터 일관성, 무결성을 우선으로 할지, DB의 성능과 단순화를 우선으로 할지 정해야 한다.
- 방법: 테이블 통합, 테이블 분할, 중복 테이블 추가, 중복 속성 추가
- 테이블 통합
- 두 개의 테이블을 조인하는 경우가 많을 때, 그냥 통합하는 것.(두 개의 테이블을 이용하여 항상 조회를 수행하는 경우)
- 종류: 1:1 관계 테이블 통합, 1:N 관계 테이블 통합, 슈퍼타입/서브타입 테이블 통합
- 고려 사항
- 데이터 검색은 간편하지만 레코드 증가로 처리량 증가
- 입력, 수정, 삭제 규칙이 복잡해질 수 있다.(이상 발생 가능성 증가)
- Not null, Default, Check 등의 제약 조건 설계가 어렵다.
- 테이블 분할
- 테이블을 수직, 혹은 수평으로 분할하는 것
- 수평 분할: 레코드를 기준으로 테이블을 분할, 레코드별로 사용 빈도 차이가 큰 경우
- 수직 분할: 속성을 기준으로 테이블을 분할, 속성이 너무 많을 때
- 갱신 위주의 속성 분할: 데이터 갱신할 때 레코드 잠금, 갱신이 자주 일어나는 속성을 분할
- 자주 조회되는 속성 분할: 자주 조회되는 속성이 극히 일부일 때
- 크기가 큰 속성 분할: 이미지 혹은 2GB 이상의 텍스트 형식 등
- 보안을 적용해야 하는 속성 분할: 특정 속성에 대해서만 보안을 적용
- 분할 시 고려사항
- 기본키의 유일성 관리가 어려워진다.
- 데이터 양이 적거나 사용 빈도가 낮으면 굳이 분리해야 할까?
- 수행 속도가 느려질 수도 있다.
- 데이터 검색에 중점을 두고 분할하자...
- 중복 테이블 추가
- 여러 테이블에서 데이터를 추출해서 사용해야 하거나 다른 서버에 저장된 테이블을 이용해야 하는 경우 중복 테이블을 추가하여 작업 효율 향상 가능
- 중복 테이블을 추가하는 경우
- 정규화로 인한 수행 속도 감소
- 많은 범위의 데이터를 자주 처리
- 특정 범위의 데이터를 자주 처리
- 처리 범위를 줄이지 않고는 수행 속도를 개선할 수 없는 경우
- 추가하는 방법
- 집계 테이블: 집계를 위한 테이블 + 원본 테이블에 트리거
- 진행 테이블: 이력 관리, 백업
- 특정 부분만을 포함하는 테이블
- 중복 속성 추가
- 조인해서 데이터를 처리할 때 조회 경로의 단축을 위해 자주 사용하는 속성을 추가하는 것.
- 중복 속성을 추가하는 경우
- 조인이 자주 발생하는 속성
- 접근 경로가 복잡한 속성
- 액세스의 조건으로 자주 사용되는 속성
- 기본키의 형태가 적절하지 않거나 여러 개의 속성으로 이루어진 경우
- 고려 사항
- 테이블 중복과 속성의 중복을 고려
- 데이터 일관성 및 무결성에 유의
- SQL 그룹 함수를 이용하여 처리할 수 있어야
- 저장 공간의 지나친 낭비는 아닌지...
'CS > 인터넷DB응용' 카테고리의 다른 글
| [인터넷DB응용] 웹 서비스의 발전과 모바일 웹 서비스 (0) | 2023.06.11 |
|---|---|
| [인터넷DB응용] 데이터베이스 전체 설계 과정 (0) | 2023.06.09 |
| [인터넷DB응용] PL-SQL - 2 (0) | 2023.04.17 |
| [인터넷DB응용] PL-SQL - 1 (0) | 2023.04.16 |
| [인터넷DB응용] SQL 활용 - 3 (0) | 2023.04.14 |