-- transaction time state tables article
-- proposed SQL3

DROP TABLE WDS_TT

CREATE TABLE WDS_TT (
   RA_Hour INT,
   RA_Min INT,
   RA_Sec INT,
   Dec_Degree INT,
   Dec_Minute INT,
   Discoverer CHAR(7),
   Mag_First DECIMAL(5,2)
) AS TRANSACTIONTIME

-- Note: nonsequenced transaction-time modifications are allowed only for
-- testing purposes, to initialize a table prior to applying other
-- statements to that table. Hence, the following constructs are not
-- included in the SQL3 change proposal, but would be provided, in some
-- form, by a DBMS, again, just for testing purposes
 
NONSEQUENCED TRANSACTIONTIME
   PERIOD '[1988-11-08 00:00:00.000000 - 9999-12-31 00:00:00.000000)'
INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute,
   Discoverer, Mag_First)
VALUES (05, 57, 40, 00, 02, 'BU 1190', 6.5)

NONSEQUENCED TRANSACTIONTIME
  PERIOD '[1989-03-12 00:00:00.000000 - 1992-11-15 00:00:00.000000)'
INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute,
   Discoverer, Mag_First)
VALUES (00, 00, 00, 75, 30, 'A  1248', 12.0)

NONSEQUENCED TRANSACTIONTIME
  PERIOD '[1990-02-09 00:00:00.000000 - 9999-12-31 00:00:00.000000)'
INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute,
   Discoverer, Mag_First)
VALUES (04, 13, 20, 50, 32, 'CHR  15', 15.5)

NONSEQUENCED TRANSACTIONTIME
  PERIOD '[1991-03-25 00:00:00.000000 - 9999-12-31 00:00:00.000000)'
INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute,
   Discoverer, Mag_First)
VALUES (01, 23, 70, -09, 55, 'HJ 3433', 10.5)

NONSEQUENCED TRANSACTIONTIME
  PERIOD '[1992-11-15 00:00:00.000000 - 1994-05-18 00:00:00.000000)'
INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute,
   Discoverer, Mag_First)
VALUES (00, 00, 09, 75, 30, 'A  1248', 12.0)

NONSEQUENCED TRANSACTIONTIME
  PERIOD '[1993-12-19 00:00:00.000000 - 1996-07-09 00:00:00.000000)'
INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute,
   Discoverer, Mag_First)
VALUES (02, 33, 10, -09, 25, 'LDS3402', 10.6)

NONSEQUENCED TRANSACTIONTIME
  PERIOD '[1994-05-18 00:00:00.000000 - 1995-07-23 00:00:00.000000)'
INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute,
   Discoverer, Mag_First)
VALUES (00, 00, 09, 75, 30, 'A  1248', 10.5)

NONSEQUENCED TRANSACTIONTIME
  PERIOD '[1995-07-23 00:00:00.000000 - 9999-12-31 00:00:00.000000)'
INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute,
   Discoverer, Mag_First)
VALUES (00, 00, 08, 75, 30, 'A  1248', 10.5)

SELECT * FROM WDS_TT

DROP VIEW WDS

CREATE VIEW WDS (RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute,
   Discoverer, Mag_First)
AS SELECT RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute,
   Discoverer, Mag_First
FROM WDS_TT

SELECT * FROM WDS

-- some updates

INSERT INTO WDS_TT (RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute,
   Discoverer, Mag_First)
VALUES (00, 02, 00, 60, 57, 'ES 1933', 2.4)

INSERT INTO WDS_TT (RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute,
   Discoverer, Mag_First)
VALUES (00, 08, 00, 31, 23, 'STT 256', 7.9)

DELETE FROM WDS_TT WHERE Discoverer = 'ES 1933'

UPDATE WDS SET_TT Dec_Minute = 33 WHERE Discoverer = 'STT 256'

SELECT * FROM WDS_TT

SELECT * FROM WDS

-- define a prior state as a view

CREATE VIEW WDS_April_1 AS
NONSEQUENCED TRANSACTIONTIME
SELECT RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute,
   Discoverer, Mag_First
FROM WDS_TT
WHERE TRANSACTIONTIME(WDS_TT) OVERLAPS DATE '1994-04-01'

