[Lecture] DataBase - Finals Summary

💻 2023년 데이터베이스 기말고사 대비 요점 정리

👀 [07] 데이터베이스 언어 SQL

뷰의 개념

• 다른 테이블을 기반으로 만든 가상 테이블(virtual table)
• 뷰를 통한 기본 테이블의 내용 검색은 수월하지만 기본 테이블의 내용 변경 작업은 제한적임
  • 뷰를 만드는 데 기반이 되는 물리적인 테이블을 기본 테이블(base table) 이라고 함
• 다른 뷰를 기반으로 새로운 뷰 생성 가능

뷰 생성 - create view

create view view_name(attribute_list)
as select ...
[with check option];

• 뷰 생성의 기본 형식
• 뷰를 구성하는 속성 리스트를 생략 시 select 절에 나열된 속성의 이름을 뷰에서 그대로 사용
  • order by 를 사용할 수 없다는 점 제외하고 일반 select 문과 동일
• with check option : 뷰의 정의 조건을 위반하는 삽입 및 수정, 삭제 연산 시도 시 실행 거부

create view Vip_customer
as select id, name, age, class
from Customer
where class='vip'
with check option;

• 고객 테이블에서 등급이 vip인 고객의 고객아이디, 고객이름, 나이, 등급으로 구성된 뷰를 우수고객이라는 이름으로 생성

create view Product_count
as select manufacturer, count(*) as cnt
from Product
group by manufacturer
with check option;

• 제품 테이블에서 제조업체별 제품수로 구성된 뷰를 업체별제품수라는 이름으로 생성

뷰 활용

select * from Vip_customer
where age>=20;

• 우수고객 뷰에서 나이가 20세 이상인 고객에 대한 모든 내용 검색
• 뷰에 대한 select 문이 내부적으로 기본 테이블에 대한 select 문으로 변환되어 수행됨

create view Product_1
as select id, inventory, manufacturer
from Product
with check option;

• 제품에 대한 제품1 뷰 생성

create view Product_2
as select name, inventory, manufacturer
from Product
with check option;

• 제품에 대한 제품2 뷰 생성

insert into Product_1
values ('p08', 1000, '신선식품');

• 새로운 제품의 정보를 제품1 뷰에 삽입
• 뷰를 통해 값을 제시하지 않은 속성에는 널 값이 저장됨

insert into Product_2
values ('시원냉면', 1000, '신선식품');

• 제품2 뷰에 대한 삽입 연산이 실패함
• 제품2 뷰는 제품 테이블의 기본키인 제품번호 속성이 없음
• 뷰에 대한 삽입﹒수정﹒삭제 연산은 결과적으로 기본 테이블의 내용을 바꾸기 때문에 주의해서 사용

변경 불가한 뷰의 주요 특징

• 기본 테이블의 기본키를 구성하는 속성이 포함되어 있지 않은 뷰는 변경 불가
  • 기본 테이블에서 not null 지정 속성이 포함되어 있지 않은 뷰는 변경할 수 없는 경우가 있음
• 기본 테이블에 있던 내용이 아닌 집계 함수로 새로 계산된 내용을 포함하고 있는 뷰는 변경 불가
• distinct 키워드를 포함하여 정의한 뷰는 변경 불가
• group by 절을 포함하여 정의한 뷰는 변경 불가
• 여러 개의 테이블을 조인하여 정의한 뷰는 변경할 수 없는 경우가 많음

뷰의 대표적인 장점

• 질의문을 좀 더 쉽게 작성 가능
• 데이터의 보안 유지에 도움이 됨
• 데이터를 좀 더 편리하게 관리 가능

뷰 삭제 - drop view

drop view view_name;

• 뷰 삭제의 기본 형식

drop view Vip_customer;

• 우수고객 뷰 삭제
• 삭제할 뷰를 참조하는 제약조건이 존재한다면 제약조건을 먼저 삭제해야지 뷰 삭제가 가능함

👀 [08] 데이터베이스 설계

데이터베이스 설계 단계

