정보처리기사_3과목_데이터베이스구축(1)

관계데이터베이스 언어 1. SQL - SQL(구조적 질의어) : IBM에서 개발된 데이터베이스에 사용되는 언어 - SQL의 특징       관계대수와 관계해석을 기초로한 고급 데이터 언어      이해하기 쉬운형태      대화식 질의어로 사용 가능      데이터정의, 데이터 조작, 제어 기능 제공      COBOL, C, PASCAL 등의 언어에 삽입      레코드 집합 단위로 처리      비절차적 언어 SQL정의어 - 스키마, 도메인, 테이블, 뷰, 인덱스를 정의하거나 제거하는데 사용 CREATE문 = 스키마, 도메인, 테이블, 뷰, 인덱스 정의에 사용 스키마 정의 = CREATE SCHEMA 스키마이름 AUTHORIZATION 사용자아이디 도메인 정의 = CREATE DOMAIN 도메인이름 데이터타입 테이블 정의 = CREATE TABLE 테이블명                     ({열이름  데이터 타입 [NOT NULL][DERAULT 묵시값]}                      [RPIMARY KEY (열이름)]                    제외가능                      ....UNIQUE , FOREGIN KEY, ON DELETE, ON UPDATE, CHECK  등 조건입력가능 제외도 가능                      ) 인덱스 정의 = CREATE INDEX 문에 의해 생성 시스템이 자동적으로 관리 CREATE [UNIQUE] INDEX 인덱스 이름                 ON 테이블 이름 (열이름) ALTER 문 = 기존테이블에 대해 새로운 열의 첨가, 값의 변경, 기존 열의 삭제 등에 사용 ALTER TABLE 테이블 이름 ADD 열 이름 데이터 타입 ALTER TABLE 테이블 이름 ALTER 열 이름 SET DEFAULT 값 ALTER TABLE 테이블 이름 DROP 열 이름 CASCADE ※ ADD : 열추가, ALTER : 값 변경, DROP : 열 삭제 DROP 문 = 스키마, 도메인, 테이블, 뷰, 인덱스 제거시 사용(전체 삭제) DROP SCHEMA 스키마 이름 [CASCADE OR RESTRICTED] DROP DOMAIN 도메인 이름 [CASCADE OR RESTRICTED] DROP TABLE 테이블 이름 [CASCADE OR RESTRICTED] DROP INDEX 인덱스 이름 ※ CASCADE : 삭제할 요소가 참조 중이면 삭제 되지 않는다. ※ RESTRICTED : 삭제할 요소가 참조 중이더라도 삭제 된다. SQL 조작어 SELECT(검색문) SELECT 열 이름(검색 대상) FROM 테이블이름 WHERE 조건 GROUP BY 열이름 HABING조건 ORDER BY 열이름 ASC or DESC GROUP BY : 그룹으로 나누어 준다. HAVING : 그룹에 대한 조건 ORDER BY : 정렬 수행 부분 매치 질의문 : % -> 하나 이상의 문자, _ -> 단일문자   ※부분 매치 질의문에서는 = 대신 LIKE 사용 널(NULL)값 비교 시는 = 대신 IS (또는 IS NOT)을 사용 집계함수 : COUNT, SUM, AVG, MAX, MIN INSERT(삽입문) INSERT INTO 테이블(열이름) VALUES(열값_리스트) 하나의 테이블만을 대상으로 한다 NULL 값을 입력할수 있고 부속 질의어를 포함 할수 있다. 모든 열의 값을 입력할 때는 테이블 명 다음의 열 이름을 생략할 수 있다. UPDATE(갱신문) -기존 레코드 열값을 갱신할 경우 사용 UPDATE 테이블  SET 열 이름 = 변경 내용 WHERE 조건 DELETE(삭제문) - 기존 테이블의 행을 삭제할 경우 사용 DELETE FROM 테이블 WHERE 조건 SQL뷰 하나 이상의 테이블로부터 유도되어 만들어진 가상 테이블 실행시간에만 구체화 되는 특수한 테이블 특징 - 뷰가 정의된 기본 테이블이 제거(변경)되면 뷰도 자동적으로 변경(제거)된다. - 외부 스키마는 뷰와 기본 테이블의 정의로 구성된다. - 뷰에 대한 검색은 기본테이블과 거의 동일(삽입, 삭제, 갱신은 제약) - DBA는 보안 측면에서 뷰를 활용할 수 있다. - 뷰는 CREATE문에 의해 정의되며, SYSVIEWS에 저장된다. - 한번 정의된 뷰는 변경할 수 없으며, 삭제한 후 다시 생성 - 뷰의 정의는 ALTER문을 이용하여 변경할 수 없다. - 뷰를 제거할때는 DROP문을 사용한다. 장단점 장점 논리적 독립성 제공 데이터 접근 제어로 보안가능 사용자의 데이터 관리를 간단하게함 하나의 테이블로 여러개의 상이한 뷰를 정의 단점 독자적인 인덱스를 가질수 없다. 정의를 변경할 수 없다. 삽입, 삭제, 갱신, 연산에 많은 제약이 따른다. 뷰의 생성 CREATE VIEW 뷰 이름(열 이름 리스트)          AS SELECT 문             WITH CHECK OPTION - AS SELECT문은 일반 검색문과 같지만 UNION이나 ORDER BY를 사용할 수 없다. - WITH CHECK OPTION절은 이뷰에 대한 갱신이나 삽입 연산이 실행 될때 뷰 정의 조건을 위해하면 실행을 거절 시킨다는것을 명세한다. 뷰의제거 - 뷰의 정의는 ALTER문을 이용하여 변경할수 없다. - 뷰 제거할 대는 다음과 같은 형식의 DROP문을 사용한다. DROP VIEW 뷰이름{RESTRICT OR CASCASE} 3. Embedded(내장) SQL - SQL은 단말기를 통해 대화식으로 사용될 수도 있지만, COBOL, C, JAVA와 같은 호스트 프로그래밍 언어로 작성되는 응용프로그램 속에서 내장해서 사용할 수도 있다. 응용 프로그램 속에 내장해서 사용하는 SQL을 내장 SQL이라고 한다. 응용프로그램의 특징 - EXEC SQL을 앞에 붙임 - 내장 SQL실행문은 호스트 실행문이 나타나는 어느 곳에서나 사용가능 - SQL문에 사용되는 호스트 변수는 콜론(:)을 앞에 붙임 - 호스트 변수와 대응하는 필드의 데이터 타입의 일치 - 호스트 변수와 데이터베이스 필드의 이름 같아도 무방 - SELECT에 의한 검색 결과는 튜플로 구성된 테이블이지만 호스트 언어들은 한 번에 하나의 레코드만 취급(커서의 필요성) Cursor(커서) -검색 결과 테이블의 튜플들을 순서대로 지시 -DECLARE : 커서와 관련된 SQL문을 정의 -OPEN: 커서를 개방 -FETCH : 커서를 가리키는 결과 테이블의 한 튜플을 호스트 변수로 가져옴 -CLOSE : 커서를 폐쇄

