2023년 제 24회 정보시스템 감리사 기출문제 풀이 - 데이터베이스(61-65)

[오늘부터 데이터베이스 기출문제 61-65를 풀어보겠습니다.]

 

문제 61

61. 다음과 같은 함수 종속을 가진 릴레이션 R을 BCNF로 정규화한 결과로 옳은 것은? (단, 아래 지문에서 밑줄친 열은 기본키, 이탤릭체는 외래 키를 의미한다.) R(A, B, C, D) 함수종속: A→(B, C, D), B→(A, C, D), D→C ① R1(A, B, D), R2(D, C) ② R1(A, B, C), R2(C, D) ③ R1(A, B, C), R2(B, D) ④ R1(A, C, D), R2(B, A)

 

출제의도

BCNF 정규화에 대한 이해를 검증하려는 의도로 출제되었습니다. BCNF는 데이터베이스 설계에서 중요한 정규형이며, 그것이 어떻게 동작하는지 이해하는 것은 데이터베이스 시스템을 효과적으로 사용하는 데 중요합니다.

 

각 문항에 대해서 설명드리겠습니다:

① R1(A, B, D), R2(D, C):

이것은 올바른 BCNF 정규화입니다. R1은 A→B, A→D의 함수 종속성을 가지며, A는 R1의 기본키입니다. R2는 D→C의 함수 종속성을 가지며, D는 R2의 기본키입니다.

 

② R1(A, B, C), R2(C, D):

이것은 BCNF를 만족하지 않습니다. R2에서 C는 기본키가 아니지만, C→D의 함수 종속성이 있습니다.

 

③ R1(A, B, C), R2(B, D):

이것은 BCNF를 만족하지 않습니다. R2에서 B는 기본키가 아니지만, B→D의 함수 종속성이 있습니다.

 

④ R1(A, C, D), R2(B, A):

이것은 BCNF를 만족하지 않습니다. R2에서 B는 기본키가 아니지만, B→A의 함수 종속성이 있습니다.

 

따라서, 정답은 ① R1(A, B, D), R2(D, C)

 

문제 62

62. 다음 Sailors, Boats, Reserves 3개의 릴레이션에서 질의 “color가 'red'인 Boat와 'green'인 Boat를 모두 reserve한 Sailor의 sname을 검색하 라.”를 SQL로 나타낸 문장으로 옳은 것은? (단, Sailors, Boats, Reserves 릴레이션 각각의 기본 키는 sid, bid, (sid, bid)이다.) Sailors(sid, sname, rating, age) Boats(bid, bname, color) Reserves(sid, bid, day) ① SELECT S.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid AND (B.color = 'red' OR B.color = 'green'); ② SELECT S.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid AND (B.color = 'red' AND B.color = 'green'); ③ SELECT S.sname FROM Sailors S, Reserves R1, Boats B1, Reserves R2, Boats B2 WHERE S.sid = R1.sid AND R1.bid = B1.bid AND S.sid = R2.sid AND R2.bid = B2.bid AND B1.color = 'red' AND B2.color = 'green'; ④ SELECT S.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red' UNION SELECT S2.sname FROM Sailors S2, Boats B2, Reserves R2 WHERE S2.sid = R2.sid AND R2.bid = B2.bid AND B2.color = 'green';

 

출제의도

SQL 질의문 작성에 대한 이해를 검증하는 것입니다.

 

각 항목에 대한 설명은 다음과 같습니다:

① SELECT S.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid AND (B.color = ‘red’ OR B.color = ‘green’);:

이 설명은 옳지 않습니다.

이 질의문은 ‘red’ 또는 'green’인 보트를 예약한 선원의 이름을 반환합니다. 하지만 우리가 원하는 것은 'red’와 ‘green’ 보트를 모두 예약한 선원의 이름입니다.

 

② SELECT S.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid AND (B.color = ‘red’ AND B.color = ‘green’);:

이 설명은 옳지 않습니다.

이 질의문은 보트의 색상이 동시에 'red’와 'green’인 경우를 찾으려고 시도합니다. 하지만 이는 불가능합니다.

 

③ SELECT S.sname FROM Sailors S, Reserves R1, Boats B1, Reserves R2, Boats B2 WHERE S.sid = R1.sid AND R1.bid = B1.bid AND S.sid = R2.sid AND R2.bid = B2.bid AND B1.color = ‘red’ AND B2.color = ‘green’;:

이 설명은 옳습니다.

이 질의문은 ‘red’ 보트를 예약한 선원과 ‘green’ 보트를 예약한 선원을 찾아 그들의 교집합을 반환합니다. 이는 우리가 원하는 결과입니다.

 

④ SELECT S.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘red’ UNION SELECT S2.sname FROM Sailors S2, Boats B2, Reserves R2 WHERE S2.sid = R2.sid AND R2.bid = B2.bid AND B2.color = ‘green’;:

