[정보처리기사] 3과목 - 데이터베이스 구축

데이터 모델의 구성

개체 / 속성 / 관계

- 개체

몇 개의 속성으로 구성, 자체로 구별 가능, 다른 개체와 하나 이상의 관계 있음

- 속성

DB의 가장 작은 논리적 단위, 항목, 필드

• 차수: 속성의 수
• 기본키 속성: 개체 식별 가능
• 외래키 속성: 다른 개체와 관계에 포함
• 일반 속성: 위에 미포함이나 개체에 포함

- 관계

개체간 / 속성간 관계

일대일 / 일대다 / 다대다 관계

튜플 = 행, 레코드

릴레이션(테이블)의 수평 행, 여러 속성의 모임, 실제 데이터

• 카디널리티: 튜플의 수, 기수, 대응수

도메인

하나의 속성이 취할 수 있는 같은 타입 원자값들의 집합

키

튜플들을 서로 구분할 수 있는 기준이 되는 속성

제약조건: 키를 이용해 입력되는 데이터에 제한을 주는 

• 후보키: 유일성 O, 최소성 O, 튜플을 유일하게 식별하기 위해 사용하는 속성
• 기본키: NULL값 X, 후보키 중 특별히 선정된 주키
• 대체키: 후보키 중 기본키를 제외한 나머지 보조키
• 슈퍼키: 유일성O, 최소성X
• 외래키: 다른 릴레이션의 기본키를 참조

무결성

DB에 저장된 값과 현실 값이 일치하는 정확성

• 개체 무결성: 기본키를 구성하는 어떤 속성도 NULL값 X, 중복값 X
• 도메인 무결성: 속성 값이 정의된 도메인에 속한 값이어야 함
• 참조 무결성: 외래키 값은 NULL 또는 참조 릴레이션 기본키 값이어야 함
• 사용자 정의 무결성: 속성값이 사용자 정의 제약조건을 만족해야 함
• 무결성 강화법: 앱, 데베 트리거, 제약조건

관계대수

관계형 DB에서 정보를 검색하기 위해 어떻게 유도하는지 기술한 절차적 언어

 

- 순수관계연산자 ( 셀프조디 )

Select: 튜플 선택(수평 연산)

Project: 속성 추출(수직 연산)

Join: 두 릴레이션을 합쳐서 새로운 하나의 릴레이션을 만듦

Division: 두 릴레이션의 도메인 값이 일치하는 튜플들을 찾아냄

 

- 일반집합연산자 ( 합교차카 )

합집합 / 교집합 / 차집합

카티션 프로덕트: 차수는 더하고 카디널리티는 곱함

정규화

하나의 종속성이 하나의 릴레이션에 표현될 수 있도록 분해하는 과정
이상: 정규화를 거치지 않아 생기는 곤란한 현상
ex) 삽입 / 삭제 / 갱신 이상

 

- 정규화 종류 ( 도부이결다조 )

• 1NF: 릴레이션에 속한 모든 도메인이 원자 값만으로 되어 있음
• 2NF: 기본키가 아닌 모든 속성이 기본키에 대해 부분적 함수 종속을 제거함
• 3NF: 기본키가 아닌 모든 속성이 기본키에 대해 이행적 함수 종속 관계를 만족하지 않음
• BCNF: 강한 제3정규형, 릴레이션 R에서 모든 결정자가 후보키임
• 4NF: 릴레이션 R에 다치 종속이 성립하는 경우, R의 모든 속성이 A에 함수적 종속 관계를 만족함
• 5NF: 릴레이션 R의 모든 조인 종속이 R의 후보키를 통해서만 성립됨

시스템 카탈로그

DBMS에서 지원(스스로 생성, 유지)하는 모든 정보 유지관리하는 시스템 테이블
데이터사전, 메타데이터
일반 사용자도 SQL로 내용 검색 가능
INSERT, DELETE, UPDATE문으로 카탈로그 갱신 불가능

1. SQL 응용

SQL

• DDL (데이터 정의어): 도메인, 스키마, 테이블, 뷰, 인덱스 정의, 변경, 삭제
• DML (데이터 조작어): 인터페이스와 사용자 사이, 질의어를 통해 저장된 데이터 실질적으로 처리
• DCL (데이터 제어어): 데이터의 보안, 무결성, 회복, 병행 수행 제어 등을 정의

DDL (정의어) ( DDL: CAD T )

- DDL 대상 ( 도스테 뷰인 )

도메인 / 스키마 / 테이블 / 뷰 / 인덱스

• CREATE (생성)
• AFTER (수정)
• DROP ( DB 오브젝트 삭제)
• TRUNCATE (DB 오브젝트 내용 삭제)

DML (조작어) ( DML: SDIU )

튜플을 검색, 삽입, 변경(갱신), 삭제

• SELECT (검색)
  • DISTINCT: SELECT의 검색 조건, 중복된 값을 제거
• DELETE (삭제) 
• INSERT (삽입): INSERT INTO~ VALUES
• UPDATE (변경, 갱신)

- JOIN: 2개 테이블에 연관된 튜플들을 결합해 하나의 새로운 릴레이션으로 반환

SELECT 명령어 ( 셀프웨구해오 )	SELECT / FROM / WHERE / HAVING / ORDER BY
UPDATE 명령문 ( 업셋웨 )	UPDATE / SET / WHERE
DELETE 명령문 ( 델프웨 )	DELETE / FROM / WHERE

