HYS

HYS

ㅇㅅㅇ

정보처리기사 정리 - 데이터베이스 구축

데이터베이스 구축

데이터 베이스

정보 시스템

  • 자료(Data) : 단순 사실 결과값, 가공 x
  • 정보(Infomation) : 의사결정에 도움을 주는 유용한 현태, 가공 o

데이터베이스 정의

  • 통합된 데이터: 자료의 중복을 배제
  • 저장된 데이터: 컴퓨터가 실시간 접근을 할 수 있는 저장메체에 저장된 자로
  • 운영 데이터: 존재 가치가 확실하고 없어서는 안되는 필수적인 자료
  • 공용 데이터: 여러 응용시스템이 공동소지, 유지하는 자료

기존 파일처리 방식에서의 문제점

  • 종속성으로 인한 문제점
    • 종속성: 응용프로그램과 데이터 파일이 상호 의존적인 관계에 있는 것
    • 저장된 데이터의 접근 방법 변경 시 응용프로그램도 같이 변경해야됨
  • 중복성으로 인한 문제점
    • 일관성 : 중복된 데이터 간의 내용이 일치하지 않는 상황
    • 보안성 : 중복된 모든데이터에 동등의 보안수준 유지 어려움
    • 경제성 : 동일한 데이터 반복작업으로 저장공간 낭비, 비용 낭비
    • 무결성 : 제어의 분산으로 데이터의 정확성 유지 어려움

DBMS 의 정의 및 장단점

  • DBMS란 사용자와 데이터베이스 사이에서 사용자의 요구에 따라 정보 생성, DB 관리해주는 SW
  • DBMS의 기능
    • 정의기능, 정의기능, 제어 기능
  • DBMS의 장점
    • 데이터의 중복 피함, 저장된 데이터 공동 이용, 일관성 유지, 보안 유지
    • 데이터의 표준화 가능, 데이터 통합관리, 최신 데이터 유지 등
  • DBMS의 단점
    • DB 전문가 부족, 전산화 비용 증가, 대용량 디스크의 집중적 Access로 과부하 발생 등

스키마(Schema)

  • 스키마란 데이터베이스의 구조화(개체, 속성, 관계), 제약조건에 대해 전반적인 명세를 기술한 것
  • 스키마는 데이터 사전(메타데이터)에 저장
  • DB의 3계층
    • 외부 스키마 : 사용자나 응용프로그래머가 각 개인의 입장에서 필요로 하는 DB 구조로, 하나의 DB 시스템에 여러개의 외부 스키마 존재 가능
    • 개념 스키마 : DB의 전체적인 논리적 구조로 하나만 존재한다. DB 관리자에 의해 구성
    • 내부 스키마 : 물리적 저장 장치의 입장에서 본 DB 구조로 실제로 DB에 저장될 레코드의 물리적 구조 정의

DB 언어

  • 데이터 정의어(DDL: Data Definition Language)
    • DB의 구조, 데이터 형식, 접근 방식 등 DB 구축하거나 수행할 목적의 언어
    • CREATE, ALTER, DROP
  • 데이터 조작어(DML: Data Manipulation Language)
    • 사용자로 하여금 데이터를 처리할 수 있게 하는 도구, 검색, 삽입, 삭제, 변경
    • SELECT, INSERT, UPDATE, DELETE
  • 데이터 제어어(DCL: Data Control Language)
    • 무결성, 보안 및 권한제어 회복 등을 하기 위한 언어
    • GRANT, REVOKE, COMMIT, ROLLBACK

