[정보처리기사] 3과목 - 데이터베이스 구축(1)

논리 DB 설계

---

데이터베이스 설계

- 사용자의 요구를 분석하여 그것에 맞게 설계하고 특정 DBMS로 DB를 구현하여 사용자들이 사용하는 것

- 무결성(정확성), 일관성, 회복, 보안, 효율성, DB확장

 

 

DB 설계 순서

- 요구 조건 분석 -> 개념적 설계 -> 논리적 설계 -> 물리적 설계 -> 구현

 

1. 요구 조건 분석

- DB를 사용할 사람들로부터 필요한 용도를 파악

- 수집된 정보를 바탕으로 요구 조건 명세 작성

 

2. 개념적 설계(정보 모델링, 개념화)

- 현실 세계에 대한 인식을 추상적 개념으로 표현하는 과정

- 개념 스키마, E-R 모델, 트랜잭션 모델링

 

3. 논리적 설계(데이터 모델링)

- DBMS가 자원하는 논리적 자료 구조로 변환시키는 과정

- 논리 스키마 설계, 트랜잭션 인터페이스 설계, 관계형 DB-Table, 계층형 DB-Free, 망형 DB-Graph

 

4. 물리적 설계(데이터 구조화)

- 논리적 설계 단계에서 표현된 데이터를 물리적 저장장치에 저장할 수 있는 물리적 구조의 데이터로 변환하는 과정

 

5. 구현

- 논리적 설계와 물리적 설계 단계에서 도출된 DB스키마를 파일로 생성하는 과정

 

 

데이터 모델

- 현실 세계의 정보들을 컴퓨터에 표현하기 위해 단순화, 추상화하여 체계적으로 표현한 개념적 모델

- 종류: 개념적 데이터 모델, 논리적 데이터 모델, 물리적 데이터 모델

 

표시할 요소

- 구조(Structure): 논리적으로 표현된 개체 타입들 간 관계로서 데이터 구조 및 정적 성질 표현

- 연산(Operation): DB에 저장된 실제 데이터를 처리하는 작업에 대한 명세, DB를 조작하는 기본 도구

- 제약조건(Constraint): 실제 데이터의 논리적인 제약 조건

 

구성요소

개체(Entity)

- DB에 표현하려는 것으로 현실 세계의 대상체

- 속성: 개체가 가지고 있는 특성

- 개체 인스턴스: 개체를 구성하고 있는 각 속성들이 값을 가져 하나의 개체를 나타내는 것

- 유일한 식별자에 의해 식별 가능

- 다른 개체와 하나 이상의 관계가 존재

- 개체(튜플)의 수를 카디널리티라고 함

- 자료 흐름도(DFD)를 통해 업무 분석을 수행했을 경우 자료 흐름도의 자료 저장소(Data Store)를 이용

속성(Attribute)

- DB를 구성하는 가장 작은 논리적 단위

- 파일 데이터 항목 또는 데이터 필드에 해당

- 개체의 특성을 기술

- 속성의 수를 차수 혹은 디그리(Degree)라고 함

- 종류: 기본 속성, 설계 속성, 파생 속성

- 분류: 기본키 속성, 외래키 속성, 일반 속성

관계(Relationship)

- 개체 간 논리적인 연결

- 1:1, 1:N, N:M 3가지 관계 존재

- 종속관계(Dependent Relationship): 두 개체 사이 주종 관계를 표현

- 중복관계(Redundant Relationship): 두 개체 사이 2번 이상의 종속 관계 발생

- 재귀관계(Recursive Relationship): 개체가 자기 자신과 관계를 갖는 것

- 배타관계(Exclusive Relationship): 개체 속성이나 구분자를 기준으로 개체 특성을 분할

 

+) 식별관계: 개체 A, B 사이 관계에서 A 개체의 기본키가 B 개체의 외래키면서 동시에 기본키

+) 비식별관계: 개체 A, B 사이 관계에서 A 개체의 기본키가 B 개체의 비기본키 영역에서 외래키가 되는 관계

 

 

식별자(Indentifier)

- 하나의 개체 내에서 각각의 인스턴트를 유일하게 구분 지을 수 있는 것

 

