[정보처리기사_실기] Chapter 2. 데이터 입출력 구현

 

■ 데이터 모델링 절차  [ 개  논  물 ] 

개념 데이터 모델링 -> 논리 데이터 모델링 -> 물리 데이터 모델링 -> DB 구축 

 

■ 데이터 모델의 구성요소 

개체 (Entity) / 속성 (Attribute) / 관계 (Relationship)

* 속성 : 데이터의 가장 작은 논리적 단위로서 파일 구조상의 데이터 항목 또는 데이터 필드에 해당함

 

■ 데이터 모델링 정의

데이터 정보구조를 실체와 관계를 중심으로 명확하고 체계적으로 표현하여 문서화하는 기법 

 

■ 논리 데이터 모델링 정의 

데이터 구조에 대한 논리적 정의 단계, 엔티티와 속성들의 관계를 구조적으로 정의하는 단계

+ 의사소통의 보조자료로서 E-R 모델을 사용

 

■ E-R (Entity-Relation) 모델의 정의 (ERD)

정보들을 개체, 속성, 관계로 기술한 모델로 실제로 존재하는 데이터와 그들간의 관계를 사람이 이해할 수 있는 형태로 명확하게 표현하기 위한 모델이다. 

+ ERD는 업무 분석 결과로 도출된 실체(엔티티)와 엔티티 간의 관계를 도식화한 다이어그램

 

■ E-R 다이어그램의 기호 (ERD)

□ : 개체 (Entity)

◇ : 관계 (Relation) 

○ : 속성 (Attribute)

◎ : 다중속성 (Multi-Value Attribute) 

 

* 관계(Relation)의 유형 

1:1 / 1:N / N:M (다대다) 

 

■ 물리 데이터의 모델링 절차 

(1) 개체 --> 테이블로 변환 

(2) 속성 --> 컬럼으로 변환 

(3) UID --> 기본키로 변환 

(4) 관계 --> 외래키로 변환 

(5) 컬럼 유형과 길이 정의 

(6) 반정규화 수행  (반 정규화는 물리데이터 모델링중 가장 마지막 단계에서 시스템 성능향상과 개발 및 운영의 단순화를 위해 데이터 모델을 통합하는 기법이다. )

 

■ 물리 데이터 모델링 데이터 유형 

(1) CHAR : 최대 2000바이트의 고정길이 문자열 저장가능

(2) VARCHAR2, VARCHAR : 최대 4000바이트의 가변 길이 문자열 저장가능 

(3) NUMBER : 38자릿수의 숫자 저장 가능 

(4) DATE : 날짜 값을 저장 

(5) BLOB, CLOB : 바이너리 (Binary), 텍스트 데이터 최대 4GB까지 저장 

(BLOB은 바이너리 데이터를 DB외부에 저장하기 위한 타입이고, CLOB은 문자열 데이터를 DB 외부에 저장하기 위한 타입이다.) 

 

■ 키본키 (Primary Ket) 

유일성, 최소성, 대표성을 가지며 NULL을 허용하지 않는 키

 

■ DFD (Data Flow Diagram : 데이터 흐름도) 

- DFD는 시스템 모델링 도구로서 가장 보편적으로 사용되는 것 중의 하나, 데이터에 비해 기능이 매우 복잡하고 중요할 경우에 매우 유용하게 사용될 수 있다. 

- DFD는 프로세스, 데이터 흐름, 데이터 저장소, 외부 엔티티 등으로 이루어진다. 

(Process, data flow, data store, terminator) 

- 데이터의 흐름에 중심을 두는 분석용 도구 

---

 

■ 이상현상의 정의 

정규화를 거치지 않으면 데이터 중복으로 인해 테이블 조작시 문제가 발생하는 것 

 

■ 이상현상 3가지  (삽삭갱)

(1) 삽입 이상 (Insert Anomaly) : 데이터를 삽입할 때 의도와는 상관없이 원하지 않는 값들도 함께 삽입해야하는 현상 

(2) 갱신 이상 (Update Anomaly) : 데이터를 갱신 할 때 일부의 정보만 갱신되어 데이터 정보에 모순이 발생하는 현상 

