USE MASTER
GO

CREATE DATABASE Claims
GO

USE Claims
GO

CREATE TABLE Customer
(
	CustomerID INT PRIMARY KEY,
	CustomerName NVARCHAR(30) NOT NULL
)


CREATE TABLE Nomenclature
(
	NomenclatureID INT PRIMARY KEY,
	NomenclatureName NVARCHAR(30) NOT NULL,
	Price MONEY  NOT NULL
)

CREATE TABLE Deal
(
	DealID INT IDENTITY(1, 1) PRIMARY KEY,
	CustomerID INT NOT NULL,
	NomenclatureID INT NOT NULL,
	[Count] DECIMAL(8,2) NOT NULL,
	DealDate DATETIME NOT NULL
)

ALTER TABLE Deal ADD CONSTRAINT [FK_Deal_CustomerID_Customer] FOREIGN KEY(CustomerID)
REFERENCES Customer(CustomerID)
GO

ALTER TABLE Deal ADD CONSTRAINT [FK_Deal_NomenclatureID_Nomenclature] FOREIGN KEY(NomenclatureID)
REFERENCES Nomenclature(NomenclatureID)
GO

CREATE INDEX I_Deal ON Deal(CustomerID, DealDate, NomenclatureID)
GO

INSERT INTO Customer(CustomerID, CustomerName)
SELECT 1, 'Ivanov' UNION ALL
SELECT 2, 'Petrov' UNION ALL
SELECT 3, 'Sidorov'

INSERT INTO Nomenclature(NomenclatureID, NomenclatureName, Price)
SELECT 1, 'Coca-Cola', 30 UNION ALL
SELECT 2, 'Pepsi', 25 UNION ALL
SELECT 3, 'Heineken', 55

INSERT INTO Deal(CustomerID, NomenclatureID, [Count], DealDate)
SELECT 1, 1, 250, '2010-01-01' UNION ALL
SELECT 1, 2, 130, '2010-02-01' UNION ALL
SELECT 1, 1, 150, '2010-03-01' UNION ALL
SELECT 1, 2, 550, '2010-04-01' UNION ALL
SELECT 1, 1, 160, '2010-05-01' UNION ALL
SELECT 1, 2, 150, '2010-06-01' UNION ALL
SELECT 1, 1, 180, '2010-07-01' UNION ALL

SELECT 2, 1, 252, '2010-01-15' UNION ALL
SELECT 2, 2, 132, '2010-02-15' UNION ALL
SELECT 2, 3, 152, '2010-03-15' UNION ALL
SELECT 2, 1, 552, '2010-04-15' UNION ALL
SELECT 2, 1, 162, '2010-05-15' UNION ALL
SELECT 2, 2, 152, '2010-06-15' UNION ALL
SELECT 2, 3, 182, '2010-07-15' UNION ALL

SELECT 3, 1, 253, '2010-01-23' UNION ALL
SELECT 3, 2, 133, '2010-02-23' UNION ALL
SELECT 3, 3, 153, '2010-03-23' UNION ALL
SELECT 3, 1, 553, '2010-04-23' UNION ALL
SELECT 3, 1, 163, '2010-05-23' UNION ALL
SELECT 3, 2, 153, '2010-06-23' UNION ALL
SELECT 3, 3, 183, '2010-07-23'