분류

- 대표성 여부

: 주식별자(개체를 대표하는 유일한 식별자, 유일성/최소성/불변성/존재성), 보조식별자(주 식별자를 대신하여 개체를 식별할 수 있는 속성)

- 스스로 생성 여부

: 내부 식별자(개체 내에서 스스로 만들어지는 식별자), 외부 식별자(다른 개체와 관계를 통해 만들어지는 식별자)

- 단일 속성 여부

: 단일 식별자(주 식별자가 한 가지 속성으로만 구성), 복합 식별자(주 식별자가 두 개 이상의 속성으로 구성)

- 대체 여부

: 원조 식별자(원래의 식별자), 대리 식별자(주 식별자의 속성이 두 개 이상인 경우 속성들을 하나의 속성으로 묶어 사용하는 식별자)

 

 

E-R(개체-관계) 모델

- 개념적 모델의 대표

- 1976년 피터 첸에 의해 제안

- E-R 다이아그램(E-R 모델의 기본 아이디어를 쉽게 기호를 사용하여 시각적으로 표현한 것)으로 표현

 

관계형 DB 구조

- 튜플: 릴레이션을 구성하는 각각의 행, 속성의 모임으로 구성

+) 튜플 수 = 카디널리티(Cardinality) = 기수 = 대응수

- 속성: DB를 구성하는 가장 작은 논리적 단위, 개체 특성을 기술

+) 속성 수 = 디그리(Degree) = 차수

- 도메인: 하나의 속성이 취할 수 있는 같은 타입 원자 값의 집합

 

릴레이션 특징

- 똑같은 튜플이 포함될 수 없으므로 모두 상이하다.

- 튜플 사이에는 순서가 없다

- 속성의 명칭은 유일해야 하지만 속성을 구성하는 값을 다를 수 있음

- 속성에는 순서가 없다

- 속성들은 논리적으로 더 이상 쪼갤 수 없는 원자값만 저장

 

 

키(Key)

- 조건에 만족하는 튜플을 찾거나 정렬 시 기준이 되는 속성

- 종류: 후보키, 기본키, 대체키, 슈퍼키, 외래키

1. 후보키(Candidate Key)

- 기본키로 사용할 수 있는 속성들

- 모든 튜플에 대해서 유일성과 최소성을 만족

2. 기본키(Primary Key)

- 후보 키 중 선정된 주 키로 중복된 값을 가질 수 없음

- NULL 값을 가질 수 없음

3. 대체키(Alternate Key)

- 후보키가 둘 이상일 때 기본키를 제외한 나머지

4. 슈퍼키(Super Key)

- 한 릴레이션 내 속성들의 집합으로 구성된 키

- 한 가지 속성일 땐 Key가 될 수 없지만 여러 속성이 뭉쳐 Key의 속성을 가짐

- 유일성은 만족하지만 최소성은 만족시키지 못함

5. 외래키(Foreign Key)

- 다른 릴레이션의 기본키를 참조하는 속성 또는 속성 집합

- 외래 키의 값은 참조한 릴레이션의 기본키 값과 동일해야 함(=참조무결성)

 

 

무결성

- DB에 저장된 데이터 값과 그것이 표현하는 현실 세계의 값이 일치하는 정확성

- 데이터 무결성은 애플리케이션, DB트리거, 제약조건을 이용하여 강화할 수 있다.

- 종류: 개체 무결성, 도메인 무결성, 참조 무결성, 사용자 정의 무결성

개체 무결성(Entity Integrity)

- 기본키를 구성하는 어떤 속성도 NULL 값이나 중복 값을 가질 수 없다

도메인 무결성(Domain Integrity)

- 주어진 속성 값이 정의된 도메인에 속한 값이어야 한다.

참조 무결성(Referential Integrity)

- 외래키 값은 NULL이거나 참조 릴레이션의 기본키 값과 동일해야 한다

사용자 정의 무결성

- 속성 값들이 사용자가 정의한 제약 조건에 만족해야 한다

 

 

관계해석

- 원하는 정보가 무엇이라는 것만 정의하는 비절차적 특징