데이터 모델의 구성요소 및 표시사항

  • 표시사항
    • 구조(Structure): 논리적으로 표현된 개체들 간의 관계
    • 연산(Operation): DB에 저장된 실제 데이터 처리하는 방법
    • 제약조건(Constrait): DB에 저장될 수 있는 실제 데이터의 논리적 제약조건 명시
  • 데이터 모델의 구성요소
    • 속성(Attribute) : 데이터의 가장 작은 논리적 단위로서 파일 구조의 데이터 항목 또는 데이터 필으데 해당하는 것
    • 개체(Entity) : DB에 개념 또는 정보 단위를 표현한 것으로 서로 연관된 몇개의 속성으로 구성, 파일시스템의 레코드에 대응하는 것.
    • 관계(Relationship) : 개체간의 관계 또는 속성간의 관계
  • E-R 다이어 그램
    • E-R 모델은 개념적 데이터 모델의 가장 대표적인 것으로 개체 타입과 이들 간의 관계 타입을 이용
    기호 이름 의미
    사각형 개체(Entitity)
    마름모 관계(Relationship)
    타원 속성(Attribute)
    밑줄 타원 기본키 속성(Primary Key)
    복수 타원 복합 속성
    관계 n:m
    선, 링크 개체 속성간의 연결

데이터베이스의 Relation의 구조

  • 튜플
    • 릴레이션을 구성하는 각각의 행으로 속성의 모음
    • 튜플의 수 = 카디널리티
  • 속성
    • 파일 구조상의 데이터 항목, 또는 데이터 필드(열)에 해당, DB를 구성하는 가장 작은 단위
    • 속성의 수 = Degree
  • 도메인
    • 하나의 속성이 취할 수 있는 같은 타입의 원자의 집합

KEY의 개념과 종류

DB에서 다른 튜플들과 구분할 수 있는 유일한 기준이 되는 속성

  • 후보키
    • 릴레이션을 구성하는 속성들 중 튜플을 유일하게 식별하기 위해 사용하는 속성의 부분집합
    • 릴레이션 내에서 유일성 최소성을 지녀야 함
  • 기본키
    • 후보키 중에서 선택한 기본 키, 특정 튜플을 유일하게 구별 가능하다.
    • Null 값을 가질 수 없음
    • 기본 키로 정의된 속성에는 동일한 값 중복 저장 x
  • 대체키
    • 후보키가 2개 이상일때 기본키를 제외한 나머지 후보키
  • 슈퍼키
    • 한 릴레이션 내에 속성들의 집합으로 구성된 키
    • 릴레이션을 구성하는 모든 튜플에 대해 유일성을 만족시키나 최소성을 만족시키지 않음
  • 외래키
    • 관계를 맺고 있는 릴레이션 간에 참조하는 릴레이션의 기본키와 같은 속성

무결성

  • 개체 무결성 : 기본키를 구성하는 속성값은 Null이나 중복값을 가질 수 없다.
  • 참조 무결성 : 외래키의 값은 Null또는 기본키의 값과 같아야 함.
  • 도메인 무결성 : 특정 속성 값이 그 속성에 정의된 도메인과 같은 값이여야 함.

관계 대수

  • 관계형 DB에서 원하는 정보와 그 정보를 어떻게 유도하는지에 대한 절차적인 언어
  • 순수 관계 연산자
    • SELECT : 튜플 중 선택 조건을 만족하는 튜릉의 집합을 만들어 릴레이션 생성, 수평연산
    • PROJECT : 속성 list에 제시된 속성만 추출, 수직 연산
    • JOIN : 공통 속성을 중심으로 2개의 릴레이션을 하나로 합쳐 새로운 릴레이션 생성
    • DIVISION : 선택된 속성을 제외한 나머지를 새로운 릴레이션으로 생성
  • 일반 집합연산자
    • 합집합(UNION), 교집합(INTERSECTION), 차집합(DIFFERENCE), 교차곱(CARTESIAN PRODUCT)

관계 해석

  • 원하는 정보가 무엇인지만 정의하는 비절차적 특징을 가진 언어
  • 원하는 정보 정의시 계산 수식 사용

이상(Anomaly) 의 개념 및 종류

  • 정규화거치지 않은 DB 내에서 데이터 들이 불필요하게 중복되 릴레이션 조작 시 발생하는 예기치 못한 곤란한 현상이 이상이다.
    • 삽입 이상 : 릴레이션에 데이터 삽입할 떄 의도와 상관없이 불필요한 값 삽입됨
    • 삭제 이상 : 릴레이션에서 튜플 삭제 시 원하지 않은 값도 같이 삭제
    • 갱신 이상 : 릴레이션의 튜플에 있는 속성 값 갱신 시 일부 정보만 갱신 되어 정보의 모순이 발생하는 현상

