관계형 데이터베이스 설계: DBMS-MySQL 튜토리얼-php.cn

Relational Database Design: DBMS

관계형 데이터베이스 디자인: 종합 가이드

관계형 데이터베이스 설계는 효과적인 데이터베이스 시스템의 초석으로, 중복성을 줄이고 데이터 무결성을 유지하면서 데이터를 효율적으로 구성하는 데 중점을 둡니다. 이 문서에서는 분해, 정규화, 기능적 종속성 및 키에 대한 철저한 탐색을 제공하여 관계형 데이터베이스 설계 원칙을 완전히 이해할 수 있도록 해줍니다.

관계형 데이터베이스 설계의 분해

분해는 중복을 제거하고 일관성을 개선하며 성능을 최적화하기 위해 큰 관계(테이블)를 더 작고 의미 있는 관계로 나누는 프로세스입니다. 이는 정규화의 중요한 측면입니다.

분해 유형

손실 분해:
- 분해된 관계를 결합하여 원래 테이블을 완벽하게 재구성할 수 없는 경우 분해는 손실입니다.
- 이는 분해 중에 일부 데이터나 관계가 손실될 때 발생합니다.
- 예: 테이블을 고려하십시오 :
EmployeeID | ProjectID | ProjectManager --------------------------------------- E1 | P1 | M1 E2 | P1 | M1
로그인 후 복사
로그인 후 복사
로그인 후 복사
로그인 후 복사
로그인 후 복사
로그인 후 복사
로그인 후 복사
이를 다음과 같이 분해하면:
- 표 1: 직원 ID | 프로젝트ID
- 표 2: 프로젝트 ID | 프로젝트 관리자 이러한 테이블을 다시 조인하면 데이터가 중복되거나 일관되지 않아 손실이 많은 분해가 발생할 수 있습니다.
무손실 분해:
- 데이터 손실이나 불일치 발생 없이 분해된 관계를 결합하여 원본 테이블을 완벽하게 재구성할 수 있는 경우 분해는 무손실입니다.
- 이는 분해가 모든 기능적 종속성을 보존하거나 주요 속성이 분해된 각 관계에 포함될 때 달성됩니다.

기능적 의존성

기능적 종속성(FD)은 한 속성(또는 속성 집합)의 값이 다른 속성(또는 속성 집합)의 값을 결정하는 관계에서 두 속성 간의 관계를 설명합니다. 관계형 데이터베이스 설계 및 정규화의 기본 개념입니다.

정의:

X와 Y를 관계 R의 속성 집합으로 둡니다. 기능적 종속성 X → Y는 R의 두 튜플(행)에 대해 튜플이 X 값에 동의하는 경우 다음을 의미합니다. 그들은 또한 Y의 가치에 동의해야 합니다.

X: 행렬식(왼쪽에 있는 속성).
Y: 종속(오른쪽의 속성).

예:

학생 정보를 저장하는 테이블을 생각해 보세요.

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

로그인 후 복사

여기서 학생ID → 이름, 전공은 학생ID에 따라 이름과 전공이 모두 고유하게 결정되기 때문입니다.

기능적 종속성의 속성:

재귀성: Y가 X의 부분 집합이면 X → Y입니다.
증강: X → Y이면 XZ → YZ입니다(양쪽에 속성을 추가하면 종속성이 유지됩니다).
전이성: X → Y 및 Y → Z이면 X → Z입니다.

관계형 데이터베이스의 키

키는 테이블에서 레코드를 고유하게 식별하고 데이터 무결성을 적용하는 데 필수적입니다.

키 유형:

