데이터 모델과 SQL 기초: 정규화, 조인, 트랜잭션 관리 이해하기

데이터 모델과 SQL에서는 정규화와 반정규화의 개념을 통해 데이터 구조를 최적화하고, 관계와 조인의 중요성을 이해합니다. 또한, 트랜잭션 관리, Null 속성의 처리, 식별자 개념(본질식별자 및 인조식별자)에 대해 다룹니다. 이를 통해 데이터베이스 설계의 기초를 다질 수 있습니다.

정규화와 반정규화

정규화

정규화(Normalization)는 데이터베이스 설계에서 데이터 중복을 최소화하고 데이터 무결성을 유지하기 위해 데이터를 구조화하는 과정입니다. 정규화는 여러 단계로 이루어지며, 각 단계는 특정한 규칙을 따릅니다. 일반적으로 1NF, 2NF, 3NF로 나뉘어 설명됩니다.

1NF (제1정규형)

1NF는 모든 필드가 원자값(Atomic Value)을 가져야 한다는 규칙입니다. 즉, 각 열에 저장된 값은 더 이상 나눌 수 없는 단일 값이어야 합니다.

예제:

다음과 같은 비정규화된 테이블이 있다고 가정합니다.

이 테이블은 전화번호가 여러 개의 값을 가집니다. 이를 1NF로 변환하면 다음과 같습니다.

CREATE TABLE 직원 (
    직원ID NUMBER PRIMARY KEY,
    이름 VARCHAR2(50),
    전화번호 VARCHAR2(15)
);

INSERT INTO 직원 VALUES (1, '홍길동', '010-1234-5678');
INSERT INTO 직원 VALUES (1, '홍길동', '02-123-4567');
INSERT INTO 직원 VALUES (2, '김영희', '010-9876-5432');

2NF (제2정규형)

2NF는 1NF를 만족하면서, 기본 키가 아닌 모든 속성이 기본 키에 완전 함수 종속(Full Functional Dependency)되어야 한다는 규칙입니다. 즉, 부분 종속이 없어야 합니다.

예제:

다음은 1NF를 만족하는 테이블입니다.

부서명은 부서ID에만 의존하므로, 부서ID를 기준으로 새로운 테이블을 만들 수 있습니다.

CREATE TABLE 부서 (
    부서ID NUMBER PRIMARY KEY,
    부서명 VARCHAR2(50)
);

INSERT INTO 부서 VALUES (10, '인사부');
INSERT INTO 부서 VALUES (20, '개발부');

CREATE TABLE 직원 (
    직원ID NUMBER PRIMARY KEY,
    부서ID NUMBER,
    FOREIGN KEY (부서ID) REFERENCES 부서(부서ID)
);

3NF (제3정규형)

3NF는 2NF를 만족하면서, 기본 키가 아닌 모든 속성이 서로 종속되지 않아야 한다는 규칙입니다. 즉, 이행적 종속(Transitive Dependency)이 없어야 합니다.

예제:

다음은 2NF를 만족하는 테이블입니다.

부서위치는 부서ID에 의존하므로, 이를 별도의 테이블로 분리할 수 있습니다.

CREATE TABLE 부서위치 (
    부서ID NUMBER PRIMARY KEY,
    부서위치 VARCHAR2(50)
);

INSERT INTO 부서위치 VALUES (10, '서울');
INSERT INTO 부서위치 VALUES (20, '부산');

CREATE TABLE 직원 (
    직원ID NUMBER PRIMARY KEY,
    부서ID NUMBER,
    FOREIGN KEY (부서ID) REFERENCES 부서(부서ID)
);

반정규화

반정규화(Denormalization)는 정규화를 통해 나누어진 여러 테이블을 다시 결합하여 성능을 향상시키는 과정입니다. 데이터베이스의 읽기 성능을 높이기 위해 데이터 중복을 허용할 수 있습니다. 그러나 반정규화는 데이터 무결성을 저해할 수 있으므로 신중하게 사용해야 합니다.

예제:

정규화된 직원 테이블과 부서 테이블이 있을 때, 성능 향상을 위해 두 테이블을 결합할 수 있습니다.

CREATE TABLE 직원_부서 (
    직원ID NUMBER PRIMARY KEY,
    이름 VARCHAR2(50),
    부서명 VARCHAR2(50),
    부서위치 VARCHAR2(50)
);

INSERT INTO 직원_부서 VALUES (1, '홍길동', '인사부', '서울');
INSERT INTO 직원_부서 VALUES (2, '김영희', '개발부', '부산');

이렇게 하면 JOIN 연산 없이도 직원과 부서 정보를 쉽게 조회할 수 있습니다. 하지만, 데이터 중복이 발생하므로 데이터 수정 시 주의가 필요합니다.

