2020 정보처리기사 실기 요약 - 데이터베이스

데이터베이스 요약

1. DBMS 필수 기능
정의 기능 : 모든 응용 프로그램들이 요구하는 데이터 구조를 지원하기 위해 데이터베이스에 저장될 데이터의 형과 구조에 대한 정의, 이용방식, 제약 조건 등을 명시하는 기능
조작 기능 : 데이터 검색, 갱신, 삽입, 삭제 등을 체계적으로 처리하기 위해 사용자와 데이터베이스 사이의 인터페이스 수단을 제공하는 기능
제어 기능 : 데이터베이스를 접근하는 갱신, 삽입, 삭제 작업이 정확하게 수행되어 데이터의 무결성이 유지되도록 제어하는 기능
DCL(데이터 제어 언어)의 기능 : 보안, 무결성, 장애회복, 병행수행제어

2. 정규화 관련
이상 현상 : 릴레이션 조작시 데이터들이 불필요하게 중복되어 예기치 않게 발생하는 곤란한 현상이며, 데이터의 중복성으로 인해 릴레이션을 조작할때 발생하는 비합리적인 현상[삽입,삭제,갱신]
비정규화 : 정규화된 엔티티, 속성, 관계에 대해 성능 향상과 개발 운영의 단순화를 위해 중복, 분리, 통합(그룹핑)을 수행하는 데이터 모델링 기법
정규화
관계형 데이터베이스의 설계에서 중복을 최소화 할 수 있도록 데이터를 구조화 하여 각 릴레이션에서 이상현상이 생기지 않도록 하는 데이터 모델링 기법

3. 정규화 과정
1NF : 릴레이션에 속한 모든 도메인이 원자값만으로 되어 있는 정규형. 릴레이션의 모든 속성값이 원자값으로만 되어 있는 정규형
2NF : 릴레이션 R이 1NF고, 기본키가 아닌 모든 속성이 기본키에 대해 완전 함수적 종속을 만족하는 정규형(부분적 함수 종속 제거)
3NF : 릴레이션 R이 2NF이고, 기본키가 아닌 모든 속성이 기본키에 대해 이행적 종속을 만족하지 않는 정규형(A-B이고, B->C일 때 A->C를 만족하지 않음)(이행적 함수 종속 제거)
BCNF : 릴레이션 R에서 결정자가 모두 후보키인 정규형. 모든 BCNF가 종속성을 보존하는 것은 아님(결정자이면서 후보키가 아닌 것 제거)
*결정자 : 속성 간의 종속성을 규명할 떄 기준이 되는 값(학번에 따라 이름이 결정되는 학번->이이 일 때 학번을 결정자라고 함)
4NF : 릴레이션 R에 다치 종속 A->>B가 성립하는 경우, R의 모든 속성이 A에 함수적 종속 관계를 만족하는 정규형
5NF : 릴레이션 R의 모든 조인 종속이 R의 후보키를 통해서만 성립되는 정규형

 

