Create a Star Schema to support Internet Sales The facts tha

Create a Star Schema to support Internet Sales. The facts that are needed are: Order Quantity, Discount Amount, Sales Amount, and Tax Amount. We need to be able to access this data by customer id, date and product name. Create a Start Schema model to support these requirements. To do so, identify your fact table and your dimensions tables based on the information provided above. Create the SQL code for each table. Populate each table with at least 5 records. Create a query that shows all of the facts by customer id, date and product name sorted by date.

Solution

create database internet_Sales;

use internet_Sales;

#SQL code for each table.

create table fact_table(

customer_id int(11),
O_date datetime default null,
P_name varchar(100) default null,
PRIMARY KEY (customer_id)
);

create table dimension_table(
customer_id int(11),
order_quantity int(11),
discount_amt float,
sale_amt float,
tax_amt float,
FOREIGN KEY (customer_id) REFERENCES fact_table (customer_id)
);

#Populating each table with 5 records.
insert into fact_table(customer_id, O_date, P_name)
values (1,\'2016-11-10\',\'A\'),
(2,\'2016-11-11\',\'B\'),
(3,\'2016-11-12\',\'C\'),
(4,\'2016-11-13\',\'D\'),
(5,\'2016-11-14\',\'E\');

insert into dimension_table(customer_id, order_quantity, discount_amt, sale_amt, tax_amt)
values (1,10,20,200,10),
(2,15,30,300,15),
(3,20,40,400,20),
(4,25,50,500,25),
(5,30,60,600,30);

#Query that shows all of the facts by customer id, date and product name sorted by date.

select a.customer_id, a.O_date, a.P_name, b.customer_id, b.order_quantity,
b.discount_amt, b.sale_amt, b.tax_amt
from fact_table a join dimension_table b
on a.customer_id = b.customer_id
order by a.O_date;