[데이터베이스개론·SQL] 250114

이슬비·2025년 1월 14일

Database sql

데이터베이스개론·SQL

목록 보기

2/9

정규화(Normalization)

데이터베이스의 설계 단계에서 데이터 중복을 제거하고 데이터 무결성을 유지하기 위한 과정이다.

특징으로는 다음과 같다.

데이터를 논리적으로 분리하여 효율적으로 저장하고 관리하도록 설계.
데이터 중복 방지.
데이터 일관성 및 무결성 유지.
데이터 저장소의 효율성 향상.
데이터 변경 시 발생할 수 있는 문제(삽입, 삭제, 갱신 이상) 최소화.

제1정규형(1NF)

모든 속성은 반드시 하나의 값만 가져야 한다.

이름	직업
이지은	배우, 가수, 작곡가

직업 속성의 값을 하나만 갖도록 1차 정규화를 적용한다.

이름	직업
이지은	배우
이지은	가수
이지은	작곡가

또한 유사한 속성이 반복되는 경우도 1차 정규화의 대상이다.

이름	사이트1	사이트2	사이트3
이지은	인스타그램	페이스북	유튜브

사이트 속성이 반복되기 때문에 다음과 같이 분리한다.

이름	사이트
이지은	인스타그램
이지은	페이스북
이지은	유튜브

제2정규형(2NF)

엔터티의 모든 일반 속성은 반드시 모든 주식별자에 종속되어야 한다. 제2정규형을 적용하는 이유는 삽입, 삭제, 갱신 시 이상이 발생할 수 있기 때문이다.

주문번호	음료코드	주문수량	음료명
202106010010	A1001	2	아메리카노
202106010011	A1002	1	카페라떼
202106010012	A1001	3	아메리카노

주문번호와 음료코드가 주식별자이며, 일반속성인 음료명은 음료코드 속성에만 종속되기 때문에 분리한다.

주문번호	음료코드	주문수량	음료코드	음료명
202106010010	A1001	2	A1001	아메리카노
202106010011	B1002	1	B1002	카페라떼
202106010012	A1001	3

기본키가 단일키일 경우 제2정규형은 항상 만족한다.

제3정규형(3NF)

주식별자가 아닌 모든 속성 간에는 서로 종속될 수 없다.

일련번호	이름	소속사코드	소속사명
1	이지은	A1001	EDAM엔터테이먼트
2	김향기	B1004	지킴엔터테이먼트
3	유건우	B1004	지킴엔터테이먼트

일련번호가 주식별자이며, 소속사코드와 소속사명은 서로 종속되기 때문에 분리한다.

일련번호	이름	소속사코드	소속사코드	소속사명
1	이지은	A1001	A1001	EDAM엔터테이먼트
2	김향기	B1004	B1004	지킴엔터테이먼트
3	유건우	B1004

문제
아래 논리 데이터 모델을 3차 정규화까지 수행했을 때 도출되는 엔터티 수는 몇인가? (하나의 대출자에 대해 하나의 대출번호로 여러 개의 도서 대출 / 반납을 관리하고자 한다고 가정하고, 엔터티 통합은 고려하지 않음)

[학생] : 학번(PK), 성명, 학과번호
[학과] : 학과번호(PK), 학과명
[교수] : LAB실이용승인교수번호(PK), LAB실이용승인교수명
[LAB실이용신청] : 학번(PK), LAB실이용신청일(PK), LAB실이용시작일, LAB실이용만기일, LAB실승인교수번호
[대출] : 대출번호(PK), 대출자번호, 대출자신분구분, 대출일자
[대출도서] : 대출번호(PK), 대출도서번호, 반납일자
[도서] : 대출도서번호(PK), 대출도서명, 출판사명, 출판년월, 대표저자명, ISBN

반정규화(Denormalization)

데이터베이스 설계에서 정규화를 통해 분리된 테이블을 특정 성능 향상이나 시스템 요구에 따라 다시 결합하거나 중복 데이터를 허용하는 과정이다. 성능 최적화와 효율적인 데이터 엑세스를 위해 데이터 모델을 조정하는 것이다.

반정규화의 필요성은 다음과 같다.

정규화의 한계
- 정규화를 통해 데이터 무결성과 중복 문제를 해결하지만, 다수의 테이블 조인이 필요한 쿼리에서는 성능 저하 발생.
- 읽기 속도가 중요한 상황에서 정규화된 데이터 모델은 비효율적일 수 있음.
성능 최적화
- 쿼리 성능을 개선하기 위해 데이터를 중복 저장하거나 테이블을 합치는 방식으로 읽기 속도를 높임.
- OLAP(Online Analytical Processing) 시스템에서 활용.

