본문 바로가기
  • 適者生存
WorkOut/정보처리기사

SQL 문제 풀이 | 데이터베이스 기본

by lcrvvxln 2024. 4. 11.

적자생존

 

7. SQL 응용 

Chapter 01. 데이터베이스 기본

 

# 01 - 20년 1회

트랜잭션의 특성 중 일관성, 지속성 외 2개의 특성을 쓰시오.

 

 

A. 원자성, 격리성

원자성 : 트랜잭션 연산 전체가 모두 성공하거나 실패되어야 하는 성질

독립성 : 동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않아야 하는 성질 

# 트랜잭션의 특성 4가지
원자성, 일관성, 격리성(고립성), 영속성

※ ACID : Atomicity Consistency Isolation Durability 

1.원자성 
트랜잭션 구성 연산 전체가 모두 정상 실행되거나(=성공) 모두 취소(=실패)되어야 하는 성질
→ 회복성 보장

2. 일관성
시스템 고정요소는 트랜잭션 수행 전과 완료 후의 상태가 같아야 하는 성질
→ 무결성 제약 조건, 동시성 제어

3. 격리성 = 고립성
동시 실행 트랜잭션들이 서로 영향을 미치지 않아야 하는 성질 
→ 고립화 수준 (레벨 0,1,2,3)

4. 영속성
성공 완료된 트랜잭션 결과는 영속적으로 DB에 저장되는 성질
→ 회복 기법

 

해설.

원자성은 하나라도 실패할 경우, 전체가 취소되어야 하는 특성

격리성은 트랜잭션 실행 중, 생성 연산의 중간 결과를 다른 트랜잭션이 접근 불가한 특성

 

 

# 02 - 21년 2회 

트랜잭션 원자성에 대하여 설명하시오.

 

 

A. 트랜잭션 원자성이란 트랜잭션을 구성하는 연산 전체가 모두 정상 실행되거나 모두 취소되어야 하는 성질이다.

 

 

해설.

트랜잭션은 4가지 특성을 가지며, 원자성은 트랜잭션의 작업이 부분적으로 실행되거나 중단되지 않는 을 보장하는 성질이다.

 

1. 원자성
- 트랜잭션 구성 연산 전체가 모두 정상 실행되거나 모두 취소되어야 하는 성질 
- 트랜잭션 연산 전체가 모두 성공 또는 실패 (All or Nothing) 되어야 하는 성질

2. 일관성
- 시스템 고정요소는 트랜잭션 수행 전과 수행 완료 후 상태가 동일해야 하는 성질

3. 격리성 (=고립성)
- 동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않아야 하는 성질 

4. 영속성 
- 성공 완료된 트랜잭션 결과는 영속적으로 데이터베이스에 저장되어야 하는 성질

 

 

# 03 - 20년 2회

트랜잭션 Rollback에 대해 설명하시오.

 

 

A. 트랜잭션을 저장 무효화시키는 명령어 

# 트랜잭션 제어어 (TCL ; Transaction Control Language) - 3가지
COMMIT, ROLLBACK, CHECKPOINT

※ CLC / 커롤체 : COMMIT, ROLLBACK, CHECKPOINT / 커밋, 롤백, 체크포인트 

1. COMMIT - 트랜잭션 확정
트랜잭션을 메모리에 영구적으로 저장하는 명령어 

2. ROLLBACK - 트랜잭션 취소
트랜잭션 내역을 저장 무효화시키는 명령어 

3. CHECKPOINT - 저장 시기 설정
ROLLBACK을 위한 시점 지정 명령어 

 

해설.

롤백은 오류가 발생했을 때, 오류 이전특정 시점(SAVEPOINT, CHECKPOINT) 상태로 되돌려주는 제어어

커밋은 트랜잭션을 메모리에 영구적으로 저장하는 제어어

체크포인트는 롤백을 위한 시점을 지정하는 제어어

 

 

 

# 04 - 22년 1회 

