-- Standard SQL-92

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),
   Trans_Start TIMESTAMP,
   Trans_Stop TIMESTAMP
)

INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute,
   Discoverer, Mag_First, Trans_Start, Trans_Stop)
VALUES (05, 57, 40, 00, 02, 'BU 1190', 6.5,
   TIMESTAMP '1988-11-08 00:00:00.000000', TIMESTAMP '9999-12-31 00:00:00.000000')

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

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

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

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

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

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

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

SELECT * FROM WDS_TT

DROP TABLE WDS

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

INSERT INTO WDS(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute,
   Discoverer, Mag_First)
SELECT RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute,
   Discoverer, Mag_First
FROM WDS_TT
WHERE Trans_Stop = TIMESTAMP '9999-12-31 00:00:00.000000'

SELECT * FROM WDS

-- trigger defs

-- Note: triggers are not in SQL-92, but are defined in the proposed SQL3

DROP TRIGGER INSERT_WDS

DROP TRIGGER DELETE_WDS

DROP TRIGGER UPDATE_WDS

CREATE TRIGGER INSERT_WDS AFTER INSERT ON WDS
REFERENCING NEW AS N FOR EACH ROW
INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute,
   Discoverer, Mag_First, Trans_Start, Trans_Stop)
VALUES (N.RA_Hour, N.RA_Min, N.RA_Sec, N.Dec_Degree, N.Dec_Minute,
   N.Discoverer, N.Mag_First,
   CURRENT_TIMESTAMP, TIMESTAMP '9999-12-31 00:00:00.000000')

CREATE TRIGGER DELETE_WDS AFTER DELETE ON WDS
REFERENCING OLD AS O FOR EACH ROW
UPDATE WDS_TT SET Trans_Stop = CURRENT_TIMESTAMP
WHERE WDS_TT.Discoverer = O.Discoverer
   AND WDS_TT.Trans_Stop = TIMESTAMP '9999-12-31 00:00:00.000000'

CREATE TRIGGER UPDATE_WDS AFTER UPDATE ON WDS
REFERENCING OLD AS O NEW AS N FOR EACH ROW
BEGIN ATOMIC
   UPDATE WDS_TT SET Trans_Stop = CURRENT_TIMESTAMP
   WHERE WDS_TT.Discoverer = O.Discoverer
      AND WDS_TT.Trans_Stop = TIMESTAMP '9999-12-31 00:00:00.000000';
   INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute,
      Discoverer, Mag_First, Trans_Start, Trans_Stop)
   VALUES (N.RA_Hour, N.RA_Min, N.RA_Sec, N.Dec_Degree, N.Dec_Minute,
      N.Discoverer, N.Mag_First,
      CURRENT_TIMESTAMP, TIMESTAMP '9999-12-31 00:00:00.000000');
END

-- some updates, to test triggers (not in article)

INSERT INTO WDS (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 (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 WHERE Discoverer = 'ES 1933'

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

SELECT * FROM WDS

SELECT * FROM WDS_TT

-- get the current state from the audit log

SELECT RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute,
   Discoverer, Mag_First
FROM WDS_TT
WHERE Trans_Stop = TIMESTAMP '9999-12-31 00:00:00.000000'

-- define a prior state as a view

CREATE VIEW WDS_April_1 AS
SELECT RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute,
   Discoverer, Mag_First
FROM WDS_TT
WHERE Trans_Start <= TIMESTAMP '1994-04-01 00:00:00.000000'
   AND TIMESTAMP '1994-04-01 00:00:00.000000' < Trans_Stop

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?

SELECT Mag_First, Trans_Start, Trans_Stop
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?

SELECT W2.Discoverer,
   (CASE WHEN W1.Trans_Start > W2.Trans_Start
    THEN W1.Trans_Start
    ELSE W2.Trans_Start END),
   (CASE WHEN W1.Trans_Stop < W2.Trans_Stop
    THEN W1.Trans_Stop
    ELSE W2.Trans_Stop END)
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
   AND (CASE WHEN W1.Trans_Start > W2.Trans_Start
        THEN W1.Trans_Start
	ELSE W2.Trans_Start END)
       < (CASE WHEN W1.Trans_Stop < W2.Trans_Stop
	  THEN W1.Trans_Stop
	  ELSE W2.Trans_Stop END)

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

SELECT W1.Discoverer, W1.RA_Sec AS Old_Value, W2.RA_Sec AS New_Value,
   W1.Trans_Stop AS When_Changed
FROM WDS_TT AS W1, WDS_TT AS W2
WHERE W1.Discoverer = W2.Discoverer
   AND W1.Trans_Stop = W2.Trans_Start AND W1.RA_Sec <> W2.RA_Sec

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),
   Trans_Start TIMESTAMP,
   Trans_Stop TIMESTAMP,
   Valid_From DATE,
   Valid_To DATE
)

INSERT INTO WDS_B(Discoverer, Mag_First, Trans_Start, Trans_Stop,
   Valid_From, Valid_To)
VALUES ('A  1248', 12.0,
   TIMESTAMP '1989-03-12 00:00:00.000000', TIMESTAMP '1995-11-15 00:00:00.000000',
   DATE '1922-05-14', DATE '9999-12-31')

INSERT INTO WDS_B(Discoverer, Mag_First, Trans_Start, Trans_Stop,
   Valid_From, Valid_To)
VALUES ('A  1248', 12.0,
   TIMESTAMP '1995-11-15 00:00:00.000000', TIMESTAMP '9999-12-31 00:00:00.000000',
   DATE '1922-05-14', DATE '1994-10-16')

INSERT INTO WDS_B(Discoverer, Mag_First, Trans_Start, Trans_Stop,
   Valid_From, Valid_To)
VALUES ('A  1248', 10.5,
   TIMESTAMP '1995-11-15 00:00:00.000000', TIMESTAMP '9999-12-31 00:00:00.000000',
   DATE '1994-10-16', DATE '9999-12-31')

SELECT * FROM WDS_B

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

CREATE VIEW WDS_VT_AS_OF_Jan_1 AS
SELECT Discoverer, Mag_First, Valid_From, Valid_To
FROM WDS_B
WHERE Trans_Start <= TIMESTAMP '1994-01-01 00:00:00.000000'
   AND TIMESTAMP '1994-01-01 00:00:00.000000' < Trans_Stop

SELECT * FROM WDS_VT_AS_OF_Jan_1

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

SELECT B1.Discoverer, B1.Trans_Stop AS When_Changed,
   (CASE WHEN B1.Valid_From > B2.Valid_From
    THEN B1.Valid_From
    ELSE B2.Valid_From END) AS Valid_From,
   (CASE WHEN B1.Valid_To < B2.Valid_To
    THEN B1.Valid_To
    ELSE B2.Valid_To END) AS Valid_To
FROM WDS_B AS B1, WDS_B AS B2
WHERE B1.Discoverer = B2.Discoverer
   AND B1.Trans_Stop = B2.Trans_Start
   AND (CASE WHEN B1.Valid_From > B2.Valid_From
        THEN B1.Valid_From
	ELSE B2.Valid_From END)
       < DATE '1929-12-31'
   AND DATE '1920-01-01' < (CASE WHEN B1.Valid_To < B2.Valid_To
                            THEN B1.Valid_To
			    ELSE B2.Valid_To END)
   AND (CASE WHEN B1.Valid_From > B2.Valid_From
        THEN B1.Valid_From
	ELSE B2.Valid_From END)
       < (CASE WHEN B1.Valid_To < B2.Valid_To
          THEN B1.Valid_To
	  ELSE B2.Valid_To END)

DROP TABLE WDS_B

DROP TABLE WDS