데이터베이스 실무 응용 정리

1. 개요

 

   가. 데이터베이스의 특징

         1) 동시 공유

         2) 계속적인 변화

         3) 실시간 접근성

         4) 내용에 의한 참조

         5) 데이터 중복의 최소화

 

   나. 데이터베이스의 정의

         1) 통합된 데이터

         2) 저장된 데이터

         3) 공용 데이터

         4) 운영 데이터

          -> 업무 처리를 위해 다수의 사용자들이 공동으로 사용하기 위한 데이터의 집합

 

   다. 시스템 카탈로그

       - 데이터에 저장되어 있는 테이블, 인덱스, 뷰, 제약조건, 사용자 등 개체들에 대한 정보와 

         정보들의 관계를 저장한 것. 

         시스템 카탈로그는 데이터 사전이라고도 하며 저장된 내용을 메타 데이터라고 함.

 

   라. DBMS의 필수 기능

         1) 정의 기능

         2) 조작 기능

         3) 제어 기능        

 

   마. DBMS의 구조

 

 

   바. 데이터베이스 언어

         1) DDL : 스키마에 사용되는 제약, 구조 형식, 방식, 정의 목적

              가) CREATE

              나) RENAME

              다) ALTER

              라) TRUNCATE

              마) DROP

         2)  DML : 응용프로그램과 DBMS의 인터페이스를 위한 언어 검색, 삽입, 삭제, 갱신 등

              가) SELECT

              나) DELETE

              다) UPDATE

              라) INSERT

         3) DCL : 보안 및 권한 제어, 무결성, 회복, 병행 제어

              가) GRANT

              나) REVOKE

              다) ROLLBACK

              라) COMMIT

 

   사. 스키마

         1) 외부 스키마(External Schema)

              - 프로그래머나 사용자의 입장에서 데이터베이스의 모습으로 조직의 일부분을 정의한 것

         2) 개념 스키마(Conceptual Schema)

              - 모든 응용 시스템과 사용자들이 필요로 하는 데이터를 통합한 조직 전체의 데이터베이스 구조를 

                논리적으로 정의한 것

         3) 내부 스키마(Internal Schema)

              - 전체 데이터베이스의 물리적 저장 형태를 기술한 것

 

   아. 데이터베이스 관리자(DBA)

         - 데이터베이스 시스템과 관련된 모든 자원에 대해 기획 및 통제를 하며, DB 언어를 통해 DBMS를 거쳐

           DB 시스템의 전체적인 관리 운영에 책임을 지는 사람

 

   자. 데이터베이스 사용자

         1) 응용 프로그래머

              - 데이터베이스의 내용을 일반 사용자가 사용할 수 있도록 프로그램을 개발하는 사람

         2) 일반 사용자

              - 데이터베이스의 내용을 실제 사용하는 사람

         3) 데이터 관리자(DA) DBA가 아니다.

              - 조직 내에서 데이터 정의와 체계화, 감독 및 보안 업무를 담당하는 관리자

   

   차. 데이터 사전

         - 데이터베이스에 저장되어 있는 모든 데이터 정보를 유지 관리하는 시스템.

         - 시스템 카탈로그라고도 한다.

   

   카. 메타 데이터

         - 데이터에 관한 데이터 즉 실제 저장되는 데이터는 아니지만 저장되는 데이터와 직접 혹은 

           간접적으로 관계가 있는 정보를 제공하는 데이터

         - 대표적인 메타 데이터 포멧

             1) MARC : 목록 레코드를 식별하여 축저그 유통할 수 있도록 코드화한 메타데이터

             2) DC : 네트워크 환경에서 각종 전자 정보를 기술하는 메타 데이터

             3) ONIX : 유통에 관한 통계와 체계적인 정보를 취급

             4) MODS : 디지털 도서관의 범용 서지 정보 표준 메타 데이터

         - 메타데이터의 상호운용성 확보 방법

             1) 상호 매핑

             2) MDR : 메타데이터의 등록과 인증을 통해서 메타데이터를 유지관리하는 레지스트리

 

