Database Systems: Anomalies, Functional Dependencies, and Normalization Explained

Contents

1️⃣ 회사 데이터베이스 구현(Company Database Implementation)

2️⃣ 이상과 함수종속(Anomaly and Functional Dependencies)

3️⃣릴레이션 정규화(Relation Normalization)

1️⃣ 회사 데이터베이스 구현

(Company Database Implementation)

사원관리 시스템의 릴레이션 스키마 복습

✅ 사원관리 ERD ➡️릴레이션 스키마➡️ 테이블 설계 ➡️ 물리적데이터베이스 스키마➡️ 데이터 베이스 구현

지금까지 배운 데이터베이스 시스템의 정보를 바탕으로 위의 다이어그램이 "사원관리 시스템 ERD"라는 것을 바로 알 수 있다.
개체는 사원, 부서, 프로젝트로 구성되고 약개체로는 부양가족이 있다.
관계로는 소속, 관리, 참여, 수행, 감독, 부양이 있다.
관례는 개체와 개체사이의 연관된 정보를 표현하는 단위이다.
N:M관계는 별도의 릴레이션으로 표시한다.
1:N, N:1같은 다대다 관계는 한쪽 릴레이션에 포함해서 설계한다.

✅ 사원관리 ERD ➡️릴레이션 스키마➡️ 테이블 설계 ➡️ 물리적데이터베이스 스키마➡️ 데이터 베이스 구현

사원관리 ERD는 위와같은 릴레이션 스키마로 만들어진다.

✅ 사원관리 ERD ➡️릴레이션 스키마➡️ 테이블 설계 ➡️ 물리적데이터베이스 스키마 ➡️ 데이터 베이스 구현

각각의 릴레이션은 각각의 테이블로 대응이 된다.
테이블 설계시 여러가지 고려사항중 영문화 작업을 미리 표준화하여 보관하면 일관성있게 작업할 수 있다.

✅ 사원관리 ERD ➡️릴레이션 스키마➡️ 테이블 설계 ➡️ 물리적데이터베이스 스키마 ➡️ 데이터 베이스 구현

테이블 설계 이후엔 물리적데이터베이스 스키마 작업을 한다.

개체수에 맞게 테이블도 정의서도 작성한다.

릴레이션으로부터 테이블명과 컬럼명을 도출하여 각각의 테이블 구조를 정의한다.
논리적인 모델의 데이터 타입을 물리적인 DBMS의 특성과 성능을 고려하여 최적의 데이터 타입을 선택한다. 기본키, 외래키 등 제약조건을 정의해야 한다.

타입은 여러가지가 있지만 우 내가 생각했을 때 알맞는 타입을 설정한다.
NOT NULL에 Y가 있다면 NOT NULL을 허용하지 않는다는 뜻
외래키는 참조테이블과 참조컬럼을 포함한다.
8번의 직속상사는 SSN을 참조한다. 주의할 점은 이렇게 참조할땐 데이터 타입과 길이가 같아야한다.

Primary Key는 2개여도 된다.

약개체였던 부양가족은 사원번호와 이름을 Primary Key로 구성하였다.

✅ 사원관리 ERD ➡️릴레이션 스키마➡️ 테이블 설계 ➡️ 물리적데이터베이스 스키마 ➡️ 데이터 베이스 구현

데이터베이스 생성 ➡️ 각각의 테이블 생성 ➡️ 외래키 정의 ➡️ 초기데이터 입력

테이블을 만드는 명령어인 CREATE TABLE을 사용하여 내가 사용할 테이블의 묶음인 데이터베이스를 먼저 만든다. 사원관리 company 데이터베이스를 만들었다.
USE company : 는 company DB를 사용하겠다는 명령어

데이터베이스 생성 ➡️ 각각의 테이블 생성 ➡️ 외래키 정의 ➡️ 초기데이터 입력

