관계 대수 연산과 관계 대수 질의 예시

관계 대수 연산의 완전 집합

• 지금까지 모든 연산자는 선택, 프로젝트 ,합집합, 차집합, 카티션 프로덕트 연산들 만의 조합으로 표현할 수 있다.
• 연산자 집합 {𝝈, ∏ , U, -, x}를 관계대수 연산자의 완전 집합(Complete set)이라 부른다.

기타 연산자의 표현

• R ∩ S = (R U S) - ((R - S ) U (S - R)) → 교집합
• R ⨝<조건>S = 𝝈<조건>(R X S)

---

디비전 연산

T(Y) = R(Z) ➗ S(X)는 다음과 같이 정의 된다. (X⊆ Z이고, Y = Z - X 임)

• T1 = ∏y (R)
• T2 = ∏y((S X T1 ) - R)
• T = T1 - T2

 

질의: Smith가 근무하는 모든 프로젝트에서 근무하는 사원들의 이름을 검색하시오.

SMITH <- 𝝈FNAME="JoHm" AND LNAME="Smith"(EMPLOYEE)
SMITH_PNOS <- ∏PNO(WORKS_ON ⨝ essn=ssnSMITH)
SSN_PNOS <- ∏essn,pno(WORKS_ON)
SSNS(SSN) <- SSN_PNOS ➗ SMITH_PNOS
RESULT <- ∏FNAME, LNAME(SSNS * EMPLOYEE)

---

추가적인 관계 연산(집계 함수들과 집단화)

집계합수(Aggregate function, 집단 함수)

• SUM, COUNT, AVERAGE, MIN, MAX 함수를 의미
• 데이터베이스 응용에서 값들의 집합 또는 투플들의 집합에 적용되며, 표준 관계 대수로 표현할 수 없음

<그룹화 애트리뷰트들>F<함수 리스트>(R)

#F를 적어야 명시적으로 집계함수임을 알릴 수 있어서 생략을 안하는 게 좋다
DNO F count SSN (EMP)

 

예제1

모든 사원의 평균 급여 검색(그룹화 불필요)

R(AVGSAL) <- F AVERAGE SALARY(EMPLOYEE)

#다른표현, ｐ(재명명 연산)
ｐR(AVGSAL) (F AVERAGE SALARY(EMPLOYEE))

 

예제2

각부서 번호와 부서별 사원 수와 평균 급여를 검색

#1
R(DNO, NUMBEPS, AVGSAL) <- DNO F COUNT SSN, AVERAGE SALARY(EMPLOYEE)
#위 DNO를 그룹화 애트리뷰트(Grouping attribute)라고 부른다.

#다른표현
ｐR(DNO, NUMBEPS, AVGSAL) (DNO F COUNT SSN, AVERAGE SALARY(EMPLOYEE))

SELECT DNO, COUNT(SSN) AS NO_OF_EMPLOYEE, AVG(SALARY) AS AVERAGE_SALARY
FROM EMPLOYEE
GROUP BY DNO;

 

SELECT COUNT(SSN), AVG(SALARY)
FROM EMPLOYEE;

---

순환적 폐포(Recursive Closure) 연산

• 동일한 테이블에서 투플들 간 순환적 관계(Recursive relationship)를 질의하는 데 사용
• 관계 대수로서 표현할 수 없다!

예시

EMPLOYEE 테이블에서 사원과 상사간의 관계에 대하여 특정 사원의 모든 상사(직간접 상사관계에)에 있는 직원을 모두 검색하시오,

• 이러한 질의는 루핑(Looping)을 사용하여 한 단계 상사들의 집합을 구하고, 이를 바탕으로 다음 단계 상사를 구하며, 이러한 과정을 더 이상 상사 집합이 없을 때까지 (사장이 나올때까지) 구해 나가야 함
  • 호스트언어(Embedded SQL) 등을 사용하여 해결할 수 있음

1. Borg이 관리하는 사람을 찾기
2. 1의 결과가 관리하는 사람 찾기
3. 둘을 합집합

---

외부 조인 연산

외부 조인(Outer join)

• 정규 Equijoin이나 자연 조인(Natural join) 연산에서 조인, 조건을 만족하지 않은 튜플들은 결과 릴레이션에서도 나타나지 않음
• 조인에 참여하는 릴레이션의 모든 투플들을 조인 결과 릴레이션에 나타내고 싶은 경우 외부조인을 사용함
• 외부 조인에서는 상대방 릴레이션에 대응되는 투플이 없으면 빈 애트리뷰트들에 null 값을 채워서 결과에 포함시킴

외부 조인의 종류

왼쪽 외부 조인(Left outer join)

• R1⟕R2: R1의 모든 투플들이 결과 릴레이션에 나타남(왼쪽것만)

