SQLD준비용

Posted May 25, 2025 Updated May 26, 2025

91 min read

SQLD준비용

06 SQL 기본

구분	설명	주요 명령어
DDL (Data Definition Language, 데이터 정의어)	데이터베이스 구조를 정의하거나 수정하는 언어	`CREATE`, `ALTER`, `DROP`, `TRUNCATE`
DML (Data Manipulation Language, 데이터 조작어)	데이터베이스의 데이터를 조작(입력, 수정, 삭제, 조회)하는 언어	`SELECT`, `INSERT`, `UPDATE`, `DELETE`
DCL (Data Control Language, 데이터 제어어)	데이터에 대한 접근 권한 및 보안 설정을 관리하는 언어	`GRANT`, `REVOKE`, `DENY`
TCL (Transaction Control Language, 트랜잭션 제어어)	트랜잭션의 시작과 종료, 롤백 등을 제어하는 언어	`COMMIT`, `ROLLBACK`, `SAVEPOINT`, `SET TRANSACTION`

세부 설명

DDL: 테이블, 스키마, 인덱스 등의 구조를 생성/변경/삭제. 데이터베이스 구조 정의에 사용.
DML: 데이터 자체를 다룸. 데이터를 검색하거나 수정하는 데 초점.
DCL: 사용자 권한 부여/박탈, 데이터 접근 제어에 사용.
TCL: 트랜잭션 관리로 데이터 무결성과 일관성 유지.

작업 순서

DCL -> DDL -> DML 순으로

1. DDL (Data Definition Language, 데이터 정의어)

DDL은 데이터베이스의 **구조**를 정의하거나 수정

테이블, 스키마, 인덱스 등을 만들거나 변경, 삭제할 때 사용 데이터 베이스의 개체를 생성 삭제 변경 트랜잭션 발생시키지 않음 create drop alter

**예시**:

  
CREATE TABLE students (

    id INT PRIMARY KEY,

    name VARCHAR(50),

    age INT

);

2. DML (Data Manipulation Language, 데이터 조작어)

DML은 데이터베이스 안의 **데이터**를 직접 다루는 언어

데이터를 추가, 수정, 삭제하거나 조회할 때 사용 구문이 사용되는 대상은 테이블의 행 select insert update delete insert –> 해당 열이 없다고 생각하고 입력 update –> 기존에 입력외어 있는 값 변경 –> where 절 생략 가능하나 생략할 경우 테이블의 전체 행의 내용 변경 delete from 테이블 이름 –> 생략할 시에는 전체 데이터를 삭제함

**예시**:

  
INSERT INTO students (id, name, age) VALUES (1, '홍길동', 20);

SELECT * FROM students WHERE age > 18;

집계함수

COUNT(): 행의 개수를 샘

3. DCL (Data Control Language, 데이터 제어어)

DCL은 데이터베이스에 **누가 접근할 수 있는지**를 관리하는 언어

보안과 권한 설정에 사용

grant revoke deny 권한 부여 및 뺏기

**예시**:

  
GRANT SELECT ON students TO user1;

4. TCL (Transaction Control Language, 트랜잭션 제어어)

TCL은 **트랜잭션**을 관리하는 언어

데이터의 일관성을 보장

**예시**:

  
BEGIN;

UPDATE students SET age = 21 WHERE id = 1;

COMMIT;

07 SQL 고급

제어 흐름 함수란?

제어 흐름 함수는 조건을 평가해 특정 값을 반환하거나 로직을 제어합니다. 프로그래밍 언어의 if, switch와 유사한 역할을 하며, 쿼리 결과를 동적으로 조정할 때 사용됩니다. 주요 제어 흐름 함수는 DBMS마다 다르지만, 대표적인 함수들을 아래에 정리했습니다.

주요 제어 흐름 함수

1. IF (MySQL, MariaDB)

설명: 조건이 참일 때와 거짓일 때 각각 다른 값을 반환합니다.
구문: IF(조건, 참일_때_값, 거짓일_때_값)
예시: 학생 테이블에서 나이에 따라 성인/미성년 구분.

  
SELECT name, age, IF(age >= 20, '성인', '미성년') AS age_group
FROM students;

결과 (예: students 테이블): | name | age | age_group | |——–|—–|———–| | 홍길동 | 20 | 성인 | | 김영희 | 18 | 미성년 | 연계: 이전 질문의 DATE 형식과 결합해 특정 날짜 기준으로 나이를 계산한 후 IF로 분류 가능.

2. CASE

설명: 다중 조건을 처리하며, 조건에 따라 다른 값을 반환. 모든 DBMS에서 지원되는 표준 함수.
구문:

  
CASE
    WHEN 조건1 THEN 값1
    WHEN 조건2 THEN 값2
    ELSE 기본값
END

예시: 학년별로 등급을 매기기.

  
SELECT name, grade,
       CASE
           WHEN grade = 1 THEN '1학년'
           WHEN grade = 2 THEN '2학년'
           ELSE '기타'
       END AS grade_label
FROM students;

결과: | name | grade | grade_label | |——–|——-|————-| | 홍길동 | 1 | 1학년 | | 이철수 | 2 | 2학년 | | 정수진 | 3 | 기타 | 특징: 복잡한 조건 분기가 필요할 때 유용. GROUP BY와 조합해 그룹별로 다른 기준 적용 가능.

3. COALESCE

설명: 여러 값 중 첫 번째 NULL이 아닌 값을 반환. 데이터가 없는 경우 기본값을 제공하는 데 사용.
구문: COALESCE(값1, 값2, ..., 기본값)
예시: 학생의 연락처가 없으면 ‘없음’ 반환.

  
SELECT name, COALESCE(phone, '없음') AS contact
FROM students;

결과: | name | contact | |——–|————–| | 홍길동 | 010-1234-5678 | | 김영희 | 없음 | 연계: 숫자 데이터 형식(예: DECIMAL)과 함께 사용해 결측값을 0으로 대체 가능.

4. NULLIF

설명: 두 값이 같으면 NULL을 반환, 다르면 첫 번째 값을 반환. 특정 값을 NULL로 변환할 때 유용.
구문: NULLIF(값1, 값2)
예시: 나이가 0인 경우 NULL로 처리.

  
SELECT name, NULLIF(age, 0) AS valid_age
FROM students;

결과: | name | valid_age | |——–|———–| | 홍길동 | 20 | | 김영희 | NULL | (age가 0인 경우) 용도: 잘못된 데이터(예: 0)를 NULL로 변환해 분석에서 제외.

5. IIF (SQL Server)

설명: IF와 유사하며, 조건에 따라 두 값 중 하나를 반환. SQL Server 전용.
구문: IIF(조건, 참일_때_값, 거짓일_때_값)
예시: 성적에 따라 합격/불합격 판단.

  
SELECT name, score, IIF(score >= 60, '합격', '불합격') AS result
FROM students;

결과: | name | score | result | |——–|——-|——–| | 홍길동 | 75 | 합격 | | 김영희 | 55 | 불합격 | 대안: 다른 DBMS에서는 CASE로 동일 기능 구현.

실제 사용 예시: 집계 함수 및 GROUP BY와 결합

이전 질문에서 다룬 집계 함수(COUNT, AVG)와 GROUP BY, HAVING을 제어 흐름 함수와 결합하면 강력한 분석이 가능합니다. 예를 들어, 학생 데이터를 학년별로 그룹화하고, 평균 나이에 따라 학년 상태를 분류해보겠습니다.

  
SELECT grade,
       COUNT(*) AS student_count,
       AVG(age) AS avg_age,
       CASE
           WHEN AVG(age) >= 20 THEN '성인 학년'
           ELSE '청소년 학년'
       END AS age_category
FROM students
GROUP BY grade
HAVING COUNT(*) > 1;

결과 (예: students 테이블): | grade | student_count | avg_age | age_category | |——-|—————|———|————–| | 1 | 3 | 19.0 | 청소년 학년 | | 2 | 2 | 20.5 | 성인 학년 |

설명:

GROUP BY grade: 학년별로 데이터를 그룹화.
COUNT(*)와 AVG(age): 각 학년의 학생 수와 평균 나이 계산.
CASE: 평균 나이가 20세 이상이면 ‘성인 학년’, 아니면 ‘청소년 학년’으로 분류.
HAVING: 학생 수가 1명을 초과하는 학년만 출력.

날짜/시간 데이터 형식과의 연계

