제 5 장 SQL: 확장된 질의 , 주장 , 트리거 , 뷰

제 5 장 • SQL: 확장된 질의, 주장, 트리거, 뷰 Fundamentals of Database Systems R. A. Elmasri and S. B. Navathe Fundamentals of Database Systems

SQL 질의에서의 NULL • SQL은 애트리뷰트의 NULL(널)인지 검사하는 질의들이 있음 • 알려지지 않는 값, 이용할 수 없는 값, 적용할 수 없는 값 • SQL에서는 NULL과 비교하기 위해 IS나 IS NOT을 사용 • 그 이유는 각 NULL값은 모든 다른 NULL 값과는 다르다고 간주 • = 형태의 비교가 적당하지 않음 • Query 18:감독관이 없는 모든 종업원들의 이름을 검색하시오. Q18: SELECT FNAME, LNAME FROM EMPLOYEE WHERE SUPER_SSN IS NULL Note:조인 조건을 지정했을 때, 조인 애트리뷰트에 대해서 NULL 값을 갖는 튜플들은 결과에 나타나지 않음 Fundamentals of Database Systems

Three-Valued Logic Fundamentals of Database Systems

중첩 질의 • 완전한 SELECT 질의(중첩 질의, nested query)는 다른 질의(외부 질의, outer query)의 WHERE 절 내에 명시될 수 있음 • 4장의 질의들의 대부분은 중첩을 사용하는 형태에 명시될 수 있음 • 비상관 중첩 질의(uncorrelatednested query): outer query의 투플 변수가 nested query에 나타나지 않는 경우 • Query 1: Research에서 근무하는 모든 사원의 이름과 주소를 검색하시오. (IN : 집합의 멤버쉽 연산자)Q1: SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME='Research' ) Fundamentals of Database Systems

중첩 질의 (계속) • 중첩 질의는 Research 부서의 번호(number)를 선택함 • 외부 질의는 DNO 값이 중첩 질의 결과에 있으면 EMPLOYEE 튜플을 선택함 • 비교 연산자 IN은 하나의 값 v와 값들의 집합 V를 비교함 • v가 V에서 요소들(elements) 중의 하나이면 TRUE이 됨 • 일반적으로 중첩 질의들을 여러 레벨들을 포함할 수 있음 • 모호한 애트리뷰트에 대한 참조 규칙은 가장 안쪽의 중첩 질의에서 선언된 릴레이션에 속함 Fundamentals of Database Systems

중첩 질의 예제 SELECT DISTINCT Pnumber FROM PROJECT WHERE Pnumber IN (SELECT Pnumber FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND Lname = ‘Smith’) OR Pnumber IN (SELECT Pno FROM WORKS_ON, EMPLOYEE WHERE Essn = Ssn AND Lname = ‘Smith’); Fundamentals of Database Systems

중첩 질의 (계속) • 집합원소로서 투플을 사용한 경우 SELECT DISTINCT Essn FROM WORKS_ON WHERE (Pno, Hours) IN (SELECT Pno, Hours FROM WORKS_ON WHERE Essn = ‘123456789’); Fundamentals of Database Systems

연산자 ANY, ALL • ANY (SOME): 다중 집합의 원소 중에 조건을 만족하는 경우가 하나라도 있으면 True, otherwiseFalse • ALL: 다중집합의 모든 원소에 대해서 조건을 만족하는 경우에 True, otherwiseFalse SELECT Lname, Fname FROM EMPLOYEE WHERE Salary >ALL (SELECT Salary FROM EMPLOYEE WHERE Dno = 5); Fundamentals of Database Systems

중첩 질의 • 애트리뷰트 이름의 모호함을 피하기 위해 투플 변수를 사용한다. Query 16. 부양가족의 이름과 성별이 같은 사원들의 이름(Fname, Lname)을 검색하라 SELECT E.Fname, E.Lname FROM EMPLOYEE AS E WHERE E.Essn IN (SELECT Essn FROM DEPENDENT AS D WHERE E.Fname = D.Dependent_name E.Sex = D.Sex); Fundamentals of Database Systems

상관 중첩 질의 • 만약 중첩 질의의 WHERE 절에 있는 조건에서 외부 질의에 선언된 릴레이션의 애트리뷰트를 사용하는 경우에 두 질의는 상관된(Correlated) 질의라고 함 • Query 16:부양가족의 이름과 성별이 같은 사원들의 이름(Fname, Lname)을 검색하라 Q16: SELECT E.FNAME, E.LNAME FROM EMPLOYEE AS E WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT ASD WHERE E.FNAME=D.DEPENDENT_NAME AND E.SEX = D.SEX) Fundamentals of Database Systems

