개발자 Lim

1. MySQL 다운로드 설치

중요!: 여기서 설정해둔 비밀번호는 따로 메모해두거나 꼭 암기해 두어야 한다.

2. MySQL Workbench 실행 후 연결 객체 생성

+버튼을 통해 새 연결 객체 생성

여기서 Username과 설정한 Password를 SpringBoot에서 연결 작업에 필요하기 때문에 따로 메모해두거나 꼭 암기해 두어야 한다.

Test Connection 버튼을 통해 연결이 잘 되는지 확인!

Ok 버튼을 통해 생성

3. 사용할 DB Schema 생성

DDL, DCL

• CREATE, ALTER, DROP, GRANT, REVOKE, COMMIT, ROLLBACK
• 자동 완료, 자동 복귀
  • 트랜잭션의 성공 실패 여부에 따라 자동으로 커밋, 롤백 되는 것
• DDL, DCL이 성공적으로 실행된 경우 복구 불가능

DML

• SELECT, INSERT, DELETE, UPDATE
• commit: 트랜잭션 종료
• rollback: 가장 최근 commit 시점까지 복귀
• set autocommit on: DML이 자동으로 commit 됨

오라클에서 자동 완료가 되는 경우

• SQL*Plus가 정상적으로 종료하는 경우
• DDL, DCL 명령을 실행하는 경우
• set autocommit on 명령을 실행하고 개별 DML 명령어를 사용하는 경우

저장점

• 트랜잭션을 여러 개로 분할하여 일부만을 복귀시키고자 할 때 사용
• 지정점 지정: savepoint <라벨>
• 지정된 지점으로 복귀: rollback to <라벨>

데이터베이스에서의 장애 종류

• 트랜잭션 장애: 트랜잭션 내의 논리적 오류나 잘못된 입력 데이터, 또는 시스템 내의 자원 부족으로 인한 트랜잭션 중단 (DBMS)
• 시스템 장애: 정전이나 하드웨어 결함으로 시스템 작동이 중단 (DBMS)
• 미디어 장애: 디스크와 같은 저장장치의 일부 또는 전체가 손상 (OS)
  • <대처방안>
  • 백업 파일: DVD나 자기 테이프와 같은 저장장치를 이용
  • Mirroring: 하나의 데이터베이스를 서로 다른 디스크에 복제 <RAID 기법>

복구

• 데이터베이스를 장애 발생 이전의 일관된 상태로 복원
• 복구의 기본 원리는 데이터 중복
• 로그를 확인하여 트랜잭션이 완료되었다면 <T commit>로그가 있다면 수행 실패하였다면 <T commit>로그가 없다면 롤백
  • UNDO: 변경되기 전 값을 가지고 복구
  • REDO: 변경된 값을 가지고 복구 (재실행 연산)
• 로그가 존재하지 않는다면 백업이 불가능

로그

• 트랜잭션이나 시스템 장애에 대비한 데이터베이스 갱신 이력을 저장 한 것 로그는 OS가 갱신한 데이터 항목을 기록하기 전에 로그 레코드에 먼저 기록 되어야 함
• 구성 요소
  • 트랜잭션의 시작 <T start>
  • 트랜잭션의 완료 <T commit>
  • 데이터 항목에 대한 갱신
    • 지연 갱신 기법: <T,x, v(갱신 값)>
    • 즉시 갱신 기법: <T, x, v1(갱신 전 값), v2(갱신 후 값)>