이전 질문에서 다룬 날짜/시간 데이터 형식(예: DATETIME, DATE)과 결합하면 더 유용합니다. 예를 들어, 이벤트 날짜에 따라 상태를 분류:

  
SELECT event_name, event_date,
       CASE
           WHEN event_date < '2025-05-26' THEN '지난 이벤트'
           WHEN event_date = '2025-05-26' THEN '오늘 이벤트'
           ELSE '예정 이벤트'
       END AS event_status
FROM events;

주의사항

DBMS별 차이: IF는 MySQL/MariaDB 전용, IIF는 SQL Server 전용. CASE와 COALESCE는 대부분의 DBMS에서 표준으로 지원.
성능: 복잡한 CASE문은 쿼리 성능에 영향을 줄 수 있으니, 불필요한 조건은 피하세요.
NULL 처리: COALESCE와 NULLIF를 활용해 NULL 값을 적절히 관리하세요.
SELECT 절 제한: GROUP BY와 사용할 때는 SELECT에 포함된 열이 그룹화 기준이거나 집계 함수로 처리되어야 합니다.

데이터베이스의 문자열 함수

함수	설명	예시	주요 DBMS	특징
CONCAT	여러 문자열을 하나로 연결	`CONCAT(first_name, ' ', last_name)` → `홍 길동`	MySQL, PostgreSQL, SQL Server	NULL 처리 방식 DBMS마다 다름
CONCAT_WS	구분자를 사용해 문자열 연결	`CONCAT_WS('-', city, country)` → `서울-한국`	MySQL, PostgreSQL	NULL 무시 가능
SUBSTRING / SUBSTR	문자열의 일부 추출	`SUBSTRING(email, 1, 5)` → `user1`	MySQL, PostgreSQL, SQL Server, Oracle	시작 위치와 길이 지정
LEFT	문자열의 왼쪽부터 지정된 길이만큼 추출	`LEFT(phone, 3)` → `010`	MySQL, PostgreSQL, SQL Server	전화번호 앞부분 추출
RIGHT	문자열의 오른쪽부터 지정된 길이만큼 추출	`RIGHT(email, 3)` → `com`	MySQL, PostgreSQL, SQL Server	이메일 도메인 추출
LENGTH / LEN	문자열의 길이 반환	`LENGTH(name)` → `5`	MySQL (`LENGTH`), SQL Server (`LEN`), PostgreSQL (`LENGTH`)	바이트 vs 문자 수 계산 다름
UPPER	문자열을 대문자로 변환	`UPPER(name)` → `HONG GILDONG`	MySQL, PostgreSQL, SQL Server, Oracle	검색 시 대소문자 무시
LOWER	문자열을 소문자로 변환	`LOWER(email)` → `user@example.com`	MySQL, PostgreSQL, SQL Server, Oracle	데이터 정규화
TRIM	문자열의 양쪽 공백 제거	`TRIM(' hello ')` → `hello`	MySQL, PostgreSQL, SQL Server, Oracle	`LTRIM`, `RTRIM`도 지원
REPLACE	특정 패턴을 다른 문자열로 교체	`REPLACE(phone, '-', '')` → `01012345678`	MySQL, PostgreSQL, SQL Server, Oracle	데이터 정제에 유용

예시

CONCAT: SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM students; → 홍 길동
SUBSTRING: SELECT SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS username FROM users; → user1
REPLACE: SELECT REPLACE(phone, '-', '') AS clean_phone FROM contacts; → 01012345678

데이터베이스의 조인(Join)

조인(Join)은 데이터베이스에서 두 개 이상의 테이블을 연결하여 데이터를 결합하는 강력한 기능 테이블 간의 공통 열(예: 키)을 기준으로 데이터를 합쳐, 한 번의 쿼리로 여러 테이블의 정보를 조회할 수 있습니다. 이전 질문들에서 다룬 집계 함수, GROUP BY, HAVING, 숫자/날짜 데이터 형식, 제어 흐름 함수, 문자열 함수와 연계해 실용적인 예시를 추가했습니다.

조인이란?

조인은 서로 다른 테이블의 데이터를 특정 조건(보통 열 값의 일치)에 따라 결합하는 작업입니다. 예를 들어, 학생 정보 테이블과 성적 테이블이 있을 때, 학생 이름과 그 학생의 성적을 함께 조회하려면 조인을 사용합니다.

핵심 요소:

키(Key): 테이블 간 연결을 위한 공통 열 (예: 학생 ID).
조인 조건: 테이블을 어떻게 결합할지 정의 (예: ON students.id = grades.student_id).
결과: 두 테이블의 데이터를 결합한 새로운 결과 집합.

주요 조인 유형

아래는 주요 조인 유형과 그 특징입니다.

1. INNER JOIN

설명: 두 테이블에서 조인 조건을 만족하는 행만 반환합니다. 공통 데이터만 포함.
용도: 양쪽 테이블에 모두 존재하는 데이터만 필요할 때.
예시:

  
SELECT students.name, grades.score
FROM students
INNER JOIN grades ON students.id = grades.student_id;

결과 (예: students와 grades 테이블): | name | score | |——–|——-| | 홍길동 | 85 | | 김영희 | 90 | 특징: 조건에 맞지 않는 행(예: 성적이 없는 학생)은 제외.

2. LEFT OUTER JOIN (LEFT JOIN)

설명: 왼쪽 테이블의 모든 행과 오른쪽 테이블에서 조건을 만족하는 행을 반환. 오른쪽 테이블에 매칭되는 데이터가 없으면 NULL로 채움.
용도: 왼쪽 테이블의 모든 데이터를 유지하고 싶을 때.
예시:

  
SELECT students.name, grades.score
FROM students
LEFT JOIN grades ON students.id = grades.student_id;

결과: | name | score | |——–|——-| | 홍길동 | 85 | | 김영희 | 90 | | 이철수 | NULL | (성적이 없는 학생) 특징: 왼쪽 테이블(students)의 모든 행 포함.

3. RIGHT OUTER JOIN (RIGHT JOIN)

설명: 오른쪽 테이블의 모든 행과 왼쪽 테이블에서 조건을 만족하는 행을 반환. 왼쪽 테이블에 매칭 데이터가 없으면 NULL.
용도: 오른쪽 테이블의 모든 데이터를 유지할 때.
예시:

  
SELECT students.name, grades.score
FROM students
RIGHT JOIN grades ON students.id = grades.student_id;

결과: | name | score | |——–|——-| | 홍길동 | 85 | | 김영희 | 90 | | NULL | 70 | (학생 정보 없는 성적) 특징: 오른쪽 테이블(grades)의 모든 행 포함.

4. FULL OUTER JOIN

설명: 양쪽 테이블의 모든 행을 반환. 매칭되지 않는 데이터는 NULL로 채움.
용도: 양쪽 테이블의 모든 데이터를 확인하고 싶을 때.
예시:

  
SELECT students.name, grades.score
FROM students
FULL JOIN grades ON students.id = grades.student_id;

결과: | name | score | |——–|——-| | 홍길동 | 85 | | 김영희 | 90 | | 이철수 | NULL | | NULL | 70 | 특징: 양쪽 테이블의 모든 행 포함. 일부 DBMS(예: MySQL)는 직접 지원 안 함.

5. CROSS JOIN

설명: 두 테이블의 모든 행을 조합(카티션 곱). 조인 조건 없이 모든 경우의 수 반환.
용도: 모든 가능한 조합이 필요할 때 (드물게 사용).
예시:

  
SELECT students.name, courses.course_name
FROM students
CROSS JOIN courses;

결과 (학생 3명, 강의 2개라면 3×2=6행): | name | course_name | |——–|————-| | 홍길동 | 수학 | | 홍길동 | 영어 | | 김영희 | 수학 | | … | … | 특징: 결과 행 수가 많아질 수 있으니 주의.

이전 질문과의 연계

조인은 이전에 다룬 기능들과 조합하면 더 강력합니다.

1. 집계 함수 및 GROUP BY

학생과 성적 테이블을 조인해 학년별 평균 성적을 계산:

  
SELECT students.grade, AVG(grades.score) AS avg_score
FROM students
INNER JOIN grades ON students.id = grades.student_id
GROUP BY students.grade
HAVING AVG(grades.score) >= 80;

결과: | grade | avg_score | |——-|———–| | 1 | 85.5 | | 2 | 90.0 | 설명: INNER JOIN으로 학생과 성적을 연결, GROUP BY로 학년별 그룹화, HAVING으로 평균 80점 이상 학년만 출력.

2. 문자열 함수

학생 이름과 이메일을 연결해 출력:

  
SELECT CONCAT(students.first_name, ' ', students.last_name) AS full_name,
       LOWER(students.email) AS email
