[전산공부] 데이터베이스

1) 데이터베이스 시스템

✔️ 스키마

1. 외부 스키마 : 프로그래머나 사용자의 입장에서 데이터베이스 정의
   1. 여러 개의 외부 스키마가 존재 O (공용 가능)
   2. 전체 논리적 일부분 표현 → Subschema라고 한다.
2. 개념 스키마 : 범 기관적인 입장에서 데이터베이스 정의
   1. 조직 전체 데이터베이스의 논리 구조
   2. 하나만 존재
   3. 개념 스키마와 외부 스키마 사이에는 논리적 데이터 독립성이 있어야 한다.
   4. 데이터베이스 내에는 하나의 개념 스키마 존재
   5. 개체, 관계, 제약조건, 접근 권한, 보안 정책, 무결성 규칙에 관한 명세
3. 내부 스키마 : 물리적 저장 형태를 기술하는 것
   1. 개념 스키마에 대한 저장구조를 정의

 

- 논리적 데이터 독립성: 데이터의 논리적 구조가 변경되어도 응용프로그램은 변경되지 않는 속성

- 물리적 데이터 독립성: 내부 스키마(물리적 구조)가 변경되어도 개념 스키마(논리적 구조)는 영향받지 않는다.

 

 

✔️ 시스템 카타로그

• 시스템 데이터베이스
• 자료 사전 역할
• 테이블 정보, 인덱스 정보, 뷰 정보 등 저장
• SQL을 통해 검색은 가능. 갱신 삭제 삽입 불가능.
• DBMS가 생성 및 관리
• 메타 데이터

---

2) 데이터 모델링

데이터 모델 구성요소

1) 구조(Structure): 논리적으로 표현된 개체 타입들 간의 관계로서 데이터 구조 및 정적 성질 표현

2) 연산(Operation): 데이터베이스에 저장된 실제 데이터를 처리하는 작업에 대한 명세로서 데이터베이스를 조작하는 기본 도구

3) 제약 조건(Constraint): 데이터베이스에 저장될 수 있는 실제 데이터의 논리적인 제약 조건

 

✔️ 데이터베이스 설계 순서(단계)

[ 개념적 설계 ]

• 개체-관계 모델(E-R 모델)
• 개념 스키마
• 트랜잭션 모델링

[ 논리적 설계 ]

• 관계, 계층, 네트워크 데이터 모델
• 레거시 데이터 모델: 네트워크, 계층
• 목표 DBMS에 맞는 논리 스키마 설계
• 트랜잭션 인터페이스 설계
• 정규화

[ 물리적 설계 ]

• B+트리, 해싱 등 인덱싱 기법을 정하는 것은 물리적 설계
• 저장 구조 및 엑세스 경로 설정
• 레코드 집중의 분석, 설계
• 저장 레코드 양식 설계
• 목표 DBMS에 맞는 물리적 구조의 데이터로 변환

 

✔️ 데이터 모델의 구성 요소

개체(Entity)

 

속성(Attribute)

 

관계(Relationship)

 

✔️ E-R 다이어그램 (개념적 데이터 모델)

• 1:1 관계: X와 Y가 1:1이면 X테이블의 기본키를 Y테이블 외래키로 추가하여 해야한다.
• 1:N 관계: X(1), Y(N)이면 X기본키 속성을 Y 외래키로 추가해야 한다.
• N:M 관계: N기본키와 Y기본키 속성을 모두 포함하는 별도의 관계 릴레이션 생성한다.

 

---

3) 관계 데이터 모델

• 차수: 하나의 릴레이션에서 속성 전체 개수
• 카디널리티: 하나의 릴레이션에서 튜플의 전체 개수

✔️ 키

• Super key(슈퍼키): 유일성, 최소성
• Candidate key(후보키): 유일성, 최소성
  • 릴레이션 튜플을 구별할 수 있는 최소한의 속성 집합으로 모든 릴레이션은 하나의 후보키를 갖는다.
• 기본키: 후보키 중에서 특별히 선정된 키로 중복된 값을 가질 수 없다.
  • 기본키는 NULL 값을 가질 수 없다.
