[데이터베이스] 11. 관계 대수 연습 문제

다음과 같이 Sailors, Boats, Reserves 테이블이 정의되어 있다.

 

Sailors (sid, sname, rating, age)

Boats (bid, bname, color)

Reserves (sid, bid, day)

 

구체적인 데이터 표기는 생략하였다.

이제 이 테이블에 대해 관계대수 문제를 풀어보자.

 

1. bid = 103 인 보트를 예약한 선원들의 이름 구하기

 

 

예약 테이블에서 bid = 103 인 예약만 가져온 뒤, 선원 테이블과 자연조인하였다.

그러면 두 테이블의 모든 공통필드를 기준으로 동등 조인을 걸고, sid 가 동일한 경우만 추려낸다.

그러면 103번 보트를 예약한 선원들만 남게 되므로, 여기에서 sname 을 프로젝션하여 조회하면 된다.

 

 

강의록에서는 이렇게 복잡하게 푼 경우도 제시하고 있다.

예약 테이블에서 bid = 103 인 행들을 추린 뒤, 그 결과물을 Temp1 이라고 이름 짓는다.

다음으로 Temp1 과 선원 테이블을 자연조인한 결과물을 Temp2 라고 이름 짓는다.

마지막으로 Temp2 에서 sname 을 프로젝션한다.

 

사실상 첫 번째 풀이와 완벽하게 같은 과정으로 푼 방식이다.

 

 

이런 풀이도 가능하다.

일단 모든 예약과 선원 쌍에 대해 자연 조인을 건다.

그러면 각 선원 정보와 그 선원이 예약한 보트 정보가 함께 쭉 나온다.

이 결과물에 대해서 bid = 103 인 경우를 추리고, 그 결과에 대해서 sname 만 프로젝션한다.

다만 이 방식은 두 테이블의 모든 데이터를 조인하므로 효율적인 풀이는 아니다.

 

 

나는 이렇게 풀었다.

중간에 sid를 한번 프로젝션하여 예약 테이블에서 불필요한 다른 정보는 제외하고 선원 테이블과 조인하였다.

 

 

2. 빨간 배를 예약한 선원 이름 구하기

 

먼저 모든 빨간 보트 정보를 가져온 뒤, 해당 정보를 예약 테이블과 조인하고, 선원 테이블과 조인하면 된다.

 

 

조금 더 효율적으로 구하려면 중간 중간에 조인에 필요한 id 값만으로 프로젝션 해주면 된다.

쿼리 최적화기는 첫번째 해답을 제시하더라도 알아서 두번째 해답과 같은 방식으로 동작하여 쿼리 동작을 최적화한다.

 

 

나는 첫번째 풀이와 동일하게  풀었다.

 

 

3. Lubber 가 예약한 배의 색상 구하기

 

나는 위와 같이 구했다.

강의록에는 따로 문제가 없었고, 책에는 bid 를 프로젝션 하는 과정을 제외하고 동일하게 구하였다.

 

 

4. 적어도 한 척의 배를 예약한 선원의 이름 구하기

적어도 한 척의 배를 예약했다는 것은 예약 테이블에 이름이 존재한다는 뜻과 같다.

따라서 예약 테이블에서 sid 를 프로젝션하여 모든 종류의 선원을 구한 뒤, 선원 테이블과 자연 조인하여 이름을 구하면 된다.

 

 

책에서는 그냥 Reserves, Sailors 를 바로 자연조인한 뒤에 선원 이름을 프로젝션하였다.

 

 

5. 빨간색 배 또는 초록색 배를 예약한 선원 이름 구하기

 

보트 테이블에서 색상이 빨간색 또는 녹색인 보트를 조회하고 예약 테이블과 조인, 선원 테이블과 조인하여 이름을 구하였다.

관계 대수에서 '또는' 을 표현할 때는 v 기호를 사용한다.

 

책에서는 union 연산을 사용하여 보트 테이블에서 색이 빨간색인 보트를 조회한 결과와 색이 초록색인 보트를 조회한 결과 각각을 union 한 뒤 자연조인 하는 방법도 소개했다.

 

 

6. 빨간색 배 그리고 초록색 배 모두 예약한 선원 이름 구하기

관계 대수에서 '그리고' 를 나타내는 기호는 ^ 인데, 이 기호를 사용하여 Boats 테이블에서 조회하면 당연히 아무 값도 안나온다.

하나의 보트는 하나의 색 정보만 가질 수 있기 때문이다.

따라서 이때는 초록색 배를 예약한 선원, 빨간색 배를 예약한 선원을 따로 구하고 교집합을 취해야 한다.

 

 

 

나는 이렇게 구했다.

 

 

강의록 정답과 동일하다.

 

 

7. 적어도 두 척의 배를 예약한 선원의 이름 구하기

이 문제가 조금 어려운 문제이다.

적어도 두 척의 배를 예약했다는 것은, 하나의 예약 테이블 안에서 같은 sid 인 선원이 서로 다른 bid 를 예약한 경우가 존재한다는 뜻이다.

따라서 같은 테이블을 크로스 프로덕트한 뒤에 sid 는 같은데, bid 는 다른 컬럼을 찾아 해당 sid 를 찾아내면 된다.

 

 

나는 이렇게 구했다.

 

 

책에서는 처음부터 선원 테이블과 조인한 다음에, 거기에서 크로스 프로덕트 시켜서 구했다.

 

 

8. 나이가 20세를 넘고 빨간 배를 예약하지 않은 선원들의 sid 구하기

빨간배를 예약하지 않은 선원들 중에는 아무 배도 예약하지 않은 선원이 있을 수 있다.

따라서 전체 선원 중에서 빨간 배를 예약한 선원 id를 빼내는 식으로 찾으면 된다.

 

 

이 문제만 선원들의 sid를 구하라고 한 이유가 있었다.

만약 sname을 구하려고 했다면 마지막 결과에 대해 다시 Sailors 테이블과 자연 조인을 한 뒤 sname을 추출해야 했을 것이다.

 

책에서는 Red 테이블을 만들 때 Sailors 테이블도 함께 조인을 걸었다.

어차피 필요한 값은 sid 필드인데 굳이 Sailors 테이블을 조인할 필요는 없어보인다.

 

 

9. 모든 배를 예약한 선원들의 이름 구하기

디비전 예시와 동일한 상황이다.

 

 

 

다음과 같이 division을 사용해서 모든 배를 예약한 sid 를 추려내고, 선원 테이블과 조인하여 결과를 얻었다.

 

 

10. Interlake 라는 이름을 가진 배를 모두 예약한 선원 이름 구하기

서로 다른 배가 같은 이름을 가질 수 있다는 것을 고려하자.

이 이름을 가진 모든 보트를 조회한 뒤, 이를 대상으로 디비전 연산을 수행하면 된다.