[CS] [데이터베이스] 정규화

정규화란

• 정규화(Normalization)
  • 관계형 데이터베이스 설계에서 중복을 최소화하도록 데이터를 구조화하는 프로세스
  • 일반적으로 정규화는 크고, 제대로 조직되지 않은 테이블과 관계들을 작고 잘 조직된 테이블과 관계들로 나누는 것

 

정규화의 목적

• 중복을 배제하여 삽입, 삭제, 갱신 이상의 발생을 방지
• 각 릴레이션에 중복된 종속성을 여러 릴레이션에 분할
• 어떠한 릴레이션이라도 데이터베이스 내에서 표현 가능
• 데이터 삽입 시 릴레이션을 재구성할 필요성이 감소
• 효과적인 검색 알고리즘 생성 가능

 

 

Key

 

• 키(key): 릴레이션에서 투플들을 유일하게 구별하는 속성 또는 속성들의 집합
  • 기본 키(primary key)
    • 후보키 중에서 기본적으로 사용하기 위해 선택한 키
    • NULL값을 가질 수 있는 후보키는 부적합
    • 값이 자주 변경될 수 있는 속성을 가진 후보키는 부적합
    • 단순한 후보키를 선택
  • 후보키(candidate key)
    • 유일성과 최소성 둘 다 만족하는 속성 또는 속성들의 집합
    • 최소의 슈퍼키
  • 대체키(alternate key)
  • 외래키(foreign key)
  • 슈퍼키(super key)

 

Key의 특성

•  유일성(Uniqueness)
  • 하나의 릴레이션에서 모든 투플은 서로 다른 키 값을 가져야 한다.
• 최소성(minimality)
  • 꼭 필요한 최소한 속성들로만 키를 구성

 

 

함수적 종속성(Functional Dependency)

• 정규화를 위해서는 속성들 간 관련성을 파악해야 함.
  • → 이런 속성들 간의 관련성이 함수적 종속성(Functional Dependency) 
•  일반적으로 1개의 릴레이션에는 1개의 함수적 종속성만 존재하도록 정규화
• 함수적 종속성은 X → Y 로 표현
  • X는 결정자, Y는 종속자로 하며 X가 Y를 함수적으로 결정함.

 

함수적 종속성의 종류

• 완전 함수적 종속성(Full Functional Dependency)
  • 종속자가 기본키에만 종속
  • 기본키가 여러 속성으로 구성되어 있을경우 기본키를 구성하는 모든 속성이 포함된 기본키의 부분집합에 종속된 경우
• 부분 함수적 종속성(Partial Functional Dependency)
  • 릴레이션에서 종속자가 기본키가 아닌 다른 속성에 종속되거나,
  • 기본키가 여러 속성으로 구성되어 있을경우 기본키를 구성하는 속성 중 일부만 종속된 경우
• 이행적 함수적 종속성(Transitive Functional Dependency)
  • 릴레이션에서 X, Y, Z라는 3 개의 속성이 있을 때 X→Y, Y→Z 이란 종속 관계가 있을 경우, X→Z가 성립
  • 즉, X를 알면 Y를 알고 그를 통해 Z를 알 수 있는 경우

 

ex) 🏠 하우스 렌탈 릴레이션

고객ID	고객  이름	숙소ID	숙소주소	1박당 가격	체크인  날짜	체크아웃 날짜	호스트  ID	호스트 이름
CR76	김눈송	PG4	12 Bourchier St, London	￦ 600,000	7/2/22	7/11/22	CO40	Tina Murphy
CR76	김눈송	PG71	Av. Emile Zola, 75015, Paris	￦ 300,000	7/11/22	7/19/22	CO94	Mathilde Tellier
CR56	이숙명	PG4	12Bourchier St, London	￦ 600,000	7/18/22	7/22/22	CO40	Tina Murphy
CR54	이숙명	PR36	13 Regency St, London	W270,000	7/22/22	7/27/22	CO90	Noah Smith

 

하우스 렌탈 릴레이션의 후보키(Candidate Key)

• {고객ID, 숙소ID} 복합키
• {고객ID, 체크인날짜} 복합키
• {숙소ID, 체크인날짜} 복합키

 

하우스 렌탈 릴레이션의 기본키(Primary Key)

⇒ {고객ID, 숙소ID} 복합키

 

 

• 하우스 렌탈 릴레이션의 함수적 종속성(Functional Dependnecy)
  • {고객ID, 숙소ID} → 체크인날짜, 체크아웃 날짜 (Full FD)
    • {고객ID, 숙소ID}가 둘 다 있어야지만 해당 고객이 예약한 예약한 체크인 날짜와 체크아웃 날짜를 알 수 있고, 부분집합인 고객ID 또는 숙소ID만으로는 알 수 없다.
    • 따라서 체크인 날짜와 체크아웃 날짜는 기본키에 완전 종속되어있다.
    • {고객 ID, 숙소 ID}로 체크인 날짜와 체크아웃 날짜를 결정할 수 있습니다.
  • 고객 ID → 고객 이름 (Partial FD)
    • 고객 ID를 통해서 고객 이름을 알 수 있다.
    • 이 경우 기본키 {고객ID, 숙소ID}의 부분 집합인 고객ID에 종속되므로 부분 함수적 종속이라고 할 수 있습니다.
    • 고객 ID로 고객 이름을 결정할 수 있습니다.
  • 숙소ID → 숙소주소, 1박비용, 호스트ID, 호스트이름 (Partial FD)

 

