BITS WILP Data Warehousing Quiz-3 2017-H2

---

Q1: 7. Which of the following is the remedy that needs to be applied to the data warehouse storing data at lowest level of granularity, so that the queries requiring summary data run faster (if memory is available in plenty)? Select one: a. Indexing b. Aggregation c. Clustering d. Partitioning Answer: Aggregation

Q2. 5. Which of the following is not an advantage of B-Tree Indexing Select one: a. Good speed of data retrieval b. Simplicity c. Works well with data with low selectivity d. Ease of maintenance  According to the above screen, B-tree indexing does not work well with ‘low selectivity’. URL: https://www.akadia.com/services/ora_index_selectivity.html Example with good Selectivity A table having 100'000 records and one of its indexed column has 88000 distinct values, then the selectivity of this index is 88'000 / 10'0000 = 0.88. Oracle implicitly creates indexes on the columns of all unique and primary keys that you define with integrity constraints. These indexes are the most selective and the most effective in optimizing performance. The selectivity of an index is the percentage of rows in a table having the same value for the indexed column. An index's selectivity is good if few rows have the same value. How to Measure Index Selectivity ? Manually measure index selectivity The ratio of the number of distinct values to the total number of rows is the selectivity of the columns. This method is useful to estimate the selectivity of an index before creating it. select count (distinct job) "Distinct Values" from emp; Distinct Values ---------------               5 select count(*) "Total Number Rows" from emp; Total Number Rows -----------------                14 Selectivity = Distinct Values / Total Number Rows             = 5 / 14             = 0.35

Q3. 4. Match the following: 1) ROLAP        a) It makes use of Multidimensional Databases 2) MOLAP        b) It makes use of Relational Databases 3) HOLAP        c) It provides portability to the users 4) DOLAP        d) It makes use of both Relational and Multidimensional databases Choose the correct option Select one: a. 1-b 2-a 3-d 4-c b. 1-c 2-b 3-a 4-d c. 1-b 2-c 3-a 4-d d. 1-c 2-d 3-b 4-a Answer: 1B, 2A, 3D so option A.

Q4. 1. Which of the following are the characteristic of OLAP. a)Contains current and historical data b)Generally read only c)Very low analytical capabilities d)Optimization of database for analysis Choose the correct option Select one: a. Only a & b b. Only c & d c. All d. Only a, b & d Answer: A, B, D So, option (D)

Q5. 2.Match the following: 1) B-tree Index        a) Data address in each entry 2) Bitmapped Index    b) Combined data and index segments 3) Clustered Index    c) Data address in each leaf node Choose the correct option Select one: a. 1-c 2-a 3-b b. 1-b 2-c 3-a c. 1-c 2-b 3-b d. 1-b 2-c 3-a Answer: 1C: B-tree indexes have ‘data address in leaf nodes’ 2A: Bit-mapped indexes have ‘data address in each entry’

Q6. 6. Deliberate splitting of a large table and its index data into manageable parts is called ______________ Select one: a. Indexing b. Partitioning c. Aggregation d. Clustering Answer: Partitioning (Option: B)

Q7. 8. Which below operation is the viewing of cross-tab (Single dimensional) with a fixed value of one attribute Select one: a. Slicing b. Pivoting c. Dicing d. Both A and B Answer: Slicing (Option: A)

Q8. 9. The operation of moving from coarser-granularity data to a finer-granularity is called as ________. Select one: a. Drill down b. Rollup c. Pivoting d. Dicing Answer Drill down (Option A) Definition: slice and dice To slice and dice is to break a body of information down into smaller parts or to examine it from different viewpoints so that you can understand it better. In cooking, you can slice a vegetable or other food or you can dice it (which means to break it down into small cubes). One approach to dicing is to first slice and then cut the slices up into dices. In data analysis, the term generally implies a systematic reduction of a body of data into smaller parts or views that will yield more information. The term is also used to mean the presentation of information in a variety of different and useful ways. This term can be compared to drilldown , which is the process of dividing an information area up into finer and finer layers in a hierarchy, but with the purpose of narrowing in to one small area or item. URL: http://whatis.techtarget.com/definition/slice-and-dice

Q9. 10. Which kind of partitioning will result in same number of columns in each partition but may have different number of rows. Select one: a. Horizontal b. None of the above c. Vertical d. Both a and b Answer: Horizontal partitioning (Option A) URL: https://www.tutorialspoint.com/dwh/dwh_partitioning_strategy.htm Vertical Partition Vertical partitioning, splits the data vertically. The following images depicts how vertical partitioning is done. Vertical partitioning can be performed in the following two ways − ·         Normalization ·         Row Splitting

Q10. 3. Which of the following is the correct control flow in the case of ROLAP? Notation: Analytical Server (AS), Desktop Client (DC), RDBMS Server (RS) Select one: a. RS -> DC -> AS -> DC -> RS b. DC -> RS -> AS -> RS -> DC c. DC -> AS -> RS -> AS -> DC d. None of the Options Answer: Request goes from DC to AS for conversion into SQL queries and then to RS. RS sends relational data to AS which then puts in the form of a chart and sends it to DC.