Geog 357 Introduction to GIS The Relational Language

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 Each operation takes one or more tables as input and returns one table as output 2

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

Relational data model Intersection: Find all classes that are Geography classes and that are also Gen Ed classes Geography Classes Class Instructor Gen Ed Classes Class Instructor Geog 357 Jones Geog 20 Fountain Geog 115 Brower Result of Intersection Class Instructor Geog 20 Fountain Meteo 110 Stankle Geog 20 Fountain Geog 435 Karnes Meteo 200 Turlock Geog 115 Brower 4

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

Relational data model Union: Find all classes that are either Geography classes or that are Gen Ed classes Geography Classes Class Instructor Gen Ed Classes Class Instructor Geog 357 Jones Geog 20 Fountain Geog 115 Brower Geog 20 Fountain Meteo 110 Stankle Geog 435 Karnes Meteo 200 Turlock Results of Union Class Instructor Geog 357 Jones Geog 20 Fountain Geog 115 Brower Geog 435 Karnes Meteo 110 Stankle Meteo 200 Turlock 6

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

Relational data model Difference: Find all classes that are Geography classes but that are not Gen Ed classes Geography Classes Class Instructor Gen Ed Classes Class Instructor Geog 357 Jones Geog 20 Fountain Results of Difference Class Instructor Geog 115 Brower Geog 357 Jones Geog 20 Fountain Meteo 110 Stankle Geog 435 Karnes Meteo 200 Turlock 8

Relational data model Join: Match records in both tables based on a common field Geography Classes Class Instructor Office Result of Join Class Instructor Office Geog 357 Jones 332 Geog 115 Brower 423 Geog 20 Fountain 125 Geog 435 Karnes 312 9

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

Relational data model Projection: List all Geography classes, but not the instructors Geography Classes Class Instructor Result of Projection Class Geog 357 Jones Geog 357 Geog 115 Brower Geog 115 Geog 20 Fountain Geog 20 Geog 435 Karnes Geog 435 11

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 12

Relational data model Selection: Find Geography classes taught by ‘Jones’ Geography Classes Class Instructor Geog 357 Jones Geog 115 Brower Geog 20 Fountain Geog 435 Karnes Result of Selection Class Instructor Geog 357 Jones 13

Basic SQL Query SELECT FROM WHERE [DISTINCT] target-list relation-list 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 Attr 1 op Attr 2, 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! 14

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 15

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 16

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 17

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

Example of Cartesian Product R 2 R 1. R 2. R 1 X R 2 19

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 20

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 21

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 22

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

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 24

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 25