Chapter 6 Formal Relational Query Languages Database System

Chapter 6: Formal Relational Query Languages Database System Concepts, 6 th Ed. ©Silberschatz, Korth and Sudarshan See www. db-book. com for conditions on re-use

Chapter 6: 정규 관계형 질의 언어 n Relational Algebra (관계 대수언어) n Tuple Relational Calculus (튜플 관계 해석) n Domain Relational Calculus (도메인 관계 해석) Database System Concepts - 6 th Edition 6. 2 ©Silberschatz, Korth and Sudarshan

관계형 대수 (Relational Algebra) n 절차식 언어 n 6 가지 기본 연산자 (Six basic operators) l 선택 (select): l 추출 (project): l 합집합 (union): l 차집합 (set difference): – l 카티션 곱 (Cartesian product): x l 재명명 (rename): n 연산자는 입력으로서 하나 이상의 릴레이션을 취해 그 결과로 새로운 릴레이션을 생성한다. Database System Concepts - 6 th Edition 6. 3 ©Silberschatz, Korth and Sudarshan

Select Operation – Example n Relation r ¡ A=B ^ D > 5 (r) Database System Concepts - 6 th Edition 6. 4 ©Silberschatz, Korth and Sudarshan

Select Operation n 표기법 (Notation): p(r) n p 를 선택 술어 (selection predicate) 라고 부른다. n 다음과 같이 정의된다: p(r) = {t | t r and p(t)} 여기에서 p 는 다음과 같은 유형의 명제 해석식이다. <애트리뷰트> = <애트리뷰트> 또는 <상수> > < (and), (or), (not)으로 연결된다. n Example of selection: dept_name=“Physics”(instructor) Database System Concepts - 6 th Edition 6. 5 ©Silberschatz, Korth and Sudarshan

Project Operation – Example n Relation r: n A, C (r) Database System Concepts - 6 th Edition 6. 6 ©Silberschatz, Korth and Sudarshan

Project Operation n Notation: 여기서 A 1, A 2는 애트리뷰트명이고 r은 릴레이션명이다. n 결과는 명시하지 않은 열을 제외한 k 열의 릴레이션으로 정의된다. n 릴레이션은 집합이기 때문에 중복 행은 결과에서 제거된다. n Example: To eliminate the dept_name attribute of instructor ID, name, salary (instructor) Database System Concepts - 6 th Edition 6. 7 ©Silberschatz, Korth and Sudarshan

Union Operation – Example n Relations r, s: n r s: Database System Concepts - 6 th Edition 6. 8 ©Silberschatz, Korth and Sudarshan

Union Operation n Notation: r s n Defined as: r s = {t | t r or t s} n r s가 가능하려면, 1. r과 s는 같은 항(애트리뷰트의 수가 같음)을 가져야 한다. 2. 애트리뷰트의 도메인은 양립할 수 있어야 한다(즉, r의 두번째 열은 s의 두번째 열의 것과 같은 유형의 값을 다룬다). n Example: to find all courses taught in the Fall 2009 semester, or in the Spring 2010 semester, or in both course_id ( semester=“Fall” Λ year=2009 (section)) course_id ( semester=“Spring” Λ year=2010 (section)) Database System Concepts - 6 th Edition 6. 9 ©Silberschatz, Korth and Sudarshan

Set difference of two relations n Relations r, s: n r – s: Database System Concepts - 6 th Edition 6. 10 ©Silberschatz, Korth and Sudarshan

Set Difference Operation n Notation r – s n Defined as: r – s = {t | t r and t s} n 차집합 연산은 양립할 수 있는 릴레이션 간에만 이루어질 수 있다. - r과 s는 같은 항을 가져야 한다. - r과 s의 애트리뷰트 도메인은 양립해야만 한다. n Example: to find all courses taught in the Fall 2009 semester, but not in the Spring 2010 semester course_id ( semester=“Fall” Λ year=2009 (section)) − course_id ( semester=“Spring” Λ year=2010 (section)) Database System Concepts - 6 th Edition 6. 11 ©Silberschatz, Korth and Sudarshan

Cartesian-Product Operation – Example n Relations r, s: n r x s: Database System Concepts - 6 th Edition 6. 12 ©Silberschatz, Korth and Sudarshan