- 관계대수로 표현한 식은 관계해석으로 표현 가능

관계대수

- 관계형 DB에서 원하는 정보와 그 정보를 검색하기 위해 유도하는 것을 기수하는 절차적 언어

- 순수 관계 연산자: select, project, join, division

- 일반 집합 연산자: union, intersection, difference, cartesian product

 

 

* 표기형식까지 알아둘 것

Select

- 수평 연산

- 조건을 만족하는 튜플을 구하여 새로운 릴레이션을 만드는 연산

 

Project

- 수직 연산

- 조건에 맞는 속성, 열을 선택하여 새로운 릴레이션을 만드는 연산

- 중복이 발생하면 중복이 제거 됨

 

Join

- 공통 속성을 중심으로 두 개의 릴레이션을 하나로 합치는 연산

- Join 결과로 만들어진 릴레이션 차수 = 두 차수의 합

- 교차곱(Catesian Product)을 수행 후 Select 수행한 것과 같음

- 자연 조인: 이중 중복된 속성을 제거해 한 번만 표현

 

Division

- R ⊃ S인 두 릴레이션이 있을 때, R의 속성이 S의 속성 값을 모두 가진 튜플에서 S가 가진 속성을 제외한 속성만 구하는 연산

 

일반 집합 연산자

- 합집합(U), 교집합(∩), 차집합(-), 교차곱(x)

+) 교차곱: 두 릴레이션 튜플들의 순서 쌍, 차수는 서로 더한 값, 카디널리티는 서로 곱한 값

 

 

정규화(Normalization)

- 관게형 DB에서 정확성을 더욱 유지하기 위해 스키마를 쪼개는 과정

- 논리적 설계 단계에서 수행

- 데이터 구조의 안전성 및 무결성 유지

- 이상 발생 방지 및 자료 저장 공간의 최소화

+) 이상(Anomaly): 정규화를 거치지 않으면 DB 내에 데이터들이 불필요하게 중복되어 의도와 상관없이 삽입, 삭제, 갱신되는 현상

 

정규화 과정

1NF(제 1정규형)

- 릴레이션에 속한 모든 값들이 원자 값으로만 구성

2NF(제 2정규형)

- 기본키가 아닌 모든 속성이 기본키에 대하여 *완전 함수적 종속을 만족

* 완전 함수적 종속: 기본키에 의해 속성이 결정

* 부분 함수적 종속: 기본키의 일부에 의해 속성이 결정

3NF(제 3정규형)

- 기본키가 아닌 모든 속성이 기본키에 대해 *이행적 종속을 만족하지 않음

* 이행적 종속: A->B, B->C일 때 A->C를 만족하는 상태

BCNF(Boyce-Cold 정규형)

- 결정자가 모두 후보키, 강한 제 3정규형

4NF(제 4정규형)

- 릴레이션에 다치 종속이 성립하는 경우 모든 속성이 함수적 종속 관계를 만족

5NF(제 5정규형)

- 모든 조인 종속이 후보키를 통해서만 성립

 

-> 비정규 릴레이션 -(도메인이 원자 값)- 1NF -(부분적 함수 종속 제거)- 2NF -(이행적 함수 종속 제거)- 3NF -(결정자이면서 후보키가 아닌 것 제거)- BCNF -(다치 종속)- 4NF -(조인종속성 이용)-5NF

 

 

반정규화

- 정규화된 데이터를 다시 통합, 중복, 분리하는 과정

- 정규화 규칙을 위배하는 행위

- 과도화 된 정규화로 떨어진 성능 향상, 효율성 증가

- 방법: 테이블 통합, 테이블 분할, 테이블 추가, 중복 속성 추가

 

테이블 통합

- 두 테이블이 조인 되는 경우가 많아 하나의 테이블로 합침

- Not NULL, Default, Check 등 제약조건 설계가 어려움

테이블 분할

- 테이블을 수평 또는 수직으로 분할

중복 테이블 추가

- 여러 다른 테이블을 사용해야 하는 경우 중복테이블 추가

중복 속성 추가

- 조인해서 데이터를 처리할 때 자주 사용하는 속성 추가

 

 

