-- (1.1) CREATE TABLE Product ( ProductNumber INT NOT NULL, Etcetera CHAR(30) NOT NULL, CONSTRAINT Product_PK PRIMARY KEY (ProductNumber) ); CREATE TABLE Price ( ProductNumber INT NOT NULL, StartDate DATE NOT NULL, Amount INT NOT NULL, -- Retains the amount in cents. CONSTRAINT Price_PK PRIMARY KEY (ProductNumber, StartDate), CONSTRAINT Price_to_Product_FK FOREIGN KEY (ProductNumber) REFERENCES Product (ProductNumber), CONSTRAINT AmountIsValid_CK CHECK (Amount >= 0) ); -- (1.2) CREATE VIEW PriceWithEndDate AS SELECT P.ProductNumber, P.Etcetera AS ProductEtcetera, PR.Amount AS PriceAmount, PR.StartDate, ( SELECT MIN(StartDate) FROM Price InnerPR WHERE P.ProductNumber = InnerPR.ProductNumber AND InnerPR.StartDate > PR.StartDate ) AS EndDate FROM Product P JOIN Price PR ON P.ProductNumber = PR.ProductNumber;

-- (2) INSERT INTO Product (ProductNumber, Etcetera) VALUES (1750, 'Price time series sample'); INSERT INTO Price (ProductNumber, StartDate, Amount) VALUES (1750, '20170601', 1000), (1750, '20170603', 2000), (1750, '20170605', 3000), (1750, '20170607', 4000);

-- (a) SELECT ProductNumber, ProductEtcetera, PriceAmount, StartDate, EndDate FROM PriceWithEndDate ORDER BY StartDate DESC;

-- (b) SELECT ProductNumber, ProductEtcetera, PriceAmount, StartDate, EndDate FROM PriceWithEndDate WHERE ProductNumber = 1750 AND StartDate <= '20170602' AND EndDate >= '20170602';