데이터베이스 설계

11 데이터베이스 구축
Section 1. 데이터베이스 개념

1. 데이터베이스 개념

⑴ 데이터와 정보
■ 데이터
- 현실 세계에서 단순히 관찰하거나 측정하여 수집한 사실이나 값
■ 정보
- 데이터를 의사 결정에 유용하게 활용할 수 있도록 처리하여 체계적으로 정리한 결과물

⑵ 데이터베이스의 정의
■ 통합 데이터(Integrated Data)
- 검색의 효율성을 위해 중복이 최소화 된 데이터의 모임
■ 저장 데이터(Stored Data)
- 컴퓨터가 접근 가능한 저장 매체에 저장된 데이터
■ 운영 데이터(Operational Data)
- 조직의 목적을 위해 존재 가치가 확실하고 반드시 필요한 데이터
■ 공유 데이터(Shared Data)
- 여러 응용 프로그램들이 공동으로 사용하는 데이터

⑶ 데이터베이스의 특징
- 실시간 접근성(Real Time Accessibility)
- 계속적인 변화(Continuous Evolution)
- 동시 공유(Concurrent Sharing)
- 내용에 의한 참조(Content Reference)
- 데이터의 독립성(Independence)

⑷ 데이터 언어
① DDL(Data Definition Language : 데이터 정의어)
- DB의 구조, 데이터 형식, 접근 방식 등 DB의 구축과 변경 목적으로 사용하는 언어

② DML(Data Manipulation Language : 데이터 조작어)
- 데이터의 검색, 삽입, 삭제, 갱신 연산 등을 포함한 집합

③ DCL(Data Control Language : 데이터 제어어)
- 보안 및 권한 제어, 무결성, 회복, 병행 제어를 위한 언어

⑸ 스키마(schema)
① 스키마의 정의
- 데이터베이스의 구조와 제약조건에 관해 전반적인 명세를 기술한 것

② 3계층 스키마
- 외부 스키마 (external schema) - 사용자 뷰
- 개념 스키마 (Conceptual schema) - 전체적인 뷰
- 내부 스키마 (Internal schema) - 저장 스키마

2. 데이터베이스 관리 시스템 (Database Management System)

⑴ DBMS의 정의
- 데이터베이스를 조작하는 별도의 소프트웨어

⑵ DBMS의 기능
- 데이터 정의
- 데이터 조작
- 데이터 제어
- 데이터 공유
- 데이터 보호
- 데이터 구축
- 유지보수

⑶ DBMS의 종류
- 계층형(Hierarchical DataBase)
- 네트워크형(Network DataBase)
- 관계형(Relational DataBase)
- 객체 지향형(Object-Oriented DataBase)
- 객체 관계형(Object-Relational DataBase)
- NoSQL
- NewSQL

Section 2. 데이터베이스 설계

1. 데이터베이스 설계 개요

⑴ 데이터베이스 설계 정의
- 요구조건에서부터 데이터베이스 구조를 도출해 내는 과정

⑵ 데이터베이스 설계 목적
- 이해관계자의 데이터 관점 요구사항에 대한 정확한 이해 및 추상화

⑶ 데이터베이스 설계 시 고려사항
- 제약조건
- 데이터베이스 무결성
- 일관성
- 회복
- 보안
- 효율성
- 데이터베이스 확장성

2. 데이터베이스 설계 단계

⑴ 요구조건 분석
- 데이터베이스의 사용자, 사용목적, 사용범위, 제약조건 등에 대한 내용을 정리하고 명세서를 작성
- 트랜잭션 유형, 트랜잭션 실행빈도와 같은 동적 DB 처리 요구조건 정의

⑵ 개념적 설계
- 현실세계를 데이터관점으로 추상화 단계
- DBMS 에 독립적으로 설계
- 데이터베이스의 개념적 스키마 구성(E-R 다이어그램)

⑶ 논리적 설계
- 자료를 컴퓨터가 이해할 수 있도록 특정 DBMS의 논리적 자료 구조로 변환하는 과정
- 특정 데이터모델(계층형, 관계형, 객체지향형 등)을 적용한 설계
- 데이터베이스의 논리적 스키마 생성
- 관계형 데이터베이스인 경우 이 단계에서 테이블을 설계하고, 정규화 과정
- 트랜잭션 인터페이스 설계

⑷ 물리적 설계
- 특정 DBMS의 물리적 구조와 내부적인 저장구조, 분산형태, 데이터타입의 특징, 인덱스의 특징 등을 구체화
하는 설계단계
- 레코드 집중의 분석 및 설계
- 오브젝트, 접근방법, 트랜잭션분석, 인덱스, 뷰, 데이터베이스 용량설계 등을 수행
- 데이터베이스의 물리적 스키마 생성
- 트랜잭션 세부 설계

⑸ 구현
- 목표 DBMS의 DDL로 기술된 명령문을 컴파일하고, 실행시켜 데이터베이스 스키마 생성

Section 3. 데이터 모델링

1. 데이터모델 개념

⑴ 데이터모델 개념
- 현실세계의 요소를 인간과 컴퓨터가 이해할 수 있는 정보로 표현한 것

