[정보처리기사] Database

2017 - 10 - 04 (수)

데이터베이스 정의

관계 데이터 연산

물리적 설계

정처기 DB 공부 (기사퍼스트 실기 문제집 참조)

1 . 데이터베이스 정의

DB정의 : 특정 조직이 업무 수행하는 데 필요한 관련성 있는 자료들의 집합체

-Integrated data : 자료의 중복을 배제 또는 최소화한 데이터의 집합

-Stored data : 저장매체에 저장된 데이터의 집합

-Operational data : 없어서는 안될 반드시 필요한 데이터의 집합

-Shared data : 공동으로 소유하고 유지하는 데이터의 집합.

DB의 특징

- Real time Accessibility

- Content reference

- Concurrent sharing

- Continuous evolution

- 데이터웨어하우스(DW) : 사용자의 효율적인 의사결정에 도움을 주기위해서 시스템에서 추출하고 변환, 통합된 주제중심적인 DB

스키마(Schema) : 데이터베이스의 구조와 제약조건에 대한 명세

- 외부 스키마 : 사용자나 응용프로그래머 관점에서 데이터베이스의 논리적 구조를 정의해 놓은 것.

- 개념 스키마 : DB 전체적인 논리적 구조

- 내부 스키마 : DB 전체적인 물리적 구조 , DBA가 관리한다.

RDB의 릴레이션(테이블) 구조 : 릴레이션이란 RDB 모델에서 2차원 테이블 형태로 나타내는 모델이다.

- 도메인(Domain) : 한 속성에 나타날 수 있는 값들의 범위

- 차수(Degree) : 속성(열)들의 개수

- 카디날리티(Cardinality) : 튜플(행)의 갯수

- 릴레이션 스킴 : 속성들의 집합

- 릴레이션 인스턴스 : 튜플들의 집합

- 인스턴스 : DB에 저장되는 값들

// Data modeling : 현실세계를 데이터 관점에서 파악하여 개념적인 모델로 표현하는 것.

// 상관 모델링 : 데이터 모델과 프로세스 모델을 비교 검토하여 서로간에 잘 맞는지 판단한다. CRUD 매트릭스를 이용한다.

// 정합성 : 무모순성의 상태로 데이터 무결성과 데이터 통합성이 이루어진 상태.

// Mapping rule(사상) : ERD에서 릴레이션 스키마로 변환하는 과정

키(Identifier) : 특정 조건에 맞는 튜플을 구분할 수 있는 단일 속성 또는 속성 그룹.

슈퍼키 : 한 릴레이션 내에 있는 속성들의 집합으로 구성된 키
후보키 : 유일성과 최소성을 가진 최소 슈퍼키
기본키 : 후보키 중에서 선택된 키
대체키 : 후보키 중에서 기본키를 제외한 속성
식별관계 : 상위개체의 기본키가 하위 개체의 외래키면서 동시에 기본키가 되는 관계( 상위 개체 PK = 하위개체의 PK,FK )
비식별관계 : 상위 개체의 기본키가 하위 개체에 일반 속성으로 전이 (상위 개체의 PK = 하위 개체의 FK)

무결성 제약조건

참조 무결성 : 릴레이션은 참조할 수 없는 외래키 값을 가질 수 없음 , 즉 참조키로 가져온 값이 참조테이블에 있어야한다. (b테이블에서 a테이블의 기본키를 참조키로 가지고 있을때, b테이블의 튜플을 마음대로 삭제할 수 도 변경할 수 도 없다. )
개체 무결성 : 한 릴레이션의 기본키를 구성하는 어떤 속성 값도 Null 값이나 중복된 값을 가질 수 없다.
도메인 무결성 : 각 속성 값은 반드시 정의된 도메인에 속한 값이어야한다.

Data Dictionary : 데이터베이스에 저장되어 있는 모든 개체들에 대한 정보를 유지/관리 하는 시스템 (시스템 카탈로그)

이상(Anomaly) : 릴레이션에서 일부 속성의 종속으로 인한 중복발생

