Outline Relational Algebra Tuple Relational Calculus Domain Relational Calculus Database System Concepts-6th Edition 6.2 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 6.2 ©Silberschatz, Korth and Sudarshan th Edition Outline Relational Algebra Tuple Relational Calculus Domain Relational Calculus
Relational Algebra Procedural language Six basic operators select:o project:.Π union: set difference:- Cartesian product:x rename:p The operators take one or two relations as inputs and produce a new relation as a result. Database System Concepts-6th Edition 6.3 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 6 6.3 ©Silberschatz, Korth and Sudarshan th Edition Relational Algebra Procedural language Six basic operators select: project: union: set difference: – Cartesian product: x rename: The operators take one or two relations as inputs and produce a new relation as a result
Select Operation Notation:op(r) p is called the selection predicate Defined as: op(=t|t∈rand p(} Where p is a formula in propositional calculus consisting of terms connected by ^(and),v(or),(not) Each term is one of: op or where op is one of:=,≠，>，≥.<.≤ Example of selection: o dept_name="Physics"(instructor) Database System Concepts-6th Edition 6.4 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 6.4 ©Silberschatz, Korth and Sudarshan th Edition Select Operation Notation: p (r) p is called the selection predicate 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: op or where op is one of: =, , >, . <. Example of selection: dept_name=“Physics”(instructor)
Project Operation Notation: Π有44） where A1,A2 are attribute names and ris a relation name. The result is defined as the relation of k columns obtained by erasing the columns that are not listed Duplicate rows removed from result,since relations are sets Example:To eliminate the dept name attribute of instructor IIID,name,salary(instructor) Database System Concepts-6th Edition 6.5 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 6.5 ©Silberschatz, Korth and Sudarshan th Edition Project Operation Notation: where A1 , A2 are attribute names and r is a relation name. The result is defined as the relation of k columns obtained by erasing the columns that are not listed Duplicate rows removed from result, since relations are sets Example: To eliminate the dept_name attribute of instructor ID, name, salary (instructor) ( ) , , 2 , 1 r k A A A
Union Operation Notation:rs Defined as: rUs={tlt∈rort∈S} For rs to be valid. 1.r,s must have the same arity (same number of attributes) 2.The attribute domains must be compatible(example:2nd column of rdeals with the same type of values as does the 2nd column of s) Example:to find all courses taught in the Fall 2009 semester,or in the Spring 2010 semester,or in both ncourse_id(semester="Fall"Ayear=2009(section)) Ilcourse_id(o semester="Spring"A year=2010(section)) Database System Concepts-6th Edition 6.6 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 6 6.6 ©Silberschatz, Korth and Sudarshan th Edition Union Operation Notation: r s Defined as: r s = {t | t r or t s} 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: 2nd column of r deals with the same type of values as does the 2nd column of s) 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))
Set Difference Operation Notation r-s Defined as: r-s={tlt∈rand tgs} Set differences must be taken between compatible relations. r and s must have the same arity 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 Ilcourse_id(o semester="Fall year=2009(section) Icourse_id(semester="Spring"Ayear=2010(section)) Database System Concepts-6th Edition 6.7 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 6 6.7 ©Silberschatz, Korth and Sudarshan th Edition Set Difference Operation Notation r – s Defined as: r – s = {t | t r and t s} Set differences must be taken between compatible relations. r and s must have the same arity 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))
Set-Intersection Operation Notation:r∩s Defined as: ros={tlt∈rand t∈s} Assume: r,s have the same arity attributes of r and s are compatible Note:r∩s=r-(r-s) Database System Concepts-6th Edition 6.8 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 6.8 ©Silberschatz, Korth and Sudarshan th Edition Set-Intersection Operation Notation: r s Defined as: r s = { t | t r and t s } Assume: r, s have the same arity attributes of r and s are compatible Note: r s = r – (r – s)
Cartesian-Product Operation Notation rx s Defined as: rxs={tqlt∈r and q∈s} Assume that attributes of r(R)and s(S)are disjoint.(That is,RS=. If attributes of r(R)and s(S)are not disjoint,then renaming must be used. Database System Concepts-6th Edition 6.9 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 6.9 ©Silberschatz, Korth and Sudarshan th Edition Cartesian-Product Operation Notation r x s Defined as: r x s = {t q | t r and q s} Assume that attributes of r(R) and s(S) are disjoint. (That is, R S = ). If attributes of r(R) and s(S) are not disjoint, then renaming must be used
Rename Operation Allows us to name,and therefore to refer to,the results of relational- algebra expressions. Allows us to refer to a relation by more than one name. Example: Px(E) returns the expression E under the name X If a relational-algebra expression E has arity n,then Px(4424n)(E) returns the result of expression E under the name X,and with the attributes renamed to A1,A2,....,An Database System Concepts-6th Edition 6.10 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 6.10 ©Silberschatz, Korth and Sudarshan th Edition Rename Operation Allows us to name, and therefore to refer to, the results of relationalalgebra expressions. Allows us to refer to a relation by more than one name. Example: x (E) returns the expression E under the name X 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 A1 , A2 , …., An . ( ) ,..., ) 2 , 1 ( E n x A A A
Formal Definition A basic expression in the relational algebra consists of either one of the following: A relation in the database A constant relation Let E,and E2 be relational-algebra expressions;the following are all relational-algebra expressions: E10 E2 E1-E2 E1x E2 Op(E),P is a predicate on attributes in E Is(E),S is a list consisting of some of the attributes in E Px(E),x is the new name for the result of E Database System Concepts-6th Edition 6.11 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 6 6.11 ©Silberschatz, Korth and Sudarshan th Edition Formal Definition A basic expression in the relational algebra consists of either one of the following: A relation in the database A constant relation Let E1 and E2 be relational-algebra expressions; the following are all relational-algebra expressions: E1 E2 E1 – E2 E1 x E2 p (E1 ), P is a predicate on attributes in E1 s (E1 ), S is a list consisting of some of the attributes in E1 x (E1 ), x is the new name for the result of E1