정규화

  • 정규화의 개요 : 종속성 이론을 이용하여 잘못 설계된 스키마를 더 작은 속성의 세트로 쪼개 바람직한 스키마로 만들어가는 과정
  • 정규화의 목적 : 데이터 구조의 안정성을 최대화, 중복을 배제해 삽입, 삭제, 갱신 이상 발생 방지, 데이터 삽입시 릴레이션 재구성 필요성 떨어짐
  • 정규화 원칙 : 정보의 무손실 표현, 데이터의 중복성 제거

  • 정규화 과정

    정규화 수행내용
    비정규 R -
    ↓ 도메인이 원자 값
    1NF -
    ↓ 부분적 함수 종속 제거
    2NF -
    ↓ 이행적 함수 종속 제거
    3NF -
    ↓ 결정자 이면서 후보키가 아닌것 제거
    BCNF -
    ↓ 다치 종속 제거
    4NF -
    ↓ 조인 종속성 이용
    5NF -

VIEW

  • 정의
    • 사용자에게 접근이 허용된 자료만 제한적으로 하나 이상의 기본테이블에서 유도한 가상 테이블
    • 저장장치 내 물리적으로 존재하지 않음, 사용자에게 있는 것 처럼 간주
  • 특징
    • 기본테이블로부터 유도되어 기본테이블과 같은 형태 구조, 거의 비슷한 동작
    • 가상테이블이므로 물리적인 구현은 아니다.
    • 필요한 데이터만 뷰로 정의해 처리 기능하므로 관리용이, 명령문 간단
    • 조인문의 사용 최소화 해 사용상 편이성을 높인다.
    • 뷰를 통해서만 데이터에 접근하게 되면 나타나 지는 데이터를 안전하게 보호할 수 있다.
    • 기본테이블의 기본키를 포함한 속성의 집합으로 뷰 구성해야 삽입, 삭제, 갱신 가능
    • 정의된 뷰는 다른 뷰 정의의 기초가 될 수 있다. 따라서, 하나의 뷰 삭제시 그 뷰를 기초를 하는 뷰도 같이 삭제 된다.
  • 장점
    • 논리적 데이터 독립성 제공
    • 사용자의 데이터 관리 간단히
    • 동일한 데이터에 대하 동시 다수의 요구 지원
    • 접근제어를 통한 자동 보호
  • 단점
    • 독립적인 인덱스를 가지지 못한다.
    • 뷰의 정의를 변경할 수 없다. 만약 변경시 삭제 후 다시 설정
    • 뷰로 구성된 내용의 삽입, 삭제, 갱신의 제약이 있다.

트랜젝션

  • DB의 상태를 변환시키는 하나의 논리적 기능을 수행하기 위한 작업단위
  • DB의 내용을 접근 변경하기 위해 실행되는 모든 동작 및 동작의 모임
  • 특징(ACID)
    • 원자성(Atomicity)
      • 트랜젝션 연산은 DB에 모두 반영되거나 모두 반영이 안되어야 한다.
      • 모든 명령은 완벽히 수행되어야 하며 하나라도 에러 발생시 전부 취소
    • 일관성(Consistency)
      • 트랜젝션 성공적으로 완료하면 언제나 일관성 있는 DB 상태로 변환된다.
      • 시스템의 고정요소는 명령 수행 전, 후가 같아야 한다.
    • 독립성,격리성(Isolation)
      • 둘 이상의 트랜젝션 실행에서 하나의 트랜젝션이 실행중이면 다른 트랜젝션 사용 x
      • 수행중인 트랜젝션이 완전히 완료 될때 까지 다른 트랜젝션은 참조 x
    • 지속성(Durability)
      • 성공적으로 완료된 트랜젝션의 결과는 영구적으로 반영된다.

SQL

