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

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

제1문 (30점)

온라인 매장에서 고객의 주문에 대응하기 위해 고객, 주문, 주문상세, 상품, 창고 개체(entity)를 이용하여 데이터베이스를 구축하고자 한다. 다음 데이터베이스 요구사항을 보고 물음에 답하시오. (총 30점)

○고객은 고객코드, 고객명, 아이디, 비밀번호, 휴대폰, 주소, 우편번호로 표현하고, 고객코드를 이용하여 식별한다.
○하나의 주문에서 하나의 상품만을 주문하는 것이 아니고, 여러 개의 다른 상품들을 주문할 수 있기 때문에 각각의 개별 상품에 대한 내용을 관리하는 주문상세와 이들 주문상세 내용을 포함하는 주문으로 구분하여 관리한다.
○주문은 주문코드를 통해 각각의 주문을 식별하며, 고객에 의해 주문이 발생한 날짜와 고객이 입력한 배송지 주소를 포함한다.
○주문 상세는 주문에 포함되는 개별 상품에 대한 내용을 관리하는데, 주문 안에서 개별 주문상품을 구분하기 위해 주문순번을 부여하고, 해당 상품에 대한 주문수량과 주문에 따른 금액을 저장한다.
○주문 상세에서 참조되는 상품은 상품코드를 통해 식별하며, 상품 이름과 가격 데이터를 관리한다.
○상품을 보관하는 창고는 창고코드로 식별되며, 창고이름과 창고주소 데이터를 관리한다.
○하나의 상품은 여러 창고에 분산 보관되며, 하나의 창고에 여러 상품들이 보관되고, 각각의 창고에 보관된 상품의 개수가 몇 개인지에 대한 재고수량을 관리한다.
※ 모델링 과정에서 약한개체(weak entity)를 식별하고 포함해야 한다.

(1) 위 데이터베이스 요구사항에 대응한 개체－관계 스키마(Entity-Relationship schema)를 작성하시오. (단, ER 스키마 표기에서 개체는 직사각형 박스로, 관계성(relationship)은 마름모꼴로 표기하는 피터 첸(Peter Chen) 표기법을 이용하고, 참여제약조건에서 전체 참여는 이중실선으로 부분참여는 단일실선으로 표기하며 카디널리티 비율 제약조건은 1:1, 1:n, n:m 중에서 선택하여 적용한다) (10점)
(2) 1)에서 작성한 ER 스키마를 동등한 관계형 스키마(relational schema)로 변환하시오. (10점)
(3) 2)의 관계형 스키마에 대하여 SQL DDL문을 통해 테이블이 생성되었고 이들 테이블에 데이터(레코드)가 삽입되었다고 가정할 때, 다음 질의를 위한 SQL문을 작성하시오. (5점)
고객의 휴대폰 번호가 01012345678인 고객에 의해 발생한 주문의 주문날짜와 배송지 주소를 출력하시오.
(4) 3)과 동일한 상황에서 다음 질의를 위한 SQL문을 작성하시오. (5점)
주문상세에서 금액의 전체 합계가 100,000원 이상인 주문코드와 전체 합계 금액을 출력하시오.

제2문 (20점)

공무원 전산직 응시과목 교재를 주문하는 도서 데이터베이스의 교재주문 릴레이션이 다음과 같이 설계되었다. 이상현상(anomaly) 제거를 위해, 릴레이션을 분해하여 정규화를 수행하고자 한다. 물음에 답하시오. (단, 함수적 종속성은 주어진 릴레이션 인스턴스로 한정한다) (총 20점)

<교재주문 릴레이션>
주문번호	주문일	회원번호	이름	주소	교재번호	교재과목	수량
221001	20220510	2	공무원	용산	3	DB	1
221002	20220511	2	공무원	용산	4	OS	1
221003	20220521	1	김공직	세종	3	DB	2
221003	20220521	1	김공직	세종	5	PL	3
221003	20220521	1	김공직	세종	2	SE	1
225001	20220525	1	김공직	세종	5	PL	2

(1) 완전 함수적 종속성(full functional dependency)을 위해 교재주문 릴레이션을 분해하여 2정규화(2NF)를 수행하시오. (10점)
(2) 1)의 제2정규형 결과에서, 비이행적 함수적 종속성(non-transitive functional dependency)을 위한 3정규화(3NF)를 수행하시오. (10점)

제3문 (15점)

