5급 공채 2차

2022년 5급 공채 기술 2차 데이터베이스론

문제 5개 · 100점. 참고 답안·채점 기준은 앱에서 볼 수 있습니다.

제1문 (30점)

일반 대학교에서 재학생들의 수강신청 데이터를 저장할 수 있도록 기존 데이터베이스에 “수강신청”과 “수강신청내역” 테이블을 추가로 생성하려고 한다. 아래 제시된 <설계 고려사항>, <질의 빈도수 순위>, <이미 생성되어 있는 관련 테이블> 및 <새로 생성할 테이블>을 참고하여 물음에 답하시오. (단, 각 테이블의 기본키는 밑줄로 표시되어 있고, 기본키는 기본인덱스를 자동생성하는 것으로 가정한다) (총 30점)
<설계 고려사항>
○매 학기 이루어지는 수강신청 데이터를 해당 테이블에 누적해서 저장해야 한다.
○개설되는 강좌목록은 매 학기 변경될 수 있다.
○한 학생이 한 학기에 여러 강좌를 수강신청할 수 있다.
○한 학생은 여러 번에 걸쳐 신청할 수 있다.
○아래 질의 빈도수 순위를 고려해야 하고, 관련 테이블은 이미 생성되어 있다.

<질의 빈도수 순위>
①특정 강좌를 수강신청한 학생명단의 검색
②특정 연도와 학기에 수강 신청한 전체 학생명단 또는 총 학생 수 검색
③학생 자신이 해당 학기 수강신청한 강좌목록, 수강신청학점, 수강신청과목수의 검색
④학생이 현재까지 수강한 총 수강신청과목수와 수강신청학점의 검색

<이미 생성되어 있는 관련 테이블>
○학생(학번, 학생명, 주민등록번호, 학년, 입학년도, 전화번호, 주소)
○강좌(강좌코드, 강좌명, 학점, 시간수, 담당교수, 강의실코드)
○강의실(강의실코드, 강의실명, 강의실타입, 수용인원수, 면적, 강의실위치, 관리담당자)

<새로 생성할 테이블>
○수강신청:학생들이 해당 연도, 해당 학기에 신청한 수강신청 요약정보(수강신청과목수, 수강신청학점)를 저장한다. 테이블을 구성하는 컬럼은 “학번, 수강신청과목수, 수강신청학점, 수강신청학기, 수강신청연도”로 한정한다.
○수강신청내역:해당 연도, 해당 학기에 신청한 상세정보(강좌코드, 재수강여부)를 저장한다. 테이블을 구성하는 컬럼은 “강좌코드, 학번, 수강신청학기, 수강신청연도, 재수강여부”로 한정한다.
  1. (1) 앞에서 제시된 <설계 고려사항>, <질의 빈도수 순위>, <이미 생성되어 있는 관련 테이블>을 고려하여 <새로 생성할 테이블>을 생성하는 DDL문을 작성하시오. (단, 사용자 질의를 효율적으로 처리할 수 있도록 각각의 테이블에 대하여 기본키, 외래키 그리고 보조 인덱스를 갖도록 DDL문을 작성하고, 각 테이블의 컬럼 데이터 타입의 문자형은 VARCHAR 형을, 수치형은 NUMBER 형을 적용한다) (10점)
  2. (2) 1)의 DDL에서 “수강신청”, “수강신청내역” 테이블의 기본키 및 외래키 구성과 보조 인덱스 구성이 <질의 빈도수 순위>에 나타난 질의 예를 처리할 때 어떻게 작동하는지를 설명하시오. (12점)
  3. (3) 1)의 DDL을 통해 정의한 “수강신청내역” 테이블에서 기본키를 위한 인덱스로 B＋－트리 인덱스와 해시 인덱스 중 어느 것으로 구성해야 효율적인지 답하고, 그 이유를 설명하시오. (8점)

제2문 (15점)

데이터베이스의 인덱스 구조에 대한 다음 물음에 답하시오. (총 15점)
  1. (1) B－트리 인덱스와 B＋－트리 인덱스의 차이점을 키값의 저장 형태 측면과 릴레이션 레코드에 접근하는 측면에서 설명하시오. (5점)
  2. (2) B＋－트리 인덱스 구조와 해싱 인덱스 구조는 질의 형태에 따라 각각 장점을 가지고 있다. 두 가지 인덱스 구조의 장점을 SQL의 SELECT 문장을 예로 들어 비교하여 설명하시오. (5점)
  3. (3) 다음 B＋－트리에서 키 7을 삭제한 후의 B＋－트리를 그리시오. (단, B＋－트리의 차수는 4이다) (5점)
    [그림]

제3문 (20점)