상관 중첩 질의 (계속) • 중첩된 SELECT… FROM… WHERE… 블록과 =과 IN 비교 연산자를 이용해서 작성된 질의는 단일 블록 질의로 변환할 수 있음 • 예를 들어, Q16는 Q16A로 쓰여질 수 있음 Q16A: SELECT E.FNAME, E.LNAME FROM EMPLOYEE E, DEPENDENT D WHERE E.SSN=D.ESSN AND E.SEX = D.SEX AND E.FNAME=D.DEPENDENT_NAME Fundamentals of Database Systems

EXISTS 함수 • EXISTS는 중첩 질의의 결과가 공집합(튜플을 포함하지 않음)인지 아닌지를 검사. 공집합이면 False, Otherwise True • 다음 장에서 EXISTS를 사용하여 질의 16를 Q16B처럼 다른 형태로 나타낼 수 있음 Fundamentals of Database Systems

EXISTS 함수 (계속) • Query 16:부양가족의 이름과 성별이 같은 사원들의 이름(Fname, Lname)을 검색하라 Q16B: SELECT E.FNAME, E.LNAME FROM EMPLOYEE AS E WHERE EXISTS (SELECT * FROM DEPENDENT AS D WHERE E.SSN=D.ESSN AND E.SEX=D.SEX AND E.FNAME=D.DEPENDENT_NAME); Fundamentals of Database Systems

EXISTS 함수 (계속) • Query 6:부양가족이 없는 종업원의 이름을 검색하시오.Q6: SELECT FNAME, LNAME FROM EMPLOYEE WHERE NOT EXISTS (SELECT * FROM DEPENDENT WHERE SSN=ESSN) • Q6에서, 상관 중첩 질의는 EMPOYEE 튜플과 관계 있는 모든 DEPENDENT 튜플을 검색 • 만약 DEPENDENT 튜플들이 존재하지 않으면, EMPLOYEE 튜플이 선택됨 Fundamentals of Database Systems

EXISTS 함수 (계속) • Query 7:부양가족을 적어도 한 명 이상 가진 관리자의 이름을 검색하라.Q7: SELECT FNAME, LNAME FROM EMPLOYEE WHERE EXISTS (SELECT * FROM DEPENDENT WHERE SSN=ESSN) AND EXISTS (SELECT * FROM DEPARTMENT WHERE SSN=MGR_SSN); Fundamentals of Database Systems