갱신이상 : 반복된 데이터 중에 일부만 수정하면 데이터의 불일치가 발생한다.
삭제이상 : 데이터를 삭제하고자할 때 필요한 데이터까지 삭제해야된다
삽입이상 : 데이터를 추가하고자할 때 필요하지 않은 데이터까지 추가해야한다.

정규화 : 데이터의 중복을 방지하고, 삽입 / 삭제 / 갱신 이상을 방지하기위함.

제 1 정규형 : 반복되는 속성을 제거한 뒤 모든 속성이 원자 도메인 만으로 되어 있는 정규형. (데이터가 중복된 경우 ex : 신희준 님의 자격증이 정보처리기사, SQLD가 있을 때 튜플 2개를 사용 회원과 자격증 테이블로 나눈다.)
제 2 정규형 : 부분 함수적 종속을 제거하여 완전 함수적 종속을 만족하는 정규형 (회원 테이블이면 기본키에 회원에 관련한 속성들만 종속되어야한다.)
제 3 정규형 : 이행 함수적 종속 관계를 제거한다. (과목테이블에서 과목번호 -> 회원번호, 회원번호 -> 주민번호 형태의 종속관계를 과목테이블과 회원테이블로 나누어 과목번호 -> 주민번호로 바꾼다.)
BCNF : 결정자가 후보키가 아닌 함수 종속 제거 모든 결정자가 후보키여야 함. (후보키가 아닌 속성에 종속되어서는 안된다. 과목테이블의 과목번호 1,2,3은 A교수의 과목일 경우 교수테이블을 분리하여 교수번호를 참조키로 하고 과목테이블을 다시 구성한다.)
제 4 정규형 : 다치 종속을 제거한다.
제 5 정규형 : 후보키를 통하지 않은 조인 종속을 제거한다.

2 . 관계 데이터 연산

관계 대수 : 절차적 언어이며 원하는 정보를 ‘어떻게’ 유도하는가를 연산자와 연산 규칙 이용하여 기술

Select(σ) : 릴레이션에서 주어진 조건을 만족하는 튜플을 검색하는 것 (행)
Project(π) : 릴레이션에서 주어진 조건을 만족하는 속성들을 검색하는 것 (열)
Join(▷◁) : 두개의 릴레이션 A와 B에서 공통된 속성을 연결하는 것.
DIVISION(÷) : 나누어지는 릴레이션인 A는 릴레이션 B의 모든 내용을 포함한 것이 결과 릴레이션이 된다.

(학생 테이블에서 이름이 신희준이고 학년이 2학년인 학생의 이름, 학번 데이터를 가져오기)
EX : π 이름, 학번 (σ 이름 = “신희준” ∧ 학년 = 2 (학생))

관계 해석 : 비절차적 언어이며 원하는 정보가 ‘무엇’ 이라는 것만 정의

데이터 모델 구성요소

Structure : 논리적으로 표현된 개체 타입들 간의 관계로서 데이터 구조 및 정적 성질 표현
Operation : 데이터베이스에 저장된 실제 데이터를 처리하는 작업에 대한 명세, 데이터베이스를 조작하는 기본 도구.
Constraint :데이터베이스에 저장될 수 있는 실제 데이터의 논리적인 제약 조건

2 . 관계 데이터 연산

DDL

CREATE DOMAIN

-- 도메인 만들기 --
-- SEX라는 도메인을 만든다. 기본값은 '여' 이고 도메인의 범위는 여, 남 으로 한정짓는다. 제약조건의 이름은 VALID_SEX이다.
CREATE DOMAIN SEX CHAR(2)
DEFAULT '여'
CONSTRAINT VALID_SEX CHECK(VALUE IN('여','남'));
--CHECK 제약조건은 속성에서 허용가능한 데이터의 범위나 조건을 지정해주는 제약조건이다.

CREATE TABLE