DCL (제어어) ( DCL: CRGR )

- DCL 기능 ( 보무병회 )

데이터 보안 / 무결성 유지 / 병행수행 제어 / 회복

• COMMIT (작업 완료, 저장 / 트랜잭션 확정)
• ROLLBACK (이전 상태로 복구 / 트랜잭션 취소)
• GRANT (권한 부여)
• REVOKE (권한 취소)

- SAVEPOINT

GRANT 명령어 ( 그온투 )	GRANT 권한 ON 테이블 TO 사용자 [WITH GRANT OPTION];
REVOKE 명령어 ( 리온프 )	REVOKE 권한 ON 테이블 FROM 사용자 [CASCADE CONSTRAINTS];

---

윈도 함수 = OLAP 함수

- 윈도 함수의 분류 ( 집순행비 )

집계함수 / 순위 함수 / 행순서 함수 / 그룹 내 비율 함수

 

- 집계 함수

COUNT, SUM. AVG, MAX, MIN, STDDEV(표준편차), VARIANCE(분산)

 

- OLAP 연산

Roll-Up / Drill-Down / Slicing / Dicing / Pivoting

2. SQL 활용

트랜잭션

논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위

- 트랜잭션 특징 ( ACID )

Atomicity / Consistency / Isolation / Durability

• 원자성: 트랜잭션 연산 전체가 정상적으로 실행되거나 취소되어야 하는 성질
  - commit / rollback, 회복성 보장
• 일관성: 시스템이 가지고 있는 고정 요소는 트랜잭션 수행 전과 후의 상태가 같아야 하는 성질
  - 무결성 제약조건, 동시성 제어
• 격리성(고립성): 동시 실행 트랜잭션 서로 영향 미치치 않아야 하는 성질
  - repeatable read serializable
• 영속성: 성공한 트랜잭션의 결과는 영속적으로 DB에 저장되어야 하는 성질
  - 회복 기법

- 병행 제어 기법 ( 낙타로다 )

낙관적 검증 / 타임 스탬프 순서 / 로킹 / 다중 버전 동시성 제어 / 2PC(준비+커밋단계)

 

- 회복 기법 종 ( 회로체그 )

로그 기반 회복 기법 / 체크 포인트 회복 기법 / 그림자 페이징 회복 기법

 

- 트랜잭션 상태 ( 활부완실철 )

활동 / 부분완료 / 완료 / 실패 / 철회 상태

부분완료 → commit → 완료

부분완료 → 실패

실패 → rollback → 철회

 

- TCL 명령어 ( 커롤체 )

커밋 / 롤백 / 체크 포인트

---

집합 연산자

- 집합 연산자 유형 ( 유유인마 )

UNION: 중복 제외 합집합

UNION ALL: 중복 포함 합집합

INTERSECT: 교집합

MINUS: 차집합

 

- 논리적 조인 유형 ( 내외교셀 )

내부 조인(INNER JOIN): 공통 존재 컬럼의 값이 같은 경우 추출

외부 조인(OUTER JOIN)

• 왼쪽 외부 조인(LEFT JOIN): 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터 추출
• 오른쪽 외부 조인(RIGHT JOIN): 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터 추출
• 완전 외부 조인(FULL JOIN): 양쪽 모든 데이터 추출

교차 조인(CROSS JOIN) = 카티션 곱(Cartesian Product): 전체 행 개수 = 두 테이블 행 수의 곱, on 구문 사용 못함

셀프 조인(SELF JOIN): 테이블 하나를 2개로 별칭

 

- 물리적 조인 ( 네소해 )

Nested-Loop(중첩 반복 조인) / Sort-Merge(정렬 합병 조인) / Hash Join

 

- 뷰: 논리적 독립, 변경 불가, DROP 후 CREATE 사용

 

- 인덱스: 기본키는 자동생성

3. 논리 데이터베이스 설계

관계 대수

- 일반 집합 연산자 ( 합교차카 )

합집합 / 교집합 / 차집합 / 카티션 프로덕트

 

- 순수 관계 연산자 ( 셀프조디 )

셀렉트 / 프로젝트 / 조인 / 디비전

• select(시그마, σ)
  table에 존재하는 행 중에서 조건을 만족하는 행의 추출하여 새로운 table을 만듦
  행을 구하기 때문에 수평연산이라고도 함
  σ[조건][table명]
  
• project(파이, π)
  table에서 속성 list로 제시된 column만 추출함
  열에 해당하는 column을 구하기 때문에 수직연산이라고도 함
  π[속성 list][table명]
• join(⋈)
  공통 column을 중심으로 두 개의 table하나로 합쳐서 새로운 table을 만듦
  join 조건이 '='일 때 동일한 column이 두 번 나타나게 되는데 이 중 중복된 column을 제거하여 한 번만 표기하는 방법을 natural join이라고 함
  [join할 table명]⋈[join 조건][join할 table명]
• division(÷)
  두 개의 table A, B에서 B에 속한 field(b)가 A에 속한 field(a)의 부분 집합일 때,
  A에서 b의 속한 값을 가진 행을 모두 제외하고 남은 행만 구함
  [table 명][[field 명]÷[field명]][table명]

- 관계 대수와 관계 해석 ( 대절해비 )

관계 대수는 절차적 언어 / 관계 해석은 비절차적 언어