시스템 카탈로그(System Catalog)

- 시스템 그 자체에 관련이 있는 다양한 객체에 관한 정보를 포함하는 시스템 DB

- 카탈로그가 생성되면 데이터 사전(Data Dictionary)에 저장되기 때문에 카탈로그를 데이터 사전이라고도 함

- 시스템 카탈로그에 저장된 정보를 메타데이터(Meta-Data)라고 함

- 유형: DB객체 정보(테이블, 인덱스, 뷰 등), 사용자 정보, 테이블 무결성 제약 조건 정보, 함수 프로시저 트리거 등에 관한 정보

- 특징

-> 일반 이용자도 SQL 문으로 내용 검색 가능

-> DML(INSERT, DELETE, UPDATE) 문으로 갱신 불가능

-> DBMS가 스스로 생성하고 유지

 

 

물리 DB 설계

---

사전 조사 분석

1. 물리 DB 설계

- 논리적 DB를 디스크 등의 물리적 저장장치에 저장할 수 있는 물리적 구조의 데이터로 변화하는 과정

- 저장 레코드 양식 설계, 레코드 집중의 분석 및 설계, 접근 경로 설계 등

- 물리적 DB구조는 여러 가지 타입의 저장 레코드 집합이라는 면에서 단순 파일과 다름

- 물리적 설계 옵션

-> 반응 시간: 트랜잭션 수행 요청부터 처리 결과 획득까지 시간

-> 공간활용도: DB파일과 접근 경로 구조에 의해 사용되는 저장공간 양

-> 트랜잭션 처리량: 단위시간 동안 DB 시스템에 의해 처리될 수 있는 트랜잭션 평균 개수

 

2. 데이터 명명 규칙 파악

- 물리 DB 모델 설계 전 파악

- 물리 논리 데이터베이스 설계 적용되는 명명규픽은 일관성 유지

- 도메인과 데이터 사전에 대한 지식 필요

 

3. 시스템 자원 파악

- DB 설치에 영향을 미칠 수 있는 물리적인 요소

 

4. DB 관리 요소 파악

- DB 운영과 관련된 관리 요소

- DB 관리 요소를 파악 후 이를 기반으로 시스템 조사 분석시 작성

- 시스템 조사 분석서를 기반으로 DB 구조, 이중화 구성, 분산 DB, 접근 제어/통제, DB 암호화 등을 파악

 

 

DB 저장 공간 설계

테이블(Table)

- DB의 가장 기본적인 객체로 행(Row)과 열(Column)로 구성

- 논리 설계 단계의 개체(Entity)에 대응하는 객체

- 종류: 일반 테이블, 클러스터 인덱스 테이블, 파티셔닝 테이블 등

 

1. 일반테이블

- 현재 사용되는 대부분의 DBMS에서 표준 테이블로 사용

2. 클러스터드 인덱스 테이블(Clustered Index Table)

- 기본키나 인덱스키의 순서에 따라 데이터가 저장되는 테이블

- 접근 경로 단축

3. 파티셔닝 테이블(Partitioning Table)

- 대용량의 테이블을 작은 논리적인 단위인 파티션으로 나눈 테이블

- 대용량의 데이터를 효과적으로 관리할 수 있지만 파티션 키를 잘못 구성하면 성능 저하 등 역효과 초래

- 방식에 따라 범위 분할, 해시 분할, 조합 분할 등으로 분류

-> 범위 분할: 지정한 열의 값을 기준

-> 해시 분할: 해시 함수를 적용한 결과 값 기준

-> 조합 분할: 범위 분할 후 해시 함수를 적용해 재분할

4. 외부 테이블(External Table)

- 일반 테이블처럼 이용할 수 있는 외부 파일로 DB 내 객체로 존재

- 데이터웨어하우스, ETL 등의 작업에서 사용

-> 데이터웨어하우스(Data Warehouse): 주요 업무 시스템에서 추출되어 새로 생성된 DB

-> ETL(Extraction, Transformation, Loading): 데이터웨어하우스를 이용해 추출, 변환, 적재하는 과정

5. 임시 테이블(Temporary Table)

- 트랜잭션이나 세션별로 저장하고 처리할 수 있는 테이블