• 대체키: 기본키로 선택되지 못한 후보키
• 외래키: 다른 릴레이션의 기본키를 참조하는 속성 또는 속성들의 집합

* 유일성: 하나의 릴레이션에서 모든 튜플은 서로 다른 키 값을 가져야 한다.

* 최소성: 꼭 필요한 최소한의 속성들로만 키를 구성한다.

 

✔️ 릴레이션 특징

• 튜플의 무순서성: 튜플 사이에는 순서가 없음
• 튜플의 유일성: 한 릴레이션에 똑같은 튜플 포함 불가
• 속성(애트리뷰트)의 무순서성: 스키마를 구성하는 속성 간 순서는 상관없음
• 속성(애트리뷰트)의 원자성: 속성을 구성하는 값은 동일한 값이 존재 가능 (속성값은 원자값을 갖는다.)
  • 속성은 식별을 위해 속성의 명칭은 유일하지만, 속성의 구성 값은 동일할 수 있다.

 

✔️ 무결성

• 개체 무결성: 기본 테이블의 기본키를 구성하는 어떤 속성도 Null 값이나 중복값을 가질 수 없다는 규정
• 도메인 무결성: 주어진 속성 값이 정의된 도메인에 속한 값이어야 한다는 규정
• 참조 무결성: 외래키 값은 Null이거나 참조 릴레이션의 기본키 값과 동일해야 한다. 즉 릴레이션은 참조할 수 없는 외래키 값을 가질 수 없다는 규정
  • 외래키는 Null 일 수도 있기 때문에, 튜플 삭제되면 무결성 위배 X
•  사용자 정의 무결성: 속성 값들이 사용자가 정의한 제약조건에 만족해야 한다는 규정

 

✔️ 관계대수

관계형 데이터베이스에서 원하는 정보와 그 정보를 검색하기 위해서 어떻게 유도하는가를 기술하는 절차적인 언어

 

[ 순수 관계 연산자 ]

• Select: 시그마(σ), 수평 단절
• Project: 파이(π), 수직 단절
  • 릴레이션의 열(세로)에 해당하는 Attribute를 추출하는 것이므로 수직 연산자
• Join: 공통 속성을 중심으로 두 개의 릴레이션을 하나로 합쳐서

• Division: ÷
  • R의 속성이 S의 속성 값을 모두 가진 튜플에서 S가 가진 속성을 제외한 속성만을 구하는 연산

✅ 고객 테이블에서 조건에 맞는 튜플 추출 방법

σ 조건 (릴레이션)

 

[ 일반 집합 연산자 ]

• UNION(합집합) : 중복은 제거해서 합침
• INTERSECTION(교집합) : 교집합
• DIFFERENCE(차집합) : 차집합
• CARTESIAN PRODUCT(교차곱) : 튜플들의 순서쌍 구하는 연산

 

✔️ 조인

• 외부 조인(outer join): 결과는 null 값 있을 수 있다.
• 자연조인(natural join): 동등조인에서 중복속성 중 하나만 남기고 모두 제거된 것
• 종류: 세타조인, 동등조인, 자연조인, 세미조인, 외부조인 등
• 조인연산: Cartesian Product + Select 형태

 

✔️ 칼럼 암호화 방식

1) API 방식 : 응용프로그램 자체를 암호화

• 암복호화 모듈이 API 라이브러리 형태로 각 애플리케이션 서버에 설치한다.
• 어플리케이션 소스에 암/복호화 API 적용하여 암/복호화 수행
• 장점 : 암복호화 속도 빠르고, DB서버에 부하가 거의 발생하지 않는다.
• 단점 : 적용된 어플리케이션 패치에 영향 받음. 구축 시 응용프로그램 전체/일부 수정 필요

 

2) Plug-In 방식 : DB서버 암호화

• 암복화화 모듈이 DBMS 내 Plug-in형태 모듈을 적용하여 암/복호화 수행
• 장점 : 어플리케이션 변경 최소화
• 단점 : 대용량 처리 시 암복호화 처리에 따른 DB서버 부하가 크다.
  
  

