정보처리기사 실기 정리 - 데이터베이스 (그냥 걍 또 만드는)

2015/04/08 - [자격증/정보처리(산업)기사] - 정보처리기사 실기 준비

2015/04/08 - [자격증/정보처리(산업)기사] - 정보처리기사 실기 정리 - 데이터베이스

2015/04/09 - [자격증/정보처리(산업)기사] - 정보처리기사 실기 정리 - 업무프로세스

2015/04/16 - [자격증/정보처리(산업)기사] - 정보처리기사 실기 정리 - 데이터베이스 (그냥 걍 또 만드는)

데이터베이스 기출 내용 토대로 중요 부분만 따로 꺼내보자

크게 5가지 목차로 분류함

1. 데이터베이스

데이터베이스의 설계과정

ER Model을 Relation Schema로 변환하는 과정을 Mapping Rule이라고 한다.

Relationship Data Model에서의 용어 정리

Domain : 해당 속성이 가질 수 있는 Type값들의 종류

2. Key / Integrity

Key

Super Key : Attribute의 집합으로 구성된 Key / 유일성 O / 최소성 X(하나 빼도 유일성이 유지된다는 말)

Candidate Key : Tuple을 유일하게 식별하기 위해 사용되는 Attribute의 부분집합 / 유일성 O / 최소성 O

Primary Key : Candidate Key 中, 선정된 Key / 중복된 값 X / Not Null / 유일성 O / 최소성 O

Alternate Key : Candidate Key 中, Primary Key를 뺀 나머지 Key들 / 유일성 O / 최소성 O

Foreign Key : 다른 Relation의 Primary Key를 참조하는 Attribute(들) / 중복된 값 X / Not Null / 유일성 O / 최소성 O

Integrity

무결성 제약 조건 : 정확하지 않은 데이터가 DB 내에 저장되는 것을 방지하기 위한 제약 조건

NULL 무결성 : 특정 Attribute값이 Not Null

고유(Unique) 무결성 : 특정 Attribute에 대해 각 Tuple들이 서로 다른 값

Domain 무결성 : 특정 Attribute값이 Domain에 속한 값

Key 무결성 : 하나의 Relation엔 적어도 하나의 Key가 있어야 한다

관계(Relationship) 무결성 : Relation에서 Tuple의 삽입 가능 여부, 서로 다른 Relation의 Tuple들 사이의 관계에 대한 적절성 여부

참조(Referential) 무결성 : Foreign Key는 Null이거나 참조 Relation의 Primary Key와 동일

개체(Entity) 무결성 : Primary Key를 구성하는 Attribute는 Not Null

※ 개체무결성이랑 NULL 무결성이랑 헷갈려

3. Anomaly / Normalization

Anomaly

Insert Anomaly : 데이터 삽입 시, 의도치 않은 값들로 인해 삽입할 수 없게 되는 현상

Delete Anomaly : 한 튜플을 삭제할 때, 의도치 않은 튜플도 연쇄 삭제되는 현상

Update Anomaly : 튜플들의 속성값을 갱신할 때, 일부 튜플만 갱신되어 정보에 불일치가 생기는 현상

Normalization

테이블의 속성들의 종속적인 관계를 이용해 테이블을 무손실 분해하는 과정으로

가능한 한 중복을 제거하여 삽입 / 삭제 / 갱신 이상의 발생 가능성을 줄이는 것이다.

비정규 릴레이션

↓ 도메인이 원자값

제 1정규형(1NF; First Nomal Form) : 테이블에 속한 모든 속성의 도메인이 원자 값(Atomic Value)임

↓ 부분적 함수 종속 제거

제 2정규형(2NF) : 기본키가 아닌 모든 속성이, 기본키에 대하여 완전 함수적 종속을 만족

↓ 이행적 함수적 종속(Transitive Functional Dependency) 제거

A→B이고 B→C일때, A→C를 만족하는 관계

제 3정규형(3NF) : 기본키가 아닌 모든 속성이 기본키에 대해 이행적 종속을 만족하지 않음

↓ 결정자가 후보키가 아닌 함수종속 제거

BCNF(Boyce-Codd Normal Form) 모든 결정자가 후보키

↓ 다치종속(MVD; Multi-Valued Dependency) 제거

테이블 R(A,B,C)에서 (A,B)→(C)도 성립하고 (A)→(C)도 성립할때 다치 종속

