데이터베이스

데이터 베이스 설계

요구사항 분석 -> 개념적 설계(ER 모델링) -> 논리적 설계(스키마로 사상) -> 스키마 정제(정규화) -> 물리적 설계(데이터베이스 성능 향상)

 

 

 

ER 다이어그램에서의 강한 관계와 약한 관계

ER 다이어그램(개체-관계 다이어그램)은 데이터베이스 설계에서 중요한 역할을 하며, 개체(엔티티)와 관계(리레이션십)를 시각적으로 표현한다. 이 다이어그램에서 강한 관계와 약한 관계는 데이터 모델링의 핵심 요소로 간주된다.

 

 

 

강한 관계 (Strong Relationship)

강한 관계는 두 개체 타입(엔티티 타입) 간의 직접적인 관계를 나타내며, 두 개체가 독립적으로 고유하게 식별될 수 있는 경우에 성립한다.

 

약한 관계 (Weak Relationship)

약한 관계는 강한 엔티티 타입과 약한 엔티티 타입 간의 관계를 정의한다. 약한 엔티티 타입은 자체 애트리뷰트 값만으로는 고유하게 식별되지 않으며, 다른 강한 엔티티 타입으로부터 키 애트리뷰트를 가져와야 고유하게 식별된다.

 

 

약한 엔티티 타입 (Weak Entity Type)

약한 엔티티 타입은 자체 애트리뷰트 값으로는 고유하게 식별되지 않으며, 다른 엔티티 타입(소유 엔티티 타입)에서 제공하는 키 애트리뷰트를 통해서만 고유하게 식별된다.

 

소유 엔티티 타입 (Owner Entity Type)

소유 엔티티 타입은 약한 엔티티 타입에게 키 애트리뷰트를 제공하여 약한 엔티티 타입이 고유하게 식별될 수 있도록 돕는다. 예를 들어, Order와 OrderItem이 있을 때, OrderItem은 Order 엔티티 타입의 키 애트리뷰트를 통해 고유하게 식별된다.

 

 

저장된 애트리뷰트

저장된 애트리뷰트는 다른 애트리뷰트와 독립적으로 존재하며, 실제 데이터를 저장하는 데 사용된다. ER 다이어그램에서는 대부분의 애트리뷰트가 저장된 애트리뷰트로 나타난다.

 

 

차수 (Degree)

차수는 관계로 연결된 엔티티 타입들의 개수를 의미한다. 예를 들어, 삼항 관계는 세 개의 엔티티 타입이 참여하는 관계를 나타내며, 차수는 3이 된다.

 

 

 

 

 

 

 

 

 

역할에 따라 카디날리티가 달라질 수 있다.

 

 

 

 

 

 

 

 

 

ER 스키마 사상 과정

 

 

E2가 가지고 있던 K가 기본키가 됨

 

 

 

2진 1:1 관계 타입을 사상할 때는 S와 T 중에서 한 릴레이션을 선택하여, 만일 S를 선택했다면 T의 기본 키를 S에외래 키로 포함시킨다. S와 T 중에서 관계 타입에 완전하게 참여하는 릴레이션을 S의 역할을 하는 릴레이션으로 선택하는 것이 좋다.

 

 

 

2진 1:N 관계 타입을 사상할 때는 관계 타입 R에 참여하는 1측의 엔티티 타입에 대응되는 릴레이션 T의 기본키를 릴레이션 S에 외래 키로 포함시킨다.

 

 

 

2진 M:N 관계 타입 R에 대해서는 릴레이션 R을 생성한다. 참여 엔티티 타입에 해당하는 릴레이션들의 기본 키를 릴레이션 R에 외래키로 포함시키고, 이들의 조합이 릴레이션 R의 기본 키가 된다.

 

 

 