• 갱신 방법
  • 지연 갱신: 트랜잭션의 수행이 성공적으로 끝난 후에 갱신 내용을 디스크에 반영 하는 것 완료 전에는 주기억 장치의 버퍼에만 기록을 저장 따라서 데이터 베이스값이 무엇인지 확실히 알 수 없음 (UNDO 연산 불필요, REDO 연산 필요) -> NO-UNDO/REDO 알고리즘
    • 트랜잭션 T가 시작되면 로그에 <T start>를 기록
    • T가 데이터 항목 x에 쓰기 연산을 수행하면 로그에 기록하고 메모리 버퍼에 변경된 x 저장
    • 최종적으로 T가 모든 연산에 대한 실행을 마치면 T는 부분 완료 상태가 되고 로그에 <T commit>을 기록
    • <T commit>이 로그에 기록되면 T는 완료 상태가 되어 종료
    • 이후 적정한 시기에 버퍼에 저장된 x를 디스크에 저장
  • <복구 방법>
    • 로그 파일의 처음부터 기록을 순차적으로 검색
    • 로그에 저장된 트랜잭션 T의 로그 레코드에서 <T commit>이 저장되었으면 갱신 기록에 대해 REDO 수행
    • <T commit>이 없는 나머지 트랜잭션에 대한 로그 레코드는 무시
  •  즉시 갱신: 트랜잭션의 수행시 버퍼에 내용을 쓰자마자 OS에게 요청하여 데이터베이스 디스크에 작성하는 것지연 갱신 기법에서는 트랜잭션이 실패하면 절대 변경된 데이터가 데이터 베이스에 반영이 되지 않기 때문에 UNDO연산이 필요하지 않지만 즉시 갱신 기법은 트랜잭션이 실패하더라도 중간에 write연산이 껴 있다면 결과가 반영이 될 수 있기 때문에  UNDO/REDO 알고리즘이 사용된다.
  • <복구 방법> 
    • 로그 파일이 기록된 마지막부터 반대방향으로 순차적으로 검색
    • 로그에 저장된 각 트랜잭션 T의 로그 레코드에 대해서 <T start>가 있으나 <T commit>이 없으면 UNDO 연산 수행
    • 로그 파일에 처음 혹은 검사점에 도달했으면 반대방향으로 순차적으로 검색
    • 로그에 저장된 각 트랜잭션의 로그에 대해 <T commit>이 저장되었으면 REDO 연산 수행 

검사점을 이용한 복구

로그의 크기가 커질 수록 복구 부담이 증가 하고 그에 따라 불필요한 REDO 연산이 다량 발생하기 때문에 주기적으로 디스크에 강제적으로 쓰는 작업(검사점)을 한다.

검사점 작업(즉시 갱신만을 가정)

• 모든 트랜잭션의 실행 중단
• 갱신된 모든 버퍼의 내용을 디스크로 출력
• 로그에 <checkpoint> 레코드 저장
• 중단된 트랜잭션을 다시 실행

잠금(locking)

트랜잭션의 실행 순서를 강제로 제어 하여 하나의 트랜잭션이 수행하는 동안 특정 데이터 항목에 대해 다른 트랜잭션이 동시에 접근하지 못하도록 방지 하는 것 lock이 걸린 데이터는 lock을 실행한 트랜잭션만 독점적으로 접근 가능

• 비관적인 방법: 동시에 실행해도 괜찮은 작업도 동시에 실행하지 못 하게 막을 수도 있다.
• 사용 연산: lock, unlock
• 잠금의 종류
  • (s-lock)공유잠금: 데이터는 read 하는 연산시 하는 잠금 두 트렌젝션이 요청시 동시에 제공 가능
  • (x-lock)배타잠금: 데이터를 write 하는 연산시 하는 잠금 두 트랜젝션이 요청시 동시에 제공 불가능
• 한계: 아무 조건 없이 lock, unlock 연산을 사용한다면 직렬 가능한 스케줄을 완벽히 보장 할 수 없음 다음 사례와 같이 OS가 연산의 순서를 다르게 할 경우 결과가 달라질 수 있기 때문

• 대안책 2Pase Locking: lock, unlock 연산을 확장 단계 축소 단계로 나누어서 확장단계일 때는 lock만 축소단계일때는 unlock 연산만 사용 할 수 있게끔 하여 직렬 가능한 스케줄을 보장한다. (단 교착 상태는 방지 불가능 하여 발생시 하나의 트랜젝션 작업을 죽여버린다.)

타임스탬프(timestamp)