다음에 해당하는 DB 트랜잭션 연산을 [보기]에서 찾아 적으시오.

 

① 장애 발생 전 DB로 복구하는 기법으로 디스크에 저장된 로그를 분석하여 트랜잭션의 시작(Start)과 완료(Commit)에 대한 기록이 있는 트랜잭션 작업을 재실행하는 기법 

② 장애 시 디스크에 저장된 로그를 분석하여 트랜잭션의 시작(Start)은 있지만, 완료(Commit) 기록이 없는 트랜잭션들이 작업한 변경 내용을 모두 취소하는 기법 

 

 

A. ① Redo ② Undo

 

# 회복 기법 주요 요소 (영속성 주요 기법) 2가지
트랜잭션 수행 중 장애로 인해 손상된 데이터베이스손상 이전정상적 상태복구시키는 작업

REDO / UNDO

1. REDO
- 데이터베이스가 비정상 종료되었을 때 디스크에 저장된 로그 분석해서 트랜잭션 시작(Start)과 완료(Commit) 기록이 있는 트랜잭션 작업을 재작업하는 기법
- 데이터베이스 내용 자체 손상 시, 가장 최근의 복제본 적재한 후 이후 일어난 변경만 로그 이용하여 재실행하여 데이터베이스 복원하는 기법

2. UNDO
- 데이터베이스 비정상 종료 시, 디스크 저장 로그 분석하여 트랜잭션 시작(Start)은 있지만 완료(Commit) 기록이 없는 트랜잭션들이 작업한 변경 내용 모두 취소하는 기법 
- 데이터베이스 내용 자체는 손상되지 않았으나 변경 중이거나 변경된 내용의 신뢰성을 잃은 경우 모든 변경 내용을 취소하여 복원하는 기법  

 

해설.

REDO는 데이터베이스가 비정상 종료되었을 때, 디스크 저장 로그 분석하여 트랜잭션 시작(Start)과 완료(Commit)에 대한 기록 있는 트랜잭션 재작업하는 기법UNDO는 데이터베이스가 비정상 종료되었을 때, 디스크 저장 로그 분석하여 트랜잭션 시작(Start)은 있으나 완료(Commit) 기록 없는 트랜잭션 작업 변경 내용 모두 취소하는 기법

 

 

# 05 - 21년 2회 

같은 자원을 액세스하는 다중 트랜잭션 환경에서 DB의 일관성과 무결성을 유지하기 위해 트랜잭션의 순차적 진행을 보장하는 직렬화 기법은 무엇인지 쓰시오.

 

 

A. 

# 병행 제어 (일관성 주요 기법) 
다수 사용자 환경에서 여러 트랜잭션 수행 시, 데이터베이스 일관성 유지를 위해 상호 작용 제어하는 기법

1. 로킹 
하나의 트랜잭션 실행 동안 특정 데이터 항목에 대해 다른 트랜잭션이 동시 접근하지 못하게 상호배제 기능 제공
▶ 데이터베이스, 파일, 레코드 등이 로킹 단위가 됨  
▶ 로킹 단위 작아지면 데이터베이스 공유도 증가
▶ 로킹 단위 작아지면 로킹 오버헤드 증가 
▶ 한 번에 로킹할 수 있는 객체 크기를 로킹 단위라고 함

2. 낙관적 검증 
검증 수행 없이 먼저 트랜잭션 수행하고, 종료 시 검증 수행하여 데이터베이스에 반영하는 기법

3. 타임 스탬프 순서 
트랜잭션과 트랜잭션이 읽거나 갱신한 데이터에 대해 트랜잭션 실행 시작 전 타임 스탬프 부여하고 부여된 시간에 따라 작업 수행하는 기법

4. 다중버전 동시성 제어 
트랜잭션 타임스탬프와 접근 데이터 타임스탬프 비교하여 직렬가능성 보장되는 버전 선택하여 접근하는 기법

