1 / 31

Relational algebra

Learn the fundamentals of relational algebra in SQL, including unary and binary operations, joins, projection, selection, set operators, and more. Enhance your SQL skills with clear examples and explanations.

blaine
Télécharger la présentation

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. Relational algebra SHIRAJ MOHAMED M | MIS

  2. Relational algebra Notation SHIRAJ MOHAMED M | MIS

  3. Unary Operations Projection stud#, nameStudents In SQL: Select stud#, name From Students; Selection course = ‘Computing’Students In SQL: Select * From Students Where course = ‘Computing’; SHIRAJ MOHAMED M | MIS Selection & Projection stud#, name(course = ‘Computing’Students) In SQL: Select stud#, name From students Where course = ‘Computing’;

  4. Binary Operations/Joins Cartesian Product: Students X Courses In SQL: Select * From Students, Courses; SHIRAJ MOHAMED M | MIS

  5. Rename • RENAME operator (): Renames the input relation and attributes with a new relation name & attributes specified. S(B1, B2, …, BN) (R) Example, TEMP  NAME, MAJOR(STUDENT) STUD_INFO (FULL_NAME,M_DEPT ) TEMP SHIRAJ MOHAMED M | MIS

  6. Renaming Example SHIRAJ MOHAMED M | MIS • TEMP  DNO=5(EMPLOYEE) • R(FIRSTNAME, LASTNAME, SALARY)  • FNAME, LNAME, SALARY (TEMP)

  7. Union and Set-Difference • All of these operations take two input relations, which must be union-compatible: • Same number of fields. • Corresponding’ fields have the same type. SHIRAJ MOHAMED M | MIS

  8. Set Operators • Given two relations R1, R2 that are union-compatible, we have that • R1  R2 returns the set of tuples that are in R1 or R2. [UNION] • R1  R2 returns the set of tuples that are both in R1 and R2. [INTERSECTION] • R1 - R2 returns the set of tuples that are in R1, but not in R2. [SET DIFFERENCE] SHIRAJ MOHAMED M | MIS

  9. Set Operators •  Name (FACULY)   Name (STUDENT) •  Address (FACULY)   Address (STUDENT) •  CrsCode (CLASS) -  CrsCode (TRANSCRIPT) SHIRAJ MOHAMED M | MIS

  10. Union S1 S2 SHIRAJ MOHAMED M | MIS

  11. Set Difference S1 S2 – S1 S2 SHIRAJ MOHAMED M | MIS

  12. Joins Three new join operators are introduced: • Left Outer Join (denoted as ) • Right Outer Join (denoted as ) • Full Outer Join (denoted as ) SHIRAJ MOHAMED M | MIS

  13. Join… Students ⋈ <stud# =200> Courses In SQL: Select * From Students, Courses Where stud# = 200; SHIRAJ MOHAMED M | MIS

  14. Left Outer Join Left Outer Join : A <join condition> B • ensures that all tuples in the in the relation A are present in the result set. • The tuples in A without matching tuples in B are filled with null values for B’s attributes SHIRAJ MOHAMED M | MIS

  15. Left Outer Join - Example StudentsCourses stud# namecoursecourse# name 100Fred PH PH Pharmacy 200 Dave CM CM Computing 400 Peter EN CH Chemistry Students<course = course#>Courses stud# Students.name course course# Courses.name 100 Fred PH PH Pharmacy 200 Dave CM CM Computing • Peter EN NULL NULL SHIRAJ MOHAMED M | MIS

  16. Right Outer Join Right Outer Join: A <join condition> B • Reverse of left outer join. Retrieves all tuples of B and null values for attributes of A in non-matching tuples of B SHIRAJ MOHAMED M | MIS

  17. Right Outer Join - Example StudentsCourses stud# namecoursecourse# name 100Fred PH PH Pharmacy 200 Dave CM CM Computing 400 Peter EN CH Chemistry Students<course = course#>Courses stud# Students.name course course# Courses.name 100 Fred PH PH Pharmacy 200 Dave CM CM Computing NULL NULL NULL CH Chemistry SHIRAJ MOHAMED M | MIS

  18. Combination of Unary and Join Operations StudentsCourses stud# name address course course# name 100Fred Aberdeen PH PH Pharmacy 200 Dave Dundee CM CM Computing 300 Bob Aberdeen CM Show the names of students (from Aberdeen) and the names of their courses R1= Students ⋈<course=course#> Courses R2= <address=“Aberdeen”> R1 R3= <Students.name, Course.name> R2 SHIRAJ MOHAMED M | MIS Students.name Courses.name Fred Pharmacy Bob Computing

  19. Full Outer Join Full Outer Join: A <join condition> B • ensures that all tuples of A and B are present in the result set SHIRAJ MOHAMED M | MIS

  20. Exercise 1 Example: Customer • Query 1: List customers whose cred_lim is greater than £500. • Query 2: List customers whose cred_lim is greater than £500 and lives in London. SHIRAJ MOHAMED M | MIS

  21. Answers • Query 1: List customers whose cred_lim is greater than £500. (cred_lim > 500)(customer) • Query 2: List customers whose cred_lim is greater than £500 and lives in London. (cred_lim>500) AND (city=London)(customer) SHIRAJ MOHAMED M | MIS

  22. Exercise 2 Reserves Sailors Boats 1.Find names of sailors who’ve reserved boat #103 2.Find names of sailors who’ve reserved a red boat 3.Find sailors who’ve reserved a red or a green boat 4.Find sailors who’ve reserved a red and a green boat 5. Find the names of sailors who’ve reserved all boats SHIRAJ MOHAMED M | MIS

  23. Solution 2: 1.Find names of sailors who’ve reserved boat #103 • Solution 1: SHIRAJ MOHAMED M | MIS

  24. A more efficient (???) solution: 2.Find names of sailors who’ve reserved a red boat • Information about boat color only available in Boats; so need an extra join: SHIRAJ MOHAMED M | MIS

  25. 3.Find sailors who’ve reserved a red or a green boat • Can identify all red or green boats, then find sailors who’ve reserved one of these boats: SHIRAJ MOHAMED M | MIS

  26. 4.Find sailors who’ve reserved a red and a green boat • Previous approach won’t work! Must identify sailors who’ve reserved red boats, sailors who’ve reserved green boats, then find the intersection (note that sid is a key for Sailors): SHIRAJ MOHAMED M | MIS

  27. 5. Find the names of sailors who’ve reserved all boats • Uses division; schemas of the input relations to / must be carefully chosen: SHIRAJ MOHAMED M | MIS • To find sailors who’ve reserved all ‘Interlake’ boats: .....

  28. Aggregate Functions and Operations • Aggregation function takes a collection of values and returns a single value as a result. avg: average valuemin: minimum valuemax: maximum valuesum: sum of valuescount: number of values • Aggregate operation in relational algebra G1, G2, …, GngF1( A1), F2( A2),…, Fn( An) (E) • E is any relational-algebra expression • G1, G2 …, Gn is a list of attributes on which to group (can be empty) • Each Fiis an aggregate function • Each Aiis an attribute name

  29. Aggregate Operation – Example • Relation account grouped by branch-name: branch-name account-number balance Perryridge Perryridge Brighton Brighton Redwood A-102 A-201 A-217 A-215 A-222 400 900 750 750 700 branch-nameg sum(balance) (account) branch-name balance Perryridge Brighton Redwood 1300 1500 700

  30. Aggregate Functions • Result of aggregation does not have a name • Can use rename operation to give it a name • For convenience, we permit renaming as part of aggregate operation branch-nameg sum(balance) as sum-balance (account)

  31. End… SHIRAJ MOHAMED M | MIS

More Related