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: Formal Relational Query Languages 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 Procedural language n Six basic operators l select: l project: l union: l set difference: – l Cartesian product: x l rename: n The operators take one or two relations as inputs and produce a new relation as a result. 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 is called the selection predicate n Defined as: p(r) = {t | t r and p(t)} Where p is a formula in propositional calculus consisting of terms connected by : (and), (or), (not) Each term is one of: <attribute> op <attribute> or <constant> where op is one of: =, , >, . <. 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: where A 1, A 2 are attribute names and r is a relation name. n The result is defined as the relation of k columns obtained by erasing the columns that are not listed n Duplicate rows removed from result, since relations are sets 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 For r s to be valid. 1. r, s must have the same arity (same number of attributes) 2. The attribute domains must be compatible (example: 2 nd column of r deals with the same type of values as does the 2 nd column of 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 n Set differences must be taken between compatible relations. l r and s must have the same arity l attribute domains of r and s must be compatible 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 Assume that attributes of r(R) and s(S) are disjoint. (That is, R S = ). n If attributes of r(R) and s(S) are not disjoint, then renaming must be used. Database System Concepts - 6 th Edition 6. 13 ©Silberschatz, Korth and Sudarshan
Composition of Operations n Can build expressions using multiple operations 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
Rename Operation n Allows us to name, and therefore to refer to, the results of relational- algebra expressions. n Allows us to refer to a relation by more than one name. n Example: x (E) returns the expression E under the name X n If a relational-algebra expression E has arity n, then returns the result of expression E under the name X, and with the attributes renamed to A 1 , A 2 , …. , An. Database System Concepts - 6 th Edition 6. 15 ©Silberschatz, Korth and Sudarshan
Example Query n Find the largest salary in the university l Step 1: find instructor salaries that are less than some other instructor salary (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: Find the largest salary (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
Example Queries n Find the names of all instructors in the Physics department, along with the course_id of all courses they have taught 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
Formal Definition n A basic expression in the relational algebra consists of either one of the following: l A relation in the database l A constant relation n Let E 1 and E 2 be relational-algebra expressions; the following are all relational-algebra expressions: l E 1 E 2 l E 1 – E 2 l E 1 x E 2 l p (E 1), P is a predicate on attributes in E 1 l s(E 1), S is a list consisting of some of the attributes in E 1 l x (E 1), x is the new name for the result of E 1 Database System Concepts - 6 th Edition 6. 18 ©Silberschatz, Korth and Sudarshan
Additional Operations We define additional operations that do not add any power to the relational algebra, but that simplify common queries. n Set intersection n Natural join n Assignment n Outer join Database System Concepts - 6 th Edition 6. 19 ©Silberschatz, Korth and Sudarshan
Set-Intersection Operation n Notation: r s n Defined as: n r s = { t | t r and t s } n Assume: l r, s have the same arity l attributes of r and s are compatible n Note: 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 Operation n Notation: r s n Let r and s be relations on schemas R and S respectively. s is a relation on schema R S obtained as follows: Then, r l Consider each pair of tuples tr from r and ts from s. l If tr and ts have the same value on each of the attributes in R S, add a tuple t to the result, where 4 t has the same value as tr on r 4 t has the same value as ts on s n Example: R = (A, B, C, D) S = (E, B, D) l Result schema = (A, B, C, D, E) l r s is defined as: r. A, r. B, r. C, r. D, s. E ( r. B = s. B r. D = s. D (r x s)) Database System Concepts - 6 th Edition 6. 22 ©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 n Find the names of all instructors in the Comp. Sci. department together with the course titles of all the courses that the instructors teach l name, title ( dept_name=“Comp. Sci. ” (instructor teaches course)) n Natural join is associative l (instructor teaches) (teaches course) is equivalent to n Natural join is commutative l instruct teaches Database System Concepts - 6 th Edition teaches instructor is equivalent to 6. 24 ©Silberschatz, Korth and Sudarshan
Assignment Operation n The assignment operation ( ) provides a convenient way to express complex queries. l Write query as a sequential program consisting of 4 a series of assignments 4 followed by an expression whose value is displayed as a result of the query. l Assignment must always be made to a temporary relation variable. Database System Concepts - 6 th Edition 6. 25 ©Silberschatz, Korth and Sudarshan
Outer Join n An extension of the join operation that avoids loss of information. n Computes the join and then adds tuples form one relation that does not match tuples in the other relation to the result of the join. n Uses null values: l null signifies that the value is unknown or does not exist l All comparisons involving null are (roughly speaking) false by definition. 4 We shall study precise meaning of comparisons with nulls later Database System Concepts - 6 th Edition 6. 26 ©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
Null Values n It is possible for tuples to have a null value, denoted by null, for some of their attributes n null signifies an unknown value or that a value does not exist. n The result of any arithmetic expression involving null is null. n Aggregate functions simply ignore null values (as in SQL) n For duplicate elimination and grouping, null is treated like any other value, and two nulls are assumed to be the same (as in SQL) Database System Concepts - 6 th Edition 6. 30 ©Silberschatz, Korth and Sudarshan
Null Values n Comparisons with null values return the special truth value: unknown n Three-valued logic using the truth value unknown: l OR: (unknown or true) = true, (unknown or false) = unknown (unknown or unknown) = unknown l AND: (true and unknown) = unknown, (false and unknown) = false, (unknown and unknown) = unknown l NOT: (not unknown) = unknown l In SQL “P is unknown” evaluates to true if predicate P evaluates to unknown n Result of select predicate is treated as false if it evaluates to unknown Database System Concepts - 6 th Edition 6. 31 ©Silberschatz, Korth and Sudarshan
Division Operator n Given relations r(R) and s(S), such that S R, r s is the largest relation t(R-S) such that txs 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 l The result to the right of the is assigned to the relation variable on the left of the . l May use variable in subsequent expressions. Database System Concepts - 6 th Edition 6. 32 ©Silberschatz, Korth and Sudarshan
n relations r, s: n r s: A A B B 1 2 3 1 1 1 3 4 6 1 2 s r Database System Concepts - 6 th Edition 6. 33 ©Silberschatz, Korth and Sudarshan
Extended Relational-Algebra-Operations n Generalized Projection n Aggregate Functions Database System Concepts - 6 th Edition 6. 34 ©Silberschatz, Korth and Sudarshan
Generalized Projection n Extends the projection operation by allowing arithmetic functions to be used in the projection list. n E is any relational-algebra expression n Each of F 1, F 2, …, Fn are arithmetic expressions involving constants and attributes in the schema of 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. 35 ©Silberschatz, Korth and Sudarshan
Aggregate Functions and Operations n Aggregation function takes a collection of values and returns a single value as a result. 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 n Note: Some books/articles use Database System Concepts - 6 th Edition instead of 6. 36 (Calligraphic G) ©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. 37 ©Silberschatz, Korth and Sudarshan
Aggregate Operation – Example n Find the average salary in each department dept_name avg(salary) (instructor) avg_salary Database System Concepts - 6 th Edition 6. 38 ©Silberschatz, Korth and Sudarshan
Aggregate Functions (Cont. ) n Result of aggregation does not have a name l Can use rename operation to give it a name l For convenience, we permit renaming as part of aggregate operation dept_name Database System Concepts - 6 th Edition avg(salary) as avg_sal (instructor) 6. 39 ©Silberschatz, Korth and Sudarshan
Modification of the Database n The content of the database may be modified using the following operations: l Deletion l Insertion l Updating n All these operations can be expressed using the assignment operator Database System Concepts - 6 th Edition 6. 40 ©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. 41 ©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) Database System Concepts - 6 th Edition 6. 42 ©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. 43 ©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”)} Database System Concepts - 6 th Edition 6. 44 ©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. 45 ©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. 46 ©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. 47 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. 48 ©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. 49 ©Silberschatz, Korth and Sudarshan
- Slides: 49