5. 2PC
데이터베이스 동시성 제어 기술 중 하나
여러 개 분산 데이터베이스 시스템에서 트랜잭션 일관성 유지하기 위한 기법 
트랜잭션 2단계로 분리하여 제어 > 준비 단계 & 커밋 단계 
준비 단계 : 트랜잭션 수행 결과를 다른 분산 시스템에 알리는 과정 
커밋 단계 : 모든 분산 시스템에서 트랜잭션 수행 결과 일치하는지 확인하는 과정, 모든 분산 시스템이 트랜잭션 성공 수행 시, 커밋 수행하고 아니면 롤백 수행

 

해설.

로킹은 같은 자원 액세스하는 다중 트랜잭션 환경에서 DB 일관성무결성 유지를 위해 트랜잭션의 순차적 진행을 보장하는 직렬화 기법이다.

한 번에 로킹할 수 있는 객체 크기를 로킹 단위라고 하며 데이터베이스, 파일, 레코드 등이 로킹 단위가 될 수 있다. 로킹 단위가 작아지면 데이터베이스 공유도로킹 오버헤드증가한다.

 

 

# 06 - 20년 1회, 22년 3회

STUDENT 테이블에 컴퓨터과 학생 50명, 인터넷과 학생 100명, 사무자동화학과 학생 50명 정보가 저장되어 있을 때, 다음 SQL 문의 실행 결과의 튜플 수는?

(단, DEPT 칼럼은 학과명이다.)

 

① SELECT DEPT FROM SUTENDT;

② SELECT DISTINCE DEPT FROM STUDENT

③ SELECT COUNT(DISTINCT DEPT) FROM STUDENT WHERE DEPT = '컴퓨터과'

 

 

A. ① 200   ② 3   ③ 1

 

해설.

① 단순 SELECT(조건 검색)는 전체 테이블 튜플을 검색하기 때문에 200건(=50+100+50) 조회

② DISTINCT(중복 제거)는 동일 튜플을 제거하기 때문에 학과명을 기준으로 중복 제거 했으므로 컴퓨터과 1건, 인터넷과 1건, 사무자동화학과 1건 총 3건 조회

③ GROUP BY 없을 경우, 전체 테이블에서 튜플 개수를 COUNT 하기 때문에 WHERE 절에 따라 컴퓨터과인 튜플 중 학과 기준 중복 제거할 경우, 컴퓨터과 1건이 조회된 출력값 행 1개가 조회 

 

 

# 07 - 20년 2회 

학생 테이블은 학번(VARCHAR), 이름(VARCHAR), 학년(NUMBER), 수강과목(VARCHAR), 점수(NUMBER), 연락처(VARCHAR)를 속성으로 가진다. 아래 조건을 만족하는 SQL문을 작성하시오.

 

1) 학생 테이블에서 3, 4학년인 학번, 이름을 조회한다.

2) IN 연산자 사용해야 한다. 

 

[ 학생 테이블 ]

학번 이름 학년 수강과목 점수 연락처
1000 김정미 1 알고리즘 90 010-1111-2222
2000 강은미 2 데이터베이스 95 010-2222-2222
3000 홍길동 3 전산수학 90 010-3333-3333
4000 장길산 4 운영체제 95 010-4444-4444

 

A.

SELECT 학번, 이름
  FROM 학생 
 WEHRE 학년 IN (3,4)

 

해설.

WHERE 절에서 IN은

컬럼 IN (값1, 값2, ...) 형태로 작성 

▶ 컬럼이 IN 안에 포함된 경우의 데이터만 조회 

 

 

# 08 - 20년 4회

데이터베이스의 회복(Recovery) 기법 중 Rollback 시 Redo, Undo가 모두 실행되는 트랜잭션 처리법으로 트랜잭션 수행 중 갱신 결과를 바로 DB에 반영하는 기법은 무엇인가? 

 

 

A. 로그 기반 회복 기법 - 즉각 갱신 회복 기법 

 

# 회복 기법 종류 - 3가지