2. 모델링

 

   가. 데이터베이스 설계 과정

         1) 요구조건 분석

         2) 개념적 설계

         3) 논리적 설계

         4) 물리적 설계

         5) 데이터베이스 구현

         6) 유지보수

 

   나. E-R 모델

         - 개체, 속성, 관계 등에 대하여 용이하게 표현할 수 있는 ER 도형을 정의한다.

 

   다. E-R 도형

   

   라. Mapping Rule

         - 개념적 데이터베이스 모델링 결과를 관계형 데이터베이스 이론에 근거하여 데이터베이스 구조로 변환

 

   마. Key

         1) 후보키 : 유일하게 사용되는 속성들의 집합(유일성, 최소성)

         2) 기본키: 후보키 중 특별히 선택된 키(중복x, NULL x)

         3) 대체키 : 후보키 중 기본키를 제외한 후보키

         4) 외래키 : 다른 테이블을 참조하기 위한 키 (외래키와 참조하는 테이블의 기본키는 같다.)

         5) 슈퍼키 : 후보키와 다른 속성들의 모든 조합(유일성)

         6) 복합키 : 기본키 2개를 묶어서 사용

 

   바. 무결성 제약조건

         1) 개체 무결성 : 기본키는 NULL일 수 없다

         2) 참조 무결성 : 외래키는 NULL이거나 참조 릴레이션의 기본키 값과 동일해야 한다.

         3) NULL 무결성 : 특정 속성 값이 NULL이 될 수 없도록 하는 규정

         4) 고유 무결성 : 각 튜플이 갖는 속성 값이 달라야 한다.

         5) 도메인 무결성 : 특정 속성의 값이 그 속성이 정의된 도메인에 속한 값이여야 하는 규정 ex) 성별

         6) 관계 무결성 : 다른 릴레이션 튜플 사이 관계에 대한 적절성 지정

         7) 키 무결성 : 하나의 테이블에는 적어도 하나의 키가 존재해야 한다.

 

   사. 식별 관계 / 비식별 관계

         - 식별 관계 : A 개체의 기본키가 B 개체의 외래키이면서 동시에 기본키가 되는 관계

         - 비식별 관계 : A 개체의 기본키가 B 개체의 비기본키 영역에서 외래키가 되는 관계

 

3. SQL

   가. DDL

         1) CREATE SCHEMA 

              - CREATE SCHEMA 스키마명 AUTHORIZATION 사용자 ID;

              ex) ID가 홍길동인 사용자의 스키마 '대학교'를 정의하는 SQL문

CREATE SCHEAMA 대학교 AUTHORIZATION 홍길동;

         2) CREATE DOMAIN 

              - CREATE DOMAIN 도메인명 데이터_타입

                     [DEFAULT 기본값]

                     [CONSTRAINT 제약조건명 CHECK(VALUE IN(범위 값)];

              ex) 성별을 "남" 또는 "여"와 같은 정해진 1개의 문자로 표현되는 도메인 SEX를 정의하는 SQL문

CREATE DOMAIN SEX CHAR(1)
DEFAULT '남'  
CONSTRAINT VALID-SEX CHECK(VALUE IN('남', '여'));

           3) CREATE TABLE 

              - CREATE TABLE 테이블명

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

                      [PRIMARY KEY(기본키)],

                      [UNIQUE (대체키)],

                      [FOREIGN KEY(외래키) REFERENCES 참조테이블(기본키)

                           [ON DELETE 옵션]

                           [ON UPDATE 옵션]],

                      [CONSTRAINT 제약조건명 CHECK(조건명)];

