Question
A data warehouse star schema with the following characteristics will be defined:
Dimension tables:
Date
Product
Customer
Fact table:
Sales
For this project, perform the following steps:
*Create the tables defined above in a star schema. Add the necessary columns in each table to facilitate the implementation of the queries defined below. Only the four tables listed above are allowed in that star schema
*Write PL/SQL code (anonymous blocks and/or subprograms) to populate the warehouse schema with data from the normalized database provided in the attached DDL script
*Write SQL code to perform the following queries:
*What customer age group spent the most money in the last year? An age group is defined as a ten years interval, such as: 11 – 20, 21 – 30, etc
*In what zip codes did the highest number of sales (number of items) occur during April 2015?
*What day of the week did they do most business (by value of sales) in the last year?
*What quarter is the worst (by value of sales) for each product category, using the whole set of historical data available in the warehouse?
*What was the best sales month for each product in the last year?
*Write a couple of paragraphs describing:
*How this small data warehouse can help decision making
*How is it different from the original database used as data source
*Submit the PL/SQL blocks and SQL statements as a text file (Notepad)
Solution Preview
This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.
drop table sales;drop table dates;
drop table product;
drop table customer;
CREATE TABLE DATES
(
DATE_KEY NUMBER NOT NULL
, DAY NUMBER NOT NULL
, MONTH NUMBER NOT NULL
, YEAR NUMBER NOT NULL
, QUARTER NUMBER NOT NULL
, WEEKDAY VARCHAR2(20)
, CONSTRAINT DATES_PK PRIMARY KEY
(
DATE_KEY
)
ENABLE
);
CREATE TABLE PRODUCT
(
PROD_KEY NUMBER NOT NULL
, NAME VARCHAR2(20)
, PRICE NUMBER
, CATEGORIES VARCHAR2(20)
, CONSTRAINT PRODUCT_PK PRIMARY KEY
(
PROD_KEY
)
ENABLE
);
CREATE TABLE CUSTOMER
(
CUST_KEY NUMBER NOT NULL
, NAME VARCHAR2(50) NOT NULL
, DOB DATE
, EMAIL VARCHAR2(50)
, ZIPCODE CHAR(5) NOT NULL
, CONSTRAINT CUSTOMER_PK PRIMARY KEY
(
CUST_KEY
)
ENABLE
);...