(3) 삭제 이상 (Delete Anomaly) : 데이터 삭제 시 원치않는 데이터도 함께 삭제되어, 연쇄 삭제 현상이 일어나는 현상 

 

■ 정규화에 대한 설명 

불필요한 데이터를 제거하고 데이터의 중복을 최소화 / 다양한 관점에서 쿼리를 지원 / 각종 이상현상을 방지 

(데이터 베이스 정규화란 데이터의 중복을 줄이고 무결성을 향상시키기 위해 정규화된 형태로 재 디자인 하는 기법이다.) 

 

■ 정규화 [ 원 부 이 결 다 조 ]

1NF - 도메인이 원자값 

2NF - 부분적 함수 종속 제거 

3NF - 이행적 함수 종속 제거 

BCNF - 결정자이면서 후보키가 아닌 것 제거  

4NF - 다치 종속 제거 

5NF - 조인 종속 제거 

 

■ 반정규화

[개념] 정규화된 데이터모델을 분리, 중복, 통합하는 과정으로 의도적으로 정규화를 위반하는 행위 

시스템의 성능 향상, 개발 및 운영의 편의성등을 위해 정규화된 데이터 모델을 통합, 중복, 분리하는 과정으로 

의도적으로 정규화를 위배하는 행위이다. 

(정규화와 반대의 개념으로 시스템 성능향상과 운영을 단순화를 위해 데이터 모델을 통합하는 기법) 

필요한 경우 

- 데이터를 조회할 때 디스크 I/O 량이 많아 성능이 저하되는 경우 

- 조인으로 인한 성능저하가 예상되는 경우 

- 컬럼을 계산하여 읽을 때, 성능저하가 예상되는 경우 

 

■ 반정규화 종류 

(1)  테이블 반정규화 

   - 테이블 병합 :  1:1관계 , 1:M 관계, 슈퍼/서브타입 테이블 병합 

*슈퍼/서브타입 관계 : 여러 엔티티에서 공통 속성이 존재할 경우 속성들을 하나로 묶어 통합하는 데이터 모델링 기법

슈퍼타입은 상위 공통 데이터의 내용을 담는 타입, 서브타입은 하위 분할된 정보를 담는 타입  

   - 테이블 분할 : 수직 분할(컬럼/속성단위) // 수평 분할(로우/레코드 기준)   - 파티셔닝 기법을 이용

   - 테이블 추가 : 중복테이블추가 / 통계테이블추가 / 이력테이블추가 / 부분테이블추가 

(2) 칼럼 반정규화 

 중복 칼럼 추가 / 파생칼럼 추가 / 이력테이블 칼럼추가 / PK에 의한 칼럼 추가 / 응용 시스템 오작동을 위한 칼럼 추가 

(3) 관계 반정규화 : 중복관계 추가  

 

■ 인덱스 (INDEX) 

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

 

■ 뷰 (VIEW) 

사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된, 이름을 가지는 가장 테이블이다. / 물리적으로 존재하지 않지만 사용자에게는 있는 것처럼 간주 / CREATE 문으로 정의 , DROP 문으로 제거 / 데이터의 논리적 독립성을 제공 

 

■ 트랜잭션의 정의 

하나의 논리적인 기능을 수행하기 위한 최소의 작업단위 

 

■ 트랜잭션 특징 [ 원 일 독 지 ] - 영문도 외우기 

원자성(Atomicity) / 일관성(Consistency) / 독립성(Isolation) / 지속성 (Durability)

 

■ 파티션 정의 

대용량 테이블이나 인덱스를 작은 논리적 단위로 나누는 것 

 

■ 파티션 3가지 [ 범 해 조 ] 

범위 파티션 (Range Partition) / 해시 파티션 (Hash Partition) / 조합 파티션 (Composite Partition) / + List Partition 리스트 파티션 

 

■ 클러스터 (Cluster) 

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

클러스터링된 테이블은 데이터 조회 속도는 향상시키지만 데이터 입력, 수정, 삭제에 대한 성능은 저하시킨다. 

