SQL - Object-Relational Extensions

• Postgresql and many other databases actually have many extensions that go well beyond the relational data model.

• As these extensions violate relational data model, think about what you are giving up and use them sparingly!

  • Simplicity of data model and queries

  • Optimizations may not be as easy to perform

• We will go through some of these here, using Postgresql as an example.

Semantic Hierarchies - Inheritance

• Recall in E-R diagrams, we talked about ISA relationships.

  • A isa B, meaning A inherits all the attributes of B (and adds some more)

• Postgresql allows you to define class hierarchies:

  See example database to be used.

  CREATE TABLE cities (
       name            text
       , population      float
       , altitude        int     -- in feet
  );
  
  CREATE TABLE capitals (
       state           char(2)
  ) INHERITS (cities);
  

• Querying subtables:

  SELECT
     name
     , altitude
  FROM
     cities
  WHERE
     altitude > 50;
  

  Includes all cities, i.e. capitals as well.

  SELECT
     name
     , altitude
  FROM ONLY
     cities
  WHERE
     altitude > 50;
  

  Includes only cities, not capitals.

• To find out which table a row comes from:

  SELECT
      p.relname
      , c.name
      , c.altitude
  FROM
      cities c
      , pg_class p
  WHERE
      c.altitude > 50
      and c.tableoid = p.oid;
  
  Output:
  relname   |   name    | altitude
  ----------+-----------+----------
  cities    | Las Vegas |     2174
  cities    | Mariposa  |     1953
  capitals  | Madison   |      84
  

• Semantic hierarchies about sets of objects and their relationship to each other.

  • A type of object (capital) is a special type of city.

  • All cities include the capitals.

Complex objects

• You can create user defined types

  create type phone_type as (
       num varchar(12)
       , type varchar(50)
  );
  
  create table person (
       id int
       , name varchar(30)
       , phone phone_type
  ) ;
  
  insert into person values(
       1
       , 'Kara Danvers'
       , ('555-1234','work')::phone_type
  ) ;
  
  select * from person ;
  
   id |     name     |      phone
  ----+--------------+-----------------
   1 | Kara Danvers | (555-1234,work)
  

• These complex types really go against the first normal form: that all values should be atomic. But, they allow multiple related values to be encapsulated.

• You can access the types using dot notation

  select * from person where (phone).type = 'work';
  

• Technically you should store the both attributes for phone separately, but this way, you can tell that they belong together.

• You can also define user defined types to be restricted domains of values and then use in multiple places.

Collection of Values

• In addition to records (like the one above), you can also define collection of values.

• Arrays:

  CREATE TABLE tictactoe (
      squares   integer[3][3]
  );
  
  INSERT INTO tictactoe VALUES('{{1,2,3},{4,5,6},{7,8,9}}');
  
  SELECT squares[3][2] FROM tictactoe; --not zero indexed
  
  squares
  ---------
  8
  (1 row)
  
  
  CREATE TABLE messages (
       msg  text[]
  ) ;
  
  INSERT INTO messages VALUES ('{"hello", "world"}') ;
  INSERT INTO messages VALUES ('{"I", "feel", "so", "free"}') ;
  
  SELECT msg[2] FROM messages ;
  msg
  -------
  world
  feel
  (2 rows)
  
  SELECT msg[2:3] FROM messages; --slicing, really?
  
  msg
  -----------
  {world}
  {feel,so}
  (2 rows)
  

• The best of use complex types is to write procedures/functions using pl/pgsql or a programming language like C.

Typed objects and methods

• The main use of typed objects is to create extensions for handling specific types of data.

• For each data type, there are specific methods that apply to them, like an object-oriented programming language!

• Some really useful examples:

  • Geographic data: points (geo locations), polygons (state, city boundaries), line segments (roads, rivers)

  • Text data: vectors of words and weights for each word

  • JSON

    SELECT '{"foo": {"bar": "baz"}}'::jsonb;
    
    jsonb
    -------------------------
    {"foo": {"bar": "baz"}}
    
    SELECT '{"foo": {"bar": "baz"}}'::jsonb->'foo';
    
    ?column?
    ----------------
    {"bar": "baz"}
    

Geographic Data

• PostGIS is an extension for supporting geographic data with many useful data types of functions.

• First install postgis and create the extension from a superuser:

  create extension postgis;
  create database geodb owner sibeladali template template_postgis ;
  

• Now you can use all the data types and methods available in postgis.

  CREATE TABLE bwithloc (
       name  VARCHAR(100)
       , location geography(POINT,4326)
  ) ;
  
  insert into bwithloc values('Rensselaer Polytechnic Institute',
       ST_GeographyFromText('SRID=4326;POINT(42.7308634 -73.6816793)'));
  
  insert into bwithloc values('Shalimar Restaurant',
       ST_GeographyFromText('SRID=4326;POINT(42.732293 -73.688473)'));
  
  insert into bwithloc values('The Placid Baker',
       ST_GeographyFromText('SRID=4326;POINT(42.7313916 -73.690868)'));
  

• SRID shows the projection used to compute the latitude and longitude.

• You can also enter polygons as arrays of points, line segments are arrays of lines, etc.

• Many geography functions are available (distance is in meters):

  SELECT
     b1.name
     , b2.name
     , ST_DISTANCE(b1.location, b2.location)
  FROM
     bwithloc b1
     , bwithloc b2
  WHERE
     b1.name < b2.name ;
  

• Other examples:

  • Check whether a point is inside a polygon (which city is this restaurant in)?

  • Check the length of a line segment

Text Querying

• The text queries we have seen so far very simplistic: find if the text contains a specific word.

• More sophisticated approaches treat text as a collection of words or tokens.

  • If you want to learn more, information retrieval is a field that studies this!

• Postgresql supports text processing:

  SELECT to_tsvector('fat cats ate fat rats');
  
  to_tsvector
  -----------------------------------
  'ate':3 'cat':2 'fat':1,4 'rat':5
  

  numbers show the location of the keyword in the text.

• Text queries will consist of boolean connection of keywords, tokenized and stop words removed:

  SELECT to_tsquery('english', 'The & Fat & Rats');
  to_tsquery
  ---------------
  'fat' & 'rat'
  

• You can search a keyword query in a document by relevance. The number of times a word appears will increase the relevance of the text to the query.

  We will use the Yelp database as an example:

  SELECT
     b.name
     , ts_rank_cd(to_tsvector('english', r.review_text), query) AS rank
  FROM
     reviews r
     , businesses b
     , to_tsquery('pizza & (crust | sauce) & (delicious|tasty)') query
  WHERE
     b.business_id = r.business_id
     and to_tsvector('english', r.review_text) @@ query
  ORDER BY
     rank DESC
  LIMIT 10;
  
              name            |   rank
  ----------------------------+-----------
   DeFazio's Pizzeria         |      0.05
   Little Bites and More      |      0.05
   Notty Pine Tavern          | 0.0366667
   Red Front Restrnt & Tavern | 0.0285714
   New York Style Pizza       |     0.025
   Milano Restaurant          | 0.0218698
   DeFazio's Pizzeria         | 0.0202986
   The Fresh Market           |      0.02
   Dante's Pizzeria           | 0.0192982
   Labella Pizza              | 0.0155556
  

Summary

• Postgresql extensible with many new data types and associated methods.

• We will also see how it is possible to create the appropriate indices for these data types.