[SW Academy] 01.10

관계 대수와 관계 해석

관계 대수

관계 대수 개요

• 관계 대수식(Relational Algebra Expression)은 연산자들의 모임을 사용해서 구성된 대수식
• 기존의 릴레이션들로 부터 새로운 릴레이션을 생성
• 하나 또는 두 개의 릴레이션을 파라미터로 하나의 릴레이션 인스턴스를 반환
  
• 연산자들을 조합해서 복잡한 질의를 만들기 쉬움
• 릴레이션, 단항(Unary) 연산자와 이항(Binary) 연산자로 순환적으로 정의
• 셀렉션, 프로젝션, 합집합, 차집합, 카티션 프로덕트등의 기본 연산자의 조합으로 구성
• 관계 질의는 연산자의 적용 순서를 통해 원하는 답을 구하는 계산 절차를 한 단계씩 묘사한 것
  • 대수식은 질의 수행을 위한 계획으로 생각할 수 있음
  • 관계 시스템은 대수식을 질의 수행 계획으로 표현하는데 이용

셀렉션과 프로젝션

• 릴레이션에서 투플들을 선택(Selction)할 수 있는 연산자 시그마( δ )
• 필드들을 추출(Projection)할 수 있는 연산자 파이 ( π )
• 단일 릴레이션에 있는 데이터를 조작하는데 사용
• 셀렉션 연산자 δ는 셀렉션 조건을 통해 얻을 투플들을 명세
  • 비교 연산자 <, ⇐, =, ≠, >= > 중의 하나를 op라고 할 때 
    애트리뷰트 op 상수 또는 애트리뷰트 op 애트리트뷰트 형태의 항을 불리언으로 조합
• 프로젝션 연산자 π는 릴레이션의 필드들을 추출
  ex)
  아래와 같은 릴레이션 스키마가 있고,
  파일럿(파일럿번호: 정수, 파일럿이름: 문자열, 등급: 정수, 나이: 실수)
  릴레이션 스키마의 각 인스턴스 Pilot1이 아래와 같다고 할 때,
  

1. δ등급>5(Pilot1) 이라면
   등급이 5보다 큰 투플들만 산출된다.
2. π파일럿이름,등급(Pilot1) 이라면
   파일럿이름과 등급만 산출되고 나머지 필드는 버린다.
3. π파일럿이름,등급(δ등급>5(Pilot1)) 이라면
   등급이 5보다 큰 투플들의 파일럿 이름과 등급만 산출된다.

집합 연산

• 합집합, 교집합, 차집합, 카티션 프로덕트와 같은 표준 집합 연산
• 합집합(Union, ∪)
  인스턴스 R이나 인스턴스 S 모두에 속하는 투플들을 포함하는 릴레이션 인스턴스를 만듦
  R과 S는 합병 가능해야 하며, 결과 스키마는 R의 스키마와 동일
• 교집합(Intersection, ∩)
  R ∩ S는 양쪽에 함께 속하는 모든 투플로 구성된 릴레이션 인스턴스를 만듦
• 차집합(Set-different, – )
  R – S는 R에는 속하고 S에는 속하지 않는 투플로 구성된 릴레이션 인스턴스를 만듦
• 카디션 프로덕트(Cartisian Product, X)
  R X S는 R의 모든 필드와 S의 모든 필드를 순서대로 가지는 스키마의 릴레이션 인스턴스를 만듦
  R X S는 r∈R, s∈S 쌍에 대하여 투플 <r, s>를 하나씩 가짐
  (크기가 매우 클 수 있고, 실제로 원하는 결과는 카티션 프로덕트 결과의 일부인 경우가 대부분이기 때문에 연산 자체는 많이 쓰이지 않는다)

이름 바꾸기

