관계 데이터 연산

관계 데이터 연산의 개념

• 데이터 모델 = 데이터 구조(data structure) + 연산(operation) + 제약조건(constraint)

관계 데이터 연산(relational data operation)

• 관계 데이터 모델의 연산.
• 원하는 데이터를 얻기 위해 릴레이션에 필요한 처리 요구를 수행하는 것.
• 관계 대수와 관계 해석이 있다.
  • 기능과 표현력 측면에서 능력이 동등하다.
  • 처리절차를 얼마나 자세히 기술하느냐에 따라 차이를 보인다.
  • 관계 데이터 연산
    • 관계 대수
      원하는 결과를 얻기 위해 데이터의 처리 과정을 순서대로 기술
    • 관계 해석
      원하는 결과를 얻기 위해 처리를 원하는 데이터가 무엇인지만 기술

관계 대수(relational algebra)의 개념

• 원하는 결과를 얻기 위해 릴레이션의 처리 과정을 순서대로 기술하는 언어
  • 절차 언어(procedural language)
• 릴레이션을 처리하는 연산자들의 모임
  • 대표 연산자 8개
  • 일반 집합 연산자와 순수 관계 연산자로 분류된다.
• 폐쇄 특성(closure property)이 존재한다
  • 피연산자도 릴레이션이고 연산의 결과도 릴레이션이다.

관계 대수

• 관계 대수 연산자
  • 일반 집합 연산자
    • 합집합
    • 교집합
    • 차집합
    • 카티션 프로덕트
  • 순수 관계 연산자
    • 셀렉트
    • 프로젝트
    • 조인
    • 디비전

일반 집합 연산자(set operation)

릴레이션이 튜플의 집합이라는 개념을 이용하는 연산자

연산자	기호	표현	의미
합집합	\(\cup\)	\(R\cup S\)	릴레이션 R과 S의 합집합을 반환
교집합	\(\cap\)	\(R \cap S\)	릴레이션 R과 S의 교집합을 반환
차집합	\(-\)	\(R-S\)	릴레이션 R과 S의 차집합을 반환
카티션 프로덕트	\(\times\)	\(R\times S\)	릴레이션 R의 각 튜플과 릴레이션 S의 각 튜플을 모두 연결하여 만들어진 새로운 튜플을 반환

특성

• 피연산자가 두개 필요하다.
  • 두개의 릴레이션을 대상으로 연산을 수행한다.
• 합집합, 교집합, 차집합은 피연산자인 두 릴레이션이 합병이 가능해야 한다.
  • 합병 가능(union-compatible)조건
    • 두 릴레이션의 차수가 같아야 한다.
    • 두 릴레이션에서 서로 대응되는 속성의 도메인이 같아야 한다.

합집합(union)

합병 가능한 두 릴레이션 R과 S의 합집합

• 릴레이션 R에 속하거나 릴레이션 S에 속하는 모든 튜플로 결과 릴레이션을 구성한다.

  \(R\)
  번호	이름
  100	A
  200	B
  300	C

  \(S\)
  번호	이름
  100	A
  101	D
  102	E

  \(R \cup S\)
  번호	이름
  100	A
  200	B
  300	C
  101	D
  102	E

• 결과 릴레이션의 특성
  • 차수는 릴레이션 R과 S의 차수와 같다.
  • 카디널리티는 릴레이션 R과 S의 카디널리티를 더한 것과 같거나 적어진다.
    합병시 중복은 하나로 표현한다.
• 교환적 특징이 있다.
  \(R \cup S = S \cup R\)
• 결합적 특징이 있다.
  \((R \cup S) \cup T = R \cup (S \cup T)\)

교집합(intersection)

합병 가능한 두 릴레이션 R과 S의 교집합

• 릴레이션 R과 릴레이션 S에 속하는 모든 튜플로 결과 릴레이션을 구성한다.

  \(R\)
  번호	이름
  100	A
  200	B
  300	C

  \(S\)
  번호	이름
  100	A
  101	D
  102	E

  \(R \cap S\)
  번호	이름
  100	A

• 결과 릴레이션의 특성
  • 차수는 릴레이션 R과 S의 차수와 같다.
  • 카디널리티는 릴레이션 R과 S의 어떤 카디널리티보다 크지 않다.