4. 데이터 관련
데이터 흐름도 (Data Flow Diagram; DFD) : 데이터 흐름도는 데이터가 각 프로세스를 따라 흐르면서 변환되는 모습을 나타낸 그림으로, 시스템 분석과 설계에서 매우 유용하게 사용되는 다이어그램이다.
자료 사전((Data Dictionary; DD) : 자료 사전은 자료 요소, 자료 요소들의 집합, 자료의 흐름, 자료 저장소의 의미와 그들 간의 관계, 관계 값, 범위, 단위들을 구체적으로 명시하는 사전이다.
HIPO(Hierarchy Input Process Output) : HIPO는 시스템의 분석 및 설계나 문서화할 때 사용되며 하향식 소프트웨어 개발을 위한 문서화 도구이다.
관계 대수 : 관계형 데이터베이스에서 원하는 정보와 그 정보를 어떻게 유도하는 가를 기술하고, 관계로 표현된 데이터를 취급하는 대수적인 연산 체계이자 절차적 정형언어이다.
시스템 카탈로그 : 데이터베이스에 저장되어 있는 모든 데이터 개체들의 관한 정보나 명세에 대한 정보가 수록되어 있는, DBMS가 생성하고 유지하는 데이터베이스 내의 테이블들의 집합체

 

5. 트랜잭션 관련
로킹 기법 : 하나의 트랜잭션이 데이터를 액세스 하는 동안 다른 트랜잭션이 그 데이터 항목을 액세스할 수 없도록 제어하는 기법
병행 제어 : 동시에 여러 개의 트랜잭션을 수행할 때, 데이터베이스의 일관성 유지를 위해 트랜잭션 간의 상호작용을 제어하는 기법

 

6. 고급 데이터베이스
데이터 웨어하우스 : 급장하는 대량의 데이터를 효과적으로 분석해 정보화하고 이를 여러 계층의 사용자들이 효율적으로 사용할 수 있도록 한 데이터베이스
데이터 마트 : 전사적으로 구축된 데이터 웨어하우스로부터 특정 주제나 부서 중심으로 구축된 소규모 단일 주제의 데이터 웨어하우스
데이터 마이닝 : 데이터 웨어하우스에 저장된 데이터 집합에서 사용자의 요구에 따라 유용하고 가능성 있는 정보를 발견하기 위한 기법
OLAP(Online Analytical Processing) : 다차원으로 이루어진 데이터로부터 통계적인 요약정보를 분석해 의사 결정에 활용하는 방식
OLTP(Online Transaction Processing) : 온라인 업무 처리 형태의 하나로 네트워크 상의 여러 이용자가 실시간으로 데이터베이스의 데이터를 갱신하거나 검색하는 등의 단위 작업을 처리하는 방식
tpmC : DB기반 OLTP 미들웨어 시스템 서비스를 위한 H/W 성능을 측정하기 위한 대표적 방법으로, 1분간의 최대 처리 건수를 나타냄
옵티마이저 : SQL을 가장 빠르고 효율적으로 수행할 최적(최저비용)의 처리경로를 생성해주는 DBMS 내부의 핵심 엔진

 

7. 무결성
개체 무결성 : 기본 테이블의 기본키를 구성하는 어떤 속성도 Null 값이나 중복값을 가질 수 없다는 규정
도메인 무결성 : 주어진 속성 값이 정의된 도메인에 속한 값이어야 한다는 규정.
참조 무결성 : 외래키 값은 Null이거나 참조 릴레이션의 기본키 값과 동일해야 함. 즉 릴레이션은 참조할 수 없는 외래키를 가질 수 없다는 규정
사용자 정의 무결성 : 속성 값들이 사용자가 정의한 제약조건에 만족해야 한다는 규정
NULL 무결성 : 릴레이션의 특정 속성 값이 NULL이 될 수 없도록 하는 규정
고유 무결성 : 릴레이션의 특정 속성에 대해 각 튜플이 갖는 속성 값들이 서로 달라야 한다는 규정
키 무결성 : 하나의 릴레이션에는 적어도 하나의 키가 존재해야 한다는 규정
관계 무결성 : 릴레이션에 어느 한 튜플의 삽입 가능 여부 또는 한 릴레이션과 다른 릴레이션의 튜플들 사이의 관계에 대한 적절성 여부를 지정한 규정

 

8. SQL 응용
프로시저 구성 : DECLARE/ BEGIN/ END/ CONTROL/ SQL/ EXCEPTION/ TRANSACTION [디비엔컨SET]
 -> 절차형 SQL문을 활용해 사전에 정의해놓은 SQL문을 호출할 경우 특정 기능을 수행하는 트랜잭션 언어
사용자 정의함수 구성 : DECLARE / BEGIN / END / CONTROL / SQL / EXCEPTION/ RETURN [디비엔컨SER]
 -> 사용자가 정의해 SQL문 처리 수행 후 단일 값으로 반환하는 절차형 SQL
트리거 구성 : DECLARE / EVENT / BEGIN /END / CONTROL / SQL / EXCEPTION [디이비엔컨SE]
 -> 특정 테이블에 수정, 삭제, 삽입 등의 데이터 이벤트 발생시 자동으로 DBMS에서 실행되도록 구현된 프로그램

 

9. 그룹 함수 : 소 그룹 간의 중간 합계, 소계를 계산하여 산출하는 함수. GROUP BY 뒤에 쓰고, SUM, AVG, COUNT와 같은 그룹함수와 함께 쓰임
ROLLUP : 소계, 중간 집계 값 산출
CUBE : 다차원 집계 생성
GROUPING SET : 컬럼에 대한 개별 집계
HAVING 조건 : 'GROUP BY 컬럼 HAVING 조건'으로 쓰고, 그룹에 조건을 추가할 때 사용

 

10. 윈도우 함수 : 데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해 SQL에 추가한 함수. OVER 명령어.
ex) SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ( [PARTITION BY 칼럼]] [ORDER BY 절] [WINDOWING 절] ) FROM 테이블 명;

 