FROM students
INNER JOIN grades ON students.id = grades.student_id
WHERE grades.score >= 80;

결과: | full_name | email | |———–|——————-| | 홍 길동 | hong@example.com | | 김 영희 | kim@example.com |

3. 날짜/시간 데이터 형식

이벤트와 참석자 테이블을 조인해 날짜별 참석자 조회:

  
SELECT events.event_name, events.event_date, COUNT(attendees.id) AS attendee_count
FROM events
LEFT JOIN attendees ON events.id = attendees.event_id
WHERE events.event_date >= '2025-05-26'
GROUP BY events.event_name, events.event_date;

결과: | event_name | event_date | attendee_count | |—————–|————-|—————-| | Tech Conference | 2025-05-26 | 10 | | AI Workshop | 2025-05-27 | 5 |

4. 제어 흐름 함수

성적에 따라 등급 부여:

  
SELECT students.name, grades.score,
       CASE
           WHEN grades.score >= 90 THEN 'A'
           WHEN grades.score >= 80 THEN 'B'
           ELSE 'C'
       END AS grade_level
FROM students
INNER JOIN grades ON students.id = grades.student_id;

결과: | name | score | grade_level | |——–|——-|————-| | 홍길동 | 85 | B | | 김영희 | 95 | A |

주의사항

성능: 조인은 데이터가 많을수록 계산 비용이 크므로, 적절한 인덱스를 설정해 최적화하세요.
조인 조건: ON 절에서 명확한 키를 사용해 잘못된 데이터 결합 방지.
NULL 처리: LEFT/RIGHT/FULL JOIN에서 NULL 값이 반환될 수 있으니, COALESCE 같은 함수로 처리.
DBMS 차이: MySQL은 FULL JOIN을 직접 지원하지 않으므로 LEFT JOIN과 RIGHT JOIN을 UNION으로 대체 가능.

마무리

조인(INNER, LEFT, RIGHT, FULL, CROSS)은 데이터베이스에서 여러 테이블의 데이터를 결합해 풍부한 정보를 조회하는 핵심 기능입니다. 집계 함수, 문자열 함수, 날짜/시간 데이터, 제어 흐름 함수와 함께 사용하면 복잡한 데이터 분석도 쉽게 처리할 수 있습니다. 예를 들어, 학생과 성적 데이터를 조인해 학년별 통계를 내거나, 이벤트와 참석자를 연결해 날짜별 분석을 할 수 있죠.

08 테이블, 뷰, 테이블 스페이스

뷰(View)는 데이터베이스에서 테이블의 데이터를 기반으로 생성된 가상의 테이블 실제 데이터를 저장하지 않고, 쿼리의 결과를 동적으로 보여주는 논리적 객체로, 데이터 조회를 간편하게 하고 보안을 강화하는 데 유용합니다.

뷰란?

뷰는 특정 쿼리의 결과를 테이블처럼 사용하는 객체입니다. 물리적으로 데이터를 저장하지 않고, 호출될 때마다 정의된 쿼리를 실행해 결과를 보여줍니다. 예를 들어, 학생 테이블과 성적 테이블을 조인한 결과를 자주 조회한다면, 이 쿼리를 뷰로 만들어 간단히 재사용할 수 있습니다.

핵심 특징:

가상 테이블: 실제 데이터는 원본 테이블에 저장되며, 뷰는 쿼리 결과를 보여줄 뿐.
재사용성: 복잡한 쿼리를 간소화해 반복 사용 가능.
보안: 특정 열이나 행만 노출해 데이터 접근 제어.
업데이트 가능성: 일부 조건 충족 시 뷰를 통해 데이터 수정 가능(제한적).

뷰의 주요 용도

복잡한 쿼리 간소화: 자주 사용하는 조인이나 집계 쿼리를 뷰로 저장.
데이터 보안: 민감한 데이터를 숨기고 필요한 부분만 공개.
데이터 추상화: 사용자에게 데이터 구조를 단순화해 제공.
일관된 데이터 제공: 동일한 쿼리 결과를 여러 사용자/애플리케이션에 제공.

뷰 생성 및 사용

1. 뷰 생성 (CREATE VIEW)

뷰는 CREATE VIEW 문으로 정의
구문:

  
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
[WHERE condition | JOIN | GROUP BY ...];

예시: 학생과 성적 테이블을 조인해 성적이 80점 이상인 학생 정보를 뷰로 생성.

  
CREATE VIEW high_score_students AS
SELECT s.name, s.grade, g.score
FROM students s
INNER JOIN grades g ON s.id = g.student_id
WHERE g.score >= 80;

2. 뷰 조회

뷰는 일반 테이블처럼 조회 가능.

  
SELECT * FROM high_score_students;

결과: | name | grade | score | |——–|——-|——-| | 홍길동 | 1 | 85 | | 김영희 | 2 | 90 |

3. 뷰 수정 (UPDATE, INSERT, DELETE)

뷰를 통해 원본 테이블의 데이터를 수정할 수 있지만, 제한이 있습니다(단일 테이블 기반, 집계 함수 미포함 등). 예시:

  
UPDATE high_score_students
SET score = 88
WHERE name = '홍길동';

4. 뷰 삭제

  
DROP VIEW high_score_students;

1. 조인과 뷰

학생과 성적 테이블을 조인한 결과를 뷰로 저장해 자주 사용.

  
CREATE VIEW student_grades AS
SELECT s.name, s.grade, g.score,
       CONCAT(s.first_name, ' ', s.last_name) AS full_name -- 문자열 함수 사용
FROM students s
INNER JOIN grades g ON s.id = g.student_id;

사용:

  
SELECT full_name, score FROM student_grades WHERE grade = 1;

2. 집계 함수 및 GROUP BY

학년별 평균 성적을 계산하는 뷰.

  
CREATE VIEW grade_summary AS
SELECT s.grade, COUNT(*) AS student_count, AVG(g.score) AS avg_score
FROM students s
INNER JOIN grades g ON s.id = g.student_id
GROUP BY s.grade
HAVING AVG(g.score) >= 80;

조회:

  
SELECT * FROM grade_summary;

결과: | grade | student_count | avg_score | |——-|—————|———–| | 1 | 3 | 85.5 | | 2 | 2 | 90.0 |

3. 문자열 함수

이메일 도메인을 추출한 뷰 생성.

  
CREATE VIEW user_emails AS
SELECT name, LOWER(email) AS email, RIGHT(email, 3) AS domain
FROM users;

결과: | name | email | domain | |——–|——————-|——–| | 홍길동 | hong@example.com | com |

4. 제어 흐름 함수

성적 등급을 부여하는 뷰.

  
CREATE VIEW graded_students AS
SELECT s.name, g.score,
       CASE
           WHEN g.score >= 90 THEN 'A'
           WHEN g.score >= 80 THEN 'B'
           ELSE 'C'
       END AS grade_level
FROM students s
INNER JOIN grades g ON s.id = g.student_id;

결과: | name | score | grade_level | |——–|——-|————-| | 홍길동 | 85 | B | | 김영희 | 95 | A |

5. 날짜/시간 데이터 형식

이벤트 날짜별 참석자 수를 뷰로 저장.

  
CREATE VIEW event_attendees AS
SELECT e.event_name, e.event_date, COUNT(a.id) AS attendee_count
FROM events e
LEFT JOIN attendees a ON e.id = a.event_id
WHERE e.event_date >= '2025-05-26'
GROUP BY e.event_name, e.event_date;

결과 (2025-05-26 기준): | event_name | event_date | attendee_count | |—————–|————-|—————-| | Tech Conference | 2025-05-26 | 10 |

뷰의 장점

쿼리 간소화: 복잡한 조인/집계 쿼리를 뷰로 저장해 쉽게 재사용.
보안 강화: 민감한 열(예: 주민번호)을 제외한 데이터만 뷰로 제공.
일관성: 동일한 쿼리 결과를 여러 사용자에게 제공.
유지보수 용이: 쿼리 로직을 뷰에 중앙화해 수정 간편.

주의사항

성능: 뷰는 쿼리 실행 결과를 보여주므로, 복잡한 뷰는 성능 저하를 유발할 수 있음. 인덱스 활용 필요.
업데이트 제한: 집계 함수, GROUP BY, 다중 테이블 조인 등이 포함된 뷰는 수정 불가.
DBMS 차이: 뷰의 기능(예: materialized view)은 DBMS마다 다름. MySQL은 기본 뷰만 지원, PostgreSQL은 물리적 저장 뷰 지원.
의존성: 원본 테이블이 삭제/변경되면 뷰도 영향을 받음.

