-- duplicates article
-- proposed SQL3

-- Prevent value-equivalent rows in NICUStatus

CREATE TABLE NICUStatus (
   Name CHAR(15),
   Status CHAR(8),
   UNIQUE (Name, Status)
) AS VALIDTIME PERIOD(DATE)

VALIDTIME PERIOD '[1997-11-19 - 1997-11-21)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Kenneth Robert', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-27)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Alexis May', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-25)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Natalie Sue', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-26)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Kelsey Ann', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-26)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Brandon James', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-28)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Nathan Roy', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-20)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Joel Steven& critical')
VALIDTIME PERIOD '[1997-11-20 - 1997-11-26)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Joel Steven& serious')
VALIDTIME PERIOD '[1997-11-21 - 1998-01-03)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Kenneth Robert', 'fair')
VALIDTIME PERIOD '[1997-11-27 - 1998-01-11)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Alexis May', 'fair')
VALIDTIME PERIOD '[1997-12-02 - 9999-12-31)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Alexis May', 'fair')
-- will cause an error
VALIDTIME PERIOD '[1997-12-02 - 9999-12-31)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Alexis May', 'fair')

-- Prevent nonsequenced duplicates in NICUStatus

DROP TABLE NICUStatus

CREATE TABLE NICUStatus (
   Name CHAR(15),
   Status CHAR(8),
   NONSEQUENCED VALIDTIME UNIQUE (Name, Status, VALIDTIME)
) AS VALIDTIME PERIOD(DATE)

VALIDTIME PERIOD '[1997-11-19 - 1997-11-21)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Kenneth Robert', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-27)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Alexis May', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-25)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Natalie Sue', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-26)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Kelsey Ann', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-26)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Brandon James', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-28)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Nathan Roy', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-20)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Joel Steven& critical')
VALIDTIME PERIOD '[1997-11-20 - 1997-11-26)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Joel Steven& serious')
VALIDTIME PERIOD '[1997-11-21 - 1998-01-03)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Kenneth Robert', 'fair')
VALIDTIME PERIOD '[1997-11-27 - 1998-01-11)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Alexis May', 'fair')
VALIDTIME PERIOD '[1997-12-02 - 9999-12-31)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Alexis May', 'fair')
-- will cause an error
VALIDTIME PERIOD '[1997-12-02 - 9999-12-31)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Alexis May', 'fair')

-- Prevent current duplicates in NICUStatus

DROP TABLE NICUStatus

CREATE TABLE NICUStatus (
   Name CHAR(15),
   Status CHAR(8),
   UNIQUE (Name, Status)
) AS VALIDTIME PERIOD(DATE)

VALIDTIME PERIOD '[1997-11-19 - 1997-11-21)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Kenneth Robert', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-27)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Alexis May', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-25)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Natalie Sue', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-26)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Kelsey Ann', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-26)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Brandon James', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-28)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Nathan Roy', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-20)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Joel Steven& critical')
VALIDTIME PERIOD '[1997-11-20 - 1997-11-26)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Joel Steven& serious')
VALIDTIME PERIOD '[1997-11-21 - 1998-01-03)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Kenneth Robert', 'fair')
--will cause an error if today is 1998-01-06
VALIDTIME PERIOD '[1997-11-27 - 1998-01-11)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Alexis May', 'fair')
-- will cause an error
VALIDTIME PERIOD '[1997-12-02 - 9999-12-31)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Alexis May', 'fair')
-- will cause an error
VALIDTIME PERIOD '[1997-12-02 - 9999-12-31)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Alexis May', 'fair')

-- Prevent current duplicates in NICUStatus, assuming no future data

DROP TABLE NICUStatus

CREATE TABLE NICUStatus (
   Name CHAR(15),
   Status CHAR(8),
   UNIQUE (Name, Status)
) AS VALIDTIME PERIOD(DATE)

VALIDTIME PERIOD '[1997-11-19 - 1997-11-21)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Kenneth Robert', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-27)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Alexis May', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-25)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Natalie Sue', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-26)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Kelsey Ann', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-26)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Brandon James', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-28)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Nathan Roy', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-20)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Joel Steven& critical')
VALIDTIME PERIOD '[1997-11-20 - 1997-11-26)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Joel Steven& serious')
VALIDTIME PERIOD '[1997-11-21 - 1998-01-03)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Kenneth Robert', 'fair')
VALIDTIME PERIOD '[1997-11-27 - 1998-01-11)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Alexis May', 'fair')
VALIDTIME PERIOD '[1997-12-02 - 9999-12-31)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Alexis May', 'fair')
-- will cause an error
VALIDTIME PERIOD '[1997-12-02 - 9999-12-31)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Alexis May', 'fair')

-- Prevent sequenced duplicates in NICUStatus

DROP TABLE NICUStatus

CREATE TABLE NICUStatus (
   Name CHAR(15),
   Status CHAR(8),
   VALIDTIME UNIQUE (Name, Status)
) AS VALIDTIME PERIOD(DATE)

VALIDTIME PERIOD '[1997-11-19 - 1997-11-21)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Kenneth Robert', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-27)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Alexis May', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-25)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Natalie Sue', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-26)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Kelsey Ann', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-26)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Brandon James', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-28)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Nathan Roy', 'serious')
VALIDTIME PERIOD '[1997-11-19 - 1997-11-20)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Joel Steven& critical')
VALIDTIME PERIOD '[1997-11-20 - 1997-11-26)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Joel Steven& serious')
VALIDTIME PERIOD '[1997-11-21 - 1998-01-03)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Kenneth Robert', 'fair')
VALIDTIME PERIOD '[1997-11-27 - 1998-01-11)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Alexis May', 'fair')
-- will cause an error
VALIDTIME PERIOD '[1997-12-02 - 9999-12-31)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Alexis May', 'fair')
-- will cause an error
VALIDTIME PERIOD '[1997-12-02 - 9999-12-31)'
INSERT INTO NICUStatus (Name, Status)
VALUES ('Alexis May', 'fair')