⑵ 데이터모델 구조
■ 개체(Entity)
- 데이터베이스에 데이터로 표현하려고 하는 현실 세계의 대상체
■ 개체 타입 (Entity type)
- 개체를 구성하고 있는 속성들의 집합
■ 개체 인스턴스 (Entity instance)
- 데이터베이스에 저장되는 구체적인 객체
■ 개체 세트 (Entity set)
- 개체 인스턴스의 집합
■ 속성(Attribute)
- 데이터의 가장 작은 논리적 단위
■ 관계(Relation)
- 개체와 개체가 맺고 있는 의미 있는 연관성

⑶ 데이터모델 표시해야 할 요소
■ 구조(Structure)
- 데이터베이스에 표현될 대상으로서의 개체 타입과 개체 타입들간의 관계
■ 연산(Operation)
- 데이터베이스에 저장될 실제 데이터를 처리하는 방법
■ 제약조건(Constraint)
- 저장될 수 있는 데이터의 논리적인 제약조건

2. 개체-관계 모델 (Entity Relation Model)

⑴ 개체-관계 모델 개념
- 데이터베이스에 대한 요구 사항을 그래픽적으로 표현하는 방법

⑵ 개체 (Entity)
- 현실 세계에서 꼭 필요한 사람이나 사물과 같이 구별되는 모든 것

⑶ 애트리뷰트, 속성(Attribute)
- 개체나 관계가 가지고 있는 고유의 특성
■ 속성의 유형
속성 유형

설명

단일 값 속성
- 값을 하나만 가질 수 있는 속성 (ex. 이름, 학번 등)

다중 값 속성
- 값을 여러 개 가질 수 있는 속성 (ex. 취미 등)

단순 속성
- 의미를 더는 분해할 수 없는 속성 (ex. 성별 등)

복합 속성
- 의미를 분해할 수 있는 속성 (ex. 주소, 생년월일 등)

유도 속성
- 기존의 다른 속성의 값에서 유도되어 결정되는 속성 (ex. 주민번호와 성별)

널 속성
- 아직 결정되지 않은 존재 하지 않는 값

키 속성
- 각 개체를 식별하는데 사용하는 속성

⑷ 관계 (Relationship)
- 서로 다른 개체가 맺고 있는 의미 있는 연관성

⑸ E-R 다이어그램 기호
기호

기호 이름

설명

사각형
- 개체(Entity)

마름모
- 관계(Relationship)

타원
- 속성(Attribute)

밑줄 타원
- 기본키 속성

이중 타원
- 복합속성

선 링크
- 개체와 속성 연결

3. 데이터 모델의 품질 기준

기준항목
정확성

완전성
준거성
최신성

일관성

활용성

설명
데이터 모델이 표기법에 따라 정확하게 표현되었고, 업무영역 또는 요구사항이 정확하게
반영되었음을 의미함
데이터 모델의 구성 요소를 정의하는데 있어서 누락을 최소화하고, 요구사항 및 업무영역
반영에 있어서 누락이 없음을 의미함
제반 준수 요건들이 누락 없이 정확하게 준수되었음을 의미함
데이터 모델이 현행 시스템의 최신 상태를 반영하고 있고, 이슈사항들이 지체없이 반영되고
있음을 의미
여러 영역에서 공통 사용되는 데이터 요소가 전사 수준에서 한 번만 정의되고 이를 여러
다른 영역에서 참조·활용되면서, 모델 표현상의 일관성을 유지하고 있음을 의미함
작성된 모델과 그 설명 내용이 이해관계자에게 의미를 충분하게 전달할 수 있으면서, 업무
변화 시에 설계 변경이 최소화되도록 유연하게 설계되어 있음을 의미

Section 4. 논리 데이터베이스 설계

1. 논리적 데이터 모델링

⑴ 논리적 모델링
- 개념적 설계에서 추출된 실체와 속성들의 관계를 구조적으로 설계하는 단계

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

⑴ 정규화의 개념
- 관계형 데이터베이스의 설계에서 중복을 최소화하게 데이터를 구조화

⑵ 정규화의 목적
- 데이터의 중복을 최소화
- 정보의 무손실 : 정보가 사라지지 않아야 함
- 독립적인 관계는 별개의 릴레이션으로 표현
- 정보의 검색을 보다 용이하게 함
- 이상현상 최소화

⑶ 이상(Anomaly) 현상
- 데이터 중복으로 인해 릴레이션 조작 시 예상하지 못한 곤란한 현상이 발생
■ 이상의 종류
- 삽입 이상 : 데이터를 삽입할 때 불필요한 데이터가 함께 삽입되는 현상
- 삭제 이상 : 한 튜플을 삭제할 때 연쇄 삭제 현상으로 인해 정보 손실
- 갱신 이상 : 튜플의 속성값을 갱신할 때 일부 튜플의 정보만 갱신되어 정보에 모순이 생기는 현상