마무리

뷰는 데이터베이스에서 복잡한 쿼리를 간소화하고, 데이터를 안전하게 관리하며, 일관된 결과를 제공하는 강력한 도구입니다. 조인, 문자열 함수, 제어 흐름 함수, 집계 함수, 날짜/시간 데이터 형식과 조합하면 데이터를 효율적으로 가공하고 분석할 수 있습니다. 예를 들어, 학년별 성적 통계를 뷰로 저장해 빠르게 조회하거나, 이벤트 데이터를 날짜별로 정리해 관리할 수 있죠.

궁금한 점이나 특정 예시, 혹은 시각화(예: 뷰 데이터를 기반으로 학년별 평균 성적 차트)가 필요하면 말씀해주세요! 차트 요청 시 바로 생성하겠습니다.

09 인덱스, 사용자 관리

인덱스(Index)는 데이터베이스에서 데이터 검색 속도를 높이기 위해 사용되는 구조입니다. 책의 색인처럼, 테이블의 특정 열에 대한 빠른 접근 경로를 제공하여 쿼리 성능을 개선합니다. 이 포스트에서는 인덱스의 개념, 종류, 장단점, 그리고 이전 질문들(뷰, 조인, 문자열 함수, 제어 흐름 함수, 집계 함수, 데이터 형식 등)과 연계한 실용적인 예시를 통해 알기 쉽게 설명하겠습니다.

인덱스란?

인덱스는 테이블의 특정 열(또는 열들의 조합)에 대해 데이터베이스가 빠르게 검색할 수 있도록 정리된 데이터 구조입니다. 주로 B-트리나 해시 테이블 같은 자료 구조를 사용하며, 테이블 데이터 자체를 복사하지 않고 참조 정보(포인터)를 저장합니다.

비유: 책에서 특정 주제를 찾으려면 전체 페이지를 읽는 대신 색인을 보면 빠르게 페이지를 찾을 수 있죠. 인덱스는 데이터베이스에서 이런 역할을 합니다.

핵심 요소:

대상 열: 인덱스를 생성할 열(예: id, name).
목적: SELECT, WHERE, JOIN, ORDER BY 등에서 검색 속도 향상.
저장 위치: DBMS 내부에 별도의 데이터 구조로 저장.

인덱스의 주요 종류

1. 기본 인덱스 (Primary Index)

설명: 기본 키(Primary Key)에 자동 생성되는 인덱스. 고유한 값을 보장.

예시: students 테이블의 id 열에 기본 키 설정.

  
CREATE TABLE students (
    id INT PRIMARY KEY, -- 자동으로 기본 인덱스 생성
    name VARCHAR(50)
);

특징: 중복 불가, NULL 불가.

2. 고유 인덱스 (Unique Index)

설명: 열의 값이 고유해야 함을 보장. 기본 키와 달리 NULL 허용 가능.

예시: 이메일 열에 고유 인덱스 생성.

  
CREATE UNIQUE INDEX idx_email ON users(email);

특징: 중복 값 방지, 검색 속도 향상.

3. 비고유 인덱스 (Non-Unique Index)

설명: 중복 값을 허용하는 일반 인덱스. 자주 검색되는 열에 사용.

예시: 학생의 학년 열에 인덱스 생성.

  
CREATE INDEX idx_grade ON students(grade);

특징: WHERE, JOIN 등에서 성능 개선.

4. 복합 인덱스 (Composite Index)

설명: 두 개 이상의 열을 조합한 인덱스. 다중 조건 검색에 유용.

예시: 이름과 학년으로 자주 검색.

  
CREATE INDEX idx_name_grade ON students(name, grade);

특징: 열 순서 중요(쿼리 조건과 일치해야 효율적).

5. 클러스터드 인덱스 (Clustered Index)

설명: 테이블의 물리적 데이터 순서를 인덱스 순서에 맞춰 정렬. 테이블당 하나만 가능.
예시: 기본 키는 대개 클러스터드 인덱스.
특징: 데이터 자체를 재정렬, 검색 매우 빠름.

6. 비클러스터드 인덱스 (Non-Clustered Index)

설명: 데이터와 별도로 인덱스 저장. 여러 개 생성 가능.
예시: idx_email 같은 일반 인덱스는 비클러스터드.
특징: 추가 저장 공간 필요, 클러스터드보다 느릴 수 있음.

인덱스의 장점

검색 속도 향상: WHERE, JOIN, ORDER BY, GROUP BY에서 빠른 조회.
효율적 데이터 접근: 특히 큰 테이블에서 효과적.
쿼리 최적화: DBMS의 쿼리 옵티마이저가 인덱스를 활용해 최적 경로 선택.

인덱스의 단점

저장 공간 증가: 인덱스는 별도의 데이터 구조로 저장되므로 디스크 공간 사용.
쓰기 성능 저하: INSERT, UPDATE, DELETE 시 인덱스도 갱신되므로 속도 저하.
관리 부담: 인덱스가 많아지면 유지보수 복잡성 증가.

이전 질문과의 연계

인덱스는 이전에 다룬 기능들과 함께 사용하면 쿼리 성능을 크게 개선할 수 있습니다.

1. 조인과 인덱스

조인 성능을 높이기 위해 조인 조건 열에 인덱스 생성.

  
CREATE INDEX idx_student_id ON grades(student_id);
SELECT s.name, g.score
FROM students s
INNER JOIN grades g ON s.id = g.student_id
WHERE g.score >= 80;

설명: grades.student_id에 인덱스를 추가해 조인 속도 향상.

2. 뷰와 인덱스

뷰는 가상 테이블이므로 직접 인덱스를 생성할 수 없지만, 원본 테이블의 인덱스를 활용.

  
CREATE VIEW high_score_students AS
SELECT s.name, g.score
FROM students s
INNER JOIN grades g ON s.id = g.student_id
WHERE g.score >= 80;

팁: s.id와 g.student_id에 인덱스가 있으면 뷰 조회 속도 빨라짐.

3. 집계 함수 및 GROUP BY

GROUP BY와 HAVING에서 자주 사용되는 열에 인덱스 추가.

  
CREATE INDEX idx_grade ON students(grade);
SELECT s.grade, COUNT(*) AS student_count, AVG(g.score) AS avg_score
FROM students s
INNER JOIN grades g ON s.id = g.student_id
GROUP BY s.grade
HAVING AVG(g.score) >= 80;

설명: grade 열 인덱스로 그룹화 성능 개선.

4. 문자열 함수

문자열 함수를 사용하는 쿼리에서 인덱스 활용 가능.

  
CREATE INDEX idx_email ON users(email);
SELECT LOWER(email) AS normalized_email
FROM users
WHERE email = 'HONG@EXAMPLE.COM';

주의: LOWER(email) 같은 함수 적용 시 인덱스 사용 안 될 수 있음. 함수 기반 인덱스(DBMS별 지원) 고려.

5. 날짜/시간 데이터 형식

날짜 조건이 자주 사용되면 인덱스 추가.

  
CREATE INDEX idx_event_date ON events(event_date);
SELECT event_name, event_date
FROM events
WHERE event_date >= '2025-05-26';

설명: event_date 인덱스로 날짜 범위 검색 속도 향상.

6. 제어 흐름 함수

CASE 등 제어 흐름 함수와 함께 사용 시 조건 열에 인덱스 적용.

  
CREATE INDEX idx_score ON grades(score);
SELECT s.name, g.score,
       CASE
           WHEN g.score >= 90 THEN 'A'
           WHEN g.score >= 80 THEN 'B'
           ELSE 'C'
       END AS grade_level
FROM students s
INNER JOIN grades g ON s.id = g.student_id
WHERE g.score >= 80;

실제 예시

students와 grades 테이블이 있다고 가정:

  
CREATE TABLE students (
    id INT PRIMARY KEY, -- 기본 인덱스 자동 생성
    name VARCHAR(50),
    grade INT
);
CREATE TABLE grades (
    student_id INT,
    score INT
);
CREATE INDEX idx_student_id ON grades(student_id); -- 조인용 인덱스
CREATE INDEX idx_score ON grades(score); -- 검색용 인덱스

쿼리:

  
SELECT s.name, g.score
FROM students s
INNER JOIN grades g ON s.id = g.student_id
WHERE g.score >= 80
ORDER BY g.score DESC;

idx_student_id: 조인 성능 개선.
idx_score: WHERE와 ORDER BY 성능 향상.

주의사항

