CS614 Midterm Solved Subjective with References by Moaaz

Solved Subjective · From Midterm Papers · 1st DEC,2012 · PSMD01 · SPRING 2012

Q1. How aggregates awareness helps the users (2 marks)

Answer: (Page 87) Aggregate awareness allows using pre-built summary tables by some front-end tools. It is smart enough to develop or compute higher level aggregates using lower level or more detailed aggregates.

Q2. Diff b\w one to one and one to many transformation (2 marks)

Answer: (Page 144) Simple scalar transformation is a one-to-one mapping from one set of values to another set of values using straightforward rules.

source system results in several columns in the DW.

Q3. How cube is created in ROLAP (3 marks)

Answer: (Page 88) Cube is a logical entity containing values of a certain fact at a certain aggregation level at an intersection of a combination of dimensions.

Q4. What is timestamps (3 marks)

Answer: (Page 150) The tables in some operational systems have timestamp columns. The timestamp specifies the time and date that a given row was last modified. If the tables in an operational system have columns containing timestamps, then the latest data can easily be identified using the timestamp columns.

Q5. factors behind poor data Quality (5 marks)

Answer: (Page 139) Data warehousing fundamentals

Data Pollution Sources

• System conversions and migrations
• Heterogeneous systems integration
• Inadequate database design of source systems
• Data aging
• Incomplete information from customers
• Input errors
• Internationalization/localization of systems
• Lack of data management policies/procedures

Types of Data Quality Problems

• Dummy values in source system fields
• Absence of data in source system fields
• Multipurpose fields
• Cryptic data
• Contradicting data
• Improper use of name and address lines
• Violation of business rules
• Reused primary keys
• Nonunique identifiers

6) Which denormalization technique used to reduce foreign keys in a relation 5 marks[/i]

Answer: (Page 53) Collapsing Tables technique used to reduce foreign keys in a relation. In general, collapsing tables in One-toOne relationship has fewer drawbacks than others. There are several advantages of this technique, some of the obvious ones being reduced storage space, reduced amount of time for data update, some of the other not so apparent advantages are reduced number of foreign keys on tables, reduced number of indexes (since most indexes are created based on primary/foreign keys).

Q1. Difference between Low granular and high granular (2)

Answer: (Page 47) Data warehousing fundamentals Data granularity in a data warehouse refers to the level of detail. The lower the level of detail, the finer the data granularity. Of course, if you want to keep data in the lowest level of detail, you have to store a lot of data in the data warehouse. You will have to decide on the granularity levels based on the data types and the expected system performance for queries.

Q2. Difference between one to one and one to many transformation (2)

Answer: Rep

Q3. Describe reason to summarization during data transformation (3)

Answer: (Page 136) Sometimes you may find that it is not feasible to keep data at the lowest level of detail in your data warehouse. It may be that none of your users ever need data at the lowest granularity for analysis or querying. For example, for a grocery chain, sales data at the lowest level of detail for every transaction at the checkout may not be needed. Storing sales by product by store by day in the data warehouse may be quite adequate. So, in this case, the data transformation function includes summarization of daily sales by product and by store.

Q4. How clustering and associative rule work (3)

Answer: (Page 264) Clustering is the technique of reshuffling, relocating exiting segments in given data which is mostly heterogeneous so that the new segments have more homogeneous data items. This can be very easily understood by a simple example. Suppose some items have been segmented on the basis of color in the given data. Suppose the items are fruits, then the green segment may contain all green fruits like apple, grapes etc. thus a heterogeneous mixture of items. Clustering segregates such items and brings all apples in one segment or cluster although it may contain apples of different colors red, green, yellow etc. thus a more homogeneous cluster than the previous cluster.

Q5. Splitting of single field transformation is used to store individual components of name and addres in separate field of data warehousing, main reason to doing this (5)

Answer: (Page 154) You need to store individual components of names and addresses in separate fields in your data warehouse for two reasons. First, you may improve the operating performance by indexing on individual components. Second, your users may need to perform analysis by using individual components such as road, sector, city etc.

Q6. explain additive and non additive and examples (5).

Answer: (Page 119) There can be two types of facts i.e. additive and non-additive. Additive facts are those facts which give the correct result by an addition operation. Examples of such facts could be number of items sold, sales amount etc. Non-additive facts can also be added, but the addition gives incorrect results. Some examples of non-additive facts are average, discount, ratios etc. Consider three instances of 5, with the sum being 15 and average being 5. Now consider two numbers i.e. 5 and 10, the sum being 15, but the average being 7.5. Now, if the average of 5 and 7.5 is taken this comes to be 6.25, but if the average of the actual numbers is taken, the sum comes to be 30 and the average being 6. Hence averages, if added gives wrong results. Now facts could be averages, such as average sales per week etc, thus they are perfectly legitimate facts.