--테이블 만들기 --
CREATE TABLE 학생 (
  학번 CHAR(10),
  이름 CHAR(10),
  성별 SEX, --위에서 정의해준 도메인을 가져옴.
  생년월일 DATE,
  PRIMARY KEY(학번), --기본키를 학과로 지정
  UNIQUE(이름), -- 이름은 유니크하게 중복 X
  FOREIGN KEY(학과코드) REFERENCES 학과(학과코드)
    ON DELETE SET NULL ON UPDATE CASCADE
  , --학과 테이블의 학과코드를 참조키로
  CHECK 생년월일 >="1992-05-03" -- 생년월일은 해당 날짜 이상만 입력되도록.
);

CREATE VIEW

VIEW : 사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위한 가상 테이블이다. 논리적으로만 존재하고 물리적으로는 존재하지 않는다.

-- 뷰 만들기 --
CREATE VIEW 여학생_VIEW(이름, 성별) AS
  SELECT 이름, 성별
  FROM 학생
  WHERE 성별 = '여'
  WITH CHECK OPTION; -- VIEW에대한 수정 삽입 연산 실행 및 WHERE절 조건에 위배될 경우 실행을 거부하다.

CREATE INDEX

--인덱스 만들기 --
CREATE UNIQUE INDEX 이름_IDX
  ON 학생(이름 ASC)
  CLUSTER;

ALTER TABLE

--테이블 구조변경--
ALTER TABLE 학과 ADD 연락처 CHAR(10); -- 새로운 속성 추가
ALTER TABLE 학과 ALTER 학과명 SET DEFAULT 'MIS' -- 기존 속성 변경 및 기본값을 MIS로
ALTER TABLE 학과 DROP 학과명 -- 기존속성 제거

DROP TABLE

--테이블 제거--
DROP TABLE 학과 CASCADE; --학과 테이블 삭제 CASCADE : 참조하는 테이블을 연쇄적으로 제거

DML

INSERT

INSERT INTO 학생 VALUES('2014280447','신희준','서울특별시영등포구...','3');

DELETE

DELETE FROM 학생 WHERE 이름 = '신희준';

SELECT

SELECT * FROM 학생 WHERE 이름 LIKE '신%' --이름이 신으로 시작하는 데이터 SELECT
SELECT * FROM 학생 WHERE 주소 IS NULL; -- 주소가 NULL인 튜플 검색
SELECT DISTINCT 과목 FROM 학생 WHERE 학년 >=3; -- 3학년 이상인 과목을 중복없이 검색

UPDATE

UPDATE 학생 SET 학과 = "DM" WHERE 학년 <=3;
UPDATE 학생 SET 학년 = 학년+1 ;

DCL
GRANT - 권한주기 / REVOKE - 권한 취소

--SHJ 에게 학생테이블에대한 SELECT권한 부여
GRANT SELECT ON 학생 TO SHJ;
GRANT ALL ON 학생 TO SHJ;
REVOKE SELECT ON 수강생 FROM KWS CASCADE;

COMMIT : 트랜잭션의 성공했을 경우 그 결과를 DB에 적용하여 완료시킴

ROLLBACK : 트랜잭션의 실패로 작업을 취소하고 이전상태로 되돌림.

3 . 물리적 설계

역정규화 : 시스템의 성능 향상을 위해서 정규화에 위배되는 행위를 하는 것.

튜닝 : 데이터베이스가 일정한 성능을 유지할 수 있도록 비효율적인 요소를 제거하고 성능 개선을 위하여 SQL문장을 포함, 데이터베이스의 여러 요소를 조정하는 작업.

로킹 : 하나의 트랜잭션이 데이터를 엑세스하는 동안 다른 트랜잭션이 그 데이터 항목을 엑세스 할 수 없도록 하는 방법. (병행제어 기법)

로킹 기법

- 공유잠금(Shared-lock) : 판독(read) 연산은 실행할 수 있으나 기록(write) 연산은 실행할 수 없다.

- 전용잠금(Exclusive-lock) : 판독(read) 연산은 물론 기록 (write) 연산도 실행 할 수 있다.

