※ 데이터베이스
어느 한 조직에서 업무 처리를 위해 다수의 응용 시스템 혹은 다수의 사용자들이
공용으로 사용하기 위해 통합, 저장된 운영 데이터의 집합
1. 특징
계속적인 진화
동시 공유
실시간 접근성
내용에 의한 참조
데이터 중복의 최소화
2. 구성 요소
l 개체 (Entity)
사람이 생각하는 개념이나 정보 단위와 같은 현실 세계의 대상체
서로 구별이 되는 하나하나의 대상
하나 이상의 속성 (Attribute)으로 구성
l 속성 (Attribute)
데이터베이스 구축하는 가장 작은 논리적 단위
개체 (Entity)의 특성, 상태를 기술하는 것
l 관계 (Relationship)
두 개 이상의 개체 또는 속성 간의 상호 연관성
관계의 종류
1) 1 : 1(일 대 일) 두 개체 간 구성 원소가 하나씩 대응
2) 1 : N(일 대 다) 하나의 원소와 여러 원소가 대응
3) N : M(다 대 다) 상호 여러 개의 원소들과 대응
l Total/Partial Constraint, Disjoint/Overlapping 제약조건 설명
1) 필수적참여 (Total Constraint)
모든 개체 인스턴스가 관계에 반드시 참여
서브클래스의 합집합이 수퍼클래스와 같은 경우로
수퍼클래스의 한 개체는 반드시 하나의 서브클래스에 속해야 한다.
2) 선택적참여 (Partial Constraint)
개체 인스터스 중 일부만 관계에 참여해도 됨
ABCD에서 만약 A,B 만으로 구성된 개체가 있다면 이는 Partial 한 것이고 무조건 C나 D혹은 둘 모두를 포함해야 한다면 이는 Total 한 것이다.
3) 비 중복 규칙 (Disjoint Constraint)
상위 유형의 개체 인스턴스가 어떤 하위유형의 인스턴스이기도 하다면
이 인스턴스는 동시에 이와 다른 하위유형의 인스턴스는 될 수 없음
(한 Vehicle은 car또는 truck중 하나에 속하지만 둘 다 속할 수 없음)
서브클래스들 간의 교집합이 형성되지 않는 경우다.
수퍼클래스의 개체가 서브클래스 어느 곳에도 해당하지 않을 수도 있는 제약조건을 의미한다.
4) 중복 규칙 (Overlapping Constraint)
하나의 개체 인스턴스가 동시에 2개 이상의 하위유형들의 인스턴스가 될 수 있다. (어떤 부품은 제조, 구매라는 2가지 방식 모두에 의해 확보된다.)
서브클래스들 간의 교집합이 허용되는 경우를 말한다.
만약 어떤 개체가 A, B, C, D 속성을 모두 가질 경우 이는 Overlapping
어떠한 개체도 C와 D를 함께 가지지 않는다면 이는 Disjoint 한 경우이다.
l 문항 1번의 경우에 서브클래스들(R1과 R2)만을 위한 릴레이션들로 사상하고자 한다. 이때 각 제약조건을 만족할 때 사상 가능 또는 불가능, 특징을 기술하라.
1) Total : 사상 가능.
모든 엔터티가 서브클래스에 속하므로 R1, R2 둘 중 하나 혹은
둘 모두에 사상하면 된다.
2) Partial : 사상 불가능
서브클래스에 속하지 않는 개체가 있지만
이를 담아두기 위한 릴레이션이 없으므로 사상할 수 없다.
3) Disjoint : 사상 가능
R1, R2 중에 해당하는 릴레이션에 개체를 사상하면 된다.
4) overlap : 사상 가능
교집합이 아닌 개체는 disjoint 와 같은 방식으로 사상하면 되고
Overlap 된 개체일 경우 A,B를 중복시켜 R1 과 R2 에 사상한다.
l 데이터베이스 언어
1) 정의어 (DDL)
데이터베이스 구조를 정의, 수정, 삭제 (CREATE, ALTER, DROP)
2) 조작어 (DML)
자료를 검색, 삽입, 갱신, 삭제 (SELECT, INSERT, UPDATE, DELETE)
3) 제어어 (DCL)
무결성 유지, 병행 수행 제어, 보호, 관리 (COMMIT, ROLLBACK, GRANT, REVOKE)
3. 시스템 카탈로그 (System Catalog) = 데이터 사전 (Data Dictionary) = Meta Data
테이블, 인덱스, 뷰, 제약조건, 사용자 등 개체에 대한 정보와
정보들 간의 관계를 저장한 것
사용자는 시스템 카탈로그 검색만 가능하고 삽입 삭제 갱신 불가
시스템에 의해서 만 갱신됨
4. 스키마 (Schema)
l 외부 스키마 (서브 스키마)
사용자의 관점에서 외적 사항만 표현
Ø 논리적 독립성
사용자 뷰 일관성과 데이터 모델링, 논리구조를 분리
논리적 구조가 변경되어도 응용프로그램에 영향주면 안됨
l 개념 스키마
논리적 관점에서 데이터의 논리적 구조, 규칙 표현
Ø 물리적 독립성
데이터 논리구조와 데이터 물리적 구성 분리
물리적 구조가 변경되어도 논리 구조 바뀌지 않음
l 내부 스키마
데이터베이스의 물리적 저장구조 기술 (기계 관점)
5 데이터 베이스 관리자 (DBA, DataBase Administrator)
데이터베이스 시스템과 관련된 모든 자원에 대해 기획, 통제
언어를 사용해 DBMS을 거쳐 시스템 전체 관리 운영에 책임지는 사람, 집단
l 데이터베이스 관리 시스템 (DBMS, DataBase Management System)
다수의 사용자들이 데이터베이스 내의 데이터를 접근할 수 있도록 해주는
소프트웨어 도구의 집합이다.
l 구조
l 기능
1) 정의 (Definition Facility)
데이터의 형태, 구조 등 여러 사항을 정의(생성)하는 기능
2) 조작 (Manipulation Facility)
사용자가 자료 사용할 수 있도록 요구에 따라 검색 갱신 삽입 삭제 지원
3) 제어 (Control Facility)
데이터 정확성과 안전성 유지를 위해 무결성 유지, 보안, 병행 수행 제어 등
l 장점
데이터의 중복성, 종속성 최소화
일관성 유지, 무결성 유지, 보안 유지, 표준화 구현 가능
사용자 간의 데이터 공유 가능
l 단점
많은 운영비 소요, 자료 처리 복잡
백업과 회복의 어려움
한 부분의 장애가 전체 시스템에 영향
※ 데이터베이스 설계
스키마를 정의하고 이에 따라 데이터베이스 구현하는 전반적인 과정
설계는 개논물
구조는 외개물
l 과정
1) 요구 사항 분석
사용자가 무엇에 대한 정보를 필요로 하는지 분석
데이터베이스 사용하는 용도 파악, 요구조건 명세서 작성
2) 개념적 설계
구현할 데이터베이스 정하고 구성할 구성요소 결정한 후 (E-R 모델 이용)
수행할 작업과 관계를 설저하는 과정
3) 논리적 설게
구조를 논리적으로 구현 가능한 데이터 모델로 변환하는 단계
DBMS에 논리적 스키마를 설계하는 단계, 트렌젝션의 인터페이스 설계
4) 물리적 설계
효율적 기계 처리에 맞도록 설계하는 과정
내부 저장 장치 구조, 접근 경로 등을 설계
5) 구현
실제 데이터베이스를 만드는 과정
데이터베이스 언어를 이용하여 간결 명료 하면서 분석, 계획한 내용과 일치하고 유지보수 용이하게 작성
유지보수※ 관계 데이터 모델
자료의 저장 형태를 2차원 구조의 테이블로 표현하는 방법을 말한다.
l 용어
1) 릴레이션 (Relation) = 테이블(Table)
자료 저장 형태가 2차원 테이블로 표현
2) 속성 (Attribute) = 필드 (Field) = Col
릴레이션 구성하는 열, 항목
3) 튜플 (Tuple) = 레코드 (Record) = Row
릴레이션 한 행
4) 도메인 (Domain)
하나의 속성이 가질 수 있는 값들의 범위
5) 릴레이션 스키마 (Relation Schema)
릴레이션 이름 + 속성 이름 집합 = (학생 + 학번, 이름, 학과, 학년, 생일)
6) 릴레이션 인스턴스 (Relation Instance)
릴레이션 어느 시점까지 입력된 튜플들 집합 (행 싹다)
7) 차수 (Degree)
릴레이션 구성하는 속성(Attribute)의 수
8) 카디널리티 (Cardinality)
릴레이션에 입력된 튜플의 수 = 릴레이션 인스턴스 요소 수
l 특징
릴레이션의 튜플들은 모두 상이하다. 유일하다. (중복 x), 순서 없다.
릴레이션의 속성들은 원자 값으로 구성되며 분해 불가. 순서 없다.
※ 키 (Key)
튜플 (Tuple)을 식별하기 위해 사용하는 속성 (Attribute)이나 속성의 집합
1. 후보키 (Candidate Key)
기본키가 될 수 있는 후보로, 슈퍼키 중에서 최소성을 만족하는 키
유일성과 최소성을 모두 만족하는 키
유일성만 만족하면 슈퍼키 (Super key)
후보키 중에 특별히 선택된 키는 기본키 (Primary Key) 나머지 대체키 (Alternate Key)
l 속성 - 개체 무결성, 고유 무결성
l 유일성 - 각 튜플을 유일하게 식별할 수 있는 성질
l 최소성 - 각 튜플을 유일하게 식별할 수 있는 최소 구성의 성질
2. 기본키 (Primary Key)
후보키 중에서 튜플을 식별하기 위해 특별히 선택된 키
기본키는 짧고 단순한 데이터를 저장하는 필드, 자주 변경되지 않는 필드로 선택
Like 주민번호… 학생 번호…
l 속성 - 개체 무결성, 고유 무결성
3. 대체키 (Alternate Key)
후보키 중에서 기본키를 제외한 속성
4. 외래키, 참조키 (Foreign Key)
한 테이블의 키 중 다른 테이블의 레코드를 유일하게 식별할 수 있는 키
다른 테이블 레코드를 참조하기 위해 사용
l 속성 – 참조 무결성, 도메인 무결성
l 규칙
1) 삽입 - 개체, 참조, 도메인, 키 무결성
2) 삭제 - 참조 (삭제되는 튜플이 외래키에 의해 참조되는 경우)
3) 갱신 - 갱신은 삭제 후 삽입이므로 위의 두개
5. 슈퍼키 (Super Key)
후보키와 다른 속성들의 모든 조합
유일성 만족, 최소성은 만족 안함
6. 복합키
한 개 이상의 필드를 포함하는 키
※ 무결성 (Integrity) 제약조건
데이터베이스에 저장된 값들에 대해 여러 제한을 주어
데이터에 대한 신뢰를 보장하게 하여 일관성을 유지시켜주는 것
1. 개체 무결성
개체를 식별하기 위해서 오류가 없도록 하기 위한 제약조건
식별(구분)에 문제가 없도록 모든 기본 키는 NULL값을 가질 수 없고 중복될 수 없다.
2. 참조 무결성
테이블 참조 시 오류가 없도록 하기 위한 제약조건
기본키와 참조키 간의 관계가 항상 유지됨을 보장,
테이블을 참조하는 키가 존재하는 한 삭제될 수 없고 기본키도 변경될 수 없음.
외래키는 NULL올 수 있고, 참조 릴레이션의 기본키와 같아야 한다.
(속성명은 달라도 괜춘)
참조되는 테이블에 존재하는 값만 가져와야 함 (NULL말고)
l NULL이 올 수 있는 이유는
현실에서 어떤 학원을 다닌다고 치자
학원을 신청했다고 해서 선생님이 바로 정해지지는 않음
이런 경우 선생님 ID는 NULL이 될 수 있는 것임
l 중복이 되는 이유는 왜일까…?
l 위반 되는 경우
삽입 - 중복된 튜플을 삽입하는 경우
삭제 갱신 - 삭제하려는 튜플을 참조하는 참조키가 있을 경우
3. 도메인 무결성
속성에 대한 값은 그 속성의 도메인에 속하는 값이어야 함
외래키와 참조되는 테이블의 기본키는 동일 도메인 가진다.
4. 고유 (Unique) 무결성
속성값은 모두 달라야 하는 제약조건
중복되면 안된다.
5. NULL 무결성
속성값으로 NULL이 올 수 없는 제약조건
6. Key 무결성
한 릴레이션(테이블)에 최소한 하나의 키가 존재해야 한다.
l 관계 데이터 연산
※ 관계 대수 (Relationl Algebra)
릴레이션에서 사용자가 원하는 결과를 얻기 위해 연산자를 표현하는 방법으로,
결과를 얻기 위한 절차를 표현하기 때문에, 절차적 언어라고 한다.
1. 순서 관계 연산자
SELECT, PROJECT, JOIN, DIVISION
l 셀렉트 (SELECT)
조건을 만족하는 튜플 구하는 연산
결과는 조건을 만족하는 튜플들로 테이블이 만들어진다.
연산 기호는 시그마 이용
시그마<조건>(테이블 이름)
l 프로젝트 (PROJECT)
속성의 값을 구하는 연산
연산 기호는 파이 이용
파이<조건>(테이블 이름)
보통 튜플을 뽑고 속성을 추출한다.
파이<조건>(시그마<조건>(테이블 이름))
l 조인 (JOIN)
두 테이블로부터 조건에 맞는 관련 튜플들을 하나의 튜플로 결합하여
하나의 테이블을 만드는 연산
연산 기호는 장구를 이용한다.
테이블1장구<조건>테이블2
1) 동일 조인 (Equi Join)
두 테이블의 모든 속성을 합하여 하나의 테이블 만든다. 중복 포함
관계 연산자 ‘=’ 연산자만을 사용하여 조건 표현
학생 장구<조건1=조건2>성적
2) 자연 조인 (Natural Join)
동일 조인에서 중복 제거
3) 외부 조인 (Outer Join)
조건에 맞지 않은 튜플도 모두 결과 테이블에 포함
자료가 없는 부분은 NULL로 표현됨
4) 세타 조인 (Theta Join)
세타 연산자(!=, <, <=, >, >=)를 이용해 조건 수식 표현
동일 조인처럼 모든 속성 합하여 하나의 테이블 만든다. 중복 포함
= 쓰면 동일이고 나머지 쓰면 세타로 보면 됨
5) 셀프 조인 (Self Join)
한 테이블에서 자기 자신에 조인을 시키는 것
6) 안티 조인 (Anti Join)
테이블의 값을 추출할 때 조인의 대상이 되는 테이블과 일치하지 않는
데이터를 추출하는 조인방식이다.
NOT IN, NOT EXITSTS 연산시 발생하는 조인 연산이다.
7) 디비전 (DIVISION)
A DIVISION B는 B테이블의 조건 만족하는 튜플들을 A에서 추출하는 연산
연산 기호는 %를 사용한다.
2. 일반 집합 연산자
합집합, 교집합, 차집합, 카티션 프로덕트
l 합집합
두 테이블 속성을 합집합하여 모두 표현하되, 중복된 것은 한 번만 표현
l 교집합
교집합한다. 속성값이 같은 것만 표현
l 차집합
차집합한다. X-Y = X테이블에서 Y테이블 속성 제외한 값을 표현
l 카티션 프로덕트 (Cartesian Product)
XxY
두 테이블의 속성값을 모두 대응시켜 모든 경우를 표현한다.
※ 관계 해석 (Relational Calculus)
릴레이션에서 결과를 얻기 위한 과정을 표현하는 것으로
연산자 없이 정의하는 방법을 이용하는 비절차적 언어이다.
※ SQL (Structured Query Language)
관계 데이터페이스에서 사용되는 대표적인 언어로,
관계 대수와 관계 해석을 기초로 데이터베이스 작업을 보다 효율적 다양하게 표현, 처리하기
위한 고급 데이터베이스 언어이다.
대화식이며 다른 언어로 작성된 프로그램에 삽입되어 처리할 수 있다.
※ SQL 정의어(DDL)
관계 데이터베이스에서 사용될 테이블, 스키마, 도메인, 인덱스, 뷰 등을 정의, 수정, 제거
1. CREATE
테이블, 스키마, 도메인, 인덱스, 뷰 등을 정의(생성)하기 위한 명령문
l 테이블 정의
CREATE TABLE 테이블_이름
({속성_이름 데이터_타입 [NOT NULL][DELETE 값],}
[PRIMARY KEY(속성_이름),]
[UNIQUE(속성_이름),]
[FOREIGN KEY(속성_이름) REFERENCES 참조테이블(속성_이름)]
[ON DELETE CASCADE | SET NULL | SET DEFAULT | NO ACTION]
[ON UPDATE CASCADE | SET NULL | SET DEFAULT | NO ACTION],
[CONSTRAINT 제약조건_이름][CHECK(속성_이름=범위 값)]
);
[NOT NULL] - NULL 무결성
[PRIMARY KEY(속성_이름),] – 기본키 설정
[UNIQUE(속성_이름),] – 고유 무결성
[FOREIGN KEY(속성_이름) REFERENCES 참조테이블(속성_이름)] – 외래키 설정
[ON DELETE CASCADE | - 삭제 시 어떻게 조치할 것인가?
[ON UPDATE CASCADE | - 갱신 시 어떻게 조치할 것인가?
SET NULL | - 삭제/변화 시 관련 속성을 NULL 처리
SET DEFAULT | - 삭제/변화 시 관련 속성을 기본값으로 처리
NO ACTION] – 삭제/변화 시 아무 변화 없도록
[CONSTRAINT 제약조건_이름][CHECK(속성_이름=범위 값)] – 도메인 무결성
l 스키마 정의
시스템 관리자가 일반 사용자에게 스키마에 대한 권한을 주기 위한 스키마를 만들기 위해 사용된다.
CREATE SCHEMA 스키마_이름 AUTHORIZATION 사용자;
l 도메인 정의
한 속성값의 범위(도메인)를 지정하기 위해 사용된다.
CREATE DOMAIN 도메인_이름 데이터_타입
[DEFAULT 기본값]
[CONSTRAINT 제약조건_이름 CHECK(VALUE IN(범위 값))];
l 인덱스 정의
데이터베이스 내의 자료를 효율적으로 검색하기 위해 만든다.
시스템에 의해 자동으로 관리됨
CREATE [UNIQUE] INDEX 인덱스_이름
ON 테이블_이름(속성_이름 [ASC | DESC])
[CLUSTER];
UNIQUE – 중복 허용 여부
ON 테이블_이름(속성_이름) – 지정 테이블 속성으로 인덱스 생성
[ASC | DESC] – 오름차순(기본) | 내림차순
[CLUSTER] – 인접한 튜플들을 물리적인 그룹으로 묶어 저장 시 사용
2. ALTER
기존에 만들어진 테이블에 새로운 속성을 추가, 변경, 삭제할 대 사용
ALTER TABLE 테이블_이름 ADD 속성_이름 데이터_타입 [DEFAULT];
ALTER TABLE 테이블_이름 ALTER 속성_이름 데이터_타입 [SET DEFAULT];
ALTER TABLE 테이블_이름 DROP 속성_이름 데이터_타입 [CASCADE | DEFAULT];
3. DROP
기존에 사용되던 테이블, 스키마, 인덱스, 뷰, 제약조건 등을 제거
DROP TABLE 테이블_이름 [CASCADE | RESTRICT];
DROP SCHEMA 이름 [CASCADE | RESTRICT];
DROP DOMAIN 이름 [CASCADE | RESTRICT];
DROP VIEW 이름 [CASCADE | RESTRICT];
DROP INDEX 이름;
DROP INDEX 이름;
DROP CONSTRAINT 이름;
RESTRICT – 삭제할 요소가 참조 중이면 삭제 안됨
CASCADE – 삭제할 요소가 참조 중이더라도 삭제됨, 참조 중인 다른 테이블도
연쇄적으로 같이 삭제된다.
※ SQL 조작어(DML)
실제 사용자가 이용(조작)하기 위한 언어이며,
데이터 검색 삽입 수정 삭제를 위해 사용된다.
1. SELECT (검색문)
SELECT [DISTINCT] 속성_이름
FROM 테이블_이름
[WHERE 조건]
[GROUP BY 속성_이름 [HAVING 그룹조건]]
[ORDER BY 속성_이름 [ASC | DESC]];
SELECT 절 – 관계 대수의 프로젝트(파이)와 같다.
DISTICT – 중복은 한 번만 표현, 생략하면 중복 포함
WHERE 절 – 관계 연산자, 논리 연산자 사용
GROUP BY 절 – 한 속성을 그룹으로 분류
HAVING – 그룹에 조건을 제시
ORDER BY 절 – 정렬, 오름차순(기본) | 내림차순
l BETWEEN A AND B
WHERE 절 안에서 조건이 연속적인 값을 나타낼 때 사용한다.
WHERE 점수 BETWEEN 85 AND 90; 85에서 90까지의 값
l 집계함수
SUM(속성_이름), AVG, MAX, MIN, COUNT
COUNT(*)은 어떤 속성으로 세어도 무방할 때 사용
l AS
SELECT 명령문으로 검색된 결과를 원하는 속성 이름으로 표현
SELECT SUM(점수) AS 1학년집계
l IN
OR과 동일한 연산 결과를 나타낸다.
WHERE 학년 IN(3,4)
WHERE 학년=3 OR 학년=4;
l EXISITS
부속 질의문의 검색 결과가 존재하는지 여부를 확인해서 존재하는 경우 수행
l 부속(하위) 질의문
여러 테이블 이용하는 경우 질의문 안에 질의문 가지는 형태
연결은 =, IN, EXISTS 등으로 연결됨
l 부분 매치 질의문
조건문 작성 시 자료의 일부를 가지고 비교하여 검색하는 질의문
%는 여러 문자를 대신하고
_는 한 자리를 대신한다.
WHRER 연락처 LIKE ‘%7588’;
l NULL값과 비교하는 질의문
NULL과 비교하는 경우 WHRER절에 = 대신 IS를 사용 <>대신 IS NOT 사용
WHERE 연락처 IS NULL;
WHERE 연락처 IS NOT NULL;
l JOIN 질의문
두 테이블을 JOIN 연산한 뒤 자료를 검색하는 형태의 질의문
SELECT 이름, 학과, 학생수
FROM 학생정보 JOIN 학과인원 ON (학생정보.학과=학과인원.학과);
JOIN ~ USING
같은 속성을 사용하는 경우 이렇게 가능하다. 학과가 같은 튜플만 조인
SELECT 이름, 학과, 학생수
FROM 학생정보 JOIN 학과인원 USING(학과);
2. INSERT (삽입문)
기존 테이블에 새로운 자료를 삽입하는 경우 사용하는 명령문
INSERT INTO 테이블_이름[(속성 이름…)]
VALUES (자료…);
삽입 시 테이블이 모든 속성을 가지고 있으면 속성 이름 생략해도 됨, 아니면 기입
3. UPDATE (갱신문)
테이블의 자료 중에서 값을 변경하고자 하는 경우 사용되는 명령문
UPDATE 테이블_이름
SET 속성_이름=변경 내용
[WHERE 조건];
4. DELETE (삭제문)
테이블의 자료를 삭제할 때 사용하는 명령문
WHERE 절의 조건에 맞는 튜플만 삭제되며, WHERE 절이 생략되면 모든 튜플 삭제
※ SQL 제어어(DCL)
관리자가 데이터 보안, 무결성 유지, 병행제어, 회복 등을 위해 사용하는 언어
1. COMMIT
데이터베이스 내의 연산이 성공적으로 종료되어 연산에 의한 수정을 유지하기 위함
2. ROLLBACK
연산이 비정상적으로 종료되거나 정상 수행 되었다 하더라도
수행하기 이전 상태로 되돌리기 위해 연산 내용을 취소할 때 사용
3. GRANT
관리자가 사용자에게 데이터베이스에 대한 권한을 부여할 때 사용
GRANT 권한 내용 ON 테이블_이름 TO 사용자 [WITH GRANT OPTION]
[WITH GRANT OPTION]는 자신이 가지고 있는 권한을 다른 사용자에게 부여 가능
EX) GRANT UPDATE ON 학생 TO ABC WITH GRANT OPTION;
사용자 ABC에게 학생 테이블에 대한 UPDATE 권한 부여
4. REVOKE
관리자가 사용자에게 부여했던 권한을 취소하기 위해 사용되는 명령어
REVOKE 권한 내용 ON 테이블_이름 FROM 사용자 [CASCADE];
[CASCADE]는 사용자가 다른 사용자에게 부여했던 권한도 모두 연쇄적으로 취소
EX) REVOKE UPDATE ON 학생 FROM ABC CASCADE;
사용자 ABC에게 부여했던 학생 테이블에 대한 UPDATE 권한 취소
※ SQL 문제
research 부서에서 하는 프로젝트 10시간 이상 한 모든 사원 이름
1. 관계대수로 표현
2. SQL문장으로 표현
SELECT 이름
FROM 사원, 프로젝트
WHERE 사원.부서=research AND 프로젝트.시간 >= 10;
대충 이렇지 않을까?
research부서에서 일하고 sex=m을 부양하는 사원의 이름
EMP랑 DEP의 Ssn이 연결, 부양 가족이 없는 사원 이름
(상관 중첩 질의로)
Ans) SELECT name
FROM EMPLOYEE
WHERE NOT EXISTS ( SELECT *
FROM DEPENDENT
WHERE ESSN = DSSN );
※ 뷰 (VIEW)
하나 이상의 테이블로부터 유도되어 만들어진 가상 테이블
처리 과정 중의 중간 내용이나 테이블 일부 내용을 검색해 보여주거나 별도 관리하기 위해
사용되는 임시 테이블
실제 물리적으로 기억공간을 차지하지 않으며 논리적 독립성 제공하고,
데이터 접근제어로 보안성 향상
뷰가 정의된 기본 테이블 제거되면 뷰도 자동으로 제거됨
뷰에 대한 검색은 일반 테이블과 거의 동일
뷰에 대한 삽입 삭제 갱신은 제약이 따른다.
뷰는 ALTER 문으로 변경 불가능하다. 변경하고 싶으면 삭제 후 다시 생성해야 한다.
1. 뷰 생성
CREATE VIEW 뷰_이름[(뷰_속성이름)]
AS SELECT 기본테이블의 속성_이름
FROM 기본테이블_이름
[WHERE 조건]
[WITH CHECK OPTION];
AS SELECT 기본테이블의 속성_이름
속성 이름은 다르게 부여 가능하다.
[WITH CHECK OPTION]
뷰에 대한 갱신 삽입 수정 시 WHERE 조건에 맞지 않으면 실행 되지 않도록 할 때 사용
2. 뷰 삭제
DROP VIEW 뷰_이름 [RESTRICT | CASCADE];
3. 뷰 구현방법
l 질의 수정 방식
뷰에 대한 질의를 기본 테이블에 대한 질의로 변환하여 처리
l 뷰 실체화 방식
처음 질의에서 뷰에 대한 요구가 있을 때 임시 뷰 테이블을 물리적으로 생성하고, 뷰에 다른 질의들이 이어질 것이라는 가정 하에 테이블 생성하여 유지하는 방식
※ 이상 (Anomaly)과 함수적 종속
※ 이상 (Anomaly)
이상 현상은 데이터베이스의 논리적 설계 시 하나의 릴레이션에 많은 속성들이 존재하여,
데이터의 중복과 종속으로 인해 발생되는 문제점을 말한다.
1. 삭제 이상 (Deletion Anomaly)
테이블에서 하나의 자료를 삭제하고자 하는 경우
그 자료가 포함된 튜플이 삭제됨으로 인해
원하지 않은 자료까지 함께 삭제가 이루어져 발생하는 문제를 말한다.
2. 삽입 이상 (Insertion Anomaly)
삽입하는 과정에서 원하지 않는 자료가 삽입된다든지
삽입하는데 자료가 부족해 삽입이 되지 않아 발생하는 문제점을 말한다.
3. 갱신 이상 (Update Anomaly)
자료를 갱신하는 과정에서 정확하지 않거나 일부의 튜플만 갱신됨으로 인해
정보가 모호해지거나 일관성이 없어져 정확한 정보 파악이 안되는 현상을 말한다.
※ 함수적 종속 (Functional Dependency)
종속이란 어떤 릴레이션에 속성 A, B가 있을 때,
임의 튜플에서 A의 값이 B의 값을 함수적으로 결정한다면,
즉 A값 알면 B값을 알 수 있거나 A값에 따라 B값 달라진다면
B는 A에 함수적으로 종속되어 있다고 한다.
‘A->B’ 라고 표기, A는 결정자, B는 종속자
1. 완전 함수 종속 (Full Functional Dependency)
한 속성이 오직 기본키에 종속되는 경우
2. 부분 함수 종속 (Partial Functional Dependency)
한 속성이 기본키가 2개 이상 복합키로 구성된 경우, 이 중 일부에 종속되는 경우
3. 이행적 함수 종속 (Transitive Functional Dependeny)
A를 알면 B를 알고, B를 알면 C를 알 수 있을 때
A를 알면 C를 알 수 있는 경우
A->B, B->C, A->C가 성립※ 정규화 (Normalization) / 역정규화
※ 정규화 (Normalization)
논리적 설계 단계에서 발생할 수 있는 종속으로 인한 이상현상의 문제점을 해결하기 위해
속성들 간의 종속 관계를 분석하여 여러 개의 릴레이션으로 분해하는 과정
정규화된 결과를 정규형이라고 한다.
도부이결다조~
1. 제1정규형 (1NF : First Normal Form)
한 릴레이션 구성하는 모든 도메인이 원자 값(각자 튜플로 구성되도록)
만으로 구성되도록 하는 정규형
2. 제2정규형 (2NF)
제1정규형을 만족하면서 릴레이션을 구성하는 모든 속성이 기본키에
완전 함수 종속이 되도록 분해하는 과정
모든 부분 함수 종속을 제거하는 과정
3. 제3정규형 (3NF)
제2정규형을 만족하면서 릴레이션을 구성하는 속성들 간에
이행적 함수 종속 관계를 분해하는 과정
A->B->C 인 테이블을
A->B, B->C로 분해
4. 보이스-코드 정규형 (BCNF : Boyce-Codd Normal Form)
제3정규형을 만족하면서 릴레이션에서 모든 결정자가 후보키가 되도록 하는 과정
A->BC 이런 테이블에서
A->B, A->C로 분해
5. 제4정규형 (4NF)
다치 종속(MVD : Multivalued Dependency) 관계가 성립하는 경우를 분해하는 과정
하나의 속성 값이 여러 개의 속상 값을 결정하는 것을 의미한다.
A ->>B
과목명 강사 교재
POP글씨 최수지 POP-1
POP글씨 최수지 POP-2
과목명->>강사, 과목명->>교재이런 집합을 결정한다는 뜻임
1정규화에 의해 저렇게 집합이 아니라 원자 값을 가져야 하니까 발생하는 현상
릴레이션 속성이 3개 이상일 때 존재
6. 제5정규형 (5NF)
릴레이션에 존재하는 조인 종속(Join Dependency)이 후보키를 통해서만
성립이 되도록 하는 정규형이다.
조인 종속이란 원래 릴레이션을 분해한 뒤 자연 조인한 결과가 원래 릴레이션과
같은 결과가 나오는 종속성을 말한다.
그러니까 중복되는 것이 하나도 없는 경우를 뜻함
어떤 릴레이션 스키마 R = {A, B, C, D}에서 함수적 종속성들의 집합은 F = {AB→C, C→D}
1) R이 다음 정규형에 속하면 O표, 속하지 않으면 X표 하라. 그 이유도 각각 간단히 설명
2NF ( O ) : C와 D는 기본키 AB에 대해서 완전 함수 종속이다.
3NF ( X ) : D는 기본키AB에 대해서 이행적 함수 종속이다.
BCNF ( X ) : 3NF를 만족하지 않으므로 BCNF도 만족하지 않는다.
또한 C->D 함수적 종속성에서 C는 슈퍼키가 아니다.
2) 이 R을 R1 = {A, B, C}와 R2 = {C, D}로 분해하였다.
① 이 분해가 무손실 조인(lossless join) 특성을 만족하는가? 이유를 설명하라.
만족한다. R1과 R2를 자연조인 했을 때 가짜 투플이 생성되지 않는다.
② 이 분해가 종속성 보존(dependency preservation) 특성을 만족하는가? 이유를 설명하라
만족한다. 분해하기 전의 함수적 종속성 AB→C, C→D 을 추론 해 낼 수 있다. 또는 분해된 릴레이션 스키마상에 존재한다.
무손실 조인
분해집합 D가 가져야 하는 또 다른 특성은 분해집합의 릴레이션들을 자연 조인했을 때 어떠한 가짜 투플도 생성되지 않아야 한다는 것이다.
종속성 보존
함수적 종속성들의 집합 F의 각 함수적 종속성 X->Y가 분해집합 D의 한 릴레이션 스키마 Ri에 직접 나타나거나 Ri에 나타나는 함수적 종속성들로부터 추론할 수 있는 것이 바람직하다.
a->b, b->c, c->a
(1) a,b,c 는 후보키가 가능한가? 이유는?
후보키는 최소 슈퍼키여야 하는데 b 와 c를 빼도 a 그자체로 슈퍼키의 특성을 만족하므로
a,b,c, 는 후보키가 아니다.
(2) 무손실성 조인인가?
문제가 뭔지는 모르겠지만 [abc] 를 [ab] [bc] 로 나누었다면 무손실성 조인임.
(3) 종속성 만족하는가?
문제가 뭔지는 모르겠지만 [abc] 를 [ab] [bc] 로 나누었다면 종속성 보존은 되지
않았음. c->a 의 종속성이 소실됨.
ab->c, cd->e, de->b
(1) 후보키가 아닌 것은?
ab, abd, abcd, acd, ade
ab : d, e 식별 불가
abcd : 최소 슈퍼키 ㄴㄴ
(2) BCNF 인가?
ab, cd, de 가 슈퍼키가 아님 따라서 BCNF 가 아님.
(3) 3NF 인가?
3NF 임. c, e, b 가 주요 애트리뷰트임.
※ 역정규화 (De-Normalization)
역정규화는 정규화된 릴레이션을 물리적 데이터 모델링 과정에서 성능을 향상시키기 위해
중복을 허용하고라도 다시 통합하거나 분할하여 구조를 재조정하는 것을 말한다.
정규화된 릴레이션을 너무 많이 분해하여
외래키를 이용해 자주 참조하다보니 오히려 성능 저하가 생기는 것을 해결하는 것임
개논물에서 논 – 정규화, 물 – 역정규화
l 릴레이션 역정규화
1) 릴레이션 병합
두 릴레이션을 합하는 방법
2) 릴레이션 분할
자주 사용되는 속성이나 튜플, 자주 사용되지 않는 속성이나 튜플을 분해
Ø 수직 분할 – 속성 분할
Ø 수평 분할 – 튜플 분할
l 속성 역정규화
1) 속성 추가
릴레이션 A, B에서 A의 외래키에 의해 B의 특정 속성값을 자주 참조하는 경우
B의 속성을 A에 추가
2) 파생속성 추가
현재 릴레이션에 없는 속성이지만 작업의 효율을 위해
한 속성으로부터 계산이나 가공에 의해 파생되는 속성
※ 트랜잭션(Transaction) / 회복 / 병행 제어
데이터베이스 내에서 한꺼번에 모두 수행되어야 할 연산들의 집합
하나의 작업 처리를 위한 논리적 작업 단위
트랜잭션 내의 연산은 한꺼번에 완료 (Commit)되어야 하고,
그렇지 못하면 모두 취소(Rollback)되어야 함
1. 성질 (ACID)
l 원자성 (Atomicity)
트랜잭션 내의 연산은 모두 수행되어야 하며, 그렇지 못하면 모두 취소되어야 함
l 일관성 (Consistency)
트랜잭션이 완료된 이후 결과에 모순이 생기면 안된다.
l 격리성, 독립성 (Isolation)
하나의 트랜잭션이 수행 중에는 다른 트랜잭션이 접근할 수 없고, 각각 독립적이여야 한다.
l 영속성, 지속성 (Durability)
트랜잭션이 완료된 후 결과는 지속적으로 유지되어야 한다.
2. 연산
l COMMIT
트랜잭션이 성공적으로 종료된 후 변경된 내용을 지속적으로 유지하기 위한 연산
l ROLLBACK
트랜잭션이 비정상적으로 수행되었거나 오류 발생 시
수행 작업을 모두 취소하고 이전 상태로 되돌아가기 위한 연산
3. 상태도
부분 완료 -> 완료 = COMMIT
실패 -> 철회 = ROLLBACK
4. 스케줄
동시에 여러 트랜잭션들이 병행 실행되는 경우 트랜잭션 연산들의 실행 순서
l 직렬 스케줄 (Serial Schedule)
각 트랜잭션별로 구분하여 한 트랜잭션을 구성하는 연산들을 연속적으로
모두 수행한 후에 다른 트랜잭션 수행하는 방식
l 비직렬 스케줄 (Nonserial Schedule)
트랜잭션들이 병행하여 수행하는 방법으로
인터리브 실행 기법을 이용하여 각 트랜잭션 연산을 번갈아가며 실행
l 직렬 가능 스케줄 (Serializable Schedule)
직렬 스케줄 기법을 이용한 결과 = 비직렬 스케줄 기법을 이용한 결과 인 경우
※ 회복
여러 가지 요인으로 인해 손상된 데이터베이스를 손상 이전인 정상상태로 복구시키는 작업
회복을 위해 로그 (Log)를 이용하는데
로그는 트랜잭션이 수행되어 변경되는 상황 정보를 기록하는 것이다.
1. 회복 기법
l 즉시 갱신 기법
트랜잭션이 실행 상태에서 변경되는 내용을 즉시 데이터베이스에 적용
내용을 로그에 기록해서 로그를 토대로 회복
l 지연 갱신 기법
부분 완료가 될 때까지 변경 내용을 데이터베이스에 적용시키지 않고 대기
부분 완료되면 로그의 내용을 토대로 데이터베이스에 적용
l 검사 시점 기법
실행 중간에 검사 시점 (Check Point)를 지정하여 검사 시점까지 수행 후
완료된 내용을 데이터베이스에 적용
l 그림자 페이징 (Shadow Paging)기법
로그를 사용하지 않고 데이터베이스를 동일한 크기 단위인 페이지로 나누어
각 페이지마다 복사하여 그림자 페이지를 보관하는 기법
변경 내용은 원본 페이지에만 적용하고 장애 발생 시 그림자 페이지로 회복
2. 재수행과 취소
l REDO
COMMIT이 되면 변경 내용을 데이터베이스에 반영하는데, 이때 로그를 토대로
재수행하며 변경된 내용을 데이터베이스에 반영하는 과정
l UNDO
트랜잭션 수행 도중 오류 발생이나 비정상 종료로 인해 시작 시점으로
되돌아가는 과정
l 병행 제어 (Concurrency Control)
동시에 여러 트랜잭션이 실행되는 경우를 병행 실행이라고 하는데
병행 실행 시 트랜잭션 간의 격리성을 유지하여 트랜잭션 수행에 문제 생기지 않도록 제어
1. 로킹 (Locking)
트랜잭션의 병행 실행 시 하나의 트랜잭션이 사용하는 데이터베이스 내의 데이터를
다른 트랜젝션이 접근 못하도록 하는 것
l 과정
LOCK -> 트랜잭션 실행 -> 트랜잭션 완료 -> UNLOCK
l 접근 허용
한 트랜잭션의 수행시간이 너무 길어 너무 오래 LOCK이 걸려있으면
교착상태가 발생할 수 있다.
따라서 한 트랜잭션 수행하는 동안에도 다른 트랜잭션이 데이터에 접근할 수 있도록 공유 락과 배타 락이 있다.
1) 공유 락 (Shared Lock) = READ ONLY
2) 배타 락 (Exclusive Lock) =READ WRITE 둘 다 안됨
l 로킹 단위
LOCK을 설정할 데이터의 크기를 나타낸다.
로킹 단위는 테이블, 속성, 튜플 단위로 설정할 수 있다.
단위가 크면 많은 양의 데이터 LOCK가능, 가지 수는 감소, 병행성 수준 낮아짐
단위가 작으면 적은 양의 데이터 LOCK가능, 가지 수 증가, 병행성 수준 높아짐
2. 2단계 로킹 기법 (Two-Phase Locking Protocol)
LOCK 설정 대상 데이터가 여러 개인 경우
모든 데이터에 LOCK을 설정하는 단계와 완료 후 LOCK을 해제하는 단계의
2단계로 구성한 기법이다.
l 확장 단계
트랜잭션 수행에 필요한 데이터들을 LOCK 설정
l 축소 단계
트랜잭션 수행에 사용되었던 데이터들에 대해 필요 없는 것들 LOCK 해제
l 단점
교착 상태 발생 가능
연쇄 복귀 발생 가능
l 개선
1) 엄격한 2단계 로킹 기법 (Strict 2PL Protocol)
Ø 규칙
어떤 트랜잭션이 어떤 객체를 읽거나 쓸 때, 먼저 그 객체에 공유/배타 락을 요구한다.
그 트랜잭션이 종료할 때까지 소유한 모든 배타 락을 유지한다.
Ø 특징
배타 락을 유지하기 때문에 연쇄 복귀 (Cascading Rollback)이 방지됨
여전히 교착상태는 피할 수 없음
2) 엄중한 2단계 로킹 기법 (Rigorous 2PL Protocol)
Ø 규칙
트랜잭션들이 종료될 때까지 모든 공유/배타 락을 유지한다.
Ø 특징
트랜잭션들이 COMMIT한 순서대로 직렬 가능
3. 타임 스탬프 (Time Stamp)
각 트랜잭션이 데이터에 접근할 시간을 미리 지정하여 기억시킨 뒤
그 시간(타임 스탬프)의 순서에 따라 순서대로 데이터에 접근하여 수행하는 방법
모든 트랜잭션은 데이터 접근 시간에 맞춰 수행하므로 교착상태 발생하지 않음
4. 병행 제어를 하지 않았을 때 문제점
l 갱신 분실 (Lost Update)
두 개 이상의 트랜잭션 수행 과정에서 연산 결과 일부 분실되는 현상
l 모순성 (Inconsistency)
두 개 이상의 트랜잭션이 수행되어 얻어진 결과가 일관성 없이 다른 현상
l 연쇄 복귀 (Cascading Rollback)
하나의 트랜잭션 취소로 인해 연쇄적으로 다른 트랜잭션도 취소되는 현상
l 비완료 의존성 (Uncommittes Dependency)
하나의 트랜잭션 수행이 실패한 후 회복되기 이전에
다른 트랜잭션이 실패한 갱신 결과를 참조하는 현상
5. 동시성 제어 문제 해결을 위한 고립수준 (Isolation Level)
트랜젝션에서 일관성이 없는 데이터를 허용하도록 하는 수준
l 필요성
트랜잭션이 DB를 다루는 동안 다른 트랜잭션이 관여하지 못하도록 하기 위해
Lock을 걸어야 하는데 Lock의 범위를 적절히 조절해주어야 한다.
Lock의 범위를 너무 넓히면 잘못된 값이 처리될 가능성이 있고
범위를 너무 좁히면 병행성이 떨어져서 성능의 이득을 볼 수 없다.
l 종류
1) Read Uncommitted (레벨 0)
Dirty Read를 허용하는 고립수준이다. 일관성을 유지할 수 없다.
다른 트랜잭션이 COMMIT여부 관계없이 현재 데이터를 그대로 읽는다.
2) 완결 판독 (Read Committed) (레벨 1)
Unrepeatable Read를 허용하는 고립수준이다.
COMMIT되어 확정된 데이터만 읽는 것을 허용한다.
COMMIT되지 않은 값에 대해서는 이전 버전의 값을 읽음
3) 반복가능 판독 (Repeatable Read) (레벨 2)
Phantom Problem 까지만 허용하는 고립수준이다.
데이터를 읽어가며 Lock을 획득한다.
누군가 읽으면 Lock이 걸려서 다른 사람은 갱신/삭제가 안되는 것
COMMIT되기 이전에는 다른 트랜잭션에 의한 갱신/삭제 연산을 금지한다.
트랜잭션이 읽어가는 데이터에 대해 잠금 수행하고
그 Tx이 COMMIT 되기 전까지 다른 트랜잭션의 변경연산 금지 ﾠ
4) 직렬가능 (Serializable) (레벨 3)
Phantom Problem조차 허용하지 않는 고립수준이다.
트랜잭션 간의 직렬성 및 독립성을 보장하는 고립수준이다.
유령문제(Phantom Problem)를 방지하며 Tx의 독립성을 보장
l 트랜잭션 데이터 공유 문제
| 트랜젝션 A | 트랜잭션 B | 문제 | 동시 접근 |
| Read | Read | 없다. | 허용 |
| Read | Write |
Dirty Read, Non-Repeatable, Phantom Read |
허용/불가 |
| Write | Write | Lost Update | 불가 |
l 유령 문제(Phantom Problem)
트랜잭션이 SQL WHERE 절로 테이블에서 행의 집합을 읽었을 때
다른 트랜잭션이 WHERE 절을 만족하는 조건의 데이터를 테이블에 삽입하여
이전에 존재하지 않았던 레코드가 갑자기 나타나는 경우를 말한다.
트랜잭션 과정에서 동일한 쿼리가 두 번 실행되고
두 번째 쿼리의 결과가 첫 번째 쿼리와 다른 경우를 말한다.
트랜잭션이 다시 반복되었을 때 이전에는 없던 행을 참조하게 됨
이는 트랜잭션 도중에 새 레코드가 삽입되는 것을 허용하기 때문에 나타난다.
l Dirty Read
변경 후 아직 COMMIT되지 않은 값을 읽고, ROLLBACK 후의 값을 다시 읽어서 최종 결과가 상이한 현상
완료되지 않은 트랜잭션에 의해 수정된 데이터를 읽음
만약 트랜잭션이 철회되면 존재하지 않는 데이터를 읽은 것이 됨.
l 반복 불가능 읽기 (Unrepeatable Read / Non-Repeatable Read)
트랜잭션이 진행되는 동안 (같은 행을) 두 번의 검색이 하는데 두 번째 검색 결과가 처음과 다른 것
한 트랜잭션이 특정 값을 여러 번 조회하는 중에
다른 트랜잭션이 값을 변경하여 그 값이 다르게 조회되는 경우
l Phantom Problem vs Unrepeatable Read
반복 불가능 읽기는 단일 레코드에 적용
다른 트랜잭션의 UPDATE 문의 결과로 인한 것
유령 문제는 주어진 쿼리 필터링을 만족하는 범위 레코드에 적용
그래서 2 단계 잠금 프로토콜 같은 것을 써서 범위 락을 걸어야함
다른 트랜잭션의 INSERT, DELETE문의 결과로 인한 것
※ 인덱스 (INDEX)
수 많은 데이터 중에서 원하는 자료를 빠르고 효율적으로 검색하기 위해 사용하는 방법/자료구조
l 왜 사용하는가
기본적으로 데이터 검색할 때 전체 데이터를 순회하는 Full Scan을 하게 되는데 테이블이 크면 오래걸린다.
그래서 더 빠르게 검색하기 위해 사용
조건과 일치하는 열을 빨리 찾기 위해
조인시 다른 테이블의 열을 추출하기 위해
최소, 최대 값을 찾기 위해
l 구성
테이블 내의 1개 이상의 컬럼을 이용해 생성 할 수 있다.
| 인덱스 파일 (Index File) | 데이터의 위치(주소)를 관리, 기억 |
|
데이터 파일 (Data File) |
실제 데이터를 기억 |
l 특징
|
특징 |
이유 |
| 데이터에 빠른 접근을 할 수 있다. | 키 값으로 행 데이터의 위치를 찾는다. |
| 변경에 취약하다 |
원본 테이블에서 인덱스로 설정된 필드 값을 포함한 데이터 Update, Insert, Delete 하면 인덱스도 수정해야 되기 때문 |
|
물리적 구조와 밀접한 관계가 있다. |
레코드가 저장된 물리적 구조에 접근하는 방법을 제공 |
|
데이터베이스에 의존적이다. |
인덱스는 사용자가 직접 접근할 수 없고 검색, 질의 처리에만 사용된다. |
테이블의 기본 키는 자동으로 인덱스 된다.
데이터 형식 때문에 인덱스 될 수 없는 필드도 있다.
l 효율적으로 하는 방법
| 방법 | 이유 | |
| 인덱스 선택 |
고유 값을 갖는 필드만 Update 빈번하지 않은 것으로 |
레코드의 Update, Insert, Delete 일어나는 경우, 인덱스도 변경(재정렬) 해야 하므로 성능 저하됨 |
|
인덱스 개수 |
개수 최소로 단일 인덱스 여러개 보다 다중 컬럼 인덱스 고려 |
|
| 인덱스 키 (컬럼) |
키의 크기는 되도록 작게 기본 키, JOIN시 자주 사용되는 것으로 사용 |
|
| 자료형 | 정수형이 가장 효율적 | 가변적인 크기와 정규화 할 수 없는 데이터는 비효율적임 |
| 정규화 | 정규화가 되어 있어야 한다. |
정규화 되지 않은 테이블은 컬럼이 많고 조합할 수 있는 인덱스가 많아진다. |
l 인덱스 사용해도 효과 없는 경우
필드 값이 너무 서로 다를 경우
필드 값이 너무 비슷한 경우
인덱스 Scan이 순차 Scan 보다 느릴 경우 - 미리 테스트를 해보면 된다.
l 장점
테이블에서 검색과 정렬 속도 향상
테이블 행의 고유성 강화
l 단점
파일 크기가 늘어난다.
다중 사용자 환경에서 여러 사용자가 한 페이지를 동시에 수정할 수 있는 병행성 감소
인덱스 된 필드의 데이터 Update, Insert, Delete 할 때 성능 저하
l 인덱스 저장 방식
| 클러스터드 인덱스 | 넌 클러스터드 인덱스 | |
| 설명 |
하나의 속성을 기준으로 정렬시킨 후 테이블을 재구성하여 인덱스를 만드는 방법 가능하면 기본 키를 기준으로 한다. |
데이터 주소를 이용하여 인덱스를 만들어 주소 값을 이용하여 검색하는 방법 |
| 재배열 | O | X |
| 정렬 | 디스크에 정렬된 상태로 저장 |
디스크에 정렬되지 않은 상태로 저장 인덱스 페이지는 정렬한다. |
| 용량 |
정렬되어 있기 때문에 리프 레벨의 인덱스 테이블 필요 없어서 (테이블 자체가 인덱스라서) 넌 클러스터드 인덱스 보다 작다. |
원본을 정렬하지 않기 때문에 따로 인덱스 페이지를 만들기 때문에 크다 |
| 정확도 |
범위 조회에 유리 30% 이내 |
작은 범위에 유리 3% 이내 |
| 생성 개수 | 테이블 당 1개 | 테이블 당 249개 |
| 변경이 있을 때 | 인덱스로 지정된 컬럼을 기준으로 정렬되어 삽입되므로 느리다. | 재정렬 필요 없음 |
| 인덱스 페이지 |
키 값, 데이터 페이지 번호로 구성 |
키 값, ROW ID 로 구성 ROW ID = (페이지 그룹 번호, 데이터 페이지 번호, 데이터 페이지 오프셋) |
| 데이터 찾는 방법 | 페이지 번호의 인덱스 |
페이지 그룹 번호의 데이터 페이지번호의 데이터 페이지 오프셋이 위치 |
l 인덱스 종류 (알고리즘)
l 다중 컬럼 인덱스 (Multiple-Column Index)
두 개 이상의 컬럼(필드) 조합하여 생성한 인덱스
최대 10개의 컬럼 포함 가능
첫 필드 값이 같은 레코드를 구분할 수 있다.
단일 컬럼 인덱스 보다 더 비효율적으로 Update/Insert/Delete 하기 때문에 신중히 사용
name, address 두 개를 사용한다고 쳤을 때
그래서 뒤의 값인 address로만 검색하면 효과를 볼 수 없음, name을 먼저 해야 된다.
l B-트리 (Balanced Tree)
자료의 구조를 균형 있는 트리 구조로 나타내는 방법
한 노드에 M개의 자료가 배치되면 M차 B-Tree라고 한다.
1) 탄생 과정
이진 트리는 두개의 자식밖에 가지지 못하고 균형이 맞지 않으면
검색 효율이 떨어진다. 이를 개선하기 위해 나온 트리
자식을 더 많이 가질 수 있게 일반화하고, 트리의 균형을 자동으로 맞춘다.
2) 조건
루트 노드가 리프 노드가 아닌 경우 항상 최소 2개의 자식을 가진다.
루트와 리프 노드를 제외한 모든 노드
[Degree/2] ~ M개의 서브 트리를 가진다. [ ] 는 버림이다.
모든 리프 노드는 같은 Level에 존재, 최소 [M/2]-1 개의 Key를 가져야 한다.
새로운 Key는 리프 노드에 삽입, Key는 중복될 수 없다.
3) 특성
왼쪽 서브 트리부터 작은 값이 들어가며 각 노드 내의 Key값은 오름차순이다.
4) 장점
스스로 균형을 맞추기 때문에 최악의 경우에도 O(log N)의 검색 성능 보인다.
l B*트리
1) 탄생 과정
B-트리에서 생성되는 노드의 수를 줄이기 위해 나왔다.
B-트리의 단점인 삽입에서 분열, 삭제에서 합병 등의
보조 연산을 가급적 지연시켜 회수를 감소시키려고 함
2) 조건
루트 노드를 제외한 모든 노드는 2/3이상 채워져야 한다.
노드가 가득차면 분열하는 대신 이웃 노드로 재배치한다.
이는 인접 노드까지 모두 가득찰 때까지 분열을 지연한다.
3) 장점
B*트리의 평균 저장공간 사용률은 81%에 달한다.
l B+트리 (B-트리 + 데이터 연결 리스트)
1) 탄생 과정
B-트리는 특성상 순회 작업이 상당히 난감하다.
B+트리는 색인구조에서 순차접근에 대한 문제의 해결책으로 제시되었다.
2) 조건
인덱스 노드 = 리프 노드를 제외한 나머지 노드
데이터 노드 = 리프 노드
특정 Key값이 리프 노드와 리프 노드의 부모 노드에 공존할 수 있다.
리프 노드는 연결 리스트 형태로 데이터의 빠른 접근을 위한 인덱스 역할만 함
데이터 노드 하나의 크기는 인덱스 노드 하나와 크기가 같지 않아도 됨
인덱스 노드에 존재하는 Key는
자신의 우측 서브 트리의 데이터 노드의 첫 번째 위치에 존재한다.
3) 장점
데이터 노드로 인해 순차 탐색에 유리함
4) 단점
B-트리는 Best Case에는 루트에서 끝날 수 있지만
B+트리는 무조건 리프 노드까지 가야한다.
l 해시 인덱스 (Hash Index)
| 특징 | |
| 해시랑 똑같은 장/단점 가진다 | 빠르지만, 충돌나면 느리다 같은 거 |
| 키 값을 저장하는 것이 아니라 해시 함수의 결과를 저장 |
컬럼이 아무리 길어도 작게 저장됨, 용량 적게 차지한다. 범위 검색, 부정형 비교, 원본 값 기준으로 정렬은 할 수 없고 등가 비교검색( 값이 같은지 다른지 )만 가능
주로 메모리 기반 테이블에서 사용된다. 디스크 기반 대용량 테이블로는 거의 사용되지 않음 |
|
|
|
l 전문 검색 인덱스 (Full Text Search Index)
문서 내용 전체를 인덱스화해서 특정 키워드가 포함되는 문서를 검색하는 전문 검색
문서의 키워드를 인덱싱하는 기법에 따라 구분자(Stopword), N-그램 으로 나뉜다.
B+트리는 LIKE 검색으로 중간일치, 후방일치 검색을 할 수 없지만, 전문 검색 인덱스 방법으로는 가능
구분자 (Delimiter) 또는 불용어 (Stopword)
전문의 내용을 사용자가 정의한 문자열을 구분자로 등록,
구분자를 이용해 키워드 분석하고 결과 단어를 인덱스로 생성하여 검색에 활용
키워드의 일부분을 검색할 수는 없다.
N-그램
본문을 무조건 몇 글자씩 잘라서 인덱싱 하는 방법
띄어쓰기가 전혀 없거나 문장 기호가 다른 언어 등을 고려하여, 규칙이 없는 전문도 분석하기 위해 존재
N은 인덱싱할 키워드의 최소 글자(바이트)를 의미
2-그램 인덱싱 (Bi-Gram)
구성
| 프론트 앤드 인덱스 | 2글자 단위의 최소 키워드와 대한 키를 관리 |
| 백엔드 인덱스 | 2글자 이상의 키워드 묶음을 관리 |
생성 과정
1) 문서의 본문을 2글자보다 큰 크기로 구분해서 백엔드 인덱스 생성
2) 백엔드 인덱스의 키워드를 2글자 씩 잘라서 프론트엔드 인덱스 생성
검색 과정
검색어를 2바이트로 동일하게 잘라서 프론트엔드 인덱스를 검색하고
백엔드 인덱스를 통해 일치하는 결과 가져옴
구분자 vs N-그램
검색어 : 노트북
| 노트북 | 삼성 노트북 | 삼성노트북 | |
| 구분자 | O | O | X |
| N-그램 | O | O | O |
검색어 길이가 길어질 수록 성능 저하
| 인덱스 크기 | 삽입 삭제 시간 | 전문 검색 시간 | 정확도 | |
| 구분자 | 작음 | 빠름 | 느림 | 낮음 |
| N-그램 | 큼 | 느림 | 빠름 | 높음 |
l R-트리 인덱스 (공간 인덱스)
MBR의 포함 관계를 B-트리 형태로 구현한 것
B-트리는 컬럼이 1차원이면 R-트리는 2차원이다.
R = Rectangle
최소 경계 사각형 (MBR, Minimum Bounding Rectangle)
어떤 도형을 둘러싼 최소 크기의 사각형을 의미합니다.
각 도형의 가장 바깥을 둘러싼 사각형이 최상위 레벨 = 노드
부분적인 도형을 둘러싼 사각형이 자식 노드로 늘어가는 식으로 구현된다.
사용 용도
2차원의 데이터를 인덱싱하고 검색하려는 목적으로 사용되며, GPS( 위도, 경도 ) 같은 공간 검색
내 위치에서 일정 거리 이내의 음식점을 찾아줘 이런거
l Fractal-트리 인덱스 (스트리밍 B-트리)
| B-트리의 문제점 | Fractal-트리의 개선 |
|
인덱스 키를 검색, 변경하는 과정에서 디스크의 랜덤 I/O가 상대적으로 많이 필요 |
순차 I/O로 변경해서 처리 대신에 B-트리보다 더 많은 정렬이 필요 |
|
노화로 인해 테이블이나 인덱스를 최적화 필요 |
단편화 발생하지 않도록 구성 가능 |
| 일정 수준을 넘어가면 급격한 성능 저하 발생 |
인덱스 키를 클러스터링하여 이러한 현상 없음 대용량 테이블에서 높은 성능 보장 |
B-트리로 생성된 인덱스를 Fractal-트리로 변경해도 동일한 효과 얻을 수 있음
독점 특허로 구현된 알고리즘임
노화 (Aging)
오랜 시간 동안 데이터가 변경되면서 단편화 발생하고, 인덱스 효율 떨어지는 현상
l 해싱
내용 수정 해야함
global detph, local depth 와 디렉토리 용량과의 관계를 설명하라.
확장가능 해싱에서 디렉토리의 엔트리를 결정하기 위해 사용되는 해시 값의 처음 몇개의 비트를 전역 깊이라 하며 각 디렉토리 엔트리가 가리키고 있는 버켓 안에서 각 레코드가 기반으로 하고 있는 비트 수를 지역 깊이라고 한다. 전역 깊이는 지역 깊이들의 최대값이다.
디렉토리 엔트리 수는 전역깊 이가 d라고 할때 2^d 개이다.
지역 깊이가 전역 깊이가 같은 버켓에서 오버플로우가 발생할 경우 버켓이 분할됨과 동시에 전역깊이도 1증가하여 디렉토리의 엔트리 수가 2배가 된다.
모든 지역 깊이의 값이 전역 깊이보다 작을 경우 전역 깊이가 감소하여 디렉토리 엔트리 수가 절반이 된다.
※ 분산 데이터베이스 (Distributed Database)
통신 네트워크를 통해 여러 대의 컴퓨터에 데이터를 분산시켜 저장하고 관리하여
사용자의 정보 요청 시 각각 컴퓨터에서 직접 처리, 제공하도록 구성된 데이터베이스
1. 분산 형태
l 수평 분산
다량의 정보를 여러 개의 동등한 기능을 가진 컴퓨터에 저장시켜 운영
각 서버는 서로 공유 가능, 하나의 서버 문제 생겨서 운영에 지장 없음
l 수직 분산
전체 운영을 담당하는 서버와 처리를 담당하는 부 서버로 구성하여 운영
관리가 용이하나 주 서버 장애 발생 시 전체 운영에 지장
2. 장 단점
l 장점
자체적인 처리 능력으로 신속한 서비스 제공
확장성 용이
신뢰성, 가용성 증진
효율성, 융통성
l 단점
구축이 어려움
오류 증가
구축, 운영 비용 증가
3. 튜닝 (Tuning)
데이터베이스 성능 향상과 사용자의 요구에 따라 빠른 검색을 통한
신속한 서비스 제공, 저장 공간 효율 향상시키는 등
최적화하기 위해 재조정(조율)하는 것
데이터 검색 시 자료가 저장된 블록의 이동과 접근 횟수를 줄일 수 있도록 공간 조정
트랜잭션 무결성 유지하면서 정보 공유를 위해 적정 수준의 Locking 기법 사용
l 개선 요소
설계, DBMS, SQL, H/W관점
l 과정
성능 진단 – 튜닝/분석 – 결과
4. 트리거 (Trigger)
삽입, 삭제, 갱신 연산을 테이블 내용이 바뀌었을 때
데이터 일관성과 무결성 유지를 위해 연관 테이블도 연쇄적으로 변경이 이루어질 수 있도록 하는 것
또는
특정 작업이 이루어졌을 때 자동으로 다른 작업이 처리되도록 하는 것
Event - DML 구문(삽입, 삭제, 갱신)
Condition - 동작을 실행해야 할 때의 조건
Action - 조건이 충족될 때 수행됨
CREATE [OR REPLACE] TRIGGER [트리거명]
BEFORE | AFTER [DML 구문] ON [구문 실행될 테이블 이름]
[ FOR EACH ROW ] – 각 행 별로 트리거 적용 된다. old나 new 사용가능
[ WHEN (condition) ]
BEGIN
[수행문들]
END;
/
임피던스 불일치(Impedance Missmatch)
1. 프로그래밍 언어의 데이터 타입과 관계 데이터 모델에서 사용 가능한 애트리뷰트 데이터타입이 서로 다르다.
각 애트리뷰트 타입을 해당 프로그래밍 언어에서 호환 가능한 타입으로 바인딩하는 것이 필요하다. 서로 다른 프로그래밍 언어들은 서로 다른 데이터 타입을 가지므로 각 프로그래밍 언어에 대해 서로 다른 바인딩이 필요하다.
2. 대부분의 질의 결과들이 투플들의 집합이나 다중 집합을 가지며, 각 투플은 여러 애트리뷰트 값들의 모임으로 구성되기 때문에 발생한다.
프로그램에서는 투플 집합으로부터 각 투플의 데이터 값들을 접근하여 이를 출력하거나 처리할 필요가 있다.
이를 위해 하나의 테이블 형태의 질의 결과 자료구조를 프로그래밍 언어에서 적절한 자료구조로 바인딩하는 것이 필요하다.
한번에 한 튜플 씩 접근하고 이 투플로부터 개별 애트리뷰트 값을 추출하기 위해 질의 결과 내에 있는 투플들을 대상으로 반복하는 루프 기능이 필요하다. 이를 위해 하나의 테이블 형태의 질의 결과 자료구조를 프로그래밍 언어에서 적절한 자료구조로 바인딩하는것이 필요하다.
추출된 애트리뷰트 값들은 프로그램에서 추가적인 처리를 할 수 있도록 적절한 프로그램 변수에 보통 복사된다. 질의 결과 내의 투플들을 루프하기 위해 커서 또는 반복 변수가 사용된다. 각 투플내의 개별 값들은 보통 적절한 타입의 프로그램 변수에 전달한다.
데이터베이스 모델과 동일한 데이터 모델과 데이터 타입을 사용하는 특별한 데이터베이스 프로그래밍 언어를 개발한다면 이러한 임피던스 불일치는 줄일 수 있다.
※ 커넥션 풀 (Connection Pool)
클라이언트의 요청에 따라 각 어플리케이션의 스레드에서 데이터베이스에 접근하기 위해
필요한 Connection을 미리 여러 개 생성해 두어 저장해 두고 필요할 때 꺼내 쓰는 기법
l 장점
매 연결마다 Connection 객체를 생성하고 소멸하는 비용을 줄일 수 있다.
DB 접근 시간 단축
Connection 수를 제한하여, 메모리와 DB에 걸리는 부하 조절가능
'취업 준비' 카테고리의 다른 글
| 컴퓨터 네트워크 (Computer Network) (3) | 2019.10.05 |
|---|---|
| 운영체제 (Operating System) (0) | 2019.10.05 |
| 컴퓨터 공학 전공 지식 요약 모음집 (0) | 2019.10.05 |