Data Warehouse

--Create Lonica Operational Database USE master GO IF EXISTS ( SELECT name FROM sys.databases WHERE name = N'Lonica2' ) Drop DATABASE Lonica2 GO Create Database Lonica2 GO USE Lonica2 /* lONICA - CREATE TABLE statements */ CREATE TABLE Vendor ( VendorID Int NOT NULL, VendorName VARCHAR(25) NOT NULL, PRIMARY KEY (VendorID) ); CREATE TABLE Category ( CategoryID Int NOT NULL, CategoryName VARCHAR(25) NOT NULL, PRIMARY KEY (CategoryID) ); CREATE TABLE Product ( ProductID Int NOT NULL, ProductName VARCHAR(25) NOT NULL, ProductPrice NUMERIC(7,2) NOT NULL, VendorID INT NOT NULL, CategoryID INT NOT NULL, PRIMARY KEY (ProductID), FOREIGN KEY (VendorID) REFERENCES Vendor(VendorID), FOREIGN KEY (CategoryID) REFERENCES Category(CategoryID) ); CREATE TABLE Region ( RegionID Int NOT NULL, RegionName VARCHAR(25) NOT NULL, PRIMARY KEY (RegionID) ); CREATE TABLE Store ( StoreID Int NOT NULL, StoreZip CHAR(5) NOT NULL, RegionID int NOT NULL, PRIMARY KEY (StoreID), FOREIGN KEY (RegionID) REFERENCES Region(RegionID) ); CREATE TABLE Customer ( CustomerID Int NOT NULL, CustomerName VARCHAR(15) NOT NULL, CustomerZip CHAR(5) NOT NULL, PRIMARY KEY (CustomerID) ); CREATE TABLE SalesTransaction ( TID VARCHAR(8) NOT NULL, CustomerID Int NOT NULL, StoreID Int NOT NULL, TDate DATE NOT NULL, PRIMARY KEY (TID), FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID), FOREIGN KEY (StoreID) REFERENCES Store(StoreID) ); CREATE TABLE Includes ( ProductID Int NOT NULL, TID VARCHAR(8) NOT NULL, Quantity INT NOT NULL, PRIMARY KEY (ProductID, TID), FOREIGN KEY (ProductID) REFERENCES Product(ProductID), FOREIGN KEY (TID) REFERENCES SalesTransaction(TID) ); /* INSERT INTO statements */ INSERT INTO Vendor VALUES (1001,'Pacifica Gear'); INSERT INTO Vendor VALUES (1002,'Mountain King'); INSERT INTO Category VALUES (2001,'Camping'); INSERT INTO Category VALUES (2002,'Footwear'); INSERT INTO Product VALUES (3001,'Zzz Bag',100,1001,2001); INSERT INTO Product VALUES (3002,'Easy Boot',70,1001,2002); INSERT INTO Product VALUES (3003,'Cosy Sock',15,1001,2002); INSERT INTO Product VALUES (3004,'Dura Boot',90,1001,2002); INSERT INTO Product VALUES (3005,'Tiny Tent',150,1002,2001); INSERT INTO Product VALUES (3006,'Biggy Tent',250,1002,2001); INSERT INTO Region VALUES (4001,'Chicagoland'); INSERT INTO Region VALUES (4002,'Tristate'); INSERT INTO Region VALUES (4003,'Denton'); INSERT INTO Store VALUES (5001,'60600',4001); INSERT INTO Store VALUES (5002,'60605',4001); INSERT INTO Store VALUES (5003,'35400',4002); INSERT INTO Store VALUES (5004,'87000',4003); INSERT INTO Customer VALUES (6001,'Tina','60137'); INSERT INTO Customer VALUES (6002,'Tony','60600'); INSERT INTO Customer VALUES (6003,'Pam','35400'); INSERT INTO SalesTransaction VALUES (7001,6001,5001,'01/Jan/2020'); INSERT INTO SalesTransaction VALUES (7002,6001,5001,'01/Jan/2020'); INSERT INTO SalesTransaction VALUES (7003,6002,5002,'02/Jan/2020'); INSERT INTO SalesTransaction VALUES (7004,6002,5002,'02/Jan/2020'); INSERT INTO SalesTransaction VALUES (7005,6003,5003,'02/Jan/2020'); INSERT INTO Includes VALUES (3001,7001,1); INSERT INTO Includes VALUES (3002,7001,1); INSERT INTO Includes VALUES (3003,7001,5); INSERT INTO Includes VALUES (3003,7003,1); INSERT INTO Includes VALUES (3001,7003,1); INSERT INTO Includes VALUES (3003,7005,2); INSERT INTO Includes VALUES (3005,7005,4); INSERT INTO Includes VALUES (3006,7005,2); INSERT INTO Includes VALUES (3004,7005,1);