company DB생성후엔 테이블 정의서를 참고하여 각각의 테이블을 만들어야한다.

데이터베이스 생성 ➡️ 각각의 테이블 생성 ➡️ 외래키 정의 (여기까지 하면 완벽하게 테이블 정의를 완성한 단계이다) ➡️ 초기데이터 입력

CONSTRAINT는 제약조건이라는 뜻
외래키는 나중에 별도로 지정한다. 데이터를 입력하는데 초기에 제약이 걸리기 때문이다.

데이터베이스 생성 ➡️ 각각의 테이블 생성 ➡️ 외래키 정의 ➡️ 초기데이터 입력

처음 생성된 데이터베이스는 구조만 있으며, 여기에 실제 운영 데이터들을 변환하고 입력한다. SQL의 INSERT INTO 명령어를 사용하여 초기 데이터를 입력한다. 외래키가 있는 경우 데이터 입력 순서에 주의해야 해야한다.

외래키때문에 부서테이블 데이터를 먼저 입력한다. 부서가 있어야 사원이 입력이 되기 때문이다.

입력된 데이터는 SELECT * FROM 으로 확인할 수 있다.

2️⃣ 이상과 함수종속

(Anomaly and Functional Dependencies )

지금까지 데이터베이스 구현을 복습했다. 데이터베이스 논리적 설계를 했을때 만들어진 릴레이션 스키마가 잘 설계되었는지 분석하고 문제가 있을시 별도의 릴레이션을 만들어주는 형태의 조치를 하기위해 필요한 부분이다. 이를 통해 데이터베이스의 효율성과 안정성을 향상시킬 수 있다.

위의 스키마는 잘 설계된 릴레이션 스키마이다. 하지만 모든 릴레이션이 잘 설계되지 않는다. 헷갈리는 경우가 많다.

위의 릴레이션을 예로 들어보자. 정보를 표현하는데는 문제가 없지만 3가지의 "이상, Anomaly" 문제가 생긴다.
1. 삽입이상 (Insertion Anomaly)
2. 삭제이상 (Deletion Anomaly)
3. 수정이상 (Modification Anomaly)

✅ 삽입이상 (Insertion Anomaly) 즉, 어떤 데이터를 삽입 하려고 할 때 불필요하고 원하지 않는 데이터도 함께 삽입해야만 하고 그렇지 않으면 삽입이 되지 않는 현상을 삽입 이상(insertion anomaly)라고 한다.

사원부서 릴레이션에 새로운 신입 사원 튜플을 삽입하려면 반드시 사원이 일하는 부서에 대한 정보를 명시하거나, 널 값을 명시해야 한다. 또한, 사원이 없는 새 부서를 사원부서 릴레이션에 삽입하기 어렵다. 사원번호가 기본키일 경우엔 아예 삽입이 안된다.

사원과 부서는 원래 독립된 정보인데 사원부서로 합쳤기 때문에 이런 문제가 일어난다.
강남길 신입사원의 DB를 삽입하기 위해선 정해지지 않은 소속부서등을 임의로 입력해야하기 때문에 불필요한 데이터를 삽입하게 된다.

✅ 삭제이상 (Deletion Anomaly) 한 튜플을 삭제함으로써 유지해야 될 정보까지도 삭제되는 현상이 일어나게 되어 정보의 손실이 발생하게 되는데 이러한 현상을 삭제 이상(deletion anomaly)라고 한다.

만약 어떤 부서에서 일하는 유일한 사원에 대한 튜플을 삭제하게 되면, 그 부서에 대한 정보도 데이터베이스에서 없어지게 된다.

유명희&한상진 사원들 퇴사한다고 영업부서가 사라지는 것은 아니다. 릴레이션은 튜플단위로 삭제가 되기 때문에 둘다 모두 퇴사하게되면 영업부가 사라지게 된다.