• 교환적 특징이 있다.
  \(R \cap S = S \cap R\)
• 결합적 특징이 있다.
  \((R \cap S) \cap T = R \cap (S \cap T)\)

차집합(difference)

합병 가능한 두 릴레이션 R과 S의 차집합

• 릴레이션 R에는 존재하고 릴레이션 S에는 존재하지 않는 튜플로 결과 릴레이션을 구성한다.

  \(R\)
  번호	이름
  100	A
  200	B
  300	C

  \(S\)
  번호	이름
  100	A
  101	D
  102	E

  \(R - S\)
  번호	이름
  200	B
  300	C

  \(S - R\)
  번호	이름
  101	D
  102	E

• 결과 릴레이션의 특성
  • 차수는 릴레이션 R과 S의 차수와 같다.
  • R차집합S의 카디널리티는 릴레이션 R의 카디널리티와 같거나 적다.
  • S차집합R의 카디널리티는 릴레이션 S의 카디널리티와 같거나 적다.
• 교환적, 결합적 특징이 없다.

카티션 프로덕트(cartesian product)

두 릴레이션 R과 S의 카티션 프로덕트

• 릴레이션 R에 속한 각 튜플과 릴레이션 S에 속한 각 튜플을 모두 연결하여 만들어진 새로운 튜플로 결과 릴레이션을 구성한다.

  \(R\)
  번호	이름
  100	A
  200	B
  300	C

  \(S\)
  번호	나이
  100	40
  101	30
  102	20

  \(R \times S\)
  \(R\)번호	\(R\)이름	\(S\)번호	\(S\)이름
  100	A	100	40
  100	A	101	30
  100	A	102	20
  200	B	100	40
  200	B	101	30
  200	B	102	20
  300	C	100	40
  300	C	101	30
  300	C	102	20

• 결과 릴레이션의 특성
  • 차수는 릴레이션 R과 S의 차수를 더한 것과 같다.
  • 카디널리티는 릴레이션 R과 S의 카디널리티를 곱한 것과 같다.
• 교환적 특징이 있다
  \(R \times S = S \times R\)
• 결합적 특징이 있다
  \((R \times S) \times T = R \times (S \times T)\)

순수 관계 연산자(relational operation)

릴레이션의 구조와 특성을 이용하는 연산자

연산자	기호	표현	의미
셀렉트	\(\sigma\)	\(\sigma 조건(R)\)	릴레이션 R에서 조건을 만족하는 튜플들을 반환
프로젝트	\(\pi\)	\(\pi 속성리스트(R)\)	릴레이션 R에서 주어진 속성들의 값으로만 구성된 튜플들을 반환
조인	\(\bowtie\)	\(R \bowtie S\)	공통 속성을 이용해 릴레이션 R과 S의 튜플들을 연결하여 만들어진 새로운 튜플들을 반환
디비전	\(\div\)	\(R \div S\)	릴레이션 S의 모든 튜플과 관련이 있는 릴레이션 R의 튜플들을 반환

셀렉트(select)

• 릴레이션에서 조건을 만족하는 튜플만 선택하여 결과 릴레이션을 구성
• 하나의 릴레이션을 대상으로 연산을 수행
• 수학적 표현법
  \(\sigma 조건식(R)\)
  괄호안의 릴레이션을 대상으로 조건식을 만족하는 튜플들을 골라낸다.
• 데이터 언어적 표현법
  릴레이션 where 조건식
• 조건식
  • 비교식, 프레디킷(predicate)이라고도 한다.
  • 속성과 상수의 비교나 속성들 간의 비교로 표현한다.
  • 비교연산자와 논리연산자를 이용해 작성한다.

• 예시

  고객
  고객아이디	고객이름	나이	등급	직업	적립금
  A	영희	20	gold	학생	1000
  B	철수	25	vip	간호사	2000
  C	민수	30	gold	교사	3000
  D	영수	23	silver	학생	0

  고객 릴레이션에서 등급이 gold인 튜플을 검색
  -> \(\sigma 등급 = 'gold'(고객)\) 또는 where 등급=’gold’

  결과
  고객아이디	고객이름	나이	등급	직업	적립금
  A	영희	20	gold	학생	1000
  C	민수	30	gold	교사	3000

  결과 릴레이션은 연산 대상 릴레이션의 수평적 부분 집합이다.

  고객 릴레이션에서 등급이 gold이고, 적립금이 2000이상인 튜플을 검색
  -> \(\sigma 등급 = 'gold' \wedge 적립금 \geq 2000(고객)\) 또는 고객 where 등급=’gold’ and 적립금 \(\geq\) 2000