• 데이터베이스 설계 : 사용자들의 요구 사항을 고려하여 데이터베이스를 생성하는 과정
• E-R 모델과 릴레이션 변환 규칙을 이용한 설계
  • 1단계 : 요구 사항 분석
    • 데이터베이스 용도 파악
      • 데이터베이스를 실제로 사용할 주요 사용자의 범위 결정
      • 사용자가 조직에서 수행하는 업무 분석
      • 면담, 설문지 배포, 업무 관련 문서의 분석 등의 방법을 이용해 요구 사항 수집
      • 수집된 요구 사항에 대한 분석 결과를 요구 사항 명세서로 문서화
    • 결과물 : 요구 사항 명세서
  • 2단계 : 개념적 설계
    • DBMS에 독립적인 개념적 구조 설계
      • 요구 사항 분석 결과를 기반으로 중요한 개체를 추출한 후에 개체 간의 관계를 결정하여 이를 E-R 다이어그램으로 표현
    • 결과물 : 개념적 스키마
  • 3단계 : 논리적 설계
    • DBMS에 적합한 논리적 구조 설계
      • 개념적 설계 단계의 결과물을 릴레이션 스키마로 변환
        • 변환 규칙
          • (1) 모든 개체 릴레이션으로 변환
          • (2) n:m 관계 릴레이션으로 변환
          • (3) 1:n 관계 외래키 표현
            • 일반적인 관계 외래키 표현
            • 약한 개체가 참여하는 관계는 외래키 포함 기본키 지정
          • (4) 1:1 관계 외래키 표현
            • 일반적인 관계 외래키 서로 주고 받음
            • 관계에 필수적으로 참여하는 개체의 릴레이션만 외래키를 받음
            • 모든 개체가 필수적 참여일 시 릴레이션 하나로 합침
          • (5) 다중 값 속성 릴레이션 변환
          • 속성이 많은 관계는 관계 유형에 상관없이 릴레이션으로 변환하는 것 고려 가능
      • 테이블 명세서(릴레이션 스키마에 대한 설계 정보를 기술한 문서) 작성
    • 결과물 : 논리적 스키마
  • 4단계 : 물리적 설계
    • DBMS로 구현 가능한 물리적 구조 설계
    • 결과물 : 물리적 스키마
  • 5단계 : 구현
    • SQL 문을 작성한 후 이를 DBMS에서 실행하여 데이터베이스 생성
  • 1~3단계가 핵심 단계

👀 [09] 정규화

정규화의 개념

• 정규화(normalization) : 함수적 종속성을 이용해, 릴레이션을 연관성이 있는 속성들로만 구성되도록 분해하여 이상 현상이 발생하지 않는 올바른 릴레이션으로 만들어 나가는 과정
  • 함수적 종속성(FD; Functional Dependency) : 속성들 간의 관련성
  • 이상(anomaly) 현상 : 불필요한 데이터 중복이 발생하여 릴레이션에 대한 데이터의 삽입, 수정, 삭제 연산을 수행할 때 발생할 수 있는 부작용

이상 현상의 종류

• 삽입 이상(insertion anomaly) : 새 데이터를 삽입하기 위해 불필요한 데이터도 함께 삽입해야 하는 문제
• 갱신 이상(update anomaly) : 중복 튜플 중 일부만 변경하여 데이터가 불일치하게 되는 모순의 문제
• 삭제 이상(deletion anomaly) : 튜플을 삭제하면 꼭 필요한 데이터까지 함께 삭제되는 데이터 손실의 문제

정규화 과정 정리

• 

👀 [10] 회복과 병행 제어

트랜잭션의 개념

• 트랜잭션(transaction) : 작업 하나를 수행하는 데 필요한 데이터베이스의 연산들을 모아놓은 것

트랜잭션의 특성

• ACID
  • 원자성(Atomicity) : 트랜잭션을 구성하는 연산들이 모두 정상적으로 실행되거나 하나도 실행되지 않아야 함
    • 회복 기능
  • 일관성(Consistency) : 트랜잭션이 성공적으로 수행된 후에도 데이터베이스가 일관된 상태를 유지해야 함
    • 병행 제어 기능
  • 격리성(Isolation) : 수행 중인 트랜잭션이 완료될 때까지 트랜잭션이 생성한 중간 연산 결과에 다른 트랜잭션들이 접근할 수 없음
    • 병행 제어 기능
  • 지속성(Durability) : 트랜잭션이 성공적으로 완료된 후 데이터베이스에 반영한 수행 결과는 어떠한 경우에도 손실되지 않고 영구적이어야 함
    • 회복 기능