11. 인덱스
데이터 레코드를 빠르게 접근하기 위해 키 값, 포인터 쌍으로 구성되는 데이터 구조. 인덱스는 데이터가 저장된 물리적 구조와 밀접한 관계. 레코드가 저장된 물리적 구조에 접근하는 방법 제공. 인덱스를 통해 파일 레코드에 대한 액세스를 빠르게 수행. 레코드의 삽입과 삭제가 수시로 일어나는 경우에는 인덱스의 개수를 최소로 하는 것이 효율적. 인덱스가 없으면 TABLE SCAN(특정한 값을 찾기위해 모든 데이터 페이지를 확인)이 발생.
트리 기반 인덱스 : 인덱스를 저장하는 블록들이 트리 구조를 이루는 것으로, 상용 DBMS에서는 트리 구조 기반의 B+트리 인덱스를 주로 활용
비트맵 인덱스 : 인덱스 컬럼의 데이터를 Bit 값인 0또는 1로 변환해 인덱스 키로 사용하는 방법. 비트맵 인덱스의 목적은 키 값을 포함하는 로우의 주소를 제공하는 것. 분포도가 좋은 컬럼에 적합하며 성능 향상 효과를 기대할 수 있음.
함수 기반 인덱스 : 컬럼의 값 대신 컬럼에 특정 함수나 수식을 적용해 산출된 값을 사용하는 것으로, B+ 트리 인덱스나 비트맵 인덱스를 생성해 사용. 데이터를 입력하거나 수정할 때 함수를 적용해야 하므로 부하가 발생할 수 있음.
비트맵 조인 인덱스 : 다수의 조인된 객체로 구성된 인덱스로, 단일 객체로 구성된 일반적인 인덱스와 액세스 방법이 다름.
도메인 인덱스 : 개발자가 필요한 인덱스를 직접 만들어 사용하는 것. 확장형 인덱스(Extensible Index)라고도 함.

 

12. 뷰(View)
사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된 이름을 가지는 가상 테이블. 뷰는 데이터 보정 작업, 처리 과정 시험 등 임시적인 작업을 위한 용도로 활용된다. 뷰는 가상 테이블이기 때문에 물리적 구현이 없고, 데이터의 논리적 독립성을 제공한다.

 

13. 클러스터 설계
데이터 저장 시 데이터 액세스 효율을 향상시키기 위해 동일한 성격의 데이터를 동일한 데이터 블록에 저장하는 물리적 저장 방법. 클러스터링된 테이블은 데이터 조회 속도 향상시키지만 데이터 입력, 수정, 삭제에 대한 성능은 저하시킨다. 클러스터는 데이터의 분포도가 넓을수록 유리

 