3진 이상의 각 관계 타입 R에 대하여 릴레이션 R을 생성한다. 관계 타입 R에 참여하는 모든 엔티티 타입에 대응되는 릴레이션들의 기본키를 릴레이션 R에 외래 키로 포함시킨다. 관계 타입 R에 참여하는 엔티티 타입들의 카디날리티가 1:N:N이면 카디날리티가 1인 릴레이션의 기본 키를 참조하는 외래 키를 제외한 나머지외래 키들의 조합이 릴레이션 R의 기본 키가 된다.

 

1인 쪽은 기본키가 되지 않음

 

 

 

각 다치 애트리뷰트에 대하여 릴레이션 R을 생성한다. 다치 애트리뷰트에 해당하는 애트리뷰트를 릴레이션 R에 포함시키고, 다치 애트리뷰트를 애트리뷰트로 갖는 엔티티 타입이나 관계 타입에 해당하는 릴레이션의 기본 키를 릴레이션 R에 외래 키로 포함시킨다.

 

 

 

E1에 B 포함 안함, MVA에 B가 기본키가 됨

 

 

 

 

 

 

 

 

 

자기 디스크

 

 

 

 

디스크에서 임의의 블록을 읽어오거나 기록하는데 걸리는 시간은 탐구시간(seek time), 회전 지연 시간(rotational delay), 전송 시간(transfertime)의 합

 

탐구시간: 디스크의 읽기/쓰기 헤드가 원하는 트랙으로 이동하는 데 걸리는 시간

회전지연시간: 디스크가 회전하여 원하는 블록이 디스크 헤드 아래에 올 때까지 기다리는 시간

전송시간: 디스크에서 데이터 블록을 실제로 읽거나 쓰는 데 걸리는 시간

 

 

 

 

지연 관리 방법

레코드 삭제시 레코들을 이동시킬 필요 없이 free list를 관리하여 처리

 

 

 

 

파일과 인덱스

히프 파일

가장 단순한 화일 조직이며, 일반적으로 레코드들이 삽입된 순서대로 화일에 저장됨

 

 

 

 

 

순차 파일

레코드들이 하나 이상의 필드 값에 따라 순서대로 저장된 파일이며, 레코드들이 일반적으로 레코드의 탐색 키(search key) 값의 순서에 따라 저장됨 -> 탐색 키에 대한 검색만 효율적

 

 

 

 

 

 

 

기본 인덱스

기본 키를 기반으로 하며, 테이블의 물리적 저장 순서를 결정할 수 있다. 항상 유일성을 보장하며 자동으로 생성된다.

 

 

 

 

 

보조 인덱스

기본 키 이외의 필드를 기반으로 하며, 물리적 저장 순서에 영향을 미치지 않는다. 여러 개의 보조 인덱스를 생성할 수 있으며, 유일성을 보장하지 않는다.

 

 

보조 인덱스는 인덱스만 정렬되어 있음

 

 

 

 

 

클러스터링 인덱스

클러스터링 인덱스는 데이터베이스 테이블의 기본 키 또는 다른 고유한 키를 기반으로 데이터를 물리적으로 정렬하여 저장함

이로 인해 인덱스 순서와 데이터 저장 순서가 일치하게 됨

 

 

클러스터링 인덱스는 범위질의에 유용 

 

 

 

 

 

밀집 인덱스

 

 

 

희소 인덱스는 각 데이터 블록마다 한 개의 엔트리를 갖고, 밀집 인덱스는 각 레코드마다 한 개의 엔트리를 가짐

 

일반적으로 희소 인덱스가 블록 수가 더 적음

 

희소 인덱스는 일반적으로 밀집 인덱스에 비해 인덱스 단계 수가 1정도적으므로 인덱스 탐색시 디스크 접근 수가 1만큼 적을 수 있음

 