• 교환적 특징이 있다.
• \[\sigma 조건식1(\sigma 조건식2 (R)) = \sigma 조건식2(\sigma 조건식1 (R))\]
• \[\sigma 적립금 \geq 2000(\sigma 등급='gold' (고객)) = \sigma 등급='gold'(\sigma 적립금 \geq 2000 (고객)) = \sigma 등급='gold' \wedge 적립금 \geq 2000(고객)\]

프로젝트(project)

• 릴레이션에서 선택한 속성의 값으로 결과 릴레이션을 구성
• 하나의 릴레이션을 대상으로 연산을 수행
• 수학적 표현법
  \(\pi 속성리스트(R)\)
• 데이터 언어적 표현법
  릴레이션[속성리스트]

• 예시

  고객
  고객아이디	고객이름	나이	등급	직업	적립금
  A	영희	20	gold	학생	1000
  B	철수	25	vip	간호사	2000
  C	민수	30	gold	교사	3000
  D	영수	23	silver	학생	0

  • 예시1
    고객 릴레이션에서 고객이름, 등급, 적립금을 검색
    -> \(\pi 고객이름,등급,적립금(고객)\) 또는 고객(고객이름,등급,적립금)

    결과
    고객이름	등급	적립금
    영희	gold	1000
    철수	vip	2000
    민수	gold	3000
    영수	silver	0

    결과 릴레이션은 연산 대상 릴레이션의 수직적 부분 집합이다.

  • 예시2
    고객 릴레이션에서 등급을 검색
    -> \(\pi 등급(고객)\) 또는 고객(등급)

    결과
    gold
    vip
    silver

    중복은 하나만 표현한다.

조인(join)

• 조인 속성을 이용해 두 릴레이션을 조합하여 결과 릴레이션을 구성
  • 조인 속성의 값이 같은 튜플만 연결하여 생성된 튜플을 결과 릴레이션에 포함
  • 조인 속성
    두 릴레이션이 공통으로 가지고 있는 속성
• 표현법
  \(릴레이션1 \bowtie 릴레이션2\)
• 자연 조인(natural join)이라고도 한다.
  • 표현법
    \(릴레이션1 \bowtie_N 릴레이션2\)

• 예시

  고객
  고객아이디	고객이름	나이	등급
  A	철수	20	gold
  B	영희	30	vip
  C	민수	40	gold
  D	영수	50	silver

  주문
  주문번호	주문고객	주문제품	수량
  101	A	우동	5
  102	C	파이	10
  103	B	만두	15

  주문고객은 주문 릴레이션의 외래키이다.

  고객 \(\bowtie\) 주문
  고객아이디	고객이름	나이	등급	주문번호	주문제품	수량
  A	철수	20	gold	101	우동	5
  B	영희	30	vip	103	만두	15
  C	민수	40	gold	102	파이	10

  고객 릴레이션의 고객아이디 속성과 주문 릴레이션의 주문고객 속성에서 공통인 부분만 가져온다.

  일반적으로 기본키와 외래키가 조인 속성으로 사용된다.

디비전(division)

• 표현법
  \(릴레이션1 \div 릴레이션2\)
• 릴레이션2의 모든 튜플과 관련이 있는 릴레이션1의 튜플로 결과 릴레이션을 구성한다.
  • 단, 릴레이션1이 릴레이션2의 모든 속성을 포함하고 있어야 연산이 가능하다.
• 예시

  • 예시1

    고객
    고객아이디	고객이름	나이	등급	직업	적립금
    A	철수	20	gold	학생	1000
    B	영희	30	vip	간호사	2000
    C	민수	40	gold	교사	3000
    D	영수	50	silver	학생	5000

    우수등급
    등급
    gold

    고객 \(\div\) 우수등급
    고객아이디	고객이름	나이	등급	직업	적립금
    A	철수	20	gold	학생	1000
    C	민수	40	gold	교사	3000

  • 예시2

    주문내역
    주문고객	제품이름	제조업체
    A	우동	남양
    C	파이	롯데
    B	만두	남양
    A	만두	남양
    C	만두	남양

    제품1
    제품이름
    우동
    만두

    제품2
    제품이름	제조업체
    만두	남양

    주문내역 \(\div\) 제품1
    주문고객	제조업체
    A	남양

    우동과 만두를 모두산 고객

    주문내역 \(\div\) 제품2
    주문고객
    B
    A
    C

    만두를 남양것을 구입한 고객

