4장 관계 대수와 SQL1

2023-03-24 데이터베이스 수업

4장 관계 대수와 SQL

관계 데이터 모델에서 지원되는 두 가지 정형적인 언어

관계 해석

• 원하는 데이터만 명시하는 선언적 언어
  EX) 셀렉트 연산, 프로젝트 연산

**관계 대수

: 질의를 수행할 것인지를 명시하는 절차적 언어, 쿼리문 이용

• 상용관계 DBMS들에서 널리 사용되는 SQL의 이론적 기초
• SQL을 구현하고 최적하하는 DBMS의 내부언어
• 데이터를 조회하는 여러 방법을 질의문이 그 방법 중 최적의 방법을 찾아줌.

관계 데이터 모델에서 지원되는 관계 대수는 관계형 대수(Relational algebra)이다. 관계 대수는 관계형 데이터베이스의 쿼리를 수행하기 위해 사용되는 수학적인 표기법! 관계 대수는 다양한 연산자들을 사용하여 관계형 데이터베이스의 릴레이션(Relation)에서 데이터를 조작하고 선택한다.

이러한 관계 대수 연산자들은 SQL과 같은 관계형 데이터베이스 쿼리 언어에서 사용된다. 데이터베이스 시스템은 이러한 연산자들을 최적화하여 더 효율적인 쿼리 실행을 가능하게 함.

⭐ SQL

• DBMS의 표준 질의어
• 릴레이션 정의, 관계 데이터베이스에서 정보 검색, 갱신, 무결성 제약조건들을 명시

관계 대수

• 기존의 릴레이션으로부터 새로운 릴레이션 생성
• 하나의 릴레이션이나, 이미 만들어진 식을 같이 연산자를 적용하여 복잡한 관계식을 만듬.
• 산술 연산자와 유사하게 단일, 두 개의 릴레이션을 입력으로 받아 하나의 결과 릴레이션 생성
• 릴레이션과 릴레이션 사이의 연산자를 만들고, 그 결과에 또 다른 연산자를 붙여서 복잡한 관계식 형성 가능
  EX) 숫자가 릴레이션, 관계 대수 연산자는 산술 연산자로 비유 가능!

☑️ 관계 대수 연산자 목록

➡️ 선택(Selection): 특정 릴레이션에서 조건에 맞는 튜플(Tuple)들을 선택합니다. 일반적으로 SELECT 문으로 표현됩니다.

➡️ 투영(Projection): 특정 릴레이션에서 필요한 속성(Attribute)만 선택합니다. 일반적으로 SELECT 문에서 특정 컬럼만 선택하는 것과 비슷합니다.

➡️ 교차곱(Cross Product): 두 개의 릴레이션에서 모든 가능한 조합의 튜플을 생성합니다. 일반적으로 JOIN 문에서 사용되는 것과 비슷합니다.

➡️ 자연조인(Natural Join): 두 개의 릴레이션에서 같은 이름을 가진 속성들을 기준으로 조인합니다.

➡️ 합집합(Union): 두 개의 릴레이션에서 중복을 제거하고 합칩니다.

➡️ 교집합(Intersection): 두 개의 릴레이션에서 공통된 튜플만 선택합니다.

➡️ 차집합(Difference): 두 개의 릴레이션에서 첫 번째 릴레이션에는 있지만 두 번째 릴레이션에는 없는 튜플만 선택합니다.

필수적인 연산자: 선택(Selection), 투영(Projection), 합집합(Union), 차집합(Difference), 카디션 곱(Cartesian product)

선택(Selection)

• 릴레이션 R에서 셀렉션 조건식을 만족하는 투플만을 선택하여 반환
• 단항 연산자 (릴레이션 하나에 적용)
• 애트리뷰트의 수 (릴레이션의 차수)
• 실렉션 조건 = 프레디키트
• 비교 연산자(<,>,=)와 부울 연산자(AND, OR, NOT) 포함 가능(참, 거짓 판단 가능)

EX) 하나의 릴레이션에서 내가 원하는 조건만을 만족하는 투플 반환, DNO=3인 투플만을 반환하라.

투영(Projection)

• 릴레이션 R에서 애트리뷰트 리스트에 나열된 애트리뷰트 값만 반환
• 중복이 존재하는 릴레이션(키 값이면 중복이 없음!)에서는 중복을 제거
• 한 릴레이션의 애트리뷰트들의 부분 집합을 구함(차수가 같거나 작아질 수 있음, 그러나 카디널리티는 그대로!)
• 애트리뷰트 순서대로 가져옴
• 실렉션 결과엔 중복 투플 존재 가능하지만 프로젝션에서는 중복된 투플 존재 가능

집합 연산자

• 합집합(Union), 차집합(Difference), 교집합
  : 여기서 합집합, 차집합만 필수 연산자
