1 / 34

Relational Algebra

After this lecture, you should be able to: Understand the differences between SQL (Structured Query Language) and Relational Algebra expressions . Build queries in Relational Algebra. Understand how DBMS’s process the SQL queries. Make simple query optimization .

berget
Download Presentation

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. After this lecture, you should be able to: Understand the differences between SQL (Structured Query Language) and Relational Algebra expressions. Build queries in Relational Algebra. Understand how DBMS’s process the SQL queries. Make simple query optimization. Be ready for Quiz 4 and Complete Assignment 7 (Part I). Relational Algebra

  2. Relational Algebra • Relational algebra deals with a set of relationsclosed under operators. Operators operate on one or more relations to yield a relation. • Algebraic Expressions (a + b) * c (A  B)  C • Relational algebra is a basis for database query languages.

  3. Characteristics of Relational Algebra • Operational • The order of operations can be specified. • Set-at-a-time • Multiple rows are processed by one operation. • Not too detailed • How to access data is not specified. • Good for query optimization. • Who supplied P2? • SQL Select SName from S, SP where S.S# = SP.S# and P# = 'P2' ; • Relational Algebra SName (S ⋈ (p#=‘P2‘ SP ))

  4. Relational algebra … • Union: A  B • Intersection: A  B • Difference: A ̶ B • Cartesian Product: S × SP • Division: A / B • Selection: city=‘Rome‘ S • Projection: SName,StatusS • Join: S ⋈ SP • Renamingρ(citypcity) P

  5. Terms: Relational algebra ~ Database Relation ~ Table Attribute ~ Column Tuple ~ Row (Record) Relation schema ~ Structure of table Relation Instance ~ Data of table

  6. Union and Intersection P Q P  Q P  Q

  7. Union and Intersection: Exercise P Q P  Q = ? P  Q = ?

  8. Difference (-) Q P P ̶ Q

  9. Difference: Exercise Q P P ̶ Q = ?

  10. Cartesian Product (×) Q P P × Q = {(p, q)|p  P and q  Q}

  11. Cartesian Product: Exercise Q P P × Q = ?

  12. Selection () R B = 2 R

  13. Get all the information of the suppliers located in Paris. Table S sno | sname | status | city ----------------------------- s1 | Smith | 20 | London s2 | Jones | 10 | Paris s3 | Blake | 30 | Paris s4 | Clark | 20 | London s5 | Adams | 30 | Athens select * from s where city = ‘Paris’; city = ‘Paris‘ S

  14. Projection () R  C, AR

  15. What are the S#’s and statuses of the suppliers? Table S sno | sname | status | city ----------------------------- s1 | Smith | 20 | London s2 | Jones | 10 | Paris s3 | Blake | 30 | Paris sno | status ------------ s1 | 20 s2 | 10 s3 | 30 select sno, status from s; sno, statusS

  16. What are the S#’s and statuses of the suppliers located in Paris? Table S sno | sname | status | city ----------------------------- s1 | Smith | 20 | London s2 | Jones | 10 | Paris s3 | Blake | 30 | Paris s4 | Clark | 20 | London s5 | Adams | 30 | Athens sno | status ------------ s2 | 10 s3 | 30 select sno, status from s where city = 'Paris; sno, status(city = ‘Paris‘ S)

  17. Selection and Projection: Exercise Get the names and weights of the green parts. pno pname color weight city ---- ------ ------- ------ ------ p1 nut red 12 London p2 bolt green 15 Paris p3 screw green 17 Rome pname weight ----- ------ bolt 15 screw 17 select pname, weight from p where color = ‘green’; pname, weight(color = ‘green‘ P)

  18.  Join P Q B1 ≥ B2(P × Q)

  19. Equijoin Q P B1 = B2(P × Q)

  20. Natural Join (⋈ ) P Q P ⋈ Q

  21. Natural Join: Example S SP S ⋈ SP = ?

  22. Selection, Join, and Projection: Example What are the part numbers of the parts supplied by the suppliers located in Taipei? SQL: select SP.pno from S, SP where S.city = ‘Taipei’ and S.sno = SP.sno; Algebra: pno((S.City=‘Taipei’S)⋈ SP)

  23. Selection, Join, and Projection: Exercise What are the names of the suppliers who supplied green parts? SQL: select sname from P, SP, S where P.color = ‘green’ and P.pno = SP.pno and SP.sno = S.sno; Algebra: sname((pno(color=‘green‘P))⋈ SP ⋈ S)

  24. Subtraction Examples Get the supplier numbers of the suppliers who did not supply red parts. • The supplier numbers of all the suppliers: AS = s# (S) • The supplier numbers of the suppliers who supplied some red parts: RPS = s# (SP ⋈ (Color = ‘Red‘ (P)) • The answer is: AS - RPS

  25. Subtraction Examples (cont’d) Get the names of the suppliers who supplied only red parts. • The supplier numbers of the suppliers who supplied some parts: SS = s# (SP) • The supplier numbers of the suppliers who supplied at least one non-red parts: NRS = s# (SP ⋈ (color <> ‘Red‘ (P)) • The answer is: SName ((SS – NRS) ⋈ S)

  26. Rename (ρ) Ρ(B->B2, 3C2)

  27. Join with Renaming: Example I Get the pairs of the supplier numbers of the suppliers who supply at least one identical part. SQL: select SPX.sno, SPY.sno from SP SPX, SP SPY where SPX.pno = SPY.pno and SPX.sno < SPY.sno Algebra: snox,snoy(snox < snoy ((ρ(snosnox)sno,pnoSP)⋈ (ρ(snosnoy)sno,pnoSP) ) )

  28. Join with Renaming: Example II What are the colors of the parts supplied by the suppliers located in Taipei? Table S Table P sno | sname | sts | city pno | pname | color | wgt | city -------------------------- ---------------------------------- s1 | Smith | 20 | London p1 | nut | red | 12 | London s2 | Jones | 10 | Taipei p2 | bolt | green | 17 | Taipei p3 | screw | blue | 17 | Rome Table SP sno | pno | qty --------------- s1 | p1 | 300 s2 | p3 | 200

  29. Join with Renaming: Example II What are the colors of the parts supplied by the suppliers located in Taipei? SQL: select color From S, SP, P where S.city = ‘Taipei’ and S.sno = SP.sno and SP.pno = P.pno; Algebra: color(S.City=‘Taipei‘(S ⋈ SP ⋈ P)) Joined also by S.city = P.city !

  30. Join with Renaming: Example II (cont’d) Not Correct: color(S.City=‘Taipei‘(S ⋈ SP ⋈ P)) Renamed: color(City=‘Taipei‘ (S ⋈ SP ⋈ρ(citypcity)P)) Optimized: color((pno((sno(City=‘Taipei‘S)) ⋈ SP))⋈ P)

  31. Join Example: Query Plan color ⋈ pno ⋈ sno city=‘Taipei‘S P S SP

  32. SQL Query Processing • An SQL query is transformed into an algebraic form for query optimization. • Query optimization is the major task of an SQL query proccessor. • select A1, A2, … , Am from T1, T2, … , Tn where C is converted to A1, A2, …, AmC (T1 x T2 x … x Tn) and then optimized.

  33. Query Optimization Guidelines • JOIN operations are associative and commutative • (R ⋈ S) ⋈ T = R ⋈ ( S ⋈ T) • R ⋈ S = S ⋈ R • JOIN and SELECT operations are associative and commutative if the SELECT operations are still applicable. • PROJECTIONs can be performed to remove column values not used. • These properties can be used for query optimization. • The order of operations can be changed to produce smaller intermediate results.

  34. Optimization: Example What are the part numbers of the parts supplied by the suppliers located in Taipei? Not Optimized: pno (S.City=‘Taipei‘(S ⋈ SP)) Improved: pno ((S.City=‘Taipei‘S)⋈ SP) Optimized: pno((sno(S.City=‘Taipei‘S))⋈(sno,pnoSP))

More Related