수퍼키:
- 관계에서 튜플을 고유하게 식별할 수 있는 하나 이상의 속성 집합입니다.
- 예: EmployeeID 및 Name 속성이 있는 테이블에서 {EmployeeID}, {EmployeeID, Name}은 슈퍼키입니다.
후보 키:
- 적절한 하위 집합이 없다는 의미의 최소 슈퍼키도 슈퍼키입니다.
- 예: {EmployeeID}가 튜플을 고유하게 식별할 수 있는 경우 이는 후보 키입니다.
기본 키:
- 튜플을 고유하게 식별하기 위해 데이터베이스 설계자가 선택한 후보 키입니다.
- 예: Employee 테이블의 EmployeeID
외래 키:
- 다른 테이블의 기본 키를 참조하여 테이블 간의 관계를 설정하는 한 테이블의 속성(또는 속성 집합)입니다.
- 예: 부서 테이블의 DepartmentID를 참조하는 Employee 테이블의 DepartmentID.
복합 키:
- 두 개 이상의 속성으로 구성된 기본 키입니다.
- 예: 학생 등록 테이블의 (StudentID, CourseID)
고유 키:
- 열(또는 열 조합)의 모든 값이 고유하도록 보장하는 키 제약 조건

정규화 및 정규형

정규화는 중복성과 종속성을 줄이고 데이터 무결성을 보장하기 위해 속성과 관계를 구성하는 프로세스입니다. 이는 연속적인 정규형

의 기준을 점진적으로 충족함으로써 달성됩니다.

일반 형식(종합 개요)

첫 번째 정규형(1NF)

정의:

다음 기준을 만족하는 경우 관계는 제1정규형(1NF)에 있다고 합니다.

원자성: 모든 속성(열)에는 원자 값이 포함되어야 합니다. 이는 각 열의 값이 분할될 수 없으며 더 이상 분류될 수 없음을 의미합니다.
단일 값 항목: 테이블의 각 열에는 단일 데이터 유형의 값이 포함되어야 하며 열에는 집합, 목록 또는 배열이 있어서는 안 됩니다.
행의 고유성: 각 행은 고유해야 합니다. 즉, 테이블에는 행을 구별하기 위한 기본 키가 있어야 합니다.
반복 그룹 없음: 테이블에는 동일한 속성(예: 항목1, 항목2 등)에 대해 여러 열이 있어서는 안 되며, 단일 셀에 여러 값이 저장되어서도 안 됩니다.

설명:

원자 값: 각 셀의 데이터는 가장 간단한 형식이어야 합니다. 예를 들어, 하나의 셀에 여러 항목을 저장하는 대신 각 항목이 자체 행을 차지해야 합니다.
반복 그룹: 여러 열이나 행이 동일한 유형의 데이터를 나타내므로 테이블이 1NF를 준수하지 않게 됩니다.
기본 키: 기본 키는 각 행을 고유하게 식별할 수 있도록 보장하며 이는 관계형 데이터베이스의 기본 요구 사항입니다.

예:

비준수 테이블(1NF가 아님):

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

로그인 후 복사

항목 열에는 여러 값(예: "펜, 노트")이 포함되어 있으므로 원자성을 위반합니다.
항목이 별도의 행이 아닌 단일 셀에 저장되기 때문에 반복되는 그룹이 있습니다.

호환 테이블(1NF):

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

로그인 후 복사

여기서 Items 열은 원자 값으로 구분되며 각 항목은 별도의 행에 있습니다.
원자성을 보장하기 위해 여러 값을 포함하는 셀이 없습니다.
테이블에 반복되는 그룹이나 배열이 없어 1NF를 준수합니다.

두 번째 정규형(2NF)

정의:

다음과 같은 경우 관계는 제2정규형(2NF)입니다.

이미 첫 번째 정규형(1NF)에 있습니다(즉, 다중 값 또는 반복 그룹 없음).
프라임이 아닌 모든 속성은 전체 기본 키에 완전히 기능적으로 종속됩니다.

비프라임 속성: 후보 키의 일부가 아닌 속성입니다.
완전히 기능적으로 종속: 비프라임 속성은 복합 기본 키의 일부가 아닌 전체 복합 기본 키에 종속되어야 합니다.

설명:

부분 종속성은 비프라임 속성이 전체 키가 아닌 복합 기본 키의 일부에만 종속될 때 발생합니다.
2NF는 관계를 더 작은 관계로 분해하여 부분 종속성을 제거하여 프라임이 아닌 속성이 전체 기본 키 또는 다른 후보 키에만 종속되도록 합니다.

이 단계는 부분 종속성으로 인한 중복을 줄이고 데이터를 더 잘 구성합니다.