⑷ 함수적 종속 (Functional Dependency)
① 함수적 종속의 개념
- 어떤 릴레이션 R이 있을때 X와 Y를 각각 속성의 부분집합이라고 가정했을 때
- X의 값을 알면 Y의 값을 바로 식별할 수 있고, X의 값에 Y의 값이 달라질 때, Y는 X에 함수적 종속이라고 함
- 이를 기호로 표현하면 X→Y

② 함수적 종속 관계
- 완전 함수적 종속 (Full Functional Dependency)
- 부분 함수적 종속 (Partial Functional Dependency)
- 이행적 함수 종속 (Transitive Functional Dependecy)

⑸ 정규화 과정

Section 5. 물리 데이터베이스 설계

1. 물리 데이터베이스 설계

⑴ 물리 데이터베이스 설계 과정
- 사용자 DBMS 결정
- 데이터 타입 크기 결정
- 데이터 용량, 설계 및 업무 프로세스 분석
- 역정규화(반정규화)
- 인덱스 정의
- 데이터베이스 생성

⑵ 물리 데이터베이스 설계시 고려사항
■ 반응시간
- 응답시간을 최소화 해야 한다.
■ 트랜잭션 처리량
- 얼마나 많은 트랜잭션을 동시에 발생시킬 수 있는지 검토한다.
■ 공간활용
- 데이터가 저장될 공간을 효율적으로 배치한다.

⑶ 데이터베이스 암호화 방식
■ API방식
- 데이터베이스 솔루션 외부의 어플리케이션에서 데이터의 암/복화화를 수행
■ Plug-in 방식
- 데이터베이스 서버에 제품을 설치 → 암/복호화 수행
■ TDE(Transparent Data Encryption)방식
- DBMS 암호화 기능을 이용하여 데이터 파일 저장시 암호화
- 파일에 저장된 내용을 메모리로 가져올 때 DBMS에 의해 복호화
■ 파일암호화 방식
- 데이터뿐만 아니라 비정형 데이터 암호화 적용가능
■ 하드웨어방식
- 별도의 하드웨어 장비를 외부에 설치

2. 반정규화

⑴ 반정규화의 개념
- 데이터베이스 정규화 이후, 성능향상과 개발 편의성 등 정규화 기법에 위배되는 수행 기법

⑵ 반정규화시 고려사항
- 데이터의 중복이 발생하여 데이터 수정시 무결성이 깨질 수 있다.
- 읽기 속도는 향상되지만, 삽입/삭제/수정 속도는 느려짐
- 저장공간의 효율이 떨어짐
- 테이블이 크고 복잡해져 유지보수가 어려움
- 과도한 반정규화는 오히려 성능을 저하시킴
- 반정규화를 위해서는 사전에 데이터의 일관성과 무결성을 우선으로 할지, 데이터베이스의 성능과 단순화를 우선으로 할지를 결정해야 함

⑶ 반정규화의 적용순서
- 반정규화의 대상을 조사한다.
- 다른 방법으로 유도한다.
- 반정규화 수행

⑷ 반정규화의 유형
구분
테이블 분할

테이블 중복

컬럼기반 분할

컬럼 중복

유형

설명

수평분할
- 레코드 단위로 분할

수직분할
- 컬럼 단위로 분할

통계 테이블 추가
- DW, OLAP 데이터 용

진행 테이블 추가
- 업무 프로세스 상태

조회 빈도 기반
- 고빈도 컬럼 분리

크기 기반 분할
- 일정 용량 컬럼 분리

중복 컬럼 추가
- 자주 조회되는 컬럼 추가

파생 컬럼 추가
- 연산 결과 별도 저장

4. 데이터베이스 이중화

⑴ 데이터베이스 이중화 구성
- 장애발생시 데이터베이스를 보호하기 위한 방법으로 동일한 데이터베이스를 중복시켜 동시에 갱신하여 관리하는 방법
- 서버와 네트워크, 프로그램 등의 정보 시스템이 지속적으로 정상 운영이 가능한 고가용성(HA, High Availability) 서버로 구성하는 것

⑵ 데이터베이스 이중화의 분류
■ Eager 기법
- 트랜잭션 수행 중에 발생한 변경은 발생 즉시 모든 이중화서버로 전달되어 연쇄적으로 변경 내용이 반영
■ Lazy 기법
- 트랜잭션의 수행이 완전히 완료된 후에 그 변경 사실에 대한 새로운 트랜잭션을 작성하여 각 노드에게
전달하는 기법

⑶ 데이터베이스 이중화의 종류
① Active-Active
- 다중화된 장비가 모두 가동되는 방식

② Active-Standby
- 두 대 중 하나는 가동이 되고, 하나는 장애 상황의 경우를 대비해서 준비 상태로 대기
■ Active-Standby 타입
Hot Standby
- Standby 장비가 가동되었을 때 즉시 사용가능

Warm Standy
- Standby 장비가 가동되었을 때 설정에 대한 준비가 필요함

Cold Standby
- Standby 장비를 평소에는 정지시켜두며 필요에 따라서 직접 켜서 구성을 함

5. 데이터베이스 백업