SELECT * FROM WDS_April_1

-- Which stars were of magnitude 11 or brighter, as currently known?

SELECT Discoverer FROM WDS WHERE Mag_First <= 11.0

-- Asking the same question, as best known on April 1, 1994

SELECT Discoverer FROM WDS_April_1 WHERE Mag_First <= 11.0

-- When was it recorded that A1248 had a magnitude other than 10.5?

TRANSACTIONTIME SELECT Mag_First
FROM WDS_TT
WHERE Discoverer = 'A  1248' AND Mag_First <> 10.5

-- When was it recorded that a star had a magnitude equal to that of A1248?

TRANSACTIONTIME SELECT W2.Discoverer,
FROM WDS_TT AS W1, WDS_TT AS W2
WHERE W1.Discoverer = 'A  1248'
   AND W2.Discoverer <> W1.Discoverer
   AND W1.Mag_First = W2.Mag_First

-- When was the RA_Sec position of a double star corrected?

NONSEQUENCED TRANSACTIONTIME
SELECT W1.Discoverer, W1.RA_Sec AS Old_Value, W2.RA_Sec AS New_Value,
   END(TRANSACTIONTIME(W1)) AS When_Changed
FROM WDS_TT AS W1, WDS_TT AS W2
WHERE W1.Discoverer = W2.Discoverer
   AND W1.RA_Sec <> W2.RA_Sec
   AND TRANSACTIONTIME(W1) MEETS TRANSACTIONTIME(W2)

DROP TABLE WDS_TT

DROP TRIGGER INSERT_WDS

DROP TRIGGER DELETE_WDS

DROP TRIGGER UPDATE_WDS

-- bitemporal tables

DROP TABLE WDS_B

CREATE TABLE WDS_B(
   Discoverer CHAR(7),
   Mag_First DECIMAL(5,2)
) AS VALIDTIME PERIOD(DATE) AND TRANSACTIONTIME

-- Note: Again, nonsequenced transaction-time modifications are allowed only
-- for testing purposes, to initialize a table prior to applying other
-- statements to that table.

NONSEQUENCED TRANSACTIONTIME
   PERIOD '[1989-03-12 00:00:00.000000 - 1995-11-15 00:00:00.000000)'
AND VALIDTIME PERIOD '[1922-05-14 - 9999-12-31)'
INSERT INTO WDS_B(Discoverer, Mag_First)
VALUES ('A  1248', 12.0)

NONSEQUENCED TRANSACTIONTIME
  PERIOD '[1995-11-15 00:00:00.000000 - 9999-12-31 00:00:00.000000)'
AND VALIDTIME PERIOD '[1922-05-14 - 1994-10-16)'
INSERT INTO WDS_B(Discoverer, Mag_First)
VALUES ('A  1248', 12.0)

NONSEQUENCED TRANSACTIONTIME
  PERIOD '[1995-11-15 00:00:00.000000 - 9999-12-31 00:00:00.000000)'
AND VALIDTIME PERIOD '[1994-10-16 - 9999-12-31)'
INSERT INTO WDS_B(Discoverer, Mag_First)
VALUES ('A  1248', 10.5)

SELECT * FROM WDS_B

-- What was the history recorded as of January 1, 1994?

CREATE VIEW WDS_VT_AS_OF_Jan_1 AS
VALIDTIME AND NONSEQUENCED TRANSACTIONTIME
SELECT Discoverer, Mag_First
FROM WDS_B
WHERE TRANSACTIONTIME(WDS_B) OVERLAPS DATE '1994-01-01'

SELECT * FROM WDS_VT_AS_OF_Jan_1

-- List the corrections made on plates taken in the 1920's.

NONSEQUENCED TRANSACTIONTIME AND VALIDTIME
SELECT B1.Discoverer, B1.Trans_Stop AS When_Changed
FROM WDS_B AS B1, WDS_B AS B2
WHERE B1.Discoverer = B2.Discoverer
   AND B1.Trans_Stop = B2.Trans_Start
   AND VALIDTIME(B1) OVERLAPS PERIOD '[1920-01-01 - 1929-12-31]'

DROP TABLE WDS_B

DROP VIEW WDS