관계와 조인의 이해

관계

관계(Relationship)는 데이터베이스에서 테이블 간의 연결을 의미합니다. 관계형 데이터베이스에서는 여러 테이블이 서로 연결되어 데이터를 효율적으로 관리할 수 있습니다. 일반적으로 관계는 다음과 같은 유형으로 나뉩니다.

1. 일대일 관계 (One-to-One): 한 테이블의 레코드가 다른 테이블의 단일 레코드와 연결되는 경우입니다.
2. 일대다 관계 (One-to-Many): 한 테이블의 레코드가 다른 테이블의 여러 레코드와 연결되는 경우입니다.
3. 다대다 관계 (Many-to-Many): 한 테이블의 여러 레코드가 다른 테이블의 여러 레코드와 연결되는 경우입니다.

예제: 일대다 관계

직원 테이블과 부서 테이블을 통해 일대다 관계를 이해해봅시다. 한 부서는 여러 직원들을 가질 수 있습니다.

CREATE TABLE 부서 (
    부서ID NUMBER PRIMARY KEY,
    부서명 VARCHAR2(50)
);

CREATE TABLE 직원 (
    직원ID NUMBER PRIMARY KEY,
    이름 VARCHAR2(50),
    부서ID NUMBER,
    FOREIGN KEY (부서ID) REFERENCES 부서(부서ID)
);

위의 예제에서 각 부서는 여러 직원을 가질 수 있으며, 각 직원은 하나의 부서에 속합니다.

조인

조인(Join)은 두 개 이상의 테이블을 연결하여 데이터를 조회하는 방법입니다. SQL에서는 다양한 종류의 조인을 제공하여 필요에 따라 데이터를 결합할 수 있습니다. 주요 조인 유형은 다음과 같습니다.

1. INNER JOIN: 두 테이블에서 일치하는 레코드만 반환합니다.
2. LEFT JOIN (LEFT OUTER JOIN): 왼쪽 테이블의 모든 레코드와 오른쪽 테이블의 일치하는 레코드를 반환합니다. 오른쪽 테이블에 일치하는 레코드가 없으면 NULL을 반환합니다.
3. RIGHT JOIN (RIGHT OUTER JOIN): 오른쪽 테이블의 모든 레코드와 왼쪽 테이블의 일치하는 레코드를 반환합니다. 왼쪽 테이블에 일치하는 레코드가 없으면 NULL을 반환합니다.
4. FULL JOIN (FULL OUTER JOIN): 두 테이블의 모든 레코드를 반환하며, 일치하지 않는 경우 NULL을 반환합니다.

예제: INNER JOIN

직원과 부서 정보를 조회하기 위해 INNER JOIN을 사용할 수 있습니다.

SELECT 직원.이름, 부서.부서명
FROM 직원
INNER JOIN 부서 ON 직원.부서ID = 부서.부서ID;

위 쿼리는 직원의 이름과 해당 직원이 속한 부서명을 반환합니다. 두 테이블에서 부서ID가 일치하는 레코드만 조회됩니다.

예제: LEFT JOIN

LEFT JOIN을 사용하여 모든 직원의 이름과 해당 부서명을 조회할 수 있습니다. 부서가 없는 직원도 포함됩니다.

SELECT 직원.이름, 부서.부서명
FROM 직원
LEFT JOIN 부서 ON 직원.부서ID = 부서.부서ID;

이 쿼리는 모든 직원의 이름과 그들의 부서명을 반환하며, 부서가 없는 직원의 경우 부서명은 NULL로 표시됩니다.

예제: RIGHT JOIN

RIGHT JOIN을 사용하여 모든 부서와 그 부서에 속한 직원의 정보를 조회할 수 있습니다.

SELECT 직원.이름, 부서.부서명
FROM 직원
RIGHT JOIN 부서 ON 직원.부서ID = 부서.부서ID;

이 쿼리는 모든 부서의 이름과 그 부서에 속한 직원의 이름을 반환하며, 직원이 없는 부서의 경우 직원 이름은 NULL로 표시됩니다.

예제: FULL JOIN

FULL JOIN을 사용하여 모든 직원과 모든 부서의 정보를 조회할 수 있습니다.

SELECT 직원.이름, 부서.부서명
FROM 직원
FULL JOIN 부서 ON 직원.부서ID = 부서.부서ID;

이 쿼리는 모든 직원과 모든 부서의 이름을 반환하며, 일치하지 않는 경우 NULL로 표시됩니다.

이와 같이 관계와 조인을 이해하면 데이터베이스에서 효율적으로 데이터를 관리하고 조회할 수 있습니다.