Cartesian-Product Operation n Notation r x s n Defined as: r x s = {t q | t r and q s} n r(R)과 s(S)의 애트리뷰트가 서로 다르다고 가정하 자(즉, R S = ). n r(R)과 s(S)의 애트리뷰트가 서로 다르지 않다면, 재명명을 사용해야 한다. Database System Concepts - 6 th Edition 6. 13 ©Silberschatz, Korth and Sudarshan

복합 연산 (Composition of Operations) n 여러 연산을 사용해 표현식을 만들 수 있다. n Example: A=C(r x s) n rxs n A=C(r x s) Database System Concepts - 6 th Edition 6. 14 ©Silberschatz, Korth and Sudarshan

예제 질의 n 대학에서 가장 높은 급여는 ? l Step 1: 임의의 다른 강사보다 적은 급여를 받는 강사 급여를 모두 찾아라. (i. e. not maximum) – using a copy of instructor under a new name d 4 instructor. salary l ( instructor. salary < d. salary (instructor x d (instructor))) Step 2: 최대값을 갖는 급여를 찾아라. (instructor) – instructor. salary ( instructor. salary < d, salary (instructor x d (instructor))) 4 salary Database System Concepts - 6 th Edition 6. 16 ©Silberschatz, Korth and Sudarshan

예제 질의 n 물리학과 (Physics department)에 소속된 강사 ID와, 이 들 강사가 가르친 모 든 course_id 를 찾으시오. l Query 1 instructor. ID, course_id ( dept_name=“Physics” ( instructor. ID=teaches. ID (instructor x teaches))) l Query 2 instructor. ID, course_id ( instructor. ID=teaches. ID ( dept_name=“Physics” (instructor) x teaches)) Database System Concepts - 6 th Edition 6. 17 ©Silberschatz, Korth and Sudarshan

추가 연산 관계형 대수에 어떠한 능력도 추가되지 않지만 질의를 단순화하는 추가 연산 을 정의한다. n 공통 집합 (Set intersection) n 자연 죠인 (Natural join) n 배정 (Assignment) n 외부 조인 (Outer join) Database System Concepts - 6 th Edition 6. 19 ©Silberschatz, Korth and Sudarshan

공통 집합 연산 n Notation: r s n Defined as: r s = { t | t r and t s } n 가정: - r과 s는 같은 항수를 갖는다. - r과 s의 애트리뷰트는 양립성이 있다. n 유의: r s = r - (r - s) Database System Concepts - 6 th Edition 6. 20 ©Silberschatz, Korth and Sudarshan

Set-Intersection Operation – Example n Relation r, s: n r s Database System Concepts - 6 th Edition 6. 21 ©Silberschatz, Korth and Sudarshan

Natural Join Example n Relations r, s: n r s Database System Concepts - 6 th Edition 6. 23 ©Silberschatz, Korth and Sudarshan

Natural Join and Theta Join n Comp. Sci. department에 속한 모든 강사 이름과 이 들 강사가 가르친 모든 코스 명을 찾으시오. name, title ( dept_name=“Comp. Sci. ” (instructor teaches course)) n Natural join 연산에는 결합법칙이 성립한다. l (instructor teaches) (teaches course) is equivalent to n Natural join 연산에는 교환법칙이 성립한다. l instruct teaches instructor is equivalent to n 세타 조인 (theta join) 연산자 r l r s = Database System Concepts - 6 th Edition s 는 다음과 같이 정의된다. (r x s) 6. 24 ©Silberschatz, Korth and Sudarshan

Outer Join – Example n Relation instructor name ID Srinivasan Wu Mozart 10101 12121 15151 dept_name Comp. Sci. Finance Music n Relation teaches ID 10101 12121 76766 Database System Concepts - 6 th Edition course_id CS-101 FIN-201 BIO-101 6. 27 ©Silberschatz, Korth and Sudarshan

Outer Join – Example n Join instructor teaches ID 10101 12121 name Srinivasan Wu dept_name course_id Comp. Sci. Finance CS-101 FIN-201 dept_name course_id Comp. Sci. Finance Music CS-101 FIN-201 null n Left Outer Join instructor ID 10101 12121 15151 Database System Concepts - 6 th Edition teaches name Srinivasan Wu Mozart 6. 28 ©Silberschatz, Korth and Sudarshan