SQL 분류

  • DDL(데이터 정의어)
    • 스키마, 도메인, 테이블, 뷰, 인덱스 정의, 변경, 삭제 시 사용하는 언어
    • CREATE, ALTER, DROP
  • DML(데이터 조작어)
    • DB 사용자가 질의어 사용해서 실질적인 데이터를 처리하는 언어
    • SELECT, INSERT, DELETE, UPDATE
  • DCL(데이터 제어어)
    • 데이터 보안, 무결성, 데이터 회복, 병행수행 제어 등 데이터 관리목적의 언어
    • COMMIT, ROLLBACK, GRANT, REVOKE

DDL - CREATE 문

Domain

CREATE DOMAIN  도메인명 [AS] 데이터_타입
    [DEFAULT 기본값]
    [CONSTRAINT 제약조건명 CHECK (범위값)];

TABLE

CREATE TABLE 테이블명
    (속성명 데이터_타입 [DEFAULT 기본값][NOT NULL],
    [, PRIMARY KEY(기본키_속성명, ...)]
    [, UNIQUE(대체키_속성명, ...)]
    [, FOREIGN KEY(외래키_속성명, ...)
        REFERENCES 참조테이블(기본키_속성명, ...)]
        [ON DELETE 옵션]
        [ON UPDATE 옵션]
    [, CONSTRAINT 제약조건명][CHECK (조건식)]);
  • ON DELETE 옵션 : 참조체이블의 튜플이 삭제 되었을 때 기본 테이블에 취해야 할 사항 정의
    • NO ACTION, CASCADE, SET NULL, SET DEFAULT
  • ON UPDATE 옵션 : 참조 테이블의 참조 속성 값이 변경되었을 때 기본테이블에 취해야 할 사항을 지정
    • NO ACTION, CASCADE(모두 업데이트 또는 모두 삭제), SET NULL, SET DEFAULT

VIEW

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

INDEX

CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명(속성명 [ASC/DESC]...)
[CLUSTER];
  • UNIQUE : 사용되면 중복 값이 없는 속성으로 인텍스를 생성, 없으면 중복값을 허용
  • CLUSTER : 인덱스가 클러스터드 인덱스로 설정(인덱스의 키의 순서대로 재정렬)

DDL - ALTER

테이블에 대한 정의를 변경

ALTER TABLE 테이블명 ADD 속성명 데이터_타입 [DEFAULT '기본값'];
ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값'];
ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE];
  • ADD : 새로운 속성(열)을 추가할 때 사용
  • ALTER : 특정 속성의 DEFAULT 값을 변경할 때 사용
  • DROP COLUMN : 특정 속성을 삭제할 때 사용

DDL - DROP

스키마, 도메인, 기본 테이블, 뷰 테이블, 인덱스, 제약조건 등을 제거하는 명령문

DROP SCHEMA 스키마명 [CASCADE|RESTRICTED];
DROP DOMAIN 도메인명 [CASCADE|RESTRICTED];
DROP TABLE 테이블명 [CASCADE|RESTRICTED];
DROP VIEW 뷰명 [CASCADE|RESTRICTED];
DROP INDEX 인덱스명 [CASCADE|RESTRICTED];
DROP CONSTRAINT 제약조건명;
  • CASCADE : 제거할 요소를 참조하는 다른 모든 개체를 함께 제거
  • RESTRICTED : 다른 개체가 제거할 요소를 참조중일 때는 제거 취소

DML - INSERT (INTO)

기본 테이블에 새로운 튜플을 삽입할 때 사용

INSERT INTO 테이블명([속성명, 속성명, ...])
VALUE (데이터, 데이터, ...);

DML - DELETE (FROM)

기본 테이블에 있는 튜플들 중에서 특정 튜플(행)을 삭제할 떄 사용한다.

DELETE
FROM 테이블명
[WHERE 조건];

DML - UPDATE (SET)

기본 테이블에 있는 튜플들 중에서 특정 튜플의 내용을 변경할 떄 사용한다.

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

DML - SELECT (FROM)

SELECT [PREDICATE] [테이블명].속성명, [테이블명].속성명...
[, 그룹함수(속성명) [AS 별칭]]
[, WINDOW함수 OVER (PARTITION BY 속성명, 속성명, ...
              ORDER BY 속성명, 속성명, ...)]