EXISTS 함수 (계속) • Query 3: 5번 부서가 담당하는 모든 프로젝트에 근무하는 사원들의 이름을 검색하라.Q3A: SELECT FNAME, LNAME FROM EMPLOYEE WHERE NOTEXISTS ((SELECT PNUMBER FROM PROJECT WHERE DNUM=5) EXCEPT (SELECT PNO FROM WORKS_ON WHERE SSN=ESSN); • Oracle은 EXCEPT 대신 MINUS로 표기 • MySQL은 EXCEPT를 지원하지 않음 Fundamentals of Database Systems

EXISTS 함수 (계속) • Query 3: 5번 부서가 담당하는 모든 프로젝트에 근무하는 사원들의 이름을 검색하라.Q3B: SELECT FNAME, LNAME FROM EMPLOYEE WHERE NOTEXISTS ( SELECT * FROM WORKS_ON B WHERE (B.PNO IN (SELECT PNUMBER FROM PROJECT WHERE DNUM = 5) AND NOT EXISTS (SELECT * FROM WORKS_ON C WHERE C.ESSN = SSN AND C.PNO = B.PNO))); • 관계해석적 표현 Fundamentals of Database Systems

명시적 집합 • 중첩 질의 대신에 WHERE 절에 값들의 명시적 집합을 사용할 수 있음 • Query 17:프로젝트 번호 1,2,3에서 일하는 모든 사원의 사회보장 번호를 검색하시오. Q17: SELECT DISTINCT ESSN FROM WORKS_ON WHERE PNO IN (1, 2, 3) Fundamentals of Database Systems

애트리뷰트 이름 재명명 Q8A: SELECT E.LNAME AS Employee_name, S.Lname AS Supervisor_name FROM EMPLOYEE AS E, EMPLOYEE AS S WHERE E.SUPER_SSN = S.SSN; Fundamentals of Database Systems

SQL에서 조인된 테이블 • FROM 절에 조인 연산의 결과를 지정할 수 있음 • 여러 가지 유형의 조인을 명시할 수 있도록 허용 • JOIN, NATURAL JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, CROSS JOIN, etc Fundamentals of Database Systems

SQL에서 조인된 테이블 (계속) • Examples:Q8: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEE E, EMPLOYEE S WHERE E.SUPERSSN=S.SSNQ8는 다음과 같이 쓰여질 수 있음:Q8B:SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM (EMPLOYEE AS E LEFT OUTER JOIN EMPLOYEE AS S ON E.SUPERSSN=S.SSN)Q1: SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE DNAME='Research' AND DNUMBER=DNO Fundamentals of Database Systems

SQL에서 조인된 테이블(계속) • 이전 Q1은 다음과 같이 쓰여질 수 있음 :Q1A: SELECT FNAME, LNAME, ADDRESS FROM (EMPLOYEE JOIN DEPARTMENT ON DNUMBER=DNO) WHERE DNAME='Research’이거나Q1B: SELECT FNAME, LNAME, ADDRESS FROM (EMPLOYEE NATURAL JOIN DEPARTMENT AS DEP (DNAME, DNO, MSSN, MSDATE) WHERE DNAME='Research’ Fundamentals of Database Systems

SQL에서 조인된 릴레이션 특징 (계속) • Another Example; • Q2 는 조인된 테이블들에서 다중 조인 형태로 취할 수 있음 • 조인된 테이블의 개념을 사용하여 Q2로 표현 Q2A: SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS FROM (PROJECT JOIN DEPARTMENT ON DNUM=DNUMBER) JOIN EMPLOYEE ON MGRSSN=SSN WHERE PLOCATION='Stafford’ Fundamentals of Database Systems

집단함수 • SQL에서는 COUNT, SUM, MAX, MIN, AVG 등을 포함 • Query 19:사원의 급여의 합, 최고 급여, 최저 급여, 평균 급여를 구하시오. Q19: SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM EMPLOYEE Fundamentals of Database Systems

집단함수 (계속) • Query 20: ‘Research’ 부서에 근무하는 모든 사원들의 급여의 합과 최고 급여, 최소 급여, 평균 급여를 구하시오. Q20: SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY),AVG(SALARY) FROM EMPLOYEE, DEPARTMENT WHERE DNO=DNUMBER AND DNAME='Research‘; Q20: SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY),AVG(SALARY) FROM (EMPLOYEE JOIN DEPARTMENT OnDNO=DNUMBER) WHERE DNAME='Research‘; Fundamentals of Database Systems

집단함수 (계속) • Queries 21 and 22: (Q21) 회사 내의 총 사원의 수와, (Q22) ‘Research’ 부서에 근무하는 총 사원 수를 검색하시오. Q21: SELECT COUNT (*) FROM EMPLOYEE Q22: SELECT COUNT (*) FROM EMPLOYEE, DEPARTMENT WHERE DNO=DNUMBER AND DNAME='Research’ Fundamentals of Database Systems

집단함수 (계속) • Query 23:서로 다른 급여들의 개수를 구하라. Q23: SELECT COUNT (DISTINCTSALARY) FROM EMPLOYEE; Fundamentals of Database Systems

집단함수 (계속) • Query 5:둘이상의부양 가족이 있는 모든 사원의 이름을 검색하라 SELECT LNAME, FNAME FROM EMPLOYEE WHERE (SELECT COUNT (*) FROM DEPENDENT WHERE SSN = ESSN) > 2; Fundamentals of Database Systems

그룹화 • 많은 경우에, 릴레이션 내에 있는 튜플들의 여러 부분 집단으로 나누고 집단 함수를 적용하기도 함 • 튜플의 각 부분 집합은 그룹화 애트리뷰트(들)에 대해 값은 튜플들로 구성됨 • 각 그룹마다 독립적으로 집단 함수들을 적용할 수 있음 • SQL은 SELECT 절에 나타나는 애트리뷰트들 중에서 그룹화 애트리뷰트를 GROUP BY절에 명시 Fundamentals of Database Systems

그룹화 (계속) • Query 24:각 부서에 대해서 부서번호, 부서 내에 있는 사원의 수, 평균 급여를 검색하시오. Q24: SELECT DNO, COUNT (*), AVG (SALARY) FROM EMPLOYEE GROUP BY DNO • Q20에서, EMPLOYEE 튜플들을 그룹화 애트리뷰트인 DNO 값이 같은 튜플들끼리 여러 그룹으로 분할함 • 각 그룹의 튜플들에 대하여 COUNT와 AVG 함수를 적용함 • SELECT 절에는 그룹화 애트리뷰트와 각 튜플들의 그룹에 적용할 집단함수들만 포함함 • 조인조건은 그룹화와 함께 사용할 수 있음 Fundamentals of Database Systems

