S1: Csci 8701 - Week 3
Copyright: S. Shekhar, C. S. Dept., University of Minnesota, Minneapolis, MN 55455. ,,.(home)
S2: Chapter Outline
  • 3.1 Standard Query Languages
  • 3.2-3 Relational Algebra, SQL
  • 3.4 Extending SQL for spatial data
  • 3.6 Trends: Object Relational SQL


Copyright: S. Shekhar, C. S. Dept., University of Minnesota, Minneapolis, MN 55455. ,,.(home)
S3: 3.1 Standard Query Languages
  • Example Database - Relational Schema (pp. 77)
    • Country(name, cont, pop, shape)
    • City(name, country, pop, capital?, shape)
    • River(name, origin, length, shape)
  • Entity Relationship Diagram (Fig. 3.1, pp. 78)
  • Table instance (Table. 3.1, pp. 87)
  • Q? Critique relational schema w.r.t ERD
    • capital-of relationship


Copyright: S. Shekhar, C. S. Dept., University of Minnesota, Minneapolis, MN 55455. ,,.(home)
S4: 3.2-3 Relational Algebra, SQL
  • Relational Algebra
    • Operand type - relations
    • Operations: select, project, join
    • ... conditional-join vs. natural-join
    • ... set union, intersection, cross-product
    • Examples (Table 3.7, pp. 90)
  • SQL Summary
    • Data Definition, e.g. create table
    • Data Control, e.g. grant
    • Data Manipulation, e.g. select, insert
  • Example Queries (pp. 83-85)
    • Output of queries (Table 3.8, pp. 91)
    • Q? Which example queries are spatial?


Copyright: S. Shekhar, C. S. Dept., University of Minnesota, Minneapolis, MN 55455. ,,.(home)
S5: 3.4 Extending SQL for spatial data
  • Motivation
    • Column types - numeric, string
    • Need spatial types and operations
  • Open Geodata Interchange Standard (OGIS)
    • Data types - Figure 3.2 (pp. 93)
    • Q? Is it a field model? object model?
    • Operators - Table 3.9 (pp. 94)
  • Example schema (pp. 95)
    • Note spatial data types
  • Example queries (pp. 95-99)
    • Note use of spatial operators


Copyright: S. Shekhar, C. S. Dept., University of Minnesota, Minneapolis, MN 55455. ,,.(home)
S6: 3.4 Extending SQL for spatial data
  • Identify spatial operators in following:
    • list neighboring countries of USA
    • list countries, river Nile passes through
    • list cities on the banks of Nile
  • Compare and contrast OGIS concepts of
    • Within vs. Contain
    • Intersect vs. Overlap
    • Intersect vs. Intersection
    • Difference vs. SymmDiff
  • Give examples of spatial queries which
    • can not be answered by OGIS operators?
    • can be answered by OGIS operators but not with
    • ... point model of cities in Table 3.1 (pp. 87)


Copyright: S. Shekhar, C. S. Dept., University of Minnesota, Minneapolis, MN 55455. ,,.(home)
S7: 3.6 Trends: Object Relational SQL
  • Object Oriented or Object Relational Models
    • Allows user defined data types within SQL
    • UDTs can include operations
    • Operator Syntax object.operation(operand)
  • Object Relational implementation
    • Create type statement (pp. 100)
    • Example - Point, LineType (pp. 101)
  • Object Relational Syntax
    • Example queries on pp. 102-3


Copyright: S. Shekhar, C. S. Dept., University of Minnesota, Minneapolis, MN 55455. ,,.(home)
S8: 3.6 Trends: Object Relational SQL
  • Relational implementation
    • Spatial operators are stored procedures (sec. 3.5)
  • Extreme relational implementations
    • Spatial data types are Foreign keys
    • ... to spatial tables (Fig. 2.5, pp. 64)
    • More Extreme: Normalize spatial tables
    • ... see Fig. 1.3, 1.4 (pp. 27-28)
  • Try writing following queries in SQL
    • ... on normalized spatial tables
    • Find all polygons with point 1 as a corner
    • Find all polygons containing point 1


Copyright: S. Shekhar, C. S. Dept., University of Minnesota, Minneapolis, MN 55455. ,,.(home)