트랜잭션의 연산

• commit : 트랜잭션이 성공적으로 수행되었음을 선언
  • 작업 완료
• rollback : 트랜잭션을 수행하는 데 실패했음을 선언
  • 작업 취소

트랜잭션의 상태

• 활동(active) : 트랜잭션이 수행되기 시작되어 현재 수행 중인 상태
• 부분 완료(partially committed) : 트랜잭션의 마지막 연산이 실행된 직후의 상태
• 완료(committed) : 트랜잭션이 성공적으로 완료되어 commit 연산을 실행한 상태
• 실패(failed) : 장애가 발생하여 트랜잭션의 수행이 중단된 상태
• 철회(aborted) : 트랜잭션을 수행하는 데 실패하여 rollback 연산을 실행한 상태

회복

• 회복(recovery) : 데이터베이스에 장애가 발생했을 때 장애가 발생하기 전의 모순이 없고 일관된 상태로 복구시키는 것
• 회복의 핵심 원리는 데이터 중복
  • 덤프(dump) : 데이터베이스 전체를 다른 저장 장치에 주기적으로 복사하는 방법
  • 로그(log) : 데이터베이스에서 변경 연산이 실행될 때마다 데이터를 변경하기 이전 값과 변경한 이후의 값을 별도의 파일에 기록하는 방법
    • redo : 재실행
    • undo : 취소

회복 기법의 유형

• 로그 회복 기법 : 로그를 이용한 회복
  • 즉시 갱신 회복 : 트랜잭션을 수행하는 도중에 데이터 변경 연산의 결과를 데이터베이스에 즉시 반영
  • 지연 갱신 회복 : 트랜잭션이 부분 완료되면 데이터 변경 연산의 결과를 데이터베이스에 한 번에 반영
• 검사 시점 회복 기법 : 검사 시점을 이용한 회복
• 미디어 회복 기법 : 데이터베이스 덤프를 이용한 회복

병행 수행과 병행 제어

• 병행 수행 : 여러 개의 트랜잭션을 동시에 수행하는 것
  • 갱신 분실, 모순성, 연쇄 복귀의 문제가 있음
• 병행 제어 : 병행 수행 시 문제가 발생하지 않고 정확한 결과를 얻을 수 있도록 트랜잭션의 수행을 제어하는 것
  • 목적 : 시스템 활용도 최대화, 사용자에 대한 응답시간 최소화, 데이터베이스 일관성 유지
  • 기법 : 로킹 기법, 타임 스탬프 기법, 다중 버전 기법

로킹 기법

• 로킹(locking) 기법 : 한 트랜잭션이 먼저 접근한 데이터에 대한 연산을 모두 마칠 때까지, 해당 데이터에 다른 트랜잭션이 접근하지 못하도록 상호 배제(mutual exclusion) 하여 직렬 가능성을 보장하는 것
  • lock : 트랜잭션이 사용할 데이터에 대한 독점권을 가지기 위해 사용
  • unlock : 트랜잭션이 데이터에 대한 독점권을 반납하기 위해 사용
• 로킹 단위
  • lock 연산을 실행하는 대상 데이터의 크기
  • 전체 데이터베이스부터 작게는 속성, 릴레이션이나 튜플까지도 가능함
  • 로킹 단위가 커질수록 병행성은 낮아지지만 제어가 쉬움
  • 로킹 단위가 작아질수록 제어가 어렵지만 병행성은 높아짐

👀 [11] 보안과 권한 관리

보안

• 데이터베이스 보안의 목표 : 조직에서 허가한 사용자만 데이터베이스에 접근할 수 있도록 통제하여 보안을 유지하는 것
• 데이터베이스 보안
  • 물리적 환경에 대한 보안(자연 재해 등으로부터 보호)
  • 권한 관리를 통한 보안(권한이 없는 사용자로부터 보호)
  • 운영 관리를 통한 보안(권한이 있는 사용자로부터 보호)

