[SQL&Database] 테이블 관리 및 정규화

Comely·2025년 6월 9일

SQL

목록 보기

4/6

데이터베이스와 테이블 기본 관리

데이터베이스 생성과 삭제

-- 데이터베이스 생성
CREATE DATABASE 데이터베이스이름;

-- 데이터베이스 삭제 (되돌릴 수 없음!)
DROP DATABASE 데이터베이스이름;

테이블 생성

테이블을 만들 때는 컬럼명과 데이터 타입을 지정해야 합니다.

-- 기본 테이블 생성
CREATE TABLE 테이블명 (
    컬럼1이름 datatype,
    컬럼2이름 datatype,
    컬럼3이름 datatype
    -- 주의: 마지막 줄에는 콤마(,) 금지!
);

-- 실제 예시
CREATE TABLE new_table (
    id INT,
    이름 VARCHAR(100),
    나이 INT
);

기본값 설정

CREATE TABLE new_table2 (
    id INT,
    이름 VARCHAR(100) DEFAULT '홍길동',  -- 기본값 설정
    나이 INT
);

데이터가 입력되지 않을 때 자동으로 채워질 기본값을 설정할 수 있습니다.

테이블 삭제

DROP TABLE 테이블명;  -- 되돌릴 수 없음!

컬럼 관리 (ALTER TABLE)

컬럼 추가

ALTER TABLE 테이블명
ADD 컬럼명 VARCHAR(100);

-- 기본값과 함께 추가
ALTER TABLE 테이블명
ADD 컬럼명 INT DEFAULT 0;

컬럼 데이터 타입 변경

ALTER TABLE 테이블명
MODIFY COLUMN 컬럼명 datatype;

-- 예시
ALTER TABLE member 
MODIFY COLUMN 나이 BIGINT;

주의사항: 이미 문자 데이터가 있는 컬럼을 숫자 타입으로 변경하는 것은 불가능

컬럼 삭제

ALTER TABLE 테이블명
DROP COLUMN 컬럼명;

제약조건 (Constraints)

1. NOT NULL - 필수 입력 설정

CREATE TABLE new_table (
    id INT NOT NULL,        -- NULL 값 입력 금지
    이름 VARCHAR(100) NOT NULL,
    나이 INT
);

해당 컬럼에 데이터를 반드시 입력해야 함
빈 값으로 두면 저장이 거부됨

2. UNIQUE - 중복 값 방지

-- 단일 컬럼에 UNIQUE 설정
CREATE TABLE new_table (
    id INT UNIQUE,          -- 모든 값이 달라야 함
    이름 VARCHAR(100),
    나이 INT
);

-- 복수 컬럼 조합으로 UNIQUE 설정
CREATE TABLE new_table (
    id INT,
    이름 VARCHAR(100),
    나이 INT,
    UNIQUE(이름, 나이)      -- 이름+나이 조합이 중복되면 안됨
);

UNIQUE 조합 예시:
| id | 이름 | 나이 |
|---|---|---|
| 1 | aaa | 20 |
| 2 | aaa | 21 | ← 허용 (조합이 다름)
| 3 | aaa | 20 | ← 거부 (조합이 중복)

3. CHECK - 값의 범위나 조건 제한

CREATE TABLE new_table (
    id INT,
    이름 VARCHAR(100),
    나이 INT CHECK (나이 > 0),      -- 양수만 입력 가능
    성별 VARCHAR(10) CHECK (성별 IN ('남', '여'))  -- 특정 값만 허용
);

4. PRIMARY KEY - 기본 키 설정

CREATE TABLE new_table (
    id INT PRIMARY KEY,     -- 자동으로 NOT NULL, UNIQUE 포함
    이름 VARCHAR(100),
    나이 INT
);

PRIMARY KEY의 역할:

각 행을 고유하게 식별하는 역할
NOT NULL과 UNIQUE 제약이 자동으로 적용
테이블당 하나만 설정 가능

5. AUTO_INCREMENT - 자동 증가