예:

비준수 테이블(2NF가 아님):

학생 강좌 정보를 저장하는 테이블을 생각해 보세요.

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

로그인 후 복사

복합 기본 키: (StudentID, CourseID).
부분 의존성:
- 강사와 부서는 전체 기본 키(StudentID, CourseID)가 아닌 CourseID에만 의존합니다.

이는 프라임이 아닌 속성(강사 및 부서)이 복합 키에 부분적으로 종속되므로 2NF를 위반합니다.

호환 테이블(2NF):

부분 종속성을 제거하려면 테이블을 두 개의 관계로 분해하세요.

학생 코스 테이블:

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

로그인 후 복사

과정 세부정보 표:

OrderID | Items
-------------------
1       | Pen, Notebook
2       | Pencil

로그인 후 복사

제3정규형(3NF)

정의:

다음과 같은 경우 관계는 제3정규형(3NF)입니다.

제2정규형(2NF)입니다(즉, 부분 종속성이 없음).
전이적 종속성이 존재하지 않습니다. 이는 다음을 의미합니다.
- 프라임이 아닌 속성은 다른 비프라임 속성에 종속되지 않습니다.
- 프라임이 아닌 속성은 다른 비프라임 속성을 통하지 않고 후보 키에만 의존해야 합니다.

비프라임 속성: 후보 키의 일부가 아닌 속성입니다.
전이적 종속성: 프라임이 아닌 속성이 프라임이 아닌 다른 속성을 통해 후보 키에 간접적으로 의존하는 종속성입니다.

설명:

3NF에서는 전이적 종속성을 제거하여 중복성을 줄이고 데이터 일관성을 향상합니다.

전이적 종속성 예: A → B 및 B → C인 경우 A → C는 전이적 종속성입니다. 이는 C가 A부터 B까지 간접적으로 의존함을 의미합니다.
B를 변경하면 C를 업데이트할 때 이상이 발생할 수 있으므로 이러한 종속성은 중복성을 초래합니다.

예:

비준수 테이블(3NF가 아님):

OrderID | Item
---------------
1       | Pen
1       | Notebook
2       | Pencil

로그인 후 복사

후보 키: StudentID는 각 행을 고유하게 식별합니다.

문제: HOD 속성은 StudentID에 직접적으로 의존하지 않고 부서에 의존합니다.
- StudentID → 부서(직접 종속성).
- 부서 → HOD(전이적 종속성).
- 그래서 StudentID → HOD는 전이적 종속성입니다.

이 구조는 중복성을 초래합니다. CS 부서의 HOD가 변경되면 여러 행을 업데이트해야 합니다.

호환 테이블(3NF):

전이적 종속성을 해결하려면 테이블을 두 개의 관계로 분해하세요.

학생-학과 테이블:

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

로그인 후 복사

부서-HOD 테이블:

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

로그인 후 복사

보이스-코드 정규형(BCNF)

정의:

관계는 BCNF(Boyce-Codd Normal Form)인 경우

제3정규형(3NF)입니다(즉, 부분적 또는 전이적 종속성이 존재하지 않음).
모든 행렬식은 후보 키입니다.

결정자: 다른 속성이 기능적으로 종속되는 속성(또는 속성 집합)입니다.
후보 키: 관계의 각 튜플을 고유하게 식별할 수 있는 최소 속성 집합입니다.

3NF와 BCNF의 주요 차이점:

3NF는 소수가 아닌 속성이 후보 키에 기능적으로 종속되는 일부 종속성을 허용하는 반면 BCNF는 모든 결정자가 후보 키인지 확인하여 이러한 예외를 제거합니다.

설명:

BCNF는 3NF보다 엄격하며 관계가 3NF를 충족할 수 있지만 BCNF를 위반하는 종속성으로 인해 여전히 중복성이 있는 상황을 해결합니다.

BCNF가 필요한 경우:

비후보 키 속성이 후보 키의 일부를 결정하여 중복성과 이상이 발생하는 경우 BCNF가 필요합니다.

예:

비준수 테이블(BCNF에 포함되지 않음):

OrderID | Items
-------------------
1       | Pen, Notebook
2       | Pencil