⑴ 백업 방식
■ 전체 백업(Full Backup)
- 선택된 폴더의 DATA를 모두 백업하는 방식
■ 증분 백업(Incremental Backup)
- Full 백업 이후 변경/추가된 Data만 백업하는 방식
■ 차등 백업(Differential Backup)
- Full 백업 이후 변경/추가된 Data를 모두 포함하여 백업
■ 실시간 백업(RealTime Backup)
- 즉각적으로 모든 변경사항을 분리된 스토리지 디바이스에 복사
■ 트랜잭션 로그 백업(Transaction log Backup)
- 데이터베이스에서 실행되는 모든 SQL문을 기록한 로그
- REDO(다시 실행), UNDO(원상태로 복구) 로 복원
- CHECK POINT : 설정한 지점 이전 까지는 트랜잭션이 성공적으로 수행이 돼서 disk에 확실히 저장된 상태

⑵ 복구시간목표/복구시점 목표
■ 복구 시간 목표(RTO)
- 서비스 중단 시점과 서비스 복원 시점 간에 허용되는 최대 지연 시간
■ 복구 시점 목표(RPO)
- 마지막 데이터 복구 시점 이후 허용되는 최대 시간

Section 6. 데이터베이스 물리속성 설계

1. 파티셔닝

⑴ 파티셔닝 개념
- 데이터베이스를 여러 부분으로 분할하는 것

⑵ 분할 기준
① 범위 분할 (range partitioning)
- Partition Key 의 연속된 범위로 파티션을 정의
- 파티션 키 위주로 검색이 자주 실행될 경우 유용
예) 월별, 분기별 등

② 목록 분할 (list partitioning)
- 특정 Partition 에 저장 될 Data 에 대한 명시적 제어
- 많은 SQL 에서 해당 Column 의 조건이 많이 들어오는 경우 유용
ex) [한국, 일본, 중국 → 아시아] [노르웨이, 스웨덴, 핀란드 → 북유럽]

③ 해시 분할 (hash partitioning)
- 파티션 키 값에 해시 함수를 적용하고, 거기서 반환된 값으로 파티션 매핑
- 데이터가 모든 파티션에 고르게 분산되도록 DBMS가 관리
- 병렬처리 시 성능효과 극대화

④ 라운드 로빈 분할 (round robin partitioning)
- Data를 균일하게 분배해서 저장하는 방식

⑤ 합성 분할 (composite partitioning)
- 위의 기술들을 복합적으로 사용하는 방법
ex) 범위 분할 후 분할 된 데이터를 해시 분할하는 등

2. 클러스터 설계

⑴ 클러스터의 개념
- 디스크로부터 데이터를 읽어오는 시간을 줄이기 위해서 조인이나 자주 사용되는 테이블의 데이터를 디스크의 같은 위치에 저장시키는 방법

⑵ 클러스터 대상 테이블
- 분포도가 넓은 테이블
- 대량의 범위를 자주 조회하는 테이블
- 입력, 수정, 삭제가 자주 발생하지 않는 테이블
- 자주 JOIN 되어 사용되는 테이블
- ORDER BY, GROUP BY, UNION 이 빈번한 테이블

3. 인덱스(Index)

⑴ 인덱스의 개념
- 추가적인 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조

⑵ 인덱스의 종류
■ 클러스터 인덱스
- 테이블당 1개만 허용되며, 해당 컬럼을 기준으로 테이블이 물리적으로 정렬
■ 논클러스터 인덱스
- 테이블당 약 240개의 인덱스 생성 가능
- 레코드의 원본은 정렬되지 않고, 인덱스 페이지만 정렬
■ 밀집 인덱스
- 데이터 레코드 각각에 대해 하나의 인덱스가 만들어짐
■ 희소 인덱스
- 레코드 그룹 또는 데이터 블록에 대해 하나의 인덱스가 만들어짐

⑶ 인덱스 생성/삭제
① 인덱스 생성
--문법
CREATE INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, 컬럼3.......)
--예제
CREATE INDEX USER_INDEX ON TB_USER(NAME,HP);


② 인덱스 삭제
--문법
DROP INDEX 인덱스 명;
--예제
DROP INDEX USER_INDEX;

4. 뷰(View)

⑴ 뷰의 개념
- 하나 이상의 기본 테이블로부터 유도된, 이름을 가지는 가상 테이블

⑵ 뷰 생성/삭제
① 뷰 생성
--문법
CREATE VIEW [view_name]AS
SELECT [field_name_1], [field_name_2]
FROM [table_name]
WHERE [조건];
--예제
CREATE OR REPLACE VIEW USER_VIEW AS
SELECT
id, user
FROM TB_USER
WHERE name like ‘이%’
;

② 뷰 삭제
--문법
DROP VIEW 뷰 명;
--예제
DROP VIEW USER_VIEW;

5. 시스템 카탈로그

⑴ 시스템 카탈로그
- 데이터베이스 관리자의 도구로, 데이터베이스에 저장되어 있는 모든 데이터 개체들에 대한 정의나 명세에 대한 정보가 수록되어 있는 시스템 테이블

⑵ 시스템 카탈로그의 내용
- 릴레이션 관련 정보
- 인덱스 관련 정보
- 뷰 관련 정보
- 통계 관련 정보
- 사용자 관련 정보

