CS614 Midterm Short Notes

CS614 Short Notes-mid-term/ Short Questions Answers

By SONU ILYAS MUGHAL

Write a quarry to extract total number of female students registered in BS Telecom. 5 marks Total Number of Female students in BS Telecom SELECT COUNT (DISTINCT r.SID) AS Expr1 FROM Registration r INNER JOIN Student s ON r.SID = s.SID AND s.[Last Degree] IN ('F.Sc.', 'FSc', 'HSSC', 'A-Level', 'A level') AND r.Discipline = 'TC' AND s.Gender = '1„

Describe the lessons learn at during agri-data ware house case study?

•  Extract Transform Load (ETL) of agricultural extension data is a big issue. There are no digitized operational databases so one has to resort to data available in typed (or hand written) pest scouting sheets. Data entry of these sheets is very expensive, slow and prone to errors.
•  Particular to the pest scouting data, each farmer is repeatedly visited by agriculture extension people. This results in repetition of information, about land, sowing date, variety etc (Table-

2. . Hence, farmer and land individualization are critical, so that repetition may not impair aggregate queries. Such an individualization task is hard to implement for multiple reasons.

•  There is a skewness in the scouting data. Public extension personnel (scouts) are more likely to visit educated or progressive farmers, as it makes their job of data collection easy. Furthermore, large land owners and influential farmers are also more frequently visited by the scouts. Thus the data does not give a true statistical picture of the farmer demographics.
•  Unlike traditional data warehouse where the end users are decision makers, here the decision-making goes all the way “down” to the extension level. This presents a challenge to the analytical operations‟ designer, as the findings must be fairly simple to understand and communicate.

What are the fundamental strengths and weakness of k means clustering?

Strength

•  Relatively efficient: O (tkn), where n is # objects, k is # clusters, and t is # iterations. Normally, k, t << n.
•  Often terminates at a local optimum. The global optimum may be found using techniques such as: deterministic annealing and genetic algorithms

Weakness

 Applicable only when mean is defined, then what about categorical data?

•  Need to specify k, the number of clusters, in advance
•  Unable to handle noisy data and outliers

Data profiling is a process of gathering information about columns, what are the purpose that it must fulfill? Describe briefly identify the following:

•  Total number of values in a column
•  Number of distinct values in a column
•  Domain of a column
•  Values out of domain of a column
•  Validation of business rules

We run different SQL queries to get the answers of above questions. During this process we can identify the erroneous records. Whenever we will come across an erroneous record, we will just copy it in error or exception table and set the dirty bit of record in the actual student table. Then we will correct the exception table. After this profiling process we will transform the records and load them into a new table Student_Info

Define additive and non additive facts

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.

What are three fundamental reasons for warehousing web data?

1. Searching the web (web mining).
2. Analyzing web traffic.
3. Archiving the web.

First, web warehousing can be used to mine the huge web content for searching information of interest. It‟s like searching the golden needle from the haystack. Second reason of Web warehousing is to analyze the huge web traffic. This can be of interest to Web Site owners, for e-commerce, for e-advertisement and so on. Last but not least reason of Web warehousing is to archive the huge web content because of its dynamic nature.

What are the two basic data warehousing implementation strategies and their suitability conditions?

Top Down & Bottom Up approach: A Top Down approach is generally useful for projects where the technology is mature and well understood, as well as where the business problems that must be solved are clear and well understood. A Bottom Up approach is useful, on the other hand, in making technology assessments and is a good technique for organizations that are not leading edge technology implementers. This approach is used when the business objectives that are to be met by the data warehouse are unclear, or when the current or proposed business process will be affected by the data warehouse.

Bitmap Indexes: Concept

•  Index on a particular column
•  Index consists of a number of bit vectors or bitmaps
•  Each value in the indexed column has a corresponding bit vector (bitmaps)
•  The length of the bit vector is the number of records in the base table
•  The ith bit is set to 1 if the ith row of the base table has the value for the indexed column

List and explain fundamental advantages of bit map indexing

Bitmap Index: Adv.

•  Very low storage space.
•  Reduction in I/O, just using index.
•  Counts & Joins
•  Low level bit operations. An obvious advantage of this technique is the potential for dramatic reductions in storage overhead. Consider a table with a million rows and four distinct values with column header of 4 bytes resulting in 4 MB. A bitmap indicating which of these rows are for these values requires about 500KB. More importantly, the reduction in the size of index "entries" means that the index can sometimes be processed with no I/O and, more often, with substantially less I/O than would otherwise be required. In addition, many index-only queries (queries whose responses are derivable through index scans without searching the database) can benefit considerably. Database retrievals using a bitmap index can be more flexible and powerful than a B-tree in that a bitmap can quickly obtain a count by inspecting only the index, without retrieving the actual data. Bitmap indexing can also use multiple columns in combination for a given retrieval. Finally, you can use low-level Boolean logic operations at the bit level to perform predicate evaluation at increased machine speeds. Of course, the combination of these factors can result in better query performance.

List and explain fundamental disadvantages of bit map indexing

Bitmap Index: Dis. Adv.

•  Locking of many rows
•  Low cardinality
•  Keyword parsing
•  Difficult to maintain - need reorganization when relation sizes change (new bitmaps) Row locking: A potential drawback of bitmaps involves locking. Because a page in a bitmap contains references to so many rows, changes to a single row inhibit concurrent access for all other referenced rows in the index on that page. Low cardinality: Bitmap indexes create tables that contain a cell for each row times each possible value (the product of the number of rows times the number of unique values). Therefore, a bitmap is practical only for low- cardinality columns that divide the data into a small number of categories, such as "M/F", "T/F", or "Y/N" values. Keyword parsing: Bitmap indexes can parse multiple values in a column into separate keywords. For example, the title “Marry had a little lamb" could be retrieved by entering the word “Marry" or “lamb" or a combination. Although this keyword parsing and lookup capability is extremely useful, textual fields tend to contain high-cardinality data (a large number of values) and therefore are not a good choice for bitmap indexes.