※ 회로체그 : 회복기법 - 로그 기반 / 체크 포인트 / 그림자 페이징 

1. 로그 기반 회복 기법
지연 갱신 회복 기법과 즉각 갱신 회복 기법 
지연 갱신 회복 기법 : 트랜잭션 완료 전까지 데이터베이스 기록하지 않는 기법
즉각 갱신 회복 기법 : 트랜잭션 수행 중 갱신 결과를 바로 DB에 반영하는 기법 

2. 체크 포인트 회복 기법 
장애 발생 시, 검사점 이후에 처리된 트랜잭션에 대해서만 장애 발생 이전 상태로 복원시키는 회복 기법 

3. 그림자 페이징 회복 기법 
데이터베이스 트랜잭션 수행 시 복제본 생성하여 데이터베이스 장애 시 이를 이용해 복구하는 기법 

 

해설.

회복 기법 중 로그 기반 회복 기법에는 지연 갱신 회복 기법즉각 갱신 회복 기법이 있으며,트랜잭션 수행 중 갱신 결과바로 DB에 반영하는 기법은 즉각 갱신 회복 기법이다.

 

 

# 09 - 20년 2회 

STUDENT 테이블의 NAME 속성에 IDX_NAME 이름으로 인덱스 생성하는 SQL문을 작성하시오.

 

[ STUDENT 테이블 ]

STID NAME SCORE DEPTID
1000 김정미 90 1
2000 강은미 95 2
3000 홍길동 90 3
4000 장길산 95 4

 

 

A.

CREATE INDEX IDX_NAME ON STUDENT(NAME);

 

해설.

CREATE INDEX 문법 

CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, ...);

 

# INDEX 관련 DDL (DDL ; Data Definition Language) - 3가지

1. CREATE INDEX -인덱스 생성 명령
CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, ...);

2. ALTER INDEX - 인덱스 수정 명령
ALTER [UNIZUE] INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, ...);

3. DROP INDEX - 인덱스 삭제 명령
DROP INDEX 인덱스명;

 

 

#10 - 20년 3회

학생 테이블에서 이름이 민수인 튜플을 삭제하는 SQL문을 작성하시오.

 

[ 학생 테이블 ]

학번 이름 점수 과목이름
1000 김정미 90 알고리즘
2000 강은미 95 데이터베이스
3000 홍길동 90 전산수학
4000 민수 95 운영체제

 

A.

DELETE FROM 학생
 WHERE 이름=민수;

 

해설.

튜플 삭제 시, DELETE 명령어 사용

DELETE FROM 테이블명
 WHERE 조건;

 

# DELETE (데이터 삭제) 명령어 - 데이터 조작어 (DML; Data Manipulation Language)
 
DELETE는 데이터 내용 삭제 시 사용하는 명령어 

DELETE FROM 테이블명
WHERE 조건;

▶ 모든 레코드 삭제 시, WHERE절 없이 DELTE만 
▶ 레코드 삭제해도 테이블 구조는 남아 있어 테이블을 완전히 삭제하는 DROP과 다름

 

 

# 11 - 21년 3회

다음이 설명하는 용어를 쓰시오.

  • (      )은/는 데이터베이스에서 (키값, 주소) 형태의 자료구조이다.
  • 데이터베이스 파일 구조에는 순차, (    ), 해싱 접근 방법이 있다.

 

A. 인덱스

 

# 데이터베이스 파일 구조 - 3가지

※ 순인해 : 순차, 인덱스, 해싱

1. 순차 방법
레코드들의 물리적 순서와 논리적 순서가 같게 순차적으로 저장하는 방법

2. 인덱스 방법
인덱스가 가리키는 주소를 따라 원하는 레코드접근할 수 있도록 하는 방법
< 키값, 주소 > 쌍으로 구성

- 인덱스
검색 연산 최적화를 위해 데이터베이스 내 에 대한 주소 정보로 구성된 데이터 구조 