✅ 수정이상 (Modification Anomaly) 중복된 튜플들 중에서 일부 튜플의 애트리뷰트 값만을 갱신시킴으로써 정보의 모순성(inconsistency)이 생기는 현상을 수정 이상(Modification anomaly) 이라 한다.

D4번 부서의 책임자를 변경하면 그 부서에서 일하는 모든 사원 튜플에 대해서 갱신해야 한다. 만일 일부 튜플만 변경시킨다면 데이터베이스의 일관성이 없어지게 된다.

김미순&박성호의 책임자는 안준태였는데 안준태가 퇴사후 한상진으로 책임자가 바뀌었을시 김미순의 책임자만 갱신했기때문에 수정이상이 생긴다.

2️⃣ 이상 (Anomaly)

이상의 원인과 해결(Reason and solution for Anomaly)

이상(Anomaly)의 원인은 여러가지가 있지만

애트리뷰트들 간에 존재하는 여러 종속 관계를 하나의 릴레이션에 다 표현하려고 하니 이상이 발생하게 된다. 애트리뷰트들 간의 종속관계를 분석하여 여러 개의 릴레이션으로 분해(decomposition)하여 이상을 해결할 수 있다. 이렇게 종속관계를 파악해서 이상이 발생하지 않도록 릴레이션을 분해하는 과정을 정규화 (Normalization)이라고 한다.

이상의 해결(Solution for Anomaly) - 스키마 변환(Schema transformation)

모든 이상의 원인은 간단하게 정보를 하나의 릴레이션에서 표현하려고 하기 때문에 발생한다. 그것을 독립된 의미가 있는 여러 릴레이션으로 분해하는데 분해과정을 스키마 변환(Schema transformation)이라고 한다.

스키마 변환(Schema transformation) 수집된 결과를 명시된 제약 조건에 따라 여러 개의 릴레이션으로 분할한다. 스키마 변환의 원칙은 3가지로 나뉜다.

정보의 무손실 (Information preservation)
데이터의 중복성 감소 (Reduction of data duplication)
분리의 원칙 (Principle of separation) 애트리뷰트 15개를 서로 독립된 것으로 분리한다는 뜻

2️⃣함수 종속성 (Functional Dependencies)

이상(Anomaly)이 언제 생길지를 함수 종속성을 통해 파악할 수 있다. 함수 종속성은 조금 어려운데 의미적인 내용을 이해하려고 해보자.

데이터 애트리뷰트들의 의미와 애트리뷰트들 간의 상호 관계로부터 유도되는 제약조건의 일종이다. ‘애트리뷰트들의 집합 X의 값이 애트리뷰트들의 집합 Y의 값을 유일하게(unique) 결정한다면 X는 Y를 함수적으로 결정한다(functionally determines)’고 정의한다.

스키마만 본다면 함수종속성을 분석할 수 없다. 함수 종속은 데이터의 의미(data semantics)를 표현한다. (애트리뷰트들의 값과 다른 값의 관계 등) 예를들어 학년이 학번에 종속되어 있다는 것은 학번이 지정되면 학년 값이 유일하게 결정되어야 한다.

학번이 입력되면 학생의 이름도 찾을 수 있고 학년도 찾을 수 있다. 연락처도 마찬가지이다. 반대로 어느 학생의 이름만 알고 있다면 동명이인일 경우도 있기 때문에 다른 정보를 유일하게 결정할수 없게 된다. 이 데이터베이스는 잘못된 데이터를 가지고 있다는 것을 의미하게 된다.

함수 종속성의 예제 - 1( Example of Functional Dependencies - case 1) 각각의 애트리뷰트가 어떤 의미를 갖는가?

사원번호는 사원명을 함수적으로 결정한다. 사원번호 ➔ 사원명

과제번호는 과제명과 수행장소를 결정한다. 과제명이 유일하다는 가정하에 수행장소도 알수 있다. 과제번호 ➔ {과제명, 수행장소}

