-- Oracle

DROP TABLE NICUStatus;

-- Prevent value-equivalent rows in NICUStatus

CREATE TABLE NICUStatus (
   Name VARCHAR(15),
   Status VARCHAR(8),
   from_date DATE,
   to_date DATE,
   UNIQUE (Name, Status)
);

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kenneth Robert', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-21','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-27','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Natalie Sue', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-25','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kelsey Ann', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-26','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Brandon James', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-26','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Nathan Roy', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-28','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Joel Steven', 'critical',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-20','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Joel Steven', 'serious',
   TO_DATE('1997-11-20','YYYY-MM-DD'), TO_DATE('1997-11-26','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kenneth Robert', 'fair',
   TO_DATE('1997-11-21','YYYY-MM-DD'), TO_DATE('1998-01-03','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair',
   TO_DATE('1997-11-27','YYYY-MM-DD'), TO_DATE('1998-01-11','YYYY-MM-DD'));

-- should result in error
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair',
   TO_DATE('1997-12-02','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

-- should result in error
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair',
   TO_DATE('1997-12-02','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

-- Prevent nonsequenced duplicates in NICUStatus

DROP TABLE NICUStatus;

CREATE TABLE NICUStatus (
   Name CHAR(15),
   Status CHAR(8),
   from_date DATE,
   to_date DATE,
   UNIQUE (Name, Status, from_date, to_date)
);

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kenneth Robert', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-21','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-27','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Natalie Sue', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-25','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kelsey Ann', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-26','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Brandon James', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-26','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Nathan Roy', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-28','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Joel Steven', 'critical',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-20','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Joel Steven', 'serious',
   TO_DATE('1997-11-20','YYYY-MM-DD'), TO_DATE('1997-11-26','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kenneth Robert', 'fair',
   TO_DATE('1997-11-21','YYYY-MM-DD'), TO_DATE('1998-01-03','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair',
   TO_DATE('1997-11-27','YYYY-MM-DD'), TO_DATE('1998-01-11','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair',
   TO_DATE('1997-12-02','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

-- should result in error
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair',
   TO_DATE('1997-12-02','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

--Prevent current duplicates in NICUStatus

DROP TABLE NICUStatus;

CREATE TABLE NICUStatus (
   Name CHAR(15),
   Status CHAR(8),
   from_date DATE,
   to_date DATE,
   CHECK (NOT EXISTS (SELECT N1.SSN
                     FROM NICUStatus AS N1
                     WHERE 1 < (SELECT COUNT(Name)
                        FROM NICUStatus AS N2
                        WHERE N1.Name = N2.Name AND N1.Status = N2.Status
                           AND N1.from_date <= CURRENT_DATE
                           AND CURRENT_DATE < N1.to_date
                           AND N2.from_date <= CURRENT_DATE
                           AND CURRENT_DATE < N2.to_date)))
);

### Doesn't work: Oracle does not allow tables to be mentioned in check
### constraints 

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

DROP TABLE NICUStatus;

CREATE TABLE NICUStatus (
   Name CHAR(15),
   Status CHAR(8),
   from_date DATE,
   to_date DATE,
   UNIQUE (Name, Status, to_date)
);

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kenneth Robert', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-21','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-27','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Natalie Sue', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-25','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kelsey Ann', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-26','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Brandon James', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-26','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Nathan Roy', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-28','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Joel Steven', 'critical',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-20','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Joel Steven', 'serious',
   TO_DATE('1997-11-20','YYYY-MM-DD'), TO_DATE('1997-11-26','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kenneth Robert', 'fair',
   TO_DATE('1997-11-21','YYYY-MM-DD'), TO_DATE('1998-01-03','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair',
   TO_DATE('1997-11-27','YYYY-MM-DD'), TO_DATE('1998-01-11','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair',
   TO_DATE('1997-12-02','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

-- should result in error
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair',
   TO_DATE('1997-12-02','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

-- Prevent sequenced duplicates in NICUStatus

DROP TABLE NICUStatus;

CREATE TABLE NICUStatus (
   Name CHAR(15),
   Status CHAR(8),
   from_date DATE,
   to_date DATE,
   CHECK (NOT EXISTS (SELECT N1.Name
                     FROM NICUStatus AS N1
                     WHERE 1 < (SELECT COUNT(Name)
                        FROM NICUStatus AS N2
                        WHERE N1.Name = N2.Name AND N1.Status = N2.Status
                           AND N1.from_date < N2.to_date AND N2.from_date < N1.to_date)))
);

### Doesn't work: Oracle does not allow tables to be mentioned in check
### constraints 

--Prevent sequenced duplicates in NICUStatus in Oracle

DROP TABLE NICUStatus;

CREATE TABLE NICUStatus (
   Name VARCHAR(15),
   Status VARCHAR(8),
   from_date DATE,
   to_date DATE
);

CREATE OR REPLACE TRIGGER seq_duplicates
AFTER INSERT OR UPDATE ON NICUStatus
DECLARE 
   valid INTEGER;
BEGIN
   SELECT 1
   INTO valid
   FROM DUAL
   WHERE NOT EXISTS (SELECT N1.Name
         FROM NICUStatus N1, NICUStatus N2
         WHERE N1.Name = N2.Name AND N1.Status = N2.Status
            AND N1.from_date < N2.to_date AND N2.from_date < N1.to_date
            AND N1.rowid != N2.rowid);

   EXCEPTION
      WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR ( -20001 , 'Name and Status must be sequenced unique' );
END;
/

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kenneth Robert', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-21','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-27','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Natalie Sue', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-25','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kelsey Ann', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-26','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Brandon James', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-26','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Nathan Roy', 'serious',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-28','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Joel Steven', 'critical',
   TO_DATE('1997-11-19','YYYY-MM-DD'), TO_DATE('1997-11-20','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Joel Steven', 'serious',
   TO_DATE('1997-11-20','YYYY-MM-DD'), TO_DATE('1997-11-26','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kenneth Robert', 'fair',
   TO_DATE('1997-11-21','YYYY-MM-DD'), TO_DATE('1998-01-03','YYYY-MM-DD'));

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair',
   TO_DATE('1997-11-27','YYYY-MM-DD'), TO_DATE('1998-01-11','YYYY-MM-DD'));

-- should result in an error
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair',
   TO_DATE('1997-12-02','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

-- should result in an error
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair',
   TO_DATE('1997-12-02','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));