관계 대수를 이용한 질의 표현 예

고객
고객아이디	고객이름	나이	등급
A	철수	20	gold
B	영희	30	vip
C	민수	40	gold
D	영수	50	silver

주문
주문번호	주문고객	주문제품	수량
101	A	우동	5
102	C	파이	10
103	B	만두	15

주문고객은 주문 릴레이션의 외래키이다.

• 예시1
  등급이 gold인 고객의 이름과 나이를 검색
  -> \(\pi 고객이름,나이(\sigma등급='gold'(고객))\)

  결과
  고객이름	나이
  철수	20
  민수	40

• 예시2
  고객이름이 민수인 고객의 등급과, 민수 고객이 주문한 주문제품, 수량을 검색
  -> \(\pi 등급,주문제품,수량(\sigma 고객이름='민수'(고객 \bowtie 주문))\)

  결과
  등급	주문제품	수량
  gold	파이	10

• 예시3
  주문수량이 10개 미만인 주문 내역을 삭제
  -> \(주문 - (\sigma 주문수량<10(주문))\)

  결과
  주문번호	주문고객	주문제품	수량
  102	C	파이	10
  103	B	만두	15

연습문제

• 문제1
  • 학생(학번, 이름, 학년)
  • 과목(과목번호, 과목이름)
  • 수강(학번, 과목번호, 중간성적, 기말성적, 학점)
  1. 모든 과목의 이름을 검색하는 질의문을 관계대수로 표현
     \(\pi 과목이름(과목)\)
  2. 1학년 학생의 학번과 이름을 검색하는 질의문을 관계 대수로 표현
     \(\pi 학번,이름(\sigma 학년=1(학생))\)
  3. 중간성적이 80점 이상이고 기말성적이 70점 이상인 학생의 학번과, 수강한 과목번호와 학점을 검색하는 질의문을 관계대수로 표현
     \(\pi 학번,과목번호,학점(\sigma 중간성적 \geq 80 \wedge 기말성적 \geq 70(수강))\)
  4. 모든 과목을 수강하고 있는 학생의 학번을 검색하는 질의문을 관계대수로 표현
     \(\pi 학번,과목번호(수강) \div \pi 과목번호(과목)\)
  5. 3번 과목에서 A0성적을 받은 학생의 이름과 학년을 검색하는 질의문을 관계대수로 표현
     \(\pi 이름,학년(\sigma 과목번호=3 \wedge 학점='A0'(학생 \bowtie 수강))\)
• 문제2
  • 고객(고객번호, 이름, 거주도시, 할인율)
  • 판매자(판매자번호, 이름, 수수료)
  • 제품(제품번호, 제품명, 재고량, 가격)
  • 주문(주문번호, 고객번호, 제품번호, 판매자번호, 주문수량)
  1. 수수료가 5%미만인 판매자의 번호와 이름을 검색하는 질의문을 관계대수로 표현
     \(\pi 판매자번호,이름(\sigma 수수료<5(판매자))\)
  2. C001 고객이 주문한 P003 제품의 판매자 이름과 수수료를 검색하는 질의문을 관계대수로 표현
     \(\pi 이름,수수료(\sigma 고객번호='C001' \wedge 제품번호='P003'(주문 \bowtie 판매자))\)
  3. A003 판매자에게 주문하지 않은 고객의 이름을 모두 검색하는 질의문을 관계대수로 표현
     \(\pi 이름(고객) - \pi 이름(\sigma 판매자번호='A003'(고객 \bowtie 주문))\)
  4. 부산에 거주하는 모든 고객으로부터 주문을 받은 판매자의 번호를 검색하는 질의문을 관계대수로 표현
     \(\pi 판매자번호,고객번호(주문) \div \pi 고객번호(\sigma 거주도시='부산'(고객))\)