Use the star schema you queried last week to create a star schema for the Bob's Fixit Group Case Study. The star schema needs to support the the analysis or measurement of bid dollar amount by customer zip code, customer state, bid month and bid year.

**NOTE: Please answer with code for Microsoft SQL.

LAST WEEKS STAR SCHEMA CODE:

IF OBJECT_ID(N'dbo.DimSalesPerson', N'U') IS NOT NULL DROP TABLE [dbo].[DimSalesPerson]; go create table DimSalesPerson ( sales_person_id int primary key, fname varchar(30), lname varchar(30), sales_team_id varchar(2), sales_team_name varchar(30)) go IF OBJECT_ID(N'dbo.DimDate', N'U') IS NOT NULL DROP TABLE [dbo].[DimDate]; go create table DimDate ( date_key int primary key, quater varchar(10), year varchar(10)) go IF OBJECT_ID(N'dbo.DimCustomer', N'U') IS NOT NULL DROP TABLE [dbo].[DimCustomer]; go create table DimCustomer ( customer_id int primary key, customer_name varchar(30), industry_code varchar(2), industry_name varchar(30), city varchar(30), state varchar(30), street varchar(30), zip varchar(12)) go IF OBJECT_ID(N'dbo.DimProduct', N'U') IS NOT NULL DROP TABLE [dbo].[DimProduct]; go create table DimProduct ( product_id int primary key, product_name varchar(30)) go IF OBJECT_ID(N'dbo.FactSales', N'U') IS NOT NULL DROP TABLE [dbo].[FactSales]; go create table FactSales ( sales_person_id int, date_key int, customer_id int, product_id int, units_sold int, sales_amount money, primary key (sales_person_id, product_id, customer_id, date_key), foreign key (sales_person_id) REFERENCES DimSalesPerson(sales_person_id), foreign key (date_key) REFERENCES DimDate(date_key), foreign key (customer_id) REFERENCES DimCustomer(customer_id), foreign key (product_id) REFERENCES DimProduct(product_id)) go insert into DimSalesPerson values (1, 'Patrick', 'Smith', 'H1', 'Hot Sales') insert into DimSalesPerson values (2, 'Chris', 'Adams', 'H1', 'Hot Sales') insert into DimSalesPerson values (3, 'Sam', 'Jones', 'B1', 'Big Sales') insert into DimSalesPerson values (4, 'James', 'Rinkleff', 'B1', 'Big Sales') insert into DimSalesPerson values (5, 'Luke', 'Depot', 'W1', 'Winning Sales') insert into DimDate values (1, 'Q1', '2016') insert into DimDate values (2, 'Q2', '2016') insert into DimDate values (3, 'Q3', '2016') insert into DimDate values (4, 'Q4', '2016') insert into DimDate values (5, 'Q1', '2017') insert into DimDate values (6, 'Q2', '2017') insert into DimDate values (7, 'Q3', '2017') insert into DimCustomer values (1, 'Costco', '1', 'Retail', 'Omaha', 'NE', '1234 West Center', '68164') insert into DimCustomer values (2, 'Panera Bread', '2', 'Restaurant', 'California City', 'CA', '1234 Sunny Street', '99564') insert into DimCustomer values (3, 'Chich-fil-a', '2', 'Restaurant', 'New York City', 'NY', '1234 haze Street', '44536') insert into DimCustomer values (4, 'Best Buy', '1', 'Retail', 'Seattle', 'WA', '1234 microsoft Street', '23456') insert into DimCustomer values (5, 'Amazon', '3', 'Online', 'Miami', 'FL', '1234 bussy Street', '34567') insert into DimProduct values (1, 'Bread Toaster') insert into DimProduct values (2, 'Rice Oven') insert into FactSales values(1, 1, 1, 1, 2, 45.23) insert into FactSales values(1, 2, 3, 2, 83, 346.99) insert into FactSales values(2, 3, 2, 1, 63, 7364.99) insert into FactSales values(1, 3, 2, 2, 33, 7465.99)