CREATE TABLE 학생
 (이름 VARCHAR(15) NOT NULL,
  학번 CHAR(8),
  전공 CHAR(5),
  성별 SEX,
  생년월일 DATE,
  PRIMARY KEY(학번),
  FOREIGN KEY(전공) REFERENCES 학과(학과코드)
    ON DELETE SET NULL
    ON UPDATE CASADE,
  CONSTRAINT 생년월일 제약
    CHECK(생년월일 >= '1980-01-01'));

              - PRIMARY KEY : 기본키로 지정

              - UNIQUE : 대체키로 지정, 중복된 값 가질 수 없음

              - FOREIGN KEY ~ REFERENCES : 외래키 지정 CASCADE 자동 적용

                   - ON DELETE 옵션 : 참조 속성 값이 삭제되었을 때 테이블이 취해야할 사항 지정

                   - ON UPDATE 옵션 : 참조 속성 값이 변경되었을 때 테이블이 취해야 할 사항 지정

                          - NO ACTION : 아무 조취 취하지 않음

                          - CASCADE : 관련 속성도 모두 변경

                          - SET NULL : 관련 튜플의 속성을 NULL로 변경

                          - SET DEFAULT : 관련 속성 값을 기본값으로 변경

                   - CONSTRAINT : 제약 조건의 이름을 지정

                          - CHECK : 제약조건을 명시

               - CONSTRAINT를 사용해서 FOREIGN KEY의 이름을 지정할 수 있다.

                   - CONSTAINT 제약조건명 FOREIGN KEY(전공) REFERENCES 학과(학과코드)

                                   

         4) CREATE VIEW

                - VIEW 란     

                    가) 하나 이상의 테이블로부터 유도되어 만들어진 가상 테이블

                    나) 물리적 공간 존재하지 않는다.

                    다) 기억 공간을 차지하지 않는다.

                    라) 논리적 독립성을 유지한다.

                    마) 데이터 접근제어 보안성을 향상시킨다. 

              - CREATE VIEW 뷰명[(속성명)]

                AS SELECT문;

              ex) <고객> 테이블에서 주소가 '안산시'인 고객들의 성명과 전화번호를 '안산고객'이라는 뷰로 정의하시오.

CREATE VIEW 안산 고객(성명, 전화번호)
AS SELECT 성명, 전화번호                
FROM 고객              
WHERE 주소 = '안산시';

          5) CREATE INDEX

               - CREATE [UNIQUE] INDEX <인덱스명>

                      ON 테이블명 ({속성명[ASC/DESC], [속성명[ASC/DESC]]})

                      [CLUSTER];

               - CLUSTER : 지정된 키에 따라 튜플들을 그룹으로 지정하기 위해 사용

               ex) <고객> 테이블에서 UNIQUE한 특성을 갖는 고객번호 속성에 대해 내림차순 정렬하여

                    '고객번호_idx'라는 이름으로 인덱스를 정의하시오

              CREATE UNIQUE INDEX 고객번호_idx

              ON 고객(고객번호 DESC);

CREATE UNIQUE INDEX 고객번호_idx  
ON 고객(고객번호 DESC);

          6) ALTER TABLE

               - ALTER TABLE 테이블명 ADD 속성명 데이터_타입 [DEFALUT '기본값']; -> 속성 추가

               - ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값]; -> 속성의 기본값 변경

               - ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE]; -> 속성 제거

 

          7) DROP -> 스키마, 도메인, 테이블, 뷰, 인덱스, 트리거 제거 가능 

               - DROP SCHEMA 스키마명 [CASCADE | RESTRICT];

               - CASCADE 

               - RESTRICT : 다른 개체가 참조중일 경우 제거 취소

 

 

4. 정규화

   가. 함수적 종속

         - X -> Y 관계를 갖는 속성 X와 Y에서 X를 결정자라 하고 Y를 종속자라 한다.

 

   나. 이상현상(Anomaly)

         1) 데이터베이스 설계 시 데이터의 중복과 종속으로 인해 발생되는 문제점

         2) 삽입이상 : 원하지 않는 자료 삽입, 자료가 부족해 삽입불가로 인한 문제점

         3) 삭제이상 : 하나의 자료를 삭제할 때 그 자료가 포함된 튜플이 삭제됨으로 인해 

                           원하지 않는 자료까지 삭제하여 발생하는 문제점

         4) 갱신이상 : 정확하지 않는 튜플 또는 일부의 튜플만 갱신하여 발생하는 문제점

 

  다. 정규화

        -> 데이터의 중복성과 종속성을 제거하기 위해, 이상현상을 해결하기 위해

            릴레이션의 종속관계를 분석해 여러 개의 릴레이션으로 분해하는 과정

     

 

         1) 완전 함수적 종속 : 어떤 테이블 R에서 속성 Y가 다른 속성들의 집합 X 전체에 대해 함수적 종속이면서

                                      X의 어떠한 진부분 집합에도 함수적 종속이 아닐 때 완전 함수적 종속이라고 한다.

         2) 이행적 함수적 종속 : A -> B 이고 B -> C 일때 A -> C를 만족하는 관계

         3) 다치 종속 : A, B, C 3개의 속성을 가진 테이블 R에서 어떤 복합 속성(A, C)에 대응하는 B 값의 집합이

                            A 값에만 종속되고 C 값에는 무관하면 B는 A에 다중값 종속이라고 하고 A ->-> B로 표기

         4) 조인 종속 : 어떤 테이블 R의 속성에 대한 부분 집합 X, Y ...  Z가 있다고 하자.

                            이때 만일 테이블 R이 자신의 프로젝션 X, Y ... Z를 모두 조인한 결과와 동일한 경우

                            테이블 R은 조인 종속을 만족한다고 한다.

 