로그인 후 복사

기능적 종속성:

CourseID → 강사
강사 → 방

후보 키: CourseID

문제:

결정인 강사는 후보 키가 아니라 방을 결정합니다.
모든 행렬식이 후보 키가 아니기 때문에 이는 BCNF를 위반합니다.

호환 테이블(BCNF):

BCNF를 달성하려면 테이블을 두 가지 관계로 분해하세요.

강좌-강사 테이블:

OrderID | Item
---------------
1       | Pen
1       | Notebook
2       | Pencil

로그인 후 복사

강사실 테이블:

StudentID | CourseID | Instructor | Department
----------------------------------------------
S1        | C1       | Dr. Smith  | CS
S2        | C2       | Dr. Jones  | EE
S1        | C2       | Dr. Jones  | EE

로그인 후 복사

제4정규형(4NF)

정의:

다음과 같은 경우 관계는 제4정규형(4NF)입니다.

BCNF(Boyce-Codd Normal Form)입니다(즉, 부분적, 전이적 또는 기타 이상이 없음).
다중 값 종속성이 없습니다.

다중 값 종속성(MVD): 테이블의 하나의 속성이 여러 개의 독립적인 속성 집합을 결정할 때 다중 값 종속성이 존재합니다. 즉, 관계에 서로 관련되지 않은 두 개 이상의 독립적인 다중 값 속성이 포함되어 있으면 4NF를 위반합니다.

설명:

4NF의 주요 목표는 레코드에 직접 관련되지는 않지만 동일한 키에 대한 의존성으로 인해 함께 나타나는 두 개 이상의 독립적인 속성이 레코드에 포함될 때 발생하는 다중 값 종속성을 제거하는 것입니다.

이러한 유형의 종속성은 동일한 정보의 여러 복사본이 행으로 반복되기 때문에 중복성을 초래합니다.
관계를 분해하여 MVD를 제거함으로써 중복성을 제거하고 데이터베이스의 일관성을 향상시킵니다.

핵심 개념:

4NF에서는 관계에 후보 키에 의존하는 다중 값 속성이 두 개 이상 있어서는 안 됩니다. 테이블을 적절하게 분해하여 각 다중 값 종속성을 제거해야 합니다.

예:

비준수 테이블(4NF가 아님):

학생, 학생이 듣는 강좌, 소속 클럽에 대한 정보를 저장하는 테이블을 생각해 보세요.

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

로그인 후 복사

후보 키: 학생ID

다중 값 종속성:

학생 ID는 강좌 세트와 클럽 세트를 모두 결정할 수 있지만 이러한 세트는 서로 독립적입니다.
- StudentID → {Courses} (StudentID와 Courses 간의 다중 값 종속성)
- StudentID → {Clubs} (StudentID와 Clubs 간의 다중 값 종속성)

학생ID가 강좌와 동아리를 독립적으로 결정하므로 이 표는 4NF를 위반합니다. 이로 인해 동일한 학생 ID가 코스와 동아리의 다양한 조합으로 여러 번 반복되므로 중복이 발생합니다.

호환 테이블(4NF):

테이블이 4NF를 준수하도록 하려면 테이블을 두 개의 테이블로 분해하여 다중 값 종속성을 제거해야 합니다.

학생 코스 테이블:

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

로그인 후 복사

학생동아리 테이블:

OrderID | Items
-------------------
1       | Pen, Notebook
2       | Pencil

로그인 후 복사

이제 두 개의 다중 값 종속성이 별도로 처리됩니다.

학생-강좌 테이블은 학생과 수강하는 강좌 간의 관계를 저장합니다.
학생-동아리 테이블은 학생과 그들이 소속된 동아리 간의 관계를 저장합니다.

제5정규형(5NF)

정의:

관계는 PJNF(Projection-Join Normal Form)라고도 알려진 제5정규형(5NF)입니다.

제4정규형(4NF)입니다(즉, 다중 값 종속성이 존재하지 않음).
정보 손실 없이 더 이상 분해할 수 없습니다. 이는 관계에 조인 종속성 또는 무손실 조인 분해