처리범위가 넓은 경우는 단일 테이블 클러스터링을, 조인이 많이 발생하는 경우에는 다중 테이블 클러스터링을 사용 

 

■ 클러스터 설계 시 고려사항 

- 검색 효율은 높여 주나 입력, 수정, 삭제 시는 부하가 증가함을 고려

- UNION, DISTINCT, ORDER BY, GROUP BY가 빈번한 컬럼이 대상 

- 수정이 자주 발생하지 않는 컬럼은 검토 대상 

- 처리 범위가 넓어 문제가 발생하는 경우는 단일 테이블 클러스터링을 고려 

- 조인이 많아 문제가 발생되는 경우는 다중 테이블 클러스터링을 고려 

 

■ 디스크 구성 설계 방법

- 정확한 용량을 산정하여 디스크 사용의 효율을 높인다.

- 업무량이 집중되어 있는 디스크를 분리하여 설계한다. 

- 입출력 경합을 최소화하여 데이터의 접근 성능을 향상시킨다. 

- 디스크 구성에 따라 테이블 스페이스 개수와 사이즈 등을 결정한다. 

- 파티션 수행 테이블은 별도로 분류한다. 

 

■ 분산 데이터베이스 

하나의 데이터베이스 관리시스템(DBMS)이 여러 CPU에 연결된 저장 장치들을 제어하는 형태의 데이터베이스로 데이터베이스를 연결하는 빠른 네트워크 환경을 이용하여 데이터베이스를 여러지역 노드로 위치시켜 사용과 성능을 극대화 시킴

분산 데이터베이스는 논리적으로 분산된 데이터베이스를 이용한다. 

 

■분산 데이터 베이스의 투명성 

- 분할 투명성(단편화) : 하나의 논리적 릴레이션이 여러 단편으로 분할되어 저장 

- 위치 투명성 : 사용하려는 데이터 저장 장솟 명시 불필요, 위치정보가 시스템 카탈로그에 유지 

- 지역사상 투명성 : 지역 DBMS와 물리적 DB사이의 매핑보장, 각 지역 시스템 이름과 무관한 이름 사용 가능 

- 중복 투명성 : DB객체가 여러 site에 중복되어있는 지 알필요가 없는 성질 

- 장애 투명성 : 장애에 무관한 트랜잭션의 원자성 유지 

- 병행 투명성 : 다수 트랜잭션 동시 수행시에도 결과의 일관성 유지 

 

---

■ 데이터 언어 

- DDL (데이터 정의어 - Data Definition Language) : CREATE / DROP / ALTER / TRUNCATE / RENAME

  ex) CREATE TABLE 테이블명 ( 컬럼명 데이터형, , );

       DROP TABLE 테이블명;

       ALTER TABLE 테이블명 [ADD/MODIFY] (컬럼명 데이터형); - 오브젝트 구조변경 

       RENAME 테이블명 TO 새로운 테이블명;

       TRUNCATE TABLE 테이블명;  --> 구조는 그대로 두고 내용을 전체삭제 

- DML (데이터 조작어 - Data Manipulation Language) : INSERT / DELETE / UPDATE / SELECT 

  ex)  INSERT INTO 테이블 (컬럼,컬럼) VALUES (값, 값); 

        DELETE FROM 테이블 WHERE 컬럼 = 조건; 

        UPDATE 테이블 SET 컬럼 = 바꿀값 WHERE 컬럼 = 조건; 

        SELECT 항목 FROM 테이블 WHERE 조건 

              --> 항목 (DISTINCT / * / alias / table_name/ WHERE / GROUP BY / HAVING / ORDER BY 

* where 절에서 사용할 수 있는 부분 처리 : EXISTS, NOT EXISTS, IN, NOT IN 

- DCL (데이터 제어어- Data Control Language) : GRANT / REVOKE / ROLE

  ex) GRANT 부여할 권한유형 TO User [롤네임]; 

       REVOKE SELECT ON 테이블 FROM scott; 

       CREATE ROLE 롤네임; 