14. 파티션 설계
대용량 테이블이나 인덱스를 작은 논리적 단위인 파티션으로 나누는 것. 대용량 DB의 경우 중요한 몇 개의 테이블에만 집중되어 데이터가 증가되므로, 이런 테이블들을 작은 단위로 나눠 분산시키면 성능 저하를 방지할 뿐만 아니라 데이터 관리도 쉬워진다. 테이블이나 인덱스를 파티셔닝 하면 파티션키나 인덱스키에 따라 물리적으로 별도의 공간에 데이터가 저장된다. 데이터 처리는 테이블 단위로 이뤄지고, 데이터 저장은 파티션 별로 수행된다.
[인덱스 파티션]
Local Paritioned Index : 테이블 파티션과 인덱스 파티션이 1:1 대응 되도록 파티셔닝
Global Partitioned Index : 테이블 파티션과 인덱스 파티션이 독립적으로 구성되도록 파티셔닝
Prefixed Partitioned Index : 인덱스 파티션 키와 인덱스 첫 번째 컬럼이 같음
Non-Prefixed Partitioned Index : 인덱스 파티션 키와 인덱스 첫 번째 컬럼이 다르다.

 

15. 분산 데이터베이스
논리적으로는 하나의 시스템에 속하지만 물리적(여러 지역으로)으로는 네트워크를 통해 연결된 여러 개의 컴퓨터 사이트에 분산되어 있는 데이터베이스
위치 투명성 : 액세스 하려는 데이터베이스의 실제 위치를 알 필요 없이 단지 데이터베이스의 논리적 명칭만으로 액세스 할 수 있음
중복 투명성 : 동일 데이터가 여러 곳에 중복되어 있더라도 사용자는 마치 하나의 데이터만 존재하는 것처럼 사용하고, 시스템은 자동으로 여러 자료에 대한 작업을 수행한다.
병행 투명성 : 분산 데이터베이스와 관련된 다수의 트랜잭션들이 동시에 실현되더라도 그 트랜잭션의 결과는 영향을 받지 않는다.
장애 투명성 : 트랜잭션, DBMS, 네 트워크, 컴퓨터 장애에도 불구하고 트랜잭션을 정확하게 처리한다.

 

16. NoSQL(Not Only SQL)
전통적인 RDBMS와 다른 DBMS를 지칭하기 위한 용어로, 데이터 저장에 고정된 테이블 스키마가 필요하지 않고 조인 연산을 사용할 수 없으며, 수평적으로 확장이 가능한 DBMS으로, Basically Availale, Soft-state, Eventually Consistency의 특성이 있음


17. 관계대수의 연산자
합집합 U : 이항 연산으로 관계성이 있는 두 개의 릴레이션을 합집합하여 하나의 릴레이션을 만들어 내는 연산
교집합 ∩ : 이항 연산으로 관계성이 있는 두개의 릴레이션에서 중복되어 있는 내용을 선택하여 새로운 릴레이션을 만들어 내는 연산
차집합 - : 이항 연산으로 관계성이 있는 두개의 릴레이션이 있을 때 그 중 하나의 릴레이션에서 또 다른 릴레이션의 내용과 겹치는 내용을 제거해서 새로운 릴레이션을 생성하는 연산
카티션 프로덕트 X : 이항 연산으로 두 릴레이션의 현재 투플로 구성 가능한 모든 조합 만드는 연산

Select б : 릴레이션의 주어진 조건을 만족하는 튜플을 선택하는 연산
Project π : 단항 연산으로 릴레이션에서 참조하고자 하는 어트리뷰트를 선택하여 분리해 내는 연산
Join >< : 두 릴레이션 간의 에트리뷰트 값이 동일한 튜플을 연결하는 연산
Division ÷ : 두 개의 릴레이션 A와 B가 있을 때 B의 릴레이션의 모든 조건을 만족하는 경우의 튜플들을 릴레이션 A에서 분리해 내어 프로젝션하는 연산