희소 인덱스는 밀집 인덱스에 비해 모든 갱신과 대부분의 질의에 대해 더 효율적임. 그러나 질의에서 인덱스가 정의된 애트리뷰트만 검색(예를 들어, COUNT질의)하는 경우에는 데이터 화일을 접근할 필요 없이 인덱스만 접근해서 질의를 수행할 수 있으므로 밀집 인덱스가 희소 인덱스보다 유리함. 한 화일은 한 개의 희소 인덱스와 다수의 밀집 인덱스를 가질 수 있음

 

클러스터링 인덱스는 희소 인덱스일 경우가 많으며 범위 질의 등에 좋음. 보조 인덱스는 밀집 인덱스이므로 일부 질의에 대해서는 화일을 접근할필요 없이 처리할 수 있음

 

보조 인덱스는 일반적으로 밀집 인덱스이므로 같은 수의 레코드들을접근할 때 보조 인덱스를 통하면 기본 인덱스를 통하는 경우보다 디스크접근 횟수가 증가할 수 있음

 

 

 

 

 

다단계 인덱스

가장 상위 단계 인덱스를 마스터 인덱스(master index)라고 부름

대부분의 다단계 인덱스는 B+-트리를 사용

 

 

 

 

CREATE INDEX EmpIndex ON EMPLOYEE (DNO, SALARY); 로 인덱스를 만들었을 때, DNO와 SALARY를 동시에 사용할 수 있고, DNO만을 사용하여 질의에 활용할 수도 있지만 SALARY만 이용해 질의할 순 없음(DNO기준으로 정렬된 것 내에서 SALARY로 정렬되었기 때문)

 

 

인덱스는 검색 속도를 향상시키지만 인덱스를 저장하기 위한 공간이 추가로 필요하고 삽입, 삭제, 수정 연산의 속도는 저하시킴

 

 

 

 

 

 

 

인덱스가 정의된 애트리뷰트에 산술 연산자가 사용됐을 때, 널값에 대해서는 인덱스가 사용되지 않음

 

 

 

 

질의 튜닝을 위한 추가 지침

• DISTINCT절의 사용을 최소화하라
• GROUP BY절과 HAVING절의 사용을 최소화하라
• 임시 릴레이션의 사용을 피하라
• SELECT * 대신에 SELECT절에 애트리뷰트 이름들을 구체적으로명시하라

 

 

 

 

 

뷰

• 뷰는 다른 릴레이션으로부터 유도된 릴레이션
• 뷰는 관계 데이터베이스 시스템에서 데이터베이스의 보안 메카니즘(뷰를 통해 데이터 접근)
• 복잡한 질의를 간단하게 표현하는 수단
• 데이터 독립성을 높이기 위해서 사용(데이터 구조가 바꿔도 기존의 질의 다시 작성할 필요 X)
• 데이터의 무결성을 보장하기 위해 사용(With Check Option)
• 동일한 데이터에 대한 여러가지 뷰 제공

 

 

 

 

 

 

시스템 카탈로그

시스템 카탈로그는 시스템 내의 객체(기본 릴레이션, 뷰, 인덱스, 사용자, 접근 권한 등)에 관한 정보를 포함

시스템 카탈로그 = 데이터 사전 = 시스템 테이블 = 메타데이터

시스템 카탈로그의 정보를 통해 질의 최적화 수행

 

 

 

DELETE, UPDATE 또는 INSERT문을 사용하여 사용자가 직접 시스템 카탈로그를 변경할 수 없음

 

 

 

 

 

 

오라클 시스템 카탈로그

 

 

 

 

WHERE은 Table 이름, FROM은 뷰 이름, SELECT는 선택할 것들

 

 

뷰에 테이블의 기본키가 포함되어 있지 않으면 EMPNO 입력이 불가능

 

 

 

 

 

 

 

 

릴레이션 정규화

정규화(normalization)는 주어진 릴레이션 스키마를 함수적 종속성과 기본키를 기반으로 분석하여, 원래의 릴레이션을 분해함으로써 중복과 세가지 갱신 이상을 최소화 함

 

갱신이상

