SHARE
TWEET

The Iceberg Exercise

anchormodeling Sep 20th, 2015 (edited) 18,978 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2.     Lars Rönnbäck
  3.     lars.ronnback@anchormodeling.com
  4.  
  5.     HISTORY    
  6.     2015-09-16 Created the script.
  7.     2015-09-20 Added uni- and bi-temporally independent
  8.                and dependent joins.
  9.     2015-10-02 Updated to show positive assertions.
  10.                Added section on indecisiveness.
  11.                
  12.  
  13. -------------------------------------------------------
  14.     Create two different positors that may differ
  15.     in opinions about icebergs.
  16.     Note that a 'default' positor has already
  17.     been created with identity 0.
  18. -------------------------------------------------------
  19. */
  20. insert into _Positor
  21. values (1);
  22.  
  23. /*
  24. -------------------------------------------------------
  25.     Populate the knot values.
  26. -------------------------------------------------------
  27. */
  28. insert into ATT_Attachment (
  29.     ATT_ID, ATT_Attachment
  30. ) values
  31. (0, 'Not attached'),
  32. (1, 'Attached');
  33.  
  34. insert into FRZ_Frozen (
  35.     FRZ_ID, FRZ_Frozen
  36. ) values
  37. (0, 'Not frozen'),
  38. (1, 'Frozen');
  39.  
  40. insert into MIA_MissingInAction (
  41.     MIA_ID, MIA_MissingInAction
  42. ) values
  43. (0, 'Missing in action'),
  44. (1, 'Known whereabouts');
  45.  
  46. insert into SOM_SplitOrMerge (
  47.     SOM_ID, SOM_SplitOrMerge
  48. ) values
  49. (1, 'Split'),
  50. (2, 'Merge');
  51.  
  52. insert into SUB_Submerged (
  53.     SUB_ID,     SUB_Submerged
  54. ) values
  55. (0, 'Not submerged'),
  56. (1, 'Submerged');
  57.  
  58. insert into EOE_EnteredOrExited (
  59.     EOE_ID,     EOE_EnteredOrExited
  60. ) values
  61. (0, 'Exited'),
  62. (1, 'Entered');
  63.  
  64. /*
  65. -------------------------------------------------------
  66.     Insert a signal from one of the transmitters.
  67.     Resulting in a TR_ID = 1 being created.
  68. -------------------------------------------------------
  69. */
  70. insert into lTR_Transmitter (
  71.     Positor,
  72.     TR_LOC_ChangedAt, TR_LOC_PositedAt, TR_LOC_Reliability, TR_LOC_Transmitter_Location,
  73.     TR_MIA_ChangedAt, TR_MIA_PositedAt, TR_MIA_Reliability, TR_MIA_MIA_MissingInAction,
  74.                       TR_FSG_PositedAt, TR_FSG_Reliability, TR_FSG_Transmitter_FirstSignal,
  75.     TR_LSG_ChangedAt, TR_LSG_PositedAt, TR_LSG_Reliability, TR_LSG_Transmitter_LastSignal
  76. )
  77. values (
  78.     0,
  79.     '1912-04-15', '2015-01-01',  50, geography::STPointFromText('POINT(41.7325 49.9469)', 4326),
  80.     '1912-04-15', '2015-01-01', 100, 'Known whereabouts',
  81.                   '2015-01-01', 100, '1912-04-01',
  82.     '1912-04-15', '2015-01-01', 100, '1912-04-15 23:40'
  83. );
  84.  
  85. /*
  86. -------------------------------------------------------
  87.     Update, since we got another signal, but the
  88.     location remained the same.
  89.     Only historized attributes can be updated.
  90. -------------------------------------------------------
  91. */
  92. update lTR_Transmitter
  93. set
  94.     TR_LSG_ChangedAt = '1912-04-16',
  95.     TR_LSG_PositedAt = '2015-01-01',
  96.     TR_LSG_Reliability = 80,
  97.     TR_LSG_Transmitter_LastSignal = '1912-04-16 01:01'
  98. where
  99.     TR_ID = 1
  100. and
  101.     Positor = 0;
  102.  
  103. /*
  104. -------------------------------------------------------
  105.     Check the data.
  106.     The last signal should be different between the
  107.     two days.
  108. -------------------------------------------------------
  109. */
  110. select TR_LSG_Transmitter_LastSignal from pTR_Transmitter('1912-04-15') where Positor = 0 and TR_ID = 1;
  111. select TR_LSG_Transmitter_LastSignal from pTR_Transmitter('1912-04-16') where Positor = 0 and TR_ID = 1;
  112.  
  113. /*
  114. -------------------------------------------------------
  115.     Correct, that the first signal was in fact  
  116.     received a day later.
  117.     All attributes can be corrected.
  118.     In order to retract an assertion a new assertion
  119.     is made on the same posit but with zero
  120.     reliability.
  121. -------------------------------------------------------
  122. */
  123. insert into lTR_Transmitter (
  124.     Positor, TR_ID,
  125.     TR_FSG_PositedAt, TR_FSG_Reliability, TR_FSG_Transmitter_FirstSignal
  126. )
  127. values (
  128.     0, 1,
  129.    '2015-01-02',  0, '1912-04-01' -- set reliability to zero
  130. ), (
  131.     0, 1,
  132.    '2015-01-02', 80, '1912-04-02' -- assert another posit
  133. );
  134.  
  135. /*
  136. -------------------------------------------------------
  137.     Check the data.
  138.     The default positor should have made no assertion
  139.     about the first signal before 2015, and was 100%
  140.     sure about the signal on the first, but retracted
  141.     that and is 80% sure of another posit on the
  142.     second of February, 2015.
  143. -------------------------------------------------------
  144. */
  145. select TR_FSG_Transmitter_FirstSignal from tTR_Transmitter(0, DEFAULT, '2014-12-31', '+') where TR_ID = 1;
  146. select TR_FSG_Transmitter_FirstSignal from tTR_Transmitter(0, DEFAULT, '2015-01-01', '+') where TR_ID = 1;
  147. select TR_FSG_Transmitter_FirstSignal from tTR_Transmitter(0, DEFAULT, '2015-01-02', '+') where TR_ID = 1;
  148.  
  149. /*
  150. -------------------------------------------------------
  151.     Insert an iceberg adrift.
  152.     Resulting in a IB_ID = 1 being created.
  153. -------------------------------------------------------
  154. */
  155. insert into lIB_Iceberg (
  156.     Positor,
  157.                       IB_FDA_PositedAt, IB_FDA_Reliability, IB_FDA_Iceberg_FormationDate,
  158.     IB_FRZ_ChangedAt, IB_FRZ_PositedAt, IB_FRZ_Reliability, IB_FRZ_FRZ_Frozen,
  159.     IB_LOC_ChangedAt, IB_LOC_PositedAt, IB_LOC_Reliability, IB_LOC_Iceberg_Location,
  160.     IB_SUB_ChangedAt, IB_SUB_PositedAt, IB_SUB_Reliability, IB_SUB_SUB_Submerged,
  161.     IB_SIZ_ChangedAt, IB_SIZ_PositedAt, IB_SIZ_Reliability, IB_SIZ_Iceberg_Size,
  162.                       IB_DDA_PositedAt, IB_DDA_Reliability, IB_DDA_Iceberg_DisintegrationDate
  163. )
  164. values (
  165.     0,
  166.                   '2015-01-01', 100, '1912-02-13',
  167.     '1912-02-13', '2015-01-01', 100, 'Not frozen',
  168.     '1912-04-15', '2015-01-01',  50, geography::STPointFromText('POINT(42.7325 49.9469)', 4326),
  169.     '1912-02-13', '2015-01-01', 100, 'Not submerged',
  170.     '1912-02-13', '2015-01-01',  50, 555,
  171.                   '2015-01-01', 100, null -- not yet disintegrated
  172. );
  173.  
  174. /*
  175. -------------------------------------------------------
  176.     Let another positor (1, a boat) have a different
  177.     opinion about the whereabouts of the iceberg.
  178.     Note that this must use IB_ID = 1 to refer to
  179.     the same iceberg.
  180. -------------------------------------------------------
  181. */
  182. insert into lIB_Iceberg (
  183.     Positor, IB_ID,
  184.                       IB_FDA_PositedAt, IB_FDA_Reliability, IB_FDA_Iceberg_FormationDate,
  185.     IB_FRZ_ChangedAt, IB_FRZ_PositedAt, IB_FRZ_Reliability, IB_FRZ_FRZ_Frozen,
  186.     IB_LOC_ChangedAt, IB_LOC_PositedAt, IB_LOC_Reliability, IB_LOC_Iceberg_Location,
  187.     IB_SUB_ChangedAt, IB_SUB_PositedAt, IB_SUB_Reliability, IB_SUB_SUB_Submerged,
  188.     IB_SIZ_ChangedAt, IB_SIZ_PositedAt, IB_SIZ_Reliability, IB_SIZ_Iceberg_Size,
  189.                       IB_DDA_PositedAt, IB_DDA_Reliability, IB_DDA_Iceberg_DisintegrationDate
  190. )
  191. values (
  192.     1, 1,
  193.                   '2015-01-01', 100, '1912-02-13',
  194.     '1912-02-13', '2015-01-01', 100, 'Frozen', -- the boat claims the iceberg is stuck at another location
  195.     '1912-04-15', '2015-01-01',  50, geography::STPointFromText('POINT(41.7325 49.9469)', 4326),
  196.     '1912-02-13', '2015-01-01', 100, 'Not submerged',
  197.     '1912-02-13', '2015-01-01',  50, 911, -- and quite much larger
  198.                   '2015-01-01', 100, null -- not yet disintegrated
  199. );
  200.  
  201. /*
  202. -------------------------------------------------------
  203.     Check the data.
  204.     The positors differ in their opinion about the
  205.     size of the iceberg, whether it is frozen or not,
  206.     and its location.
  207. -------------------------------------------------------
  208. */
  209. select
  210.     Positor,
  211.     IB_SIZ_Iceberg_Size,
  212.     IB_FRZ_FRZ_Frozen,
  213.     IB_LOC_Iceberg_Location.STBuffer(IB_SIZ_Iceberg_Size)
  214. from
  215.     lIB_Iceberg where IB_ID = 1;
  216.  
  217. /*
  218. -------------------------------------------------------
  219.     Connect the transmitter to the iceberg.
  220. -------------------------------------------------------
  221. */
  222. insert into lIB_to_TR_attached_ATT_currently (
  223.     Positor,
  224.     IB_to_TR_attached_ATT_currently_ChangedAt,
  225.     IB_to_TR_attached_ATT_currently_PositedAt,
  226.     IB_to_TR_attached_ATT_currently_Reliability,
  227.     IB_ID_to,
  228.     TR_ID_attached,
  229.     currently_ATT_Attachment
  230. ) values (
  231.     0,
  232.     '1912-02-13',
  233.     '2015-01-01',
  234.     100,
  235.     1, -- IB_ID = 1 (our only iceberg)
  236.     1, -- TR_ID = 1 (our only transmitter)
  237.     'Attached'
  238. );
  239.  
  240. /*
  241. -------------------------------------------------------
  242.     Check the data.
  243.     Perform a join over the latest available data in
  244.     the two iceberg and transmitter anchors adjoined
  245.     by the tie modeling the attachment relationship.
  246.     Note that the boat (positor 1) has no concept
  247.     of attached transmitters.
  248.  
  249.     Temporally this corresponds to a join in a
  250.     regular non-temporal database, which can only
  251.     ever hold the latest available information.
  252. -------------------------------------------------------
  253. */
  254. select
  255.     *
  256. from
  257.     lIB_Iceberg ib
  258. left join
  259.     lIB_to_TR_attached_ATT_currently ibtr
  260. on
  261.     ibtr.IB_ID_to = ib.IB_ID
  262. and
  263.     ibtr.Positor = ib.Positor
  264. left join
  265.     lTR_Transmitter tr
  266. on
  267.     tr.TR_ID = ibtr.TR_ID_attached
  268. and
  269.     tr.Positor = ibtr.Positor
  270. where
  271.     ib.IB_ID = 1;
  272.  
  273. /*
  274. -------------------------------------------------------
  275.     Check the data.
  276.     Change to a temporally _independent_ join, a join
  277.     in which a temporal "snapshot" of each
  278.     participating _table_ can be eastablished _before_
  279.     joining the rows.
  280.    
  281.     The query below answers:
  282.     Where were the iceberg and attached transmitter on
  283.     the 13th of February 1912?
  284.  
  285.     Most use-cases of temporal data are covered by
  286.     this type of join.
  287. -------------------------------------------------------
  288. */
  289. select
  290.     *
  291. from
  292.     pIB_Iceberg('1912-02-13') ib
  293. left join
  294.     pIB_to_TR_attached_ATT_currently('1912-02-13') ibtr
  295. on
  296.     ibtr.IB_ID_to = ib.IB_ID
  297. and
  298.     ibtr.Positor = ib.Positor
  299. left join
  300.     pTR_Transmitter('1912-02-13') tr
  301. on
  302.     tr.TR_ID = ibtr.TR_ID_attached
  303. and
  304.     tr.Positor = ibtr.Positor
  305. where
  306.     ib.IB_ID = 1;
  307.  
  308. /*
  309. -------------------------------------------------------
  310.     Check the data.
  311.     Change to a temporally _dependent_ join, a join
  312.     in which a temporal "snapshot" of each
  313.     participating _instance_ must be eastablished
  314.     _during _ the join.
  315.    
  316.     The query below answers:
  317.     Where were the iceberg and attached transmitter at
  318.     the time of formation?
  319.  
  320.     All use-cases of joins can be expressed     through
  321.     temporally independent or dependent joins.
  322. -------------------------------------------------------
  323. */
  324. select
  325.     *
  326. from
  327.     IB_FDA_Iceberg_FormationDate fda
  328. cross apply (
  329.     select
  330.         *
  331.     from
  332.         pIB_Iceberg(fda.IB_FDA_Iceberg_FormationDate) ib
  333.     where
  334.         ib.IB_ID = fda.IB_FDA_IB_ID
  335.     and
  336.         ib.Positor = fda.IB_FDA_Positor
  337. ) ib
  338. outer apply (
  339.     select
  340.         *
  341.     from
  342.         pIB_to_TR_attached_ATT_currently(fda.IB_FDA_Iceberg_FormationDate) ibtr
  343.     where
  344.         ibtr.IB_ID_to = ib.IB_ID
  345.     and
  346.         ibtr.Positor = ib.Positor
  347. ) ibtr
  348. outer apply (
  349.     select
  350.         *
  351.     from
  352.         pTR_Transmitter(fda.IB_FDA_Iceberg_FormationDate) tr
  353.     where
  354.         tr.TR_ID = ibtr.TR_ID_attached
  355.     and
  356.         tr.Positor = ibtr.Positor
  357. ) tr
  358. where
  359.     ib.IB_ID = 1;
  360.  
  361. /*
  362. -------------------------------------------------------
  363.     Check the data.
  364.     A bi-temporally dependent/independent join, with
  365.     the same conditions as in the join above.
  366.  
  367.     Note that changing time has a temporally dependent
  368.     join and that positing time is independent. Since
  369.     positing time is independent it is possible to
  370.     create a variable that holds the positing time
  371.     we are interested int.
  372. -------------------------------------------------------
  373. */
  374. declare @positingTimepoint date = '2015-01-01';
  375.  
  376. select
  377.     *
  378. from
  379.     IB_FDA_Iceberg_FormationDate fda
  380. cross apply (
  381.     select
  382.         *
  383.     from
  384.         tIB_Iceberg(fda.IB_FDA_Positor, fda.IB_FDA_Iceberg_FormationDate, @positingTimepoint, '+') ib
  385.     where
  386.         ib.IB_ID = fda.IB_FDA_IB_ID
  387. ) ib
  388. outer apply (
  389.     select
  390.         *
  391.     from
  392.         tIB_to_TR_attached_ATT_currently(ib.IB_FDA_Positor, fda.IB_FDA_Iceberg_FormationDate, @positingTimepoint, '+') ibtr
  393.     where
  394.         ibtr.IB_ID_to = ib.IB_ID
  395. ) ibtr
  396. outer apply (
  397.     select
  398.         *
  399.     from
  400.         tTR_Transmitter(ib.IB_FDA_Positor, fda.IB_FDA_Iceberg_FormationDate, @positingTimepoint, '+') tr
  401.     where
  402.         tr.TR_ID = ibtr.TR_ID_attached
  403. ) tr
  404. where
  405.     ib.IB_ID = 1;
  406.  
  407. /*
  408. -------------------------------------------------------
  409.     Check the data.
  410.     A bi-temporally dependent/dependent join.
  411.  
  412.     The added condition is that we want to see the
  413.     information as we knew it at time of when we
  414.     knew the formation date, for each instance.
  415. -------------------------------------------------------
  416. */
  417. select
  418.     *
  419. from
  420.     IB_FDA_Iceberg_FormationDate fda
  421. cross apply (
  422.     select
  423.         *
  424.     from
  425.         tIB_Iceberg(fda.IB_FDA_Positor, fda.IB_FDA_Iceberg_FormationDate, fda.IB_FDA_PositedAt, '+') ib
  426.     where
  427.         ib.IB_ID = fda.IB_FDA_IB_ID
  428. ) ib
  429. outer apply (
  430.     select
  431.         *
  432.     from
  433.         tIB_to_TR_attached_ATT_currently(ib.IB_FDA_Positor, fda.IB_FDA_Iceberg_FormationDate, fda.IB_FDA_PositedAt, '+') ibtr
  434.     where
  435.         ibtr.IB_ID_to = ib.IB_ID
  436. ) ibtr
  437. outer apply (
  438.     select
  439.         *
  440.     from
  441.         tTR_Transmitter(ib.IB_FDA_Positor, fda.IB_FDA_Iceberg_FormationDate, fda.IB_FDA_PositedAt, '+') tr
  442.     where
  443.         tr.TR_ID = ibtr.TR_ID_attached
  444. ) tr
  445. where
  446.     ib.IB_ID = 1;
  447.  
  448.  
  449. /*
  450. -------------------------------------------------------
  451.     Update of a static attribute is not done
  452.     using an update-statement, since being a
  453.     correction it involves both a retraction and
  454.     a new assertion.
  455.  
  456.     Note the resulting warning message.
  457. -------------------------------------------------------
  458. */
  459. update lTR_Transmitter
  460. set
  461.     TR_FSG_Transmitter_FirstSignal = '1912-01-31'
  462. where
  463.     TR_ID = 1;
  464.  
  465.  
  466. /*
  467. -------------------------------------------------------
  468.     Change the reliability of an existing posit.
  469.     Sometimes the degree of belief in a posit will
  470.     change over time. In this case the belief is
  471.     lowered from 80 (previous value) to 10.
  472. -------------------------------------------------------
  473. */
  474. insert into lTR_Transmitter (
  475.     Positor, TR_ID,
  476.     TR_FSG_PositedAt, TR_FSG_Reliability, TR_FSG_Transmitter_FirstSignal
  477. )
  478. values (
  479.     0, 1,
  480.    '2015-01-03', 10, '1912-04-02' -- set reliability 10
  481. );
  482.  
  483. /*
  484. -------------------------------------------------------
  485.     Check the data.
  486.     We now see the updated reliability, since this
  487.     is the latest available with respect to the
  488.     positing time.
  489. -------------------------------------------------------
  490. */
  491.  
  492. select TR_FSG_Reliability from lTR_Transmitter where Positor = 0 and TR_ID = 1;
  493.  
  494. /*
  495. -------------------------------------------------------
  496.     Add a second belief with the same positing time.
  497.     This can only be done if indecisiveness is allowed
  498.     in the implementation.
  499.  
  500.     Along with the fact that we believe with 10%
  501.     certainty that the first signal was received
  502.     on the 2nd of April, we at the same positing
  503.     time believe that it was receive on the 1st
  504.     of April with 90% certainty.
  505. -------------------------------------------------------
  506. */
  507. insert into lTR_Transmitter (
  508.     Positor, TR_ID,
  509.     TR_FSG_PositedAt, TR_FSG_Reliability, TR_FSG_Transmitter_FirstSignal
  510. )
  511. values (
  512.     0, 1,
  513.    '2015-01-03', 90, '1912-04-01' -- set reliability 90
  514. );
  515.  
  516. /*
  517. -------------------------------------------------------
  518.     Check the data.
  519.     Note that in order to always retrieve a single
  520.     row per attribute, the views select the one
  521.     with the highest reliability.
  522.  
  523.     Our best guess for the first signal is
  524.     on April 1st, with 90% certainty. Note that
  525.     the database still keeps a record of the
  526.     fact that another posit exists at the same
  527.     time, but with only 10% certainty.
  528. -------------------------------------------------------
  529. */
  530. select TR_FSG_Reliability from lTR_Transmitter where Positor = 0 and TR_ID = 1;
  531.  
  532. /*
  533. -------------------------------------------------------
  534.     Kill the transmitter.
  535.     This uses the overall Reliability column
  536.     to clear out the reliability of the columns
  537.     in the view.
  538. -------------------------------------------------------
  539. */
  540. update lTR_Transmitter
  541. set
  542.     Reliability = 0
  543. where
  544.     TR_ID = 1
  545. and
  546.     Positor = 0;
  547.  
  548. /*
  549. -------------------------------------------------------
  550.     Check the data.
  551.     Note that due to historization the previous
  552.     value in effect now became visible for the
  553.     last received signal.
  554. -------------------------------------------------------
  555. */
  556. select * from lTR_Transmitter where Positor = 0;
  557.  
  558. /*
  559. -------------------------------------------------------
  560.     Kill the transmitter (again).
  561.     This instead uses a delete on the view.
  562. -------------------------------------------------------
  563. */
  564. delete lTR_Transmitter
  565. where
  566.     TR_ID = 1;
  567.  
  568. /*
  569. -------------------------------------------------------
  570.     Check the data.
  571.     This removed the last version we had for the
  572.     last received signal. However, had there been
  573.     more versions we would have had to run more
  574.     deletes to get rid of them.
  575.  
  576.     The identity will, however, still live in the
  577.     database, since we are keeping its memory.
  578.     Therefore we usually have WHERE conditions in
  579.     CRT that checks for non-null values of the
  580.     particular columns we are interested in.
  581. -------------------------------------------------------
  582. */
  583. select * from lTR_Transmitter where Positor = 0;
RAW Paste Data
Top