현재 오즈코딩스쿨 강의를 통해 프론트엔드를 학습하고 있습니다.
본 포스트는 해당 강의에 대한 내용 정리를 목적으로 합니다.
1. 데이터 모델링 기초
1) 데이터 모델링이란?
데이터 모델링은 현실 세계의 데이터를 구조화하여, 데이터베이스에 저장하고 효율적으로 사용할 수 있도록 설계하는 과정이다.
a. 데이터 모델링의 3단계
| 단계 | 설명 | 사용 모델 |
|---|---|---|
| 개념적 모델링 | 업무 중심의 데이터 구조를 설계하는 단계이다. | ERD (Entity Relationship Diagram) |
| 논리적 모델링 | DBMS에 독립적인 논리적 구조를 설계하는 단계이다. | 정규화된 테이블 구조 |
| 물리적 모델링 | 실제 데이터베이스 시스템에 맞게 설계하는 단계이다. | SQL DDL (CREATE TABLE 등) |
b. 데이터 모델링의 기본 용어
| 용어 | 설명 |
|---|---|
| 엔티티(Entity) | 독립적으로 존재하며 관리할 대상이 되는 객체이다. (예: 사용자, 상품, 주문 등) |
| 속성(Attribute) | 엔티티가 가지는 고유한 정보이다. (예: 이름, 이메일, 가격 등) |
| 관계(Relationship) | 두 개 이상의 엔티티 간의 연관성을 나타내는 요소이다. |
| 기본키(Primary Key) | 엔티티를 유일하게 식별할 수 있는 속성이다. |
| 외래키(Foreign Key) | 다른 테이블의 기본키를 참조하여 관계를 표현하는 속성이다. |
c. 데이터 모델링 도구
-
ERDCloud: 웹 기반 ERD 작성 도구이다.
- ERD 예시[User] --------< [Order] | | user_id (PK) order_id (PK) name user_id (FK) email date -
User엔티티와Order엔티티는 1:N 관계이다. -
하나의 사용자는 여러 개의 주문을 할 수 있는 구조이다.
-
dbdiagram.io: 간단하고 직관적인 모델링 도구이다.
-
MySQL Workbench: MySQL에서 공식 제공하는 ERD 설계 도구이다.
-
Oracle SQL Developer: 오라클 전용 데이터 모델링 및 관리 도구이다.
2) 정규화란?
정규화는 데이터의 중복을 줄이고, 데이터 무결성을 높이기 위해 테이블을 구조적으로 나누는 과정이다.
a. 정규화 장점
-
🔁 데이터 중복 감소
- 동일한 정보가 여러 테이블에 반복 저장되지 않는다.
- 예: 고객 주소를 하나의 테이블에만 저장하고 참조하면 된다.
- 효과: 저장 공간 절약 + 중복으로 인한 오류 방지
-
🛡️ 데이터 무결성 향상
- 데이터가 한 곳에서만 관리되므로 일관성을 유지하기 쉽다.
- 예: 고객 이름이 바뀌면 한 군데만 수정하면 됨.
- 효과: 수정(Update)·삭제(Delete) 시 오류 최소화
-
📚 데이터 구조의 명확화
- 각 테이블이 명확한 역할(주제)을 가지게 된다.
- 예: customer, order, product 등 역할별 분리
- 효과: 유지보수와 협업이 쉬워짐
-
📈 확장성과 유연성 향상
- 새로운 요구사항이나 속성이 생겨도 구조 변경이 간단하다.
- 예: 고객 테이블에 전화번호만 추가하면 됨
- 효과: 서비스 기능 확장 시 유리함
-
🧮 정확한 질의(Query) 결과
- 설계가 체계적이기 때문에 잘못된 중복 집계나 계산 오류를 방지할 수 있다.
- 효과: 리포트, 통계, 분석의 신뢰도 향상
-
🔒 보안 관리 용이
- 민감한 데이터(예: 사용자 비밀번호)를 별도 테이블로 분리 가능
- 효과: 테이블 단위로 접근권한 설정이 가능함
b. 정규화 단점
-
조인이 많아져 복잡한 쿼리 발생 가능
-
실시간 성능이 중요한 시스템에서는 비정규화로 속도 최적화 필요
-
그래서 일반적으로 3차 정규형(3NF)까지 정규화하고, 읽기 성능이 중요한 상황에서는 일부 비정규화(denormalization)도 사용한다.
c. 제1정규형(1NF)
제1정규형(1NF)은 데이터베이스 정규화의 첫 번째 단계로, 가장 기본적이고 중요한 정규화 규칙이다.
모든 컬럼의 값은 원자값(atomic value)을 가져야 한다. 즉, 하나의 칸(셀)에는 하나의 값만 존재해야 한다.
| 항목 | 설명 |
|---|---|
| 원자성(Atomicity) | 각 칼럼은 더 이상 나눌 수 없는 단일 값만 가져야 함 |
| 반복 그룹 제거 | 배열, 리스트, 쉼표 구분 값 등 중복 구조 제거 |
✅ Primary Key
| 특징 | 설명 |
|---|---|
| 고유성 | 각 행을 유일하게 식별할 수 있어야 한다. |
| NOT NULL | 기본 키는 절대 NULL을 가질 수 없다. |
| 단일 또는 복합 | 하나의 컬럼 또는 여러 컬럼의 조합으로 구성 가능하다. |
| 테이블당 1개만 가능 | 하나의 테이블에는 오직 하나의 Primary Key만 설정할 수 있다. |
d. 제2정규형(2NF): 부분 함수 종속을 제거해야 한다.
2NF = 1NF + 기본키에 대한 부분 함수 종속 제거
-
함수 종속(Function Dependency)이란?
- 어떤 속성 A가 다른 속성 B를 결정하는 경우 B는 A에 함수 종속이다라고 표현.
(기호: A → B)
- 어떤 속성 A가 다른 속성 B를 결정하는 경우 B는 A에 함수 종속이다라고 표현.
-
부분 함수 종속(Partial Dependency)이란?
- 복합 기본키(Composite Key)를 사용하는 테이블에서, 어떤 속성이 기본키의 일부에만 종속되어 있는 경우.
- 이 경우 그 속성은 기본키 전체에 종속되지 않기 때문에 문제가 된다.
e. 제3정규형(3NF): 이행 함수 종속을 제거해야 한다.
제3정규형(3NF)이란, 제2정규형(2NF)을 만족하면서 이행적 함수 종속(Transitive Dependency)이 없는 상태를 의미한다.
즉, 기본키가 아닌 속성이 다른 기본키가 아닌 속성에 종속되지 않아야 한다는 것이 핵심이다.
- 이행적 함수 종속이란?
이행적 함수 종속이란 A → B, B → C인 경우, A → C가 성립하는 종속 관계를 의미한다.
이때 C는 A에 직접 종속된 것이 아니라 B를 거쳐 종속되었기 때문에 이행적으로 종속되었다고 한다.
3) 테이블 나누기 (FK)
a. 테이블 나누기란?
- 한 테이블에 여러 종류의 데이터를 모두 넣으면 중복, 업데이트 오류가 발생할 수 있다.
- 이를 방지하기 위해 관련 데이터끼리 묶어 역할별로 테이블을 분리한다.
- 예를 들어, 고객 정보와 주문 정보를 따로 분리하는 것
b. 테이블 나누기 장점
- 데이터 중복 제거 → 저장 공간 절약
- 데이터 무결성 보장 → 업데이트/삭제 시 오류 감소
- 유지보수 용이 → 역할별 데이터 관리 명확
- 복잡한 관계 표현 가능 → 다양한 비즈니스 모델 지원
c. Foreign Key(외래 키) 정의
- 한 테이블의 컬럼이 다른 테이블의 Primary Key를 참조할 때 사용한다.
- 참조 무결성을 보장하여 두 테이블 간의 관계를 유지한다.
- 예:
order테이블의customer_id는customer테이블의customer_id를 참조한다.
4) 테이블 관계
a. 1:1 관계 (One to One)
- 한 테이블의 한 행이 다른 테이블의 한 행과 연결(예: 사용자 - 사용자 프로필)
- 한 사람당 하나의 주민등록번호, 하나의 프로필
- 드물게 사용, 확장/보안용
CREATE TABLE user (
user_id SERIAL PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE profile (
user_id INT PRIMARY KEY,
bio TEXT,
FOREIGN KEY (user_id) REFERENCES user(user_id)
);- 보통 자주 사용되진 않지만, 보안 분리나 드문 확장 데이터를 분리할 때 사용
b. 1:N 관계 (One to Many) ← 가장 흔함
- 한 행이 다른 테이블의 여러 행과 연결(예: 고객 - 주문)
- 한 명의 고객이 여러 개의 주문을 가질 수 있음
CREATE TABLE customer (
customer_id SERIAL PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
order_date DATE,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);customer_id는orders테이블에 Foreign Key로 사용됨
c. N:M 관계 (Many to Many)
- 여러 행이 서로 여러 행과 연결(예: 학생 - 수강과목)
- 한 명의 학생이 여러 과목을 듣고, 한 과목을 여러 학생이 들을 수 있음
- 중간 테이블 필수
CREATE TABLE student (
student_id SERIAL PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE course (
course_id SERIAL PRIMARY KEY,
title VARCHAR(100)
);
-- 중간 테이블 필요
CREATE TABLE enrollment (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES student(student_id),
FOREIGN KEY (course_id) REFERENCES course(course_id)
);enrollment이 학생-과목 관계를 연결하는 중간 테이블
d. 관계를 만드는 핵심: 외래 키 (Foreign Key)
-
다른 테이블의 기본 키를 참조하여 관계를 형성
-
데이터 무결성 보장, JOIN 가능
2. SQL로 여러 테이블 다루기
1) JOIN(테이블 붙이기)
조인은 두 개 이상의 테이블을 특정 조건에 따라 연결하여 하나의 결과로 보여주는 SQL 문법이다.
a. INNER JOIN
두 테이블에서 조건에 맞는 행만 출력하는 방식이다.
SELECT u.name, o.order_date
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;users와orders테이블을 user_id로 연결하여 주문한 사용자만 보여주는 쿼리이다.
b. LEFT JOIN
왼쪽 테이블은 모두 출력하고, 오른쪽 테이블은 일치하는 데이터만 출력한다. (없으면 NULL)
SELECT u.name, o.order_date
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;- 주문을 하지 않은 사용자도 결과에 포함되는 쿼리이다.
c. RIGHT JOIN
오른쪽 테이블은 모두 출력하고, 왼쪽 테이블은 일치하는 데이터만 출력한다. (MySQL 등에서 사용)
SELECT u.name, o.order_date
FROM users u
RIGHT JOIN orders o ON u.user_id = o.user_id;- 모든 주문이 보이게 하고, 사용자 정보가 없으면 NULL로 출력된다.
d. FULL JOIN
- 양쪽 테이블에 없는 데이터도 보고 싶을 때
- 일치 여부를 기준으로 통합 집계/비교할 때
- 예: 회원 목록 + 구매 내역 → 모든 사람의 상태 보고 싶을 때
SELECT a.id, a.name, b.score
FROM student a
FULL JOIN grade b ON a.id = b.id;e. CROSS JOIN
- 두 테이블의 모든 행을 곱집합(Cartesian Product) 형태로 조인하는 방식이다.
- 첫 번째 테이블의 각 행마다 두 번째 테이블의 모든 행을 결합한다.
SELECT color_name, size_name
FROM color
CROSS JOIN size;f. SELF JOIN
- 테이블을 자기 자신과 조인하는 방식이다.
- 하나의 테이블 안에서 행들끼리 비교/관계 연결이 필요할 때 사용
- 테이블에 별칭(alias)을 주어 자기 자신을 두 개처럼 사용한다
SELECT
e.name AS employee,
m.name AS manager
FROM
employee e
LEFT JOIN
employee m ON e.manager_id = m.emp_id;2) GROUP BY + HAVING
a. GROUP BY
GROUP BY는 SQL에서 데이터를 특정 기준으로 묶어 집계 함수(Aggregate Function)를 적용할 때 사용하는 구문이다.
SELECT 열1, 집계함수(열2)
FROM 테이블
GROUP BY 열1;-
열1 기준으로 데이터를 묶고, 묶인 그룹마다 열2에 집계함수를 적용하는 구조이다.
-
자주 쓰는 집계 함수
| 함수 | 설명 |
| ---------- | --------- |
|COUNT(*)| 행의 개수를 센다 |
|SUM(열)| 합계를 구한다 |
|AVG(열)| 평균을 구한다 |
|MAX(열)| 최대값을 구한다 |
|MIN(열)| 최소값을 구한다 |
b GROUP BY + HAVING
WHERE는 집계되기 전 조건 필터링, HAVING은 집계된 결과를 나중에 필터링할 때 사용한다.
SELECT user_id, SUM(total_amt) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(total_amt) > 20000;- 총 주문 금액이 2만원 이상인 사용자만 보여주는 쿼리이다.
c. GROUP BY 다중 컬럼
여러 기준으로 묶고 싶을 때는 쉼표로 열을 나열한다.
SELECT user_id, DATE(order_date), COUNT(*) AS daily_orders
FROM orders
GROUP BY user_id, DATE(order_date);- 사용자별로 날짜 기준으로 주문 수를 집계하는 예시이다.
d. 요약
| 항목 | 설명 |
|---|---|
GROUP BY | 데이터를 기준 컬럼으로 묶는다 |
집계 함수 | 묶인 그룹에 통계 처리를 적용한다 |
HAVING | 그룹화된 결과에 조건을 건다 |
GROUP BY 다중 열 | 복합 기준으로 그룹화한다 |
2) 서브쿼리(Subquery)
쿼리 안에 또 다른 쿼리를 넣어 결과를 조건으로 사용하는 방식이다.
SELECT name
FROM users
WHERE user_id IN (
SELECT user_id
FROM orders
WHERE order_date >= '2025-01-01'
);- 2025년 이후에 주문한 사용자 이름을 출력하는 쿼리이다.
3) UNION
두 개 이상의 SELECT 결과를 위로 이어 붙이는 방식이다. (열 개수와 데이터 타입이 같아야 한다.)
SELECT name, email FROM customers
UNION
SELECT name, email FROM suppliers;- 고객과 공급자의 이름과 이메일을 모두 출력한다. (중복 제거됨) 중복 포함하려면
UNION ALL을 사용한다.
4) 다중 테이블 INSERT/UPDATE/DELETE
여러 테이블을 대상으로 데이터를 삽입, 수정, 삭제할 수도 있다. 하지만 일반적으로는 트랜잭션을 사용하여 하나의 논리적 작업으로 묶는 방식을 많이 쓴다.
START TRANSACTION;
INSERT INTO orders (user_id, order_date) VALUES (1, NOW());
INSERT INTO order_items (order_id, product_id, quantity) VALUES (LAST_INSERT_ID(), 101, 2);
COMMIT;- 주문과 동시에 주문 상세도 함께 저장하는 예시이다.
5) Pagila 데이터베이스
a. Pagila 데이터베이스란?
Pagila 데이터베이스는 PostgreSQL 전용 샘플 데이터베이스로, MySQL의 유명 샘플 DB인 Sakila를 기반으로 만들어졌다.
영화 대여점을 주제로 하며, 현실적인 비즈니스 시나리오를 반영한 데이터 구조를 제공한다. SQL 학습, 조인 연습, 통계 분석, 데이터 모델링 등에 유용하다.
b. 주요 테이블 요약
| 테이블명 | 설명 |
|---|---|
film | 영화 기본 정보 (제목, 길이, 등급 등) |
actor | 배우 정보 |
film_actor | 영화-배우 관계 (N\:M) |
category | 영화 장르 |
film_category | 영화-장르 관계 |
language | 영화 언어 |
customer | 고객 정보 |
address, city, country | 주소 정보 |
store | 지점 (매장) 정보 |
staff | 매장 직원 정보 |
inventory | 매장 내 보유 DVD 재고 정보 |
rental | 대여 기록 |
payment | 결제 기록 |
c. 주요 관계
[film] ---< [film_actor] >--- [actor]
|
v
[film_category] >--- [category]
|
v
[inventory] ---< [rental] ---< [payment]
^
[customer]d. Pagila 데이터베이스 환경설정
- PostgreSQL 설치 https://www.postgresql.org/download/
PostgreSQL은 Pagila가 동작하기 위한 필수 DBMS이므로 먼저 설치해야 한다.
설치는 공식 사이트에서 운영체제에 맞게 진행하면 된다. 설치 중에는 pgAdmin도 함께 설치하는 것이 편리하다.
# postgresql 설치
brew install postgresql@16# 설정 파일 수정
vi ~/.zshrc
# 수정한 설정 파일 적용
source ~/.zshrc#postgresql로 주석처리 된 부분을 확인해보면 zshrc 파일을 열어서 postgresql 경로를 등록
# postgresql 실행
brew services start postgresql@16
# 상태 확인
brew services list- PostgreSQL에서 데이터베이스 생성
CREATE DATABASE pagila;- 스키마 및 데이터 삽입
방법 1: psql 명령어 이용
터미널에서 아래와 같이 실행하면 된다.
psql -U postgres -d pagila -f pagila-schema.sql
psql -U postgres -d pagila -f pagila-data.sqlpostgres는 PostgreSQL의 기본 사용자명이고, pagila는 사용할 데이터베이스 이름이다.
pagila-schema.sql은 테이블 정의, pagila-data.sql은 초기 데이터가 들어 있는 파일이다.
방법 2: pgAdmin 사용
GUI를 선호한다면 pgAdmin으로도 쉽게 설정할 수 있다.
pgAdmin에서 pagila 데이터베이스를 선택하고 Query Tool에서 SQL 파일을 실행하면 된다.
- 설치 확인
SELECT * FROM actor LIMIT 10;
SELECT * FROM film LIMIT 10;e. Pagila 데이터 모델링 이해
Pagila는 아래와 같은 주요 주체(Entity)로 구성되어 있다
- 고객(Customer): 대여를 하는 사람
- 직원(Staff): 대여를 처리하는 사람
- 매장(Store): 직원과 재고가 있는 지점
- 영화(Film): 대여 가능한 콘텐츠
- 재고(Inventory): 각 매장에서 보유한 영화 복사본
- 대여(Rental): 고객이 영화를 빌리는 행위
- 결제(Payment): 대여에 따른 지불 내역