트랜잭션

트랜잭션(Transaction)은 데이터베이스에서 수행되는 일련의 작업을 의미하며, 이 작업들은 하나의 논리적 단위로 묶여야 합니다. 트랜잭션은 데이터베이스의 일관성과 무결성을 유지하기 위해 중요합니다. 트랜잭션은 다음과 같은 속성을 가집니다. 이를 ACID 속성이라고 합니다.

ACID 속성

1. 원자성 (Atomicity): 트랜잭션 내의 모든 작업이 성공적으로 완료되거나, 모두 실패해야 한다는 것을 의미합니다. 즉, 트랜잭션의 중간 상태는 존재하지 않습니다.

2. 일관성 (Consistency): 트랜잭션이 완료되면 데이터베이스는 일관된 상태를 유지해야 합니다. 트랜잭션이 시작되기 전과 후의 데이터 상태가 일관되어야 합니다.

3. 고립성 (Isolation): 여러 트랜잭션이 동시에 실행될 때, 각 트랜잭션은 서로 영향을 미치지 않아야 합니다. 즉, 한 트랜잭션의 작업이 다른 트랜잭션에 영향을 주지 않도록 보장해야 합니다.

4. 지속성 (Durability): 트랜잭션이 성공적으로 완료되면, 그 결과는 영구적으로 데이터베이스에 반영되어야 합니다. 시스템 장애가 발생하더라도 데이터는 손실되지 않아야 합니다.

트랜잭션의 시작과 종료

SQL에서는 BEGIN, COMMIT, ROLLBACK 명령어를 사용하여 트랜잭션을 관리합니다.

• BEGIN: 트랜잭션을 시작합니다.
• COMMIT: 트랜잭션을 성공적으로 완료하고, 변경 사항을 데이터베이스에 반영합니다.
• ROLLBACK: 트랜잭션을 취소하고, 이전 상태로 되돌립니다.

예제: 트랜잭션 사용하기

아래 예제에서는 직원의 급여를 업데이트하는 트랜잭션을 보여줍니다. 급여 업데이트 작업이 성공적으로 완료되면 COMMIT을 사용하여 변경 사항을 반영하고, 오류가 발생하면 ROLLBACK을 사용하여 이전 상태로 되돌립니다.

BEGIN;

UPDATE 직원
SET 급여 = 급여 * 1.1
WHERE 부서ID = 30;

-- 만약 오류가 발생하면
-- ROLLBACK;
-- 그렇지 않으면
COMMIT;

위의 예제에서 BEGIN으로 트랜잭션을 시작하고, 특정 부서의 직원 급여를 10% 인상합니다. 오류가 발생하지 않으면 COMMIT을 통해 변경 사항을 저장합니다. 만약 오류가 발생했다면 ROLLBACK을 사용하여 급여 업데이트를 취소할 수 있습니다.

트랜잭션의 중요성

트랜잭션은 데이터베이스의 무결성을 유지하는 데 필수적입니다. 예를 들어, 은행 계좌 간의 돈 이체와 같은 작업에서는 두 개의 계좌에서 동시에 작업이 이루어져야 하므로, 트랜잭션을 통해 원자성을 보장해야 합니다. 이를 통해 데이터의 일관성을 유지하고, 오류 발생 시 안전하게 복구할 수 있습니다.

트랜잭션을 적절히 관리함으로써 데이터베이스의 신뢰성과 안정성을 높일 수 있습니다.

Null 속성

Null 속성은 데이터베이스에서 특정 필드의 값이 존재하지 않음을 나타내는 특수한 상태입니다. Null은 "값이 없음"을 의미하며, 이는 공백이나 0과는 다릅니다. Null은 데이터베이스에서 중요한 개념으로, 데이터의 무결성과 의미를 이해하는 데 필수적입니다.

Null의 의미

1. 값이 없음: Null은 해당 필드에 값이 없음을 나타냅니다. 이는 데이터가 아직 입력되지 않았거나, 해당 정보가 존재하지 않음을 의미합니다.
2. 미지의 값: Null은 특정 값이 알려지지 않거나 불확실한 경우를 나타낼 수 있습니다.
3. 비어 있는 값: Null은 공백 문자열이나 0과는 다르며, 단순히 "존재하지 않음"을 표현합니다.

Null과 다른 값의 차이

Null은 다음과 같은 이유로 다른 값과 구별됩니다:

• Null은 수학적 계산에서 사용될 수 없으며, 다른 값과 비교할 수 없습니다.
• Null은 비교 연산자(=, !=)로 비교할 수 없으며, 대신 IS NULL 또는 IS NOT NULL을 사용해야 합니다.