최대한 병행 수행을 보장하며 직렬 가능한 스케줄에 위배될 가능성이 있으면 실행 취소하는 기법
타임스탬프는 트랜잭션이 시작된 시점을 나타내며, 각 트랜잭션에 고유한 타임스탬프를 부여 이는 데이터베이스에서 레코드를 읽거나 쓸 때마다 해당 레코드에 대한 타임스탬프를 업데이트하며, 이를 통해 트랜잭션의 실행 순서를 결정하여 직렬 가능한 스케줄을 보

• 낙관적인 방법: 가급적 전부 실행 시키되 결과가 직렬 스케쥴을 위배했다면 rollback 하는 방식
• 타임스탬프 프로토콜 규칙
  • 1. Thomas Write Rule: 더 이전의 타임스탬프를 가진 트랜잭션에 의해 작성된 레코드를 더 늦은 타임스탬프를 가진 트랜잭션이 덮어쓰려고 하면, 이를 무시합니다. 이로 인해 더 이전의 변경 사항이 무효화되는 것을 방지합니다.
    2. Wait-Die and Wound-Wait Schemes: 이는 타임스탬프가 충돌하는 트랜잭션에 대해 어떻게 처리할지를 결정합니다. Wait-Die 스키마에서는 더 이전의 타임스탬프를 가진 트랜잭션을 기다리게 하고, Wound-Wait 스키마에서는 더 이전의 타임스탬프를 가진 트랜잭션을 중단시킵니다.

• 다중 사용자 DBMS에서 데이터 고립성을 지키기 위해 필요한 기법

트랜잭션에서의 연산

• x: 테이블, 레코드, 필드 등 데이터베이스를 구성하는 임의의 구성 요소
• read(x): 이름이 x인 데이터베이스 항목을 트랜잭션의 지역변수 x를 읽어 들인다. SQL select 연산
• write(x): 지역변수 x에 저장된 값을 데이터베이스 항목 x에 저장한다. SQL update 연산

동시성 제어가 필요한 이유

운영체제의 프로세스에 자원 할당 과정에서 트랜잭션 명령들 간의 선점이 가능하다. 이때 선점방식은 서로간의 간섭에 의해서 잘못된 데이터를 생성할 수 있기 때문에 동시성 제어가 필요하다.

끼어들기로 인한 문제

• 갱신 분실: 트랜잭션 A에 의해 수행된 갱신이 트랜잭션 B에 의해 사라진 것
  • Uncommitted Dependency (Dirty Read): 트랜잭션 A가 데이터 항목 X를 업데이트하고, 그 변경이 아직 커밋되지 않은 상태에서 트랜잭션 B가 같은 데이터 항목 X를 읽는 경우
  • Inconsistent Analysis (Non-repeatable Read): 트랜잭션 A가 데이터 항목 X를 읽은 후, 트랜잭션 B가 같은 데이터 항목 X를 업데이트하고 커밋하는 경우
• 연쇄 복귀: 트랜잭션의 실패로 인해 다른 트랜잭션들이 롤백되어야 하는 상황
• 불일치 분석: 선점으로 인해 트랜잭션의 일관성이 유지되지 못하는 상황

선점으로 인한 문제점 해결 방법

• 트랜잭션들을 비선점형으로 순차적으로 실행(가장 단순한 방법)
  • 단점: OS 활용률이 낮아짐
• 선점을 최대한 허용하면서 직렬 스케줄과 동일한 결과를 갖도록 실행 순서 제어
  • 직렬 가능한 스케줄: 선점을 최대한 허용하면서 한 결과가 순서대로 작업한 결과와 같은 스케줄

직렬 가능한 스케줄인지 판단하는 방법

스케줄에 나타난 연산들의 순서를 전체적인 실행 결과에 영향을 미치지 않도록 교환 이 때 주어진 스케줄이 직렬 스케줄로 변환되면 직렬 가능한 스케줄이다.

트랜잭션

• 데이터베이스를 접근하고 처리하는 기본 단위
• 실행 중 멈추거나 중단되지 않는 최소 작업 단위 (원자성)
  • All or Nothing
  • 트랜잭션이 성공하여 반영되거나 실패하여 아무것도 반영되지 않거나 둘 중 한가지 결과만이 나올 수 있다. 