- TCL (트랜잭션 제어어 - Transaction Control Language) : COMMIT / ROLLBACK / SAVEPOINT 

-> 트랜잭션 젲어어는 트랜잭션의 DML 작업단위를 제어하는 명령어 

 

 

■ PL/SQL 

- 프로시저 출력 내용을 화면에 보여주기 위해서는 환경 변수 SERVEROUTPUT(디폴트 값이 OFF) ON으로 변경해야한다. 

- PL/SQL 작성 후 마지막에 SHOW ERRORS 구문을 추가해주면 어느 곳에 에러가 있는지 출력해준다.

- 동일 구문 

생성 또는 재생성 : CREATE OR REPLACE 

파라미터 : IN 또는 OUT 또는 INOUT

변수 선언을 할 경우  : IS

구문 실행 전 : BEGIN

종료시 :  (COMMIT;) END; --> END는 BEGIN이랑 셋트다. (★)

 

 

■ PL/SQL 종류 

(1) 함수 (2) 프로시저 (3) 패키지 (4) 트리거 

 

■ PROCEDURE 사용법 

만들기: 

CREATE OR REPLACE  PROCEDURE  AAA     --프로시져 AAA 생성  (                                                         -- 파라미터    DEPT IN VARCHAR,     --  들어오는 값    STU OUT NUMBER   --  나가는 값  ) IS                                                        -- 변수 선언    (DECLEAR) CITY VARCHAR2(20) := '서울'; BEGIN                                                  -- 프로시저 시작  CONTROL / 조건문  INSERT INTO ~~                                    -- 프로시저 내용  VALUES ~~  COMMIT;                                              -- 구문저장  EXCEPTION / 예외상황 수행 END;                                                    -- 프로시저 끝

 SET SERVEROUTPUT ON 은 출력을 위한 작업, 그 후에 EXECUTE를 이용해 프로시저를 실행 