• 집합 연산자의 입력으로 사용되는 두 릴레이션은 합집합 호환이어야함.

합집합 호환

• R1의 애트리뷰트 수 = R2의 애트리뷰트 수, 각각의 애트리뷰트에 대해서 각 도메인이 대응되어야함.
  EX) R1(A1, A2, ... An)과 R2(B1, B2, ... Bn)에서 domain(Ai)=domain(Bi) 도메인이 같아야 합쳐질때 호환 가능

• 관계 대수에서 합집합 호환(Union Compatibility)은 두 개의 릴레이션(Relation)이 합집합 연산(Union operation)을 수행할 수 있는 조건을 말합니다.

• 합집합 연산은 두 개의 릴레이션에서 중복을 제거하고 합치는 연산으로, 두 개의 릴레이션이 합집합 호환을 만족할 때만 수행이 가능합니다.

• 두 개의 릴레이션은 합집합 호환을 만족하려면 다음과 같은 조건을 만족해야 합니다.

1) 두 개의 릴레이션의 속성 수(Attribute)가 같아야 합니다.
2) 두 개의 릴레이션에서 같은 위치에 있는 속성의 도메인(Domain)이 같아야 합니다.
(도메인이란, 속성이 가질 수 있는 값의 범위를 말합니다.)

EX) 다음과 같은 두 개의 릴레이션 R과 S가 있다고 가정해봅시다.

R(A, B, C)
S(D, E, F)
☑️ 이 경우, 두 개의 릴레이션은 합집합 호환을 만족하지 않습니다. 이유는 릴레이션 R과 S의 속성 수가 다르기 때문입니다.

반면에, 다음과 같은 두 개의 릴레이션 R과 S는 합집합 호환을 만족합니다.

R(A, B, C)
S(A, D, E)
☑️이유는 두 개의 릴레이션에서 같은 위치에 있는 속성 A의 도메인이 같기 때문입니다. 따라서, 이 두 개의 릴레이션은 합집합 연산을 수행할 수 있습니다.

두 개의 릴레이션 R과 S에서 각각 다음과 같은 데이터가 저장되어 있다면,
R
1 a b
2 c d

S
1 e f
3 g h

두 개의 릴레이션에서 합집합 연산을 수행하면 다음과 같은 결과가 나옵니다.
R ∪ S
1 a b
2 c d
3 g h
1 e f

위의 결과에서 중복되는 튜플은 하나만 선택되고, 모든 튜플이 하나의 릴레이션에 모두 포함됩니다.

합집합 연산자

• 합집합 결과 릴레이션에서 중복된 투플은 제외
• R 또는 S의 차수와 같고, 결과 릴레이션의 애트리뷰트 이름은 R의 이름과 같거나 S의 이름과 같음

교집합 연산자

• 두 릴레이션 R과 S의 교집합은 결과 릴레이션이 차수가 R 또는 S와 같고, R 또는 S의 애트리뷰트와 이름 동일

차집합 연산자

• 두 릴레이션 (R - S)
• 두 릴레이션 R과 S의 차집합은 결과 릴레이션이 차수가 R 또는 S와 같고, R 또는 S의 애트리뷰트와 이름 동일

카티션 곱 연산자

• 카디날리티가 i인 릴레이션과 카디날리티가 j인 릴레이션의 카티션 곱 R x S는 차수가 n+m이고, 카디날리티가 i*j이고, 애트리뷰트가 릴레이션 R과 S의 모든 애트리뷰트일때, R과 S의 투플의 모든 가능한 조합으로 이루어짐

• 차수가 N + M (합쳐져서 모두 들어옴) 크기가 매우 크고, 실제로 원하는 결과는 카티션 곱의 결과의 일부인 경우가 대부분이기에 효율적이지 않음

R × S = {(r, s) | r ∈ R, s ∈ S}

여기서 (r, s)는 R과 S의 튜플 쌍을 나타내는 것이고, '|'는 '이며'를 의미해. 즉, R × S는 R의 모든 튜플과 S의 모든 튜플을 조합한 결과를 나타내는 릴레이션임을 알 수 있음

관계 대수의 완전성

• 실렉션, 프로젝션, 합집합, 차집합, 카티션 곱은 관계 대수의 필수 연산자 ➡️ 이 연산자들은 모두 SQL로 표현 가능!

선택(Selection): WHERE 절을 사용해 특정 조건을 만족하는 행(row)만을 선택합니다.

SELECT *
FROM table
WHERE condition;

투영(Projection): SELECT 절을 사용해 특정 컬럼(column)만을 선택합니다.

SELECT column1, column2, ...
FROM table;

합집합(Union): UNION 연산자를 사용해 두 개의 쿼리 결과를 합칩니다.

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

차집합(Difference): EXCEPT 연산자를 사용해 두 개의 쿼리 결과의 차집합을 구합니다.