이 설명은 옳지 않습니다.

이 질의문은 ‘red’ 보트를 예약한 선원과 ‘green’ 보트를 예약한 선원을 찾아 그들의 합집합을 반환합니다. 하지만 우리가 원하는 것은 이들의 교집합입니다.

 

따라서, 이 문제에서 SQL로 나타낸 문장으로 옳은 것은 ③ 입니다.

 

문제 63

63. 다음 STUDENT(학생)와 ENROLL(수강) 릴레이션에서 “Cno가 ‘C413’인 과목을 수강한 학생의 Sname, Dept와 수강의 Grade를 검색하라.”는 질의를 SQL 로 나타낸 문장으로 옳은 것은? (단, STUDENT와 ENROLL 릴레이션 각각의 기본 키는 Sno, (Sno, Cno)이다.) STUDENT(Sno, Sname, Year, Dept) ENROLL(Sno, Cno, Grade) ① SELECT Sname, Dept, Grade FROM STUDENT JOIN ENROLL WITH(Sno) WHERE ENROLL.Cno = 'C413'; ② SELECT Sname, Dept, Grade FROM STUDENT NATURAL JOIN ENROLL USING(Sno) WHERE ENROLL.Cno = 'C413'; ③ SELECT Sname, Dept, Grade FROM STUDENT NATURAL JOIN ENROLL ON (STUDENT.Sno = ENROLL.Sno) WHERE ENROLL.Cno = 'C413‘; ④ SELECT Sname, Dept, Grade FROM STUDENT JOIN ENROLL ON (STUDENT.Sno = ENROLL.Sno) WHERE ENROLL.Cno = 'C413';

 

출제의도

SQL의 JOIN 연산자를 이해하고 올바르게 사용하는 능력을 평가하는 것입니다.

 

각 항목에 대한 설명은 다음과 같습니다:

① SELECT Sname, Dept, Grade FROM STUDENT JOIN ENROLL WITH(Sno) WHERE ENROLL.Cno = 'C413';

이 문장은 SQL 문법에 맞지 않습니다.

JOIN 연산자와 함께 WITH 절을 사용하는 것은 올바르지 않습니다.

 

② SELECT Sname, Dept, Grade FROM STUDENT NATURAL JOIN ENROLL USING(Sno) WHERE ENROLL.Cno = 'C413';

이 문장은 NATURAL JOIN과 USING 절을 동시에 사용하고 있어서 문법적으로 올바르지 않습니다.

NATURAL JOIN은 두 테이블에서 이름이 같은 모든 열을 자동으로 결합하고, USING은 결합할 열을 명시적으로 지정합니다. 둘 중 하나만 사용해야 합니다.

 

③ SELECT Sname, Dept, Grade FROM STUDENT NATURAL JOIN ENROLL ON (STUDENT.Sno = ENROLL.Sno) WHERE ENROLL.Cno = 'C413‘;

이 문장은 NATURAL JOIN과 ON 절을 동시에 사용하고 있어서 문법적으로 올바르지 않습니다.

NATURAL JOIN은 두 테이블에서 이름이 같은 모든 열을 자동으로 결합하고, ON은 결합 조건을 명시적으로 지정합니다. 둘 중 하나만 사용해야 합니다.

 

④ SELECT Sname, Dept, Grade FROM STUDENT JOIN ENROLL ON (STUDENT.Sno = ENROLL.Sno) WHERE ENROLL.Cno = 'C413';

이 문장은 SQL 문법에 맞습니다.

JOIN 연산자와 함께 ON 절을 사용하여 STUDENT 테이블과 ENROLL 테이블을 Sno 열을 기준으로 결합하고 있습니다. 그리고 WHERE 절을 사용하여 Cno가 'C413’인 행만 선택하고 있습니다.

 

따라서 이 문장은 "Cno가 'C413’인 과목을 수강한 학생의 Sname, Dept와 수강의 Grade를 검색"하는 질의를 올바르게 나타내고 있습니다. 따라서, 올바른 SQL 질의문은 ④번입니다.

 

문제 64

64. 다음 그림은 트랜잭션 T1~T5에 대해 각각의 수행 시간을 표기하였다. 트랜잭션 수행 중 체크 포인 트를 실시하였고, 그 이후 시스템이 다운되었다. 시스템이 재가동될 때 수행되는 회복 기능으로 가장 적절한 것은?

 

 

① T2, T4 트랜잭션은 재수행한다. ② T2, T4 트랜잭션은 취소한다. ③ T3, T4, T5 트랜잭션은 재수행한다. ④ T3, T4, T5 트랜잭션은 취소한다.

 

출제의도

시스템이 재가동될 때 수행되는 회복 기능에 대한 문제입니다.

 

