1 / 19

Relational Operators

Relational Operators. Lecture 7. Relational data model. relational data objects relational operators relational data integrity. Relational operators. preamble why do we need relational operators ? what kind of operations do we need to perform? how many operators would we like to have?

oistin
Download Presentation

Relational Operators

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 Operators Lecture 7

  2. Relational data model • relational data objects • relational operators • relational data integrity

  3. Relational operators • preamble • why do we need relational operators? • what kind of operations do we need to perform? • how many operators would we like to have? • what do you think a “powerful set of operators” means? • procedural / declarative • relational algebra / relational calculus

  4. Relational algebra operators • basic  primitive • 4 “set specific” operations • 4 “relation specific” operations • relational closure

  5. “Set specific” operators • specialised for relations • type compatibility • attribute name inheritance • candidate key inheritance • they are • union • intersection • difference • Cartesian product • exemplify them with a couple of relations of your choice

  6. “Relation specific” operators • restriction • <relation name> WHERE <condition> • projection • <relation name> [ <attr_1>, <attr_2>, …, <attr_n> ] • join • <relation name> JOIN <relation name> • difference • <relation name> DIVBY <relation name>

  7. Restriction • conditional expressions on atomic values • primitive data types • supported in SQL • non-conditional scalar operators cannot be used in conditional expressions; however, they can be introduced via EXTEND • domains • no support provided in SQL • PostgreSQL - extendable set of operators on atomic value • atomic / non-atomic conditions • nested conditions • logical expressions

  8. Join • natural join • theta-join • can be expressed via a Cartesian product and a restriction; how? • properties • commutative • associative

  9. Join

  10. Join

  11. Division

  12. Division

  13. Relational algebra operators • closure • nested expressions • primitive set • restriction, projection, Cartesian product, union, difference

  14. Examples

  15. Examples • get the name of the tutors who teach at least one module • get the name position and salary of all the tutors who teach at least one module • get the name of the tutors who do not teach any module • get the name and address of all the students who take level one courses • get the name of students who take all optional modules • ... • think of other queries and devise relational algebra expressions for them

  16. Extensions • EXTEND • you can think as allowing scalar computations in WHERE clauses • SUMMARISE • projections combined with the performance of summary calculations

  17. Implementation • DML in SQL • relational operators can be implemented via SELECT statements • SQL provides a richer set of operations than just the set of basic relational algebra operations • e.g. sub-queries

  18. The optimiser • optimiser • for a specific implementation, the module that ‘decides’ what is the best strategy of evaluating an expression (a query)(note that evaluation  record level operations) • example • get the addresses of all students who take the “AI” module • 3000 students; 4 modules per student, on average; some 100 students for the AI module; • ((Students JOIN Registration) WHERE Mname = “AI”) [Sname, Address] • (Students JOIN (Registration WHERE Mname = “AI”)) [Sname, Address]

  19. Summary • relational algebra • operators (set and relation specific) • relational closure • extensions • implementation - SQL • the optimiser • relational calculus

More Related