조인 종속성(JD): 조인 종속성은 관계가 둘 이상의 관계로 분해될 수 있지만 다시 결합될 때 정보가 손실되지 않을 때 발생합니다. 즉, 관계를 하위 관계로 나눌 수 있지만 데이터 손실 없이 원래 관계를 재구성할 수 있는 경우 조인 종속성이 존재합니다.

설명:

5NF는 종속성 결합을 처리하며, 데이터 손실 없이 분해된 부분에서 모든 정보를 재구성할 수 있는 방식으로 데이터를 분해합니다. 5NF의 관계는 모든 중요한 조인 종속성이 후보 키에 의해 암시되는 방식으로 설계되었습니다.

무손실 조인 분해: 관계가 더 작은 관계로 분해된 후 다시 조인되면 데이터 손실 없이 원래 관계를 완전히 재구성할 수 있습니다. 정보 손실 없이 더 이상 분해될 수 없는 관계는 5NF에 속합니다.
중요한 조인 종속성: 조인 종속성이 간단하게 충족되지 않으면 조인 종속성이 중요하지 않습니다(즉, 관계의 모든 속성이 조인 종속성에 존재하지 않음).

간단히 말하면 5NF는 부적절한 분해로 인한 중복이 발생하지 않도록 하는 데 관심이 있습니다. 관계가 분해되고 나중에 다시 결합될 때 모든 원본 데이터를 손실이나 모호함 없이 계속 사용할 수 있음을 보장합니다.

예:

비준수 테이블(5NF가 아님):

어떤 공급업체가 다양한 프로젝트에 어떤 부품을 공급하는지에 대한 정보를 저장하는 테이블을 생각해 보세요.

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

로그인 후 복사

후보 키: (공급업체, 부품, 프로젝트)

종속성 조인:

위의 관계는 정보 손실 없이 더 작은 관계로 분해될 수 있기 때문에 조인 종속성을 갖습니다. 예를 들어 테이블은 세 가지 하위 관계로 분해될 수 있습니다.

공급업체-부품 테이블:

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

로그인 후 복사

공급업체-프로젝트 테이블:

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

로그인 후 복사

파트 프로젝트 테이블:

OrderID | Items
-------------------
1       | Pen, Notebook
2       | Pencil

로그인 후 복사

테이블을 이러한 작은 관계로 분해하면 이 세 개의 작은 관계에 대해 자연스러운 조인을 수행하여 원래 테이블을 다시 만들 수 있습니다.

그러나 이 분해가 가능하기 때문에 5NF를 위반합니다. 5NF를 위반하는 이유는 특정 프로젝트에 대해 어느 공급업체가 어떤 부품을 공급하는지에 대한 정보가 여러 행에 걸쳐 중복 저장되기 때문입니다. 동일한 사실을 여러 번 저장하고 있어 불필요하며 불일치가 발생할 수 있습니다.

호환 테이블(5NF):

5NF를 달성하기 위해 정보 손실 없이 관계가 더 이상 분해될 수 없도록 테이블을 분해합니다.

공급업체-부품-프로젝트 테이블:

OrderID | Item
---------------
1       | Pen
1       | Notebook
2       | Pencil

로그인 후 복사

이 형식에서 관계는 데이터 손실 없이는 더 이상 분해될 수 없기 때문에 이제 5NF에 있습니다. 이 테이블은 원본과 동일한 정보를 나타내지만 각 속성이 후보 키에 완전히 종속되고 부적절한 분해로 인해 중복이 존재하지 않는 보다 정규화된 형식으로 나타납니다.

관계형 디자인의 주요 개념

다중 값 종속성: 하나의 속성이 여러 개의 독립 값을 결정하는 경우.
조인 종속성: 조인 중에 가짜 튜플이 생성되지 않도록 합니다.
종속성 보존: 분해 후에도 모든 기능적 종속성이 보존되도록 합니다.

이 종합 가이드는 관계형 데이터베이스 설계를 마스터하여 효율적이고 일관되며 변칙 없는 데이터베이스 시스템을 보장하는 데 도움을 줍니다.

위 내용은 관계형 데이터베이스 설계: DBMS의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!