[웹 서비스 개발] Normalization

일시 : 24-2 11주차 2차시

[1] 관계형 데이터베이스 관리 시스템 (RDBMS)에서의 테이블 스키마 고려사항

1) 목표

• 실제 세계를 정확히 표현하기
• 각 관계의 릴레이션과 속성 결정
• 속성 및 관계 식별
• 데이터 종속성
• 효율적인 데이터 관리
• 손실 없는 데이터 표현
• 최소한의 데이터 중복

[2-1] Relational Database Table

1) 형태

Customer ID	FirstName	Surname	Telephone Number
123	Robert	Ingram	555-861-2025
456	Jane	Wright	555-403-1659
789	Maria	Fernandez	555-808-9633
234	Mary	Brown	123-209-4675
356	Eugene	Kim	123-457-7364
567	Eunsun	Park	777-374-7384

(1) What is the key?

: 고객 ID (Customer ID)는 각 행의 데이터를 고유하게 식별하므로 이 테이블의 기본 키가 될 수 있다.

(2) Is this a good table?

: 이 테이블은 기본적으로 좋은 구조를 가지고 있지만, 전화번호가 중복되지 않는다고 가정할 경우 문제는 없다.

• 또한, Telephone Number 안에 두 개의 값이 들어가 있을 경우에는, Multi-part data 문제가 발생할 수 있다.
  • 테이블의 각 필드에는 단일 값만 들어가야 관계형 데이터베이스의 정규화 규칙에 부합하므로, 다중 값이 포함된 경우 전화번호를 별도의 테이블로 분리하여 관리하는 것이 바람직하다.

(3) What is the candidate keys?

: Customer ID는 각 고객을 고유하게 식별하므로 후보 키가 될 수 있다. 또한, Telephone Number도 각 고객마다 고유하다고 가정하면, 또 다른 후보 키가 될 수 있습니다.

[2-2] Try to divide the given table.

1) 테이블 분할

(1) Customer Table

: 고객의 주요 정보를 저장하는 테이블이다.

Customer ID	FirstName	Surname
123	Robert	Ingram
456	Jane	Wright
789	Maria	Fernandez
234	Mary	Brown
356	Eugene	Kim
567	Eunsun	Park

(2) Telephone Table

: 각 고객의 전화번호를 저장하는 테이블이다. 여러 개의 전화번호를 허용할 수 있도록 Customer ID를 외래 키로 참조한다.

Customer ID	Telephone Number
123	555-861-2025
456	555-403-1659
789	555-808-9633
234	123-209-4675
356	123-457-7364
567	777-374-7384

2) 각 테이블에서의 키

• 기본 키(Primary Key):

  • Customer Table: Customer ID
  • Telephone Table: Customer ID + Telephone Number (복합 키)

• 외래 키(Foreign Key):

  • Telephone Table: Customer ID (이 필드는 Customer Table의 Customer ID를 참조)

• 복합 키(Composite Key):

  • Telephone Table: Customer ID + Telephone Number (두 컬럼이 결합하여 고유성을 보장).

[3-1] Find Update Anomaly What is the key?

Employee	Skill	Current Work Location
Jones	Typing	114 Main Street
Jones	Shorthand	114 Main Street
Jones	Whittling	114 Main Street
Bravo	Light Cleaning	73 Industrial Way
Ellis	Alchemy	73 Industrial Way
Ellis	Flying	73 Industrial Way
Harrison	Light Cleaning	73 Industrial Way

(1) Update Anmaly

: 업데이트 이상(anomaly)은 중복 데이터가 있을 때 발생하며, 일관성을 유지하기 위해 여러 행을 수정해야 하는 경우를 말한다.

• 이 테이블에서 키(key)는 Employee(직원)와 Skill(기술)의 조합이다. 한 직원이 여러 기술을 가질 수 있지만, 각 행에서 한 기술은 한 직원에게만 속한다.

• 따라서 특정 직원의 Current Work Location(현재 근무 위치)가 변경되면 해당 직원의 모든 행을 수정해야 일관성을 유지할 수 있다. 이러한 구조는 한 행을 수정할 때 다른 행도 업데이트해야 하는 업데이트 이상을 초래할 수 있다.