인덱스 선택: 자주 검색/조인/정렬되는 열에만 인덱스 생성. 불필요한 인덱스는 공간 낭비.
쓰기 부담: INSERT, UPDATE, DELETE가 빈번한 테이블은 인덱스 최소화.
함수와 인덱스: WHERE LOWER(email) = 'value'처럼 함수 사용 시 인덱스 미작동 가능. 함수 기반 인덱스 고려.
DBMS별 차이: MySQL은 B-트리 중심, PostgreSQL은 GiST/GIN 등 추가 지원. 문서 확인 필요.
유지보수: 테이블 구조 변경 시 인덱스도 갱신/재생성 필요.

데이터베이스에서 사용자 관리는 보안과 데이터 무결성을 유지하는 데 핵심적인 역할을 합니다. 사용자 관리에는 사용자 계정 생성, 권한 부여, 접근 제어, 그리고 계정 유지보수가 포함됩니다.

사용자 관리의 핵심 개념

사용자 관리는 데이터베이스에 누가 접근할 수 있는지, 어떤 작업을 수행할 수 있는지를 제어하는 과정입니다. 주요 요소는 다음과 같습니다:

사용자 계정: 데이터베이스에 접근하는 개별 계정(예: user1).
권한(Privilege): 사용자가 수행할 수 있는 작업(예: SELECT, INSERT, DELETE).
역할(Role): 권한을 그룹화한 논리적 단위로, 여러 사용자에게 동일한 권한 부여.
인증(Authentication): 사용자가 올바른 계정인지 확인(예: 비밀번호, 인증서).
보안: 민감한 데이터 접근 제한 및 모니터링.

사용자 관리 방법

1. 사용자 계정 생성

사용자 계정을 만들어 데이터베이스 접근을 허용합니다.
구문 (MySQL/PostgreSQL 예시):

  
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password123'; -- MySQL
CREATE USER user1 WITH PASSWORD 'password123'; -- PostgreSQL

설명: user1 계정을 생성하고 비밀번호 설정. MySQL은 호스트(localhost) 지정 가능.
예시: 웹 애플리케이션에서 데이터 조회용 계정 생성.
1 CREATE USER 'app_user'@'%' IDENTIFIED BY 'securepass';

2. 권한 부여 (GRANT)

사용자에게 특정 작업을 수행할 권한을 부여합니다.
구문:

  
GRANT privilege ON database.table TO 'user'@'host'; -- MySQL
GRANT privilege ON table_name TO user; -- PostgreSQL

예시: students 테이블 조회 권한 부여.

  
GRANT SELECT ON school.students TO 'user1'@'localhost'; -- MySQL
GRANT SELECT ON students TO user1; -- PostgreSQL

주요 권한:

SELECT: 데이터 조회.
INSERT, UPDATE, DELETE: 데이터 조작.
CREATE, DROP: 테이블/뷰 생성 및 삭제.
ALL PRIVILEGES: 모든 권한 부여.

연계 예시 (이전 질문: 뷰):

  
CREATE VIEW high_score_students AS
SELECT name, score FROM students s
INNER JOIN grades g ON s.id = g.student_id
WHERE score >= 80;
GRANT SELECT ON school.high_score_students TO 'app_user'@'%';

설명: app_user에게 고득점 학생 뷰 조회 권한만 부여해 보안 강화.

3. 권한 회수 (REVOKE)

부여된 권한을 취소합니다.
구문:

  
REVOKE privilege ON database.table FROM 'user'@'host'; -- MySQL
REVOKE privilege ON table_name FROM user; -- PostgreSQL

예시: user1의 삭제 권한 회수.

  
REVOKE DELETE ON school.students FROM 'user1'@'localhost';

4. 역할 기반 접근 제어 (Role-Based Access Control)

역할을 만들어 여러 사용자에게 동일한 권한을 효율적으로 부여합니다.
구문 (PostgreSQL 예시):

  
CREATE ROLE read_only;
GRANT SELECT ON students, grades TO read_only;
GRANT read_only TO user1, user2;

예시: 읽기 전용 역할로 팀원 관리.

  
GRANT read_only TO 'app_user'@'%'; -- MySQL

설명: app_user에게 읽기 전용 권한 부여.

5. 사용자 계정 수정 및 삭제

비밀번호 변경:

  
ALTER USER 'user1'@'localhost' IDENTIFIED BY 'newpassword'; -- MySQL
ALTER USER user1 WITH PASSWORD 'newpassword'; -- PostgreSQL

계정 삭제:

  
DROP USER 'user1'@'localhost'; -- MySQL
DROP USER user1; -- PostgreSQL

6. 보안 및 모니터링

강력한 비밀번호: 복잡한 비밀번호 설정 및 주기적 변경.
최소 권한 원칙: 필요한 최소한의 권한만 부여.
접속 제한: MySQL의 호스트 제한(@'localhost' vs @'%')이나 IP 화이트리스트 사용.

로그 감사: 사용자 활동 로깅(예: PostgreSQL의 log_connections).

  
-- PostgreSQL: 접속 로그 활성화
SET log_connections = on;

1. 뷰와 사용자 관리

뷰를 활용해 민감한 데이터 숨기고, 사용자에게 제한된 데이터만 제공.

  
CREATE VIEW student_summary AS
SELECT name, grade, CONCAT(LEFT(email, 3), '***') AS masked_email -- 문자열 함수
FROM students;
GRANT SELECT ON student_summary TO 'public_user'@'%';

설명: public_user에게 이메일 일부 마스킹된 뷰만 조회 허용.

2. 조인과 권한

조인 쿼리가 포함된 뷰에 특정 사용자만 접근 허용.

  
CREATE VIEW student_grades AS
SELECT s.name, g.score
FROM students s
INNER JOIN grades g ON s.id = g.student_id;
GRANT SELECT ON student_grades TO 'app_user'@'%';

3. 집계 함수 및 GROUP BY

집계 결과를 뷰로 만든 후 권한 부여.

  
CREATE VIEW grade_stats AS
SELECT s.grade, AVG(g.score) AS avg_score
FROM students s
INNER JOIN grades g ON s.id = g.student_id
GROUP BY s.grade
HAVING AVG(g.score) >= 80;
GRANT SELECT ON grade_stats TO 'analyst'@'%';

설명: 분석가(analyst)에게 학년별 평균 성적 뷰 조회 권한 부여.

4. 문자열 함수

문자열 함수로 데이터 정제 후 사용자에게 제공.

  
CREATE VIEW clean_contacts AS
SELECT name, REPLACE(phone, '-', '') AS clean_phone -- 문자열 함수
FROM contacts;
GRANT SELECT ON clean_contacts TO 'support_team'@'%';

5. 날짜/시간 데이터 형식

특정 날짜 조건의 데이터를 사용자에게 제한.

  
CREATE VIEW recent_events AS
SELECT event_name, event_date
FROM events
WHERE event_date >= '2025-05-26'; -- 오늘 기준
GRANT SELECT ON recent_events TO 'event_manager'@'%';

6. 제어 흐름 함수

사용자별로 다른 데이터 표시.

  
CREATE VIEW student_status AS
SELECT name, score,
       CASE WHEN score >= 80 THEN 'Pass' ELSE 'Fail' END AS status -- 제어 흐름 함수
FROM grades;
GRANT SELECT ON student_status TO 'teacher'@'%';

7. 인덱스와 성능

사용자 쿼리 성능을 위해 인덱스 활용.

  
CREATE INDEX idx_event_date ON events(event_date);
GRANT SELECT ON events TO 'event_manager'@'%';

설명: event_manager가 날짜 기반 쿼리를 빠르게 실행하도록 인덱스 추가.

주의사항

최소 권한 원칙: 사용자에게 필요한 권한만 부여해 보안 위험 최소화.
비밀번호 관리: 강력한 비밀번호 사용, 주기적 갱신, 암호화 저장.
역할 활용: 개별 사용자마다 권한 설정 대신 역할(Role)로 관리해 효율성 증대.
DBMS 차이: MySQL은 호스트 기반 사용자 관리, PostgreSQL은 역할 중심. SQL Server는 Windows 인증 통합 가능.
감사 로그: 민감한 데이터 접근 시 사용자 활동 모니터링 필수.

실제 예시

