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: <attribute> op <attribute>or <constant> 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: <attribute> op <attribute> or <constant> 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))