• 릴레이션에서 필드의 이름을 변경(Renaming)할 수 있는 연산자 로우( ρ )
• 관계 대수식 안에서 이름 충돌이 발생할 수 있음
• 관계 대수식 안에서 릴레이션 인스턴스의 이름을 주는 것이 편리함
• 긴 대수식을 작은 부분으로 나누어 결과 인스턴스에 이름을 줄 수 있도록 하는 것이 편리함
  ex) 릴레이션 스키마의 각 인스턴스 Pilot1이 아래와 같다고 할 때,
  
  ρ(Pilot3σ등급>5(Pilot1)) 이라면
• 5등급보다 큰 필드만 나오고, 결과 릴레이션의 이름이 Pilot3가 된다.

조인

• 둘 이상의 릴레이션으로 부터 정보를 조합하는 연산 (⋈)
• 유도된 연산으로, 카티션 프로덕트와 프로젝션 연산을 함께 실행하여 얻은 결과와 동일
  

SQL

구성

• 데이터 조작어(Data Manipulation Language – DML)
  • 데이터베이스의 데이터를 조회하거나 검색하기 위한 명령 및 데이터베이스 테이블의 데이터에 변형(삽입, 수정, 삭제)을 가하는 질의어
• 데이터 정의어(Data Definition Language – DDL)
  • 테이블, 무결성 등의 데이터 구조를 정의하는데 사용되는 명령어들로 데이터 구조와 관련된 질의어
• 데이터 제어어(Data Control Language – DCL)
  • 데이터베이스에 접근하고 개체들을 사용하도록 권한을 주고 회수하는 질의어
• 트랜잭션 제어어(Transaction Control Language – TCL)
  • 논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 트랜잭션별로 제어하는 질의어

DDL

• 데이터 정의 언어
  • 데이터를 정의하는 컴퓨터 언어 또는 컴퓨터 언어 요소
  • SQL에서는 관계 데이터베이스의 구조를 정의하는 SQL 언어의 부분집합
• SQL에서의 DDL
  • 관계 데이터베이스의 구조를 정의
  • 행, 속성, 관계, 인덱스, 뷰, 저장 프로시저 등 데이터베이스의 구조와 객체 정의

DDL 구문

• SQL 데이터 정의 언어 지침
  • CREATE
    릴레이션(테이블), 뷰, 인덱스, 저장 프로시저 등 데이터베이스 객체 생성
  • DROP
    존재하는 데이터베이스 객체 삭제
  • ALTER
    존재하는 데이터베이스 객체 수정
  • TRUNCATE
    테이블 내 데이터의 완전 삭제

DML

DML 구문

• SQL 표현의 기본 구조는 SELECT, FROM, WHERE 세 개의 절로 이러우짐
• SELECT
  • 관계 대수에서 프로젝션(Projection - π) 연산과 일치, 결과에 나타날 속성을 나열하는데 사용됨
• FROM
  • 관계 대수의 카티션 프로덕트에 해당하며, 질의에서 조회할 릴레이션을 나열
• WHERE
  • 관계대수에서 셀렉션(Selection - σ) 연산과 일치, FROM 절에 나타나는 릴레이션 속성들의 조건으로 구성

SELECT 절

• 형식 질의 언어는 릴레이션이 집합이라는 수학적인 개념에 바탕을 두고 있음
  • 중복된 투플은 릴레이션에서 나타날 수 없음
  • 중복성 제거는 시간 소비성 작업이므로, SQL은 프로젝션 결과 뿐 아니라 릴레이션에서도 중복을 허용
  • 중복이 없어야 하는 결과 집합 쿼리인 경우 DISTINCT 키워드를 사용
  • all 키워드를 사용하여 중복을 허용, 중복을 허용하는 것이 기본
• FROM 리스트에 나오는 테이블들로 부터 산출될 필드 이름들의 리스트
  • 기호를 사용하여 모든 속성들을 사용할 수 있음
• 상수나 투플들의 속성에 적용되는 +, -, *, / 연산자를 포함하는 산술적 표현들을 사용할 수 있음
• 데이터 타입에 적용할 수 있는 산술적인 함수를 허용

FROM 절

