[ TIL ] Day 58 - SQL 데이터 관리 / 데이터 모델링

🗓️ 수업 일자 : 2025.12.16

✨ 오늘의 수업 평가 :  [ GOOD ]  오늘도 한 걸음 성장! 🌱✨🌙

 

이미 배웠던 데이터에 대한 이론이 진행되서 복습하는 느낌~

처음에 배울 때 어려웠던 내용들이, 이제는 한단계 업데이트 되어도 잘 따라가고 있어서 참 뿌듯하다 🤓

 

📝  오늘 배운 내용  

- SQL 데이터 관리

- 데이터베이스 모델링

- 논리적 모델링

- 물리적 모델링

 

---

1. SQL 데이터 관리

1) 데이터 제약 사항 설정

• NOT NULL : 데이터를 필수 값(공란 미허용)으로 설정, DEFAULT 가 함께 사용 됨
• CONSTRAINT : 데이터의 세부 제약 사항을 설정

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  
  -- CONSTRAINT : 작성 된 컬럼 중 하나에 대해 제약 사항을 검사하는 컬럼 추가
  CONSTRAINT content_length CHECK (LENGTH(content) >= 4)
);


-- 기존 제약사항 삭제
ALTER TABLE posts
	DROP CONSTRAINT content_length;


-- 새로운 제약사항 추가
-- 이미 존재하고 있는 데이터가 해당 제약사항을 위반하면 에러 메세지 발생
-- 기존의 데이터를 수정하거나 삭제 하고 제약사항 추가 가능
ALTER TABLE posts
  ADD CONSTRAINT content_length CHECK (LENGTH(content) >= 10);

 

 

 

2) 관계형 데이터 관리

 

Foreign Key (FK)

• 관계형 데이터 베이스의 핵심
• 무결성 보장 : 논리적이며 일관된 유효성 데이터를 사용, 잘못된 참조 또는 고아 레코드 방지
• 다른 테이블의 PK(고유키)로 FK(외래키)지정하여 두 테이블간의 관계 정립

관계형 데이터 관련 명령어

관계형 데이터 컬럼 설정하기 

CREATE TABLE coments (
  id SERIAL PRIMARY KEY,
  content TEXT NOT NULL,
  product_id int NOT NULL,
  user_id int NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  
  -- 관계형 데이터 컬럼에 대한 설정 내용
  FOREIGN KEY (현재_테이블_컬럼명) REFERENCES 참조_테이블명(컬럼명) ON DELETE 옵션
  
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
  
  FOREIGN KEY (product_id) REFERENCES products(id) : 현재 테이블의 product_id는 products(id)를 참조
  ON DELETE CASCADE : 참조한 테이블에서 데이터가 삭제 되면 현재 테이블의 연결된 데이터도 삭제
);

 

노란색 박스 : 생성된 coments 테이블의 FK 제약 조건

 

이미 선언 된 컬럼을 관계형 데이터로 수정하기 
ALTER TABLE coments
  ADD CONSTRAINT fk_user_id
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
  
  
관계형 데이터 컬럼의 "관계" 제약 중단하기
ALTER TABLE posts
  DROP CONSTRAINT fk_user_id;

노란색 박스 : 이미 선언 되어있던 coments 테이블의 'user_id' 컬럼에 FK 제약 조건 추가

 

 

 

3) Transaction (트랜젝션)

• 트랜젝션 : 하나의 논리적 작업 단위를 말함
• 여러 작업을 묶어서 "하나의 트랜젝션"으로 반영 ⇒ 작업이 모두 반영 / 모두 미반영 됨

-- 1. 트랜잭션 시작
BEGIN;

-- 2. 필요한 쿼리 진행하기
UPDATE products SET name = "곰돌이인형" where id = 1 ;
... 작업 생략 ...


-- 3-1. 위에서 작성한 쿼리들을 실행한 결과를 확정하기
COMMIT;

-- 3-2. 작성한 쿼리가 잘못 된 경우 트랜젝션 전체 취소 
ROLLBACK;

 

 

 

2. 데이터베이스 모델링

데이터 모델링

• 정해진 답은 없지만, 현업에서 사용하는 보편적인 구성 방식, 가이드 라인은 존재함
• ER다이어그램 그래픽 도구 , 텍스트 기반 다이어그램 도구 등 개발 작업 시 시각적 다이어그램을 활용
• 데이터 모델링 용어 정리
  • Entity: 하나의 데이터 그룹, 테이블. 데이터를 저장하려고 하는 위치
  • Attribute: Entity에 대해 저장하는 데이터 필드(컬럼) 정보로, "필드명 + 데이터타입 + 제약조건" 등으로 구성
  • Relationship: Entity들 사이의 관계 정의
  • Constraint: Entity의 Attribute / Relationship 에 적용되는 제약 조건 선언

 

데이터 모델링 종류

• 개념 모델 (Conceptual Model) : Entity와 Entity 간 관계만 표현. Entity를 세분화 하지 않고 통째로 표현 한 간략형 모델.
• 논리 모델 (Logical Model) : Entity, Attribute(필드명), PK, FK 정보가 담겨있는 기본 모델. 
• 물리 모델 (Physical Model) : 실제 DB를 기준으로 데이터의 타입등 모든 구현 정보가 담겨있는 모델.

개념모델, 논리모델, 물리모델 비교

 

 

3. 논리적 모델링

1) 카디널리티 모델링

 

카디널리티