• 성공하던 실패하던 값을 (DBMS가) 보장 해야한다.
• 트랜잭션 실행 전후 데이터베이스 내용이 일관되어야 한다 (일관성)
  • 일관성의 정의는 모호함으로 프로그래머가 제대로된 정의를 해준 것에 따른다.
• 트랜잭션이 실행하는 과정에서 갱신한 데이터는 트랜잭션이 완료될 때까지 다른 트랜잭션이 참조할 수 없다 (고립성)
  • 트랜잭션을 동시에 실행하면서 상호간에 간섭이 일어나지 않도록 하는 기법이 필요함(동시성 제어)
• 트랜잭션이 완료되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장되어야 한다 (지속성)

데이터 베이스 입출력 원리

• 운영체제의 I/O 버퍼 관리를 통해 작업이 완료된다.
• 입출력 작업에는 I/O 버퍼를 거친다.
  • 플러그 비트를 보고 판단하여 입력 여부를 판단
• 로그인: 사용자용 개인 user Memory 할당
• 디스크에 작성: I/O 버퍼에 들어간 블럭을 디스크로 옮긴다.
  • 언제 작업이 완료되었는지는 알 수 없음 그렇기에 데이터 베이스에 저장된 값이 무엇인지 알 수 없다.
• 사용자 입력 → 메모리 → I/O 버퍼에 → 파일 디스크
  • 이후 파일 디스크 정보 말고는 휘발성이라 소멸된다.

데이터 베이스에서 동일한 업무가 동시에 진행될 경우

• DBMS가 해당 문제 해결
  • 락: DBMS는 해당 작업이 끝날 때까지 데이터에 락을 걸어 다른 트랜잭션이 동시에 접근하는 것을 막는다
  • 직렬화: 트랜잭션들이 순차적으로 실행되도록 하여 동시에 발생할 수 있는 문제를 막는다.
  • 최적화된 동시성 제어 기법: MVCC(Multi-Version Concurrency Control)와 같은 기법은 각 트랜잭션에 대해 데이터의 특정 버전을 제공하여 동시성 문제를 해결

트랜잭션의 상태

• 동작: 트랜잭션이 시작되고 연산들이 정상적으로 실행 중인 상태
• 부분완료: 트랜잭션에 정의된 모든 연산의 실행이 끝난 상태
• 완료: 트랜잭션이 성공적으로 종료된 상태 All
• 실패: 트랜잭션이 완료되지 못하고 더 이상 실행되지 못하는 상태
• 중단: 트랜잭션이 실패 한 후 실행되기 이전으로 복귀된 상태 Nothing

• 삭제이상(deletion anomaly) 삽입이상(insertion anomaly) 갱신이상(update anomaly)를 해결 하기 위한 방법
• 데이터의 중복을 줄여서 바람직한 테이블로 만드는 과정
• 한가지 사실은 한 곳에서만 나타난다는 원리를 형시화한 것
• 일반적으로 제3 정규형을 만족하면 그 이후의 정규형의 조건은 자연스럽게 만족 하게 된다.
• 과정은 가역적이다 (2NF에서 3NF으로 바꾸었을 때 3NF에서 다시 2NF로 돌아갈 수 있는 과정)

함수 종속(FD: functional dependency)

• X → Y: 어떤 릴레이션 R에서, 애트리뷰트 X의 값 각각에 대해 애트리뷰트 Y의 값이 하나만 연관 된 것
  • X는 Y의 결정자(determinant)

무손실 분해

• 분해된 테이블을 자연조인 할 때 손실되는 데이터가 없는 분해 방법
• 가역성: 분해된 테이블을 조인 했을때 원래대로 돌아오는 경우
• 손실 판단 방법: 테이블 분해 후 함수적 종속성이 남아있는지 여부를 확인

정규화 과정

• 제1 정규형(1NF): 모든 속성이 원자값을 가지는 테이블
• 제2 정규형(2NF): 기본키가 아닌 모든 속성이 기본키에 완전히 종속되어야 한다. (목표: 부분적 함수 종속 제거)
  • ex: 학생ID와 과목코드로 이루어진 복합키가 있고, 이에 종속된 속성으로 성적이 있는 경우, 성적은 학생ID와 과목코드 모두에 종속되어야 한다. 만약 성적이 학생ID에만 종속되어 있다면, 이는 제2정규형을 만족하지 않는다.