릴레이션 R(A, B, C, D)에 대한 아래의 세 가지 물음 (a)～(c)를 주어진 함수종속 집합 F에 따라 각각 답하시오. (총 20점)
(a)R의 후보키를 모두 구하시오.
(b)R이 만족하는 최대 정규형을 판별하고 그 이유를 설명하시오. (단, 적용될 수 있는 정규형의 범위는 1NF, 2NF, 3NF, BCNF이다)
(c)R을 함수종속성 보존 BCNF로 분해하되, 할 수 없는 경우 그 이유를 설명하시오.
  1. (1) F＝{A→D, B→C} (6점)
  2. (2) F＝{A→B, A→C, BC→D} (6점)
  3. (3) F＝{AB→C, AB→D, C→A, D→B} (8점)

제4문 (15점)

데이터베이스 관리시스템에 장애가 발생하여 재시작되었을 때 디스크의 로그에 [Trans-ID, commit] 로그 레코드가 존재하면 해당 트랜잭션을 재수행하고, 존재하지 않으면 트랜잭션을 취소한다. 검사점(checkpoint)은 시스템 장애가 발생한 후 재시작되었을 때 재수행하거나 취소해야 하는 트랜잭션들의 수를 줄여준다. 회복작업에 대한 다음 물음에 답하시오. (총 15점)
  1. (1) 다음 그림과 같이 검사점을 사용하지 않았을 때, 각 트랜잭션(\mathrm{T}_{0}～\mathrm{T}_{6} \,)의 실행 상태와 필요한 회복작업에 대해 설명하시오. (7점)
    [그림]
  2. (2) 다음 그림과 같이 검사점을 사용했을 때, 각 트랜잭션(\mathrm{T}_{0}～\mathrm{T}_{6} \,)의 실행 상태와 필요한 회복작업에 대해 설명하시오. (8점)
    [그림]

제5문 (20점)

데이터베이스의 테이블 생성과 초기 데이터 삽입이 다음과 같다.
CREATE TABLE USER_BUCKET (
Fname VARCHAR(20),
Lname VARCHAR(20),
BucketID INTEGER DEFAULT 0);		CREATE TABLE BUCKET_COUNT (
BucketID INTEGER,
BucketCount INTEGER
);

INSERT INTO BUCKET_COUNT VALUES(0, 0);
INSERT INTO BUCKET_COUNT VALUES(1, 0);

이 데이터베이스에 대한 SQL-99 트리거를 다음과 같이 생성하였다.
CREATE TRIGGER USER_BEFORE_TRIGGER
BEFORE INSERT ON USER_BUCKET
REFERENCING NEW ROW AS NEWTUPLE
FOR EACH ROW
WHEN (NEWTUPLE.Fname LIKE ‘G%’)
SET NEWTUPLE.BucketID = 1;
CREATE TRIGGER USER_AFTER_TRIGGER
AFTER INSERT ON USER_BUCKET
REFERENCING NEW ROW AS NEWTUPLE
FOR EACH ROW
UPDATE BUCKET_COUNT
SET BucketCount = BucketCount + 1
WHERE BucketID = NEWTUPLE.BucketID;
CREATE TRIGGER USER_TABLE_TRIGGER
AFTER UPDATE OF Lname ON USER_BUCKET
REFERENCING
OLD TABLE AS OLDTHING
NEW TABLE AS NEWTHING
FOR EACH STATEMENT
UPDATE BUCKET_COUNT
SET BucketCount = (SELECT COUNT(*)
FROM OLDTHING);

이 데이터베이스에서 트리거의 조건 평가(condition evaluation)와 조치의 실행(action execution)은 데이터베이스의 상태를 변경시키는 트랜잭션과 같은 트랜잭션 내에서 상태변경 연산의 발생 즉시 실행되는 즉시 모드(immediate mode)로 작동한다고 가정할 때, 다음 물음에 답하시오. (총 20점)
  1. (1) 아래의 INSERT문을 수행한 후의 데이터베이스 상태를 보이시오. (5점)
    INSERT INTO USER_BUCKET(Fname, Lname) VALUES(‘Gildong’, ‘Hong’);
  2. (2) 아래의 INSERT문을 수행한 후의 데이터베이스 상태를 보이시오. (5점)
    INSERT INTO USER_BUCKET(Fname, Lname) VALUES(‘Soonmi’, ‘Kim’);
  3. (3) 아래의 SQL문들을 수행한 후의 데이터베이스 상태를 보이시오. (5점)
    BEGIN TRANSACTION
    INSERT INTO USER_BUCKET(Fname, Lname) VALUES(‘Bora’, ‘Lee’);
    INSERT INTO USER_BUCKET(Fname, Lname) VALUES(‘Gyungho’, ‘Park’);
    ROLLBACK;
  4. (4) 아래의 SQL문들을 수행한 후의 데이터베이스 상태를 보이시오. (5점)
    INSERT INTO USER_BUCKET(Fname, Lname) VALUES(‘Hyewon’, ‘Hwang’);
    INSERT INTO USER_BUCKET(Fname, Lname) VALUES(‘Gyungsoo’, ‘Oh’);
    UPDATE USER_BUCKET
    SET Lname=‘Yang’
    WHERE Fname=‘Gyungsoo’ AND Lname=‘Oh’;