트랜잭션 T1~T5의 수행 시간을 살펴보면, T1, T2, T3는 체크포인트 이전에 완료되었고, T4, T5는 체크포인트 이후에 완료되지 않았습니다. 따라서, T1, T2, T3는 이미 데이터베이스에 반영되었기 때문에 회복할 필요가 없습니다.

 

반면, T4, T5는 데이터베이스에 반영되지 않았기 때문에 회복해야 합니다. 회복 방법에는 UNDO와 REDO가 있습니다. UNDO는 트랜잭션 수행 이전의 상태로 데이터베이스를 되돌리는 방법이고, REDO는 트랜잭션 수행 이후의 변경 사항을 데이터베이스에 적용하는 방법입니다.

 

이 문제의 경우, T4, T5는 체크포인트 이후에 완료되지 않았기 때문에 데이터베이스에 반영되지 않은 상태입니다.

따라서, T4, T5는 REDO를 통해 데이터베이스에 적용해야 합니다.

 

따라서, 답은 **① 체크포인트 이후에 수행된 T4, T5 트랜잭션은 REDO 한다.**입니다.

 

문제 65

65. 다음은 릴레이션 EMPLOYEE와 DEPENDENT를 생성 하는 데이터 정의문이다. 두 릴레이션에 대해 “자신의 부양가족과 이름이 같은 직원의 이름 (Name)을 출력하라”를 처리하는 SQL 질의를 모두 나열한 것은? CREATE TABLE EMPLOYEE      ( Name VARCHAR(15) NOT NULL,        Ssn CHAR(9) NOT NULL,        PRIMARY KEY (Ssn)       ); CREATE TABLE DEPENDENT    ( Essn CHAR(9) NOT NULL,      Name VARCHAR(15) NOT NULL,      PRIMARY KEY(Essn, Name),      FOREIGN KEY(Essn) REFERENCES EMPLOYEE(Ssn) ); 가. SELECT E.Name FROM EMPLOYEE AS E WHERE E.Ssn IN (SELECT Essn FROM DEPENDENT AS D WHERE E.Name=D.Name ); 나. SELECT E.Name FROM EMPLOYEE AS E WHERE EXISTS (SELECT * FROM DEPENDENT AS D WHERE E.Ssn=D.Essn AND E.Name=D.Name ); 다. SELECT E.Name FROM EMPLOYEE AS E, DEPENDENT AS D WHERE E.Ssn=D.Essn AND E.Name=D.Name; ① 가, 나 ② 가, 다 ③ 가, 다 ④ 가, 나, 다

 

출제의도

SQL의 서브쿼리와 조인을 이해하고 올바르게 사용하는 능력을 평가하는 것입니다.

 

각 항목에 대한 설명은 다음과 같습니다:

가. SELECT E.Name FROM EMPLOYEE AS E WHERE E.Ssn IN (SELECT Essn FROM DEPENDENT AS D WHERE E.Name=D.Name );

이 질의는 EMPLOYEE 테이블에서 Ssn이 DEPENDENT 테이블의 Essn과 일치하고, EMPLOYEE의 Name과 DEPENDENT의 Name이 일치하는 직원의 이름을 선택합니다. 이 질의는 "자신의 부양가족과 이름이 같은 직원의 이름을 출력"하는 요구사항을 만족합니다.

 

나. SELECT E.Name FROM EMPLOYEE AS E WHERE EXISTS (SELECT * FROM DEPENDENT AS D WHERE E.Ssn=D.Essn AND E.Name=D.Name );

이 질의는 EMPLOYEE 테이블에서 적어도 하나의 DEPENDENT 행이 존재하며, 그 행의 Essn이 EMPLOYEE의 Ssn과 일치하고, DEPENDENT의 Name이 EMPLOYEE의 Name과 일치하는 직원의 이름을 선택합니다. 이 질의는 "자신의 부양가족과 이름이 같은 직원의 이름을 출력"하는 요구사항을 만족합니다.

 

다. SELECT E.Name FROM EMPLOYEE AS E, DEPENDENT AS D WHERE E.Ssn=D.Essn AND E.Name=D.Name; 이 질의는 EMPLOYEE 테이블과 DEPENDENT 테이블을 조인하고, 조인된 결과에서 Ssn과 Essn이 일치하고, EMPLOYEE의 Name과 DEPENDENT의 Name이 일치하는 직원의 이름을 선택합니다. 이 질의는 "자신의 부양가족과 이름이 같은 직원의 이름을 출력"하는 요구사항을 만족합니다.

 

따라서, 올바른 SQL 질의문은 ④번 '가, 나, 다’입니다. 

 

[다음 시간에는 데이터베이스 기출문제 65-70까지 풀어보도록 하겠습니다.]