제 4정규형(4NF) : 테이블 R의 다치 종속(MVD) A→→B가 존재할 경우, R의 모든 속성이 A에 함수적 종속

↓ 조인종속 제거

제 5정규형(5NF or PJNF) : 테이블 R의 모든 조인 종속(JD)이 R의 후보키를 통해서만 성립

조인 종속(JD) : 테이블 R과 R의 프로젝션들을 모두 조인한 결과가 동일

여기서는 R의 프로젝션으로 R의 후보키가 온다고보면됨

※ 첫 글자 따서 : 도부이결다조

4. SQL

관계대수(Relational Algebra)

관계형 DB에서 원하는 정보와 그 정보를 어떻게 유도하는지 기술하는 절차적 언어

- 순수 관계 연산자

SELECT

조건만족하는 Tuple의 부분집합으로 Realtion을 만듬 / σ Condition (Relation) ; ex) σ국어>80(성적)

PROJECT

주어진 Attribute 값만을 추출하여 Relation을 만듬 / π Attribute (Relation) ; ex) π이름,국어(성적)

JOIN

공통 Attribute를 중심으로 2개의 Relation을 하나로 합쳐서 새로운 Relation을 만듬

Relation ▷◁Constraint Relation ; ex) 성적 ▷◁이름=이름 학적부

Cartensian Product 연산한후 Select 연산 수행한 것과 동일하다

-- Cartensian Product : 걍 Relation 두개 조합한거

Constraint을 θ로 일반화할 수 있는 JOIN을 θ-Join(Theta JOIN)이라고도 한다

Natural JOIN : JOIN 조건이 =일 때 동일한 속성이 2번 나타나므로 중복을 제거해서 같은 속성은 한번만 표기하는 JOIN

DIVISION

Relation [ Attribute ÷ Attribute ] Relation

ex) 구입자 [ 구입품 코드 ÷ 생산품 코드 ] 생산품

구입자에서 생산품의 생산품 코드의 값들과 연관 있는 Tuple만 추출하되 구입품 코드 Attribute는 제외한다

- 일반 집합 연산자 : 수학에서 사용되는 집합 연산자를 Relation 연산에서도 사용이 가능

UNION(합집합) : ∪ : 결과에서 중복되는 Tuple은 제거

INTERSECTION(교집합) : ∩

DIFFERENCE(차집합) : －

CARTENSIAN PRODUCT(교차곱) : × : Relation에 존재하는 모든 Tuple끼리 결합

관계해석(Relational Calculus)

원하는 정보가 무엇이라는 것만 정의하는 비절차적 언어로써 Tuple 관계해석과 Domain 관계해석이 있다

관계대수로 표현한걸 관계해석으로 표현할 수 있다

질의어로 표현한다

1) DDL(Data Define Language) - CREATE / ALTER / DROP

DDL로 정의된 내용은 Metadata가 되며 System Catalog에 저장된다

CREATE SCHEMA 스키마이름 AUTHORIZATION 사용자ID;

CREATE DOMAIN 도메인이름 데이터타입

[DEFAULT 기본값] [CONSTRAINT [제약조건명] CHECK (범위값)]; -- CHECK 포함해서 총 4개 가능해 자세한건 생략

CREATE TABLE 테이블이름

(속성명 데이터타입 [NOT NULL], [, 속성명 데이터타입 [NOT NULL], ...]

[, PRIMARY KEY(기본키속성명, ...)]

[, FOREIGN KEY(외래키속성명, ...) REFERENCES 참조테이블 (참조테이블의기본키)

[ON DELETE {NO ACTION | CASCADE | SET NULL | SET DEFAULT}]

[ON UPDATE {NO ACTION | CASCADE | SET NULL | SET DEFAULT}]

]

[, CONSTRAINT [제약조건명] CHECK (범위값)] -- CHECK 포함해서 총 4개 가능해 자세한건 생략

);

CREATE VIEW 뷰명 (속성명 [, 속성명, ...]) AS SELECT문

CREATE [UNIQUE] INDEX 인덱스명 -- UNIQUE는 중복제거