반정규화 적용 방법

속성 추가 : 새로운 속성을 추가하여 조인을 생략하고 직접 데이터를 조회한다.
테이블 병합 : 두 테이블을 병합하여 하나의 테이블로 사용한다.
중복 데이터 저장 : 데이터 분석 속도를 위해 요약 데이터를 미리 계산하고 테이블에 저장한다.
이력 데이터 관리 : 트랜잭션 데이터 외에도 과거 데이터를 효율적으로 조회하기 위해 반정규화를 적용한다.

반정규화의 장점

성능 개선
- 읽기 속도 증가, 조인 연산 감소.
- 대규모 데이터 분석과 보고서 생성 시 유리.
간단한 쿼리
- 데이터 구조 단순화로 인해 SQL 쿼리가 간결해짐.
- 사용자가 테이블 구조를 쉽게 이해할 수 있음.
특정 요구사항 대응
- 응용 프로그램에서 특정 데이터 집합을 자주 조회해야 하는 경우 적합.

반정규화의 단점

데이터 중복
- 중복으로 인해 저장 공간 증가.
- 데이터 변경 시 모든 중복 데이터를 동기화해야 하는 복잡성 증가.
데이터 무결성 문제
- 업데이트나 삭제 시 데이터 불일치 가능성이 높아짐.
유연성 감소
- 테이블 병합이나 속성 추가로 인해 데이터 구조가 고정되어 유연성이 저하됨.

OLTP(Online Transaction Processing)

실시간으로 다수의 트랜잭션을 처리하는 시스템으로, 주로 운영 환경에서 사용한다. 데이터의 입력, 저장, 수정 및 조회를 빠르게 처리하며, 은행, 전자상거래, 예약 시스템 등에 활용된다.

주요 특징으로는 다음과 같다.

실시간 처리 : 다수의 사용자 요청을 신속하게 처리.
정규화된 데이터 구조 : 데이터 중복 최소화 및 데이터 무결성 유지.
트랜잭션 관리 : ACID(Atomicity, Consistency, Isolation, Durability) 보장.

구성 요소로는 다음과 같다.

데이터베이스 : 관계형 데이터베이스(Oracle, MySQL 등).
애플리케이션 : 웹 또는 모바일 기반 트랜잭션 처리 애플리케이션.
사용자 : 실시간으로 데이터를 입력하거나 조회하는 최종 사용자.

OLAP(Online Analytical Processing)

대규모 데이터를 분석하고 요약 정보와 통찰력을 제공하는 시스템이다. 데이터 분석, 보고서 생성, 비즈니스 인사이트를 제공하며, 데이터 마트, 비즈니스 인텔리전스, 대시보드 시스템 등에 활용된다.

주요 특징으로는 다음과 같다.

복잡한 분석 : 집계, 요약, 패턴 분석 쿼리를 통해 비즈니스 인사이트 제공.
대규모 데이터 처리 : 데이터 웨어하우스 및 데이터 마트를 활용하여 분석.
다차원 데이터 분석 : 시간, 지역, 제품 등 여러 차원을 기준으로 데이터 요약.

구성 요소로는 다음과 같다.

데이터 웨어하우스 : 정형, 비정형 데이터를 통합하고 분석 중심으로 저장.
OLAP 서버 : 분석 쿼리를 실행하며 다차원 데이터 모델 제공.
사용자 인터페이스 : 분석 결과를 시각적으로 제공하는 대시보드와 리포트.

Oracle에서 비정형 데이터를 다루기 위한 LOB 타입이 있다.

OLTP와 OLAP 상호작용

OLTP는 데이터 생성, OLAP는 데이터 활용을 위한 시스템이다. 즉, OLTP에서 생성된 데이터를 OLAP로 추출하여 분석한다.

ETL(Extract, Transform, Load) 프로세스란 데이터 웨어하우스로 데이터를 전송하기 위한 과정이다.
OLTP $\rightarrow$ ETS $\rightarrow$ OLAP.

논리 모델

데이터베이스 설계의 한 단계로 데이터의 구조와 관계를 기술하는 모델이다. 특정 DBMS에 종속되지 않으며 데이터의 논리적 구조를 나타낸다.

엔터티(Entity)와 속성(Attribute)을 정의하며, 관계(Relationship)와 제약 조건을 표현한다. 데이터 중복 제거를 위한 정규화를 적용한다.