수정이상: 반복된 데이터 중에서 일부만 수정하면 데이터의 불일치 발생

삽입이상: 불필요한 정보를 함께 저장하지 않고는 어떤 정보를 저장하는 것이 불가능

삭제이상: 유용한 정보를 함께 삭제하지 않고는 어떤 정보를 삭제하는 것이 불가능

 

 

결정자

결정자는 주어진 릴레이션에서 다른 애트리뷰트(또는 애트리뷰트들의 집합)를 고유하게 결정하는 하나 이상의 애트리뷰트를 의미

만일 애트리뷰트A가 애트리뷰트B의 결정자이면 B가 A에 함수적으로 종속한다고 말함

 

 

슈퍼키: 테이블에서 각 행을 유일하게 식별할 수 있는 하나 또는 그 이상의 속성들의 집합

후보키: 테이블에서 각 행을 유일하게 식별할 수 있는 최소한의 속성들의 집합

 

 

 

 

 

 

 

 

관계데이터 베이스 설계의 비공식적인 지침

• 이해하기 쉽게 명확한 스키마를 만들어라
• 널값을 피하라
• 가짜 투플이 생기지 않도록 해라
• 스키마를 정제하라

 

 

 

 

 

 

종속성

완전 함수 종속성

주어진 릴레이션 R에서 애트리뷰트 B가 애트리뷰트 A에 함수적으로 종속하면서 애트리뷰트 A의 어떠한 진부분 집합에도 함수적으로 종속하지 않으면 애트리뷰트B가  애트리뷰트A에 완전하게 함수적으로 종속한다고 말함

 

부분 함수 종속성

기본키가 여러 속성으로 구성되어 있을경우 기본키를 구성하는 속성 중 일부에게 종속된 경우 부분 함수 종속성이라 말함

 

이행 함수 종속성

X→Y, Y→Z 이란 종속 관계가 있을 경우, 해당 관계를 통해 X→Z가 성립될 때 이행적 함수 종속이라 말함

 

 

 

 

 

정규형

제1정규형

• 릴레이션의 모든 애트리뷰트가 원자값을 가짐
• 모든 애트리뷰트에 반복 그룹이 나타나지 않음
• 부분 함수 종속성이 존재할 때 갱신 이상이 발생 -> 제2정규형에서 부분 함수 종속성 제거

 

 

 

 

제2정규형

• 제1정규형을 만족하면서, 어떤 후보키에도 속하지 않는 모든 애트리뷰트들이 R의 기본키에 완전하게 함수적으로 종속하는 것
• 이행 함수 종속성이 존재하기 때문에 갱신 이상 발생 -> 제3정규형에서 이행 함수 종속성 제거

 

 

제3정규형

• 제2정규형을 만족하면서, 키가 아닌 모든 애트리뷰트가 릴레이션R의 기본키에 이행적으로 종속하지 않는 것
• 키가 아닌 애트리뷰트가 다른 애트리뷰트 결정할 때 갱신 이상 발생 -> BCNF에서는 모든 결정자를 후보키로 둠

 

 

 

BCNF

제3정규형을 만족하고, 모든 결정자가 후보키이어야 함. 즉 후보키만 다른 애트리뷰트를 결정할 수 있음

 

 

 

 

 

 

 

정규화의 장점

중복과 갱신이상 감소

무결성 제약조건을 시행하기 위해 필요한 코드의 양 감소

 

정규화 단점

정규화 과정에서 릴레이션이 분해되기 때문에 더 많은 조인이 필요

-> 검색 수행 시간을 높이기 위해 역정규화를 통해 낮은 정규형으로 돌아가기도 함

 

 

 

무손실 분해

데이터베이스 테이블을 분해할 때 데이터 손실 없이 원래의 테이블로 복원할 수 있도록 하는 것

 