3) Hybrid 방식 : API방식과 Plug-In 방식 혼용

• 대용량 트랜잭션 처리는 API 방식을 사용하여 성능 저하를 최소화하고, 나머지는 플러그인 방식을 사용하여 애플리케이션 수정 최소화하는 방식

 

4) 블록 암호화 방식

• TDE(Transparent Data Encryption) 방식 : DBMS 자체 암호화 방식
• 파일 암호화 방식 : 운영체제 암호화 방식

---

4) 관계 데이터베이스 언어(SQL)

✔️ 뷰(view)

• 뷰 정의할 때 GROUP BY 사용 가능
• 뷰 정의 질의어에는 ORDER BY 사용 불가능
• 뷰는 물리적 테이블을 기반으로 만들어지는 논리적인 테이블이며, 뷰 기반으로 새로운 뷰를 생성할 수 있다.
• 뷰를 정의할 때는 CREATE문, 제거할 때는 DROP문을 사용한다.
• 뷰 생성에 사용된 테이블의 기본키를 구성하는 속성이 포함되어 있는 뷰만 데이터 변경이 가능하다. 
• 장점
  • 데이터의 논리적 독립성 제공
  • 데이터에 대한 보안 제공
  • 뷰는 또 다른 뷰의 정의에 사용될 수 있다.
  • 뷰의 정의만 시스템 카탈로그에 저장 → 필요시 실행시간에 테이블 구축
  • 자료에 대한 접근제어로 보안 제공
  • 간단한 사용자 데이터 관리
  • 여러 사용자의 상이한 응용이나 요구 지원
• 단점
  • 독자적인 인덱스 가질 수 없다.
  • 일반적으로 뷰 정의 변경 불가, ALTER VIEW를 지원하는 데이터베이스도 존재
  • 삽입, 삭제, 갱신 제약 따름

 

---

5) 정규화

✔️ 데이터베이스 정규화 단계 (도부이결다조)

• 1NF: 원자값이 아닌 도메인 분해
• 2NF: 부분 함수 종속 제거, 완전 함수 종속 관계
• 3NF: 이행 함수 종속 제거
  • 이행적 종속이 → A→B 이고 B → C일 때 A → C를 만족한다. 이것을 제거하는 게 3NF
• BCNF : 결정자가 후보키가 아닌 함수종속 제거, 모든 키가 결정자, 후보키 생기는 거, 강한 제3정규형이라고도 한다.
• 4NF : 다치종속 제거
• 5NF : 조인종속 제거 (모든 종속이 후보키를 통해서만 성립, 후보키를 통하지 않는 게 조인 속성인데 이걸 제거했음)

 

 

✔️ 정규화

하나의 종속성이 하나의 릴레이션에 표현될 수 있도록 분해해 가는 과정

• 데이터 구조의 안정성 및 무결성을 유지하기 위해서 정규화를 수행함
• 데이터베이스의 논리적 설계 단계에서 수행
• 데이터 삽입 시 릴레이션을 재구성할 필요성을 줄인다.
• 개체와 속성의 누락 여부 확인이 가능하다.

 

✔️ 반정규화

• 반정규화를 수행하면 시스템의 성능이 향상되고 관리 효율성은 증가하지만 데이터의 일관성 및 정합성이 저하될 수 있다.
• 방법
  • 테이블 통합: 두 개의 테이블이 조인(Join)되는 경우가 많아 하나의 테이블로 합쳐 사용하는 것이 성능 향상에 도움이 될 경우
  • 테이블 분할: 테이블을 수직 또는 수평으로 분할
  • 중복 테이블 추가
  • 중복 속성 추가: 자주 사용하는 속성을 하나 더 추가하는 것

 

---

6) 트랜잭션

✔️ Lock 연산 종류