물리 모델

데이터베이스 설계의 최종 단계로 특정 DBMS에 종속적인 데이터 구조를 정의하는 모델이다. 데이터의 저장소, 성능, 보안을 고려하여 실제로 구현한다.

데이터 타입과 제약 조건을 지정하고, 인덱스와 파티션, 저장소 구조를 설계한다. 또한 DBMS의 기술적 제약 및 성능 최적화를 반영한다.

논리 모델 $\rightarrow$ 물리 모델

논리 모델에서 정의된 엔터티와 관계를 기반으로 물리적 테이블을 생성한다. 속성은 데이터 타입와 제약 조건으로 매핑하며, 데이터베이스의 성능을 고려하여 인덱스와 파티션을 추가한다.

변환 시 고려사항으로는 다음과 같다.

데이터베이스의 크기와 예상 트래픽.
읽기 및 쓰기 작업의 비율.
DBMS의 기능 및 제약.

개념적 설계 $\rightarrow$ 논리적 설계 $\rightarrow$ 물리적 설계.

SQL 개념

SQL(Structured Query Language)

데이터베이스를 관리하고 조작하기 위한 언어이다. 관계형 데이터베이스에서 데이터를 검색, 추가, 수정, 삭제(CRUD)하는 데 사용하며, 데이터를 정의, 조작, 제어하기 위한 명령어 집합을 제공한다.

DDL

데이터베이스 객체를 정의하거나 변경하는 데 사용한다.

CREATE : 테이블, 뷰, 인덱스 생성.
ALTER : 기존 객체 수정.
DROP : 객체 삭제.
TRUNCATE : 테이블 데이터 초기화.

CREATE TABLE AS SELECT : 다른 테이블이 있는 데이터를 복사하여 새로운 테이블을 생성.

CREATE 예시는 다음과 같다.

CREATE TABLE CUSTOMERS (
	CUSTOMER_ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(50) NOT NULL,
    EMAIL VARCHAR2(100) UNIQUE,
    PHONE VARCHAR2(15),
    CREATED_AT DATE DEFAULT SYSDATE
);

VARCHAR : MySQL.
VARCHAR2 : Oracle.

현업에서 NOT NULL은 오류를 방지하기 위해 자주 사용하지 않는다.

ALTER 예시는 다음과 같다.

--컬럼 추가
ALTER TABLE CUSTOMERS ADD ADDRESS VARCHAR2(200);

--컬럼 사이즈 or 데이터 타입 변경
ALTER TABLE CLIENTS MODIFY CONTACT_EMAIL VARCHAR2(200);

--컬럼 삭제
ALTER TABLE CUSTOMERS DROP COLUMN PHONE;

--컬럼명 변경
ALTER TABLE CLIENTS RENAME COLUMN NAME TO FULL_NAME;

--테이블명 변경
ALTER TABLE CUSTOMERS RENAME TO CLIENTS;

DROP과 TRUNCATE 예시는 다음과 같다.

--테이블 삭제
DROP TABLE CUSTOMERS;

--테이블 데이터 초기화
TRUNCATE TABLE CUSTOMERS;

DML

데이터베이스의 데이터를 조작하는 데 사용한다.

SELECT : 데이터 검색.
INSERT : 데이터 삽입.
UPDATE : 데이터 수정.
DELETE : 데이터 삭제.

INSERT와 SELECT 예시는 다음과 같다.

--데이터 삽입
INSERT INTO CUSTOMERS (CUSTOMER_ID, NAME, EMAIL, PHONE)
VALUES (1, 'JOHN DOE', 'JOHN.DOE@EXAMPLE.COM', '555-1234');

--데이터 조회
SELECT NAME, EMAIL
FROM CUSTOMERS
WHERE PHONE = '555-1234';

UPDATE와 DELETE 예시는 다음과 같다.

--데이터 변경
UPDATE CUSTOMERS
SET EMAIL = 'JOHN.NEW@EXAMPLE.COM'
WHERE CUSTOMER_ID = 1;

--데이터 삭제
DELETE FROM CUSTOMERS
WHERE CUSTOMER_ID = 1;

DELETE는 TRUNCATE와는 다르게 ROLLBACK이 가능하기 때문에 더 느리다.

DCL

데이터베이스 접근 권한을 관리한다.

GRANT : 권한 부여.
REVOKE : 권한 회수.

GRANT와 REVOKE 예시는 다음과 같다.

