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.

--DROP TABLES

DROP TABLE ORDERS;
DROP TABLE PRODUCTS;
DROP TABLE CUSTOMERS;

--CREATE TABLES
CREATE TABLE PRODUCTS
(
PRODUCT_NAME VARCHAR2(200) NOT NULL,
PRODUCT_ID NUMBER(13) NOT NULL,
SALES_AMT NUMBER(6) NOT NULL,
DISCOUNT NUMBER(6) NOT NULL,
TAX_AMT NUMBER(6) NOT NULL,
PRIMARY KEY(PRODUCT_ID)
);

CREATE TABLE CUSTOMERS
(
CUS_ID NUMBER(5) NOT NULL,
NAME VARCHAR2(50) NOT NULL,
PRIMARY KEY(CUS_ID)
);

CREATE TABLE ORDERS
(
ORDER_ID VARCHAR(6) NOT NULL,
ORDER_DATE DATE NOT NULL,
ORDER_QUANTITY NUMBER(4) NOT NULL,
PRODUCT_ID NUMBER(13) NOT NULL,
CUS_ID NUMBER(5) NOT NULL,
FOREIGN KEY(PRODUCT_ID) REFERENCES PRODUCTS(PRODUCT_ID),
FOREIGN KEY(CUS_ID) REFERENCES CUSTOMERS(CUS_ID),
PRIMARY KEY(ORDER_ID)
);

--INSERT INTO TABLE PRODUCTS
INSERT INTO PRODUCTS VALUES(\'Database Systems\',9788131792476, 652, 94, 56);
INSERT INTO PRODUCTS VALUES(\'Database Systems Concepts\',9789339212126,686, 25, 56);
INSERT INTO PRODUCTS VALUES(\'Introduction to algorithms\',9788120340077,995, 17, 56);
INSERT INTO PRODUCTS VALUES(\'Visual Basic 6 Programming\',9788177205378,1005,80, 89);
INSERT INTO PRODUCTS VALUES(\'Digital Logic and Computer Design\', 9788177584097,593, 67, 13);
INSERT INTO PRODUCTS VALUES(\'Fundamentals of Computer Algorithms\',9788173716126,308,16, 78);
INSERT INTO PRODUCTS VALUES(\'Computer Organization and Architecture\',9780132936330, 813, 269, 78);
INSERT INTO PRODUCTS VALUES(\'Microprocessor Architecture, Programming and Applications\',9788187972884, 316, 26,35);
INSERT INTO PRODUCTS VALUES(\'Unix Concepts And Applications\',9780070635463, 282, 25, 45);

--INSERT INTO TABLE CUSTOMERS
INSERT INTO CUSTOMERS VALUES(001,\'Soubhik Biswas\');
INSERT INTO CUSTOMERS VALUES(002,\'Souraj Ganguly\');
INSERT INTO CUSTOMERS VALUES(003,\'Sudipa Bhakta\');
INSERT INTO CUSTOMERS VALUES(004,\'Sudeshna Ghosh\');
INSERT INTO CUSTOMERS VALUES(005,\'Ritadeep Roy\');
INSERT INTO CUSTOMERS VALUES(006,\'Saswata Sinha\');
INSERT INTO CUSTOMERS VALUES(007,\'Aniket Mukherjee\');
INSERT INTO CUSTOMERS VALUES(008,\'Kinjal Sen\');
INSERT INTO CUSTOMERS VALUES(009,\'Aakash Jaiswal\');
INSERT INTO CUSTOMERS VALUES(010,\'Priyanka Ghosh\');

--INSERT INTO ORDERS
INSERT INTO ORDERS VALUES(111704,TO_DATE(\'01 NOV 2015\',\'DD MON YYYY\'),5,9788131792476,004);
INSERT INTO ORDERS VALUES(112201,TO_DATE(\'10 JUN 2015\',\'DD MON YYYY\'),1,9788131792476,001);
INSERT INTO ORDERS VALUES(113306,TO_DATE(\'30 APR 2015\',\'DD MON YYYY\'),2,9780132936330,003);
INSERT INTO ORDERS VALUES(114207,TO_DATE(\'10 MAY 2015\',\'DD MON YYYY\'),2,9789339212126,001);
INSERT INTO ORDERS VALUES(115101,TO_DATE(\'15 AUG 2015\',\'DD MON YYYY\'),3,9788177584097,005);

COMMIT;

SELECT CUSTOMERS.CUS_ID, ORDERS.ORDER_DATE, PRODUCTS.PRODUCT_NAME
FROM CUSTOMERS, ORDERS, PRODUCTS
WHERE (CUSTOMERS.CUS_ID = ORDERS.CUS_ID) AND
(PRODUCTS.PRODUCT_ID = ORDERS.PRODUCT_ID)
ORDER BY ORDER_DATE;

SAMPLE OUTPUT:

CUS_ID ORDER_DAT PRODUCT_NAME

---------- --------- -------------------------------------------

3 30-APR-15 Computer Organization and Architecture

1 10-MAY-15 Database Systems Concepts

1 10-JUN-15 Database Systems

5 15-AUG-15 Digital Logic and Computer Design

4 01-NOV-15 Database Systems