2024.01.05 모각코 3회차 결과

이번 모각코 3회차에서는 관계 대수 질의 예제를 풀어보았다.

 

관계 대수 질의

아래와 같은 릴레이션 스키마가 있고,

파일럿(파일럿번호: 정수, 파일럿이름: 문자열, 등급: 정수, 나이: 실수)비행기(비행기번호: 정수, 비행기이름: 문자열, 비행기종류: 문자열)운항(파일럿번호: 정수, 비행기번호: 정수, 운항일자: 날짜)

각 릴레이션의 인스턴스가 아래와 같다고 할 때,

Pilot1

파일럿번호 파일럿이름 등급 나이

13	홍길동	1	44
32	이순신	10	44
44	안중근	7	32

 

AirCraft

비행기번호 비행기이름 비행기종류

101	에놀라게이	폭격기
102	톰캣	전투기
103	블랙버드	정찰기

 

Flight

파일럿번호 비행기번호 운항일자

13	101	2022-10-09
44	102	2022-11-23

 

 

테이블 생성하기

 

Pilot1

mysql> CREATE TABLE Pilot1 (
    -> PilotNo INTEGER,
    -> PilotName VARCHAR(20),
    -> Grade INTEGER,
    -> Age INTEGER
    -> );

 

 

AirCraft

mysql> CREATE TABLE AirCraft (
    -> AirCraftNo INTEGER,
    -> AirCraftName VARCHAR(20),
    -> AirCraftCat VARCHAR(20),
    -> );

 

Flight

mysql> CREATE TABLE Flight (
    -> PilotNo INTEGER,
    -> AirCraftNo INTEGER,
    -> OperationDate DATE
    -> );

 

 

 

1. 비행기 101을 운항하는 파일럿의 이름을 구하세요.

ALTER TABLE Pilot1 ADD CONSTRAINT pk_Pilot1 PRIMARY KEY(PilotNo);
ALTER TABLE Flight ADD CONSTRAINT fk_Flight_Pilot1 FOREIGN KEY (PilotNo) REFERENCES Pilot1 (PilotNo);

ALTER TABLE AirCraft ADD CONSTRAINT pk_AirCraft PRIMARY KEY(AirCraftNo);
ALTER TABLE Flight ADD CONSTRAINT fk_Flight_AirCraft FOREIGN KEY (AirCraftNo) REFERENCES AirCraft(AirCraftNo);

 

-FOREIGN KEY  설정과 FOREIGN KEY 설정을 위한 PRIMARY KEY를 설정하였다.

 

mysql> SELECT PilotName FROM Pilot1 WHERE PilotNo = (SELECT PilotNo FROM Flight WHERE AirCraftNo = 101);
+-----------+
| PilotName |
+-----------+
| 홍길동    |
+-----------+

 

 

2. 문제1을 응용하여 개명 연산을 사용하여 각 대수식을 작은 부분으로 분할한 다음 합쳐 연산하는 관계 대수식을 작성.

-- 부분 1: 항공기 번호에 해당하는 조인 수행
PART1 := Flight ⨝_{PilotNo, PilotNo} Pilot1

-- 부분 2: 선택 연산을 사용하여 조건을 충족하는 튜플 선택
PART2 := σ_{AircraftNo = 101} PART1

-- 부분 3: 프로젝션 연산을 사용하여 원하는 속성만 선택
RESULT := π_{PilotName} PART2

-- 결과 출력
RESULT

 

3. 전투기를 운항하는 파일럿의 이름을 구하세요.

SELECT PilotName FROM Pilot1 WHERE PilotNo = (SELECT PilotNo FROM Flight WHERE AirCraftNo = (SELECT AirCraftNo FROM AirCraft WHERE AirCraftCat = '전투기'));
+-----------+
| PilotName |
+-----------+
| 안중근    |
+-----------+
1 row in set (0.00 sec)

 

 

 

4.이순신이 조종하는 비행기의 종류를 구하세요.

mysql> SELECT AirCraftCat FROM AirCraft WHERE AirCraftNo = (SELECT AirCraftNo FROM Flight WHERE PilotNo = (SELECT PilotNo FROM Pilot1 WHERE PilotName = '이순신'));
Empty set (0.00 sec)

 

-  Pilot '이순신' 은 Flight에 존재하지않는다.

 

 

5.운항 스케줄이 잡혀있는 모든 파일럿의 이름을 구하세요.

mysql> SELECT PilotName FROM Pilot1 WHERE PilotNo IN (SELECT PilotNo FROM Flight);
+-----------+
| PilotName |
+-----------+
| 홍길동    |
| 안중근    |
+-----------+
2 rows in set (0.00 sec)

 

 - 가져올 값이 단일 값이 아니라 집합이므로 "=" 가 아닌 IN 을 사용해주었다.

 

 

6. 폭격기 또는 정찰기를 운항하는 파일럿의 이름을 구하세요

mysql> SELECT PilotName FROM Pilot1 WHERE PilotNo IN (SELECT PilotNo FROM Flight WHERE AirCraftNo IN (SELECT AirCraftNo
FROM AirCraft WHERE AirCraftCat = '정찰기' OR AirCraftCat = '폭격기'));
+-----------+
| PilotName |
+-----------+
| 홍길동    |
+-----------+
1 row in set (0.00 sec)

 

-  합집합 조건을 만족하기 위해 OR 을 사용하였다.

- 가져오고자하는 값이 집합이므로 "=" 대신 IN을 사용하였다.

 

 

7. 전투기와 폭격기를 운항하는 파일럿의 이름을 구하세요.

 

mysql> SELECT PilotName FROM Pilot1 WHERE PilotNo IN (SELECT PilotNo FROM Flight WHERE AirCraftNo IN (SELECT AirCraftNo
FROM AirCraft WHERE AirCraftCat = '전투기' AND AirCraftCat = '폭격기'));
Empty set (0.00 sec)

 

- 6번문제와 비슷하게 교집합 조건을 만족하기 위해 AND를 사용하였다.

 

 

8. 폭격기를 운항하지 않는 나이가 40세 이상인 파일럿의 파일럿번호를 구하세요.

mysql> SELECT PilotNo FROM Pilot1 WHERE Age > 40 AND PilotNo IN (SELECT PilotNo FROM Flight WHERE AirCraftNo NOT IN (SELECT AirCraftNo FROM AirCraft WHERE AirCraftCat = '폭격기'));
Empty set (0.01 sec)

 

- 두 가지의 교집합 조건을 만족시키기 위해서 7번문제에서 이용한 AND 를 이용했다.