see the new one

CCOS221 DW Labs 1 to 3.STO.pd 701 2. Use the CREATE and SELECT us to Deemaldabled_PRODUCT Dating PRODUCT_COPY and CATEGORY_COPY PRODUCTS PRODUCT MODUCT NAME CATEGORY CATEGORY_NAME PARENT CATEGORY_O PARENT CATEGORY NAME Distry the entire content of the URODUCT te should get you in the PRODUCT_COPY in which malformat PRODUCT Note that you have prepared the denormaludable PODUCT toont a DW PRODUCT anemon Ung the Muminat model concepts/Termine what they can he Wettbutes TOUT Use the CREATE and SELECT tatements to the State you at A ORDERS DIRECT ORDER TO ORDER_YEAR ORDER MOOE SALES, Etanie rosh ORDER MODE DIRECT NOT NULL SALES REPOUTO_CHAR TO NUMBER functions Duplay the title coment. You should Check that the ORDER_MODE come in the Remove the ORDEMODE come from ORDERS RECT & ORDERS ONLINE Same ORDES ORECT ORDER MODE ONLINE * Display the table content. You should Check that the ONDERMODE come the condition in the CCOS221 DW Labs 1 70 3 STD. CCDS-221 Data Warehouse Temove the ORDER MODE and SALES_20_ORDERS_ONLINE Optional question In the same way it the CUSTOMERS tablettes having the mettres CUSTOMERS.net for Male GENDER) for GENDER Part. Megetables Merger is the opposite of the NION. The state.com Le love the same number of columns with completo Create to compatible tables sued from the tablety contents . - 4 Lah Summary, Desorbe the main tasks you memented and the end 10:00 PM Mon 8 Feb @ 100% - CCDS221 DW Labs 1 TO 3_STD.pdf CCDS-221 Data Warehouse Lab #3 ETL Process Denormalization, Merging, and Splitting of Tables Statement purpose ROLAP (On-Line Analytical Processing) technology implements the dimensional tables of the multidimensional schema as Denormalized (just in 1NF) tables in order to speed up the processing of analytical queries (avoiding multiple JOINS). This work will allow you to prepare (in the staging area) denormalized tables by joining normalized tables, split a table into sub-tables and Merge the result of several queries/tables Activity Outcomes Students will be able to join tables to build denormalized tables in the staging area, split and Merge tables. Instructor Note Implement all the questions below and submit, for each one, the code and its execution result (as a screenshot) displayed by Oracle Use the E Database schema available at the link https://ivesal.oracle.com to answer. Note: The SELECT privilege is GRANTed to PUBLIC on the tables so that you prefix cach table of this DB by its creator (OE) in SQL statements (You can also create a synonym). You need the tables PRODUCT_INFORMATION, CATEGORY_TAB, and CUSTOMERS. Sample contents of these tables are in Appendix 1. Answer the following questions, Part I. Denormalization Consult tables PRODUCT_INFORMATION and CATEGORY TAB to understand their structure and content 10:01 PM Mon 8 Feb @ 100% - 7 of 13 CCDS221 DW Labs 1 To 3_STD.pdf Part I. Denormalization Consult tables PRODUCT_INFORMATION and CATEGORY_TAB to understand their structure and content. CATEGORIES_TAB (CATEGORY_NAME, CATEGORY_DESCRIPTION, CATEGORY_ID, PARENT_CATEGORY_ID) PRODUCT_INFORMATION (PRODUCT_ID, PRODUCT_NAME... CATEGORY_ID SUPPLIER_ID..) Note the existence of two relationships: each product has a CATEGORY_ID that exists in the CATEGORY_TAB table, and each category has a parent category (PARENT_CATEGORY_ID). Academic Year 2020/2021 University of Jeddah College of Computer Science & Engineering IST Dept Term 11 CCDS-221 Data Warehouse 1. CREATE the following tables: PRODUCT_COPY (PRODUCT_ID, PRODUCT_NAME, CATEGORY_ID, SUPPLIER_ID) by SELECTing rows from the OE.PRODUCT_INFORMATION table. CATEGORY_COPY by SELECTing rows from the OE CATEGORY_TAB table. 2. Use the CREATE and SELECT statements to create a Denormalized table called T_PRODUCT by natural-joining PRODUCT_COPY and CATEGORY_COPY. T_PRODUCT has the columns: PRODUCT_ID, PRODUCT_NAMES 10:01 PM Mon 8 Feb @ 100% CCDS221 DW Labs 1 TO 3_STD.pdf 7 of 13 2. Use the CREATE and SELECT statements to create a Denormalized table called T_PRODUCT by natural-joining PRODUCT_COPY and CATEGORY_COPY. T_PRODUCT has the columns PRODUCT ID, PRODUCT NAME CATEGORY 10, CATEGORY_NAME, PARENT_CATEGORY_ID, PARENT_CATEGORY_NAME, SUPPLIER_ID Display the entire content of the T_PRODUCT table. (You should get as many rows as in the PRODUCT_COPY table) In which Normal form is T_PRODUCT? Justify your answer. Note that you have prepared the denormalized table T_PRODUCT to load a DW PRODUCT dimension. 3. Using the Multidimensional model concepts/Terminology, what are the Analysis-level columns and the Weak-attributes in T_PRODUCT? Part II. Split table 4. Use the CREATE and SELECT statements to split the OE ORDERS table into two sub-tables you call: A ORDERS_DIRECT (ORDER_ID, ORDER_YEAR, ORDER_MODE, SALES_REP_ID) containing rows such as ORDER_MODE - 'DIRECT and having NOT NULL SALES_REP_ID (Use TO_CHAR, TO_NUMBER functions) a. Display the table content. You should get 70 rows. b. Check that the ORDER_MODE values are correct regarding the condition in the 'query Where! c Remove the ORDER_MODE column from ORDERS_DIRECT. B. ORDERS_ONLINE (Same columns as ORDERS_DIRECT) containing rows such as ORDER_MODE - ONLINE a. Display the table content. You should get 32 rows. b. Check that the ORDER_MODE values are correct regarding the condition in the 'query- Where! CCOS221 DW Labs 1 to 3.STO.pd 701 2. Use the CREATE and SELECT us to Deemaldabled_PRODUCT Dating PRODUCT_COPY and CATEGORY_COPY PRODUCTS PRODUCT MODUCT NAME CATEGORY CATEGORY_NAME PARENT CATEGORY_O PARENT CATEGORY NAME Distry the entire content of the URODUCT te should get you in the PRODUCT_COPY in which malformat PRODUCT Note that you have prepared the denormaludable PODUCT toont a DW PRODUCT anemon Ung the Muminat model concepts/Termine what they can he Wettbutes TOUT Use the CREATE and SELECT tatements to the State you at A ORDERS DIRECT ORDER TO ORDER_YEAR ORDER MOOE SALES, Etanie rosh ORDER MODE DIRECT NOT NULL SALES REPOUTO_CHAR TO NUMBER functions Duplay the title coment. You should Check that the ORDER_MODE come in the Remove the ORDEMODE come from ORDERS RECT & ORDERS ONLINE Same ORDES ORECT ORDER MODE ONLINE * Display the table content. You should Check that the ONDERMODE come the condition in the CCOS221 DW Labs 1 70 3 STD. CCDS-221 Data Warehouse Temove the ORDER MODE and SALES_20_ORDERS_ONLINE Optional question In the same way it the CUSTOMERS tablettes having the mettres CUSTOMERS.net for Male GENDER) for GENDER Part. Megetables Merger is the opposite of the NION. The state.com Le love the same number of columns with completo Create to compatible tables sued from the tablety contents . - 4 Lah Summary, Desorbe the main tasks you memented and the end 10:00 PM Mon 8 Feb @ 100% - CCDS221 DW Labs 1 TO 3_STD.pdf CCDS-221 Data Warehouse Lab #3 ETL Process Denormalization, Merging, and Splitting of Tables Statement purpose ROLAP (On-Line Analytical Processing) technology implements the dimensional tables of the multidimensional schema as Denormalized (just in 1NF) tables in order to speed up the processing of analytical queries (avoiding multiple JOINS). This work will allow you to prepare (in the staging area) denormalized tables by joining normalized tables, split a table into sub-tables and Merge the result of several queries/tables Activity Outcomes Students will be able to join tables to build denormalized tables in the staging area, split and Merge tables. Instructor Note Implement all the questions below and submit, for each one, the code and its execution result (as a screenshot) displayed by Oracle Use the E Database schema available at the link https://ivesal.oracle.com to answer. Note: The SELECT privilege is GRANTed to PUBLIC on the tables so that you prefix cach table of this DB by its creator (OE) in SQL statements (You can also create a synonym). You need the tables PRODUCT_INFORMATION, CATEGORY_TAB, and CUSTOMERS. Sample contents of these tables are in Appendix 1. Answer the following questions, Part I. Denormalization Consult tables PRODUCT_INFORMATION and CATEGORY TAB to understand their structure and content 10:01 PM Mon 8 Feb @ 100% - 7 of 13 CCDS221 DW Labs 1 To 3_STD.pdf Part I. Denormalization Consult tables PRODUCT_INFORMATION and CATEGORY_TAB to understand their structure and content. CATEGORIES_TAB (CATEGORY_NAME, CATEGORY_DESCRIPTION, CATEGORY_ID, PARENT_CATEGORY_ID) PRODUCT_INFORMATION (PRODUCT_ID, PRODUCT_NAME... CATEGORY_ID SUPPLIER_ID..) Note the existence of two relationships: each product has a CATEGORY_ID that exists in the CATEGORY_TAB table, and each category has a parent category (PARENT_CATEGORY_ID). Academic Year 2020/2021 University of Jeddah College of Computer Science & Engineering IST Dept Term 11 CCDS-221 Data Warehouse 1. CREATE the following tables: PRODUCT_COPY (PRODUCT_ID, PRODUCT_NAME, CATEGORY_ID, SUPPLIER_ID) by SELECTing rows from the OE.PRODUCT_INFORMATION table. CATEGORY_COPY by SELECTing rows from the OE CATEGORY_TAB table. 2. Use the CREATE and SELECT statements to create a Denormalized table called T_PRODUCT by natural-joining PRODUCT_COPY and CATEGORY_COPY. T_PRODUCT has the columns: PRODUCT_ID, PRODUCT_NAMES 10:01 PM Mon 8 Feb @ 100% CCDS221 DW Labs 1 TO 3_STD.pdf 7 of 13 2. Use the CREATE and SELECT statements to create a Denormalized table called T_PRODUCT by natural-joining PRODUCT_COPY and CATEGORY_COPY. T_PRODUCT has the columns PRODUCT ID, PRODUCT NAME CATEGORY 10, CATEGORY_NAME, PARENT_CATEGORY_ID, PARENT_CATEGORY_NAME, SUPPLIER_ID Display the entire content of the T_PRODUCT table. (You should get as many rows as in the PRODUCT_COPY table) In which Normal form is T_PRODUCT? Justify your answer. Note that you have prepared the denormalized table T_PRODUCT to load a DW PRODUCT dimension. 3. Using the Multidimensional model concepts/Terminology, what are the Analysis-level columns and the Weak-attributes in T_PRODUCT? Part II. Split table 4. Use the CREATE and SELECT statements to split the OE ORDERS table into two sub-tables you call: A ORDERS_DIRECT (ORDER_ID, ORDER_YEAR, ORDER_MODE, SALES_REP_ID) containing rows such as ORDER_MODE - 'DIRECT and having NOT NULL SALES_REP_ID (Use TO_CHAR, TO_NUMBER functions) a. Display the table content. You should get 70 rows. b. Check that the ORDER_MODE values are correct regarding the condition in the 'query Where! c Remove the ORDER_MODE column from ORDERS_DIRECT. B. ORDERS_ONLINE (Same columns as ORDERS_DIRECT) containing rows such as ORDER_MODE - ONLINE a. Display the table content. You should get 32 rows. b. Check that the ORDER_MODE values are correct regarding the condition in the 'query- Where