▶ 데이터를 빠르게 찾는 수단으로 테이블 조회 속도를 높이는 자료 구조 
▶ 테이블 특정 레코드 위치 알려주는 용도 

3. 해싱 방법
키값을 해시 함수에 대입시켜 계산한 결과를 주소로 사용하여 레코드에 접근하는 방법

 

 

해설.

인덱스는 검색 연산 최적화를 위해 데이터베이스 내 에 대한 주소 정보로 구성된 데이터 구조

 

데이터베이스 파일 구조는 3가지인데

순차방법은 레코드들의 물리적 순서가 레코드들의 논리적 순서와 같게 순차적으로 저장하는 방법이다.

인덱스 방법은 인덱스가 가리키는 주소를 따라 원하는 레코드에 접근할 수 있게 하는 방법으로 <키값, 주소> 쌍 구성이다.

해싱 방법은 키값을 해시 함수에 대입하여 계산한 결과를 주소로 사용하여 레코드에 접근하는 방법이다.

 

 

 

# 12 - 20년 3회 

DB 스키마에 대해 서술하시오.

 

 

A. 스키마는 데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본 구조로 외부, 개념, 내부 3계층으로 구성되어 있다. 외부는 사용자와 개발자 관점의 논리적 구조이며 사용자 뷰를 의미한다. 개념 스키마는 데이터베이스의 전체적인 논리적 구조로 전체적인 뷰를 의미하며 개체 간 관계, 제약 조건, 접근 권한, 무결성, 보안에 대해 정의한다. 내부 스키마는 물리적 저장 장치 관점의 데이터 구조로 실제 데이터베이스에 저장되는 레코드 형식을 정의하고 데이터 항목 표현 방법, 내부 레코드 물리적 순서 등을 표현한다. 

 

# 스키마 (Schema)
스키마는 데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본 구조

- 외부 / 개념 / 내부 3계층 구성

1. 외부 스키마
사용자개발자 관점에서 필요로 하는 데이터베이스 논리적 구조
사용자 뷰를 의미, 서브 스키마로 불림 

2. 개념 스키마
데이터베이스의 전체적논리적 구조
전체적인 뷰를 의미
개체 간 관계, 제약조건, 접근 권한, 무결성, 보안에 대해 정의 

3. 내부 스키마 
물리적 저장 장치 관점에서 보는 데이터베이스 구조 
실제 데이터베이스에 저장될 레코드 형식을 정의하고 저장 데이터 항목의 표현 방법, 내부 레코드의 물리적 순서 등을 표현

 

해설.

도메인하나의 속성이 가질 수 있는 원자값들의 집합으로 속성의 데이터 타입과 크기, 제약조건 등의 정보를 나타낸다.

스키마는 데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적 구조이다.

테이블데이터 저장 공간이다.는 하나 이상의 물리 테이블에서 유도되는 가상 테이블을 의미한다.인덱스검색을 빠르게 하기 위한 데이터 구조이다.

 

 

# 13 - 20년 3회

학생 테이블에 주소 속성을 추가하는 SQL 문을 작성하시오.

 

(  ①  ) TABLE 학생 (  ②  ) 주소 VARCHAR(20);

 

 

A.

① ALTER ② ADD

 

해설.

ALTER TABLE 컬럼 추가 문법

ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 [제약조건];

 

 

# ALTER - 데이터 정의어 (DDL; Data Definition Language)
ALTER은 테이블 수정 명령어

1. ALTER TABLE 컬럼 추가 
테이블에 컬럼 추가하는 문법
CREATE TABLE 제약조건 PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT 사용 가능 

ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 [제약조건];

2. ALTER TABLE 컬럼 수정
테이블 컬럼 수정하는 문법
CREATE 문 제약조건 명시 후에 ALTER 통해 제약조건 변경 가능 

ALTER TABLE 테이블명 ALTER 컬럼명 데이터타입 [제약조건];

3. ALTER TABLE 컬럼 삭제 
테이블 컬럼 삭제하는 문법

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;