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

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

제1문 (20점)

아래의 E－R 다이어그램(entity-relationship diagram)에 대한 다음 물음에 답하시오. (총 20점)
[그림]

(1) E－R 다이어그램 설계를 지원하는 일부 도구는 이진 관계(binary relationship)만을 표현하도록 지원하며 차수(degree)가 3인 경우는 지원하지 않는 경우가 있다. 주어진 E－R 다이어그램을 이진 식별 관계(binary identifying relationship)를 이용하여 동등한 의미의 E－R 다이어그램으로 변환하시오. (8점)
(2) 1)의 변환 결과 E－R 다이어그램을 위한 관계 데이터베이스의 테이블 스키마를 설계하시오. (단, 각 외래키(foreign key)가 참조하고 있는 기본키(primary key)를 화살표로 명확하게 표현한다) (7점)
(3) 1)의 변환 결과 E－R 다이어그램의 Supply에 해당하는 테이블을 생성하기 위한 SQL문을 작성하시오. (단, 릴레이션의 이름은 개체 타입과 같은 이름을 사용하고, 속성의 데이터 타입은 임의로 정할 수 있으며, 참조된 기본키에 변화가 생기면 외래키에도 같은 변화가 일어나도록 지정한다) (5점)

제2문 (20점)

다음은 유기동물보호소에 대한 데이터베이스 스키마를 설명한 것이다. 물음에 해당하는 SQL문을 작성하시오. (총 20점)

Animal(AID: integer, Name: string, DateAdmitted: date, Type: string)
Adopter(PIN: integer, Name: string, Gender: string, Address: string)
Adoption(AnimalID: integer, PIN: integer, AdoptDate: date)

○밑줄 친 속성(attribute)은 각 릴레이션의 기본키이다.
○Animal은 이 유기동물보호소에 현재 있는 동물들에 관한 릴레이션이며, DateAdmitted는 해당 동물이 보호소에 들어온 날짜이고, Type은 동물의 종류(개, 고양이 등)를 뜻한다.
○Adopter는 동물을 입양한 입양인에 대한 정보를 포함하는 릴레이션이며, Gender는 ‘남’ 또는 ‘여’를 값으로 갖는다.
○Adoption은 입양에 관한 정보를 포함하는 릴레이션이며, AnimalID는 입양되는 동물의 AID를 참조하는 외래키이고, PIN은 입양인의 PIN을 참조하는 외래키이다. AdoptDate는 입양된 날짜이다.

(1) 최소한 2마리 이상을 입양한 여성 입양인의 이름과 주소를 구하시오. (5점)
(2) 한 번도 입양된 적이 없는 동물의 종류를 구하시오. (10점)
(3) 모든 종류의 동물을 입양한 입양인의 이름을 구하시오. (5점)

제3문 (20점)

주어진 릴레이션 R에 대해 트랜잭션의 고립화 수준(isolation level)과 관련하여 다음 물음에 답하시오. (단, 고립화 수준은 READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE이다) (총 20점)

R
id	total
1	10
2	0
3	0

(1) 다음의 트랜잭션 T1과 T2가 동시에 수행되는 경우 T2의 결과가 0으로 출력될 수 있는 고립화 수준이 있는지 판단하고, 있다면 그 이유를 설명하시오. (10점)
T1 T2
1
2

3

4 begin transaction;
update R set total = total - 10
where id = 1;
update R set total = total + 10
where id = 2;
commit; begin transaction;
select sum(total) from R;

commit;
(2) 다음의 트랜잭션 T3과 T4가 동시에 수행되는 경우 고립화 수준별로 T4의 출력 가능한 값을 제시하시오. (10점)
T3 T4
1
2
3
4 begin transaction;
insert into R values (4, 100);
commit;
begin transaction;
select sum(total) from R;
select sum(total) from R;
commit;

제4문 (20점)

릴레이션 정규화에 대한 다음 물음에 답하시오. (단, 함수 종속은 주어진 릴레이션 인스턴스로 한정한다) (총 20점)

(1) 다음 릴레이션에서 아래 주어진 각 함수 종속(functional dependency)이 존재하는지 판단하고 그 이유를 설명하시오. (5점)
A B C D
8 8 2 3
6 7 3 9
6 7 3 7
8 3 4 9

가) D→A
나) C→D
다) { B, C }→D
라) { A, B }→C

(2) 다음은 부품의 상태와 재고위치를 관리하기 위한 릴레이션이다. 이 릴레이션에서 식별할 수 있는 함수 종속을 나열하고, 이 함수 종속에 근거하여 최대한의 고차 정규형으로 분해한 결과를 보이시오. (단, 밑줄 친 속성은 기본키를 나타낸다) (15점)

상태번호	부품번호	상태	재고위치	수량
S1	P1	20	서울	300
S1	P2	20	서울	200
S1	P3	20	서울	400
S1	P4	20	서울	200
S1	P5	20	서울	100
S1	P6	20	서울	100
S2	P1	10	부산	300
S2	P2	10	부산	400
S3	P2	30	광주	200
S4	P2	20	서울	200
S4	P4	20	서울	300
S4	P5	20	서울	400

제5문 (20점)

아래의 XML 문서를 참조하여 다음 물음에 답하시오. (총 20점)

(1) 아래의 Xpath식의 결과를 쓰시오. (5점)
/node/node[3]/node/text()
(2) 아래의 Xpath 식의 결과를 쓰시오. (5점)
/node/node[node/text()=3]/node/text()
(3) 아래의 Xpath 식의 결과를 쓰시오. (5점)
/node/node[node/text()=3][node/text()=4]/node/text()
(4) 아래의 Xpath 식의 결과를 쓰시오. (5점)
/node[node/node/text()=3][node/node/text()=4]/node/node/text()

	T1	T2
1 2 3 4	begin transaction; update R set total = total - 10 where id = 1; update R set total = total + 10 where id = 2; commit;	begin transaction; select sum(total) from R; commit;

	T3	T4
1 2 3 4	begin transaction; insert into R values (4, 100); commit;	begin transaction; select sum(total) from R; select sum(total) from R; commit;