⑶ 시스템 카탈로그의 특징
- 시스템 카탈로그 자체도 시스템 테이블로 구성되어 있어 사용자가 SQL문을 이용하여 내용을 검색해 볼 수 있다.
- 시스템 카탈로그는 데이터베이스 관리 시스템에 의해 생성되고 유지된다.
- 사용자가 시스템 카탈로그를 직접 갱신하는 것은 허용되지 않는다.

⑷ 시스템 카탈로그의 종류
■ SYSTABLES
- 기본 테이블 및 뷰 테이블의 정보를 저장하는 테이블
■ SYSCOLUMNS
- 모든 테이블에 대한 정보를 열(속성) 중심으로 저장하는 테이블
■ SYSVIEW
- 뷰에 대한 정보를 저장하는 테이블
■ SYSTABAUTH
- 테이블에 설정된 권한 사항들을 저장하는 테이블
■ SYSCOLAUTH
- 각 속성에 설정된 권한 사항들을 저장하는 테이블

Section 7. 관계 데이터베이스 모델

1. 관계 데이터 모델

⑴ 관계 데이터 모델 개념
- 데이터의 논리적 구조가 릴레이션, 즉 테이블 형태의 평면 파일로 표현되는 데이터 모델

⑵ 관계 데이터 릴레이션의 구조
<학생 릴레이션>
속성(Attribute)

커디널리티

↓

↓

↓

↓

↓

학번

이름

학년

학과

성별

001

이흥직

3

컴퓨터

남

↔

002

이경직

1

철학

여

← 튜플

003

이창훈

2

체육

남

←

↓
차수

성별의 도메인

2. 관계데이터 언어(관계대수, 관계해석)

⑴ 관계 대수의 개념
- 원하는 데이터를 얻기 위해, 데이터를 어떻게 찾는지에 대한 처리 과정을 명시하는 절차적인 언어

⑵ 관계해석
- 관계 데이터 모델의 제안자인 코드(E. F. Codd)가 수학의 Predicate Calculus(술어 해석)에 기반을 두고 관계 데이터베이스를 위해 제안
- 관계해석은 원하는 정보가 무엇이라는 것만 정의하는 비절차적 특성

Section 8. 키와 무결성 제약조건

1. 컬럼

⑴ 컬럼의 개념
- 릴레이션에서 정보를 나타내는 최소 단위로, 각 열의 상태나 특성을 나타내는 항목을 말한다.

⑵ 속성의 분류
■ 기본속성
- 업무로부터 추출한 모든 속성
■ 설계속성
- 코드성 데이터, 릴레이션 식별용 일련번호
■ 파생속성
- 다른 속성에 영향을 받아 발생하는 속성
- 계산값, 합계, 재고 등

⑶ 세부 의미에 따른 분류
■ 단순 속성(Simple Attribute)
- 나이, 성별같이 다른 속성들로 구성될 수 없는 단순한 속성
■ 복합 속성(Composite Attribute)
- 주소와 같이 시, 구, 동처럼 여러 세부 속성들로 구성될 수 있는 속성

⑷ 구성방식 따른 분류
■ PK(Primary Key) 속성
- 릴레이션에서 튜플을 유일하게 구분할 수 있는 속성
■ FK(Foreign Key) 속성
- 다른 릴레이션과의 관계에서 참조하고 있는 속성
■ 일반 속성
- 릴레이션에 포함된 속성 중, PK와 FK가 아닌 속성

⑸ 도메인
- 속성이 가질 수 있는 값의 범위

2. 키 종류

⑴ 키(Key)의 개념
- 데이터베이스에서 조건에 만족하는 튜플을 찾거나 순서대로 정렬할 때 다른 튜플들과 구별할 수 있는 유일한 기준이 되는 컬럼

⑵ 키(Key)의 종류
① 후보키 (Candidate Key)
- 릴레이션을 구성하는 속성들 중에서 튜플을 유일하게 식별할 수 있는 속성들의 부분집합
- 튜플에 대한 유일성과 최소성을 만족시켜야 한다.

② 기본키 (Primary Key)
- 후보키 중에서 선택한 주키(Main Key)
- Null 값을 가질 수 없다. (개체 무결성)
- 기본키로 정의된 속성에는 동일한 값이 중복되어 저장될 수 없다. (개체 무결성)

③ 대체키 (Alternate Key)
- 후보키가 둘 이상일 때 기본키를 제외한 나머지 후보키

④ 슈퍼키 (Super Key)
- 한 릴레이션 내에 있는 속성들의 집합으로 구성된 키

⑤ 외래키 (Foreign Key)
- 관계(Relation)를 맺고 있는 릴레이션 R1, R2에서 릴레이션 R1이 참조하고 있는 릴레이션 R2의 기본키와 같은 R1 릴레이션의 속성

3. 데이터베이스 무결성

⑴ 데이터베이스 무결성 개념
- 데이터의 정확성, 일관성, 유효성이 유지되는 것

⑵ 데이터베이스 무결성 종류
① 개체 무결성 (Entity integrity)
- 모든 릴레이션은 기본 키(primary key)를 가져야 한다
- 기본키는 중복되지 않은 고유한 값을 가져야한다.
- 릴레이션의 기본키는 NULL 값을 허용하지 않는다.