• 범위 변수를 사용하여 릴레이션의 별칭을 정의할 수 있음
• 리스트에 있는 릴레이션들의 카티션 프로덕트를 정의
  • 자연 조인은 카티션 프로덕트, 셀렉션 연산, 프로젝션 연산으로 수행됨
  • FROM절에서 간단히 자연 조인을 SQL 연산으로 표현할 수 있음
• 하나 이상의 릴레이션 스키마에서 나타나는 속성의 모호성을 피하기 위해 relation-name.attribute.com 표기법을 사용
• FROM 절에서 외부 조인을 수행할 수 있는 확장을 포함함

WHERE 절

• WHERE 절의 조건식으로 셀렉션 연산을 수행
  • 식 op 식  형태의 조건들을 조합한 불리언 식으로, op는 비교 연산자 {<, ⇐, =, <>, >=, >} 중의 하나
  • 범위 연산을 위해 BETWEEN 비교 연산을 제공

집합 연산

UNION

• 관계 대수식의 합집합에 대응
• 두 SQL 질의의 결과가 합병 가능 조건이면 UNION 연산 가능
• SELECT 절과는 다르게 기본적으로 중복을 제거함
  • UNION ALL 을 사용하여 모든 중복을 유지할 수 있음

INTERSECT

• 관계 대수식의 교집합에 대응
• 두 SQL 질의의 결과가 합병 가능 조건이면 INTERSECT 연산 가능
• SELECT 절과는 다르게 기본적으로 중복을 제거함
  • INTERSECT ALL 을 사용하여 모든 중복을 유지할 수 있음

EXCEPT

• 관계 대수식의 차집합에 대응
• 두 SQL 질의의 결과가 합병 가능 조건이면 EXCEPT 연산 가능
• SELECT 절과는 다르게 기본적으로 중복을 제거함
• EXCEPT ALL을 사용하여 모든 중복을 유지할 수 있음

서브 쿼리

• 단일 질의 안에 질의가 포함된 형태의 쿼리
  • 관계 대수 질의 결과는 릴레이션을 반환함
  • 서브 쿼리의 결과를 주 쿼리에서 받아서 처리는 형식으로, 포함된 쿼리(Nested Query) 라라고도 부름
• 서브 쿼리 (Subquery)
  • 가장 많이 사용되는 형태로, WHERE 절에서 셀렉션 연산의 값을 산출하기 위해 사용하는 질의
• 인라인 뷰(Inline View)
  • FROM 절에서 질의의 결과를 테이블처럼 사용하는 용도의 질의
• 스칼라 서브 쿼리(Scala Subquery)
  • SELECT 문에서 사용되는 서브 쿼리로, 주로 계산 결과값을 쿼리 결과에 포함시키는데 사용되는 질의
• 연관 서브 쿼리
  • 서브 쿼리에서 주 쿼리의 필드값과 연관되어 사용하는 질의

포함된 서브 쿼리

• 단일 질의 안에 질의가 포함된 형태의 일반적인 쿼리로, 서비 쿼리의 결과를 변수처럼 사용
• 단일행 서브 쿼리(Single Row Subquery)
  • 쿼리의 결과가 하나의 Row만을 산출
• 다중행 서브 쿼리(Multi Row Subquery)
  • 쿼리 결과가 여러 Row를 산출
• 다중 컬럼 서브 쿼리(Multi Column Subquery)
  • 전체 테이블을 비교하는 것 보다 일부 테이블만을 불러와 그 중에서 셀렉션하는 것이 비교 횟수가 적음

인라인 뷰

• 서브 쿼리가 FROM 절 안에서 사용되는 경우 인라인 뷰(Inline View)라고 함
• FROM 절에서 사용된 서브 쿼리의 결과가 하나의 테이블에 대한 뷰 처럼 사용됨
  • 서브 쿼리와 테이블 조인이 이루어질 경우 쿼리의 가독성이 떨어짐
  • 전체 테이블을 비교하는 것 보다 일부 테이블만을 불러와 그 중에서 셀렉션하는 것이 비교 횟수가 적음

스칼라 서브쿼리