[3-2] Divide Table in this example

1) Employee_Skill Table

• 직원과 기술 정보를 저장하는 테이블이다. 각 직원이 여러 기술을 가질 수 있으므로, 이 테이블에서는 직원과 기술의 조합이 고유하게 식별된다.

Employee	Skill
Jones	Typing
Jones	Shorthand
Jones	Whittling
Bravo	Light Cleaning
Ellis	Alchemy
Ellis	Flying
Harrison	Light Cleaning

2) Employee_Work_Location Table

• 직원의 근무 위치 정보를 저장하는 테이블이다. 직원마다 하나의 근무 위치만 저장한다.

Employee	Current Work Location
Jones	114 Main Street
Bravo	73 Industrial Way
Ellis	73 Industrial Way
Harrison	73 Industrial Way

3) 키 (Key)

• Employee_Skill Table: (Employee, Skill) 조합이 기본 키가 된다.
• Employee_Work_Location Table: Employee가 기본 키로 사용된다.

4) Update Anomaly (업데이트 이상)

• Employee_Skill Table에서, 직원의 Current Work Location이 변경되면, 해당 직원의 Employee_Work_Location Table에서만 한 번 수정하면 되지만, Employee_Skill Table에서는 수정할 필요가 없다.
• 두 테이블로 나누면, 근무 위치 변경 시 업데이트 이상이 발생하지 않으며, 각 정보는 독립적으로 관리된다.

[4-1] Tournament Winners

Tournament	Year	Winner	Winner Date of Birth
Des Moines Masters	1998	Chip Masterson	14 March 1977
Indiana Invitational	1998	Al Fredrickson	21 July 1975
Cleveland Open	1999	Bob Albertson	28 September 1968
Des Moines Masters	1999	Al Fredrickson	21 July 1975
Indiana Invitational	1999	Chip Masterson	14 March 1977

(1) Find the key. Any problem in this table?

• 기본 키 (Primary Key): (Tournament, Year) 조합
• 문제점:
  1. 중복 데이터: 동일 대회에서 같은 선수가 여러 번 우승해 생일 정보가 중복됨.
  2. 업데이트 이상: 중복된 정보 수정 시 여러 행을 수정해야 함.

해결 방법:

• 선수 정보와 대회 정보를 별도의 테이블로 분리하여 중복을 방지.

알겠습니다. Tournament Table에 우승자 정보를 포함한 형태로 테이블을 나누어 보겠습니다.

[4-2] Divide Table in this Example

1) Tournament Table: 대회 정보와 우승자 포함

Tournament	Year	Winner
Des Moines Masters	1998	Chip Masterson
Indiana Invitational	1998	Al Fredrickson
Cleveland Open	1999	Bob Albertson
Des Moines Masters	1999	Al Fredrickson
Indiana Invitational	1999	Chip Masterson

2) Winner Table: 우승자 생일 정보만 포함

Winner	Winner Date of Birth
Chip Masterson	14 March 1977
Al Fredrickson	21 July 1975
Bob Albertson	28 September 1968

3) 해결 방법:

• Tournament Table에 우승자 정보를 포함하여 각 대회와 우승자가 연결되도록 한다.
• 우승자의 생일 정보는 별도의 Winner Table에 저장하여 중복을 방지하고, 데이터를 효율적으로 관리할 수 있다.

[5-1] Find Anomaly (Electric Toothbrush Models)

Manufacturer	Model	Model Full Name	Manufacturer Country
Forte	X-Prime	Forte X-Prime	Italy
Forte	Ultraclean	Forte Ultraclean	Italy
Dent-o-Fresh	EZbrush	Dent-o-Fresh BananaBrush-2000	USA
Kobayashi	ST-60	Kobayashi ST-60	Japan
Hoch	Toothmaster	Hoch Toothmaster	Germany
Hoch	X-Prime	Hoch X-Prime	Germany

1) What is the anomaly in this example?

: 이 테이블에서 중복 데이터와 불일치 문제가 발생합니다.