SELECT column1, column2, ...
FROM table1
EXCEPT
SELECT column1, column2, ...
FROM table2;

카티션 곱(Cartesian Product): CROSS JOIN 절을 사용해 두 개의 테이블의 카티션 곱을 구합니다.

SELECT *
FROM table1
CROSS JOIN table2;

• 다른 관계 연산자들은 필수적인 관계 연산자를 두 개 이상 조합하여 표현 가능
• 임의의 질의어가 적어도 필수적인 관계 대수 연산자들만큼의 표현력을 갖고 있으면 관계적으로 완전하다고 말함

⭐ 조인 연산자

• 두 개의 릴레이션으로 부터 연관된 투플들을 결합하는 연산자
• 세타 조인, 동등 조인, 자연 조인, 외부 조인, 세미 조인

세타 조인과 동등 조인

• 애트리뷰트는 릴레이션의 모든 애트리뷰트가 오고, 차수는 n+m, 조인 조건을 만족하는 투플들로 이루어진 릴레이션
• 세타 조인은 =, <, > 등
• 동등 조인은 세타 조인 중에서 비교 연산자가 =인 조인

자연 조인(*)

• 동등 조인의 결과에서 조인 애트리뷰트를 한 개 제외한 조인

• 가장 자주 사용된다!

• 대부분의 DBMS에서 질의는 실렉션, 프로젝션, 자연 조인으로 표현 가능

• 자연 조인(Natural Join)은 두 개의 테이블에서 공통으로 가지고 있는 컬럼을 기준으로 조인하는 것. 이를 수행하기 위해서는 JOIN 절을 사용하며, JOIN 절 뒤에 USING 절을 붙여 공통 컬럼을 지정.

예시를 위해 아래와 같이 두 개의 테이블을 생성하겠습니다.

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  department_id INT
);

CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(50)
);

이제 employees와 departments 테이블에서 공통으로 가지고 있는 department_id 컬럼을 기준으로 자연 조인을 수행해 보겠습니다.

SELECT *
FROM employees
NATURAL JOIN departments;

위의 쿼리문은 employees와 departments 테이블에서 department_id 컬럼을 기준으로 자연 조인을 수행. 이때 SELECT 문에는 employees와 departments 테이블의 모든 컬럼이 포함. 만약 모든 컬럼이 아닌 일부 컬럼만을 선택하고 싶다면, SELECT 문에서 직접 지정해주면 된다!

SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
NATURAL JOIN departments;

위의 쿼리문은 employees와 departments 테이블에서 department_id 컬럼을 기준으로 자연 조인을 수행하고, 그 결과에서 employees.employee_id, employees.first_name, departments.department_name 컬럼만을 선택합니다.

디비전 연산자

• 차수가 n+m인 릴레이션 R(A1,A2, ... An, B1,B2, ... Bm)과 차수가 m인 릴레이션 S(B1,B2, ... Bm)의 디비전 R ÷ S는 차수가 n이고, S에 속하는 모든 투플 u에 대하여 투플 tu(t와 u 결합)가 R에 존재하는 t의 집합

-관계 데이터베이스에서 디비전(Division) 연산자는 두 개의 릴레이션을 연산하여 새로운 릴레이션을 생성하는 연산자입니다. 디비전 연산자는 일반적으로 특정 속성 값들의 모든 조합이 다른 릴레이션에 대해 존재할 때 사용됩니다.

R
-----
A  B
1  2
1  3
2  3
3  4

S
-----
B  C
2  1
2  2
3  1

R의 모든 A값에 대해 S의 B값이 모두 포함되는 경우에 대해 검색하고 싶다면 디비전 연산자를 사용할 수 있습니다. 이 경우, R을 S로 디비전하여 R/S 를 생성합니다.

R / S
-----
A
1

위의 결과는 R의 모든 A값에 대해 S의 B값이 모두 포함되는 경우를 의미합니다. 즉, R과 S의 조인 결과에서 A 값이 1인 행만을 선택한 것과 같습니다.

디비전 연산자는 다른 연산자와 달리 SQL에서 직접적으로 제공되지 않기 때문에, 보통 다른 연산자들을 조합하여 디비전 연산을 수행합니다. 예를 들어, 위의 예시에서 디비전 연산을 수행하려면 다음과 같은 SQL 쿼리문을 사용할 수 있습니다.

SELECT DISTINCT R.A
FROM R
LEFT JOIN S ON R.B = S.B
GROUP BY R.A
HAVING COUNT(*) = (SELECT COUNT(*) FROM S);

위의 쿼리문에서는 R과 S를 조인한 후, R의 각 A 값에 대해 S의 B값을 COUNT한 결과가 S의 레코드 수와 같은 경우를 찾고 디비전 연산을 수행함.