PRODUCT CUSTOMER Product_ID Product Name Product Desc Product_Type Customer ID C

Question

PRODUCT CUSTOMER Product_ID Product Name Product Desc Product_Type Customer ID Customer_Name Customer_Desc Customer_Category SALES Product ID Customer ID Region_ID Time_ID Sales Profit REGION TIME Time ID Day Week Month Year Region_ID Street City Country Figure 1. A Sample Star Schema 1. Write a SQL statement to generate a 3 dimensional cube called CUBE1 that views Profits per State per, Product_Type, and per Month 2. Write a SQL statement to generate a 2 dimensional cube called CUBE2 directly from CUBE1. CUBE2 views Profits per State and per Product_Type 3. Convert the star schema to a snowflake schema by putting City, State, and Country in a separate dimension table called City. Please draw the resulting snowflake schema 4. Based on the generated snowflake schema, please write a SQL statement to create a cube that views Sales per Country, per Customer_Category, and per Year

Explanation / Answer

1) CREATE VIEW CUBE1 AS SELECT P.Profit PROFIT, R.State STATE, P.Product_Type PRODUCT_TYPE, T.Month MONTH FROM ((SALES S JOIN REGION R ON S.Region_ID=R.Region_ID) JOIN PRODUCT P ON P.Product_ID=S.Product_ID) JOIN TIME T ON T.TimeID=S.TimeID;

2) SELECT PROFIT, STATE, PRODUCT_TYPE FROM CUBE1;

3) CREATE TABLE CITY(
    City_ID int PRIAMRY KEY AUTO_INCREMENT,
    City_name,
    State VARACHAR(100),
    Country VARCHAR(30),
);

CREATE TABLE REGION(
    Region_ID int PRIMARY KEY AUTO_INCREMENT,
    Street VARACHAR(255),
    City_ID int,
    FOREGIN KEY(City_ID) REFERENCES CITY(City_ID)
);

4) SELECT S.Sales SALES, C.Country COUNTRY, CST.Customer_Category CUSTOMER_CATEGORY, T.Year YEAR FROM (((SALES S JOIN (REGION R JOIN CITY C ON R.City_ID=C.City_ID) ON S.Region_ID=R.Region_ID) JOIN CUSTOMERS CST ON CST.Customer_ID=S.Customer_ID) JOIN TIME T ON T.Time_ID = S.Time_ID;

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.