② 참조 무결성 (Referential integrity)
- 외래키 값은 NULL이거나 참조하는 릴레이션의 기본키 값과 동일해야 한다.
- 각 릴레이션은 참조할 수 없는 외래키 값을 가질 수 없다.
■ 참조 무결성 제약조건
종류
제한(Restricted)
연쇄(Cascade)

널값(Nullify)
기본값(Default)

설명
- 문제가 되는 연산을 거절
- 참조되는 릴레이션에서 튜플을 삭제하고 참조하는 릴레이션에서 이 튜플을 참조하는
튜플도 함께 삭제
- 참조되는 릴레이션에서 튜플을 삭제하고 참조하는 릴레이션에서 이 튜플을 참조하는
튜플들의 외래키에 NULL 등록
- Null을 넣는 대신에 디폴트 값을 등록

③ 도메인 무결성 (Domain integrity)
- 속성들의 값은 정의된 도메인에 속한 값이어야 한다.
- 성별이라는 컬럼에는 ‘남’, ‘여’를 제외한 데이터는 제한되어야 한다.

Section 9. 물리데이터 모델 품질 검토

1. CRUD 분석

⑴ CRUD의 개념
- 데이터 처리 기능인 Create(생성), Read(읽기), Update(갱신), Delete(삭제)를 묶어서 표현한 말이다.

⑵ CRUD의 필요성
- 모델링 작업검증
- 중요 산출물
- 테스트 시 사용
- 인터페이스 현황 파악

2. 옵티마이저

⑴ SQL 처리 흐름

⑵ 옵티마이저 개념
- 사용자가 질의한 SQL 문에 대해 최적의 실행 방법을 결정하는 역할을 수행
■ 옵티마이저의 구분
㉠ 규칙기반 옵티마이저 (Rule Based Optimizer)
- 규칙(우선순위)를 가지고 실행 계획을 생성
- 인덱스 유무, 연산자, 객체 등을 참조하여 우선순위를 부여
㉡ 비용기반 옵티마이저 (Cost Based Optimizer)
- SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식
- 소요시간 이나 자원 사용량을 가지고 실행계획 생성
- 테이블, 인덱스, 컬럼 등의 다양한 객체 통계정보와 시스템 통계정보 활용

3. SQL 성능 튜닝

⑴ 튜닝의 개념
- SQL 문을 최적화하여 빠른 시간내에 원하는 결과값을 얻기 위한 작업

⑵ SQL 성능 최적화를 위한 유틸리티
- SQL Trace
- TKPROF(Trace Kernel PROFile)
- EXPLAIN PLAN

Section 10. 분산 데이터베이스

1. 분산 데이터베이스

⑴ 분산 데이터베이스(Distribute Database)의 정의
- 여러 곳으로 분산되어있는 데이터베이스를 하나의 가상 시스템으로 사용할 수 있도록 한 데이터베이스

⑵ 분산 데이터베이스 구성요소
■ 분산 처리기
- 자체적으로 처리 능력을 가지며, 지리적으로 분산되어 있는 컴퓨터 시스템
■ 분산 데이터베이스
- 지리적으로 분산되어 있는 데이터베이스로서 해당 지역의 특성에 맞게 데이터베이스가 구성
■ 통신 네트워크
- 분산처리기들을 통신망으로 연결하여 논리적으로 하나의 시스템처럼 작동할 수 있도록 하는 통신 네트워크

⑶ 투명성 조건
■ 위치 투명성 (Location Transparency)
- 액세스하려는 데이터베이스의 실제 위치를 알 필요없이 단지 데이터베이스의 논리적인 명칭만으로 액세스할 수 있음
■ 분할 투명성 (Division Transparency)
- 하나의 논리적 테이블이 여러 단편으로 분할되어 각 단편의 사본이 여러 위치에 저장
■ 지역사상 투명성 (Local Mapping transparency)
- 지역DBMS와 물리적 DB사이의 Mapping 보장. 각 지역시스템 이름과 무관한 이름 사용 가능
■ 중복 투명성 (Replication Transparency)
- 동일 데이터가 여러 곳에 중복되어 있더라도 사용자는 마치 하나의 데이터만 존재하는 것처럼 사용하고,
시스템은 자동으로 여러 자료에 대한 작업을 수행함
■ 병행 투명성 (Concurrency Transparency)
- 분산 데이터베이스와 관련된 다수의 트랜잭션들이 동시에 실현되더라도 그 트랜잭션의 결과는 영향을 받지 않음
■ 장애 투명성 (Failure Transparency)
- 트랜잭션, DBMS, 네트워크, 컴퓨터 장애에도 불구하고 트랜잭션을 정확하게 처리함

⑷ CAP 이론
① 개념
- 어떤 분산환경에서도 일관성(C), 가용성(A), 분할내성(P) 세 가지 속성 중, 두 가지만 가질 수 있다는 것
- 3가지 모두 만족할 수는 없다.