제 1 정규형 (1NF)

각 로우마다 컬럼의 값이 1개만 있어야 한다 → 컬럼이 원자값(Atomic Value)를 갖는다.

 

다음은 1정규형을 만족하는 테이블이라고 할 수 있습니다. 하지만 2정규형 원칙에는 위배됩니다.

제목	지은이	ISBN	주제	출판사
데이터베이스 시스템	Abraham Silberschatz	9791132108504	컴퓨터/IT	한빛아카데미
데이터베이스 시스템	Henry F.Korth	9791132108504	컴퓨터/IT	한빛아카데미
데이터베이스 시스템	S.Sudarshan	9791132108504	컴퓨터/IT	한빛아카데미
앵무새 죽이기	하퍼 리	9788932917207	소설	열린책들

 

 

제 2정규형 (2NF)

제 1정규형에 속하고, 기본키가 아닌 모든 속성들이 기본키에 완전 함수 종속성이 되면 2NF에 속합니다. (부분 함수 종속성 제거할 경우)

• 고객 이름은 기본키의 부분 집합인 고객ID에,
• 숙소주소, 1박당 가격, 호스트ID, 호스트이름은 기본키의 부분집합인 숙소 ID에 부분적으로 종속된 관계
  • → 렌탈 릴레이션, 고객 릴레이션, 숙소-호스트 릴레이션 으로 나누기

렌탈 릴레이션 (기본키: {고객ID, 숙소ID})

고객ID	숙소ID	체크인날짜	체크아웃날짜
CR76	PG4	7/2/22	7/11/22
CR76	PG71	7/11/22	7/19/22
CR56	PG4	7/18/22	7/2/22
CR56	PG37	7/22/22	7/27/22
CR91	PG5	7/5/22	7/7/22

 

고객 릴레이션 (기본키: 고객ID)

고객ID	고객이름
CR76	김눈송
CR56	이숙명
CR91	최송

 

숙소-호스트 릴레이션 (기본키: 숙소ID)

숙소ID	숙소 주소	1박 당 가격	호스트ID	호스트이름
PG4	12 Bourchier St, London	200	CO40	Tina Murphy
PG71	Av. Emile Zola, 75015, Paris	400	CO94	Mathilde Tellier
PG36	13 Regency St, London	270	CO90	Noah Smith
PG5	8 Knaresborough Pl, London	300	CO40	Tina Murphy

 

→ 이제 부분 종속성이 제거되었습니다. 이제 이 릴레이션들은 1NF, 2NF를 모두 만족

 

제 3정규형(3NF)

테이블이 제 2 정규형을 만족하고, 테이블 내의 모든 속성이 기본 키에만 의존하며,  다른 후보 키에 의존하지 않으면 제 3 정규형을 만족

(기본키가 아닌 모든 속성이 기본키에 이행적 함수 종속(Transitive Functional Dependency)이 되지 않아야 한다.)

 

이행적 함수 종속은 X→Y, Y→Z면 X→Y인 관계입니다.

숙소ID → 호스트 ID

호스트ID → 호스트이름

숙소ID → 호스트이름의 관계가 성립됩니다.

숙소ID로 호스트이름을 결정짓는 건 의미가 이상합니다. 이는 두 개의 테이블로 나누면 보다 의미가 명확해집니다.

• 렌트를 위한 숙소 릴레이션(숙소ID → 숙소주소, 1박 당 가격, 호스트ID)
• 호스트 릴레이션(호스트ID → 호스트이름)

 

렌트를 위한 숙소 릴레이션 (기본키: 숙소ID)

숙소ID	숙소 주소	1박 당 가격	호스트ID
PG4	12 Bourchier St, London	200	CO40
PG71	Av. Emile Zola, 75015, Paris	400	CO94
PG36	13 Regency St, London	270	CO90
PG5	8 Knaresborough Pl, London	300	CO40

 

호스트 릴레이션 (기본키: 호스트ID) - 중복제거

호스트ID	호스트이름
CO40	Tina Murphy
CO94	Mathilde Tellier
CO90	Noah Smith

 

BCNF (Boyce/Codd Normal Form)

제 3 정규형이고, 결정자이면서 후보키가 아닌 함수 종속 제거해야 함

→ 모든 결정자가 후보키여야 한다.

 

 

 

• BCNF를 만족하지 않는 릴레이션

학번	과목	교수
100	데이터베이스	홍길동
100	자료구조	임꺽정
200	네트워크	장영실
300	인공지능	유관순

• 제약사항
  • 한 학생은 동일한 과목에 대해 한교수에게만 수강가능
  • 각 교수는 하나의 과목만 담당
  • 한 과목은 여러 교수가 담당가능
• 분석
  • (학번 + 과목)은 교수를 결정 짓는다.
  • 교수는 과목을 결정 짓는다.
  • 즉, 교수 또한 결정자인데 교수는 학번을 결정 지을 수 없으므로 후보키는 아니다.
    • → 교수 속성 분리

 

• BCNF를 만족시키는 릴레이션

학번	과목 코드
100	DB01
100	DS01
200	NW01
300	AI01

 

과목 코드	과목	교수
DB01	데이터베이스	홍길동
DS01	자료구조	임꺽정
NW01	네트워크	장영실
AI01	인공지능	유관순