• 공통 속성의 보존: 분해된 테이블들 간에 공통 속성이 있어야 한다. 이 공통 속성을 통해 조인할 수 있다.
• 함수 종속성 보존: 분해 과정에서 원래 테이블의 함수 종속성이 유지되어야 한다. 이는 분해된 테이블들이 원래 테이블의 함수 종속성을 만족하게 구성되어야 함을 의미한다.

 

 

 

 

 

권한 관리

생성자는 자신이 소유한 임의의 객체에 대한 특정 권한을 GRANT문을 사용하여 다른 사용자나 역할에게 허가할 수 있음

 

 

With grant option없이 권한을 허가받았다면 다른 사용자에게 해당 권한을 다시 허가할 수 없음

 

Revoke문으로 권한을 취소했다면 권한을 취소 당한 사용자가 WITH GRANT OPTION을 통해서 다른 사용자에게 허가했던 권한들도 연쇄적으로 취소됨

 

 

 

동일한 권한들의 집합을 여러 사용자들에게 허가하는 대신에 이 권한들을 역할에게 허가하고, 역할을 각 사용자에게 허가하기도 함

 

 

 

 

 

 

 

트랜잭션

동시성 제어

다수 사용자가 데이터베이스를 동시에 접근하도록 허용하면서 데이터베이스의 일관성을 유지함

 

회복

데이터베이스를 갱신하는 도중에 시스템이 고장 나도 데이터베이스의 일관성을 유지함

 

트랜잭션

데이터베이스 응용에서 하나의 논리적인 단위를 수행하는 데이터베이스 연산들의 모임

 

 

 

트랜잭션의 특성(ACID)

1. 원자성(Atomicity): 한 트랜잭션 내의 모든 연산들이 완전히 수행되거나 전혀 수행되지 않음(all or nothing)을 의미
2. 일관성(Consistency): 어떤 트랜잭션이 수행되기 전에 데이터베이스가 일관된 상태를 가졌다면 트랜잭션이 수행된 후에 데이터베이스는 또 다른 일관된 상태를 가짐
3. 고립성(Isolation): 한 트랜잭션이 데이터를 갱신하는 동안 이 트랜잭션이 완료되기 전에는 갱신 중인 데이터를 다른 트랜잭션들이 접근하지 못하도록 해야 함
4. 지속성(Durability): 일단 한 트랜잭션이 완료되면 이 트랜잭션이 갱신한 것은 그 후에 시스템에 고장이 발생하더라도 손실되지 않음

 

 

 

 

 

 

 

 

동시성 제어

동시성 제어 기법은 여러 사용자들이 다수의 트랜잭션들을 동시에 수행하는 환경에서 부정확한 결과를 생성할 수 있는, 트랜잭션들 간의 간섭이 생기지 않도록 함

 

• 직렬 스케쥴: 여러 트랜잭션들의 집합을 한 번에 한 트랜잭션씩 차례대로 수행함
• 비직렬 스케쥴: 여러 트랜잭션들을 동시에 수행함
• 직렬 가능: 비직렬 스케줄의 결과가 어떤 직렬 스케줄의 수행 결과와 동등함

 

비직렬 스케쥴을 허용하는 것이 동시성 수준을 높이는데 좋음

-> 직렬 스케쥴의 수행결과와 동등한 비직렬 스케쥴을 직렬가능 스케쥴이라 하고 그것들만 허용

 

 

 

 

 

 

동시성 제어 문제

• 갱신 손실(lost update): 수행 중인 트랜잭션이 갱신한 내용을 다른 트랜잭션이 덮어 씀으로써 갱신이 무효가 되는 것
• 오손 데이터 읽기(dirty read): 완료되지 않은 트랜잭션이 갱신한 데이터를읽는 것
• 반복할 수 없는 읽기(unrepeatable read): 한 트랜잭션이 동일한 데이터를 두 번 읽을때 서로 다른 값을 읽는 것

 

 

 

로크를 조금씩 해제하면 자원 효율 올라가고, 동시성이 증가함

 

 