--권한 부여
GRANT SELECT ON CUSTOMERS TO USER1;

--권한 회수
REVOKE SELECT ON CUSTOMERS FROM USER1;

TCL

트랜잭션 관리를 위한 명령어를 제공한다.

COMMIT : 트랜잭션 확정.
ROLLBACK : 트랜잭션 취소.
SAVEPOINT : 특정 지점 저장.

COMMIT과 ROLLBACK을 하지 않을 경우 휘발된다.

COMMIT와 ROLLBACK 예시는 다음과 같다.

--트랜잭션 확정
INSERT INTO CUSTOMERS (CUSTOMER_ID, NAME)
VALUES (2, 'JANE DOE');
COMMIT;

--트랜잭션 취소
DELETE FROM CUSTOMERS
WHERE CUSTOMER_ID = 2;
ROLLBACK;

`SELECT` 기초

`SELECT`

SQL에서 데이터를 검색하는 명령어이다. 테이블에서 원하는 데이터를 추출하며, 조건, 정렬, 그룹핑 등을 사용하여 복잡한 질의가 가능하다.

다음과 같이 사용한다.

SELECT 컬럼명 FROM 테이블명;

--예시
SELECT * FROM EMPLOYEES;
SELECT NAME, SALARY FROM EMPLOYEES;

특정 컬럼명을 사용하는 것과 전체 컬럼을 가져오는 것은 성능이 동일하다. DB는 Block을 기준으로 데이터를 저장하며 하나의 Block는 여러 행들을 수평으로 이어서 저장하기 때문에, 성능에 큰 차이가 없다.

ALIAS

SQL 쿼리에서 컬럼이나 테이블에 부여하는 임시 이름이다. 결과 집합의 가독성을 높이고 복잡한 쿼리를 간결하게 작성할 수 있도록 돕는 기능이다.

ALIAS의 특징은 다음과 같다.

테이블이나 컬럼 이름 대신 사용할 수 있음.
데이터베이스 구조에 영향을 주지 않는 임시 이름.
컬럼 ALIAS의 경우 AS 키워드를 사용하지만 생략도 가능.

컬럼 ALIAS는 연산 결과에 의미를 부여하며 이를 통해 가독성을 높인다. 다음과 같이 사용한다.

SELECT 컬럼명 (AS) 별칭 FROM 테이블명;

--예시
SELECT NAME AS EMPLOYEE_NAME, SALARY AS MONTHLY_SALARY FROM EMPLOYEES;
--연산 결과에 ALIAS 추가
SELECT SALARY * 12 AS ANNUAL_SALARY FROM EMPLOYEES;

테이블 ALIAS는 테이블 이름이 길거나 복잡할 때 간결한 이름으로 대체하며, JOIN 쿼리에서 테이블 이름을 축약하여 사용한다.

SELECT 컬럼명 FROM 테이블명 별칭;

--예시
--단일 테이블 사용
SELECT E.NAME, E.SALARY FROM EMPLOYEES E;
--JOIN 쿼리에서 사용
SELECT E.NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN DEPARTMENTS D
  ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
--조합 사용
SELECT E.NAME AS EMPLOYEES_NAME, D.DEPARTMENT_NAME AS DEPT_NAME
FROM EMPLOYEES E, DEPARTMENT D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;
--특수문자 또는 공백, 한글 포함
SELECT NAME AS "이름", SALARY AS "급여" FROM EMPLOYEES;

ALIAS를 설정하고 사용하지 않으면 부적합한 식별자 에러가 발생한다.

JOIN 시에 WHERE절을 사용하지 않으면 CROSS JOIN이 된다.

`NULL`

데이터베이스에서 NULL은 "값이 없음"을 의미한다. 숫자나 문자 등 어떠한 데이터도 저장되지 않은 상태이며, 이는 0이나 빈 문자열("")과는 다르다.

NULL 연산의 특징은 다음과 같다.

NULL 값은 다른 값과 비교할 수 없음.
비교 연산(=, <>, <, >, <=, >=)을 수행하면 항상 FALSE를 반환.

SELECT * FROM EMPLOYEES WHERE SALARY = NULL;  --FALSE를 반환
SELECT * FROM EMPLOYEES WHERE SALARY IS NULL;

`WHERE`

조건을 지정하여 데이터를 필터링한다. SELECT, UPDATE, DELETE와 함께 사용한다.

SELECT NAME FROM EMPLOYEES WHERE SALARY > 50000;
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 10;

