데이터베이스 기초 정리2 (설계, 트랜잭션, ALTER TABLE)

아래 데이터베이스 관련하여 정리한 첫번째 내용에 이어 작성하였습니다.

데이터베이스 기초 정리 1 (DDL, 제약조건, 인덱스, 실행계획)

 

📌 데이터베이스 설계

• 데이터베이스 설계는 단순히 테이블을 만드는 작업이 아닌, 어떤 데이터를 어떤 구조로 저장하고 관리할지 체계적으로 결정하는 과정이다.
• 보통 요구조건 분석 → 개념적 설계 → 논리적 설계 → 물리적 설계 → 구현 순서로 진행된다.단계 설명

  요구조건 분석	어떤 데이터와 기능이 필요한지 파악
  개념적 설계	엔티티와 관계를 큰 그림으로 표현
  논리적 설계	테이블, 컬럼, PK/FK 구조로 구체화
  물리적 설계	성능, 저장공간, 인덱스 등을 고려해 최적화
  구현	DDL로 실제 데이터베이스 객체 생성

 

1. 요구조건 분석

• 요구조건 분석은 시스템에서 어떤 데이터를 저장해야하고 어떤 기능이 필요한지 파악하는 단계로, 데이터 베이스를 만들기 전에 서비스의 목적과 필요한 기능을 정리하는 과정이다.
• 예를 들어 게시판 시스템을 만들 땐, 아래와 같은 요구사항과 질문을 생각할 수 있다.
  • 회원은 가입하고 로그인할 수 있어야 한다. → 회원을 구분하기 위해 어떤 정보가 필요한가?
  • 관리자는 일반 회원과 다른 권한을 가질 수 있어야 한다. → 권한은 한 명당 하나인지, 여러 개를 가질 수 있는지?

2. 개념적 설계

• 개념적 설계는 요구조건을 바탕으로 어떤 데이터들이 존재하고 서로 어떤 관계를 가지는지 큰 그림을 그리는 단계이며, 보통 E-R 다이어그램을 사용한다.
• 이 단계의 핵심은 무슨 데이터가 필요한지 데이터끼리 어떻게 연결되는지 파악하고 정리하는 것이다.
• 예를 들어 게시판 시스템에서는 회원, 권한, 게시글 과 같은 엔티티를 생각할 수 있으며, 이들 사이의 관계를 정리해야한다.
  • 회원은 여러 개의 게시글을 작성할 수 있다. → 회원 : 게시글 = 1 : N
  • 회원은 여러 권한을 가질 수 있고, 하나의 권한도 여러 회원에게 부여될 수 있다. → 회원 : 권한 = N : M

3. 논리적 설계

• 논리적 설계는 개념적 설계에서 정리한 내용을 바탕으로 이를 실제 테이블 구조로 변환하는 단계이며,
  • 엔티티를 테이블로, 속성을 컬럼으로, 관계를 PK/FK 구조로 구체화한다.
• 또한 이 과정에서 정규화를 적용하여 데이터 중복을 줄이고 데이터 무결성을 높인다.
  • 예를 들어 처음에 아래와 같이 회원테이블에 ‘권한’ 컬럼을 포함하여 테이블을 생각했다면, 권한 테이블을 따로 분리하여 만드는 방안은 어떨지 고민하고 설계하는 단계이다.
  이메일 이름 비밀번호 등록일 권한

  이메일	이름	비밀번호	등록일	권한
  a@test.com	홍길동	1234	2024-01-01	USER
  b@test.com	김철수	1234	2024-01-01	ADMIN

4. 물리적 설계

• 물리적 설계는 논리적 설계에서 더 나아가, 단순히 구조만 보는 것이 아닌 실제 DBMS 환경에서 성능과 저장 효율까지 고려해 조정하는 단계이다.
• 대표적으로 데이터 타입 구체화, 인덱스 설계, 저장 공간 고려, 조회 성능 최적화, 필요 시 반정규화 고려 등의 작업이 포함된다.