-- MySQL 방식
CREATE TABLE new_table (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 1, 2, 3... 자동 증가
    이름 VARCHAR(100),
    나이 INT
);

-- Oracle/PostgreSQL 표준 방식
CREATE TABLE new_table (
    id NUMBER GENERATED BY DEFAULT ALWAYS AS IDENTITY PRIMARY KEY,
    이름 VARCHAR(100)
);

6. 제약조건 명명과 관리

CREATE TABLE new_table (
    id INT,
    이름 VARCHAR(100),
    나이 INT,
    CONSTRAINT pk_new_table PRIMARY KEY (id),           -- 기본키 제약
    CONSTRAINT chk_age CHECK(나이 > 10),                -- 나이 체크 제약
    CONSTRAINT uk_name UNIQUE(이름)                     -- 이름 유일성 제약
);

기존 테이블에 제약조건 추가:

ALTER TABLE 테이블명 MODIFY 컬럼명 INT NOT NULL;

데이터베이스 정규화 (Normalization)

정규화의 목적

데이터 중복을 줄이고 무결성을 보장하며, 수정/삭제 시 발생할 수 있는 문제를 방지하기 위한 과정입니다.

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

문제 상황

체육센터 수강등록 시스템에서 한 회원이 여러 프로그램을 신청하는 경우:

회원번호	회원이름	프로그램
101	강호동	스쿼시초급
102	손흥민	헬스
103	김민수	헬스, 골프초급

제1정규화 해결

하나의 셀에는 하나의 값만 저장하도록 분리:

회원번호	회원이름	프로그램
101	강호동	스쿼시초급
102	손흥민	헬스
103	김민수	헬스
103	김민수	골프초급

장점:

데이터 검색, 수정, 삭제가 용이해짐
성능 문제 해결
추가 컬럼 확장이 쉬워짐

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

문제 상황

복합 기본키에서 일부 컬럼에만 종속되는 속성이 있는 경우:

회원번호	회원이름	프로그램	가격	납부여부
101	강호동	스쿼시초급	5000	0
102	손흥민	헬스	6000	1
103	김민수	헬스	6000	1
103	김민수	골프초급	8000	0

문제점: '가격'은 '프로그램'에만 의존하므로, 헬스 가격 변경 시 모든 헬스 수강생 행을 수정해야 함

제2정규화 해결

부분 종속성이 있는 컬럼을 별도 테이블로 분리:

수강등록현황 테이블

회원번호	회원이름	프로그램	납부여부
101	강호동	스쿼시초급	0
102	손흥민	헬스	1
103	김민수	헬스	1
103	김민수	골프초급	0

프로그램 테이블

프로그램	가격
스쿼시초급	5000
헬스	6000
골프초급	8000

부분 종속성 판단 연습

문제 1: 책대여내역 테이블에서 책가격 컬럼

회원아이디	책이름	날짜	회원등급	책가격	반납여부
lee	디자인책	1월1일	우수	1000	1
kim	만화책	1월2일	일반	2000	0

복합 기본키: (회원아이디 + 책이름 + 날짜)
책가격: '책이름'에만 종속 → 이동 필요

문제 2: 회원등급 컬럼

회원등급: '회원아이디'에만 종속 → 이동 필요

문제 3: 반납여부 컬럼

반납여부: 복합키 전체에 종속 → 이동 불필요

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

문제 상황

기본키가 아닌 컬럼에 종속되는 컬럼이 있는 경우

체육센터 프로그램 테이블

프로그램	가격	강사	출신대학
스쿼시	5000	김을용	서울대
헬스	6000	박덕팔	연세대
골프	8000	이상구	고려대
골프중급	9000	이상구	고려대

문제점: '출신대학'은 '프로그램'(기본키)이 아닌 '강사'에 종속됨

제3정규화 해결

이행적 종속성이 있는 컬럼을 별도 테이블로 분리:

체육센터 프로그램 테이블

프로그램	가격	강사
스쿼시	5000	김을용
헬스	6000	박덕팔
골프	8000	이상구
골프중급	9000	이상구

강사정보 테이블