다양한 연산자를 활용하여 복잡한 조건 설정이 가능하다.

비교 연산자

= : 값이 같은지 비교.
<> : 값이 다른지 비교.
>, < : 값이 크거나 작은지 비교.
>=, <= : 값이 크거나 같음 또는 작거나 같음을 비교.

SELECT * FROM EMPLOYEES WHERE SALARY > 50000;

논리 연산자 `AND`

여러 조건이 모두 TRUE인 경우만 결과를 반환한다.

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 10 AND SALARY > 50000;

논리 연산자 `OR`

여러 조건 중 하나만 TRUE인 경우 결과를 반환한다.

SELECT * FROM EMPLOYEES WHERE DEPARMENT_ID = 10 OR DEPARTMENT_ID = 20;

논리 연산자 `NOT`

조건의 결과를 반전시킨다.

SELECT * FROM EMPLOYEES WHERE NOT SALARY > 50000;

특수 연산자 `LIKE`

특정 패턴을 가진 데이터를 검색한다. 와일드카드를 활용한다.

% : 0개 이상의 문자를 대체.
_ : 1개의 문자를 대체.
ESCAPE : 와일드카드 %, _가 포함된 패턴을 찾을 때 활용.
- SELECT 컬럼명 FROM 테이블명 WHERE 컬럼명 LIKE '패턴' ESCAPE '문자';

문제
파일명이Data_2024로 시작하는 데이터를 검색하시오.
SELECT FILE_NAME
FROM FILES
WHERE FILE_NAME LIKE 'Data\_2024%' ESCAPE '\';

특수 연산자 `IN`

지정된 값 중 하나와 일치하는 데이터를 찾는다. OR 조건으로 대체 가능하다.

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN (10, 20, 30);

IN 연산자와 OR 연산자는 내부적으로 동일한 성능을 갖는다.

특수 연산자 `BETWEEN`

지정된 범위 내에 있는 값을 찾으며, 범위는 시작값과 끝값을 포함한다.

SELECT * FROM EMPLOYEES
WHERE SALARY BETWEEN 30000 AND 50000;

초과, 미만이 아닌 이상, 이하이다.

특수 연산자 `IS NULL`

NULL 값을 가진 데이터를 찾는다.

SELECT * FROM CUSTOMERS WHERE EMAIL IS NULL;
SELECT * FROM CUSTOMERS WHERE EMAIL IS NOT NULL;

연산자의 우선순위

우선순위	연산자
1	괄호 `(`, `)`
2	비교 연산자 `=`, `<>`, `<`, `>`, `<=`, `>=`
3	논리 연산자 `NOT`
4	논리 연산자 `AND`
5	논리 연산자 `OR`

트랜잭션

데이터베이스에서 하나의 논리적인 작업 단위를 의미한다. 일련의 작업이 모두 성공하거나 실패하는 것을 보장하며, 데이터의 무결성과 일관성을 유지하기 위한 기본 단위이다.

트랜잭션 특징 ACID

Atomicity(원자성)
- 트랜잭션은 모두 실행되거나, 모두 실행되지 않아야 함.
- 실패 시 모든 변경 사항을 ROLLBACK하여 원래 상태로 복구.
Consistency(일관성)
- 트랜잭션이 완료된 후 데이터베이스는 항상 일관된 상태를 유지해야 함.
- 데이터 무결성을 보장.
Isolation(고립성)
- 여러 트랜잭셔닝 동시에 실행될 때 서로 간섭하지 않도록 독립적으로 처리.
- 트랜잭션 격리 수준으로 구현.
- READ UNCOMMITED, READ COMMITED, REPEATABLE READ, SERIALIZABLE.
Durability(지속성)
- 트랜잭션이 성공적으로 완료되면 변경된 데이터는 영구적으로 저장.
- 시스템 장애 후에도 데이터는 유지.

트랜잭션 설계 시 유의 사항

작업 단위를 명확히 정의 : 트랜잭션 범위를 좁게 설계하여 성능과 오류 가능성을 최소화.
최소한의 데이터 변경 : 필요 이상의 데이터를 수정하거나 삭제하지 않도록 제한.
장기 트랜잭션 방지 : 트랜잭션이 너무 오래 실행되지 않도록 설계하여 데이터베이스 Lock 문제 방지.
적절한 격리 수준 설정 : 시스템 성능과 데이터 정확성 간의 균형을 유지.

이슬비

이전 포스트

[데이터베이스개론·SQL] 250113

다음 포스트

[데이터베이스개론·SQL] 250114