1 / 66

The Relational Algebra

The Relational Algebra. Chapter Outline. Fundamental Operator Deliverable & Additional Operator. What is Relational Algebra?. Relational Algebra is a Procedural Paradigm You Need to Tell What/How to Construct the Result

arama
Download Presentation

The Relational Algebra

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. The Relational Algebra Database System-dww

  2. Chapter Outline • Fundamental Operator • Deliverable & Additional Operator Database System-dww

  3. What is Relational Algebra? • Relational Algebra is a Procedural ParadigmYou Need to Tell What/How to Construct the Result • Consists of a Set of Operators Which, When Applied to Relations, Yield Relations (Closed Algebra) • Basic Relational Operations: • Unary Operations • SELECT s • PROJECT  or P. • Binary Operations • Set operations: • UNION  • INTERSECTION  • DIFFERENCE – • CARTESIAN PRODUCT  • JOIN operations  Database System-dww

  4. Relational Algebra R S union R S intersection R \ S set difference R S Cartesian product A1, A2, ..., An (R) projection F (R) selection R S natural join R S theta-join RSdivision [A1 B1,.., An Bn] rename Database System-dww

  5. Selection • Selects the Set of Tuples (Rows) From a Relation, Which Satisfy a Selection Condition • General Form s<selection condition> (R) • R is a Relation • Selection condition is a Boolean Expression on the Attributes of R • Resulting Relation Has the Same Schema as R • Select Finds and Retrieves All Relevant Rows (Tuples) of Table/Relation R which Includes ALL of its Columns (Attributes) Database System-dww

  6. EMP TITLE='Elect. Eng.'(EMP) ENO ENAME TITLE ENO ENAME TITLE E1 J. Doe Elect. Eng. E2 M. Smith Syst. Anal. E3 A. Lee Mech. Eng. E4 J. Miller Programmer E5 B. Casey Syst. Anal. E6 L. Chu Elect. Eng. E7 R. Davis Mech. Eng. E8 J. Jones Syst. Anal. E1 J. Doe Elect. Eng E6 L. Chu Elect. Eng. Selection Example TITLE='Elect. Eng.’ OR TITLE=‘Mech.Eng’(EMP) Database System-dww

  7. Another Selection Example A S C null W B null null Database System-dww

  8. A SELECT Condition is a Boolean Expression Form F1Y F2Y ..., Y Fq (Q>=1), Where Fi (I=1,…,q) are Atomic Boolean Expressions of the Form aqc or aqb, a, b are Attributes of R and c is a Constant. The Operator q is one of the Arithmetic Comparison Operators: <, >, =, <>, >=, <= The Operator Y is one of the Logical Operators: , , ¬ Nesting: ( ) Selection Condition Database System-dww

  9. Select certain Columns (Attributes) Specified in an Attribute ListX From a Relation R General Form <attribute-list>(R) R is a Relation Attribute-list is a Subset of the Attributes of R Over Which the Projection is Performed Project Retrieves Specified Columns of Table/Relation R which Includes ALL of itsRows (Tuples) Projection Database System-dww

  10. PROJ PNO PNAME BUDGET P1 Instrumentation 150000 P2 Database Develop. 135000 PNO,BUDGET(PROJ) P3 CAD/CAM 250000 P4 Maintenance 310000 P5 CAD/CAM 500000 PNO BUDGET P1 150000 P2 135000 P3 250000 P4 310000 P5 500000 Projection Example Database System-dww

  11. Other Projection Examples Database System-dww

  12. Several Operations can be Combined to form a Relational Algebra Expression (query) Example: Retrieve all Customers over age 60? Method 1: CNAME, ADDRESS, AGE (sAGE>60(CUSTOMER) ) Method 2: Senior-CUST(C#, Addr, Age) = CNAME, ADDRESS, AGE (sAGE>60(CUSTOMER) ) Method 3: CNAME, ADDRESS, AGE (C) where C = sAGE>60(CUSTOMER) Relational Algebra Expression Database System-dww

  13. EMP ENO ENAME TITLE E1 J. Doe Elect. Eng. E2 M. Smith Syst. Anal. E3 A. Lee Mech. Eng. E4 J. Miller Programmer E5 B. Casey Syst. Anal. TITLE(PROJ) E6 L. Chu Elect. Eng. TITLE E7 R. Davis Mech. Eng. Elect.Eng Syst.Anal Mech.Eng Programmer E8 J. Jones Syst. Anal. Characteristics of Projection • The PROJECT Operation Eliminates Duplicate Tuples in the Resulting Relation • Why? • Projection Must Maintain a Mathematical Set (No Duplicate Elements) Database System-dww

  14. Selection with Projection Example Database System-dww

  15. Renaming • The RENAME operator gives a new schema to a relation. • R1 := RENAMER1(A1,…,An)(R2) makes R1 be a relation with attributes A1,…,An and the same tuples as R2. • Simplified notation: R1(A1,…,An) := R2. • Other use () notation • R2  R1(A1,…,An) Database System-dww

  16. Sequences of Assignments • Create temporary relation names. • Renaming can be implied by giving relations a list of attributes. • Example: R3 := R1 JOINC R2 can be written: R4 := R1 * R2 R3 := SELECTC (R4) OR R4  R1 * R2 R3  SELECTC (R4) Database System-dww

  17. General Form RS where R, S are Relations Result contains Tuples from both R and S Duplications are Removed The two Operands R, S should be union-compatible Example: “find students registered for course C1 or C3” s#(CNO=‘C1’ (S-C)) s#(CNO=‘C3’ (S-C)) Union Database System-dww

  18. Two Relations R1(A1, A2, ..., An) and R2(B1, B2, ..., Bn) are said Union-compatible If and Only If They Have The Same Number of Attributes The Domains of Corresponding Attributes are Compatible, i.e., Dom(Ai)=dom(Bi) for i=1, 2, ..., N Names Do Not Have to be Same! For Relational Union and Difference Operations, the Operand Relations Must Be Union Compatible The Resulting Relation for Relational Set Operations Has the Same Attribute Names as the First Operand Relation R1 (by Convention) Union Compatibility Database System-dww

  19. General Form R – S where R and S are Relations Result Contains all tuples that are in R, but not in S. R – S <> S – R Again, there Must be Compatibility Example “Find the students who registered course C1 but not C3” s#(CNO=‘C1’ (S-C)) –s#(CNO=‘C3’ (S-C)) Set Difference Database System-dww

  20. General Form RS where R and S are Relations Result Contains all Tuples that are in R and S. RS = R – (R – S) Again, there Must be Compatibility Example “find the students who registered for both C1 and C3” s#(CNO=‘C1’ (S-C)) s#(CNO=‘C3’ (S-C)) Set Intersection Database System-dww

  21. Union, Difference, Intersection Examples What are these Other Three Result Tables? STUDENT  INSTRUCTOR STUDENT - INSTRUCTOR Database System-dww INSTRUCTOR - STUDENT

  22. Given Relations R of Degree k1 and Cardinality card1 S of Degree k2 and Cardinality card2 Cartesian Product RS is a Relation of Degree (k1+ k2) and Consists of Tuples of Degree (k1+ k2) where each Tuple is a Concatenation of one Tuple of R with one Tuple of S Cardinality of the Result of the Cartesian Product RS is card1 * card2 What is One Problem with Cartesian Product w.r.t. the Result Set? Cartesian Product Database System-dww

  23. Cartesian Product: Example R S A B C E F a1 a2 a3 b1 b1b4 c3 c5 c7 e1 e2 f1 f5 A B C E F R S  a1 a1 a2 a2 a3 a3 b1 b1 b1 b1 b4 b4 c3 c3 c5 c5 c7 c7 e1 e2 e1 e2 e1 e2 f1 f5 f1 f5 f1 f5 Database System-dww

  24. Given R(A1, …,An) and S(B1,…,Bm), the result of a Cartesian product RS is a relation of schema R’(A1, …, An, B1, …, Bm). Example “Get a list containing (S#, C#) for all students who live in Storrs but are not registered for the database course” Cartesian Product (S#(city=‘Storrs’(STUDENT))   C# (CNAME=‘Database’(COURSE))) –S#, C#(S-C) Database System-dww

  25. EMP SAL EMP ENO ENAME TITLE ENO ENAME EMP.TITLE SAL.TITLE SAL E1 J. Doe Elect. Eng E1 J. Doe Elect. Eng. Elect. Eng. 40000 E2 M. Smith Syst. Anal. E1 J. Doe Elect. Eng. Syst. Anal. 34000 E3 A. Lee Mech. Eng. E1 J. Doe Elect. Eng. Mech. Eng. 27000 E4 J. Miller Programmer E1 J. Doe Elect. Eng. Programmer 24000 E5 B. Casey Syst. Anal. E2 M. Smith Syst. Anal. Elect. Eng. 40000 E6 L. Chu Elect. Eng. E2 M. Smith Syst. Anal. Syst. Anal. 34000 E7 R. Davis Mech. Eng. E2 M. Smith Syst. Anal. Mech. Eng. 27000 E8 J. Jones Syst. Anal. E2 M. Smith Syst. Anal. Programmer 24000 E3 A. Lee Mech. Eng. Elect. Eng. 40000 E3 A. Lee Mech. Eng. Syst. Anal. 34000 SAL E3 A. Lee Mech. Eng. Mech. Eng. 27000 E3 A. Lee Mech. Eng. Programmer 24000 TITLE SAL Elect. Eng. 40000 Syst. Anal. 34000 E8 J. Jones Syst. Anal. Elect. Eng. 40000 Mech. Eng. 27000 E8 J. Jones Syst. Anal. Syst. Anal. 34000 Programmer 24000 E8 J. Jones Syst. Anal. Mech. Eng. 27000 E8 J. Jones Programmer 24000 Syst. Anal. Cartesian Product Example Database System-dww

  26. The Join Operation • Used to combine related tuples from two relation into single tuples with some condition • Cartesian product all combination of tuples are included in the result, meanwhile in the join operation, only combination of tuples satisfying the join condition appear in the result Database System-dww

  27. General Form RS where R, S are Relations,  is a Boolean Expression, called a Join Condition. A Derivative of Cartesian Product R S =  (RS) R(A1, A2, ..., Am, B1, B2, ..., Bn) is the Resulting Schema of a -Join over R1 and R2: R1(A1, A2, ..., Am)  R2 (B1, B2, ..., Bn) Theta Join (-Join) Database System-dww

  28. A -Join Condition is a Boolean Expression of the form F1y1F2y2 ..., yn-1Fq (q>=1), where Fi (i=1,…,q) are Atomic Boolean Expressions of the form Ai  Bj, Ai, Bj are Attributes of R1 and R2 Respectively q is one of the Algorithmic Comparison Operators =, <>, >, <. >=, <= The Operator yi (i=1,…,n-1) is Either a Logical AND operator  or a logical OR operator  -Join Condition Database System-dww

  29. EMP EMP E.TITLE=SAL.TITLE SAL ENO ENAME TITLE SAL.TITLE TITLE SAL E1 J. Doe Elect. Eng E2 M. Smith Syst. Anal. Elect. Eng. Elect. Eng. 40000 E3 A. Lee Mech. Eng. Analyst Analyst 34000 E4 J. Miller Programmer Mech. Eng. Mech. Eng. 27000 E5 B. Casey Syst. Anal. 24000 Programmer Programmer E6 L. Chu Elect. Eng. Syst. Anal. Syst. Anal. 34000 E7 R. Davis Mech. Eng. Elect. Eng. Elect. Eng. 40000 E8 J. Jones Syst. Anal. Mech. Eng. Mech. Eng. 27000 Syst. Anal. Syst. Anal. 34000 SAL TITLE SAL Elect. Eng. 40000 Syst. Anal. 34000 Mech. Eng. 27000 Programmer 24000 -Join Example ENO ENAME E1 J. Doe E2 M. Smith E3 A. Lee E4 J. Miller E5 B. Casey E6 L. Chu E7 R. Davis E8 J. Jones Database System-dww

  30. Equi-join (EQUIJOIN) The  Expression only Contains one or more Equality Comparisons Involving Attributes from R1 and R2 Natural Join Denoted as R S Special Equi-join of Two Relations R and S Over a Set of Attributes Common to both R and S By Common, it means that each Join Attribute in A has not only Compatible Domains but also the SameName in both Relations R and S Other Types of Join Database System-dww

  31. R R.B=S.B S R S EQUIJOIN Natural Join A R.B C E A R.B S.B C E a1 a2 b1 b1 c3 c5 e1 e1 a1 a2 b1 b1 b1 b1 c3 c5 e1 e1 Examples R S A B C B E a1 a2 a3 b1 b1b4 c3 c5 c7 b1 b5 e1 e2 Database System-dww

  32. Natural Join Combines Relations on Attributes with the Same Names STUDENT(S#, SN, CITY, Email) SC(S#, C#, G) Example Query 1: “list of students with complete course grade info” STUDENT SC Natural Join Database System-dww

  33. All Natural Joins can be Expressed by a Combination of Primitive Operators Example Query 2: “print all students info (courses taken and grades)” Natural Join Database System-dww

  34. EMP ENO ENAME TITLE E1 J. Doe Elect. Eng E2 M. Smith Syst. Anal. E3 A. Lee Mech. Eng. E4 J. Miller Programmer E5 B. Casey Syst. Anal. E6 L. Chu Elect. Eng. E7 R. Davis Mech. Eng. E8 J. Jones Syst. Anal. SAL TITLE SAL Elect. Eng. 70000 Syst. Anal. 80000 Mech. Eng. 56000 Programmer 60000 Natural Join Example EMP SAL SAL ENO ENAME E.TITLE E1 J. Doe Elect. Eng. 70000 E2 M. Smith Syst. Anal. 80000 E3 A. Lee Mech. Eng. 56000 E4 J. Miller Programmer 60000 E5 B.Casey Syst.Anal 80000 E6 L. Chu Elect.Eng 70000 E7 R.Davis Mech.Eng 56000 E8 J. Jones Syst. Anal. 80000 Database System-dww

  35. Database System-dww

  36. Another Join Example Database System-dww

  37. Quotient (Division) • Given Relations • R(T,U) of degree r • S(U) of degree s • The Division of R by S, R ÷ S • Results is a Relation of Degree (rs) • Consists of all (rs)-tuples t such that for all s-tuples u in S, the tuple tu is in R. Database System-dww

  38. R ENO PNO PNAME BUDGET 150000 E1 P1 Instrumentation E2 P1 Instrumentation 150000 E2 P2 Database Develop. 135000 E3 P1 Instrumentation 150000 E3 P4 Maintenance E4 P2 Instrumentation E5 P2 Instrumentation E6 P4 Maintenance E7 P3 CAD/CAM 310000 E8 P3 CAD/CAM 150000 S 150000 310000 250000 250000 PNAME PNO BUDGET P1 Instrumentation 150000 P4 Maintenance 310000 Division Example Find the employees who work for both project P1 and project P4? R ÷ S ENO E3 Database System-dww

  39. Database System-dww

  40. Database System-dww

  41. “list the S# of students that have taken all of the courses listed in SC” S# (SCC# (SC)) “list the S# of students who have taken all of the courses taught by instructor Smith” S# (SCC# (Instructor=‘Smith’(SC))) Division: Another Example Database System-dww

  42. Relational Algebra • Selection • Projection • Union • Difference • Cartesian Product • Intersection • Join, Equi-join, Natural Join • Quotient (Division) Fundamental Operators Derivable from the fundamental operators Database System-dww

  43. A Set of Relational Algebra Operations Is Called a Complete Set, If and Only If Any Relational Algebra Operator in the Set Cannot be Derived in Terms of a Sequence of Others in Set Any Relational Algebra Operator Not in the Set Can Be Derived in Terms of a Sequence of Only the Operators in the Set All Relational Algebra Operations Database System-dww

  44. Important Concepts: The Set of Algebra Operations {, P , , –, } is a Complete Set of Relational Algebra Operations Any Query Language Equivalent to These Five Operations is Called Relationally Complete All Relational Algebra Operations Database System-dww

  45. Additional Relational Operations • Aggregate Functions and Grouping • A type of request that cannot be expressed in the basic relational algebra is to specify mathematical aggregate functions on collections of values from the database. • Examples of such functions include retrieving the average or total salary of all employees or the total number of employee tuples. These functions are used in simple statistical queries that summarize information from the database tuples. • Common functions applied to collections of numeric values include SUM, AVERAGE, MAXIMUM, and MINIMUM. The COUNT function is used for counting tuples or values. Database System-dww

  46. Additional Relational Operations (cont.) Database System-dww

  47. Additional Relational Operations (cont.) Use of the Functional operator ℱ ℱMAX Salary(Employee) retrieves the maximum salary value from the Employee relation ℱMIN Salary(Employee) retrieves the minimum Salary value from the Employee relation ℱSUM Salary(Employee)retrieves the sum of the Salary from the Employee relation DNO ℱCOUNT SSN, AVERAGE Salary(Employee)groups employees by DNO (department number) and computes the count of employees and average salary per department.[ Note: count just counts the number of rows, without removing duplicates] Database System-dww

  48. Database System-dww

  49. Database System-dww

  50. Additional Relational Operations (cont.) • Recursive Closure Operations • Another type of operation that, in general, cannot be specified in the basic original relational algebra is recursive closure. This operation is applied to a recursive relationship. • Example: retrieve all SUPERVISEES of an EMPLOYEE e at all levels—that is, all EMPLOYEE e’ directly supervised by e; all employees e’’ directly supervised by each employee e’; all employees e’’’ directly supervised by each employee e’’; and so on . • Although it is possible to retrieve employees at each level and then take their union, we cannot, in general, specify a query such as “retrieve the supervisees of ‘James Borg’ at all levels” without utilizing a looping mechanism. • The SQL3 standard includes syntax for recursive closure. Database System-dww

More Related