데이터 베이스 설계 - 데이터베이스 설계는 사용자의 요구조건에서부터 데이터베이스 구조를 도출해 내는 과정 - 데이터 베이스 설계시 고려사항     무결성, 일관성, 회복, 보안, 효율성, 데이터베이스 확장 등 요구조건 분석        ↓      설계(개념적 설계 -> 논리적 설계 -> 물리적 설계)        ↓      구현        ↓      운영        ↓ 감시 및 개선 요구 조건 분석 = 사용자가 원하는 데이터 베이스의 용도를 파악 개념적 설계 = 현실 세계를 정보 모델링을 통해 개념적으로 표현                     DBMS와 H/W에 독립적 논리적 설계 = 개념 세계를 데이터모델링을 통해 논리적으로 표현                    개념적 설계에서 만들어진 구조를 구현 가능한 data 모델로 변환                     DBMS 종속적 H/W에 독립적 물리적 설계 = 논리적 데이터 베이스 구조를 내부 저장장치 구조와 접근 경로 등을 설계                     DBMS 와 H/W에 종속적 ※물리적 설계시 고려사항 - 응답시간, 저장공간의 효율화, 트랜잭션 처리도(처리능력)

관계 데이터베이스 정규화 1. 정규화 개념 개념  - 이상문제를 해결하기위해 어튜리뷰트 간의 종속 관계를 분석하여 여러개의 릴레이션으로 분해하는 과정 - 논리적 설계 단계에서 수행 - 정규화를 통해 릴레이션 분해하면 일반적으로 연산시간 증가 ** 성능 하향 목적 - 데이터베이스 연산의 여러 이상을 없애기 위함 anomaly(이상) - 어튜리뷰트 간에 존재하는 여러 종속 관계를 하나릐 릴레이션에 표현함으로 인해 발생하는 현상 (삽입이상, 삭제이상, 갱신이상) 삽입이상 = 데이터 삽입시 원하지 ㅇ낳는 정보를 강제 삽입해야 하는 경우와 불필요한 데이터가 함께 삽입되는 경우 삭제이상 = 튜플을 삭제함으로써 유지되어야 하는 정보까지도 연쇄삭제되는 이상 갱신이상 = 중복된 튜플중에서 일부의 attribute만 갱신 시킴으로써 정보의 모순성이 생기는 현상 스키마변환의 원리 정보의 무손실, 데이터 중복성 감소, 분리의 원칙, 종속성 보존 함수적 종속(functional dependency : FD) - 어떤 릴레이션에서 속성들의 부분 집합을 X, Y라 할때, 임의 튜플에서 X의 값이 Y의 값을 함수적으로 결정한다면, Y가 X에 함수적으로 종속되었다고 하고, 기호로는 X -> Y로 표기한다. 2. 정규화 체계 제 1정규형(1NF) 어떤 릴레이션 R에 속한 모든 도메인이 원자값(atomi value)만으로 되어 있다면 제1정규형에 속한다. 제 2정규형(2NF) 어떤 릴레이션 R이 1NF이고 키(기본)에 속하지 않은 애트리뷰트는 모두 기본키의 완전함수종속이면, 제 2정규형이다. 제 3정규형(3NF) 어떤 릴레이션 R이 2NF이고 키(기본)에 속하지 않은 모든 애트리뷰트들이 기본키에 이행적 함수종속이 아닐때 제 3정규형에 속한다. 보이스/코드 정규형(BCNF) 릴레이션 R의 모든 결정자(determinant)가 후보키(candidate key)이면 릴레이션 R은 보이스/코드 정규형에 속한다. 제4 정규형(4NF) 릴레이션에서 다치종속 관계가 성립하는 경우 제5 정규형(5NF) 릴레이션에서 조인종속이 성립하는 경우 정규화 과정 ※반정규화(De-Normalization) 정의 - 정규화되어 있는 것을 정규화 이전 상태로 만드는것을 말한다. - 많은 조인에 의해 성능이 저하되거나 데이터 조회시 디스크 I/O량이 많을대 부분적인 반정규화를 고려함 절차 - 반정규화 대상 조사 = 범위 처리빈도수 조사, 대량의 범위처리조사, 통계성 프로세스 조사, 테이블 조인개수 - 반정규화 대사을 다른 방법으로 처리 유도 할 수 있는지 검토 = 뷰 테이블, 클러스터링 적용, 인덱스 조정, 애플리케이션 - 반정규화 적용 = 테이블 반정규화, 속성의 반정규화, 관계의 반정규화