Outer Join – Example n Right Outer Join instructor teaches ID 10101 12121 76766 name Srinivasan Wu null dept_name course_id Comp. Sci. Finance null CS-101 FIN-201 BIO-101 n Full Outer Join instructor teaches ID 10101 12121 15151 76766 Database System Concepts - 6 th Edition name Srinivasan Wu Mozart null dept_name course_id Comp. Sci. Finance Music null CS-101 FIN-201 null BIO-101 6. 29 ©Silberschatz, Korth and Sudarshan

나누기 연산 (Division Operator) n r s “모두에 대한”이라는 구절을 내포한 질의에 적합하다. q = r s라 하자. 그러면 q는 q s r을 만족하는 가장 큰 릴레이션이다. n E. g. let r(ID, course_id) = ID, course_id (takes ) and s(course_id) = course_id ( dept_name=“Biology”(course ) then r s gives us students who have taken all courses in the Biology department n Can write r s as temp 1 R-S (r ) temp 2 R-S ((temp 1 x s ) – R-S, S (r )) result = temp 1 – temp 2 Database System Concepts - 6 th Edition 6. 30 ©Silberschatz, Korth and Sudarshan

나누기 연산의 예제 n 릴레이션 r, s : A B 1 2 3 1 1 1 3 4 6 1 2 n r s A Database System Concepts - 6 th Edition B 1 2 s r 6. 31 ©Silberschatz, Korth and Sudarshan

또 다른 나누기 예제 n 릴레이션 r, s: n r s A B a a a a C D a a b a b b E 1 1 3 1 1 1 D E a 1 b 1 s r A Database System Concepts - 6 th Edition B a a. 6. 32 C ©Silberschatz, Korth and Sudarshan

확장 관계형 대수 연산 n 일반화 추출 (Generalized Projection) n 집성 합수 (Aggregate Functions) Database System Concepts - 6 th Edition 6. 33 ©Silberschatz, Korth and Sudarshan

일반화 추출 n 추출 리스트에 산술 함수를 사용 하도록 함으로써 추출 연산을 확장한다. F 1, F 2, …, Fn(E) n E는 관계형 대수 표현식이다. n F 1, F 2, …, Fn 각각은 E의 스키마 내에 상수와 애트리뷰트를 내포하고 있 는 산술 표현식이다. n Given relation instructor(ID, name, dept_name, salary) where salary is annual salary, get the same information but with monthly salary ID, name, dept_name, salary/12 (instructor) Database System Concepts - 6 th Edition 6. 34 ©Silberschatz, Korth and Sudarshan

집성 함수 n Aggregation function: 값의 모임을 취해 하나의 값을 결과로 돌려준다. avg: average value min: minimum value max: maximum value sum: sum of values count: number of values n Aggregate operation in relational algebra E is any relational-algebra expression l G 1, G 2 …, Gn is a list of attributes on which to group (can be empty) l Each Fi is an aggregate function l Each Ai is an attribute name Database System Concepts - 6 th Edition 6. 35 ©Silberschatz, Korth and Sudarshan

Aggregate Operation – Example n Relation r: n sum(c) (r) A B C 7 7 3 10 sum(c ) 27 Database System Concepts - 6 th Edition 6. 36 ©Silberschatz, Korth and Sudarshan

SQL and Relational Algebra n select A 1, A 2, . . An from r 1, r 2, …, rm where P is equivalent to the following expression in multiset relational algebra A 1, . . , An ( P (r 1 x r 2 x. . x rm)) n select A 1, A 2, sum(A 3) from r 1, r 2, …, rm where P group by A 1, A 2 is equivalent to the following expression in multiset relational algebra A 1, A 2 Database System Concepts - 6 th Edition sum(A 3) ( P (r 1 x r 2 x. . x rm))) 6. 38 ©Silberschatz, Korth and Sudarshan

Tuple Relational Calculus Database System Concepts - 6 th Edition 6. 39 ©Silberschatz, Korth and Sudarshan

Example Queries n 급여가 $80, 000 이상인 강사의 ID, name, dept_name, salary 를 찾으 시오. {t | t instructor t [salary ] 80000} n 앞의 질의에서 ID 속성값만을 찾으시오. {t | s instructor (t [ID ] = s [ID ] s [salary ] 80000)} Notice that a relation on schema (ID) is implicitly defined by the query Database System Concepts - 6 th Edition 6. 42 ©Silberschatz, Korth and Sudarshan