5. 실무응용

 

   가. 내장 SQL(Embedded-SQL)

         - 일반 응용프로그램에 SQL을 삽입하여 데이터베이스 자료를 이용하고 다양한 조작을 할 수 있도록 한 것

             -> 삽입된 QSL이다.

 

   나. 커서(Cursor)

         - 내장 SQL에 의해 얻은 값은 자료(튜플)단위로 처리되는데 여러개의 자료를

           그룹으로 처리하기 위해서 커서를 이용한다.

 

   다. 스토어드 프로시저(Stored Procedure)

         - 수행해야할 SQL 처리과정을 미리 하나의 작은 프로그램으로 작성하여 데이터베이스에 

           저장해두었다가 필요한 경우 호출하여 사용하기 위해 만들어 놓은 프로그램

 

   라. 트랜잭션

         - 데이터베이스 내에서 한꺼번에 모두 수행되어야할 연산의 집합

         1) 트랜잭션의 성질

              가) 원자성 Atomicity

              나) 독립성 Isolation

              다) 일관성 Consistency

              라) 영속성 Durability

         2) 트랜잭션의 연산

              가) COMMIT : 트랜잭션이 성공적으로 종료된 후 수정내용을 지속하여 유지하기 위한 연산

              나) ROLLBACK : 트랜잭션이 비정상적 수행 혹은 오류가 발생했을 때 작업을 취소하고 이전상태로 되돌림

         3) 트랜잭션의 스케쥴

              가) 직렬 스케쥴 : 구성하는 연산들을 연속적으로 모두 실행하고 다른 트랜잭션을 수행하는 방식

              나) 비직렬 스케쥴 : 인터리브 실행 기법을 이용하여 각 트랜잭션의 연산들을 번갈아가면서 병행실행

              다) 직렬 가능 스케쥴 : 비직렬 스케쥴과 직렬 스케쥴의 결과가 동일하게 나오는 케이스

 

   마. 회복

         - 트랜잭션들을 실행하는 도중 장애가 발생하여 데이터베이스가 손상되었을 경우 손상되기 이전의

           정상 상태로 복구하는 작업

         1) 장애의 유형

              가) 시스템 장애

              나) 미디어 장애

              다) 트랜잭션 장애

              라) 실행 장애

         2) 회복 관리기(Recovery Manager)

              가) 로그

              나) 메모리 덤프

         3) 회복 기법

              가) 연기 갱신 기법

              나) 즉시 갱신 기법

              다) 그림자 페이지 기법

              라) 검사점 기법

              마) 미디어 회복 기법

              

   바. 병행제어

         - 트랜잭션 병행 실행 시 문제가 발생하지 않도록 제어하는 것

         - 병행제어 미수행시 문제점

             1) 모순성

             2) 연쇄 복귀

             3) 갱신 분실

        가) 로킹(Locking)

              - 병행 실행 시 하나의 트랜잭션이 사용하는 데이터베이스 내의 데이터를 다른 트랜잭션이 

                접근하지 못하게 하는 것

              - LOCK -> 트랜잭션 실행 -> 트랜잭션 완료 -> UNLOCK

              - 로킹 단위 : 테이블, 속성, 튜플, 로킹 단위가 크면 병행성 수준 낮아지고 작으면 병행성 수준 높아진다.

        나) 병행제어 기법

               1) 2-단계 잠금 규약 : 트랜잭션 스케쥴의 직렬성을 보장하는 대표적인 잠금 기법

               2) 타임스태프 기법 : 각 트랜잭션이 데이터에 접근할 시간을 미리 저장하여 기억한 뒤

                                           시간의 순서에 따라 데이터에 접근하여 수행하는 병행제어의 일종

 

   사. 인덱스

         - 수많은 데이터 중에서 원하는 자료를 빠르고 효율적으로 검색하기 위하서 사용하는 방법

         1) B 트리 인덱스 : 검색의 효율을 높이기 위해 자료의 구조를 균형있는 트리 구조로 나타냄

         2) B+트리 인덱스 : B트리의 변형구조

         3) 비트맵 인덱스 : Bit 값인 0 또는 1로 변환하여 인덱스 키로 사용

         4) 역방향 인덱스 : 인덱스 컬럼의 데이터를 역으로 변환하여 인덱스 키로 사용

         5) 클러스터드 인덱스 : 인덱스 키의 순서에 따라 데이터가 정렬되어 저장되는 방식

                                        삽입/삭제 시 순서를 유지하기 위해 데이터를 재정렬 해야함

         6) 넌클러스터드 인덱스 : 인덱스의 키값만 정렬, 실제 데이터는 정렬되어 있지 않다.

                                           클러스터드 보다 속도는 느리지만 재정렬할 필요가 없다.

          * 인덱스를 설계할 때는 분포도가 좋도록 설계해야 한다.

 

   아. 객체 지향 데이터베이스(OODB)

         1) 클래스 : 유사한 특성을 갖는 객체들의 모임

         2) 캡슐화 : 하나의 객체를 데이터/연산등의 정보와 하나로 묶음으로써 정보은닉이 이루어지게 한다.

         3) 상속 : 상위 클래스의 특징과 정보를 하위 클래스에서 재사용할 수 있도록 하는 것

         4) 다형성 : 동일한 이름의 연산이 각 클래스에 따라 다르게 동작하는 것

 

   자. 정보보안의 목표

         1) 기밀성

         2) 무결성

         3) 가용성

   

   차. 암호화 기법

         1) 개인키 암호화 기법

               - 동일한 키로 데이터를 암호화하고 복호화 한다. 대표적으로 DES 기법이 있다.

         2) 공개키 암호화 기법

               - 데이터를 암호화할 때 사용하는 공개키는 사용자에게 공개하고 복호화할때의 비밀키는 비밀리에 관리

                 대표적으로 RSA 기법이 있다.

 

   카. 분산 데이터베이스 목표 

         1) 병행 투명성

         2) 장애 투명성

         3) 중복 투명성

         4) 위치 투명성

 

   타. 데이터 웨어하우스(다량의 데이터를 통합하여 관리하는 다차원 데이터베이스)

        - 급증하는 다량의 데이터를 효과적으로 분석하여 정보화하여 공통의 형식으로 변환하여 관리하는 데이터베이스 

        - 데이터 웨어하우스는 다차원 데이터베이스이다.

 

   파. 데이터 마트(특정 주제로 모은 소규모 데이터 웨어하우스)

        - 데이터 웨어하우스로부터 특정 주제나 부서 중심으로 구축된 소규모 단일 주제의 데이터 웨어하우스

          대량의 다양한 정보를 사용자요구에 따라 체계적으로 분석하여 기업경영 활동을 돕기위한 시스템

 

   하. 데이터 마이닝(데이터 웨어하우스에서 정보를 찾기 위함)

         - 데이터 웨어하우스에 저장된 데이터 집합에서 사용자의 요구에 따라 유용하고 가능성있는 정보를 발견하기

           위한 기법이다. 

         - 대량의데이터로부터실존하지않는정보를얻어내기위해AI기법등으로 통계규칙이나패턴을찾는 것

 

   A. OLAP

        - 사용자가 직접 검색할 수 있는 분석형 어플리케이션

         1) MOLAP : 다차원 DB 기반 OLAP 구조

         2) ROLAP : 관계형 DB 기반 OLAP 구조

         3) HOLAP : 다차원 + 관계형

 

   B. OLTP

        - 중앙컴퓨터에 접속된 다수의 단말기에 트랜잭션 처리 결과를 즉시 되돌려주는 처리 형태

 

   C. 관계형 데이터베이스

       - 데이터를 열과 행으로 이루어진 이차원의 릴레이션으로 표현하는 데이터베이스

   

   D. 데이터 큐브

        - 데이터가 여러 차원으로 모델링되는 것으로, 차원과 사실로 정의된다.