Database System Concepts, 6th Ed.Silberschatz, Korth and SudarshanSee www.db- for conditions on re-use Chapter 6: Formal Relational Query Languages Silberschatz, Korth and Sudarshan6.2Database System Concepts - 6th EditionChapter 6: Formal Relational Query LanguagesChapter 6: Formal Relational Query LanguagesnRelational AlgebranTuple Relational CalculusnDomain Relational CalculusSilberschatz, Korth and Sudarshan6.3Database System Concepts - 6th EditionRelational AlgebranProcedural languagenSix basic operatorslselect: lproject: lunion: lset difference: lCartesian product: xlrename: nThe operators take one or two relations as inputs and produce a new relation as a result.Silberschatz, Korth and Sudarshan6.4Database System Concepts - 6th EditionSelect Operation ExamplenRelation rA=B D 5 (r)Silberschatz, Korth and Sudarshan6.5Database System Concepts - 6th EditionSelect OperationnNotation: p(r)np is called the selection predicatenDefined 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: =, , , . . nExample of selection: dept_name=“Physics”(instructor)Silberschatz, Korth and Sudarshan6.6Database System Concepts - 6th EditionProject Operation ExamplenRelation r:n A,C (r)Silberschatz, Korth and Sudarshan6.7Database System Concepts - 6th EditionProject OperationnNotation:where A1, A2 are attribute names and r is a relation name.nThe result is defined as the relation of k columns obtained by erasing the columns that are not listednDuplicate rows removed from result, since relations are setsnExample: To eliminate the dept_name attribute of instructor ID, name, salary (instructor) Silberschatz, Korth and Sudarshan6.8Database System Concepts - 6th EditionUnion Operation Example nRelations r, s:nr s:Silberschatz, Korth and Sudarshan6.9Database System Concepts - 6th EditionUnion OperationnNotation: r snDefined as: r s = t | t r or t snFor 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)nExample: 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)Silberschatz, Korth and Sudarshan6.10Database System Concepts - 6th EditionSet difference of two relationsnRelations r, s:nr s:Silberschatz, Korth and Sudarshan6.11Database System Concepts - 6th EditionSet Difference OperationnNotation r snDefined as: r s = t | t r and t snSet differences must be taken between compatible relations.lr and s must have the same aritylattribute domains of r and s must be compatiblenExample: 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)Silberschatz, Korth and Sudarshan6.12Database System Concepts - 6th EditionCartesian-Product Operation ExamplenRelations r, s:nr x s:Silberschatz, Korth and Sudarshan6.13Database System Concepts - 6th EditionCartesian-Product OperationnNotation r x snDefined as:r x s = t q | t r and q snAssume that attributes of r(R) and s(S) are disjoint. (That is, R S = ).nIf attributes of r(R) and s(S) are not disjoint, then renaming must be used.Silberschatz, Korth and Sudarshan6.14Database System Concepts - 6th EditionComposition of OperationsnCan build expressions using multiple operationsnExample: A=C(r x s)nr x snA=C(r x s)Silberschatz, Korth and Sudarshan6.15Database System Concepts - 6th EditionRename OperationnAllows us to name, and therefore to refer to, the results of relational-algebra expressions.nAllows us to refer to a relation by more than one name.nExample: x (E)returns the expression E under the name XnIf a relational-algebra expression E has arity n, then returns the result of expression E under the name X, and with theattributes renamed to A1 , A2 , ., An .Silberschatz, Korth and Sudarshan6.16Database System Concepts - 6th EditionExample QuerynFind the largest salary in the universitylStep 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 d4instructor.salary ( instructor.salary d,salary (instructor x d (instructor) lStep 2: Find the largest salary4salary (instructor) instructor.salary ( instructor.salary d,salary (instructor x d (instructor) Silberschatz, Korth and Sudarshan6.17Database System Concepts - 6th EditionExample QueriesnFind the names of all instructors in the Physics department, along with the course_id of all courses they have taughtlQuery 1 instructor.ID,course_id (dept_name=“Physics” ( instructor.ID=teaches.ID (instructor x teaches)lQuery 2 instructor.ID,course_id (instructor.ID=teaches.ID ( dept_name=“Physics” (instructor) x teaches)Silberschatz, Korth an