2단계 로킹 프로토콜

- 확장 단계(Growing phase) : 트랜잭션들은 잠금 연산만 수행할 수 있고 해제연산은 수행 불가

- 축소 단계(Shrinking phase) : 트랜잭션은 해제연산만을 수행할 수 있고 잠금 연산은 수행할 수 없다.

병행제어 필요성 : 갱신내용 손실(Lost Update), 모순성(Inconsistency), 연쇄적인 복귀(Cascading rollback) 의문제가 발생할 수 있기 떄문

모순성 : 복수의 사용자가 동시에 같은 데이터를 갱신할 때 발생하는 모순된 결과.

연쇄적인 복귀 : 병행 수행되던 트랜잭션 중 어느하나가 문제가 생겨 Rollback되는 경우 다른 트랜잭션도 함께 Rollback되는 현상

갱신 내용 손실 : 동일한 필드의 내용을 두개의 트랜잭션이 동시에 이용함으로써 갱신 내용이 손실되는 결과를 초래

시간 스탬프 순서 기법(Time Stamp Ordering) : 고유 번호인 시간스탬프를 트랜잭션에 부여하는 것.

OLAP (Online Analytical Procession) : 데이터 웨어하우스를 다차원 적으로 분석하고 시각화 하는 과정
OLAP 연산

- Drill up : 작은 범위에서 큰범위로 단계적 접근

- Drill down : drill up 의 반대

- Drill Across : 다른 큐브의 데이터에 건너서 접근

- Drill Through : 데이터웨어하우스 혹은 OLAP 시스템에 존재하는 상세데이터에 접근

- Pivoting : 보고서의 열과 행, 페이지의 차원들을 바꿈

- Slicing : 3차원 큐브를 특정 관점으로 잘라서 보는 기법

- Dicing : Slciing 기법에서 더 쪼개어 나가는 방법

트랜잭션 : 데이터베이스 접근 기본 단위이다.

트랜잭션의 특성 ( ACID 특성 )

- 원자성(Automicity) : 트랜잭션이 완료될 때 처리가 완료되거나 그 이전의 상태여야함. 모두 반영되거나 전혀 반영되지않아야함 (부분 실행 되어서는 안된다. 가장 중요함)

- 일관성(Consistency) : 트랜잭션이 그 실행을 성공적으로 완료하면 언제나 일관성 있게 DB상태로 변환되어야 함

- 독립성(Isolation) : 둘이상의 트랜잭션이 병행 실행되고 있을 때 또다른 트랜잭션이 끼어들 수 없다.

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

트리거(trigger) : 특정 테이블의 데이터에 변경이 가해졌을 때 연쇄적으로 수행되는 저장 프로시져라고 할 수 있다.

커서(cursor) : 질의 결과인 복수 개의 튜플에서 다음에 처리될 튜플을 지시하는 지시자를 의미

메타데이터 : 데이터를 설명해 주는 데이터. 대량의 정보가운데 찾고 있는 정보를 효율적으로 찾아내서 이용하기 위해 일정한 규칙에 따라 콘텐츠에 부여되는 데이터

MARC : 컴퓨터가 목록 데이터를 식별하여 축적, 유통할 수 있도록 코드화한 메타데이터 표준 형식. 미국의회 도서관에서 개발하였다.

상호 운용성 : 상호간에 통신할 수 있고 정보 교환이나 일련의 처리를 정확하게 실행할 수 있는 것

MODS : 메타데이터들과 매핑테이블이 작성되어 상호운용성이 입증된 메타데이터

MDR : 메타 데이터의 등록과 인증을 통하여 메타데이터를 유지, 관리하여 메타데이터의 명세를 공유하는 레지스트리. (메타데이터 등록소)

데이터베이스 중복저장 기법

- 덤프(Dump) : 데이터베이스 내용 전체를 일정 기간마다 다른 저장 장치 디스크에 저장하는 것.

- 로그(Log) : 변경될 떄마다 변경 내용을 저장