• Shared Lock(공유 잠금): 해당 데이터에 read 연산만(write 연산은 실행 X) 실행할 수 있다. 그리고 해당 데이터에 대한 다른 트랜잭션도 shared lock 연산을 동시에 실행할 수 있다.(동시에 접근이 가능하다)
• Exclusive Lock(배타 잠금): 해당 데이터에 read 연산과 write 연산을 모두 실행할 수 있다. 하지만 lock이 해제될 때까지 해당 데이터에 다른 트랜잭션이 어떤 lock 연산도 실행할 수 없다.(접근 불가능)

[ Pessimistic lock(비관적 락) ]

• 비관적 락이란 지원 요청에 따른 동시성 문제가 발생할 것이라고 예상하고 lock을 걸어버리는 방법이다.
• 다시 말해서, 트랜잭션이 시작될 때 Shared Lock 또는 Exclusive Lock을 걸고 시작하는 방법이다.

[ Optimistic lock(낙관적 락) ]

 

낙관적 락(Optimistic lock)은 자원에 lock을 걸어서 선점하지 말고, 동시성 문제가 발생하면 그때 가서 처리하는 방법이다.

 

 

✔️ 병행제어

다수의 사용자 환경에서 일관성 유지를 보장하는 기법

 

• 2단계 로킹 규약(2PL)을 적용하면 트랜잭션 스케줄의 직렬 가능성을 보장할 수 있으나, 교착 상태가 발생할 수도 있다.

 

---

7) SQL

✔️ Transact-SQL, T-SQL

IF와 ELSE

IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1
   PRINT 'It is the weekend.'
ELSE
   PRINT 'It is a weekday.'

 

BEGIN과 END

IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1
BEGIN
   PRINT 'It is the weekend.'
   PRINT 'Get some rest on the weekend!'
END
ELSE
BEGIN
   PRINT 'It is a weekday.'
   PRINT 'Get to work on a weekday!'
END

 

WHILE

DECLARE @i INT
SET @i = 0

WHILE @i < 5
BEGIN
   PRINT 'Hello world.'
   SET @i = @i + 1
END

 

 

기타

✔️  캐시 관리 방식

• Force: 트랜잭션 커밋 시 즉시 디스크에 기록, 데이터 일관성 보장. redo.
• No-Force: 트랜잭션 커밋 시 디스크에 즉시 기록하지 않음, 성능 향상 가능하지만 손실 위험 있음.
• Steal: 트랜잭션이 메모리에서 변경된 데이터를 다른 트랜잭션이 사용할 수 있음, 성능 향상 가능하지만 일관성 문제. undo.
• No-Steal: 트랜잭션이 변경한 데이터를 다른 트랜잭션이 사용할 수 없도록 하여 일관성 보장, 메모리 사용량 증가.

 

문제풀이

* 회복 기법

1) 로그 기반 회복 기법

 

즉시갱신 회복 기법(Immediate Update)

• 트랜잭션 수행중 갱신 결과를 바로 DB 에 반영. 장애 발생시 로그를 참고하여 되돌림
• Rollback 시 Redo(commit 한 트랜잭션), Undo(commit 전인 트랜잭션) 가 모두 실행되며, 트랜잭션 수행 중 갱신 결과를 바로 DB 에 반영한다.

지연갱신 회복 기법(Deferred Update)

• 트랜잭션이 완료 전에는 로그에만 기록. 장애 발생시 로그 폐기, Redo(commit한 트랜잭션)만 실행

 

> 2021년 국가직 7급

 

즉시 갱신에서는 커밋된 트랜잭션은 Redo 대상이고, 커밋되지 않은 트랜잭션은 Undo 대상이다.

체크포인트 이후로 커밋된 트랜잭션은 T4만 있기 때문에 T4만 반영되고 나머지는 다 반영되지 않는다.

 

> 2018년 서울시 7급

 

 

> 2021년 국가직 7급

 

격리 수준이 repeatable read 이기 때문에 commit 전인 것은 반영이 안된다. 만약에 read uncommited 였다면 커밋 전 내용도 포함해서 계산했을거다!

> 2021년 국가직 7급

 

 

 

---

📌 Reference

• [Day 101] 데이터베이스 암호화 기술
• 위키백과:트랜잭트 SQL