In order to provide the evidence of execution, for each question on SQL (or PL/SQL code),...

Transcribed Image Text:

In order to provide the evidence of execution, for each question on SQL (or PL/SQL code), show the SQL command executed in Oracle and the output from Oracle. Here, the output means "7 rows were selected", "table created", or any screenshot that shows your userID/name. Submit a word file with the name convention of 607-HW1-YourLastName.doc. Part A: Dimensional Model and OLAP branch (1) Suppose we have the following star schema. time Dimension table time_key day day_of_the_week month quarter year Branch Dimension table branch_key branch_name branch_type sales Fact table time_key item_key branch_key location key dollars_sold units_sold item Dimension table item_key item_name brand type supplier_type Location Dimension table location_key street city province_or_state country (1-a) List all the dimension names and the PK of each dimension. (2) (1-b) What is the PK of the fact table? (2) (1-c) Identify all measures in the fact table (2) (1-d) Identify the dimension hierarchy of Location dimension. (2) (1-e) Identify the dimension hierarchy of Item dimension. Assume a "brand" belongs to a "type" and a "type" has many "brands". The "supplier type" is either "domestic" or "foreign". And thus, there are M:N relationships between Type and Supplier_type. (3) 2. OLAP Queries Suppose an analyst asked the following INITIAL query to the above star schema. "Find the total amount of dollars sold and the total number of units sold for all the branches for each city and each brand for the years between 2014 to 2017." For each question below, you are asked to give an example of each OLAP operation. just write your answer in plain English. You do not need to show SQL commands (unless you want to 1 INFO 607, Spring 2022, Assignment No. 1, Due on April 24, Il-Yeol Song do so for practice). See an example answer of a Rollup query in (2-a) below. EXPLAIN WHY YOUR QUERY SATISFIES THE SPECIFIED OLAP OPERATION! (2-a) Give a specific example of a Rollup query of the above initial query. (5) (2-b) Give a specific example of a Drilldown query of the above initial query. (5) (2-c) Give a specific example of a Slice query of the above initial query. (5) (2-c) Give a specific example of a Slice query of the above initial query. (5) (2-d) Give a specific example of a Dice query of the above initial query. (5) Part B: SQL Note that you can do the following SQL questions using Personal Oracle 18C or 19C. But Personal Oracle 11g does not support materialized views (Question (5) below). Hence, Question 5 on MV must be done using Drexel Linux Oracle, if you use Personal Oracle 11g. (1) (a)Create the Order Star Schema from the file called OrderStarSchema.txt file. Copy (CTRL C)) the commands of the text file and paste them (CTRL V or right click) into Oracle and execute them. To run the file, press (CTRL + Enter) to run the commands. This will execute the commands you just pasted. The commands create a star schema and populates the star schema. Here, a fact table row represents a sale amount and quantity of the product on a given date by a specific customer with the employee who is involved with the sale. (5) (b) Enter the following SET commands: (3) SET FEEDBACK ON. SET LINESIZE 500 SET NUMWIDTH 7 The ERD of the Order Schema is shown below: Product Dimension Product Key Product ID Product Description Product Code Brand_name List Price Min_Price Price_Start Date Price_End Date Current_Flag Time Dimension Day_Key Actual Date Day of month Month Quarter Year Sales Fact OrderDate_Key(FK) Product Key(FK) Customer_Key(FK) Employee Key (FK) Sales Dollar Amount Quantity Customer Dimension Customer_Key CustomerID Name Address City State Zip_code Area_code Phone_number Credit_limit comments Employee Dimension Employee_key Employe elD First name Middle_Initial Last_name Sex Manager_id +Hire_date Salary Commission Job_code dob (2) Populating Time Dimension (a) Define a sequence called seq time that start with 1 and increment by 1. Use CREATE SEQUENCE command. This command crates a surrogate key in Oracle. (3) SQL> CREATE SEQUENCE seq_time START WITH 1 INCREMENT BY 1; (b) Copy and paste the PL/SQL program called populate time dimension in the text file (shown below again) into Oracle to populate TIME DIMENSION. Make sure to copy the forward slash at the end of the PL/SQL program as well when you copy from the text file. The forward slash tells Oracle to execute the PL/SQL program. The program is replicated here for your study. Note do not copy from this Word file as copying from a word file sometimes copies extra formatting command that are not recognized by Oracle. (5) /* This procedure is used to populate the time dimension table with values between a specified date range. */ CREATE OR REPLACE PROCEDURE populate time dimension (p start date IN DATE, pend date IN DATE) IS --Variables that will be used to hold column values while iterating from BEGIN --the start date until the end date. Typed using the column types from --the time dimension table ActualDate DayOfMonth y y Month V Quarter y Year time dimension actual date%type: time dimension day of month%type: time dimension month%type; time dimension quarter%type; time dimension year%type: --initialize the actual date to the start date ActualDate Rstart_date: LOOP --exit when the actual date is greater than the end date EXIT WHEN y ActualDate > p end date: --extract the necessary information from the current date --extract the necessary information from the current date TO_NUMBER(TO_CHAR(v_ActualDate,'DD')); --v DayOfMonth= v Month= TO_CHAR(v_ActualDate,'MONTH'); v Quarter := TO_NUMBER(TO_CHAR(v_ActualDate, 'Q')); --v Year := TO_NUMBER(TO_CHAR(v_ActualDate,'YYYY')); V DayOfMonth= EXTRACT (DAY FROM y ActualDate); v Year:= EXTRACT (YEAR FROM y ActualDate); --insert the data INSERT INTO TIME DIMENSION(Day Key. Actual Date, Day of month, Month, Quarter, Year) VALUES(seg time.NEXTVAL. ActualDate. x. DayOfMonth. x.Month. x. Quarter. x Year): --increment the date v. ActualDate:-V ActualDate + 1; END LOOP; END populate time dimension: You should see the message "Procedure created." From Oracle. (c) Use EXEC command to execute the above PL/SQL program to populate the date dimension for the last 4 years. (3) SQL> Exec populate_time_dimension ('01-JAN-1999, 31-DEC-2001'); (d) Use select command to display the first 10 days of each January of 1999, 2000, and 2001. Use only one SELECT command. (3) (3) Display Star Schema Data (4) Display the contents of all product dimension, employee dimension, customer dimension, and Sales fact tables using SELECT * command. To display data in a large row, use SET command. For example, SET LINESIZE 500 SELECT * FROM product dimension, (4) OLAP Queries on Star Schema Write SQL commands and show the output from the Order Star Schema data. Your answer must include both SQL commands and the output from Oracle. Note that the output from Oracle should be legible. Do not copy and paste too small font screen. Copy the screen with a larger font that is legible. Or enlarge your screen image. (a) Use GROUP BY for the following query (5) Find the total number of units sold (quantity) for each product for each employee for each year. Also display employee first name, last name, and product description. (b) Use RANK () function for the following query (5) Rank the products in terms of the total sales amount from top to bottom. Display product description and their total sales amount. (c) Use ROLLUP command for the following query (5) Using ROLLUP command, generate a monthly sub total, yearly sub total and grand total of each distinct product. Display Product descriptions. (d) Use CUBE command for the following query (5) Use the CUBE command. Display the various subtotal and the grand total of sales amount of each customer each year. Use customer name. (5) MV If your system allows, create materialized views. If not, create simple views. Use SET AUTOTRACE ON before you execute the query below. (a) Create a materialized view called customer monthly sales which lists the customer key, product key, sale year, sale month, and the total sales value of the product purchased by the customer in that particular month. (5) (b) Show the data of the MV object in the order of customer key. Porduct key. year, month, and the total sales amount. (5) (c) Write a query that displays customer_key. sale year, the total sales of the customer for that year. Use the MV called customer monthly sales. (5) (d) Write a query that displays customer_key. sale year, the total sales of the customer for that year. Do not use the (materialized) view, but use the tables in the basic Order Star schema. Can you confirm this query is executed using the MV called customer monthly sales by Oracle's Query Rewrite feature even though you use the base tables. (5) (6) DD Execute the following query command. The command generates the objects created by you. Show the output. Without the answer to this question, your whole Assignment 1 is marked as zero. (The SQL commands in this question may not work in non-Oracle systems. So, you should figure out the correct data dictionary names and use the correct SQL commands for those systems.) (3) COLUMN USER FORMAT A6 COLUMN OBJECT_NAME FORMAT A20 COLUMN OBJECT_TYPE FORMAT A6 SELECT user, object_name, object_type, created, sysdate FROM user objects WHERE object name IN ('TIME_DIMENSION', 'CSUTOMER_DIMENSION', 'SALES_FACT', 'EMPLOYEE_DIMENSION', 'PRODUCT_DIMENSION'); Note: Copy the above SQL commands to a text editor and then copy into Oracle. Directly copying SQL commands from a Word file sometimes copies hidden character and causes errors, especially in quotes. If you still meet a syntax error even after copying from a text editor, directly type in the SQL commands into Oracle. ORACLE Tips for formatting Output You may edit the output for visual clarity with font size, bold face and italic, adding comments- as far as you don't change the commands or output. When you paste output into Word, it really helps to have it in Courier New font in 8-point (using a landscape page) to maintain the format of Oracle output. Or you can use a snipping tool to capture the output from Oracle. Study FORMAT Command to adjust the display of output in SELECT command Page dimensions The defaults in sqlplus are 14 rows x 80-chars. To change to, eg, 60 rows x 500-chars, use: set PAGESIZE 60; set LINESIZE 500; Set up the paper in Landscape mode, and not Portrait. Then, increase the line length: Example: o SQL> SET LINESIZE 500 Oracle usually gives feedback such as rows selected". However, the default number of rows is 6. That is, when 6 or more rows are displayed, the feedback is displayed. If you want to always see the feedback displayed, you should enter the command at the beginning of the session: SQL> SET FEEDBACK ON. If you want to display all the tables you created, try SQL> SELECT table name FROM USER_TABLES; If you want to display only one table scheme, say employee table: SQL> DESC employee By default numeric data is right-justified and char data is left-justified. Numeric data is displayed in 10 spaces, but can be changed by: SQL> SET NUMWIDTH 7 This affects the display of all numeric data, until changed or disconnected. Numeric value formatting: SQL>COLUMN Salary FORMAT $999,999.99 SQL>SELECT Salary FROM Employee; will display salary in the format of $45,000. Character value formatting: SQL> COLUMN Lname FORMAT A10 Lnames are displayed in 10 char left justified format If Lname is longer than 10 chars, only the first 10 are displayed. Changing the column headings: - Use label SQL> SELECT Lname Last Name FROM Employee; - Use a double quote to include spaces SQL> SELECT Lname "NAME OF EMP" FROM Employee; - Use bar ("|") for a "stacked" heading SQL> SELECT LNAME "Last Name Of Employee" will display Last Name Of Employee Use COLUMN (or COL) commands SQL> COLUMN Lname HEADING NAME-OF-EMPLOYEE SQL> SELECT Lname FROM Employee; will use NAME-OF-EMPLOYEE whenever Lname is mentioned Displaying the current setting of a column SQL> COL Lname Clear the current setting of a column SQL> COLUMN Lname CLEAR The default is one blank space between headings of the attribute columns. This can be changed with the UFI command SET SPACE N, where N is the number of spaces to be displayed. For example, to change it to 3, type SQL> SET SPACE 3 Creating Order Star Schema for Assignment 1 of INFO 607 at Drexel Linux Oracle WARNING: 1. Copying pasting multiple commands in one CTRL C and and pasting. could casue some commands not executed properly. 2. So, copy and paste one command at a time to be in the safe side. 3. At the bottom of this file, there are commands to wipe out all the tables so that you can begin from scratch again 4. Ask any questions on Oracle and this commands to my TA */ --Perform automatic commit to immediately save all the data SET AUTOCOMMIT ON -- Creating time dimension drop table time dimension CASCADE CONSTRAINTS; create table time dimension ( Day Key Number (6) Not null, actual_date date not null, day_of_month number (2) not null, month varchar2 (9) not null, quarter number (1) not null, year number (4) not null, constraint check_time_quarter check (QUARTER IN (1,2,3,4))); alter table time_dimension add constraint time_dim_pk PRIMARY KEY (Day_key); -- Creating product_dimension drop table product_dimension cascade constraints; create table product_dimension ( prod_key number (6), product_id number (6), product_description varchar2 (30), product_code varchar2 (4), brand name varchar2 (10), list price number (8,2), min_price number (8,2), price_start_date date, price_end_date date, current_flag char (1) -- ); alter table product_dimension add constraint prod_dim_pk Primary key (prod_key); Creating employee_dimension drop table employee dimension cascade constraints; create table employee_dimension ( emp_key number (6), employee_id number (4), first_name varchar2 (15), middle_initial varchar2 (1), last_name varchar2 (15), sex char (1), manager id number (4), hire date date, salary number (7,2), commission number (7,2), job_code number (2), dob date sales_dollar_amount number (10,2), quantity number (4)); alter table sales_fact add constraint sales_key_pk primary. key (orderdate_key, prod_key, cust_key, emp_key); alter table sales_fact add constraint orderdate_key_fk foreign key (orderdate_key) references time_dimension (Day_Key); alter table sales_fact add constraint product_key_fk foreign key (prod_key) references product_dimension (prod_key); alter table sales_fact add constraint customer_key_ fk foreign key (cust_key) references customer_dimension (cust_key); alter table sales_fact add constraint employee_key_fk foreign key (emp_key) references employee_dimension (emp_key); -- Insert into time_dimension drop sequence seq_time; create sequence seq_time start with 1 increment by 1; create or replace procedure populate_time_dimension (p_start_date in DATE, p_end_date in DATE) is v actualDate date; v dayofmonth number (2); v_month varchar2 (9); V quarter number (1); v year number (4); Begin v_actualdate := p_start_date; LOOP exit when v_actualDate>p_end_date; v_dayofmonth:= to_number (to_char (v_actualDate, 'DD')); v_Month : to_char (v_actualDate, 'Month'); v_quarter : to_number (to_char (v_actualDate, 'Q')); v_year : to_number (to_char (v_actualdate, 'YYYY')); insert into time_dimension (day_key, actual_date, day_of_month, month, quarter, year) values (seq_time.nextval, v_actualDate, v_dayofmonth, v_month, v_quarter, v_year); v_actualDate:= v_ActualDate+1; End Loop: end populate_time_dimension; / exec populate_time_dimension ('01-jan-1999', '31-dec-2001'); SELECT from time_dimension; * -- Insert into product_dimension (32 rows) insert into product dimension values ( 1, 100860, ACE TENNIS RACKET I', 'IGAA', 'ACE',30, 24, TO_DATE ('01-JAN-99'), TO_DATE ('31-DEC-00'), 'F'); insert into product_dimension values ( 2, 100860, ACE TENNIS RACKET I', 'IGAA', 'ACE', 36, 30, TO_DATE ('01-JAN-01'), TO_DATE ('31-DEC-03'), 'F'); insert into product_dimension values ( 3, 100860, 'ACE TENNIS RACKET I', 'IGAA', 'ACE', 42, 38, TO_DATE('01-JAN-04'), TO_DATE('31-DEC-07'), 'F'); insert into product_dimension values ( 19, 100867, 'RH "GUIDE TO TENNIS"', 'UGAA', 'RH',30, 24, TO_DATE ('01-JUN-00'), NULL, 'T'); insert into product_dimension values ( 20, 100868, 'SB ENERGY BAR-6 PACK', 'AGAA', 'SB', 3.4, 2.8, TO DATE ('18-AUG-99'), NULL, 'T'); insert into product_dimension values ( 21, 100869, 'SB VITA SNACK-6 PACK', 'AGAA', 'SB',27.8, 22.3, TO_DATE ('01-JAN-99), TO_DATE('31-DEC- 99), 'F'); insert into product_dimension values ( 22, 100869, 'SB VITA SNACK-6 PACK', 'AGAA', 'SB',33.5, 27, TO_DATE ('01-JAN-00), TO_DATE ('31-MAY- 02'), 'F'); insert into product_dimension values ( 23, 100869, 'SB VITA SNACK-6 PACK', 'AGAA', 'SB',39.3, 34, TO_DATE ('01-JUN-02'), NULL, 'T'); insert into product_dimension values ( 24, 100870, 'WIFF SOFTBALL BAT I', 'IGAA', 'WIFF',25, 20, TO_DATE ('01-JAN-00), TO_DATE ('31-DEC- 00'), 'F');" inport into insert into product_dimension values ( 25, 100870, 'WIFF SOFTBALL BAT I', 'IGAA', 'WIFF',30, 25, TO_DATE ('01-JAN-01'), TO_DATE ('01-JUL- 02), 'F'); inport into insert into product_dimension values ( 26, 100870, 'WIFF SOFTBALL BAT I', 'IGAA', 'WIFF',35, 30, TO_DATE ('02-JUL-02'), TO_DATE ('01-DEC- 04'), 'F').' insert into product_dimension values ( 27, 100870, 'WIFF SOFTBALL BAT I', 'IGAA', 'WIFF', 40, 35, TO_DATE ('01-JAN-05'), NULL, 'T'); insert into insert into product_dimension values ( 28, 100871, 'WIFF SOFTBALL BAT II', 'ICAA', 'WIFF', 23, 18, TO_DATE ('01-JAN-00'), TO_DATE ('31-DEC- 05'), 'F'); insert into product_dimension values ( 29, 100871, 'WIFF SOFTBALL BAT II', 'ICAA', 'WIFF',27, 24, TO_DATE ('01-JAN-06'), TO_DATE ('31-DEC- 06'), 'F'); insert into product dimension values (30, 100871, 'WIFF SOFTBALL BAT II', 'ICAA', 'WIFF',30, 28, TO_DATE ('01-JAN-07'), TO_DATE ('31-DEC- 07'), 'F'); insert into product dimension values ( 31, 100871, 'WIFF SOFTBALL BAT II', 'ICAA', 'WIFF', 33, 31, TO_DATE ('01-JAN-08'), TO_DATE ('31-DEC- 08'), 'F'); insert into product_dimension values ( 32, 100871, 'WIFF SOFTBALL BAT II', 'ICAA', 'WIFF', 40, 34, TO_DATE ('01-JAN-09'), NULL, 'T'); select from product_dimension; Insert into Employee_dimension (6 rows). INSERT into EMPLOYEE DIMENSION values (1,7506, 'LYNN', 'S','DENNIS', 'F',7839, to_date('01-MAY- 00), 2750, NULL, 23, to_date ('29-APR-81')); INSERT into EMPLOYEE DIMENSION values (2,7508, 'CYNTHIA', 'D', 'WARD', 'F',7698, to_date('22-FEB- 95'), 1250, 500, 30, to_date ('18-JUN-76')); INSERT into EMPLOYEE DIMENSION values ZEITVI IR! 7820 - (3,7505, 'JEAN', 'K', 'KELLY', 'F', 7839, to_date ('04-APR- 05'),2850,NULL, 13. to date ('03-JAN-17: INSERT into EMPLOYEE DIMENSION values 00'),800. NULL 30 to date('23-NOV-51')); (4,7369, 'JOHN', 'F', 'HENNESSEY', 'M', 7902, to_date ('17-APR- " INSERT into EMPLOYEE DIMENSION values (5,7507, 'LESLIE', 'D', 'BAKKER', 'F',7839, to_date('10-JUN- 95), 2200, NULL, 14, to_date ('13-MAY-61')); INSERT into EMPLOYEE DIMENSION values (6,7499, 'KEVIN', 'J', 'ALLEN', 'M',7698, to_date('20-FEB- 95'), 1600, 300, 20, to_date ('20-MAR-55')); select from employee_dimension; insert into sales_fact values (572,9,6,3,45,1); insert into sales_fact values (572, 11, 6, 3, 42,1); insert into sales_fact values (738, 2, 2, 1, 36, 100); insert into sales_fact values (738,25,2,1,30, 12); insert into sales_fact values (738,17,2,1,24,1); insert into sales_fact values (742, 25, 2, insert into sales_fact values (742,2,2,1,36,3); insert into sales_fact values (746,5, 1, 1, 54, 2); Chata insert into sales_fact values (746,2,1,1,36, 12); insert into sales_fact values (487,1,1,1,30, 7); insert into sales_fact values (487,5, 1, 1, 54, 1); insert into sales_fact values (522, 20,3,2,3.4, 1); insert into sales_fact values (522, 28, 3, 2,23,200); insert into sales_fact values (522,1,3,2, 30, 444); insert into sales_fact values (522, 24, 3, 2, 25, 1000); Chata insert into sales_fact values (522,5, 3, 2, 54, 20); Sa insert into sales_fact values (522, 11, 3, insert into sales_fact values (487,1,3,2,30, 10); insert into sales_fact values (487,20,3,2,3.4, 1000); insert into sales_fact values (487, 24, 3, 2, 25, 500); insert into sales_fact values (532, 28, 4, 2, 23, 100); insert into sales_fact values (561, 1, 4, 2, 30, 200); insert into sales_fact values (561, 17,5, 3,24,150); insert into sales_fact values (561, 18,5, 3, 4, 100); Sa insert into sales_fact values (561,1,5, 3, 30, 50); Tek insert into sales fact values (579,1,5, 3, 30, 100); 1,30, 20); 2, 42,150); insert into sales fact values (579, 28,5, 3, 23, 1000); insert into sales_fact values (579,5,5, 3, 54, 10); insert into sales_fact values (565, 24, 6, 3, 25, 50); insert into sales_fact values (572, 28,7, 4, 23,1000); insert into sales_fact values (522, 5, 8, 5, 54, 10); insert into sales_fact values (802, 25,9,6,30,50); select from sales_fact; --To view the primary key constraint of star schema tables: SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER CONSTRAINTS WHERE AND CONSTRAINT_TYPE IN ('P', 'R') (TABLE_NAME LIKE 'DIMENSION') OR (TABLE_NAME LIKE 'FACT'); -- - The 3 ALTER commands you need to execute to createa MV in Drexel Linux Oreacle System SQL> alter session set OPTIMIZER MODE = all rows; alter session set QUERY REWRITE ENABLED = TRUE; alter session set QUERY REWRITE INTEGRITY = ENFORCED; -To drop all the tables to start from scractch. drop table time_dimension CASCADE CONSTRAINTS; drop table product dimension cascade constraints; drop table employee_dimension cascade constraints; drop table customer_dimension cascade constraints; drop table sales_fact cascade constraints; End of document In order to provide the evidence of execution, for each question on SQL (or PL/SQL code), show the SQL command executed in Oracle and the output from Oracle. Here, the output means "7 rows were selected", "table created", or any screenshot that shows your userID/name. Submit a word file with the name convention of 607-HW1-YourLastName.doc. Part A: Dimensional Model and OLAP branch (1) Suppose we have the following star schema. time Dimension table time_key day day_of_the_week month quarter year Branch Dimension table branch_key branch_name branch_type sales Fact table time_key item_key branch_key location key dollars_sold units_sold item Dimension table item_key item_name brand type supplier_type Location Dimension table location_key street city province_or_state country (1-a) List all the dimension names and the PK of each dimension. (2) (1-b) What is the PK of the fact table? (2) (1-c) Identify all measures in the fact table (2) (1-d) Identify the dimension hierarchy of Location dimension. (2) (1-e) Identify the dimension hierarchy of Item dimension. Assume a "brand" belongs to a "type" and a "type" has many "brands". The "supplier type" is either "domestic" or "foreign". And thus, there are M:N relationships between Type and Supplier_type. (3) 2. OLAP Queries Suppose an analyst asked the following INITIAL query to the above star schema. "Find the total amount of dollars sold and the total number of units sold for all the branches for each city and each brand for the years between 2014 to 2017." For each question below, you are asked to give an example of each OLAP operation. just write your answer in plain English. You do not need to show SQL commands (unless you want to 1 INFO 607, Spring 2022, Assignment No. 1, Due on April 24, Il-Yeol Song do so for practice). See an example answer of a Rollup query in (2-a) below. EXPLAIN WHY YOUR QUERY SATISFIES THE SPECIFIED OLAP OPERATION! (2-a) Give a specific example of a Rollup query of the above initial query. (5) (2-b) Give a specific example of a Drilldown query of the above initial query. (5) (2-c) Give a specific example of a Slice query of the above initial query. (5) (2-c) Give a specific example of a Slice query of the above initial query. (5) (2-d) Give a specific example of a Dice query of the above initial query. (5) Part B: SQL Note that you can do the following SQL questions using Personal Oracle 18C or 19C. But Personal Oracle 11g does not support materialized views (Question (5) below). Hence, Question 5 on MV must be done using Drexel Linux Oracle, if you use Personal Oracle 11g. (1) (a)Create the Order Star Schema from the file called OrderStarSchema.txt file. Copy (CTRL C)) the commands of the text file and paste them (CTRL V or right click) into Oracle and execute them. To run the file, press (CTRL + Enter) to run the commands. This will execute the commands you just pasted. The commands create a star schema and populates the star schema. Here, a fact table row represents a sale amount and quantity of the product on a given date by a specific customer with the employee who is involved with the sale. (5) (b) Enter the following SET commands: (3) SET FEEDBACK ON. SET LINESIZE 500 SET NUMWIDTH 7 The ERD of the Order Schema is shown below: Product Dimension Product Key Product ID Product Description Product Code Brand_name List Price Min_Price Price_Start Date Price_End Date Current_Flag Time Dimension Day_Key Actual Date Day of month Month Quarter Year Sales Fact OrderDate_Key(FK) Product Key(FK) Customer_Key(FK) Employee Key (FK) Sales Dollar Amount Quantity Customer Dimension Customer_Key CustomerID Name Address City State Zip_code Area_code Phone_number Credit_limit comments Employee Dimension Employee_key Employe elD First name Middle_Initial Last_name Sex Manager_id +Hire_date Salary Commission Job_code dob (2) Populating Time Dimension (a) Define a sequence called seq time that start with 1 and increment by 1. Use CREATE SEQUENCE command. This command crates a surrogate key in Oracle. (3) SQL> CREATE SEQUENCE seq_time START WITH 1 INCREMENT BY 1; (b) Copy and paste the PL/SQL program called populate time dimension in the text file (shown below again) into Oracle to populate TIME DIMENSION. Make sure to copy the forward slash at the end of the PL/SQL program as well when you copy from the text file. The forward slash tells Oracle to execute the PL/SQL program. The program is replicated here for your study. Note do not copy from this Word file as copying from a word file sometimes copies extra formatting command that are not recognized by Oracle. (5) /* This procedure is used to populate the time dimension table with values between a specified date range. */ CREATE OR REPLACE PROCEDURE populate time dimension (p start date IN DATE, pend date IN DATE) IS --Variables that will be used to hold column values while iterating from BEGIN --the start date until the end date. Typed using the column types from --the time dimension table ActualDate DayOfMonth y y Month V Quarter y Year time dimension actual date%type: time dimension day of month%type: time dimension month%type; time dimension quarter%type; time dimension year%type: --initialize the actual date to the start date ActualDate Rstart_date: LOOP --exit when the actual date is greater than the end date EXIT WHEN y ActualDate > p end date: --extract the necessary information from the current date --extract the necessary information from the current date TO_NUMBER(TO_CHAR(v_ActualDate,'DD')); --v DayOfMonth= v Month= TO_CHAR(v_ActualDate,'MONTH'); v Quarter := TO_NUMBER(TO_CHAR(v_ActualDate, 'Q')); --v Year := TO_NUMBER(TO_CHAR(v_ActualDate,'YYYY')); V DayOfMonth= EXTRACT (DAY FROM y ActualDate); v Year:= EXTRACT (YEAR FROM y ActualDate); --insert the data INSERT INTO TIME DIMENSION(Day Key. Actual Date, Day of month, Month, Quarter, Year) VALUES(seg time.NEXTVAL. ActualDate. x. DayOfMonth. x.Month. x. Quarter. x Year): --increment the date v. ActualDate:-V ActualDate + 1; END LOOP; END populate time dimension: You should see the message "Procedure created." From Oracle. (c) Use EXEC command to execute the above PL/SQL program to populate the date dimension for the last 4 years. (3) SQL> Exec populate_time_dimension ('01-JAN-1999, 31-DEC-2001'); (d) Use select command to display the first 10 days of each January of 1999, 2000, and 2001. Use only one SELECT command. (3) (3) Display Star Schema Data (4) Display the contents of all product dimension, employee dimension, customer dimension, and Sales fact tables using SELECT * command. To display data in a large row, use SET command. For example, SET LINESIZE 500 SELECT * FROM product dimension, (4) OLAP Queries on Star Schema Write SQL commands and show the output from the Order Star Schema data. Your answer must include both SQL commands and the output from Oracle. Note that the output from Oracle should be legible. Do not copy and paste too small font screen. Copy the screen with a larger font that is legible. Or enlarge your screen image. (a) Use GROUP BY for the following query (5) Find the total number of units sold (quantity) for each product for each employee for each year. Also display employee first name, last name, and product description. (b) Use RANK () function for the following query (5) Rank the products in terms of the total sales amount from top to bottom. Display product description and their total sales amount. (c) Use ROLLUP command for the following query (5) Using ROLLUP command, generate a monthly sub total, yearly sub total and grand total of each distinct product. Display Product descriptions. (d) Use CUBE command for the following query (5) Use the CUBE command. Display the various subtotal and the grand total of sales amount of each customer each year. Use customer name. (5) MV If your system allows, create materialized views. If not, create simple views. Use SET AUTOTRACE ON before you execute the query below. (a) Create a materialized view called customer monthly sales which lists the customer key, product key, sale year, sale month, and the total sales value of the product purchased by the customer in that particular month. (5) (b) Show the data of the MV object in the order of customer key. Porduct key. year, month, and the total sales amount. (5) (c) Write a query that displays customer_key. sale year, the total sales of the customer for that year. Use the MV called customer monthly sales. (5) (d) Write a query that displays customer_key. sale year, the total sales of the customer for that year. Do not use the (materialized) view, but use the tables in the basic Order Star schema. Can you confirm this query is executed using the MV called customer monthly sales by Oracle's Query Rewrite feature even though you use the base tables. (5) (6) DD Execute the following query command. The command generates the objects created by you. Show the output. Without the answer to this question, your whole Assignment 1 is marked as zero. (The SQL commands in this question may not work in non-Oracle systems. So, you should figure out the correct data dictionary names and use the correct SQL commands for those systems.) (3) COLUMN USER FORMAT A6 COLUMN OBJECT_NAME FORMAT A20 COLUMN OBJECT_TYPE FORMAT A6 SELECT user, object_name, object_type, created, sysdate FROM user objects WHERE object name IN ('TIME_DIMENSION', 'CSUTOMER_DIMENSION', 'SALES_FACT', 'EMPLOYEE_DIMENSION', 'PRODUCT_DIMENSION'); Note: Copy the above SQL commands to a text editor and then copy into Oracle. Directly copying SQL commands from a Word file sometimes copies hidden character and causes errors, especially in quotes. If you still meet a syntax error even after copying from a text editor, directly type in the SQL commands into Oracle. ORACLE Tips for formatting Output You may edit the output for visual clarity with font size, bold face and italic, adding comments- as far as you don't change the commands or output. When you paste output into Word, it really helps to have it in Courier New font in 8-point (using a landscape page) to maintain the format of Oracle output. Or you can use a snipping tool to capture the output from Oracle. Study FORMAT Command to adjust the display of output in SELECT command Page dimensions The defaults in sqlplus are 14 rows x 80-chars. To change to, eg, 60 rows x 500-chars, use: set PAGESIZE 60; set LINESIZE 500; Set up the paper in Landscape mode, and not Portrait. Then, increase the line length: Example: o SQL> SET LINESIZE 500 Oracle usually gives feedback such as rows selected". However, the default number of rows is 6. That is, when 6 or more rows are displayed, the feedback is displayed. If you want to always see the feedback displayed, you should enter the command at the beginning of the session: SQL> SET FEEDBACK ON. If you want to display all the tables you created, try SQL> SELECT table name FROM USER_TABLES; If you want to display only one table scheme, say employee table: SQL> DESC employee By default numeric data is right-justified and char data is left-justified. Numeric data is displayed in 10 spaces, but can be changed by: SQL> SET NUMWIDTH 7 This affects the display of all numeric data, until changed or disconnected. Numeric value formatting: SQL>COLUMN Salary FORMAT $999,999.99 SQL>SELECT Salary FROM Employee; will display salary in the format of $45,000. Character value formatting: SQL> COLUMN Lname FORMAT A10 Lnames are displayed in 10 char left justified format If Lname is longer than 10 chars, only the first 10 are displayed. Changing the column headings: - Use label SQL> SELECT Lname Last Name FROM Employee; - Use a double quote to include spaces SQL> SELECT Lname "NAME OF EMP" FROM Employee; - Use bar ("|") for a "stacked" heading SQL> SELECT LNAME "Last Name Of Employee" will display Last Name Of Employee Use COLUMN (or COL) commands SQL> COLUMN Lname HEADING NAME-OF-EMPLOYEE SQL> SELECT Lname FROM Employee; will use NAME-OF-EMPLOYEE whenever Lname is mentioned Displaying the current setting of a column SQL> COL Lname Clear the current setting of a column SQL> COLUMN Lname CLEAR The default is one blank space between headings of the attribute columns. This can be changed with the UFI command SET SPACE N, where N is the number of spaces to be displayed. For example, to change it to 3, type SQL> SET SPACE 3 Creating Order Star Schema for Assignment 1 of INFO 607 at Drexel Linux Oracle WARNING: 1. Copying pasting multiple commands in one CTRL C and and pasting. could casue some commands not executed properly. 2. So, copy and paste one command at a time to be in the safe side. 3. At the bottom of this file, there are commands to wipe out all the tables so that you can begin from scratch again 4. Ask any questions on Oracle and this commands to my TA */ --Perform automatic commit to immediately save all the data SET AUTOCOMMIT ON -- Creating time dimension drop table time dimension CASCADE CONSTRAINTS; create table time dimension ( Day Key Number (6) Not null, actual_date date not null, day_of_month number (2) not null, month varchar2 (9) not null, quarter number (1) not null, year number (4) not null, constraint check_time_quarter check (QUARTER IN (1,2,3,4))); alter table time_dimension add constraint time_dim_pk PRIMARY KEY (Day_key); -- Creating product_dimension drop table product_dimension cascade constraints; create table product_dimension ( prod_key number (6), product_id number (6), product_description varchar2 (30), product_code varchar2 (4), brand name varchar2 (10), list price number (8,2), min_price number (8,2), price_start_date date, price_end_date date, current_flag char (1) -- ); alter table product_dimension add constraint prod_dim_pk Primary key (prod_key); Creating employee_dimension drop table employee dimension cascade constraints; create table employee_dimension ( emp_key number (6), employee_id number (4), first_name varchar2 (15), middle_initial varchar2 (1), last_name varchar2 (15), sex char (1), manager id number (4), hire date date, salary number (7,2), commission number (7,2), job_code number (2), dob date sales_dollar_amount number (10,2), quantity number (4)); alter table sales_fact add constraint sales_key_pk primary. key (orderdate_key, prod_key, cust_key, emp_key); alter table sales_fact add constraint orderdate_key_fk foreign key (orderdate_key) references time_dimension (Day_Key); alter table sales_fact add constraint product_key_fk foreign key (prod_key) references product_dimension (prod_key); alter table sales_fact add constraint customer_key_ fk foreign key (cust_key) references customer_dimension (cust_key); alter table sales_fact add constraint employee_key_fk foreign key (emp_key) references employee_dimension (emp_key); -- Insert into time_dimension drop sequence seq_time; create sequence seq_time start with 1 increment by 1; create or replace procedure populate_time_dimension (p_start_date in DATE, p_end_date in DATE) is v actualDate date; v dayofmonth number (2); v_month varchar2 (9); V quarter number (1); v year number (4); Begin v_actualdate := p_start_date; LOOP exit when v_actualDate>p_end_date; v_dayofmonth:= to_number (to_char (v_actualDate, 'DD')); v_Month : to_char (v_actualDate, 'Month'); v_quarter : to_number (to_char (v_actualDate, 'Q')); v_year : to_number (to_char (v_actualdate, 'YYYY')); insert into time_dimension (day_key, actual_date, day_of_month, month, quarter, year) values (seq_time.nextval, v_actualDate, v_dayofmonth, v_month, v_quarter, v_year); v_actualDate:= v_ActualDate+1; End Loop: end populate_time_dimension; / exec populate_time_dimension ('01-jan-1999', '31-dec-2001'); SELECT from time_dimension; * -- Insert into product_dimension (32 rows) insert into product dimension values ( 1, 100860, ACE TENNIS RACKET I', 'IGAA', 'ACE',30, 24, TO_DATE ('01-JAN-99'), TO_DATE ('31-DEC-00'), 'F'); insert into product_dimension values ( 2, 100860, ACE TENNIS RACKET I', 'IGAA', 'ACE', 36, 30, TO_DATE ('01-JAN-01'), TO_DATE ('31-DEC-03'), 'F'); insert into product_dimension values ( 3, 100860, 'ACE TENNIS RACKET I', 'IGAA', 'ACE', 42, 38, TO_DATE('01-JAN-04'), TO_DATE('31-DEC-07'), 'F'); insert into product_dimension values ( 19, 100867, 'RH "GUIDE TO TENNIS"', 'UGAA', 'RH',30, 24, TO_DATE ('01-JUN-00'), NULL, 'T'); insert into product_dimension values ( 20, 100868, 'SB ENERGY BAR-6 PACK', 'AGAA', 'SB', 3.4, 2.8, TO DATE ('18-AUG-99'), NULL, 'T'); insert into product_dimension values ( 21, 100869, 'SB VITA SNACK-6 PACK', 'AGAA', 'SB',27.8, 22.3, TO_DATE ('01-JAN-99), TO_DATE('31-DEC- 99), 'F'); insert into product_dimension values ( 22, 100869, 'SB VITA SNACK-6 PACK', 'AGAA', 'SB',33.5, 27, TO_DATE ('01-JAN-00), TO_DATE ('31-MAY- 02'), 'F'); insert into product_dimension values ( 23, 100869, 'SB VITA SNACK-6 PACK', 'AGAA', 'SB',39.3, 34, TO_DATE ('01-JUN-02'), NULL, 'T'); insert into product_dimension values ( 24, 100870, 'WIFF SOFTBALL BAT I', 'IGAA', 'WIFF',25, 20, TO_DATE ('01-JAN-00), TO_DATE ('31-DEC- 00'), 'F');" inport into insert into product_dimension values ( 25, 100870, 'WIFF SOFTBALL BAT I', 'IGAA', 'WIFF',30, 25, TO_DATE ('01-JAN-01'), TO_DATE ('01-JUL- 02), 'F'); inport into insert into product_dimension values ( 26, 100870, 'WIFF SOFTBALL BAT I', 'IGAA', 'WIFF',35, 30, TO_DATE ('02-JUL-02'), TO_DATE ('01-DEC- 04'), 'F').' insert into product_dimension values ( 27, 100870, 'WIFF SOFTBALL BAT I', 'IGAA', 'WIFF', 40, 35, TO_DATE ('01-JAN-05'), NULL, 'T'); insert into insert into product_dimension values ( 28, 100871, 'WIFF SOFTBALL BAT II', 'ICAA', 'WIFF', 23, 18, TO_DATE ('01-JAN-00'), TO_DATE ('31-DEC- 05'), 'F'); insert into product_dimension values ( 29, 100871, 'WIFF SOFTBALL BAT II', 'ICAA', 'WIFF',27, 24, TO_DATE ('01-JAN-06'), TO_DATE ('31-DEC- 06'), 'F'); insert into product dimension values (30, 100871, 'WIFF SOFTBALL BAT II', 'ICAA', 'WIFF',30, 28, TO_DATE ('01-JAN-07'), TO_DATE ('31-DEC- 07'), 'F'); insert into product dimension values ( 31, 100871, 'WIFF SOFTBALL BAT II', 'ICAA', 'WIFF', 33, 31, TO_DATE ('01-JAN-08'), TO_DATE ('31-DEC- 08'), 'F'); insert into product_dimension values ( 32, 100871, 'WIFF SOFTBALL BAT II', 'ICAA', 'WIFF', 40, 34, TO_DATE ('01-JAN-09'), NULL, 'T'); select from product_dimension; Insert into Employee_dimension (6 rows). INSERT into EMPLOYEE DIMENSION values (1,7506, 'LYNN', 'S','DENNIS', 'F',7839, to_date('01-MAY- 00), 2750, NULL, 23, to_date ('29-APR-81')); INSERT into EMPLOYEE DIMENSION values (2,7508, 'CYNTHIA', 'D', 'WARD', 'F',7698, to_date('22-FEB- 95'), 1250, 500, 30, to_date ('18-JUN-76')); INSERT into EMPLOYEE DIMENSION values ZEITVI IR! 7820 - (3,7505, 'JEAN', 'K', 'KELLY', 'F', 7839, to_date ('04-APR- 05'),2850,NULL, 13. to date ('03-JAN-17: INSERT into EMPLOYEE DIMENSION values 00'),800. NULL 30 to date('23-NOV-51')); (4,7369, 'JOHN', 'F', 'HENNESSEY', 'M', 7902, to_date ('17-APR- " INSERT into EMPLOYEE DIMENSION values (5,7507, 'LESLIE', 'D', 'BAKKER', 'F',7839, to_date('10-JUN- 95), 2200, NULL, 14, to_date ('13-MAY-61')); INSERT into EMPLOYEE DIMENSION values (6,7499, 'KEVIN', 'J', 'ALLEN', 'M',7698, to_date('20-FEB- 95'), 1600, 300, 20, to_date ('20-MAR-55')); select from employee_dimension; insert into sales_fact values (572,9,6,3,45,1); insert into sales_fact values (572, 11, 6, 3, 42,1); insert into sales_fact values (738, 2, 2, 1, 36, 100); insert into sales_fact values (738,25,2,1,30, 12); insert into sales_fact values (738,17,2,1,24,1); insert into sales_fact values (742, 25, 2, insert into sales_fact values (742,2,2,1,36,3); insert into sales_fact values (746,5, 1, 1, 54, 2); Chata insert into sales_fact values (746,2,1,1,36, 12); insert into sales_fact values (487,1,1,1,30, 7); insert into sales_fact values (487,5, 1, 1, 54, 1); insert into sales_fact values (522, 20,3,2,3.4, 1); insert into sales_fact values (522, 28, 3, 2,23,200); insert into sales_fact values (522,1,3,2, 30, 444); insert into sales_fact values (522, 24, 3, 2, 25, 1000); Chata insert into sales_fact values (522,5, 3, 2, 54, 20); Sa insert into sales_fact values (522, 11, 3, insert into sales_fact values (487,1,3,2,30, 10); insert into sales_fact values (487,20,3,2,3.4, 1000); insert into sales_fact values (487, 24, 3, 2, 25, 500); insert into sales_fact values (532, 28, 4, 2, 23, 100); insert into sales_fact values (561, 1, 4, 2, 30, 200); insert into sales_fact values (561, 17,5, 3,24,150); insert into sales_fact values (561, 18,5, 3, 4, 100); Sa insert into sales_fact values (561,1,5, 3, 30, 50); Tek insert into sales fact values (579,1,5, 3, 30, 100); 1,30, 20); 2, 42,150); insert into sales fact values (579, 28,5, 3, 23, 1000); insert into sales_fact values (579,5,5, 3, 54, 10); insert into sales_fact values (565, 24, 6, 3, 25, 50); insert into sales_fact values (572, 28,7, 4, 23,1000); insert into sales_fact values (522, 5, 8, 5, 54, 10); insert into sales_fact values (802, 25,9,6,30,50); select from sales_fact; --To view the primary key constraint of star schema tables: SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER CONSTRAINTS WHERE AND CONSTRAINT_TYPE IN ('P', 'R') (TABLE_NAME LIKE 'DIMENSION') OR (TABLE_NAME LIKE 'FACT'); -- - The 3 ALTER commands you need to execute to createa MV in Drexel Linux Oreacle System SQL> alter session set OPTIMIZER MODE = all rows; alter session set QUERY REWRITE ENABLED = TRUE; alter session set QUERY REWRITE INTEGRITY = ENFORCED; -To drop all the tables to start from scractch. drop table time_dimension CASCADE CONSTRAINTS; drop table product dimension cascade constraints; drop table employee_dimension cascade constraints; drop table customer_dimension cascade constraints; drop table sales_fact cascade constraints; End of document