Database Chapter. 4~7

Database

---

데이터 정규화

데이터베이스의 데이터를 규정에 맞고 정상적인 형식으로 정리하는 과정

• 정규화를 위한 기준을 총 세 가지로 나뉜다.
  1. 1NF The first normal form: 제1 정규형
  2. 2NF The second normal form: 제2 정규형
  3. 3NF The third normal form: 제3 정규형

정규화 장점

• 데이터 중복 제거
• 데이터 무결성 준수
• 이상 현상 방지

제1 정규형 1NF

테이블을 구성하는 기본 규칙

• 테이블 각 칸(셀)에는 하나의 값만 담기
• 중복 로우 및 중복 컬럼 금지
• Primary key(기 본키) 필수 설정
• 한 컬럼 내에서는 같은 데이터 타입 사용

---

갱신 이상

• 갱신 이상이란?
  여러 곳에 중복 저장된 데이터를 업데이트하는 경우,
  한 곳의 데이터만 갱신되고 다른 곳의 데이터가 갱신되지 않아 발생하는 데이터 불일치 상황을 의미

• 갱신 이상 해결하기
  정규화를 통해 중복 데이터를 제거하고, 데이터를 각각의 엔터티로 분리한 뒤,
  각 엔터티를 나타내는 테이블의 기본 키(Primary Key) 값을 다른 테이블에서 참조(외래 키로 연결)하도록 설계한다.
  이렇게 하면 데이터가 여러 곳에서 중복 저장되지 않으므로,
  하나의 엔터티 테이블에서 데이터를 수정하더라도 해당 데이터가 참조된 모든 곳에서 자동으로 반영된다.

FK Foreign Key

다른 테이블의 PK(Primary Key)를 참조하는 키

ERD

엔터티 간의 관계를 표현한 다이어그램

• 1:1
  테이블의 한 로우에 대응하는 다른 테이블의 로우가 하나일 때
• 1:N
  테이블의 한 로우에 대응하는 다른 테이블의 로우가 여러 개일 때
• N:M
  테이블의 한 로우에 대응하는 다른 테이블의 로우가 여러 개이고,
  다른 테이블의 한 로우에 대응하는 테이블의 로우가 여러 개일 때

---

테이블 JOIN

사용자가 원하는 형태의 데이터 테이블을 만들기 위해 여러 테이블을 붙어야 할 때 사용

JOIN 유형

• INNER JOIN

  • PK와 FK가 같은 로우를 하나로 합치는 방식
  • 합치고자 하는 테이블에 공통으로 존재하는 로우만 합쳐진다.

  SELECT * FROM left_table
  JOIN right_table ON primary_key = foreign_key
  ORDER BY primary_key;

  • 기본 JOIN 방식이 INNER JOIN이기 때문에 JOIN이라고만 써도 INNER JOIN이 되며,
    ORDER BY는 필수는 아니지만 primary_key로 설정하는 것이 가독성이 좋다.

• LEFT JOIN

  • 왼쪽 테이블을 기준으로 합치는 방식
  • 왼쪽 테이블의 모든 로우가 테이블의 로우가 되며, 오른쪽 테이블에서 없는 값은 NULL로 표시된다.

  SELECT * FROM left_table
  LEFT OUTER JOIN right_table ON primary_key = foreign_key
  ORDER BY primary_key;

• RIGHT JOIN

  • 오른쪽 테이블을 기준으로 합치는 방식
  • 오른쪽 테이블의 모든 로우가 테이블의 로우가 되며, 왼쪽 테이블에서 없는 값은 NULL로 표시된다.

  SELECT * FROM right_table
  RIGHT OUTER JOIN left_table ON primary_key = foreign_key
  ORDER BY primary_key;

• FULL JOIN

  • 두 테이블에 모두 있는 로우, 왼쪽/오른쪽 테이블에만 있는 로우 모두 합치는 방식
  • 두 테이블에 모두 있는 로우의 경우, 합쳐서 조인된다.

  SELECT * FROM left_table
  FULL JOIN right_table ON primary_key = foreign_key;

  또는

  SELECT * FROM right_table
  FULL JOIN left_table ON primary_key = foreign_key;
  