• Model과 Model Full Name이 일치하지 않는 경우가 있습니다. 예를 들어, Dent-o-Fresh의 모델은 EZbrush이지만, Model Full Name은 Dent-o-Fresh BananaBrush-2000으로 되어 있어 일관성이 없습니다.
• 또한, Manufacturer와 Manufacturer Country가 잘못 연결된 경우가 있을 수 있습니다. 예를 들어, Kobayashi는 일본 제조사지만 Model Full Name에 Japan이 빠져 있습니다.

[5-2] Divide Table

1) Manufacturer Table: 제조사 정보만 담은 테이블

Manufacturer	Manufacturer Country
Forte	Italy
Dent-o-Fresh	USA
Kobayashi	Japan
Hoch	Germany

2) Model Table: 모델과 모델의 전체 이름 정보를 담은 테이블

Model	Model Full Name	Manufacturer
X-Prime	Forte X-Prime	Forte
Ultraclean	Forte Ultraclean	Forte
EZbrush	Dent-o-Fresh BananaBrush-2000	Dent-o-Fresh
ST-60	Kobayashi ST-60	Kobayashi
Toothmaster	Hoch Toothmaster	Hoch
X-Prime	Hoch X-Prime	Hoch

[6] Course Taking Advising

Student ID	AdvisorProf	Department	Course ID	Grade
100	AAA	CE	C413	A
100	AAA	CE	E412	A
200	BBB	EE	C123	B
300	CCC	CE	C312	A
300	CCC	CE	C324	C
300	CCC	CE	C413	A
400	AAA	CE	C312	A
400	AAA	CE	C324	A
400	AAA	CE	C413	B
400	AAA	CE	E412	C

: 학생들의 수강 정보를 보여주는 데이터베이스 테이블입니다

1) 특징

• 총 10개의 수강 기록이 있다
• 학생 ID는 100, 200, 300, 400 네 명의 학생이 있다.
• 지도교수는 AAA, BBB, CCC 세 명이 있다.
• 학과는 CE(Civil Engineering)와 EE(Electrical Engineering)가 있다.
• 성적은 A, B, C로 기록되어 있다.

2) 문제

: 예를 들어, Student_ID를 200으로 가진 학생의 수강 정보를 지우면, BBB라는 지도 교수는 없어지게 된다.

[6-2] Divide Table in Example

더 작게 여러 테이블로 분리하려면 정규화를 적용하여 데이터 중복을 최소화하고 각 엔티티를 더욱 독립적으로 관리할 수 있습니다. 아래는 더 작은 테이블들로 분리한 예입니다.

1) 학생(Student) 테이블

• 학생의 고유 ID, 지도 교수, 학과 정보를 저장한다.

Student ID	AdvisorProf	Department
100	AAA	CE
200	BBB	EE
300	CCC	CE
400	AAA	CE

2) 강의(Course) 테이블

• 강의의 고유 ID와 관련된 정보를 저장한다. 강의명이나 학점 등의 정보를 추가할 수 있다.

Course ID	Course Name	Department
C413	Course A	CE
E412	Course B	CE
C123	Course C	EE
C312	Course D	CE
C324	Course E	CE

3) 수강(Enrollment) 테이블

• 학생이 수강한 강의와 성적을 기록한다.
• 학생 ID와 강의 ID를 외래 키(Foreign Key)로 사용하여 다른 테이블과 연결한다.

Student ID	Course ID	Grade
100	C413	A
100	E412	A
200	C123	B
300	C312	A
300	C324	C
300	C413	A
400	C312	A
400	C324	A
400	C413	B
400	E412	C

4) 테이블 간 관계

1. Student 테이블과 Enrollment 테이블은 Student ID를 기준으로 연결된다.
2. Course 테이블과 Enrollment 테이블은 Course ID를 기준으로 연결된다.

5) 장점

1. 중복 제거:
   • AdvisorProf와 Department 정보가 Student 테이블에만 저장되므로 중복을 제거한다.
   • 강의 정보도 별도로 분리해 관리할 수 있다.
2. 유지보수 용이:
   • 예를 들어, AdvisorProf가 변경되거나 강의 정보가 수정되더라도 각 테이블에서 한 번만 수정하면 된다.
3. 데이터 일관성:
   • 중복 데이터가 제거되어 업데이트 이상(Update Anomaly)을 방지한다.

---