그룹화와HAVING 절의 결과 Fundamentals of Database Systems

그룹화 (계속) • Query 25:각 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 그 프로젝트에서 근무하는 사원들의 수를 검색하시오. Q25: SELECT PNUMBER, PNAME, COUNT (*) FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER, PNAME • 이 경우, 두 개의 테이블을 조인한 후에 그룹화와 집단함수가 적용됨 Fundamentals of Database Systems

그룹화 (계속) • Query 27:각 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 5번 부서에 속하면서 프로젝트에서 근무하는 사원의 수를 검색하시오. Q27: SELECT PNUMBER, PNAME, COUNT(*) FROM PROJECT, WORKS_ON, EMPLOYEE WHERE PNUMBER=PNO AND SSN=ESSN AND DNO=5 GROUP BY PNUMBER, PNAME HAVING COUNT (*) > 2 Fundamentals of Database Systems

HAVING 절 • 때로 어떤 조건들을 만족하는 그룹들에 대해서만 집단함수들의 값을 구하기도 함 • HAVING절은 집단함수를 적용할 그룹들을 선택하는 데 사용됨 Fundamentals of Database Systems

HAVING절 (계속) • Query 26:두 명 이상의 사원이 근무하는 각 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 프로젝트에서 근무하는 사원의 수를 검색하시오. Q26: SELECT PNUMBER, PNAME, COUNT(*) FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER, PNAME HAVING COUNT (*) > 2 Fundamentals of Database Systems

HAVING 절의 결과 Fundamentals of Database Systems

HAVING절 (계속) • Query 28: 5명 이상의 사원이 근무하는 각 부서에 대해서 부서 번호와 40,000달러가 넘는 급여를 받는 사원의 수를 검색하라. (교재 145쪽의 오류) Q28: SELECT DNUMBER, COUNT(*) FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER=DNO AND SALARY > 40000 AND DNUMBER IN (SELECT DNO FROM EMPLOYEE GROUP BY DNO HAVING COUNT (*) > 5) GROUP BY DNUMBER; Fundamentals of Database Systems

SQL 질의에 대한 요약 • SQL에서 하나의 질의는 6개의 절로 구성 • 필수적으로 질의에 나타내야 하는 두개의 절은 SELECT와 FROM 절임 • 6개의 절은 다음 순서로 명시함SELECT <attribute list>FROM <table list> [WHERE <condition>] [GROUP BY <grouping attribute(s)>] [HAVING <group condition>] [ORDER BY <attribute list>] Fundamentals of Database Systems

SQL 질의에 대한 요약 (계속) • SELECT 절은 결과에 포함될 애트리뷰트들이나 함수를 나열함 • FROM 절은 질의에서 필요한 모든 릴레이션(별명)들을 명시함 • 중첩 질의들에 사용되는 릴레이션들은 명시하지 않음 • WHERE 절은 조인조건을포함하여 FROM 절에 명시된 릴레이션들로부터 튜플들을 선택하기 위한 조건들을 명시 • GROUP BY절은 그룹화 애트리뷰트를 명시 • HAVING 절은 선택된 튜플들의 그룹들에 대한 조건을 명시 • ORDER BY절은 질의 결과를 출력하는 순서를 명시 • 질의는 WHERE절, GROUP BY절과 HAVING절의 순서로 적용함으로써 평가됨 Fundamentals of Database Systems

주장으로 제약조건 • SQL에서는 선언적 주장으로 확장된 제약조건을 명시할 수 있음 • 선언적 주장은 DDL의 CREATE ASSERTION문을 이용함 • 각 주장문은 제약조건 이름을 가지며, SQL 질의의 WHERE 절과 유사한 조건 형태로 지정됨 예) "종업원 봉급이 부서 관리자 봉급보다 많으면 안된다."라는 제약 CREATE ASSERTION SALARY_CONSTRAINT CHECK (NOT EXISTS (SELECT * FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D WHERE E.SALARY＞M.SALARY AND E.DNO=D.DNUMBER AND D.MGRSSN=M.SSN)) ; • 제약조건 이름 SALARY_CONSTRAINT 다음에 키워드 CHECK가 오며, 그 다음의 조건을 데이터베이스 상태가 만족하면 참이 됨 • 제약조건 이름은 그 제약조건을 참조, 수정, 삭제하기 위해 사용됨 Fundamentals of Database Systems