- 트랜잭션이 종료되면 삭제

 

칼럼(Column)

- 테이블의 열을 구성하는 요소로 데이터 타입과 길이 등으로 정의

 

테이블스페이스(Tablespace)

- 테이블이 저장되는 논리적인 영역으로 하나의 테이블 스페이스에 하나 또는 그 이상의 테이블을 저장

- 테이블 저장 시 논리적으로는 테이블스페이스에 물리적으로는 연관된 데이터파일에 저장 됨

- 논리적 구성이 물리적 구성에 종속되지 않아 *투명성 보장

* 투명성: 사실 존재 여부를 염두에 두지 않아도 되는 성질

 

 

트랜잭션(Transaction)

- DB의 상태를 변환시키는 하나의 논리적 기능을 수행하기 위한 작업 단위 또는 일련의 연산

특성

- Atomicity(원자성): all or nothing, 오류가 발생하면 트랜잭션 전부가 취소

- Consistency(일관성): 트랜잭션을 성공적으로 완료하면 언제나 일관성 있는 DB 상태로 변환

- Isolation(독립성): 하나의 트랜잭션 연산 중에는 다른 트랜잭션이 관여하면 안 됨, 수행 중인 트랜잭션은 완전히 완료될 때까지 다른 트랜잭션에서 수행한 결과를 참조할 수 없음

- Durability(지속성): 성공적으로 완료된 트랜잭션의 결과는 영구적으로 반영

 

 

CRUD(Create Read Update Delete) 분석

- DB의 테이블에 변화를 주는 트랜잭션의 CRUD 연산에 대해 CRUD 매트릭스를 작성하여 분석

- 테이블에 저장되는 데이터 양 유추 가능

- 트랜잭션의 부하가 집중되는 DB 채널을 파악하고 분산, 오류 방지 가능

 

CRUD 매트릭스

- 행에는 프로세스를 열에는 테이블을, 행과 열이 만나는 위치에는 변화를 표시하는 업무 프로세스와 데이터 간 상관분석표

- 우선순위: C>D>U>R

 

 

인덱스(Index)

- 데이터 레코드를 빠르게 접근하기 위해 <키 값, 포인터> 쌍으로 구성되는 데이터 구조

- 데이터가 저장된 물리적 구조와 밀접한 관계

- 인덱스가 없으면 특정 값을 찾기 위해 모든 데이터 페이지를 확인하는 *table scan이 발생

* table scan: 테이블에 있는 모든 레코드를 순차적으로 읽는 것

- 설계순서: 인덱스의 대상 테이블이나 칼럼 등을 설정 -> 인덱스의 효율성을 검토하여 인덱스 최적화 수행 -> 인덱스 정의서 작성

- 클러스터드 인덱스(Clustered Index)

: 인덱스 키 순서에 따라 데이터가 정렬되어 저장

: 한 개의 릴레이션에 하나의 인덱스만 생성 가능

- 넌클러스터드 인덱스(Non-Clustered Index)

: 인덱스 키 값만 정렬되어 있을 뿐 실제 데이터는 정렬되지 않는 방식

: 한 개의 릴레이션에 여러 개 인덱스 생성 가능

 

1. 트리 기반 인덱스

- 인덱스를 저장하는 블록들이 트리 구조를 이루고 있는 것

- B 트리 인덱스, B+ 트리 인덱스가 존재

2. 비트맵 인덱스

- 칼럼의 데이터를 Bit 값인 0 또는 1로 변환하여 인덱스 키로 사용

- 키 값을 포함하는 행(Row)의 주소를 제공

- 효율적인 논리 연산 가능, 저장공간 작음, 압축 효율 좋음

3. 함수 기반 인덱스

- 칼럼 값 대신 칼럼의 특정 함수나 수식을 적용하여 산출된 값을 사용

- 데이터를 입력하거나 수정할 때 함수를 적용하기 때문에 부하가 발생할 수 있음

- 대소문자, 띄어쓰기 등에 상관없이 조회할 때 유용

4. 비트맵 조인 인덱스

- 다수의 조인된 객체로 구성된 인덱스