FROM 테이블명, 테이블명...
[WHERE 조건]
[GROUP BY 속성명, 속성명, ...]
[HAVING 조건]
[ORDER BY 속성명 [ASC|DESC]]
  • SELECT 절
    • Predicate : 불러올 튜플의 수 제한할 명렁어
      • ALL : 전체 , 생략 가능
      • DISTINCT : 중복된 튜플 있을 시 그중 첫번쨰 한개만 검색
      • DISTINCTROW : 중복된 튜플 검색 하지만 선택된 속성값아닌 튜플 전체 대상
    • 속성명 : 검색하여 불러올 속성(열)및 수식 지정
      • 기본 테이블 모든 속성 지정 시 ‘*’ 사용
      • 두 개 이상의 테이블 검색 시 테이블명.속성명 으로 정의
  • FROM 절
    • 질의에 의해 검색 될 데이터 포함하는 테이블 명 지정
  • WHERE 절
    • 검색할 조건 기술
    • 비교 연산자 : = , <>, >, <, >=, <=
    • 논리 연산자 : NOT AND OR
    • LIKE 연산자 : %(모든 문자를 대표함), _(문자 하나를 대표함), #(숫자 하나를 대표함)
  • WINDOW 함수
    • GROUP BY 절을 이용하지 않고 속성의 값을 집계할 함수를 기술한다.
    • PARTITION BY : WINDOW 함수가 적용될 범위로 설정할 속성 지정
    • ORDER BY : PARTITION 안에서 정렬 기준으로 사용할 속성을 지정한다.
    • WINDOW함수
      • ROW_NUMBER() : 윈도우별로 각 레코드에 대한 일련 번호를 반환
      • RANK() : 윈도우별로 순위를 반환하며 공동순위를 반영
      • DENSE_RANK() : 윈도우 별로 순위를 반환하며 공동순위를 무시
      • 예시 
          SELECT 상여내역, 상여금
      ROW_NUMBER() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS NO
  FROM 상여금;
  • GROUP BY 절
    • 특정 속성 기준으로 그룹화하여 검색 시 그룹화 속성 지정
    • 일반적으로 아래와 같은 그룹함수와 함께 쓰임
      • COUNT(속성명) : 튜플 수
      • MAX(속성명) : 최대값
      • MIN(속성명) : 최소값
      • SUM(속성명) : 합계
      • AVG(속성명) : 평균
      • STDDEV(속성명) : 그룹별 표준편차를 구하는 함수
      • VARIANCE(속성명) : 그룹별 분산을 구하는 함수
      • ROLLUP(속성명, 속셩명, …) : 인수로 주어진 속성을 대상으로 그룹별 소계를 구하는 함수
      • CUBE(속성명, 속성명, …) : ROLLUP과 비슷하나 모든 조합의 그룹별 소개를 구함.
    • 예시 
        SELECT 부서, 상여내역, SUM(상여금) AS 상여금합계
  FROM 상여금
  GROUP BY CUBE(부서, 상여내역);
  • Having 절
    • Group by 절과 함께 사용 그룹에 대한 조건을 지정하낟.
  • Order by 절
    • 특정 속성을 기준으로 정렬하여 검색 할때 사용
    • ASC: 오름 차순(생략 가능)
    • DESC: 내림 차순

집합연산자를 이용한 통합 질의

  • 집합연산자를 사용하여 2개 이상의 테이블의 데이터를 하나로 통합한다. 
    SELECT 속성명, 속성명,...
FROM 테이블명
UNION | UNION ALL | INTERSECT | EXCEPT
SELECT 속성명, 속성명,...
FROM 테이블명
[ORDER BY 속성명 [ASC|DESC]]
  • UNION : 합집합으로 두 SELECT 문의 조회결과 모두 통합하여 출력, 중복행은 한번만
  • UNION ALL : 합집합으로 UNION과 같지만 중복 행도 그대로 출력
  • INTERSECT : 두 SELECT 문의 조회결과 중 공통된 행만 출력
  • EXCEPT : 첫번째 SELECT 문의 조회 결과에서 두 번째 SELECT 문의 조회 결과를 제외한 행을 출력한다.

DML - JOIN