5. 구현

• 구현은 앞선 설계 결과를 바탕으로 실제 SQL을 작성하여 데이터베이스를 생성하는 단계이다.
• 예를 들어 회원 테이블 구현은 아래처럼 작성할 수 있다.

CREATE TABLE user (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(50) NOT NULL,
    password VARCHAR(500) NOT NULL,
    regdate TIMESTAMP DEFAULT NOW()
);

---

📌 트랜잭션 (Transaction)

1. 트랜잭션이란?

1. 데이터베이스에서 하나의 작업처럼 처리되는 논리적인 실행 단위를 의미한다.
2. 예를 들어 아래 두 작업은 따로 보면 각각의 작업이지만, 실제로는 하나의 작업처럼 동시에 처리되어야 한다. 이처럼 작업의 묶음을 보장해주는 것이 바로 트랜잭션이다.
   1. A 계좌에서 돈을 출금
   2. B 계좌에 돈을 입금

2. 트랜잭션의 ACID란?

트랜잭션은 안정적인 데이터 처리를 위해 4가지(원자성(Atomicity), 일관성(Consistency), 독립성(Isolation), 지속성(Durability) 특성이 만족해야하는데, 이를 ACID라고 한다.

• Atomicity (원자성)
  • 모든 작업이 전부 실행되거나, 전부 실행되지 않아야 한다.
  • 실패하면 모든 작업이 롤백된다.
• Consistency (일관성)
  • 트랜잭션 실행 전후로 데이터는 항상 일관된 상태를 유지해야 한다.
  • → 데이터 무결성 유지
• Isolation (독립성)
  • 동시에 여러 트랜잭션이 실행되더라도 서로 간섭하지 않아야 한다.
  • ex> A 사용자가 데이터 수정 중일 때 → B 사용자는 중간 상태를 보면 안 됨
• Durability (지속성)
  • 트랜잭션이 성공적으로 완료되면 그 결과는 영구적으로 저장된다.
  • ex> 시스템이 꺼지거나 장애가 발생해도 데이터는 유지되어야함

3. 트랜잭션 예시 코드

-- 트랜잭션 시작
START TRANSACTION;

-- SQL 실행
INSERT INTO user (email, name, password) 
VALUES ('test@test.com', '테스트', '1234');

-- 커밋 또는 롤백
COMMIT;     -- 변경사항 확정
ROLLBACK;   -- 변경사항 취소

1. COMMIT이란?
   • 트랜잭션에서 수행한 경 사항을 최종적으로 확정하고 데이터베이스에 영구 반영하는 명령어
2. ROLLBACK이란?
   • 트랜잭션에서 수행한 변경 사항을 취소하고 이전 상태로 되돌리는 명령어

---

📌 ALTER TABLE

• 데이터베이스를 사용하다 보면 초기에 설계한 테이블 구조를 변경해야 하는 경우가 자주 발생한다.
• 이때 사용하는 명령어가 바로 ALTER TABLE이며,
  • 컬럼 추가, 수정, 삭제부터 제약조건 설정까지 모두 가능하다.

-- 컬럼 추가
ALTER TABLE board 
ADD COLUMN updated_date TIMESTAMP NULL;

-- 컬럼 수정
ALTER TABLE board 
MODIFY COLUMN title VARCHAR(200) NOT NULL;

-- 컬럼 이름 변경 (content -> contents)
ALTER TABLE board 
CHANGE COLUMN content contents TEXT;

-- 컬럼 삭제
ALTER TABLE board 
DROP COLUMN updated_date;

-- 외래키 추가
ALTER TABLE board
ADD CONSTRAINT fk_board_user
FOREIGN KEY (user_id) REFERENCES user(user_id);

-- 인덱스 추가
ALTER TABLE board
ADD INDEX idx_title (title);

-- 제약조건 삭제
ALTER TABLE board
DROP FOREIGN KEY fk_board_user;