1 / 27

Relational Algebra Ch. 7.4 – 7.6

Relational Algebra Ch. 7.4 – 7.6. John Ortiz. Relational Query Languages. Query languages: allow manipulation and retrieval of data from a database. Relational QLs are simple & powerful. Strong formal foundation based on logic. Allows for much optimization.

materia-perrin
Download Presentation

Relational Algebra Ch. 7.4 – 7.6

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. Relational AlgebraCh. 7.4 – 7.6 John Ortiz

  2. Relational Query Languages • Query languages: allow manipulation and retrieval of data from a database. • Relational QLs are simple & powerful. • Strong formal foundation based on logic. • Allows for much optimization. • Query languages != programming languages! • Not intended for complex calculations. • Support easy, efficient access to large data sets. Relational Algebra

  3. Preliminaries • A query is applied to relation instances, and the result of a query is also a relation instance. • Schemas of input & result relations are fixed (determined by relations & query language constructs). • A query is specified against schemas (regardless of instances). • Attributes may be referenced either by names or by positions (two notation systems). Relational Algebra

  4. Relational Algebra • Basic Operations: • Selection (): choose a subset of rows. • Projection (): choose a subset of columns. • Cross Product (): Combine two tables. • Union (): unique tuples from either table. • Set difference (): tuples in R1 not in R2. • Renaming (): change names of tables & columns • Additional Operations (for convenience): • Intersection, joins (very useful), division, outer joins, aggregate functions, etc. Relational Algebra

  5. Result Students Selection • Format: selection-condition(R). Choose tuples that satisfy the selection condition. • Result has identical schema as the input. Major = ‘CS’(Students) • Selection condition is a Boolean expression including =, , <, , >, , and, or, not. Relational Algebra

  6. Students Result Projection • Format: attribute-list(R). Retain only those columns in the attribute-list. • Result must eliminate duplicates. Major(Students) • Operations can be composed. Name, GPA(Major = ‘CS’(Students)) Relational Algebra

  7. Cross Product • Format: R1  R2. Each row of R1 is paired with each row of R2. • Result schema consists of all attributes of R1 followed by all attributes of R2. • Problem: Columns may have identical names. Use notation R.A, or renaming attributes. • Only some rows make sense. Often need a selection to follow. Relational Algebra

  8. Students  Awards Students Awards Example of Cross Product Relational Algebra

  9. Students CS_Students Renaming • Format: S(R) or S(A1, A2, …)(R): change the name of relation R, and names of attributes of R CS_Students(Major = ‘CS’(Students)) Relational Algebra

  10. Union, Intersection, Set Difference • Format: R1 R2 (R1  R2, R1  R2). Return all tuples that belong to either R1 or R2 (to both R1 and R2; to R1 but not to R2). • Requirement: R1 and R2 are union compatible. • With same number of attributes. • Corresponding attributes have same domains. • Schema of result is identical to that of R1. May need renaming. • Duplicates are eliminated. Relational Algebra

  11. TAs  RAs TAs  RAs TAs RAs TAs  RAs Examples of Set Operations Relational Algebra

  12. Joins • Theta Join. • Format: R1 join-condition R2. • Returns tuples in join-condition(R1 R2) • Equijoin. • Same as Theta Join except the join-condition contains only equalities. • Natural Join. • Same as Equijoin except that equality conditions are on common attributes and duplicate columns are eliminated. Relational Algebra

  13. Students Profs Result Examples of Joins • Theta Join. Students Students.Age<=Profs.Age Profs Relational Algebra

  14. Result Result Examples of Joins (cont.) • Equijoin. Students Prof=PID AND Name=Pname Profs • Natural Join. Students Profs Relational Algebra

  15. Some Questions About Joins * • What is the result of R1 R2 if they do not have a common attribute? • What is the result of R R? • Consider relations Students(SSN, Name, GPA, Major, Age, PSSN) Profs(PSSN, Name, Office, Age, Dept) • Which type of join should be used to find pairs of names of students and their advisors? • Can a natural join be used? How? Relational Algebra

  16. Division • Format: R1  R2. Restriction: Every attribute in R2 is in R1. • For R1(A1, ..., An, B1, ..., Bm) R2(B1, ..., Bm) and T = A1, ..., An (R1), Return the subset of T, say W, such that every tuple in W  R2 is in R1. • W is the largest subset of T, such that, (W  R2)  R1 Relational Algebra

  17. Takes CS_Req Result An Example of Division Takes  CS_Req • What is the meaning of this expression? Relational Algebra

  18. Students Result Grouping & Aggregate Functions • Format: group_attributes F aggregate_functions ( r ) • Partition a relation into groups • Apply aggregate function to each group • Output grouping and aggregation values, one tuple per group • Ex: Major F count(SID), avg(GPA) (Students) Relational Algebra

  19. Dangling Tuples in Join • Usually, only a subset of tuples of each relation will actually participate in a join. • Tuples of a relation not participating in a join are dangling tuples. • How do we keep dangling tuples in the result of a join? (Why do we want to do that?) • Use null values to indicate a “no-join” situation. Relational Algebra

  20. Outer Joins • Left Outer Join. • Format: R1 R2. Similar to a natural join but keep all dangling tuples of R1. • Right Outer Join. • Format: R1 R2. Similar to a natural join but keep all dangling tuples of R2. • (Full) Outer Join. • Format: R1 R2. Similar to a natural join but keep all dangling tuples of both R1 & R2. • Can also have Theta Outer Joins. Relational Algebra

  21. Students Awards Result Examples of Outer Joins • Left Outer Join. Students Awards Relational Algebra

  22. R S Relational Algebra Exercises • Find the result of these expressions. • R S • R R.C=S.C S • B,E((B,C R) (E<7 S)) • (A,BR) - S(A,B) (D,C S) Relational Algebra

  23. Queries In Relational Algebra Consider the following database schema: Students(SSN, Name, GPA, Age, MajorDept) Enrollment(SSN, CourseNo, Grade) Courses(CourseNo, Title, DName) Departments(DName, Location, Phone) • Two methods: • Use temporary relations. • One expression per query. Relational Algebra

  24. Queries In Relational Algebra • List student name and course title such that the student has an A in the course and the course is not offered by the student’s major department. • Find those students who got an A in any course. • Find the department of the students and the courses. • Find the final answer. Relational Algebra

  25. Summary • Relational model provides simple yet powerful formal query languages. • Relational algebra is procedural and used for internal representation of queries. • Several ways to express a given query. DBMS should choose the most efficient plan. • Any language able to express all relational algebra queries is relational complete. Relational Algebra

  26. Summary (cont.) Lots useful properties. • C1(C2(R)) = C2(C1(R)) = C1 and C2(R) • L1(L2(R)) = L1(R) , if L1  L2 • R1 R2 = R2  R1 • R1  (R2  R3) = (R1 R2)  R3 • R1 R2 = R2 R1 • R1 (R2 R3) = (R1 R2) R3 Relational Algebra

  27. Look Ahead • Next topic: Translation form ER/EER to relational model • Read from the textbook: • Chapter 14.1 – 14.2 Relational Algebra

More Related