강사	출신대학
김을용	서울대
박덕팔	연세대
이상구	고려대

Foreign Key (외래키) 관리

Foreign Key의 개념

다른 테이블의 기본키를 참조하는 컬럼으로, 테이블 간의 관계를 명확하게 설정합니다.

개선된 테이블 설계

프로그램 테이블 (Primary Key와 Foreign Key 적용)

id	프로그램	가격	강사id
1	스쿼시	5000	1
2	헬스	6000	2
3	골프	8000	3

강사정보 테이블

id	강사	출신대학
1	김을용	서울대
2	박덕팔	연세대
3	이상구	고려대

Foreign Key 생성 방법

테이블 생성 시 설정

CREATE TABLE program (
    id INT PRIMARY KEY,
    프로그램 VARCHAR(100),
    가격 INT,
    강사id INT REFERENCES teacher(id)  -- Foreign Key 설정
);

-- 또는 CONSTRAINT 사용
CREATE TABLE program (
    id INT PRIMARY KEY,
    프로그램 VARCHAR(100),
    가격 INT,
    강사id INT,
    CONSTRAINT fk_teacher FOREIGN KEY (강사id) REFERENCES teacher(id)
);

기존 테이블에 추가:

ALTER TABLE program ADD 
CONSTRAINT fk_teacher FOREIGN KEY (강사id) REFERENCES teacher(id);

Foreign Key의 장점

참조 무결성 보장: 존재하지 않는 강사ID 입력 방지
삭제 방지: 참조되고 있는 강사 데이터 삭제 차단
관계 시각화: DBeaver 등에서 테이블 관계를 화살표로 표시

정규화 실습 예제

쇼핑몰 구매내역 정규화

원본 테이블

구매자	상품명	수량	날짜	회원이름	상품카테고리	가격	무료배송여부
user1	노트북	1	2024-01-01	김철수	전자제품	1000000	Y
user2	티셔츠	2	2024-01-02	이영희	의류	50000	N

정규화 후

1. 구매내역 테이블 (복합키: 구매자+상품명+수량+날짜)

구매자id	상품id	수량	날짜
1	1	1	2024-01-01
2	2	2	2024-01-02

2. 회원정보 테이블

구매자id	회원이름
1	김철수
2	이영희

3. 상품정보 테이블

상품id	상품명	가격	카테고리id
1	노트북	1000000	1
2	티셔츠	50000	2

4. 카테고리정보 테이블 (제3정규화)

카테고리id	상품카테고리	무료배송여부
1	전자제품	Y
2	의류	N

이러한 정규화를 통해 데이터 중복을 제거하고, 수정 시 일관성을 보장하며, 데이터베이스의 효율성을 높일 수 있습니다.

Comely

App, Web Developer

이전 포스트

[SQL&Database] 서브쿼리와 그룹화 함수

다음 포스트

[SQL&Database] 테이블 관리 및 정규화

SQL

데이터베이스와 테이블 기본 관리

데이터베이스 생성과 삭제

테이블 생성

테이블 삭제

컬럼 관리 (ALTER TABLE)

컬럼 추가

컬럼 데이터 타입 변경

컬럼 삭제

제약조건 (Constraints)

1. NOT NULL - 필수 입력 설정

2. UNIQUE - 중복 값 방지

3. CHECK - 값의 범위나 조건 제한

4. PRIMARY KEY - 기본 키 설정

5. AUTO_INCREMENT - 자동 증가

6. 제약조건 명명과 관리

데이터베이스 정규화 (Normalization)

정규화의 목적

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

문제 상황

제1정규화 해결

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

문제 상황

제2정규화 해결

부분 종속성 판단 연습

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

문제 상황

제3정규화 해결

Foreign Key (외래키) 관리

Foreign Key의 개념

개선된 테이블 설계

Foreign Key 생성 방법

Foreign Key의 장점

정규화 실습 예제

쇼핑몰 구매내역 정규화

[SQL&Database] 서브쿼리와 그룹화 함수

[SQL&Database] JOIN과 데이터 조작

0개의 댓글