고급 데이터베이스 1. 트랜잭션의 특징   - 한 꺼번에 수행되어야할 일련의 데이터 베이스의 연산들   - 병행 제어 및 회복 작업의 논리적 단위   - 원자성(atomicity) 을 가짐   - OLTP와 OLAP : 단순 레코드를 위주로 한 은행 계좌처리, 항공 예약 처리 등의 단순 트랜잭션 처리응용을 OLTP(OnLine Transaction processing)라고 하고, 대규모 레코드를 대상으로 시장분석, 판매동향 분석 등을 수행 하는 DSS, EIS, Data Warehouse 등의 복잡한 트랜잭션 처리 응용을 OLAP(OnLine Analytical Processing)라고 한다 트랜잭션의 성질(원자,일관,격리,영속) 원자성(atomicity) : 트랜잭션은 전부, 전무의 실행만이 있지 일부 실행으로 트랜잭션의 기능을 가질수 없다. 일관성(consistency) : 트랜잭신이 그 실행을 성공적으로 완료하면 언제나 일관된 데이터베이스 상태로 된다라는 의미이다. 즉, 이트랜잭션의 실행으로 일관성이 깨지지 않는다라는 의미 격리성(isolation) : 연산의 중간결과에 다른 트랜잭션이나 작업이 접근할 수 없다라는 의미 영속성(durability) : 트랜잭션이 일단 그 실행을 성공적으로 끝내면 그 결과를 어떠한 경우에라도 보장받는다 라는 의미 트랜잭션의 원자성과 관련된 연산 COMMIT : 트랜잭션의 성공적인 종료 / 트랜잭션의 일괄적인 연산 성공 후 저장 하는 제어어 ROLLBACL : 트랜잭션의 비정상적인 종료 / 트랜잭션으 일괄적인 연상 실패 후 되돌리는 제어어 2. Recovery(회복) 여러가지 장애로 인해 손상된 데이터베이스를 손상되기 이전의 정상적인 상태로 복구시키는 작업(덤프와 로그 이용) 장애의 유형 - 트랜잭션 장애 : 트랜잭션 내의 오류나 내부 조건, 즉 입력 데이터의 불량, 데이터의 불명, 시스템 자원의 과다 사용 요구 등으로 정상적인 실행을 계속 할 수 없는 상태 - 시스템 장애 : 하드웨어의 오동작으로 메인 메모리에 있는 정보의 손실이나 교착 상태가 발행하여 더이상 실행을 계속 할 수 없는 상태 - 미디어 장애 : 디스크 헤드 붕괴나 고장으로 인해 저장 장치의 데이터베이스 일부 또는 전부가 손상된 상태 - 행동 장애 : 데이터를 발견하지 못했거나 연산 실패 이면 그 행동을 철회하고 응용 프로그램에 통보한다. 회복의 기본원리 = 정보의 중복(Redundancy) 복사 및 덤프 : 아카이브 로그 또는 저널 : 갱신된 속성의 옛 값/새 값 3. 병행제어 병행제어의 필요성 - 다중 사용자 환경에서는 여러개의 트랜잭션이 섞여서 실행되는데 이러한 병행 실행은 특별한 제어 방법을 사용하지 않을 경우 갱신 손실 등의 문제를 야기한다. 병행 제어를 안 할 때의 문제점 갱신분실(lost update) = 일련의 갱신 작업시 일부 갱신 사실이 반영안됨 모순성(inconsistency) 연쇄복귀(cascading rollback) 직렬가능성(Serializability) -트랜잭션을 병행 처리한 결과가 트랜젹션들을 순차적으로(직렬로) 수행한 결과와 같아지는것 주요 병행 제어 방법 로킹(locking) 기법 - lock과 unlock 연산을 통해 트랜잭션의 데이터 아이템 제어 - 하나의 트랜잭션 만이 lock을걸고 unlock할 수 있다. 2단계 로킹 규약 확장(growing phase : lock수행) - unlock을 수행 할수 없다. 축소(shrinking phase : unlock 수행) - lock을 수행할 수 없다. ※모든 트랜잭션이 2단계 로킹 규약을 준수하면 직렬 가능성을 갖는다. 4. 분산데이터 베이스 정의 - 컴퓨터 네트워크를 기반으로 데이터가 물리적으로 여러시스템에 분산되어 있으나 논리 적으로는 하나의 통합된 db인것처럼 보이도록 구성한 database (저장장치는 분리되어있으나 논리적으로 하나의 db처럼 사용) 구성요소 - 분산처리기 (distributed processor) : 지리적으로 분산되어 있는 컴퓨터 시스템 - 분산 데이터베이스(distributed database) : 지리적으로 분산되어 있는 지역 데이터베이스 - 통신 네트워크(communication network) : 지리적으로 분산된 자치 처리기들을 통신으로 연결시켜 자원을 공유하게 함으로써 논리적으로 하나의 시스템 기능을 할 수 있게 하는 망 시스템의 목표 - 위치투명성(location transparency)     사용하려는 데이터가 저장된 사이트를 사용자는 알 필요가 없는 것     위치정보는 시스템 카탈로그에 유지 - 중복투명성(replication transparency)      한 논리적 데이터 객체가 여러 상이한 사이트에 중복될 수 있음      중복 데이터의 일관성 유지는 사용자와 무관하게 시스템이 수행      중복의 이점은 성능 향상과 가용성(availability) 증진이다 시스템의 장점 지역차지성, 점진적인 시스템 용량 확장, 신회성 및 가용성, 효율성 및 융통성, 자료 공유와 분산제어 가능, 질의처리의 신속화 (연산속도의 증가) 시스템의 단점 여러사이트간의 조정을 위해 복잡도 증가 = 소프트웨어 개발비용증가 분산알고리즘의 정확성 판단 어려움 = 치명적인 오류 가능성 증가 메시지 교환 비용, 조정을위한 계산 비용 = 처리 오버헤드 증가 보안통제 어려워짐 = 각 사이트의 동일수준 보안이 어려움 중앙 집중 db에서 분산 db로 변경하는 작업이 어려움 5. 트리거 개념 = 데이터베이스가 미리 정해 놓은 특정 조건이 만족되거나 어떤 동작이 수행되면 자동으로 실행되도록 정의한 동작           ECA, Event-Condition-Action 규칙이라고 부름 구성요소 트리거가 실행될 조건이 되는 문장이나 이벤트 실행 조건의 제약 실행될 내용 타입 row(로우) 및 statement(문장) BEFORE 및 AFTER 트리거는 두 종류의 타입 중에서 각각 하나씩을 가질 수 있다. - BEFORE row  - BEFORE statement - AFTER row - AFTER statement 6. 인덱스 단일 단계 인덱스 = 단일 단계 인덱스의 각 엔트리는 <탐색 키, 레코드에 대한 포인터>로 이루어진다. 엔트리들은 탐색 키 값의 오름차순으로 정렬된다. 기본인덱스(primary index) - 탐색 키가 데이터 파일의 기본키인 인덱스를 기본 인덱스라고 한다. - 레코드들은 기본 키의 값에 따라 클러스터링 된다. - 기본 인덱스는 기본 키의 값에 따라 정렬된 데이터 파일에 대해 정의된다. - 기본 인덱스는 흔희 희소 인덱스로 유지 할수 있다. 클러스터링 인덱스(clustering index) - 클러스터링 인덱스는 탐색 키 값에 따라 정렬된 데이터 파일에 대해 정의 - 각 데이터 블록 대신에 각각의 상이한 키 값마다 하나의 인덱스 엔트리가 인덱스에 포함되어, 그탐색 키 값을 갖는 첫번째 레코드의 주소(또는 레코드가 들어 있는 블록의 주소)를 카르킨다. - 클러스터링 인덱스는 범위 질의에 유용하다.  보조 인덱스(secondary index) - 보조 인덱스는 탐색 키 값에 따라 정렬되지 않은 데이터 파일에 대해 정의된다. 하지만 인덱스에서 탐색 키 값들은 물론 정렬되어 있다. - 보조 인덱스는 일반적으로 밀집 인덱스 이므로 같은 수의 레코드들을 접근할 때 보조 인덱스를 통하면 기본 인덱스를 통하는 경우보다 디스크 접근 횟수가 증가할 수 있다. - 기본 인덱스를 사용한 순차 접근은 효율적이지만 보조 인덱스를 사용한 순차접근은 비효율 적이다. 각 레코드를 접근하기 위해서 디스크에서 블록을 읽어올 필요가 있을수 있다. 다단계 인덱스 - 다단계 인덱스에서 가장 상위 단계 인덱스를 마스터 인덱스 라고 부르며, 이는 한 블록으로 구성되기 때문에 주기억 장치에 상주할 수 있다. 7.조인(JOIN, ) - 두 관계로부터 관련된 튜플 들을 하나의 튜플로 결합하는 연산 - 카디션 프로덕트와 셀렉트를 하나로 결합한 이항 연산자로, 일반적으로 조인이라 하면 자연조인을 말한다. - 두 개 이상의 릴레이션으로부터 상호 연관성을 구하기 위한 연산자 - 릴레이션의 차수는 릴레이션 R의 차수와 S의 차수를 합한 것과 같다. 세타조인(θ-join, theta-join) - R(X), S(Y), A∈X, B∈Y 에대하여 비교 연산자를 θ로 표현 할수 있는 조인 - 세타조인 연산은 선택 연산과 카디션 프로덕트를 하나의 연산으로 결합할 수 있도록 확장된 자연 조인이다. 동일조인(equi-join) - 세타 조인에서 θ가 "="인 경우로 동일 애트리뷰트가 존재한다. 자연조인(natural join, ) == 동일 조인- 중복속성 = 동일 조인 결과에서 중복되는 속성을 하나만 남기고 모두 제거 8. crud분석 - 데이터 베이스 테이블에 변화를 주는 트랜잭션의 CRUD 연산에 대해 CRUD 매트릭스를 작성하여 분석하는 것 - CRUD 연산의 우선 순위 C > D > U > R 구분      / SQL       / 조작 Create   / INSERT   / 생성 Read     / SELECT   / 읽기, 인출 Update  / UPDATE  / 갱신 Delete   / DELETE   / 삭제, 제거