② 특징의 의미
■ 일관성(Consistency)
- 모든 노드들은 같은 시간에 동일한 항목에 대하여 같은 내용의 데이터를 사용자에게 보여준다.
■ 가용성(Availability)
- 모든 사용자들이 읽기 및 쓰기가 가능해야 하며, 몇몇 노드의 장애 시에도 다른 노드에 영향을 미치면 안된다.
■ 분할내성(Partition tolerance)
- 메시지 전달이 실패하거나 시스템 일부가 망가져도 시스템이 계속 동작할 수 있어야 한다.

2. 트랜잭션

⑴ 트랜잭션의 개념
- 데이터베이스의 상태를 변환시키는 하나의 논리적인 기능을 수행하는 작업 단위

⑵ 트랜잭션의 성질
■ 원자성 (Atomicity)
- 트랜잭션의 연산은 데이터베이스에 모두 반영되든지 아니면 전혀 반영되지 않아야 한다.
- Commit과 Rollback 명령어에 의해 보장 받는다.
■ 일관성 (Consistency)
- 트랜잭션이 그 실행을 성공적으로 완료하면 언제나 일관성 있는 데이터베이스 상태로 변환한다.
■ 독립성, 격리성 (Isolation)
- 둘 이상의 트랜잭션이 동시에 병행 실행되는 경우 어느 하나의 트랜잭션 실행중에 다른 트랜잭션의 연산이 끼어들 수 없다.
■ 영속성 (Durablility)
- 성공적으로 완료된 트랜잭션의 결과는 시스템이 고장나더라도 영구적으로 반영되어야 한다.

⑶ 트랜잭션의 상태

12 SQL 활용
Section 1. 기본 SQL 작성

1. SQL (Structured Query Language)

⑴ SQL의 개념
- 데이터베이스 시스템에서 자료를 처리하는 용도로 사용되는 구조적 데이터 질의 언어

⑵ SQL 문법의 종류
① Data Definition Language (DDL) - 데이터 정의어
- 데이터가 저장되는 테이블이나 각종 개체들을 정의하는데 사용되는 명령
- CREATE, ALTER, DROP, RENAME, TRUNCATE

② Data Manipulation Language (DML) - 데이터 조작어
- 데이터베이스 내의 데이터를 조작(추출, 생성, 수정, 삭제) 하는 명령
- SELECT, INSERT, UPDATE, DELETE

③ Data Control Language (DCL) - 데이터 제어어
- 데이터베이스에 접근하고, 객체들을 사용하도록 권한을 주고 회수하는 명령
- GRANT, REVOKE

④ Transaction Control Language (TCL) - 트랜잭션 제어어
- 논리적인 작업의 단위를 묶어 이에 의해 조작된 결과를 작업단위로별로 제어하는 명령어
- COMMIT, ROLLBACK, SAVEPOINT


Section 4. 절차형 SQL
1. 저장 프로시저 (Stored Procedure)
⑴ 저장 프로시저의 개념
- 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합

⑵ 저장 프로시저의 구조
CREATE OR REPLACE PROCEDURE 프로시저명
( 변수1 IN 변수타입, 변수2 OUT 변수타입, 변수3 IN OUT 변수타입.... )
IS
변수 처리부
BEGIN
처리내용
EXCEPTION
예외처리부
END;

2. 트리거
⑴ 트리거의 개념
- 테이블에 대한 이벤트에 반응해 자동으로 실행되는 작업

⑵ 트리거의 유형
■ 행 트리거
- 테이블 안의 영향을 받은 행 각각에 대해 실행
- FOR EACH ROW 옵션 사용
■ 문장 트리거
- INSERT, UPDATE, DELETE 문에 대해 단 한번만 실행

⑶ 트리거의 실행 시기
- BEFORE : 이벤트 전
- AFTER : 이벤트 후

⑷ 트리거 생성 예
CREATE TRIGGER TRIGGER_GOODS_STOCK
AFTER INSERT ON TB_GOODS_STOCK FOR EACH ROW
BEGIN
UPDATE TB_GOODS
SET
nStock = nStock + NEW.nStock
WHERE idx = NEW.p_idx;
END

3. 사용자 정의 함수
⑴ 사용자 정의 함수의 개념
- 프로시저와 사용자 정의 함수 모두 호출하게 되면 미리 정의 해놓은 기능을 수행하는 모듈
- 프로그램 로직을 도와주는 역할을 한다.
- 파라미터는 입력 파라미터만 가능하고, 리턴값이 하나이다.

⑵ 사용자 정의 함수의 구조
CREATE OR REPLACE FUNCTION 함수명
( 매개변수1, 매개변수2, 매개변수3,....... )
RETURN 데이터 타입
IS
변수 처리부
BEGIN
처리내용
RETURN 반환값;
EXCEPTION
예외처리부
END;


13 병행제어와 데이터전환
Section 1. 병행제어와 회복
1. 병행제어
⑴ 병행제어의 개념
- 여러 트랜잭션들이 동시에 실행되면서도 데이터베이스의 일관성을 유지할 수 있게 하는 기법

⑵ 병행제어의 문제점
① 갱신 분실 (Lost Update)
- 두 개 이상의 트랜잭션이 같은 자료를 공유하여 갱신할 때 갱신 결과의 일부가 없어지는 현상

