S1: Csci 8701 - Week 3
- Administrative
- HW 2 Due
- HW 3: Pick up peer reviews
- HW 3: Any Questions
- Goals:
- Recommended Readings:
- Chapter 3 , Supplementary Book
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
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)
- 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)