SQL(2/3) Basics of Relational Algebra and joins for Database system week11
관계데이터 연산, 데이터베이스 시스템 11주
Goals:
관계대수와 관계해석의 차이를 설명할 수 있다. (can explain the difference between relational algebra and relational calculus)
관계연산을 사용해서 질의문의 동작과정을 표현할 수 있다. (can represent the operation process of a query using relational operations.)
관계해석을 사용해서 질의문을 표현할 수 있다. (can express queries using relational calculus.)
Contents:
✏️ 관계대수(Relational algebra)
💡일반집합연산 (Set Theory Operations)
합집합, 교집합, 차집합, 곱집합
(Union, Intersection, Difference, Cartesian Product)
💡순수관계연산 (Relational operations)
- 셀렉트σ, 프로젝트π, 조인⋈, 디비전÷(Select, Project, Join, Division)
✏️조인(Join)
💡세타조인, 동등조인, 자연조인, 외부조인
(Theta Join, Equi Join, Natural Join, Outer Join)
✏️ 관계해석 (Relational calculus)
💡듀플 관계 해석(Tuple relational calculus)
💡도메인 관계 해석(Domain relational calculus)
✏️ 학습 정리 (Summary)
✏️ 관계대수(Relational algebra)
📍관계대수의 정의(Definition of Relational algebra)
관계대수는 관계형 데이터베이스에서 데이터를 처리하기 위한 형식적인 언어이다. 질의문(Query) 과 명령문(Command)를 이용해서 데이터를 조작하기 위한 수학적인 연산을 처리하는 것을 의미한다.
질의문(Query)이란? 데이터베이스에서 정보를 요청하고 조작하는 데 사용되는 명령문(command)이다. 주로 데이터베이스 사용자(나)가 데이터베이스와 상호 작용할 때 사용한다.
위의 예제처럼 질의문은 데이터베이스 사용자에게 친숙하고 읽기 쉽다. 데이터베이스 시스템은 사용자가 질의문을 보고 작성한 명령문(Command)을 "관계대수(Relational algebra)"가 수학적인 표기로 변환하여 요구사항을 처리하고 조작하여 결과를 보여주게 된다.
📍사용자가 요청한 질의문을 DBMS에서 처리하는 방법 (The process of handling user-requested queries by DBMS)
대화식 질의문(Interactive Query): 데이터베이스 시스템과의 대화형 인터페이스를 제공하여 사용자가 데이터를 조작할 수 있도록 돕는다
질의문 컴파일(Query Compiler): 사용자가 제공한 질의문을 데이터베이스 시스템이 이해할 수 있는 형식으로 해석한다.
최적화(Query Optimizer) 질의문을 가장 효율적으로 처리할 수 있는 방법을 결정하고 실행 계획을 작성한다.
실행(Runtime Database Processor) 최적화된 실행 계획에 따라 데이터베이스에서 정보를 검색하거나 조작한다.
결과 반환: 질의문의 결과를 사용자에게 반환한다.
📍관계대수의 역할(Role of Relational algebra)
릴레이션 조작에 대한 이론적 기초 제공(Providing Theoretical Foundation for Relation Manipulation): 관계대수는 릴레이션을 처리하고 조작하기 위한 이론적 기초를 제공한다. 이를 통해 데이터베이스에서 데이터를 검색, 삽입, 수정, 삭제하는 등의 연산을 수행할 수 있게된다.
데이터베이스 질의(Query) 구현 및 최적화(Implementation and Optimization of Database Queries): 관계대수는 데이터베이스 질의를 구현하는 데 사용된다. 데이터베이스 시스템은 사용자가 작성한 SQL 질의를 관계대수 표현식으로 변환하여 실행한다. 또한 관계대수는 질의를 최적화하는 데도 사용된다. 최적화 기법을 적용하여 데이터베이스 시스템이 효율적으로 질의를 처리할 수 있도록 한다.
RDBMS의 표준 언어인 SQL과의 관계(Relationship with SQL, the Standard Language of RDBMS): SQL(Structured Query Language)은 관계형 데이터베이스 관리 시스템(RDBMS)에서 가장 널리 사용되는 데이터베이스 질의 언어이다. SQL은 관계대수의 일부 연산을 사용하여 데이터를 처리하기 때문에 SQL은 관계대수의 개념을 기반으로 하며, 많은 SQL 문장이 관계대수 연산을 구현하는 데 사용된다.
📍관계대수의 특징(Feature of Relational algebra)
지난시간에서 배웠듯이 관계형 데이터베이스에서는 데이터가 릴레이션(또는 테이블)의 형태로 구조화되어 저장된다. 각 릴레이션은 튜플(또는 레코드)과 속성(또는 열)의 집합으로 이루어져 있다.
관계대수는 다음과 같은 특징을 가지고 있다.
릴레이션의 집합(A set of relations): 관계대수는 릴레이션을 처리하기 위한 연산의 집합이다. 각 연산은 하나 이상의 릴레이션을 입력으로 받고, 결과로도 릴레이션을 반환한다.
피연산자와 연산결과가 모두 릴레이션(Both operands and operation results are relations): 관계대수 연산의 입력과 출력은 모두 릴레이션이다. 따라서 이러한 연산을 통해 생성된 결과도 다시 릴레이션으로 표현된다.
절차적 언어(Procedural Language): 관계대수는 절차적인 언어로써, 연산자의 순차적인 적용을 통해 원하는 결과(릴레이션)를 얻는다. 즉, 사용자는 원하는 결과를 얻기 위해 각 연산을 순차적으로 적용하는 과정을 기술한다.- SQL(Structured Query Language)은 관계형 데이터베이스에서 데이터를 검색, 삽입, 수정, 삭제하기 위한 표준화된 비절차적 언어이다. 사용자는 SQL을 사용하여 데이터베이스에 대한 질의문을 작성하고, 데이터베이스 시스템은 해당 질의문을 해석하여 관계대수로 변환후, 연산을 사용하여 실제 데이터를 수행한 뒤 결과를 반환한다. 이러한 과정에서는 SQL이 비절차적인 언어로 사용자의 요구를 명세화하고, 관계대수가 절차적인 언어로 데이터 처리 절차를 정의하고 실행하게 된다.
이러한 특징들은 관계대수가 데이터베이스에서 데이터를 처리하는 데 사용되는 중요한 도구 중 하나임을 보여준다. 데이터베이스 시스템은 사용자가 쿼리를 바탕으로 작성한 관계대수 표현식 명령문을 해석하여 실행하고, 결과를 반환하여 사용자가 요청한 정보를 제공한다.
📍관계대수를 이용해 데이터를 처리하기 위해 할 이론들
(2 theories to apply for processing data using relational algebra)
- 아래의 이론들은 관계대수를 통해 데이터를 처리하고 관리하는 데 사용되며, 데이터베이스 시스템의 쿼리 작성과 실행에 중요한 역할을 한다. 오늘은 그 중에 2가지를 배울 예정이다.
💡1. 집합연산 (Set Theory Operations)
집합 연산은 릴레이션을 집합으로 간주하여 집합 연산을 그대로 적용하는 것을 의미한다. 이는 관계형 데이터베이스에서 흔히 사용되는 연산으로, 관계 대수에서 다루는 여러 가지 연산들 중 하나이다.
특히, 합병가능(Union Compatible)한 릴레이션에 대해서는 합집합(Union), 교집합(Intersection), 차집합(Difference) 등의 일반적인 집합 연산을 적용할 수 있다. 사진에는 빠졌지만 곱집합(Cartesian Product)도 포함된다.
합병가능한 릴레이션(Union Compatible)이란 두 릴레이션의 구조가 동일하고, 대응하는 속성의 도메인이 동일할 때 성립한다. 두 릴레이션의 차수(속성의 수)가 같고, 대응하는 속성 간의 도메인이 같아야 두 릴레이션은 합병 가능하다.
- 위는 합병가능한 릴레이션의 수학적인 표현을 보여주는 예제이다.
- 안에 들어있는 데이터의 값, 즉 타입이 같으면 된다 학번의 경우 숫자, 이름의 경우 문자열로 이루어졌기 때문에 합병가능한 릴레이션이 된다. 결론적으로 합집합(Union), 교집합(Intersection), 차집합(Difference) 등의 일반적인 집합 연산을 적용할 수 있다.
📌합집합(Union) R U S
- 릴레이션 R 또는 릴레이션 S에 속하는 모든 튜플의 집합
- 튜플의 갯수가 7개가 아닌 5개인 이유: 릴레이션의 특징인 중복이 존재하지 않는다가 적용되어 중복되는 튜플은 삭제가 되기 때문이다.
📌교집합(Intersection) R ∩ S
- 릴레이션 R 또는 릴레이션 S에 동시에 속해 있는 튜플의 집합
📌차집합(Difference) R - S
- 릴레이션 R 에는 있지만 릴레이션 S에는 없는 튜플의 집합
📌곱집합(Cartesian Product)
- 카티션 프로덕트는 두 개의 릴레이션에서 각각 한 개의 튜플을 선택하여 결합한 모든 가능한 조합을 포함하는 새로운 릴레이션을 생성하는 것이다.
- 곱집합은 주어진 두 릴레이션의 각 튜플을 조합하여 새로운 튜플을 생성하는 데 사용됩된다. 카티션 프로덕트에 관련된 릴레이션들은 서로 합병가능하지 않아도 되므로, 차수나 도메인이 달라도 상관 없다.
예를 들어, R(A1, A2)와 S(B1, B2)라는 두 릴레이션이 있을 때, 카티션 프로덕트 R x S는 R에 있는 각 튜플과 S에 있는 각 튜플을 모두 조합하여 새로운 튜플을 생성하게 되는데 이때, 새로운 튜플은 (r1, s1), (r1, s2), (r2, s1), (r2, s2)와 같은 형태를 가진다.
- 총 튜플의 갯수 결과 학생(4) * 과목(3) = 12개의 튜플이 생성되었다.
📌곱집합이 중요한 이유(Why is Cartesian Product important)
조인 연산의 기초(Basic of Join operation): 관계형 데이터베이스에서 데이터를 조합하고 쿼리하기 위한 주요 연산 중 하나는 조인이다. 카티션 프로덕트는 조인 연산을 수행하기 위한 전처리 단계로 사용된다.
조합 가능한 모든 가능성(Exploring All Possible Combinations): 카티션 프로덕트는 두 개의 릴레이션 간에 가능한 모든 조합을 생성한다. 이를 통해 우리는 두 개의 테이블 사이의 모든 관계를 확인할 수 있으며, 이는 데이터 분석 및 쿼리 작업에 매우 중요한 역할을 한다.
데이터 병합과 확장(Data Merge and Expansion) 카티션 프로덕트를 사용하면 두 개의 테이블 간의 관계를 발견하고 병합하는 것이 가능하다. 이는 데이터를 확장하고 새로운 통찰력을 얻는 데 도움이 된다.
이러한 이유로 카티션 프로덕트는 관계형 데이터베이스 및 관계형 대수에서 매우 중요한 개념이 된다.
💡2. 순수 관계 연산 (Relational database specific operations)
순수 관계 연산은 릴레이션의 조작을 위해서 특별히 정의된 연산이다. 이러한 연산은 일반적으로 관계형 데이터베이스에서 사용된다.
📌셀렉트(Select, σ ) 간단히 말하면 튜플을 가져온다. σ는 시그마라고 읽는다. 주어진 조건을 만족하는 튜플(행) 들만 걸러내는 연산이다. 릴레이션에서 선택조건을 만족하는 튜플들의 집합을 선택하는데 사용된다.
- 셀렉트 예제1: 조건이 없으면 모두 가져오게 된다.
📌프로젝트(Project, π) 셀렉트는 가로 행을 가져오는 것이고 프로젝트는 세로 열을 가져오는 것이라고 생각하면 편하다. 프로젝트 연산은 릴레이션(테이블)의 일부 열만을 선택하고 나머지는 버리는 연산이다.
- 조건이 없다.
- 위에 예제처럼 겹치는 세로 열은 사라진다.
📌디비전(Division, ÷)
두 개의 릴레이션에서 특정 조건을 충족하는 튜플을 선택하는 연산이다. 이때, 두 릴레이션은 일반적으로 R(X)와 S(Y)라는 형태를 가집니다. 여기서 X ⊃ Y(X가 Y를 포함한다는 의미)이며, 즉 R의 속성이 S의 속성값을 모두 가진 튜플에서 S가 가진 속성을 제외한 속성만을 구하려고 할 때 디비전 연산이 사용된다.
간단히 말해서, 디비전 연산은 S(Y)의 모든 튜플에 연관되어 있는 R(Z)의 튜플을 선택하는 것입니다. 다시 말해, S(Y)에 있는 각 튜플이 R(Z)의 모든 튜플과 관련이 있을 때에만 해당 R(Z)의 튜플을 선택한다.
이 연산은 주로 두 릴레이션 간의 관계를 분석하고, 한 릴레이션에 속하지만 다른 릴레이션에도 관련된 튜플을 찾는 데 사용된다. 아래 그림을 예제로 들어보면
디비전은 사진을 보면 이해가 빠르다.
형광펜으로 표시한 것처럼 등록 릴레이션에서 과목1에 해당하는 릴레이션의 학번을 보여준다. 나머지 과목들도 마찬가지 이다.
✏️조인(Join, ⋈)
조인 연산은 두 릴레이션으로부터 관련된 튜플들을 결합하여 하나의 튜플로 만드는 연산이다. 이 연산은 두 개 이상의 릴레이션을 갖는 어떤 관계 데이터베이스에 대해서 릴레이션 간의 관계를 처리할 수 있게 한다. 오늘 강의에서 가장 중요한 개념이다.
- 형식 설명: 릴레이션 R과 S를 조인한다. 그 사이엔 조건이 존재한다. 조건이 없다면 카티션 프로덕트의 결과를 적용한다. 하지만 해당 형식은 조건이 있으므로 R과 S의 카티션 프로덕트 후 A θ B 의 조건만 남겨놓는다는 뜻이다.
학생.학번 = 등록.학번의 조건을 만족하는 튜플만 남겨놓았다. 조인 연산을 하게 되면 2개로 나눠졌던 릴레이션이 하나의 릴레이션으로 보기 좋게 표현이 가능하게 된다.
결과에다 프로젝트 연산을 적용하여 원하는 열만 볼수 있게 집합을 생성할수도 있다.
💡Inner joins
- 두 개의 테이블 간에 일치하는 행만을 반환하는 조인 유형이다. 아래의 3가지가 속한다.
📌동등조인(Equi join)
특정 열들 간의 값이 서로 일치하는 경우에만 행을 결합한다.
θ 가 “=” 인 조인을 동등조인(Equi join)이라 한다.
📌세타조인(Theta join)
- 비교연산자 {=. <, ≤, ≥, >, ≠} 를 일반화 해서 θ 로 표현한 것이다.
📌자연조인(Natural join)
동일조인에서 중복되는 애트리뷰트를 제거한 것이다. ⋈N 으로 표시한다.
R과 𝑆는 릴레이션
𝑅.𝑧와 𝑆.𝑧는 조인할 두 릴레이션의 애트리뷰트 𝑧
×는 카티션 프로덕트(곱집합) 연산자
σ는 선택(Selection) 연산자
π는 프로젝션(Projection) 연산자
𝑥는 릴레이션 𝑅의 애트리뷰트 집합
𝑦는 릴레이션 𝑆의 애트리뷰트 집합
𝑧는 조인 애트리뷰트
이 수식은 다음과 같이 해석될 수 있다. 조인 조건 𝑅.𝑧=𝑆.𝑧를 충족하는 𝑅과 𝑆의 튜플을 결합한 후, 중복되는 애트리뷰트를 제거하여 애트리뷰트 집합 𝑥와 𝑦를 가진 결과를 생성한다.
- 카디션 프로덕트 실행 후 예제의 조건이었던 R.C = S.C를 수행한다. 결과 값에서 중복된 애트리뷰트 C1을 제거하면 최종 조인 결과를 확인할 수 있다.
- 조인 결과에서 중복된 학번을 삭제하였다.
- 조건 설명: 학생의 학과코드와 학과의 학과코드가 같은 것만 추출한다.
📌외부 조인(Outer join)
+ 예제에는 빠졌지만 완전 외부 조인(full other join)도 존재한다.
외부조인은 조인 연산 중에 하나로, 다른 릴레이션에 대응되는 튜플이 없는 튜플이나 널 값을 가지는 경우에도 조인 결과에 포함되는 확장된 조인 연산이다. 간단히 말하면 일치하지 않는 행도 반환하는 특별한 유형의 조인이다.
예를 들어, 왼쪽 릴레이션의 모든 튜플이 오른쪽 릴레이션의 어떤 튜플과도 일치하지 않을 때, 외부조인은 왼쪽 릴레이션의 튜플을 결과에 포함시키고 오른쪽 릴레이션의 해당 부분에는 널 값을 가지게 한다. 외부조인은 이러한 상황에서도 결과를 생성할 수 있도록 한다.
예를 들어, 왼쪽 릴레이션 A와 오른쪽 릴레이션 B가 있을 때, A와 B의 조인 결과에 대한 외부조인은 A의 모든 튜플을 결과에 포함하고, B에 대응되는 값이 없는 경우 해당 부분은 널 값을 가지게 된다.
(왼쪽 or 오른쪽) 외부조인(left or right outer join) 왼쪽(오른쪽)에 있는 릴레이션의 튜플을 결과 릴레이션에 포함하는 외부 조인
완전 외부 조인(full outer join) 양쪽에 있는 릴레이션의 튜플을 결과 릴레이션에 포함하는 외부조인
더 자세한 조인은 나중에 더 배울 예정이라고 하신다.
📍질의문을 이용하여 데이터 베이스 구현하기(Implementing databases using queries in relational algebra)
- 삽입이라는 단어는 집합연산과 관계가 있다.
- '삭제'는 차집합 연산자(-)를 이용해서 처리할 수 있다.
- 이름-학생릴레이션, 성적-등록릴레이션 조인으로 처리할 수 있다. (아래참조)
✏️관계해석 (Relational calculus)
📍관계해석의 정의(Definition of Relational calculus)
관계해석을 깊게 이해하면 어렵기 때문에 오늘 강의에서는 간단한 개념만 배운다. 관계해석은 비절차적인 방법으로, 결과를 얻기 위한 절차를 기술하는 것이 아니라 원하는 정보가 무엇인지를 선언하는 방식(Declaring the desired information)이다. 결과를 얻기 위한 절차는 관계대수에서 다룬다. (절차적인 방법)
이렇게 하면 데이터베이스 시스템이 그 결과를 어떻게 얻을 것인지를 결정하게 된다. 이 방법은 일반 사용자가 데이터에 접근하는 데 유용하며, 구체적인 조작 방법을 몰라도 결과를 얻을 수 있다.
📍관계해석의 종류(Types of Relational calculus)
튜플 관계 해석과 도메인 관계 해석이 존재한다.
📌 튜플 관계 해석 (Tuple Relational Calculus):
여러 튜플 변수를 기초로 한다. 각 튜플 변수는 주로 특정 릴레이션의 값을 범위로 가진다.
- 대표적인 예시로는 QUEL(QUEry Language)이 있다.
- 위에 보이는 형태로 이루어진다고만 이해하면 된다.
관계대수로 처리가 가능하지만 관계 해석 식으로 쓰게될 경우 예제이다.
예제를 보다시피 원하는 것만 요청한다.
📌도메인 관계 해석 (Domain Relational Calculus)
- 여러 도메인 변수를 기초로 한다. 도메인 변수들은 릴레이션 대신 도메인 상에서 정의된다.
- 대표적인 예시로는 QBE(Query By Example)가 있다.
- 튜플 관계 해석과 비슷해 보이지만 다르다.
이 두 가지 방법은 데이터베이스 시스템에서 쿼리를 작성하고 실행하는 데 사용된다.
학습 정리(Summary)