② 비완료 의존성 (Uncommitted Dependency)
- 하나의 트랜잭션 수행이 실패한 후 회복되기 전에 다른 트랜잭션이 실패한 갱신 결과를 참조하는 현상

③ 모순성 (Inconsistency)
- 두 개의 트랜잭션이 병행수행될 때 원치 않는 자료를 이용함으로써 발생하는 문제
- 갱신 분실과 비슷해 보이지만 여러 데이터를 가져올 때 발생하는 문제

④ 연쇄 복귀 (Cascading Rollback)
- 병행수행되던 트랜잭션들 중 어느 하나에 문제가 생겨 Rollback하는 경우 다른 트랜잭션도 함께 Rollback되는 현상

⑶ 병행제어 기법
① 로킹(Locking)
- 트랜잭션이 어떤 데이터에 접근하고자 할 때 로킹 수행
■ 로킹 단위에 따른 구분
구분

로크 수

병행성

오버헤드

로킹 단위가 크면

적어짐

낮아짐

감소

로킹 단위가 작으면

많아짐

높아짐

증가

② 2단계 로킹 규약(Two-Phase Locking Protocol)
- Lock과 Unlock이 동시에 이루어지면 일관성이 보장되지 않으므로 Lock만 가능한 단계와 Unlock만 가능한
단계를 구분
- 확장단계: 새로운 Lock은 가능하고 Unlock은 불가능하다.
- 축소단계: Unlock 은 가능하고 새로운 Lock은 불가능하다.

③ 타임스탬프(Time Stamp)
- 데이터에 접근하는 시간을 미리 정하여서 정해진 시간(Time Stamp)의 순서대로 데이터에 접근하여 수행

④ 낙관적 병행제어(Optimistic Concurrency Control)
- 트랜잭션 수행 동안은 어떠한 검사도 하지 않고, 트랜잭션 종료 시에 일괄적으로 검사

⑤ 다중 버전 병행제어(Multi-version, Concurrency Control)
- 여러 버전의 타임스탬프를 비교하여 스케줄상 직렬가능성이 보장되는 타임스탬프를 선택

2. 회복 (Database Recovery)
⑴ 회복의 개념
- 트랜잭션들을 수행하는 도중 장애로 인해 손상된 데이터베이스를 손상되기 이전의 정상적인 상태로 복구시키는 작업

⑵ 장애의 유형
- 트랜잭션 장애
- 시스템 장애
- 미디어 장애

⑶ Undo와 Redo
■ Undo
- 트랜잭션 로그를 이용하여 오류와 관련된 모든 변경을 취소하여 복구 수행
■ Redo
- 트랜잭션 로그를 이용하여 오류가 발생한 트랜잭션을 재실행하여 복구 수행

⑷ 로그 파일
- 트랜잭션이 반영한 모든 데이터의 변경사항을 데이터베이스에 기록하기 전에 미리 기록해두는 별도의 파일

⑸ 회복 기법
① 로그 기반 회복 기법
■ 지연갱신 회복 기법(Deferred Update)
- 트랜잭션의 부분 완료 상태에선 변경 내용을 로그 파일에만 저장
- 중간에 장애가 생기더라도 데이터베이스에 기록되지 않았으므로 UNDO가 필요 없음(미실행 된 로그 폐기)

■ 즉시갱신 회복 기법(Immediate Update)
- 트랜잭션 수행 도중에도 변경 내용을 즉시 데이터베이스에 기록
- 커밋 발생 이전의 갱신은 원자성이 보장되지 않는 미완료 갱신이므로 장애 발생 시 UNDO 필요

② 검사점 회복 기법 (Checkpoint Recovery)
Ÿ 장애 발생 시 검사점(Checkpoint) 이전에 처리된 트랜잭션은 회복에서 제외하고 검사점 이후에 처리된
트랜잭션은 회복 작업 수행

③ 그림자 페이징 회복 기법 (Shadow Paging Recovery)
Ÿ 트랜잭션이 실행되는 메모리상의 Current Page Table과 하드디스크의 Shadow Page Table 이용

④ 미디어 회복 기법 (Media Recovery)
Ÿ 디스크와 같은 비휘발성 저장 장치가 손상되는 장애 발생을 대비한 회복 기법

⑤ ARIES 회복 기법

Section 2. 데이터 전환
1. ETL(Extraction, Transformation, Loading)
⑴ ETL 개념
- 다양한 소스 시스템(source system)으로부터 필요한 데이터를 추출(extract)하여 변환(transform) 작업을 거쳐
타깃 시스템(target system)으로 전송 및 로딩(loading)하는 모든 과정

⑵ ETL 기능
■ Extraction (추출)
- 하나 또는 그 이상의 데이터 원천들로부터 데이터 획득
■ Transformation (변환)
- 데이터 클렌징, 형식 변환 및 표준화, 통합 또는 다수 애플리케이션에 내장된 비즈니스룰 적용 등
■ Load (적재)
- 변형 단계의 처리가 완료된 데이터를 특정 목표 시스템에 적재