students와 grades 테이블 관리:

  
-- 분석가 계정 생성
CREATE USER 'analyst'@'localhost' IDENTIFIED BY 'secure123';
-- 읽기 전용 역할 생성
CREATE ROLE read_only;
GRANT SELECT ON school.* TO read_only;
-- 역할 부여
GRANT read_only TO 'analyst'@'localhost';
-- 고득점 학생 뷰 생성 및 권한 부여
CREATE VIEW high_score_students AS
SELECT s.name, g.score
FROM students s
INNER JOIN grades g ON s.id = g.student_id
WHERE g.score >= 80;
GRANT SELECT ON high_score_students TO 'analyst'@'localhost';

10 JDBC 프로그래밍

JDBC (Java Database Connectivity)는 자바 애플리케이션에서 데이터베이스에 접근하고 조작할 수 있게 해주는 표준 API입니다. 이를 통해 자바 프로그램은 MySQL, PostgreSQL, Oracle, SQL Server 등 다양한 데이터베이스와 연결하여 데이터를 쿼리하고 관리할 수 있습니다.

JDBC란?

JDBC는 자바로 작성된 애플리케이션이 데이터베이스와 상호작용할 수 있도록 설계된 API로, SQL 쿼리를 실행하고 결과를 처리하는 표준화된 방법을 제공합니다. 플랫폼 독립적이며, 다양한 DBMS와 호환됩니다.

핵심 역할:

데이터베이스 연결 설정.
SQL 쿼리 실행(조회, 삽입, 갱신, 삭제 등).
결과 처리 및 트랜잭션 관리.

JDBC의 주요 구성 요소

1. JDBC 드라이버

설명: 특정 DBMS와 통신하기 위한 소프트웨어. 각 DBMS(MySQL, PostgreSQL 등)에 맞는 드라이버 필요.
종류:
- Type 1: JDBC-ODBC 브리지 (구형, 성능 낮음).
- Type 2: 네이티브 API 드라이버 (DBMS별 라이브러리 필요).
- Type 3: 네트워크 프로토콜 드라이버 (중간 서버 경유).
- Type 4: 순수 자바 드라이버 (가장 일반적, 직접 DBMS 연결).
특징: Type 4 드라이버가 가장 널리 사용되며, DBMS별로 다운로드 필요(예: MySQL Connector/J).

2. DriverManager

설명: JDBC 드라이버를 관리하고 데이터베이스 연결을 생성하는 클래스.
기능: 드라이버 로드 및 Connection 객체 생성.

3. Connection

설명: 데이터베이스와의 연결 세션을 나타내는 객체.
기능: SQL 실행, 트랜잭션 관리, 데이터베이스 메타데이터 접근.

4. Statement

설명: SQL 쿼리를 실행하는 객체.
종류:
- Statement: 정적 쿼리 실행.
- PreparedStatement: 동적/파라미터화된 쿼리 실행(보안성 우수).
- CallableStatement: 저장 프로시저 호출.
특징: PreparedStatement는 SQL 인젝션 방지 및 성능 최적화에 유리.

5. ResultSet

설명: SELECT 쿼리 결과를 저장하는 객체.
기능: 행과 열 단위로 데이터 탐색, 스크롤 방향/업데이트 가능 여부 설정.

JDBC 동작 방식

JDBC는 다음 단계를 통해 데이터베이스와 상호작용합니다:

드라이버 로드: DBMS별 JDBC 드라이버를 로드.
연결 설정: DriverManager를 통해 데이터베이스 연결.
쿼리 실행: Statement 또는 PreparedStatement로 SQL 실행.
결과 처리: ResultSet으로 조회 결과 처리.
자원 해제: 연결 및 객체 닫기.

JDBC와 이전 질문 연계

JDBC는 이전에 다룬 데이터베이스 기능과 직접 연계됩니다:

사용자 관리: JDBC로 연결 시 사용자 계정과 비밀번호로 인증. 권한 부여(GRANT)된 작업만 수행 가능.
인덱스: JDBC 쿼리 성능은 인덱스에 의존. 예: WHERE 조건 열에 인덱스가 있으면 조회 속도 향상.
뷰: JDBC로 뷰를 테이블처럼 조회 가능.
조인: 복잡한 조인 쿼리를 JDBC로 실행, PreparedStatement로 안전하게 처리.
문자열 함수: JDBC 쿼리에서 CONCAT, LOWER 등을 사용해 데이터 가공.
제어 흐름 함수: CASE 등을 JDBC 쿼리에 포함해 동적 결과 생성.
집계 함수 및 GROUP BY: JDBC로 집계 쿼리 실행, 결과 데이터를 자바 객체로 매핑.
데이터 형식: 숫자(INT, DECIMAL), 날짜(DATETIME) 데이터를 ResultSet에서 적절히 처리(예: getInt(), getTimestamp()).

주요 사용 시 고려사항

보안:
- PreparedStatement를 사용해 SQL 인젝션 방지.
- 비밀번호는 암호화된 설정 파일이나 환경 변수로 관리.
성능:
- 연결 풀(Connection Pool, 예: HikariCP)로 연결 관리 최적화.
- 배치 처리(addBatch)로 대량 데이터 작업 효율화.
자원 관리:
- try-with-resources로 Connection, Statement, ResultSet 자동 닫기.
DBMS 호환성:
- 드라이버와 SQL 문법은 DBMS마다 다를 수 있음(MySQL vs PostgreSQL).
트랜잭션 관리:
- Connection.setAutoCommit(false)로 트랜잭션 제어.
- commit() 또는 rollback()으로 데이터 일관성 유지.

JDBC의 장점

표준화: 모든 DBMS에 동일한 API 사용.
유연성: 다양한 SQL 쿼리(조인, 뷰, 집계 등) 지원.
플랫폼 독립성: 자바 기반으로 OS에 독립적.

JDBC의 단점

복잡성: 저수준 API로, 반복적인 코드(연결, 자원 해제 등) 작성 필요.
성능 한계: ORM(JPA, Hibernate)에 비해 최적화 작업 수동.
DBMS 의존성: 드라이버와 일부 SQL 문법은 DBMS별로 달라 관리 필요.

주의사항

드라이버 관리: 최신 JDBC 드라이버 사용, DBMS 버전과 호환 확인.
연결 누수: 자원 해제 안 하면 성능 저하 및 연결 고갈 위험.
SQL 인젝션: Statement 대신 PreparedStatement 사용 권장.
트랜잭션 처리: 대규모 작업 시 명시적 트랜잭션 관리 필수.
성능 최적화: 인덱스, 연결 풀, 배치 처리를 적극 활용.

데이터 모델링

데이터 모델링은 데이터베이스의 구조를 설계하고 데이터를 체계적으로 조직화하는 과정입니다. 데이터를 효율적으로 저장, 관리, 조회할 수 있도록 데이터베이스의 논리적, 물리적 구조를 정의합니다.

데이터 모델링이란?

데이터 모델링은 데이터의 구조, 관계, 제약 조건을 정의하여 데이터베이스의 설계도를 만드는 작업입니다. 비즈니스 요구사항을 반영해 데이터를 효과적으로 관리하고, 애플리케이션(예: JDBC 기반 프로그램)과 데이터베이스 간 상호작용을 최적화합니다.

핵심 목적:

데이터의 구조적 표현(테이블, 열, 관계 등).
데이터 무결성 보장(정확성, 일관성).
쿼리 성능 최적화(인덱스, 조인 등 활용).
비즈니스 요구사항 충족.

데이터 모델링의 주요 단계

데이터 모델링은 일반적으로 세 단계로 진행됩니다:

1. 개념적 데이터 모델링 (Conceptual Data Modeling)

설명: 비즈니스 요구사항을 기반으로 데이터의 전체 구조를 추상적으로 정의.
주요 작업:
- 주요 엔터티(Entity) 식별(예: 학생, 강의, 성적).
- 엔터티 간 관계(Relationship) 정의(예: 학생-성적은 1:N).
- ERD(Entity-Relationship Diagram) 작성.
특징: 비즈니스 프로세스 중심, 기술적 세부사항 배제.
연계: 사용자 관리와 뷰를 설계하기 전, 어떤 데이터가 필요한지 파악.

2. 논리적 데이터 모델링 (Logical Data Modeling)

설명: 개념적 모델을 데이터베이스 구조로 구체화. 데이터 형식, 제약 조건 포함.
주요 작업:
- 엔터티를 테이블로 변환.
- 속성(Attribute)을 열로 정의(예: INT, VARCHAR, DATETIME).
- 기본 키(Primary Key), 외래 키(Foreign Key) 설정.
- 정규화(Normalization)로 데이터 중복 최소화.
특징: DBMS 독립적, 데이터 무결성 강조.
연계: 숫자/날짜 데이터 형식, 인덱스 설계의 기초.