• SELECT 절에 포함된 서브 쿼리를 스칼라 서브 쿼리(Scala Sub Query)라고 함
• 스칼라 서브 쿼리의 반환 값은 하나의 레코드에서 단일 컬럼이어야 함
• 테이블의 조인이 많아 실행 계획의 제어가 어려울 경우 사용
  • 결과 집합이 대량인 경우 효율이 저하됨
  • 소량의 테이블에서 명칭만 가져올 경우 유리함

상호 연관 서브쿼리

• 서브 쿼리가 주 쿼리의 필드 값과 연관되어 사용하는 질의
• 일반적인 서브 쿼리는 서브 쿼리의 결과를 주 쿼리가 이용함
• 주 쿼리의 한 Row에 대해 서브 쿼리가 한번씩 실행됨
  • 테이블에서 Row를 먼저 읽고 각 Row의 서브 쿼리에서 사용함
• 결과 집합이 대량인 경우 효율이 떨어짐
• 포함된 서브 쿼리와 인라인 뷰, 포함된 서브쿼리에서 주 쿼리와 값을 비교하는 모든 경우

집단 연산

집계 함수

• SELECT절에서 산출되는 컬럼의 값에 대한 집계를 위해 사용됨
• COUNT([DISTINCT] A)
  A 필드에 있는 [유일한] 값들의 수
• SUM([DISTINCT] A)
  A 필드에 있는 [유일한] 값들의 합
• AVG(DISTINCT] A)
  A 필드에 있는 [유일한] 값들의 평균
• MAX (A)
  A 필드에 있는 최대값
• MIN (A)
  A 필드에 있는 최소값

GROUP BY 절과 HAVING 절

• 릴레이션의 투플들을 여러 그룹으로 나누어서 각 그룹별로 집단 연산을 수행
• 그룹의 수는 릴레이션의 인스턴스에 따라 좌우됨
• GROUP BY
  컬럼의 값을 기준으로 투플을 그룹화 함
• HAVING
  그룹에 대한 조건식을 지정

정렬

ORDER BY

• 데이터베이스 질의의 결과는 테이블 내의 투플의 위치(삽입된 순서)대로 산출됨
• ORDER BY 절에 사용되는 컬럼을 기준으로 데이터를 정렬
  • 오름차순(DESC)와 내림차순(ASC – 기본 값)으로 데이터를 정렬 함

INDEX

• 인덱스는 해당 파일의 기본적인 레코드 조직법으로는 효율적으로 지원되지 않는 연산의 속도를 높이기 위해 만드는 보조적인 자료구조
• 인덱스는 인덱스를 지원하는 자료구조에 생성되며, 테이블에 지정된 컬럼의 데이터를 기준으로 정렬된 데이터를 저장함
• 질의에서 인덱스를 사용하면 인덱스에 포함된(정렬된) 데이터를 기준으로 데이터를 정렬함

삽입

INSERT

• 테이블에 투플을 삽입하기 위해 사용

하나의 INSERT문으로 여러 행을 삽입하려면

• 삽입될 데이터의 형식을 지정한 후, 여러 데이터를 삽입

INSERT INTO <Table Name> [(Column Name 1, Column Name2, … Column Name n)] 
VALUES (Value 1, Value 2 … Value n), (Value 1, Value 2 … Value n), (Value 1, Value 2 … Value n)

• SELECT 질의의 결과로 산출되는 결과를 테이블에 삽입

INSERT INTO <Table Name> [(Column Name 1, Column Name2, … Column Name n)] 
SELECT <Column 1, Column 2, Column n> FROM <Table Name> [WHERE clause]

갱신

UPDATE

• 테이블의 데이터를 갱신하기 위해 사용
• 투플 전체가 아닌 데이터 단위(셀 단위)로 데이터를 갱신
• 테이블 내에서 조건에 해당하는 모든 투플의 지정된 데이터를 모두 갱신

삭제

DELETE

• 테이블의 데이터를 삭제하기 위해 사용
• 투플 단위로 데이터가 삭제됨
• WHERE 조건이 명시되지 않은 경우 테이블 내 모든 투플을 삭제