작은 단위 로크를 사용하면 동시성 올라감 but, 일일 다 로크 걸어줘야 해서 비효율적

 

다중단위 로크 할 때는 Top-down 로크 요청 -> 더 큰 범위부터 로크가 있는지 확인

로크 요청 시 조상 노드에 대해서는 Intension(의사) 로크 획득

 

 

 

 

 

 

회복

주기억 장치 버퍼 내의 동일한 데이터베이스 항목을 갱신한후에 디스크에 기록함으로써 성능을 향상

변경 사항에 대한 로그 레코드를 플래시 하고 실제 갱신 정보를 플래시 해야 손실 시 회복 가능

 

page lsn: page 내에서 마지막으로 갱신된 연산에 대한 로그  레코드 번호를 기록, page lsn이 log에 기록되어 있는지 확인하고 반영되어 있으면 pass, 반영 안되어 있으면 반영

 

고장이 발생하기 전에 트랜잭션이 완료 명령을 수행 -> 갱신 사항 재수행

고장이 발생하기 전에 트랜잭션이 완료 명령을 수행X -> 갱신 사항 취소

 

안전저장장치: 모든 유형의 고장을 견딜 수 있는 저장장치

 

트랜잭션의 원자성과 지속성을 보장하기 위해 DBMS는 로그 레코드를 기록

 

 

 

이중 로그(dual logging): 로그를 두 개의 디스크에 중복해서 저장하는 것

 

 

 

start와 commit이 둘 다 존재하면 재수행

start만 존재하면 취소

 

로그 먼저 쓰기: 데이터베이스 버퍼보다 로그 버퍼를 먼저 디스크에 기록해야 함

 

 

• Flush LSN: 가장 최근에 디스크에 저장된 로그 레코드의 LSN.
• Page LSN: 특정 페이지에 마지막으로 적용된 로그 레코드의 LSN.

 

 

 

DBMS는 회복시 재수행할 트랜잭션의 수를 줄이기 위해서 주기적으로 체크포인트를 수행함

 

 

 

0,1,3은 무시, 4,5는 재수행, 2, 6은 취소

 

 

용자들에게 데이터베이스 사용을 계속 허용하면서, 지난 번 백업 이후에갱신된 내용만 백업을 하는점진적인 백업(incremental backup)이 바람직함

 

 

 

 

 

 

오라클 트랜잭션

 

set 명령으로 트랜잭션의 속성을 명시할 수 있다.

 

고립수준이 낮으면 동시성은 높아지지만 데이터의 정확성은 낮아짐

고립수준이 높으면 데이터가 정확해지지만 동시성이 저하됨

 

READ UNCOMMITED - 읽는 것은 공유 로크 걸지 않고 데이터 읽음

READ COMMITED - 읽으려는 데이터에 대해 공유 로크를 걸고 읽기가 끝나자마자 로크를 해제함

(둘 다 갱신 데이터에 대해서는 독점 로크 검)

REPEATABLE READ - 현재 읽고 있는 것을 제외한 다른 사용자가 갱신하는 것이 가능

SERIALIZABLE - 검색되는 투플 뿐만 아니라 인덱스에 대해서도 공유 로크를 검, 영역에 해당하는 데이터에 대한 수정 및 입력이 아예 불가능함

 

 

• Repeatable Read:
  • 읽은 데이터를 트랜잭션이 끝날 때까지 다른 트랜잭션이 수정하거나 삭제할 수 없음.
  • Dirty Read와 Non-repeatable Read를 방지.
  • Phantom Read를 방지하지 않음.
• Serializable:
  • 모든 트랜잭션이 순차적으로 실행되는 것처럼 동작.
  • Dirty Read, Non-repeatable Read, Phantom Read를 모두 방지.
  • 가장 높은 일관성을 제공하지만, 성능 저하가 발생할 수 있음.

 

갱신 손실은 어디서도 발생 안함 -> 일관성이 아예 깨지므로