Domain Relational Calculus Database System Concepts - 6 th Edition 6. 43 ©Silberschatz, Korth and Sudarshan

Example Queries n Find the ID, name, dept_name, salary for instructors whose salary is greater than $80, 000 l n {< i, n, d, s> | < i, n, d, s> instructor s 80000} As in the previous query, but output only the ID attribute value l {< i> | < i, n, d, s> instructor s 80000} n Find the names of all instructors whose department is in the Watson building {< n > | i, d, s (< i, n, d, s > instructor b, a (< d, b, a> department b = “Watson” ))} Database System Concepts - 6 th Edition 6. 45 ©Silberschatz, Korth and Sudarshan

Example Queries n Fall 2009 semester 혹은 Spring 2010 semester 에 개설된 모든 코스 정보를 찾으시오. {<c> | a, s, y, b, r, t ( <c, a, s, y, b, t > section s = “Fall” y = 2009 ) v a, s, y, b, r, t ( <c, a, s, y, b, t > section ] s = “Spring” y = 2010)} This case can also be written as {<c> | a, s, y, b, r, t ( <c, a, s, y, b, t > section ( (s = “Fall” y = 2009 ) v (s = “Spring” y = 2010))} n Fall 2009 semester 와 Spring 2010 semester 에 개설된 모든 코스 정보를 찾으시오. {<c> | a, s, y, b, r, t ( <c, a, s, y, b, t > section s = “Fall” y = 2009 ) a, s, y, b, r, t ( <c, a, s, y, b, t > section ] s = “Spring” y = 2010)} Database System Concepts - 6 th Edition 6. 46 ©Silberschatz, Korth and Sudarshan

End of Chapter 6 Database System Concepts, 6 th Ed. ©Silberschatz, Korth and Sudarshan See www. db-book. com for conditions on re-use

Figure 6. 01 Database System Concepts - 6 th Edition 6. 48 ©Silberschatz, Korth and Sudarshan

Figure 6. 02 Database System Concepts - 6 th Edition 6. 49 ©Silberschatz, Korth and Sudarshan

Figure 6. 03 Database System Concepts - 6 th Edition 6. 50 ©Silberschatz, Korth and Sudarshan

Figure 6. 04 Database System Concepts - 6 th Edition 6. 51 ©Silberschatz, Korth and Sudarshan

Figure 6. 05 Database System Concepts - 6 th Edition 6. 52 ©Silberschatz, Korth and Sudarshan

Figure 6. 06 Database System Concepts - 6 th Edition 6. 53 ©Silberschatz, Korth and Sudarshan

Figure 6. 07 Database System Concepts - 6 th Edition 6. 54 ©Silberschatz, Korth and Sudarshan

Figure 6. 08 Database System Concepts - 6 th Edition 6. 55 ©Silberschatz, Korth and Sudarshan

Figure 6. 09 Database System Concepts - 6 th Edition 6. 56 ©Silberschatz, Korth and Sudarshan

Figure 6. 10 Database System Concepts - 6 th Edition 6. 57 ©Silberschatz, Korth and Sudarshan

Figure 6. 11 Database System Concepts - 6 th Edition 6. 58 ©Silberschatz, Korth and Sudarshan

Figure 6. 12 Database System Concepts - 6 th Edition 6. 59 ©Silberschatz, Korth and Sudarshan

Figure 6. 13 Database System Concepts - 6 th Edition 6. 60 ©Silberschatz, Korth and Sudarshan

Figure 6. 14 Database System Concepts - 6 th Edition 6. 61 ©Silberschatz, Korth and Sudarshan

Figure 6. 15 Database System Concepts - 6 th Edition 6. 62 ©Silberschatz, Korth and Sudarshan

Figure 6. 16 Database System Concepts - 6 th Edition 6. 63 ©Silberschatz, Korth and Sudarshan

Figure 6. 17 Database System Concepts - 6 th Edition 6. 64 ©Silberschatz, Korth and Sudarshan

Figure 6. 18 Database System Concepts - 6 th Edition 6. 65 ©Silberschatz, Korth and Sudarshan