ON 테이블명(속성명 {ASC | DESC} [, 속성명 {ASC | DESC}) -- Default Sort : ASC

[CLUSTER] -- Tuple을 그룹으로 지정하기 위한거라는데 모르겠다

CREATE TRIGGER 트리거명 [{AFTER | BEFORE}] [{INSERT | DELETE | UPDATE}] ON 테이블명

REFERENCING [{NEW | OLD}] TABLE AS 테이블명 -- NEW는 새로 추가/변경될때 | OLD는 변경되었을때 적용

FOR EACH ROW

WHEN 조건식 -- 예를들어 WHEN new_table.학년='1'

내용 -- 예를들어 BEGIN SET new_table.학년='신입생'; END;

ALTER TABLE 테이블명 ADD 속성명 데이터타입 [DEFAULT '기본값'];

ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값'];

ALTER TABLE 테이블명 DROP 속성명 [CASCADE];

DROP {SCHEMA | DOMAIN | TABLE | VIEW | INDEX | TRIGGER | CONSTRAINT} 이름명 [{CASCADE | RESTRICT}]

2-1) DML(Data Manipulation Language) - SELECT와 JOIN

SELECT

[{ALL | DISTINCT | DISTINCTROW}]

[테이블명.]속성명[ AS 별칭][, [테이블명.]속성명[ AS 별칭], ...]

FROM 테이블명[, 테이블명, ...]

[WHERE 조건] -- 비교연산자 / 논리연산자 / LIKE

[GROUP BY 속성명[, 속성명, ...]

[HAVING 조건] -- 그룹 함수에 대한 조건 ; 그룹함수(AVG(속성명),MAX(속성명),MIN(속성명),SUM(속성명),COUNT(속성명))

[ORDER BY 속성명 {ASC | DESC}[, 속성명 {ASC | DESC}]]; -- 기본값은 ASC

※ GROUP BY로 묶인 속성이나 그룹함수와 일반 속성은 함께 사용 불가.

예) 'SELECT 부서,이름,생일,AVG(기본급) AS 평균 GROUP BY 부서'에섯 이름,생일은 사용불가

※ 그룹함수와 GROUP BY, HAVING절 예시

SELECT 부서, COUNT(*) AS 사원수 FROM 사원 WHERE 기본급>=100

GROUP BY 부서 HAVING COUNT(*)>=2;

사원 테이블에서 기본급이 100이상이고 사원수가 2명이상인 부서의 부서명과 사원수

※ 하위 질의(SELECT IN SELECT)

※ 복수 테이블 검색(FROM 절에 복수의 테이블 / 속성에서 구분은 테이블명.속성명)

※ UNION(합집합) : SELECT문 UNION SELECT문(중복은 제거되서 출력)

JOIN

INNER JOIN

EQUI JOIN : 공통 속성끼리 =(equal) 비교에 의해 같은 값을 가지는 행을 연결

기본 형식

SELECT ... WHERE 테이블명1.속성명 = 테이블명2.속성명; -- WHERE절을 JOIN조건이라 한다.

NATURAL JOIN

SELECT ... FROM 테이블명1 NATURAL JOIN 테이블명2; -- 중복제거

JOIN~USING절

SELECT ... FROM 테이블명1 JOIN 테이블명2 USING(속성명); -- 공통 속성이 같은 이름

NON-EQUI JOIN : JOIN조건에 = 조건이 아닌걸 사용(>, <, <>, 등)

기본 형식

SELECT ... WHERE (조건);

예) WHERE 학생.성적 BETWEEN 성적등급.최저 AND 성적등급.최고;

OUTER JOIN : JOIN조건에 만족하지 않는 튜플도 결과로 출력하기 위한 방법

LEFT OUTER JOIN

우측항 릴레이션과 맞지 않는 좌측항 릴레이션의 튜플에 NULL값을 대입(결국 좌측 릴레이션은 모두 출력)

SELECT ... FROM 테이블명1 LEFT OUTER JOIN 테이블명2 ON 테이블명1.속성명 = 테이블명2.속성명

SELECT ... WHERE 테이블명1.속성명 = 테이블명2.속성명(+);

RIGHT OUTER JOIN

LEFT OUTER JOIN과 반대(결과적으로 우측 릴레이션은 모두 출력)

SELECT ... FROM 테이블명1 RIGHT OUTER JOIN 테이블명2 ON 테이블명1.속성명 = 테이블명2.속성명

SELECT ... WHERE 테이블명1.속성명(+) = 테이블명2.속성명;

FULL OUTER JOIN

JOIN 조건의 양쪽 릴레이션의 튜플들 모두 출력