• 제3 정규형(3NF): 모든 속성이 좌변 최소성으로 종속되어있어야 하고 상호 독립적이어야 한다. (목표: 이행적 함수 종속 제거)
  • 기본키가 아닌 다른 속성에 종속되는 속성이 없어야 한다.
  • ex: 학생ID가 기본키이고, 학생ID에 종속된 속성으로 학과명이 있고, 학과명에 종속된 속성으로 학과위치가 있다면, 이는 제3정규형을 만족하지 않는다.
• BCNF
• 제 4 정규형(4NF)
• 제 5 정규형(5NF)

FD(함수적 종속) 다이어그램

• 결정자와 종속자의 관계를 화살표로 나타낸 것
• 결장자가 기본키가 아니라면 중복이 존재하는 것
• 모든 다른 속성을 함수적으로 결정한다 → 후보키(기본키)

요구 사항과 ERD

개체 집합 (속성) 구분자

• 학생
  • 학번
  • 주민등록번호
  • 이름
  • 주소 학년
• 교수
  • 교수번호
  • 주민등록번호
  • 이름
  • 직급
  • 임용년도
• 학과
  • 학과번호
  • 학과명
  • 사무실
• 교과목
  • 과목번호
  • 교과목명
  • 학점수
• 강좌(기본키를 가지고 있지 않은 약성개체 집합)
  • 연도
  • 학기
  • 분반
  • 강의실
  • 수강인원

관계

• 소속
  • 학생 →학과 (Many to One)
    • 한 학생은 한 개의 학과에 속한다
    • 한 개의 학과에는 많은 학생들이 존재할 수 있다.
    • 학생이 없는 학과도 존재 가능
    • 학과가 없는 학생도 존재 가능
  • 교수 → 학과 (Many to One)
• 개설
  • 교과목 <= 강좌 (One to Many)
    • 교과목에 없는 강좌는 존재 할 수 없다.
    • 강좌가 없는 교과목은 존재 할 수 있다.
    • 한 강좌에 대응하는 교과목은 없거나 하나다
    • 한 교과목에 대응하는 강좌는 없거나 여러개가 있을 수 있다.
• 강의
  • 교수 ↔ 강좌 (One to One)
• 수강
  • 학생 - 강좌 (Many to Many)

설계 순서

• 방법1: 모든 개체집합들을 결정하고 다음으로 관계를 결정한다.
• 방법2: 요구사항에서 가장 중요하다고 판단되는 관계집합을 우선 결정

논리적 설계

• ERD로 부터 테이블 스키마 생성(변환)
• 논리적 설계 과정
  • 강성 개체집합을 관계형 테이블로 변환
  • 약성 개체집합을 관계형 테이블로 변환
  • 관계집합을 관계형 테이블로 변환
  • 중복되는 테이블 제거
  • 가능한 테이블들 결합

약성 개체집합의 변환

• 기본키가 존재하지 않는 약성 집합의 경우 혼자서는 테이블로 변환 하는 것이 불가능하다.
• 부분키와, 강성 개체 집합의 기본키를 요소로 가짐으로써 테이블로 변환 한다.

개체집합의 변환

• 각각의 관계되는 개체 집합의 기본키를 가지고 테이블로 변환된다.

자기연관 관계집합의 변환

• 동일한 기본키를 사용한다면 역활의 의미를 반영하여 기본키 명칭을 변경하여 포함시킨다.

관계 집합의 변환

• 일대다 관계 테이블: 일의 개체집합에 다의 기본키를 넣는 것으로 가능
• 다대다 관계 테이블: 결합이 불가능 함 따라서 그대로 스키마를 형성하는게 바람직
• 일대일 관계 테이블: 두 집합을 하나로 합치거나 한쪽에 한쪽의 기본키를 넣는 것으로 가능

일반화 관계의 변화

• 상위 개체집합의 유지
• 상위 개체집합 제거 후 하위 개체집합에 각각 포