Null 값 처리

SQL에서 Null 값을 처리하는 방법은 다음과 같습니다:

1. NULL 체크: Null 값을 확인하기 위해 IS NULL 또는 IS NOT NULL을 사용합니다.

SELECT *
FROM 직원
WHERE 급여 IS NULL;

위 쿼리는 급여가 Null인 모든 직원의 정보를 조회합니다.

2. NULL 대체: Null 값을 다른 값으로 대체하기 위해 COALESCE 함수를 사용할 수 있습니다. 이 함수는 첫 번째 인자가 Null이 아닐 경우 그 값을 반환하고, Null일 경우 두 번째 인자를 반환합니다.

SELECT 이름, COALESCE(급여, 0) AS 급여
FROM 직원;

위 쿼리는 급여가 Null인 경우 0으로 대체하여 조회합니다.

3. NULL과의 연산: Null 값이 포함된 연산은 결과가 Null이 됩니다. 예를 들어, Null과 어떤 숫자를 더하면 결과는 Null입니다.

SELECT NULL + 100 AS 결과
FROM DUAL;

위 쿼리의 결과는 Null입니다.

Null 속성의 중요성

Null 속성은 데이터베이스 설계에서 중요한 역할을 합니다. 예를 들어, 직원 테이블에서 특정 직원의 급여 정보가 아직 입력되지 않았다면, 해당 필드에 Null 값을 설정하여 데이터의 의미를 명확히 할 수 있습니다. Null 값을 통해 데이터의 누락이나 불완전성을 표현할 수 있으며, 이를 통해 데이터의 무결성을 유지할 수 있습니다.

Null 값을 적절히 처리함으로써 데이터베이스에서 발생할 수 있는 오류를 예방하고, 데이터 분석 및 조회의 정확성을 높일 수 있습니다.

식별자, 본질식별자와 인조식별자

식별자는 데이터베이스에서 각 레코드를 고유하게 식별하는 데 사용되는 속성입니다. 식별자는 데이터베이스의 무결성을 유지하고, 데이터 간의 관계를 명확히 하는 데 중요한 역할을 합니다. 식별자는 크게 본질식별자와 인조식별자로 나눌 수 있습니다.

본질식별자 (Natural Key)

본질식별자(Natural Key)는 데이터의 자연적인 속성을 기반으로 하여 레코드를 식별하는 데 사용되는 키입니다. 즉, 실제 세계에서 고유성을 가지는 속성으로, 데이터의 의미를 내포하고 있습니다. 본질식별자는 일반적으로 변경되지 않는 값이어야 하며, 예를 들어 주민등록번호, 이메일 주소, 전화번호 등이 있습니다.

예제: 본질식별자 사용하기

직원 테이블에서 이메일 주소를 본질식별자로 사용할 수 있습니다.

CREATE TABLE 직원 (
    직원ID NUMBER PRIMARY KEY,
    이름 VARCHAR2(50),
    이메일 VARCHAR2(100) UNIQUE
);

위 예제에서 이메일은 각 직원의 고유한 속성으로 본질식별자 역할을 합니다.

인조식별자 (Surrogate Key)

인조식별자(Surrogate Key)는 데이터의 자연적인 속성과는 무관하게, 데이터베이스에서 생성된 고유한 식별자입니다. 일반적으로 숫자형 데이터로, 시스템이 자동으로 생성하며, 데이터의 의미를 내포하지 않습니다. 인조식별자는 데이터의 변경이나 이동이 발생하더라도 고유성을 유지할 수 있습니다.

예제: 인조식별자 사용하기

직원 테이블에서 직원ID를 인조식별자로 사용할 수 있습니다. 이 값은 시스템이 자동으로 생성하는 고유한 숫자입니다.

CREATE TABLE 직원 (
    직원ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    이름 VARCHAR2(50),
    이메일 VARCHAR2(100) UNIQUE
);

위 예제에서 직원ID는 시스템이 자동으로 생성하는 인조식별자로, 각 직원의 고유성을 보장합니다.

본질식별자와 인조식별자의 비교

식별자 선택의 중요성

식별자는 데이터베이스 설계에서 매우 중요한 요소입니다. 본질식별자는 데이터의 의미를 반영하므로, 데이터의 가독성을 높이고 이해를 돕습니다. 반면, 인조식별자는 데이터의 변경에 강하며, 시스템에서 관리하기 용이합니다.

식별자를 선택할 때는 데이터의 특성과 요구 사항을 고려하여 본질식별자와 인조식별자 중 적절한 방법을 선택해야 합니다. 이를 통해 데이터베이스의 무결성을 유지하고, 효율적인 데이터 관리를 할 수 있습니다.