사원의 사원번호와 프로젝트의 과제번호는 그 사원이 일주일 동안 그 프로젝트를 위해서 일하는 시간을 결정한다. 사원번호로만으로는 시간을 알 수 없다. 하나의 사원이 여러개의 과제를 할 수 있기 때문이다. 과제번호 A 는 과제 수행시간을 결정할 수 없다. 결국 시간 값을 결정하는 것은 사원번호와 과제번호의 조합이다. {사원번호, 과제번호} ➔ 참여시간

함수 종속성의 예제 - 2( Example of Functional Dependencies - case 2) 표시된 화살표처럼 종속관계가 있는지 파악해보는 것

사원번호는 기본키로써 사원명, 생년월일, 주소, 소속부서번호를 유일한 값으로 결정할 수 있다.
사원명이 생년월일을 특정할 순 없다. 동명이인이 있을 수 있기 때문이다.
사원번호는 부서명과 부서 책임자 또한 측정할 수 있다.

함수 종속성은 완전 함수 종속(Full functional dependency)과 부분 함수 종속(Partial fictional dependency)으로 나뉜다.

3️⃣릴레이션 정규화

(Relation Normalization)

정규화는 관계형 데이터에서만 발생한다. 그 이유를 설명하자면 릴레이션은 테이블의 형태로 표현하는 것이다. 여러개의 테이블이 만들어지고 그 테이블이 중복을 제거하기 위해서 분해를 하는 것이고 그 과정을 정규화라고 부르기 때문이다.

정규화는 데이터베이스 논리적 설계단계에서 진행한다.

종속관계를 파악해서 이상이 발생하지 않도록 릴레이션을 분해하는 과정을 정규화 (Normalization)이라고 한다고 배웠다. 어떻게 릴레이션 정규화가 되는지 자세히 알아보자.

정규화의 개념

무손실 표현: 같은 의미의 정보 유지, 그러나 더 바람직한 구조
데이터 중복성 감소
분리의 원칙: 독립적인 관계는 별개의 릴레이션으로 표현한다. 릴레이션 각각에 대해 독립적인 조작 기능을 한다.

정규화의 원칙

서로 독립적인 관계는 별개의 릴레이션으로 표현해야 한다.

DBMS를 구성할땐 보통 제3정규형 까지 만족하는 선에서 설계한다.

제1정규형(1NF, First Normal Form) 함수 종속성을 수행하지 않는다. 릴레이션에서 애트리뷰트의 값은 도메인에 속하는 원자값(Atomic Value)이어야 한다는 제약을 의미한다. 이미 릴레이션 스키마를 만들면 제1규형을 만족한 상태가 된다. 복합 애트리뷰트(composite attribute), 다치 애트리뷰트 (multivalued attribute), 중첩 릴레이션(nested relation) 등 비원자적(non-atomic) 애트리뷰트를 허용하지 않기 때문에 있으면 안된다.

다중값을 갖는 속성의 릴레이션 변환 방법은 이미 배웠다.

기본키를 확장해서 튜플이 중복될 수 있게 함

애트리뷰트가 가질 수 있는 최대수 만큼 애트리뷰트를 확장한다.

다중값을 갖는 애트리뷰트를 별도의 릴레이션으로 분리한다.

제1정규형(1NF, First Normal Form)의 이상(anomaly)
다중 값을 갖는 애트리뷰트를 제거하는 것이다. 제1정규형을 만족하더라도 이상(anomaly)이 존재하는 릴레이션 스키마가 있을 수 있다.

제2정규형 (2NF, Second Normal Form) 1NF이고, 키에 속하지 않는 모든 애트리뷰트들이 기본키에 완전 함수 종속이 된다. 이것이 만족되면 제2정규형이 만족되었다고 할 수 있다. 프로젝션하여 분해된 릴레이션들은 자연 조인을 통해 원래의 릴레이션으로 복귀 가능하다.