5급 공채 2차

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

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

제1문 (15점)

두 개의 릴레이션 직원(직원번호, 이름, 연봉, 부서번호)과 부서(부서번호, 부서명, 전화번호)가 있을 때, 다음 물음에 답하시오. (단, 각 릴레이션의 기본키는 밑줄로 표시되어 있고, 직원 릴레이션의 ‘부서번호’는 부서 릴레이션의 ‘부서번호’를 참조하는 외래키이다) (총 15점)
직원부서
직원번호이름연봉부서번호부서번호부서명전화번호
100김혜원3,00011기획8008
200이유미5,00022홍보8000
300황준호6,00023영업6688
400김성호5,00014개발7777
500유수빈6,0001
  1. (1) 다음 SQL 문의 실행 결과를 쓰시오. (5점)
    SELECT 직원번호, 연봉, 부서명
    FROM 직원 E, 부서 D
    WHERE 연봉 > (SELECT AVG(연봉)
    FROM 직원
    WHERE 부서번호 = E.부서번호)
    AND E.부서번호 = D.부서번호 ;
  2. (2) 다음은 “각 부서에서 가장 많은 급여를 받는 직원의 이름, 부서번호, 연봉을 검색하라”는 SQL 문이다. ㉠～㉢을 올바르게 채우시오. (5점)
    SELECT 이름, 부서번호, 연봉
    FROM
    ㉠

    WHERE 연봉 = (SELECT
    ㉡

    FROM 직원
    WHERE
    ㉢
    );
  3. (3) ‘부서’ 릴레이션에서 투플(2, 홍보, 8000)을 삭제하면 발생되는 문제점을 설명하고, 이것을 해결할 수 있는 4가지 선택(option)을 설명하시오. (5점)

제2문 (20점)

공집합 상태인 B－트리에 키 값 20, 23, 13, 5, 6, 7, 9, 3, 19, 4를 순서대로 삽입한 후 변형된 B－트리가 다음과 같다. 물음에 답하시오. (단, B－트리의 차수는 4이다) (총 20점)
6

13



3
4
5




7
9





19
20
23





널 트리 포인터
  1. (1) B－트리의 현재 상태에서 키 값 2와 11을 순서대로 삽입한 후 변형된 B－트리를 제시하시오. (8점)
  2. (2) 1)에서 도출된 B－트리에 키 값 12를 삽입한 후 변형된 B－트리를 제시하시오. (7점)
  3. (3) 2)에서 도출된 B－트리에 키 값 2를 삭제한 후 변형된 B－트리를 제시하시오. (5점)

제3문 (20점)

다음 SQL 문을 보고 물음에 답하시오. (총 20점)
CREATE TABLE T1
( A1 INT NOT NULL,
A2 INT UNIQUE,
A3 INT,
PRIMARY KEY (A1),
FOREIGN KEY (A3) REFERENCES T2(B1) ON DELETE CASCADE ON UPDATE CASCADE);

CREATE TABLE T2
( B1 INT NOT NULL,
B2 INT,
B3 INT,
PRIMARY KEY (B1),
FOREIGN KEY (B3) REFERENCES T1(A1));
  1. (1) 위와 같은 DDL(Data Definition Language) 문으로 테이블 T1과 T2를 생성하려고 할 때 정상적으로 실행이 되지 않는다. 그 이유를 설명하고, 주어진 SQL 구문과 동일한 제약조건을 지니도록 DDL 문을 수정하시오. (10점)
  2. (2) 테이블 T1과 T2가 생성된 직후 다음의 SQL구문을 순차적으로 실행(㉠～㉢)하였을 때, 오류가 발생하는 구문과 그 이유를 설명하시오. (10점)
    ㉠INSERT INTO T1 VALUES(100, 10, NULL);
    ㉡INSERT INTO T1 VALUES(200, 10, NULL);
    ㉢INSERT INTO T1 VALUES(300, 20, 500);

제4문 (30점)

트랜잭션 T1, T2, T3의 <스케줄 S1>에 대한 다음 물음에 답하시오. (단, read(X)는 데이터 X 읽기연산, write(X)는 데이터 X 쓰기연산을 의미한다) (총 30점)
<스케줄 S1>
T1T2T3
read(X)
read(Y)
read(Y)
read(Z)
read(Z)
write(X)
write(Y)
write(Y)
read(Y)
  1. (1) <스케줄 S1>의 엄격한 스케줄(strict schedule) 여부와 갱신 손실 문제의 발생 여부를 판정하고, 그 이유를 각각 설명하시오. (10점)
  2. (2) 기본 타임스탬프 순서 프로토콜의 변형인 Thomas 쓰기 규칙에 따라 <스케줄 S1>을 수행할 때, 트랜잭션 철회 발생 여부를 판정하고, 그 이유를 설명하시오. (단, <스케줄 S1>에서 트랜잭션 T1, T2, T3 순서대로 시작한다) (10점)
  3. (3) 다음의 <스케줄 S2>와 <스케줄 S1>이 뷰 동치(view equivalent)인지 여부를 판정하고, 그 이유를 설명하시오. (10점)
    <스케줄 S2>
    T1T2T3
    read(X)
    read(Y)
    write(X)
    write(Y)
    read(Y)
    read(Z)
    write(Y)
    read(Z)
    read(Y)

제5문 (15점)

‘수리예약’ 릴레이션과 요구사항이 다음과 같을 때 물음에 답하시오. (총 15점)
고객ID고객이름물품종류예약일기사ID기사이름
1홍길동냉장고06/151이태백
1홍길동냉장고06/162강감찬
3황진희휴대폰06/173심수일
4유관순휴대폰06/174박문수
5이성계TV06/185최문선

<요구사항>
○동일한 이름을 갖는 고객이나 수리기사가 있을 수 있다.
○고객ID, 기사ID는 각각 고객과 수리기사의 고유한 식별자이다.
○한 명의 수리기사는 하나의 물품종류 수리만 담당한다.
○고객은 하나의 물품종류에 대하여 하루에 한 번만 예약이 가능하다.
  1. (1) 위 릴레이션에 대한 규준커버(canonical cover)를 구하시오. (5점)
  2. (2) 함수 종속을 바탕으로 후보키(candidate key)를 모두 구하시오. (5점)
  3. (3) 위 릴레이션은 BCNF(Boyce/Codd Normal Form)를 만족하는가? 만약 만족하지 않는다면 그 이유를 설명하고, BCNF를 만족하도록 무손실 분해하시오.(단, 릴레이션의 개수는 가능한 적게 유지하시오) (5점)