SQL의 Trigger(Oracle 구문) • 특정 사건(Event)이 발생하고, 조건(Condition)이 만족될 때, 취해야 할 동작(Action)을 명시함. • ECA Rule이라고도 불림 예) 사원의 급여가 상관의 급여보다 많으면, 상관에게 통보한다라는 트리거 CREATE TRIGGERSALARY_VIOLATION BEFORE INSERT OR UPDATE OF SALARY, SUPER_SSN ON EMPLOYEE /* EVENT*/ FOR EACH ROW WHEN (NEW.SALARY > ( SELECT SALARY FROM EMPLOYEE WHERE SSN=NEW.SUPER_SSN)) /* CONDITION */ INFORM_SUPERVISOR(NEW.SUPER_SSN, NEW.SSN); /* ACTION */ Fundamentals of Database Systems

SQL에서 뷰의 개념 • SQL에서 뷰(VIEW)는 다른 테이블들에서 유도된 “가상(Virtual)” 테이블 • 뷰에 적용할 수 있는 갱신 연산들은 제한됨 • 물리적인 형태로 저장되지는 않기 때문에 • 뷰에 대한 질의는 아무런 제한을 받지 않음 • 여러 개 테이블의 조합을 하나의 뷰로 표현하여 사용하면 데이터의 중복 없이 하나의 테이블처럼 보여주기 때문에 편리함 Fundamentals of Database Systems

SQL에서 뷰의 명시 • 뷰를 정의하는 SQL 명령: CREATE VIEW • 뷰의 정의는 (가상) 테이블 이름 • 애트리뷰트 이름들의 목록 • 함수나 산술 연산들을 적용하거나, 기본 릴레이션의 애트리뷰트 이름과 다른 이름을 사용하고자 할 때 이용 • 뷰의 내용을 나타내는 질의 Fundamentals of Database Systems

SQL에서 뷰: 예제 • 다른 WORKS_ON 테이블의 명시 CREATE VIEWWORKS_ON1 AS SELECT FNAME, LNAME, PNAME, HOURS FROM EMPLOYEE, PROJECT, WORKS_ON WHERE SSN=ESSN AND PNO=PNUMBER; Fundamentals of Database Systems

가상 테이블의 사용 • 새로이 생성된 테이블(뷰)에서 SQL 질의를 명시함 SELECT FNAME, LNAME FROM WORKS_ON1 WHERE PNAME=‘ProductX’; • 어떤 뷰가 더 이상 필요하지 않으면 뷰를 제거함 DROP VIEW WORKS_ON1; Fundamentals of Database Systems

SQL에서 뷰: 예제 • DEPT_INFO: 부서에대한 요약 정보를 보여주는 VIEW CREATE VIEWDEPT_INFO(DEPT_NAME, NO_OF_EMPS, TOTAL_SAL) AS SELECT DNAME, COUNT(*), SUM(SALARY) FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER=DNO GROUP BY DNAME; Fundamentals of Database Systems

SQL에서 뷰: 스키마 Fundamentals of Database Systems

효율적인 뷰 구현 • 질의수정(query modification) 방식 • 뷰에 대한 질의를 기본 테이블들에 대한 질의로 변환하여 처리 • 먼저 뷰에 대한 질의 트리를 구성하고, 뷰에 대한 질의에 대한 질의 트리를 그 위에 구성하는 방식 • 단점: 복잡한 질의로 정의된 뷰들은 비효율적 • 특히 짧은 시간 내에 뷰에 많을 질의가 적용될 때 SELECT FNAME, LNAME FROM WORKS_ON1 WHERE PNAME=‘ProductX’; (질의 수정) SELECT FNAME, LNAME FROM EMPLOYEE, PROJECT, WORKS_ON WHERE SSN=ESSN AND PNO=PNUMBER AND PNAME=‘ProductX’; Fundamentals of Database Systems

효율적인 뷰 구현 • 뷰의 실체화(view materialization) • 임의 뷰 테이블을 물리적으로 생성하고 유지하는 방식 • Data Warehouse나 OLAP응용에 사용하는 방식 • 가정: 뷰에 계속적으로 다른 질의들이 사용됨 • 문제점: 기본 테이블이 갱신되면 뷰 테이블도 변경해야 함 • 해결방법: 오버헤드가 적은 점진적 갱신(incremental update)기법 필요 Fundamentals of Database Systems

뷰의 갱신 • 집단함수를 사용하지 않는 단일 뷰의 갱신 • 뷰의 갱신은 단일 기본 테이블에 대한 갱신으로 사상될 수 있음 • 조인을 포함하는 뷰의 갱신 • 기본 릴레이션들에 대한 갱신 동작으로 사상될 수 있음 (항상 가능한 것은 아님) Fundamentals of Database Systems

제 5 장 SQL: 확장된 질의 , 주장 , 트리거 , 뷰