권한의 부여 - grant

grant 권한 on 객체 to 사용자 [with grant option];

• 권한 부여의 기본 형식
• 객체의 소유자가 다른 사용자에게 객체에 대한 사용 권한을 부여
• 부여받은 권한은 기본적으로 다른 사용자에게 부여 불가능
• with grant option : 사용자가 자신이 부여받은 권한을 다른 사용자에게도 부여 가능

grant select on Customer to f1r3_r41n;

• 고객 테이블에 대한 검색 권한을 사용자 f1r3_r41n에게 부여

grant insert, delete on Customer to public;

• 고객 테이블에 대한 삽입과 삭제 권한을 모든 사용자에게 부여
• public : 모든 사용자에게 권한을 똑같이 부여

grant update(class, reserves) on Customer to f1r3_r41n;

• 고객 테이블을 구성하는 속성 중 등급과 적립금 속성에 대한 수정 권한을 사용자 f1r3_r41n에게 부여

grant select on Customer to f1r3_r41n with grant option;

• 고객 테이블에 대한 검색 권한을 with grant option 을 포함하여 사용자 f1r3_r41n에게 부여

grant create table to f1r3_r41n;

• 테이블을 생성할 수 있는 시스템 권한을 사용자 f1r3_r41n에게 부여

grant create view to f1r3_r41n;

• 뷰를 생성할 수 있는 시스템 권한을 사용자 f1r3_r41n에게 부여

권한의 취소 - revoke

revoke 권한 on 객체 from 사용자 cascade|restrict;

• 권한 취소의 기본 형식
• 객체 소유자가 다른 사용자에게 부여된 객체의 사용 권한을 취소
• restrict : 권한을 취소할 사용자가 권한을 부여한 사용자의 사용 권한은 취소하지 않음
• cascade : 연관된 다른 사용자들의 권한도 함께 취소

revoke select on Customer from lee cascade;

• f1r3_r41n이 lee에게 부여한 고객 테이블에 대한 검색 권한을 취소하면서 lee가 다른 사용자에게 부여한 고객 테이블에 대한 검색 권한도 함께 취소함

revoke select on Customer from lee restrict;

• lee가 다른 사용자에게 권한을 부여한 적이 없는 경우에만 f1r3_r41n이 lee에게 부여한 고객 테이블에 대한 검색 권한을 취소함

revoke create table from lee;

• lee에게 부여한 테이블 생성 권한 취소

역할 생성 - create role

create role role_name;

• 역할 생성의 기본 형식
• 역할(role) : 여러 권한을 그룹으로 묶어놓은 것
• 사용자들에게 부여하고 싶은 여러 권한을 역할에 미리 넣어두고 필요할 때 역할을 부여하면 여러 권한을 한 번에 쉽게 부여 가능
  • 사용자에게 부여한 역할을 취소하면 한 번에 여러 권한 취소 가능
• 권한 관리가 쉬워짐
• 데이터베이스 관리자 담당

create role role_1;

• role_1이라는 이름의 역할 생성

역할에 권한 추가 - grant

grant 권한 on 객체 to 롤이름;

• 역할에 권한 추가의 기본 형식
• 객체의 소유자 담당

grant select, insert, delete on Customer to role_1;

• 고객 테이블에 대한 검색﹒삽입﹒삭제 권한을 role_1 역할에 할당

역할 부여 - grant

grant 롤이름 to 사용자;

• 역할 부여의 기본 형식
• 데이터베이스 관리자 담당

grant role_1 to f1r3_r41n;

• 고객 테이블에 대한 검색﹒삽입﹒삭제 권한을 포함하고 있는 role_1 역할을 사용자 f1r3_r41n에게 부여

역할 취소 - revoke

revoke 롤이름 from 사용자;

• 역할 취소의 기본 형식
• 데이터베이스 관리자 담당

revoke role_1 from f1r3_r41n;

• 사용자 f1r3_r41n에게 부여한 role_1 역할 취소

역할 제거 - drop role

drop role 롤이름;

• 역할 제거의 기본 형식
• 데이터베이스 관리자 담당

drop role role_1;

• role_1 역할 제거