면접카타 [DB]-19,20 기본키, 외래키, ER모델, 정규화

Primary Key, Foreign Key, ER 모델이란?

19. Primary Key (기본 키)

Primary Key(PK)는 테이블에서 각 행을 고유하게 식별할 수 있는 속성(열)입니다.

• 한 테이블에 하나만 존재해야 합니다.
• NULL 값을 가질 수 없습니다.
• 중복될 수 없습니다.
• 일반적으로 자동 증가(AUTO_INCREMENT) 또는 UUID를 사용하여 생성됩니다.
• 복합 키(Composite Key)로 여러 열을 묶어 기본 키로 설정할 수도 있습니다.

예제:

CREATE TABLE Users (
    user_id SERIAL PRIMARY KEY,  -- user_id가 기본 키
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

• user_id는 각 사용자를 고유하게 식별합니다.

2. Foreign Key (외래 키)

Foreign Key(FK)는 한 테이블이 다른 테이블을 참조할 때 사용하는 키입니다.

• 다른 테이블의 Primary Key를 참조합니다.
• 테이블 간의 관계(Relationships)를 정의합니다.
• 무결성을 유지하기 위해 참조하는 키가 변경되거나 삭제될 때 제약 조건을 설정할 수 있습니다.
  • ON DELETE CASCADE: 참조된 데이터 삭제 시 함께 삭제됨
  • ON DELETE SET NULL: 참조된 데이터 삭제 시 NULL로 변경됨

예제:

CREATE TABLE Orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE
);

 

• user_id는 Users 테이블의 user_id를 참조하는 외래 키입니다.
• 특정 사용자가 삭제되면 해당 사용자의 주문도 함께 삭제됩니다.

---

3. ER 모델 (Entity-Relationship Model)

ER 모델은 데이터베이스의 구조와 관계를 시각적으로 표현하는 모델입니다.

• 주요 요소:
  • 엔터티(Entity): 데이터베이스에서 관리하는 개체 (예: 사용자, 주문)
  • 속성(Attribute): 엔터티의 특성 (예: user_id, username, email)
  • 관계(Relationship): 엔터티 간의 연결 (예: Users와 Orders의 관계)

ER 다이어그램 예시:

• Users(사용자)는 Orders(주문)을 여러 개 가질 수 있다 (1:N 관계).

Users(사용자) -----(1:N)----- Orders(주문)

---

20. 정규화란?

정규화(Normalization)는 데이터 중복을 줄이고 무결성을 보장하기 위해 데이터베이스를 체계적으로 구조화하는 과정입니다.

정규화의 목적

1. 데이터 중복 방지 → 저장 공간 절약
2. 데이터 무결성 유지 → 이상(Anomaly) 방지
3. 데이터 일관성 보장 → 삽입/삭제/갱신 이상 제거
4. 데이터베이스 성능 최적화 → 효율적인 데이터 검색

---

정규화 단계

1NF (제1 정규형) - 원자성(Atomicity) 보장

• 각 컬럼이 하나의 값만 가져야 한다.
• 반복되는 그룹이 존재하면 안 된다.

문제 (1NF 위반 예시)

Orders
-------------------------------------------------
| order_id | items                | total_price |
-------------------------------------------------
| 1        | "Laptop, Mouse"       | 1200        |
| 2        | "Keyboard, Headset"   | 300         |
-------------------------------------------------

• items 컬럼에 여러 개의 값이 포함됨 → 비정규화 상태

해결 (1NF 적용)

Orders
------------------------------
| order_id | item     | price |
------------------------------
| 1        | Laptop   | 1000  |
| 1        | Mouse    | 200   |
| 2        | Keyboard | 150   |
| 2        | Headset  | 150   |
------------------------------

• 하나의 컬럼에 하나의 값만 존재하도록 변경

---

2NF (제2 정규형) - 부분 함수 종속 제거

• 1NF를 만족하면서, 기본 키의 일부분만으로 결정되는 속성을 제거한다.
• 복합 키(Composite Key)가 있을 경우 발생

문제 (2NF 위반 예시)

OrderDetails (order_id, product_id, product_name, price)
--------------------------------------------------------
| order_id | product_id | product_name | price |
--------------------------------------------------------
| 1        | 101        | Laptop       | 1000  |
| 1        | 102        | Mouse        | 200   |
| 2        | 103        | Keyboard     | 150   |
--------------------------------------------------------

• product_name과 price는 product_id에만 의존하는데, order_id도 기본 키에 포함됨

해결 (2NF 적용)

• Products 테이블을 분리하여 product_id를 참조하도록 변경

Orders (order_id, product_id)
-----------------------------------
| order_id | product_id |
-----------------------------------
| 1        | 101        |
| 1        | 102        |
| 2        | 103        |
-----------------------------------

Products (product_id, product_name, price)
------------------------------------------
| product_id | product_name | price |
------------------------------------------
| 101        | Laptop       | 1000  |
| 102        | Mouse        | 200   |
| 103        | Keyboard     | 150   |
------------------------------------------

• 이제 product_name과 price는 product_id에만 의존

---

3NF (제3 정규형) - 이행적 종속 제거

• 2NF를 만족하면서, 기본 키가 아닌 속성이 다른 기본 키가 아닌 속성을 결정하지 않아야 한다.

문제 (3NF 위반 예시)

Customers (customer_id, name, city, zip_code)
----------------------------------------------
| customer_id | name  | city   | zip_code |
----------------------------------------------
| 1          | Alice | Seoul  | 10001    |
| 2          | Bob   | Busan  | 20002    |
----------------------------------------------

• city는 zip_code에 의해 결정됨 → zip_code를 customer_id가 아닌 city에 종속시켜야 함

해결 (3NF 적용)

Customers (customer_id, name, zip_code)
---------------------------------------
| customer_id | name  | zip_code |
---------------------------------------
| 1          | Alice | 10001    |
| 2          | Bob   | 20002    |
---------------------------------------

ZipCodes (zip_code, city)
-------------------------
| zip_code | city  |
-------------------------
| 10001    | Seoul |
| 20002    | Busan |
-------------------------

• zip_code와 city를 별도 테이블로 분리

---

꼬리 질문 

1. 정규화를 하면 성능이 항상 향상될까요?
   • 아니요. 지나친 정규화는 조인(Join) 연산을 증가시켜 성능 저하를 초래할 수 있습니다.
   • OLTP 시스템(트랜잭션 중심)에서는 정규화가 유리하지만, OLAP 시스템(분석 중심)에서는 정규화를 완화(비정규화)하여 성능을 개선하는 경우가 많습니다.
2. 정규화를 적용하지 않아도 되는 경우는?
   • 읽기 성능이 중요한 데이터웨어하우스(OLAP) 시스템에서는 일부러 중복을 허용하고 정규화를 덜 적용하기도 합니다.
3. BCNF(보이스-코드 정규형)란?
   • 3NF를 만족하면서 모든 결정자가 후보 키(Candidate Key)이어야 하는 정규형입니다.
   • 즉, 후보 키가 아닌 컬럼이 다른 컬럼을 결정하는 경우를 제거합니다.