SELECT ... FROM 테이블명1 FULL OUTER JOIN 테이블명2 ON 테이블명1.속성명 = 테이블명2.속성명

SELF JOIN

같은 테이블에 2개의 속성을 연결해서 EQUI JOIN

2-2) DML - INSERT / DELETE / UPDATE

INSERT

INTO 테이블명[(속성명1[, 속성명2, ...])]

VALUES (값1[, 값2, ...]);

※ VALUES (값1[, 값2, ...]) 대신에 SELECT문 가능

DELETE FROM 테이블명 WHERE 조건;

UPDATE 테이블명 SET 속성명=값[, 속성명=값, ...] WHERE 조건;

3) DCL(Data Control Language) - COMMIT / ROLLBACK / GRANT

COMMIT

트랙잭션의 모든 변경내용을 영구적으로 DB에 반영

ROLLLBACK

트랙잭션의 모든 변경내용을 취소하고 DB를 이전 상태로 되돌리기

GRANT

DB User에게 권한을 부여

i) GRANT { DBA / RESOURCE / CONNECT } TO 사용자ID [IDENTIFIED BY 암호];

ii) GRANT 권한리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];

권한리스트 : ALL / SELECT / INSERT / DELETE / UPDATE / ALTER / 등

REVOKE : 권한 취소

i) REVOKE { DBA / RESOURCE / CONNECT } FROM 사용자ID

ii) REVOKE [GRANT OPTION FOR] 권한리스트 ON 개체 FROM 사용자 [CASCADE];

-- GRANT OPTION FOR는 WITH GRANT OPTION해제

4) View에 대해 따로 정리

기본 테이블의 기본키를 포함한 속성 집합으로 뷰를 구성해야 삽입 / 삭제 / 갱신이 가능

정의된 뷰는 다른 뷰를 정의하는데 기초가 될 수 있다.

둘 이상의 테이블에서 유도된 뷰는 변경X

DISTINCT / GROUP BY / HAVING을 사용해 만든 뷰는 변경X

CREATE VIEW 뷰명[(속성명[, 속성명, ...])]

AS SELECT절

[WITH CHECK OPTION]; -- 뷰에 대한 갱신/삽입 연산시, 뷰의 정의 조건 위배하면 실행 거부(SELECT절의 WHERE절의 참거짓)

예) CREATE VIEW DB_학생 AS SELECT 학번,이름 FROM 학생 WHERE 학과="컴공" WITH CHECK OPTION;

DROP VIEW 뷰명 { RESTRICT | CASCADE };

RESTRICT : 해당 뷰를 다른 곳에서 참조시 제거 취소

CASCADE : 해당 뷰 를 다른 곳에서 참조시 참조하는 다른 뷰나 제약 조건까지 모두 제거

5. 고급 데이터베이스

Index

행 이주(Row Migration)

VARCHAR로 정해진 크기만큼 할당되있다가 더 큰 값을 넣으면 데이터가 다른 블록으로 넘어간는 현상

다른 블록까지 검색하기에 검색 효율이 떨어짐

인덱스 종류

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

                인덱스 키의 순서에 따라 데이터가 정렬되어 있는 방식

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

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

Data Warehouse

Data Mart : 특정 주제나 부서 중심으로 구성된 단일 주제의 Data Warehouse

Data Mining : Data Warehouse에 저장된 데이터집합에서 사용자의 요구에 맞는 정보를 캐내는 기법

OLAP(On-Line Analytical Processing) : Data Mining 기법 중 하나

OLAP의 종류

ROLAP(Relational OLAP) : 관계형 데이터베이스와 관계형 질의어를 사용해 다차원 데이터를 저장하고 분석

MOLAP(Multi-dimensional OLAP) : 특수한 구조의 다차원 데이터베이스를 사용해

Cube Cache라는 주기어 장치 속에 Cube Data를 저장해 검색 속도를 높임

HOLAP(Hybrid OLAP)

ROLAP + MOLAP

OLAP에서의 연산

Roll-Up / Drill-Down : 더 요약된 / 구체적인 정보보기

Pivoting : 보고서의 행/열/페이지 차원을 바꿔보는 기능

Slicing / Dicing :

OLTP(On-Line Transaction Processing) : 온라인 업무 처리 형식의 하나로써 다수의 유저가 DB를 갱신/검색할 때 사용

ODBC(Open DataBase Connectivity)

마소에서 만들었다는거만 알면 될꺼같애