18. 데이터베이스 설계 순서
요구 조건 분석 > 개념적 설계 > 논리적 설계 > 물리적 설계 > 구현
개념적 설계 : 현실 세계를 추상적 개념으로 표현, 개념 스키마 모델링, 트랜잭션 모델링
논리적 설계 : 개념 스키마를 평가 및 정제, 트랜잭션 인터페이스 설계
물리적 설계 : 데이터베이스 파일의 저장 구조 및 액세스 경로 결정, 저장 레코드의 형식, 순서, 접근 경로

 

19. 데이터 베이스 설계시 고려사항[무일회 보효데]
무결성 : 삽입, 삭제, 갱신 등의 연산 후에도 데이터 베이스에 저장된 데이터가 정해진 제약 조건을 항상 만족해야 함
일관성 : DB에 저장된 데이터들 사이나, 특정 질의에 대한 응답이 처음부터 끝까지 일관되야함.
회복 : 시스템에 장애가 발생했을 때 장애발생 직전의 상태로 복구가능해야함
보안 : 불법적인 데이터의 노출 또는 변경이나 손실로부터 보호할 수 있어야 함
효율성 : 응답시간의 단축, 시스템의 생산성, 저장 공간의 최적화 등이 가능해야 함.
데이터베이스 확장 : 데이터베이스 운영에 영향을 주지 않으면서 지속적으로 데이터를 추가할 수 있어야 함.

 

20. 트랜잭션의 특성
Atomicity(원자성) : 트랜잭션의 연산은 데이터베이스에 모두 반영되도록 완료(Commit)되든지, 아니면 전혀 반영 되지 않도록 복구(Rollback)되어야 한다
Consistency(일관성) : 트랜잭션은 언제나 일관성 있는 결과를 가져와야 한다. 시스템이 가지고 있는 고정 요소는 트랜잭션 수행 전과 트랜잭션 수행 완료의 상태가 같아야 한다.
Isolation(독립성) : 둘 이상의 트랜잭션이 동시에 병행 실행 되는 경우 어느 하나의 트랜잭션 실행 중에 다른 트랜잭션의 연산이 끼어들 수 없다. 수행중인 트랜잭션은 완전히 완료될 때까지 다른 트랜잭션에서 수행 결과를 참조할 수 없다.
Durability(영속성) : 성공적으로 완료된 트랜잭션 결과는 시스템에 고장나더라도 영구적으로 반영되어야 한다.


21. CRUD Matrix
프로세스와 데이터 사이에 관계 의존성을 Create, Read, Update, Delete로 표현한 매트릭스


22. 쿼리 요약
INSERT INTO 테이블(컬럼1,컬럼2,...) VALUES(A,B,C)
UPDATE 테이블 SET A = B WHERE ~
DELETE FROM 테이블 WHERE ~
SELECT DISTINCT NVL(컬럼, 바꿀텍스트) WHERE 컬럼 BETWEEN a AND b(a와 b사이의 결과)
WHERE 컬럼 IN(a,b)(괄호 안에 값 중 일치하는 데이터를 출력)
DESCRIBE : 테이블 또는 뷰의 구조를 조회할때 사용되는 명령어
CREATE VIEW 뷰이름 AS (SELECT 컬럼 FROM 테이블 WHERE 조건)


23. 테이블 수정
컬럼 추가 : ALTER TABLE 테이블 ADD(컬럼, 속성) 
컬럼 삭제 : ALTER TABLE 테이블 DROP COLUMN (컬럼명)
컬럼명 수정 : ALTER TABLE 테이블 RENAME COLUMN 기존 컬럼명 TO 새로운 컬럼명
제약 조건 : CONSTRAINT 제약조건 이름 PRIMARY KEY (제약 컬럼)