1 / 25

Geog 357 – Introduction to GIS

Geog 357 – Introduction to GIS. The Relational Language. 1. Relational data model. Operations on the relational data model: defined by relational algebra intersection union difference join projection selection.

morganmargaret
Download Presentation

Geog 357 – Introduction to GIS

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. Geog 357 – Introduction to GIS The Relational Language 1

  2. Relational data model • Operations on the relational data model: defined by relational algebra • intersection • union • difference • join • projection • selection Each operation takes one or more tables as input and returns one table as output

  3. Relational data model • Intersection: find records common to two tables given certain criteria (an and operation)

  4. Relational data model • Intersection: Find all classes that are Geography classes and that are also Gen Ed classes Geography Classes Gen Ed Classes Class Instructor Class Instructor Geog357 Jones Geog115 Brower Geog20 Fountain Geog435 Karnes Geog20 Fountain Geog115 Brower Meteo110 Stankle Meteo200 Turlock Result of Intersection Class Instructor Geog20 Fountain Geog115 Brower

  5. Relational data model • Union: find records common to either of two tables (an or operation)

  6. Relational data model • Union: Find all classes that are either Geography classes or that are Gen Ed classes Results of Union Geography Classes Gen Ed Classes Class Instructor Class Instructor Class Instructor Geog357 Jones Geog20 Fountain Geog115 Brower Geog435 Karnes Meteo110 Stankle Meteo200 Turlock Geog357 Jones Geog115 Brower Geog20 Fountain Geog435 Karnes Geog20 Fountain Geog115 Brower Meteo110 Stankle Meteo200 Turlock

  7. Relational data model • Difference: Find the records in one table that are not also present in another table (an xor operation)

  8. Relational data model • Difference: Find all classes that are Geography classes butthat are not Gen Ed classes Geography Classes Gen Ed Classes Class Instructor Class Instructor Results of Difference Geog357 Jones Geog115 Brower Geog20 Fountain Geog435 Karnes Geog20 Fountain Geog115 Brower Meteo110 Stankle Meteo200 Turlock Class Instructor Geog357 Jones Geog435 Karnes

  9. Relational data model • Join: Match records in both tables based on a common field Geography Classes Instructor Result of Join Class Instructor Instructor Office Class Instructor Office Geog357 Jones Geog115 Brower Geog20 Fountain Geog435 Karnes Jones 332 Brower 423 Fountain 125 Karnes 312 Geog357 Jones 332 Geog115 Brower 423 Geog20 Fountain 125 Geog435 Karnes 312

  10. Relational data model • Projection: reduces one table in the attribute dimension (a selection of a subset of fields, for all records)

  11. Relational data model • Projection: List all Geography classes, but not the instructors Geography Classes Result of Projection Class Instructor Class Geog357 Jones Geog115 Brower Geog20 Fountain Geog435 Karnes Geog357 Geog115 Geog20 Geog435

  12. Relational data model • Selection (restriction): reduces one table in the record dimension (a selection of a subset of records, for all fields) • Criteria for selection is called a predicate

  13. Relational data model • Selection: Find Geography classes taught by ‘Jones’ Geography Classes Class Instructor Geog357 Jones Geog115 Brower Geog20 Fountain Geog435 Karnes Result of Selection Class Instructor Geog357 Jones

  14. Basic SQL Query SELECT [DISTINCT] target-list FROMrelation-list WHERE qualification • relation-list A list of relation names (possibly with a range-variable after each name). • target-list A list of attributes of relations in relation-list • qualification Comparisons (Attr op const or Attr1 op Attr2, where op is one of ) combined using AND, OR and NOT. • DISTINCT is an optional keyword indicating that the answer should not contain duplicates. Default is that duplicates are not eliminated!

  15. Selection • Selects a subset of rows that satisfy a selection condition. • Example: List all staff with a salary greater than $10,000 • In SQL: SELECT * FROM Staff WHERE Salary > 10000

  16. Projection • Deletes attributes that are not in projection list. • Schema of result contains exactly the fields in the projection list, with the same names that they had in the (only) input relation. Example: Produce a list of salaries for all staff, showing only the Sno, FName, LName, and Salary details. • SQL SELECT sno, fname, Lname, Salary FROM Staff

  17. Projection (cont…) • In relational algebra, projection operator has to eliminate duplicates! • However,real SQL implementations typically don’t do duplicate elimination unless the user explicitly asks for it SELECT DISTINCT sno, fname, Lname, Salary FROM Staff

  18. Cartesian Product R1 X R2 • Allows us “to glue together” allpossible combinations • Each row of R1 is paired with each row of R2. • Result schema has one field per field of R1 and R2, with field names inherited if possible. • Conflict: Both R1 and R2 could have a field called the same name. In SQL: SELECT FROM R1, R2

  19. Example of Cartesian Product R2 R1 R2. R1. R1 X R2

  20. Union (R È S) • R and S must be union-compatible They must have the same set of attributes with matching domains • Union of two relations R and S with m and n tuples, respectively, is obtained by concatenating them into one relation with a maximum of (m +n) tuples, duplicate tuples being eliminated. Example: Construct a list of all areas where there is either a branch or a property. In SQL SELECT area FROM Branch UNION SELECT area FROM Property_for_Rent

  21. Set Difference (R – S) • Defines a relation consisting of the rows that are in relation R, but not in S. • R and S must be union-compatible. Example: Construct a list of all cities where there is a branch office but no properties. In SQL: SELECT city FROM Brach EXCEPT SELECT city FROM Property_for_Rent

  22. Join Operations • A combination of Selection and Cartesian product operations SELECT FROM R, S WHERE {condition} • There are various forms of join operation • Equi-join: {condition} contains only equality • Natural join: Equi-join on all common fields • Outer join: Retains rows that do not satisfy condition

  23. Reln 1 Reln 1 Reln 2 Reln 2 Equi-join Example: List the names and comments of all renters who have viewed a property. SELECT Renter.rno, fname, lname, Viewing.rno, pno, comment FROM Renter, Viewing WHERE Renter.rno = Viewing.rno

  24. Natural Join • Equi-join over all common attributes x. • One occurrence of the common attribute is eliminated from the result. Example: List the names and comments of all renters who have viewed a property. SELECT rno, fname, lname, pno, comment FROM Renter, Viewing WHERE Renter.rno = Viewing.rno 46

  25. Left Outer Join • Result will include all tuples in the left relation even no match on join attribute Example: Produce a status report on property viewings. • In SQL: • SELECT pno, street, city, rno, date, comment • FROM Property_for_Rent NATURAL LEFT OUTER JOIN Viewing

More Related