INNER JOIN

  • EQUI JOIN
    • 조인 대상 테이블에서 공통 속성을 기준으로 같은 값을 가지는 행을 연결하여 결과 생성
    • EQUI JOIN 하면 중복된 속성이 나오게 되는데 하나만 출력하는 것을 NATURAL JOIN이라 한다.
    • WHERE절 사용 
        SELECT 테이블명1.속성명, 테이블명2.속성명
  FROM 테이블명1, 테이블명2
  WHERE 테이블명1.속성명 = 테이블명2.속성명;
    • NATURAL JOIN 사용 
        SELECT 테이블명1.속성명, 테이블명2.속성명
  FROM 테이블명1 NATURAL JOIN 테이블명2;
    • JOIN ~ USING 절 이용 
        SELECT 테이블명1.속성명, 테이블명2.속성명
  FROM 테이블명1 JOIN 테이블명2 USING(속성명);
  • NON-EQUI JOIN
    • NON EQUI JOIN은 JOIN 조건에 ‘=’이 아닌 나머지 비교연산자를 사용하는 JOIN
    • 표기 형식 ```sql SELECT 테이블명1.속성명, 테이블명2.속성명 FROM 테이블명1, 테이블명2 WHERE (NON-EQUI JOIN 조건)

OUTER JOIN

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

  • LEFT OUTER JOIN
    • INNER JOIN 결과를 구한 후 우측 항 릴레이션의 어떤 튜플과도 맞지 않는 좌측 항의 릴레이션에 있는 튜플들에 NULL 값을 붙여서 INNER JOIN 결과에 더함
    • 표기 형식 
        SELECT 테이블명1.속성명, 테이블명2.속성명
  FROM 테이블명1 LEFT OUTER JOIN 테이블명2
  ON 테이블명1.속성명 = 테이블명2.속성명;
  • RIGHT OUTER JOIN
    • INNER JOIN 의 결과를 구한 후, 좌측 항 릴레이션의 어떤 튜플과도 맞지 않는 우측 항의 릴레이션에 있는 튜플들에 NULL 값을 붙여서 INNER JOIN의 결과에 추가
    • 표기 형식 
       SELECT 테이블명1.속성명, 테이블명2.속성명
 FROM 테이블명1 RIGHT OUTER JOIN 테이블명2
 ON 테이블명1.속성명 = 테이블명2.속성명;

SELF JOIN

같은 테이블에서 2개의 속성을 연결하여 EQUI JOIN 하는 것

  • 표기 형식 
    SELECT 별칭1.속성명, 별칭2.속성명....
FROM 테이블명1[AS] 별칭1 JOIN 테이블명1 [AS] 별칭2
ON 별칭1.속성명 = 별칭2.속성명;

DCL - GRANT, REVOKE

데이터베이스 관리자가 데이터베이스 사용자에게 권한을 부여하거나 취소하기 위한 명령어

  • 사용자 등급 지정 및 해제 
    GRANT 사용자등급 TO 사용자_ID_리스트 [IDENTIFIED BY 암호];
REVOKE 사용자등급 FROM 사용자_ID_리스트;
  • 테이블 및 속성에 대한 권한 부여 및 취소 
    GRANT 권한_리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];
REVOKE [GRANT OPTION FOR] 권한_리스트 ON 개체 FROM 사용자 [CASCADE];
  • 권한 종류 : ALL, SELECT, INSERT, DELETE, UPDATE, ALTER
  • WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한 부여
  • GRANT OPTION FOR : 다른 사용자에게 원한을 부여할 수 있는 권한을 취소함
  • CASCADE : 권한 취소 시 부여 받았던 쇼ㅏ용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소

DCL - COMMIT

트랜잭션이 성공적으로 끝나면 데이터베이스가 일관성 상태를 가지기 위해 변경된 모든 내용을 DB에 반영해야 하는데 이때 사용하는 명령어

DCL - ROLLBACK

아직 COMMIT 되지 않은 변경된 모든 내용들을 취소하고 DB 이전상태로 복원하는 명령어

DCL - SAVEPOINT

ROLLBACK할 위치인 저장점을 지정하는 명령어

참고