해시 인덱스(hash index)는 해시 함수를 기반으로 인덱스 엔트리를 구성한다. 특히 확장성 해싱(extensible hashing)은 데이터가 증가하고 감소함에 따라 버킷을 분할하거나 합병할 수 있다. 해시의 디렉터리는 현재 디렉터리 깊이 d를 유지하는 헤더와 2^d개의 버킷을 지시할 수 있는 포인터 엔트리로 구성된다. 다음 그림은 버킷 크기가 3인 경우 검색 키값을 차례대로 삽입했을 때 17까지 삽입한 후의 확장성 해싱 구조를 보여주고 있다. 물음에 답하시오. (단, 해싱 함수 결과의 이진수 표현을 모조키로 사용한다고 가정하며, 디렉터리의 인덱스는 모조키의 처음 d 비트를 사용한다. 예를 들면, 키값 3의 경우 해시 값이 3이고 모조키는 011이므로 d가 2인 경우 01을 사용하며, d가 3인 경우는 011을 사용한다) (총 15점)

○검색 키값: 10, 3, 5, 15, 11, 17, 19, 23, 29, 31
○해싱 함수: h(x)＝x mod 8 (mod: 나머지연산)
[그림]

(1) 19, 23을 삽입하였을 때 키의 삽입 과정을 설명하고 확장성 해싱의 최종 구조를 보이시오. (10점)
(2) 29, 31을 삽입하였을 때 키의 삽입 과정을 설명하고 확장성 해싱의 최종 구조를 보이시오. (5점)

제4문 (20점)

검사점(checkpoint)을 이용하는 즉시－갱신 기반 undo-redo logging 회복기법에서, 트랜잭션(T1～T5)이 동시에 실행된 후 각 트랜잭션의 연산과 로그 내용이 다음과 같다. 물음에 답하시오. (단, 즉시－갱신은 write 수행 후 디스크에 즉시 반영된다고 가정한다) (총 20점)

T1	B＝25	로그번호	로그레코드
T2	D＝45 A＝15 C＝70	1	<T1, start>
		2	<T2, start>
		3	<T1, B, 20, 25>
T3	C＝35
		4	<T3, start>
T4	B＝27
		5	<T2, D, 40, 45>
T5	D＝47
		6	<T1, commit>

		7	<T3, C, 30, 35>
		8	<checkpoint>
		9	<T4, start>
		10	<T3, commit>
		11	<T4, B, ㉠ , ㉡ >
		㉠
		㉡
		12	<T5, start>
		13	<T2, A, 10, 15>
		14	<T4, commit>
		15	<T5, D, ㉢ , ㉣ >
㉢
㉣
16	<T2, C, 35, 70>
17	시스템 고장

(1) 로그레코드의 빈칸 ㉠, ㉡, ㉢, ㉣에 들어갈 로그의 내용을 작성하시오. (6점)
(2) 시스템 고장 후 undo를 수행할 트랜잭션을 나열하고, 해당되는 트랜잭션의 undo 회복과정을 제시하시오. (8점)
(3) A, B, C, D에 대하여 회복 연산 후의 데이터 값을 구하시오. (6점)

제5문 (15점)

데이터베이스에 1,000개의 항목 A₁, …, A_1,000이 있다. 병렬 시스템에서 아래와 같은 트랜잭션들을 수행하려고 한다.

BEGIN
READ(A_i);
perform a non-parallelizable, CPU-intensive computation;
WRITE(A_i);
COMMIT;

각 트랜잭션은 데이터베이스로부터 한 개의 항목을 읽고, CPU 연산을 수행하고, 데이터베이스에서 그 항목을 갱신한다. 상이한 트랜잭션들이 같은 항목 또는 상이한 항목들을 읽고 갱신할 수 있다. 예를 들어, 5개의 트랜잭션이 각각 A₃, A₁, A₃, A₃, A₂ 항목을 읽고 갱신할 수 있다. 병렬 시스템은 메모리를 공유하고(shared-memory), 100개의 CPU가 있으며, 다수의 트랜잭션들을 병렬로 처리하고, 각 트랜잭션은 한 CPU내에서 수행된다. 직렬 가능성(serializability)을 위해서, 병렬 시스템은 각 항목마다 하나의 로크(lock)를 사용한다. 다음 각 경우에 병렬 시스템이 초당 몇 개의 트랜잭션(TPS: Transactions Per Second)을 수행할 수 있는지를 제시하고 그 이유를 설명하시오. (단, 트랜잭션의 처리시간은 0.1초이다) (총 15점)

(1) 모든 트랜잭션들이 동일한 항목 A₁을 읽고 갱신한다. (단, 다른 어떤 항목도 읽지 않고, 갱신하지 않는다) (5점)
(2) 각 트랜잭션이 A₁～A₅₀ 항목들 중에서 균일 분포(uniform distribution)에 따라 선택된 한 개의 항목만 읽고 갱신한다. 예를 들어, 트랜잭션들이 A₄₄, A₂, A₁₅, A₁, A₃₆, A₁₁, … 항목들을 읽고 갱신한다. (5점)
(3) 각 트랜잭션이 모든 항목, 즉 A₁～A_1,000 항목들 중에서 균일 분포에 따라 선택된 한 개의 항목만 읽고 갱신한다. (5점)