3. 물리적 데이터 모델링 (Physical Data Modeling)

설명: 실제 DBMS에 맞춰 테이블, 인덱스, 파티션 등을 구현.
주요 작업:
- 테이블, 열, 인덱스 생성.
- 스토리지, 성능 최적화(예: 인덱스, 파티셔닝).
- 사용자 권한 정의(예: GRANT 설정).
특징: DBMS별 특성 반영(예: MySQL vs PostgreSQL).
연계: JDBC로 접근할 테이블 구조, 인덱스, 뷰 구현.

데이터 모델링의 주요 기법

1. ERD (Entity-Relationship Diagram)

엔터티(테이블), 속성(열), 관계(1:1, 1:N, N:N)를 시각화.
예: 학생(엔터티)-성적(엔터티) 간 외래 키로 1:N 관계 정의.

2. 정규화 (Normalization)

데이터 중복 제거 및 무결성 보장을 위해 테이블을 분리.
1NF: 모든 속성은 원자값(단일 값).
2NF: 부분 함수 종속 제거.
3NF: 이행 함수 종속 제거.
연계: 조인 쿼리에서 정규화된 테이블 사용.

3. 비정규화 (Denormalization)

성능 최적화를 위해 일부 중복 허용.
예: 자주 조회되는 조인 결과를 뷰로 저장.
연계: 뷰, 인덱스와 함께 성능 개선.

4. 키(Key) 관리

기본 키: 고유 식별자(예: student_id).
외래 키: 테이블 간 관계 연결(예: grades.student_id).
연계: 인덱스로 기본 키/외래 키 검색 속도 향상.

데이터 모델링과 이전 질문 연계

데이터 모델링은 이전 질문의 개념과 밀접하게 연결됩니다:

JDBC: 모델링된 테이블 구조를 JDBC로 접근(예: Connection, PreparedStatement로 쿼리).
사용자 관리: 물리적 모델링 단계에서 사용자 권한(GRANT, REVOKE) 정의.
인덱스: 물리적 모델링에서 검색/조인 열에 인덱스 설계.
뷰: 논리적/물리적 모델링 후 복잡한 쿼리를 뷰로 간소화.
조인: 엔터티 간 관계(1:N, N:N)를 조인으로 구현.
문자열/제어 흐름 함수: 모델링된 열에 CONCAT, CASE 등 적용.
집계 함수 및 GROUP BY: 정규화된 테이블에서 집계 쿼리 실행.
데이터 형식: 논리적 모델링에서 열의 데이터 형식(INT, DATETIME) 정의.

데이터 모델링의 장점

효율성: 데이터 구조 최적화로 쿼리 성능 향상.
무결성: 제약 조건으로 데이터 정확성 보장.
유연성: 비즈니스 변화에 따라 모델 수정 용이.
재사용성: 뷰, 조인 등으로 데이터 재활용.

데이터 모델링의 단점

복잡성: 대규모 시스템에서 모델링 과정 시간이 오래 걸림.
유지보수: 비즈니스 요구사항 변경 시 모델 수정 필요.
성능 균형: 과도한 정규화는 조인 비용 증가.

주의사항

비즈니스 이해: 요구사항 분석 없이 모델링하면 잘못된 설계 가능.
정규화 vs 비정규화: 읽기 성능(비정규화)과 쓰기 효율성(정규화) 균형 필요.
DBMS 특성: MySQL, PostgreSQL 등 DBMS별 제약(예: 인덱스 종류) 고려.
보안: 사용자 관리와 연계해 민감 데이터 접근 제한.
성능 최적화: 인덱스, 뷰를 적절히 설계해 JDBC 쿼리 속도 향상.

마무리

데이터 모델링은 데이터베이스의 구조를 체계적으로 설계하는 과정으로, 개념적, 논리적, 물리적 단계로 진행됩니다. JDBC, 사용자 관리, 인덱스, 뷰, 조인, 문자열/제어 흐름 함수, 집계 함수, 데이터 형식과 결합하여 데이터 관리 효율성을 높이고 보안을 강화합니다. 비즈니스 요구사항을 정확히 반영하고, 성능과 무결성을 균형 있게 고려하는 것이 성공적인 모델링의 핵심입니다.

정규화

정규화(Normalization)는 데이터베이스 설계에서 데이터 중복을 최소화하고 무결성을 보장하기 위해 데이터를 체계적으로 조직화하는 과정입니다. 테이블을 분리하여 데이터의 일관성을 유지하고, 삽입/삭제/갱신 이상(Anomaly)을 방지합니다.

정규화란?

정규화는 데이터베이스의 테이블을 특정 규칙(정규형, Normal Form)에 따라 분리하여 데이터 중복을 줄이고, 데이터 무결성을 보장하는 설계 기법입니다. 주로 데이터 모델링의 논리적 단계에서 수행되며, 비즈니스 요구사항을 충족하면서 효율적인 데이터 관리를 목표로 합니다.

핵심 목표:

중복 제거: 동일한 데이터가 여러 곳에 저장되지 않도록.
무결성 유지: 데이터의 정확성과 일관성 보장.
이상 방지: 삽입/삭제/갱신 시 데이터 불일치 예방.

정규화의 주요 단계

정규화는 일반적으로 1NF(제1정규형)에서 3NF(제3정규형)까지 진행하며, 더 고차원 정규형(BCNF, 4NF 등)도 있지만 실무에서는 3NF까지가 주로 사용됩니다. 아래는 주요 단계와 예시입니다.

1. 제1정규형 (1NF, First Normal Form)

설명: 모든 속성(열)이 원자값(Atomic Value)을 가져야 하며, 반복 그룹이나 다중 값이 없어야 함.
조건:
- 각 열은 단일 값만 저장.
- 동일한 유형의 데이터만 포함.
- 기본 키로 각 행을 고유하게 식별.
예시: 비정규화 테이블 (학생과 수강 과목): | student_id | name | courses | |————|——–|——————–| | 1 | 홍길동 | 수학, 영어, 과학 | | 2 | 김영희 | 수학, 역사 |
문제: courses 열에 다중 값(수학, 영어 등)이 포함되어 1NF 위반.
1NF로 변환: | student_id | name | course | |————|——–|———| | 1 | 홍길동 | 수학 | | 1 | 홍길동 | 영어 | | 1 | 홍길동 | 과학 | | 2 | 김영희 | 수학 | | 2 | 김영희 | 역사 |
설명: courses를 분리하여 각 행이 단일 값을 가지도록. student_id와 course 조합으로 기본 키 설정.

2. 제2정규형 (2NF, Second Normal Form)

설명: 1NF를 만족하며, 부분 함수 종속(Partial Dependency)을 제거. 즉, 모든 비기본 속성이 기본 키 전체에 종속해야 함.
조건:
- 1NF를 만족.
- 비기본 속성(기본 키 아닌 열)이 기본 키의 일부가 아닌 전체에 종속.
예시: 1NF 테이블: | student_id | course | student_name | course_teacher | |————|——–|————–|—————| | 1 | 수학 | 홍길동 | 이교수 | | 1 | 영어 | 홍길동 | 박교수 | | 2 | 수학 | 김영희 | 이교수 |
문제: student_name은 student_id에, course_teacher는 course에만 종속(부분 종속).
2NF로 변환:
- 학생 테이블: | student_id | student_name | |————|————–| | 1 | 홍길동 | | 2 | 김영희 |
- 과목 테이블: | course | course_teacher | |——–|—————| | 수학 | 이교수 | | 영어 | 박교수 |
- 수강 테이블: | student_id | course | |————|——–| | 1 | 수학 | | 1 | 영어 | | 2 | 수학 |
설명: 부분 종속 제거를 위해 테이블을 분리. student_id와 course가 수강 테이블의 기본 키.

3. 제3정규형 (3NF, Third Normal Form)

설명: 2NF를 만족하며, 이행 함수 종속(Transitive Dependency)을 제거. 비기본 속성이 다른 비기본 속성에 종속되지 않아야 함.
조건:
- 2NF를 만족.
- 비기본 속성이 기본 키 외의 다른 속성에 종속되지 않음.
예시: 2NF 테이블 (학생 정보): | student_id | name | department | dept_head | |————|——–|————|———–| | 1 | 홍길동 | 컴퓨터 | 김교수 | | 2 | 김영희 | 전자 | 이교수 |
문제: dept_head는 department에 종속(이행 종속).
3NF로 변환:
- 학생 테이블: | student_id | name | department | |————|——–|————| | 1 | 홍길동 | 컴퓨터 | | 2 | 김영희 | 전자 |
- 학과 테이블: | department | dept_head | |————|———–| | 컴퓨터 | 김교수 | | 전자 | 이교수 |
설명: dept_head를 별도 테이블로 분리해 이행 종속 제거.