5. 도메인 인덱스

- 개발자가 필요한 인덱스를 직접 만들어 사용하는 것으로 확장형 인덱스라고도 함

 

 

뷰(View)

- 사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된 이름을 가지는 가상 테이블

- 뷰는 저장장치 내 물리적으로 존재하지 않지만, 사용자에게 있는 것처럼 간주 됨

- 기본 테이블로부터 유도된 테이블이기 때문에 기본 테이블과 같은 형태의 구조를 사용, 조작도 기본 테이블과 거의 같음

- 필요한 데이터만 뷰로 정의해 처리할 수 있기 떄문에 관리가 용이하고 명령문이 간단해짐

- 뷰에 나타나지 않는 데이터를 안전하게 보호하는 효율적인 기법으로 사용할 수 있음

- 뷰가 정의된 기본 테이블이나 뷰를 삭제하면 그 테이블이나 뷰를 기초로 정의된 다른 뷰도 자동으로 삭제

- 기본키를 포함한 속성 집합으로 뷰를 구성해야만 삽입, 삭제, 갱신 연산이 가능

- 설계 순서: 대상 테이블 선정-> 대상 컬럼 선정 -> 정의서 작성

- 장점: 논리적 데이터 독립성 제공, 사용자의 데이터 관리를 간단하게 해줌, 접근 제어를 통한 자동 보안 제공, 동일 데이터에 대해 동시에 여러 사용자의 상이한 응용이나 요구를 지원해 줌

- 단점: 독립적인 인덱스를 가질 수 없음, 뷰의 정의를 변경할 수 없음, 뷰로 구성된 내용에 대한 삽입/삭제/갱신 연산에 제약이 따름

 

 

클러스트(Cluster)

- 데이터 저장 시 데이터 액세스 효율을 향상시키기 위해 동일한 성격의 데이터를 데이터 블록에 저장하는 물리적 저장 방법

- 데이터 조회 속도는 향상시키지만 데이터 입력, 수정, 삭제에 대한 성능은 저하시킴

- 데이터의 분포가 넓을수록 유리

- 대용량을 처리하는 트랜잭션은 전체 테이블을 스캔하는 일이 자주 발생하므로 클러스터링을 지양

- 파티셔닝된 테이블에는 클러스터링 할 수 없음

- 클러스터링 된 테이블에 클러스터드 인덱스를 생성하면 접근 성능이 향상됨

- 대상 테이블: 분포도가 넓은 테이블, 대량의 범위를 자주 조회하는 테이블, 입력/수정/삭제가 자주 발생하지 않는 테이블, 자주 조인되어 사용되는 테이블, order by/group by/union이 빈번한 테이블

 

 

파티션(Partition)

- 대용량의 테이블이나 인덱스를 작은 논리적 단위로 분할

- 데이터 처리는 테이블 단위, 저장은 파티션 별로 수행

- 장점: 데이터 접근 시 액세스 범위를 줄여 쿼리 성능 향상, 데이터 분산 저장으로 디스크 성능 향상, 파티션별 백업 및 복구를 수행하므로 속도 향상, 장애 시 피해 최소화, 가용성 향상, 입출력 분산

- 단점: 세심한 관리 요구, 테이블 간 조인 비용, 용량이 적은 테이블 파티셔닝 수행 시 성능 저하

- 종류: 범위 분할, 해시 분할, 조합 분할

 

인덱스 파티션

- 파티션 된 테이블 데이터 관리를 위해 인덱스 분할

- 종속 여부에 따른 구분

-> Local Partitioned Index: 테이블 파티션과 인덱스 파티션이 1:1 대응

-> Global Partitioned Index: 테이블 파티션과 인덱스 파티션이 독립적

- 인덱스 파티션 키 컬럼 위치에 따른 구분

-> Prefixed Partitioned Index: 인덱스 파티션 키와 인덱스 첫 칼럼이 같음

-> Non-Prefixed Partitioned Index: 인덱스 파티션 키가 인덱스 첫 칼럼과 다름

 

 

참고 자료

---

https://powerdev.tistory.com/40?category=875947

https://1d1cblog.tistory.com/93?category=847036