• CROSS JOIN

  • 두 테이블을 합칠 때 가능한 경우의 로우가 모두 나오도록 합치는 방식
  • 각각 4개의 로우를 가지고 있는 두 테이블을 합치게 되면 4 * 4 = 16개의 로우가 나오게 된다.

  SELECT * FROM left_table CROSS JOIN right_table;

• SELF JOIN

  • 하나의 테이블이 마치 두 개의 테이블인 것처럼 나누어 합치는 방식

  SELECT
    e1.name AS employee_name,
    e2.name AS manager_name
  FROM
    employees e1
  LEFT JOIN
    employees e2
  ON
    e1.manager_id = e2.employee_id;

  • 하나의 테이블을 사용하지만 두 개의 테이블인 것처럼 활용하기 위해 테이블명 뒤에 띄어쓰기 후 가상의 테이블명을 지정하고,
    컬럼의 이름도 AS를 사용하여 구분될 수 있도록 지정한다.

---

GROUP BY

GROUP BY는 데이터를 특정 기준으로 그룹화하여 요약된 결과를 보고잘 할 때 사용하며,
주로 집계함수(SUM, AVG, COUNT, MAX, MIN)등과 함께 사용된다.

SELECT department_id, AVG(salary)
FROM employee_table
GROUP BY department_id;

HAVING을 사용하여 그룹화된 데이터를 필터링 할 수 있다.

SELECT department_id, AVG(salary)
FROM employee_table
GROUP BY department_id
HAVING AVG(salary) > 70000;

---

제2 정규형 2NF

• 제1 정규형의 기준을 만족한 상태에서,
  복합 키의 일부에 종속된 컬럼(부분 함수 종속성)이 있다면,
  해당 컬럼과 종속된 복합 키의 일부를 별도의 테이블로 분리하여 데이터 중복 및 비효율성을 줄인다.
  이를 통해 부분 함수 종속성을 제거하여 완전 함수 종속성을 만족시킬 수 있다.

부분 함수 종속성

Order_ID	Product_ID	Product_Name	Quantity	Customer_Name
O001	P01	Laptop	2	Alice
O002	P02	Mouse	5	Bob
O003	P01	Laptop	1	Alice

• PK: (Order_ID, Product_ID) (복합 키)

Product_Name의 경우, Order_ID에는 속하지 않고 Product_ID에만 속하는 부분 함수 종속성을 띈다.
해당 컬럼과 복합 키를 따로 분리하여 부분 함수 종속성을 제거한다.

Order_ID	Product_ID	Quantity	Customer_Name
O001	P01	2	Alice
O002	P02	5	Bob
O003	P01	1	Alice

• PK: (Order_ID, Product_ID)

Product_ID	Product_Name
P01	Laptop
P02	Mouse

• PK: Product_ID

따로 분리함으로써 기본키를 제외한 속성이 기본키에 대해 부분적으로가 아닌 완전 함수 종속의 성질을 보여주는 것을 알 수 있다.

제3 정규형 3NF

• 제2 정규형을 만족한 상태에서,
  한 테이블에 여러 엔터티가 존재하는 경우 이행적 함수 종속성이 발생할 수 있는데,
  이 문제를 해결하기 위해, 관련된 속성을 별도의 테이블로 분리하고, 각 테이블에 하나의 주요 엔터티만 포함되도록 설계한다.
  결과적으로, 모든 비키 속성은 오직 기본 키에만 직접적으로 종속되게 만들 수 있다.

이행적 함수 종속성

이행적 함수 종속성이란, 어떤 속성이 기본 키에 직접 종속되지 않고, 다른 속성을 통해 간접적으로 종속되는 관계를 말한다.

Student_ID	Major_ID	Major_Name	Advisor_Name
S001	M01	Computer Science	Dr. Smith
S002	M02	Biology	Dr. Lee

• PK: Student_ID

이 때 Major_Name과 Advisor_Name은 PK에 직접 종속된 것이 아니라,
Major_ID를 통해 종속되고 있다.
위의 테이블을 아래의 두 테이블로 나눌 수 있다.

Student_ID	Major_ID
S001	M01
S002	M02

• PK: Student_ID

Major_ID	Major_Name	Advisor_Name
M01	Computer Science	Dr. Smith
M02	Biology	Dr. Lee

• PK: Major_ID

위와 같이 구분함으로써 PK를 제외한 속성이 모두 PK에 직접 종속되게 만들 수 있다.