1 Kingdom of Saudi Arabia Ministry of higher education Princess Norah Bent Abdul Rahman University Information technology department Data Warehouse and Data Mining IS 322 D Assignment #1 Submission Rules: • Assignment should be done in groups of 3 students. • Submission location:  Hard copy at classroom: during lecture time on Tuesday (11/4/2017).  Soft copy by Blackboard on Monday (10/4/2017 before 11 P.M.). • No submission after your lecture time. • Each group submit only one copy. • The cover page should include your name, Id, class, and assignment's number. • All parties involved in cheating will be prosecuted no matter who copied from whom, it's your duty to keep your assignment secure.2 Question 1 The ER diagram below show an operational system of a retail sales business. a. Create a complete information package diagram for the information given in the schema. b. Create a star schema for the information showing all dimension tables, fact table, attributes, and the relationships between the tables. Identify key attributes and relationship cardinalities. c. Assume that the sales representative named Smith works for a sales region in Las Vegas. After having worked for ten years in Las Vegas, the store is changed to New York. What type of update is this? How would you update the Employee dimension table? Show the new dimension table and its attributes.3 Question 2: Look to the following table, and then answer the question below: Country Month Product Quantity Riyadh Jan P1 2000 Jeddah Jan P1 1500 Abu Dhabi Feb P2 1000 Riyadh Jan P1 6000 Dubai Feb P2 7000 Abu Dhabi Jan P1 1300 Dubai Jan P2 4000 Riyadh Feb P1 14000 Jeddah Mar P2 20000 Jeddah Jan P2 5000 Dubai Mar P2 10000 a. Representation the following table using of Multi-Dimensional structure. Hint: it is not necessary to draw the cube, just show the content for each slice as a slice within a cube. b. Roll up the table above using location dimension, country instead of city. c. Aggregate the table above using the following queries : (show the result)  Select Country , Month ,sum(Quantity) From sales_table Group by Month, Country  Select Month, MAX(Quantity) From Sales_table Group by time4 Question 3: Answer the following queries by showing the result for each dimension in the result cube. (Show the display of the query as how it will affect the rows, columns, and pages) a. Display the total sales of all items for the Quarter Q2 in all cities. b. Display the total sales of Mobile, Modem and Phone in Chicago and Toronto for Q1 and Q2. c. Show the comparison of all sales for Chicago, New York and Vancouver item by item in Q3, only for those items with increased sales. d. Show the result of the first page (Vancouver) after rolling up items one level up, knowing that Mobile and Phone are from category X, Modem and Security are from category Y. .