gen-survey-database.sql

   1 -- The `Person` table is used to explain the most basic queries.
   2 -- Note that `danforth` has no measurements.
   3 create table Person(
   4         ident    text,
   5         personal text,
   6         family   text
   7 );
   8
   9 insert into Person values('dyer',     'William',   'Dyer');
  10 insert into Person values('pb',       'Frank',     'Pabodie');
  11 insert into Person values('lake',     'Anderson',  'Lake');
  12 insert into Person values('roe',      'Valentina', 'Roerich');
  13 insert into Person values('danforth', 'Frank',     'Danforth');
  14
  15 -- The `Site` table is equally simple.  Use it to explain the
  16 -- difference between databases and spreadsheets: in a spreadsheet,
  17 -- the lat/long of measurements would probably be duplicated.
  18 create table Site(
  19         name text,
  20         lat  real,
  21         long real
  22 );
  23
  24 insert into Site values('DR-1', -49.85, -128.57);
  25 insert into Site values('DR-3', -47.15, -126.72);
  26 insert into Site values('MSK-4', -48.87, -123.40);
  27
  28 -- `Visited` is an enhanced `join` table: it connects to the lat/long
  29 -- of specific measurements, and also provides their dates.
  30 -- Note that #752 is missing a date; we use this to talk about NULL.
  31 create table Visited(
  32         ident integer,
  33         site  text,
  34         dated text
  35 );
  36
  37 insert into Visited values(619, 'DR-1',  '1927-02-08');
  38 insert into Visited values(622, 'DR-1',  '1927-02-10');
  39 insert into Visited values(734, 'DR-3',  '1939-01-07');
  40 insert into Visited values(735, 'DR-3',  '1930-01-12');
  41 insert into Visited values(751, 'DR-3',  '1930-02-26');
  42 insert into Visited values(752, 'DR-3',  null);
  43 insert into Visited values(837, 'MSK-4', '1932-01-14');
  44 insert into Visited values(844, 'DR-1',  '1932-03-22');
  45
  46 -- The `Survey` table is the actual readings.  Join it with `Site` to
  47 -- get lat/long, and with `Visited` to get dates (except for #752).
  48 -- Note that Roerich's salinity measurements are an order of magnitude
  49 -- too large (use this to talk about data cleanup).  Note also that
  50 -- there are two cases where we don't know who took the measurement,
  51 -- and that in most cases we don't have an entry (null or not) for the
  52 -- temperature.
  53 create table Survey(
  54         taken   integer,
  55         person  text,
  56         quant   text,
  57         reading real
  58 );
  59
  60 insert into Survey values(619, 'dyer', 'rad',    9.82);
  61 insert into Survey values(619, 'dyer', 'sal',    0.13);
  62 insert into Survey values(622, 'dyer', 'rad',    7.80);
  63 insert into Survey values(622, 'dyer', 'sal',    0.09);
  64 insert into Survey values(734, 'pb',   'rad',    8.41);
  65 insert into Survey values(734, 'lake', 'sal',    0.05);
  66 insert into Survey values(734, 'pb',   'temp', -21.50);
  67 insert into Survey values(735, 'pb',   'rad',    7.22);
  68 insert into Survey values(735, null,   'sal',    0.06);
  69 insert into Survey values(735, null,   'temp', -26.00);
  70 insert into Survey values(751, 'pb',   'rad',    4.35);
  71 insert into Survey values(751, 'pb',   'temp', -18.50);
  72 insert into Survey values(751, 'lake', 'sal',    0.10);
  73 insert into Survey values(752, 'lake', 'rad',    2.19);
  74 insert into Survey values(752, 'lake', 'sal',    0.09);
  75 insert into Survey values(752, 'lake', 'temp', -16.00);
  76 insert into Survey values(752, 'roe',  'sal',   41.60);
  77 insert into Survey values(837, 'lake', 'rad',    1.46);
  78 insert into Survey values(837, 'lake', 'sal',    0.21);
  79 insert into Survey values(837, 'roe',  'sal',   22.50);
  80 insert into Survey values(844, 'roe',  'rad',   11.25);