[사용하기] : EXECUTE AAA('관리부','23);  또는 EXEC AAA('관리부','23'); 

[삭제] DROP PROCEDURE [프로시저명]; 

 

■ 프로시저 LOOP 문 

LOOP

   문장;

EXIT  WHEN 탈출조건;

END LOOP;

 

 

■ TRIGGER 사용법 

트리거는 하나의 SQL구문이 실행될 때 자동으로 실행되는거임.

 

만들기:

CREATE OR REPLACE  TRIGGER  DDD         -- 트리거 DDD 생성       AFTER INSERT   ON stu_list   FOR EACH ROW DECLARE                                              -- 변수 정의    I_date   stu_list.Input_date%type;   dept_num   stu_list.deptNum%type;      BEGIN                                                  -- 트리거 시작    I_date := :new.input_date;   dept_num := :new.deptNum;    UPDATE     ~~~                                     END;                                                    -- 트리거 끝

사용하기:  트리거는 조건 구문이 실행되면 자동으로 실행된다. 

 

■ VIEW (뷰) 사용법

개념: 사용자에게 제한된 데이터를 보여주기 위한 하나이상의 기본 테이블로 유도된 가상의 테이블 

만들기: CREATE VIEW [뷰이름] AS SELECT [컬럼명1],[컬럼명2] FROM [테이블명]; 

장점 : 논리적 독립성 제공 / 사용자 데이터 관리 용이 / 데이터 보안 용이

단점 : 뷰 자체 인덱스 불가 / 뷰 정의 불가 / 데이터 변경 제약이 존재

사용하기:  

속성: 

REPLACE  -> 뷰 재생성 

FORCE ->  기본 테이블의 존재 여부에 관계없이 뷰 생성 

NOFORCE -> 본 테이블이 존재할 때만 뷰 생성 

WITH CHECK OPTION -> 조건절에 한해 변경 가능 옵션

WITH READ ONLY -> 읽기만 가능 

 

■ INDEX (인덱스) 사용법

개념: 검색 연산의 최적화를 위해 데이터베이스 내의 튜플(행)들에 대한 정보를 구성하는데이터 구조 

인덱스는 데이터를 빠르게 찾을 수 있는 수단, 테이블에 대한 조회속도를 높여주는 자료구조 

만들기 : CREATE INDEX [인덱스이름] ON [테이블명](컬럼명1, 컬럼명2); 

 

인덱스틑 어떤 데이터의 위치를 알려주는 정보를 가진 주소록 같은 개념이다.

INDEX_DESC(idx_empno)  뭐 이런식으로 HINT를 사용할 수 있다.

- (그냥) INDEX : 순차적 스캔 

- INDEX_ASC : 오름차순 스캔 (위에 INDEX와 동일) 

- INDEX_DESC : 내림차순 스캔 

- FULL : 테이블 전체 스캔 

-----------------------------------

INDEX 컬럼 선정 기준 

- 분포도가 좋은 (10~15% 이내) 컬럼은 단독적으로 생성한다. 

- 자주 조합되어 사용되는 컬럼은 결합 인덱스로 생성한다. 

- 가능한 한 수정이 빈번하지 않은 컬럼을 선정한다. 

----------------------------------

INDEX의 종류 ...?

- 결합 인덱스 (Composite Index) : 인덱스들이 자주 조합될 때는 결합 인덱스를 생성한다. 

 

 

■데이터 베이스 분포도 계산 ... ?

분포도의 계산식은 다음과 같다

- {(1 / ( ) 컬럼 값의 종류 } × 100 또는

- {( Ro 컬럼 값의 평균 수 테이블의 w ) / ( 총 Row ) 수 } × 100

 

 

■ 테이블 제약조건 

참조되는 릴레이션 튜플 함께 삭제 조건 : ON DELETE CASCADE 

Restricted : 참조한 테이블에 있는 외부키에 없는 것만 수정가능 

Nullify : 참조한 테이블에 정의된 외부키와 일치하는 것을 Null로 수정 

 

 

■ 헷갈리는 같은용어

테이블 = 릴레이션 (Relation) 

행 = 로우(ROW), 카디널리티(Cardinulity), 튜플(Tuple) , 레코드(Recode)

열 = 속성(Attribute-애트리뷰트), 디그리(Degree) = 차수 

 

 

■ RANGE BETWEEN 과 AND 사이에는 3가지 유형이 있다. 

(1) UNBOUNDED PRECEDING : 최종 출력될 값의 맨 처음 ROW의 값

(2) CURRENT ROW : 현재 ROW의 값

(3) UNBOUNDED FOLLOWING : 최종 출력될 값의 맨 마지막 ROW의 값 

 

예시)

PARTITION BY COL1 ORDER BY COL3 

RANGE BETWEEN UNBOUND PRECEDING AND CURRENT ROW 

 

---

■ 순위함수 

- RANK( ) : 동일 순위의 레코드 존재 시 후 순위 넘어감

ex) 1등, 2등, 2등, 2등, 5등, 6등 ..

- DENSE_RANK( ) : 동일 순위의 레코드 존재 시에도 후 순위 넘어가지 않음

ex) 1등, 2등, 2등, 2등, 3등, 4등 ... 

- ROW_NUMBER( ) : 동일 순위의 값이 존재해도 이와 무관하게 연속 번호를 부여

ex) 2등이 3개인 경우 : 2등, 3등, 4등, 5등...

 

■ 소계 함수 

- ROLLUP

- CUBE : 주어진 데이터들의 소계 및 전체 총계까지 구해주는 함수이다. 결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹 함수이며, 내부적으로 대상 컬럼의 순서를 변경하여 또 한 번의 쿼리를 수행한다. 

- GROUPING SETS 

 

 

---

 

■ 옵티마이저 [ 데이터베이스 핵심 모듈 ]

[ 정의 ] SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로(최적의 SQL 실행계획)를 생성해주는 데이터베이스 관리자내부의 핵심엔진

- 옵티마이저는 다른말로 최적기로, SQL의 실행계획을 만들어주는 역할을 한다.

- 옵티마이저는 비정상 적인 실행계획을 수립 시 액세스 경로 및 조인순서를 제어하기 위해 Hint(힌트)를 사용한다. 

-----------------------------------