오른쪽 외부 조인(Right outer join)

• R1⟖R2: R2의 모든 투플들이 결과 릴레이션에 나타남(오른쪽것만)

완전 외부 조인(Full outer join)

• R1⟗R2: R1과 R2의 모든 투플들이 결과 릴레이션에 나타남

카티션 프로덕트 곱처럼 모든 값들이 나오는 게 아니라. 없는 것들은 null로 채운다.

 

예제

TEMP <- EMPLOYEE⟕ SSN=MGRSSN DEPARTMENT
RESULT <- ∏FNAME, MINIT, LNAME, DNAME(TEMP)

 

• MYSQL에서 Left outer join, Right outer join 지원
• Full outer join은 지원하지않음
  • 합집합, UNION으로 구현가능하다 (Left outer + Right outer)

SELECT FNAME, MINIT, LNAME, DNAME
FROM EMPLOYEE LEFT OUTER JOIN DEPARTMENT ON SSN=MGRSSN;

 

합집합 호환성이 없는 두 릴레이션을 합집합(Union)하는 데 사용됨

→ 합집합을 하되 표현할 수 없는 애트리뷰트는 null로 채움

 

외부 합집합 연산의 예제

1. STUDENT(Name, SSN, Department, Advisor)
2. FACULTY(Name, SSN, Department, Rank)
   • a와 b의 외부 합집합(Outer union)은
3. RESULT(Name, SSN, Department, Advisor, Rank)
   • STUDENT 투플은 Rank 속성의 값이 null
   • FACULTY 투플은 Advisor 속성의 값이 null

---

⭐️관계 대수 질의의 예시⭐️

• WHERE절로 조건 뺴고
• 위 조건으로 조인하고
• SELECT절 추가

질의 1 ‘Research’ 부서에서 일하는 모든 사원의 이름과 주소를 검색하시오.

SELECT FNAME, LNAME, ADDRESS
FROM DEPARTMENT JOIN EMPLOYEE ON DNUMBER=DNO
WHERE DNAME='Resarch';

 

질의2 ‘Stafford’ 에 위치한 모든 프로젝트에 대하여 프로젝트 번호와 관리 부서 번호, 부서 관리자의 성, 주소, 생년월일을 나열하시오.

SELECT PNUMBER, DNUM, LAME, ADDRESS, BDATE
FROM PROJECT, DEPARTMNET, EMPLOYEE
WHERE DNUM=DNUMBER AND MGRSSN=SSN AND PLOCATION='Stafford';

 

질의3 번호 5인 부서가 관리하는 모든 프로젝트에서 근무하는 사원들의 이름을 찾으시오.

SELECT E.LNAME, E.FNAME 
FROM EMPLOYEE E
WHERE E.SSN IN (
    SELECT WO.ESSN 
    FROM WORKS_ON WO 
    WHERE WO.PNO IN (
        SELECT P.PNUMBER 
        FROM PROJECT P 
        WHERE P.DNUM = 5
    )
);

 

질의4 “Smith가 직원(Worker)으로 참여하는 프로젝트”이거나 “Smith가 관리자(Manager)로 근무하는 부서가 관리하는 프로젝트”의 프로젝트 번호들을 나열하시오.

(SELECT DISTINCT PNUMBER
 FROM PROJECT, DEPARTMENT, EMPLOYEE
 WHERE DNUM=NUMBER AND MGRSSN=SSN AND LNAEM  = 'Smith')
UNION
(SELECT DISTINCT PNUMBER
 FROM PROJECT, WORKS_ON, EMPLOYEE
 WHERE PNUMBER=PNO AND ESSN=SSN AND LNAME='Smith');

 

질의 5 두 명 이상의 부양가족을 가진 사원의 이름을 나열하시오.

SELECT LNAME, FNAME
FROM EMPLOYEE
WHERE (SELECT COUNT(*)
			FROM DEPENDENT
			WHERE SSN=ESSN) >=2;

 

질의 6 부양 가족이 없는 사원의 이름을 나열하시오. NOT EXISTS

 

SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE NOT EXISTS (SELECT *
						FROM DEPENDENT
						WHERE SSN=ESSN);

 

⭐️ 질의7 부양 가족이 적어도 한 명 이상인 관리자(manager)들의 이름을 나열하시오

EMPLOYEE 테이블에서 SSN이 DMGR(SSN)에 있는 직원의 FNAME과 LNAME을 선택 → 교집합

SELECT E.FNAME, E.LNAME
FROM EMPLOYEE AS E, ((SELECT MGRSSN FROM DEPARTMENT)
								INTERSECT
								(SELECT ESSN FROM DEPENDENT)) AS DMGR(SSN)
WHERE E.SSN = DMGR.SSN;