정규화의 장점

중복 감소: 데이터 저장 공간 절약.
무결성 유지: 삽입/삭제/갱신 이상 방지.
유연성: 데이터 구조 변경에 유리.

정규화의 단점

복잡성 증가: 테이블 수 증가로 쿼리(예: 조인) 복잡.
성능 저하: 다중 조인으로 검색 속도 느려질 수 있음(인덱스로 완화).
설계 시간: 정규화 과정이 시간이 오래 걸릴 수 있음.

이전 질문과의 연계

정규화는 이전 질문의 개념과 밀접히 연결됩니다:

데이터 모델링: 정규화는 논리적 모델링의 핵심 과정.
JDBC: 정규화된 테이블을 JDBC로 쿼리(예: PreparedStatement로 조인).
사용자 관리: 정규화된 테이블에 사용자별 권한 설정(예: GRANT SELECT).
인덱스: 정규화로 분리된 테이블의 기본 키/외래 키에 인덱스 추가.
뷰: 정규화된 테이블을 조인한 결과를 뷰로 간소화.
조인: 정규화로 분리된 테이블을 조인으로 결합.
문자열/제어 흐름 함수: 정규화된 데이터에 CONCAT, CASE 적용.
집계 함수 및 GROUP BY: 정규화된 테이블에서 집계 쿼리 실행.

주의사항

정규화 수준: 실무에서는 3NF까지만 적용하는 경우가 많음. 과도한 정규화는 성능 저하.
비정규화 고려: 성능 최적화(예: 뷰, 인덱스) 위해 일부 중복 허용 가능.
비즈니스 요구: 정규화는 비즈니스 규칙과 데이터 사용 패턴을 반영해야.
DBMS 특성: MySQL, PostgreSQL 등 DBMS별 제약(예: 외래 키 지원) 고려.

마무리

정규화는 데이터 중복을 줄이고 무결성을 보장하는 필수적인 데이터베이스 설계 기법입니다. 1NF(원자값), 2NF(부분 종속 제거), 3NF(이행 종속 제거)를 통해 데이터 구조를 최적화하며, 데이터 모델링, JDBC, 사용자 관리, 인덱스, 뷰, 조인 등과 연계하여 효율적인 데이터 관리를 지원합니다. 적절한 정규화 수준을 선택하고, 성능과 유지보수를 균형 있게 고려하는 것이 중요합니다.

트랜잭션

트랜잭션(Transaction)은 데이터베이스에서 일련의 작업을 하나의 논리적 단위로 묶어 처리하는 메커니즘입니다. 이를 통해 데이터의 일관성과 무결성을 유지하며, 복잡한 작업이 부분적으로 실패하더라도 데이터베이스가 안정된 상태를 유지하도록 보장합니다.

트랜잭션이란?

트랜잭션은 데이터베이스에서 여러 SQL 작업(예: INSERT, UPDATE, DELETE)을 하나의 단위로 실행하여, 작업이 모두 성공하거나 실패하도록 관리하는 방법입니다. 예를 들어, 은행 계좌 간 송금 시 한 계좌에서 돈을 빼고 다른 계좌에 입금하는 두 작업은 하나의 트랜잭션으로 처리되어야 합니다.

핵심 목적:

데이터 무결성 보장.
작업의 원자성, 일관성, 격리성, 지속성 유지(ACID 속성).
오류 발생 시 데이터 복구(롤백).

트랜잭션의 ACID 속성

트랜잭션은 ACID 특성을 만족해야 합니다:

Atomicity (원자성): 트랜잭션 내 모든 작업이 모두 완료되거나, 전혀 실행되지 않아야 함. 부분 실행 방지.
Consistency (일관성): 트랜잭션 완료 후 데이터베이스가 일관된 상태를 유지(제약 조건, 데이터 무결성 준수).
Isolation (격리성): 트랜잭션은 다른 트랜잭션과 독립적으로 실행, 중간 상태가 외부에 노출되지 않음.
Durability (지속성): 트랜잭션 완료 후 결과가 영구적으로 저장, 시스템 장애에도 데이터 보존.

트랜잭션 관리

트랜잭션은 데이터베이스와 애플리케이션(예: JDBC)에서 다음과 같은 방법으로 관리됩니다:

1. 트랜잭션 시작

명시적 시작: BEGIN, START TRANSACTION 명령으로 트랜잭션 시작.
암시적 시작: 일부 DBMS에서 DML(INSERT, UPDATE 등) 실행 시 자동 시작.

2. 트랜잭션 커밋 (Commit)

설명: 트랜잭션의 모든 작업을 성공적으로 완료하고 영구 저장.
명령: COMMIT.
효과: 데이터 변경 사항이 데이터베이스에 반영, 지속성 보장.

3. 트랜잭션 롤백 (Rollback)

설명: 오류 발생 시 트랜잭션 시작 전 상태로 복구.
명령: ROLLBACK.
효과: 모든 변경 사항 취소, 데이터 일관성 유지.

4. 세이브포인트 (Savepoint)

설명: 트랜잭션 내 특정 지점으로 롤백 가능하도록 중간 저장점 설정.
명령: SAVEPOINT savepoint_name, ROLLBACK TO savepoint_name.
용도: 부분 롤백으로 유연한 오류 처리.

5. 자동 커밋 (Auto-Commit)

설명: 기본적으로 각 SQL 문이 독립 트랜잭션으로 처리.
설정: SET autocommit = 0 (비활성화, MySQL/PostgreSQL).

트랜잭션과 이전 질문 연계

트랜잭션은 이전 질문의 개념과 밀접히 연결됩니다:

정규화: 정규화된 테이블에서 트랜잭션은 데이터 무결성(예: 외래 키 제약) 유지에 필수.
데이터 모델링: 논리적/물리적 모델링 시 트랜잭션 처리 요구사항(예: ACID 준수) 반영.
JDBC: Connection.setAutoCommit(false)로 트랜잭션 제어, commit()/rollback() 호출.
사용자 관리: 트랜잭션 관련 권한(COMMIT, ROLLBACK)을 사용자/역할에 부여.
인덱스: 트랜잭션 내 UPDATE/INSERT 시 인덱스 갱신으로 성능 영향.
뷰: 업데이트 가능한 뷰에서 트랜잭션으로 데이터 수정 관리.
조인: 다중 테이블 조인 쿼리를 트랜잭션으로 묶어 일관성 보장.
문자열/제어 흐름 함수: 트랜잭션 내에서 CONCAT, CASE 등을 사용해 데이터 가공.

트랜잭션 관리 시 고려사항

격리 수준(Isolation Level):
- READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE 등.
- 격리 수준에 따라 동시성(Concurrency)과 성능 균형 조정.
- 예: SET TRANSACTION ISOLATION LEVEL READ COMMITTED (PostgreSQL).
데드락(Deadlock): 다중 트랜잭션이 자원을 점유하며 서로 대기 시 발생. 적절한 격리 수준과 락 관리 필요.
성능: 트랜잭션이 길어질수록 락 유지 시간 증가, 성능 저하 가능.
오류 처리: 예외 발생 시 롤백으로 데이터 복구.
DBMS 차이: MySQL은 InnoDB에서 트랜잭션 지원, PostgreSQL은 기본적으로 지원, Oracle은 고급 트랜잭션 기능 제공.

트랜잭션의 장점

무결성 보장: 데이터 일관성 유지.
오류 복구: 롤백으로 실패한 작업 취소.
동시성 제어: 여러 사용자의 동시 작업 관리.
유연성: 세이브포인트로 부분 복구 가능.

트랜잭션의 단점

성능 저하: 락과 격리 수준으로 인해 처리 속도 느려질 수 있음.
복잡성: 트랜잭션 관리 로직 설계가 복잡.
자원 사용: 긴 트랜잭션은 메모리와 락 자원 소모.

주의사항

최소화: 트랜잭션 범위를 최소화해 락 유지 시간 단축.
격리 수준 선택: 비즈니스 요구(예: 실시간 vs 배치)에 맞는 수준 선택.
JDBC 사용 시: try-catch와 try-with-resources로 자원 누수 방지.
인덱스 고려: 트랜잭션 내 쓰기 작업은 인덱스 갱신으로 성능 영향.
사용자 권한: 트랜잭션 관련 명령 실행 권한 확인.

This post is licensed under CC BY 4.0 by the author.