종류 

(1) RBO (Rule Based Optimizer)

- 미리 정해진 규칙에 의한 계획을 결정한다. 

- 규칙중 가장 비용이 적은 규칙을 선택하여 실행 계획을 세운다. 하지만 SQL 실행의 비용이아니라 규칙자체의 비용이라 유연하지 못하다.

(2) CBO (Cost Based Optimizer) 

- 비용기반 옵티마이저는 통계 정보(수집된 비용)를 활용한다. 

- SQL을 처리하는데 비용이 가장 적은 실행계획(Execution Plan)을 선택한다. 

 

■ 옵티마이저 HINT 문법 종류

[인덱스] 

/*+ INDEX_ASC*/    :  인덱스 오름차순으로 스캔 

/*+ INDEX_DESC*/ :  인덱스 내림차순으로 스캔

/*+ INDEX_FFS*/ : 인덱스 Full 스캔

/*+ PALLEL_INDEX*/ : 인덱스를 여러 개의 프로세스(세션)으로 수행

/*+ FULL */  :  인덱스를 사용하지 않고 테이블 Full 스캔 

 

[조인 액세스]

/*+ USE_NL */  :  Nested Loop Join 유도 

/*+ USE_HASH */  : Hash Join 유도 

/*+ USE_MERGE */  : Sort Merge Join 유도 

 

[조인 순서] 

/*+ ORDERED * /  : FROM 절에 명시된 테이블 순으로 조인

/*+ LEADING */  : 파라미텅[ 명시된 테이블 순으로 조인 

 

[기타] 

/*+ PARALLEL */  :  여러 개의 프로세스(세션)로 수행

/*+ PUSH_SUBQ*/  : 서브쿼리를 먼저 수행 

* where 절에 ROWNUM = 1이라고 명시하면 가장 첫번째 튜플(행)만 가져올 수 있다. 

 

/*+ ALL_ROWS */   

[ 전체적인 처리작업이 필요한 경우 Full-scan을 피할 수 없으므로, 단위 처리량(Throghtput)을 최적으로 한 ALL_ROWS를 사용한다. ]

- 가장 좋은 단위 처리량을 목표로 블록을 최적화하기 위해 비용기반(Cost-based) 접근 방법을 선택 

- 전체적인 최소의 자원 소비, 모든 레코드의 처리하는 시간의 최소화를 목적으로 최적화 

 

[사용법] 

SELECT /*+ INDEX_DESC(A dept_date) * /

ORDERDATE 

FROM ORDER1T A 

WHERE ORDDEPT = '430' AND STATUS = '30'

AND ROWNUM =1 

 

■ 힌트 (HINT) 

옵티마이저가 비정상적인 실행 계획을 수립 시 액세스 경로 및 조인 순서를 제어할 수 있도록 하기 위한 기법 

옵티마이저에 의해 생성된 SQL의 실행계획을 사용자가 의도하는 방향으로 수동으로 제어하는 도구

 

■ 실행 계획 (Excution Plan) 

옵티마이저가 생성한 SQL 처리 경로 

---

■ 데이터 웨어 하우스 (Data Warehouse) 

조직이나 기업체의 중심이 되는 업무시스템에서 모아진 정보를 일관된 스키마로 저장한 저장소이다. 

 

■ 데이터 마트 (Data Mart) 

전사적으로 구축된 데이터 웨어하우스로부터 특정 주제나 부서 중심으로 구축된 소규모 단일 주제의 데이터 웨어하우스이다. 일반적으로 한 기업 내에 여러 개가 존재하여 특정 부서의 의사결정을 지원하며, 다양한 질의나 요구를 충족하는 유연성과 접근성이 뛰어난 다차원 구조의 데이터를 지닌 것.

 

■ ETL (extract transform load) 

수집 대상 데이터를 추출, 가공(변환, 정제)하여 데이터 웨어 하우스에 저장하는 기술 

 

 

 

 

 

 

 

 

참고사이트 

https://cafe.naver.com/soojebi

수제비- IT 커뮤니티 (정보처리기사... : 네이버 카페