• 1:1 관계 : Entity가 1:1로 연결되는 구조
• 1:N 관계 : 연결된 Entity 관계에서 Entity A의 데이터 한개에 Entity B의 다수 데이터가 연결되는 구조
• N:M 관계  : 연결된 Entity 관계에서 양쪽의 Entity가 서로 다수의 데이터로 연결되는 구조
• 최소 카디널리티 (0 or 1) : 관계에서 연결되는 데이터가 없어도 되는지 or 최소한 하나가 있어야 하는지
  최대 카디널리티 (1 or N) : 관계 데이터가 최대 몇개까지 연결 가능한지에 대한 설정

Crow’s Foot Notation(까마귀발 표기법)

 

• | : 1개만 사용
• ○ : 0개, 즉 데이터가 없어도 됨
• < (갈라진 발) : N개, 다수 가능 (= 까마귀발 🐦)

 

Crow's Foot을 활용한 카디널리티 표기

 

1 : 1 관계 표현
order | — | payment 
주문 1개 : 결제 1개 


1 : N 관계 표현 
order | — < order_items
주문 1개 : 주문 세부 아이템은 최소 1개 이상을 보장 


N : M 관계 표현 
order < — > product
주문 : 제품 
서로 여러 데이터에 관계되어 들어감

order | — < order_items > - | product
실제로는 이렇게 중간 테이블이 공존하는 상태


1 : 0,N 관계 (없어도 되고, 여러 개 가능)
product | — ○< comment 
제품 1개 : 댓글 0개 이상 ( ○: 0개도 가능, < : 다수도 가능 )

 

 

 

 

2) 정규화

 

데이터베이스 이상 현상(Anomaly)

• 삽입 이상 : 데이터를 추가 할 때, 불필요한 정보까지 모두 입력 되어야 하는 문제
• 수정 이상 : 같은 데이터가 여러개 등록 되어있어서, 데이터를 수정해도 기존 데이터가 계속 유지되는 것처럼 보이는 것
• 삭제 이상 : 특정 데이터를 삭제하자 의도하지 않은 다른 데이터가 함께 삭제 되는 경우

정규화의 목적 : 데이터의 중복을 줄이고, 이상 현상(Anomaly)를 방지하고, 설계를 개선 하는 가이드 라인

 

 

제 1 정규형, 1NF

• 하나의 컬럼에 하나의 값을 가지고 있어야 함
• 데이터를 쪼갤 수 있는 가장 작은 단위로 쪼개야 함 
  ex) address : 서울특별시 강남구 테헤란로 123 개발빌딩 103호
        city : 서울 / discrict : 강남구 / street : 테헤란로 123 / build : 개발빌딩 103호
• 후보키 Candiate Key : 하나의 로우를 구분 할 수 있는 Attribute 집합 

 

 

제 2 정규형, 2NF

• 제1정규형을 만족하는 것을 전제로 진행
• 후보키로 복합키가 사용 되는 경우, 일부 키에만 종속되는 컬럼은 사용 불가
  ex) comment Entity에서 Candiate Key 가 [ user_id, product_id ] 일 때,
        price(제품 개별 가격) 컬럼은 product Entity에서만 사용하는 데이터 => 사용하면 안됨 
• 복합 후보키 전체에 의존하는 키에 대해서는 사용 가능  
  
  

 

제 3 정규형, 3NF

• 하나의 컬럼에 하나의 값을 가지고 있어야 함
• PK에 의존하여 사용 되지 않는 값은 사용 불가 
  
  

 

 

4. 물리적 모델링

1) 테이블 이름 규칙

• 명사를 사용하며 복수/단수 두가지 방식으로 쓰는데, 보통은 복수형 명사를 사용하고 snake_case 방식을 선호 

 

2) 컬럼 규칙

• 컬럼명 : 동일하게 명사로 snake_case 방식을 사용하는데, 단수형을 씀 
• 데이터 타입 : int(정수형), text(문자형), real(실수형) 등 타입을 지정하며 연산 / 최적화 등을 고려

 

3) 제약(constraint) 규칙

• ERD에서는 제약 규칙의 표기가 힘듦
• 별도로 메모 해 두고, 실제 작업 또는 비지니스 로직 구현 시 적용 필요

 

4) 인덱스

• 빠른 조회를 위해 사용하는 일종의 "색인" 역할.
• SQL에서는 PK, Unique 타입의 컬럼에 대해 자동으로 인덱스 설정 함.
• 일부 컬럼에 대해서는 사용자가 직접 인덱스 설정 가능.
• 자주 검색되고, 값이 다양해 중복 가능성이 낮고, 변경이 거의 없는 데이터에 대해 인덱스 설정 하는 것이 효율적

PK 와 Unique 값에 대해서는 자동으로 INDEX를 설정 해 줌

 

 

+ 추가 EXPLAIN ANALYZE란?

• SQL을 “진짜로 실행”해 보고, DB가 어떻게 처리되고 얼마나 걸렸는지 보여주는 명령어
• 쿼리가 느리거나, 인덱스 설정 확인, Join 구조의 효율 확인 등에 사용
• 결과 정보 : Seq Scan / Index Scan 여부(읽는 방식) , 각 단계 별 실행 시간, 처리된 row 수 표기 
  
  • Seq Scan : 조건에 상관 없이 모든 행 검사, 인덱스가 없거나 조건이 광범위한 경우 선택 됨
  • Index Scan : 인덱스 구조에 사용되고, 조건에 맞는 데이터만 접근할 때 선택 됨

조건 없이 전체 테이블을 불러오는 명령어 → Seq Scan 방식

 

조건을 넣어 인덱스에서 검색해 불러오는 명령어 → Index Scan 방식

 

 

---

 

📃 내일은 뭘 배울까 🤔

- 스프린트 미션 작업

- SQL vs Prisma ORM