Figure 6. 19 Database System Concepts - 6 th Edition 6. 66 ©Silberschatz, Korth and Sudarshan

Figure 6. 20 Database System Concepts - 6 th Edition 6. 67 ©Silberschatz, Korth and Sudarshan

Figure 6. 21 Database System Concepts - 6 th Edition 6. 68 ©Silberschatz, Korth and Sudarshan

Deletion n A delete request is expressed similarly to a query, except instead of displaying tuples to the user, the selected tuples are removed from the database. n Can delete only whole tuples; cannot delete values on only particular attributes n A deletion is expressed in relational algebra by: r r–E where r is a relation and E is a relational algebra query. Database System Concepts - 6 th Edition 6. 69 ©Silberschatz, Korth and Sudarshan

Deletion Examples n Delete all account records in the Perryridge branch. account – branch_name = “Perryridge” (account ) n Delete all loan records with amount in the range of 0 to 50 loan – amount 0 and amount 50 (loan) n Delete all accounts at branches located in Needham. r 1 branch_city = “Needham” (account branch ) r 2 account_number, branch_name, balance (r 1) r 3 customer_name, account_number (r 2 depositor) account – r 2 depositor – r 3 Database System Concepts - 6 th Edition 6. 70 ©Silberschatz, Korth and Sudarshan

Insertion n To insert data into a relation, we either: l specify a tuple to be inserted l write a query whose result is a set of tuples to be inserted n in relational algebra, an insertion is expressed by: r r E where r is a relation and E is a relational algebra expression. n The insertion of a single tuple is expressed by letting E be a constant relation containing one tuple. Database System Concepts - 6 th Edition 6. 71 ©Silberschatz, Korth and Sudarshan

Insertion Examples n Insert information in the database specifying that Smith has $1200 in account A-973 at the Perryridge branch. account {(“A-973”, “Perryridge”, 1200)} depositor {(“Smith”, “A-973”)} n Provide as a gift for all loan customers in the Perryridge branch, a $200 savings account. Let the loan number serve as the account number for the new savings account. r 1 ( branch_name = “Perryridge” (borrower loan)) account loan_number, branch_name, 200 (r 1) depositor customer_name, loan_number (r 1) Database System Concepts - 6 th Edition 6. 72 ©Silberschatz, Korth and Sudarshan

Updating n A mechanism to change a value in a tuple without charging all values in the tuple n Use the generalized projection operator to do this task n Each Fi is either l the I th attribute of r, if the I th attribute is not updated, or, l if the attribute is to be updated Fi is an expression, involving only constants and the attributes of r, which gives the new value for the attribute Database System Concepts - 6 th Edition 6. 73 ©Silberschatz, Korth and Sudarshan

Update Examples n Make interest payments by increasing all balances by 5 percent. account_number, branch_name, balance * 1. 05 (account) n Pay all accounts with balances over $10, 000 6 percent interest and pay all others 5 percent account_number, branch_name, balance * 1. 06 ( BAL 10000 (account )) account_number, branch_name, balance * 1. 05 ( BAL 10000 (account)) Database System Concepts - 6 th Edition 6. 74 ©Silberschatz, Korth and Sudarshan

Example Queries n Find the names of all customers who have a loan and an account at bank. customer_name (borrower) customer_name (depositor) n Find the name of all customers who have a loan at the bank and the loan amount customer_name, loan_number, amount (borrower Database System Concepts - 6 th Edition 6. 75 loan) ©Silberschatz, Korth and Sudarshan

Example Queries n Find all customers who have an account from at least the “Downtown” and the Uptown” branches. l Query 1 customer_name ( branch_name = “Downtown” (depositor account )) customer_name ( branch_name = “Uptown” (depositor l account)) Query 2 customer_name, branch_name (depositor account) temp(branch_name) ({(“Downtown” ), (“Uptown” )}) Note that Query 2 uses a constant relation. Database System Concepts - 6 th Edition 6. 76 ©Silberschatz, Korth and Sudarshan

Bank Example Queries n Find all customers who have an account at all branches located in Brooklyn city. customer_name, branch_name (depositor account) branch_name ( branch_city = “Brooklyn” (branch)) Database System Concepts - 6 th Edition 6. 77 ©Silberschatz, Korth and Sudarshan