1.Explain Cluster analysis?
Cluster analysis or clustering is the assignment of objects into groups (called clusters) so that objects from the same cluster are more similar to each other than objects from different clusters. Often similarity is assessed according to a distance measure. Clustering is a common technique for statistical data analysis, which is used in many fields, including machine learning, data mining, pattern recognition, image analysis and bioinformatics. Besides the term data clustering (or just clustering), there are a number of terms with similar meanings, including cluster analysis, automatic classification, numerical taxonomy, botryology and typological analysis.
2.Discuss different types of clustering techniques?
Data clustering algorithms can be hierarchical. Hierarchical algorithms find successive clusters using previously established clusters. Hierarchical algorithms can be agglomerative ("bottom-up") or divisive ("top-down"). Agglomerative algorithms begin with each element as a separate cluster and merge them into successively larger clusters. Divisive algorithms begin with the whole set and proceed to divide it into successively smaller clusters. Partitional algorithms typically determine all clusters at once, but can also be used as divisive algorithms in the hierarchical clustering. Density-based clustering algorithms are devised to discover arbitrary-shaped clusters. In this approach, a cluster is regarded as a region in which the density of data objects exceeds a threshold. DBSCAN and OPTICS are two typical algorithms of this kind.
Two-way clustering, co-clustering or biclustering are clustering methods where not only the objects are clustered but also the features of the objects, i.e., if the data is represented in a data matrix, the rows and columns are clustered simultaneously.
Another important distinction is whether the clustering uses symmetric or asymmetric distances. A property of Euclidean space is that distances are symmetric (the distance from object A to B is the same as the distance from B to A). In other applications (e.g., sequence-alignment methods, see Prinzie & Van den Poel (2006)), this is not the case.
Many clustering algorithms require specification of the number of clusters to produce in the input data set, prior to execution of the algorithm. Barring knowledge of the proper value beforehand, the appropriate value must be determined, a problem for which a number of techniques have been developed.
3. Explain Partitioning Around Medoids (Pam) algorithm.
Compared to the well - known kmeans algorithm, .Pam has the following features:
It operates on the dissimilarity matrix of the given data set or when it is presented with an n ´ p data matrix, the algorithm first computes a dissimilarity matrix.
It is more robust, because it minimizes a sum of dissimilarities instead of a sum of squared Euclidean distances.
It provides a novel graphical display, the silhouette plot, which allows the user to select the optimal number of clusters.
In many clustering problems, one is interested in the characterization of the clusters by means of typical objects, which represent the various structural features of objects under investigation. The algorithm Pam first computes k representative objects, called medoids. A medoid can be defined as that object of a cluster, whose average dissimilarity to all the objects in the cluster is minimal. In the classification literature, such representative objects are called centrotypes. After finding the set of medoids, each object of the data set is assigned to the nearest medoid. That is, object i is put into cluster vi, when medoid mvi is nearer than any other medoid mw.
d(i, mvi ) £ d (i,m w ) for all w = 1,..., k.
The k representative objects should minimize the objective function, which is the sum of the dissimilarities of all objects to their nearest medoid:
Objective function = S d(i, mvI)
The algorithm proceeds in two steps:
BUILD-step: This step sequentially selects k "centrally located" objects, to be used as initial medoids
SWAP-step: If the objective function can be reduced by interchanging (swapping) a selected object with an unselected object, then the swap is carried out. This is continued till the objective function can no longer be decreased.
Isolated clusters : The program considers two types of isolated clusters. L-clusters and L* clusters. Cluster C is an L-cluster, if for each object i belonging to C:
max dij < min dih, j C h C
Cluster C is an L* - cluster, if
max dij < min dlh, ij C, l C,h C
It is clear that L* - clusters are also L-clusters. It should be noted that the property of being isolated depends on the internal structure of a cluster as well as on its position relative to other clusters.
Diameter of a cluster
The diameter of Cluster C is defined as the largest dissimilarity between objects belonging to the Cluster C.
DiameterC = max dij, i,j C
Separation of a cluster
The separation of Cluster C is defined as the smallest dissimilarity between two objects; one of which belong to Cluster C and the other does not.
SeparationC = min dlh, l C, h C
Average distance to a medoid
If j is the medoid of Cluster C, the average distance of all objects of C to j is calculated as follows:
Average distancej =
where N is the number of object minus other than j.
Maximum distance to medoid
If object j is the medoid of cluster C, the maximum distance of objects of C to j is calculated as follows:
Maximum distancej = max dij , i C
Graphical display
Two of the most difficult tasks in cluster analysis are: How to decide the appropriate number of clusters and how to distinguish a bad cluster from a good one. The module Clusfind computes a set of values called silhouettes that provide key information about these tasks. First, we will explain how these are calculated and then we will show how they are used
Each cluster is represented by one silhouette, showing which objects lie within the cluster and which objects merely hold an intermediate position. The entire clustering is displayed by plotting all silhouettes into a single diagram, from which the quality of the clusters can be compared.
Silhouettes are constructed in the following way. Consider any object i of the data set, and let A denote the cluster to which it is assigned, and then calculate
a ( i ) = average dissimilarity of i to all other objects of A
Now consider any cluster C different from A and define
d ( i, C ) = average dissimilarity of i to all objects of C
Compute d ( i, C ) for all clusters C A, and then select the smallest of those
b = min d ( i,C ) , C A
Let B denote the cluster which attains the minimum i.e., d ( i,B ) = b ( i ) is called the neighbour of object i. The value s ( i ) can now be defined as:
s(i) =
Outlier Analysis:
l Working definition
– An outlier xk is an element of a data sequence S that is inconsistent with out expectations, based on the majority of other elements of S.
l Sources of outliers
– Measurement errors
– Other uninteresting anomalous data
l valid data observations made under anomalous conditions
– Surprising observations that may be important
Identifying Outliers
The 3s - edit rule
– If x ~ N(m, s2), then P(x-m>3s) » 0.003
– estimate mean x
– estimate standard deviation s’
– compute zk = (xk - x)/s’
– if zk > t, then xk is an outlier
4. Explain outlier analysis with illustration.
Rare, unusual, or just plain infrequent events are of interest in data mining in many contexts including fraud in income tax, insurance, and online banking, as well as for marketing. We classify analyses that focus on the discovery of such data items as outlier analysis. () captures the concept of an outlier as:
an observation that deviates so much from other observations as to arouse suspicion that it was generated by a different mechanism.
Outlier detection algorithms often fall into one of the categories of distance-based methods, density-based methods, projection-based methods, and distribution-based methods.
A general approach to identifying outliers is to assume a known distribution for the data and to examine the deviation of individuals from the distribution. Such approaches are common in statistics (, ) but such approaches do not scale well.
Distance based methods are common in data mining where the measure of an entities outliedness is based on its distance to nearby entities. The number of nearby entities and the minimum distance are two parameters. (see knorr and ng 1998 vldb24)
Density based approaches from breuning kriegel ng and sander 2000 sigmod LOF: local outlier factor. See also jin tung and han kdd2001.
The early work on outliers was carried out from a statistical view point where outliers are data points that deviate significantly from the identified underlying distribution of the data. () is a good textbook on outliers. () is another overview of statistical outliers.
Distance based approaches have been developed by (), () and (). Such approaches usually explore some neighbourhood and do not rely on underlying distributions.
() identify outliers by counting the number of neighbours within a specified radius of a data point. The radius and the threshold number of points are the only two parameters of the approach. The approach is simple but is inadequate for data that is distributed with uneven density where and might need to vary to cope with the changes. () have a similar approach whereby data points are ranked by the sum of their distance to their nearest neighbours.
() and then () introduce a density based approach to score data points with a local outlier factor (LOF). () introduce a heuristic to more efficiently identify the top outliers using the LOF.
() build mixture models as data becomes available and identifies outliers as those data items causing the most perturbation to the model.
() explore the issue of outliers in high dimensional space where data tends to be sparse and consequently all data points tend to be equidistant to other points (, ) and suggest an algorithm where the high dimensional space is projected to a lower dimensional space having unusually low density. An evolutionary algorithm is proposed to generate candidate subspaces in which outliers are to be searched for.
5.Explain K-Means and K-Medoids clustering algorithms?
The most common algorithm uses an iterative refinement technique. Due to its ubiquity it is often called the k-means algorithm; it is also referred to as Lloyd's algorithm, particularly in the computer science community.
Given an initial set of k means m1(1),…,mk(1), which may be specified randomly or by some heuristic, the algorithm proceeds by alternating between two steps:
Assignment step: Assign each observation to the cluster with the closest mean (i.e. partition the observations according to the Voronoi diagram generated by the means).
Si = {xj xj – mi(t) <= xj – mi(t) for all i* = 1, ….., k}
Update step: Calculate the new means to be the centroid of the observations in the cluster.
Mi (t+1) = i/ si(t)
The algorithm is deemed to have converged when the assignments no longer change.
The k-medoids algorithm is a clustering algorithm related to the k-means algorithm and the medoidshift algorithm. Both the k-means and k-medoids algorithms are partitional (breaking the dataset up into groups) and both attempt to minimize squared error, the distance between points labeled to be in a cluster and a point designated as the center of that cluster. In contrast to the k-means algorithm k-medoids chooses datapoints as centers (medoids or exemplars).
k-medoid is a classical partitioning technique of clustering that clusters the data set of n objects into k clusters known a priori. A useful tool for determining k is the silhouette.
It is more robust to noise and outliers as compared to k-means.
A medoid can be defined as that object of a cluster, whose average dissimilarity to all the objects in the cluster is minimal i.e. it is a most centrally located point in the given data set.
The most common realization of k-medoid clustering is the Partitioning Around Medoids (PAM) algorithm and is as follows:
The algorithm begins with arbitrary selection of the k objects as medoid points out of n data points (n>k)
After selection of the k medoid points, associate each data object in the given data set to most similar medoid. The similarity here is defined using distance measure that can be Euclidean distance, Manhattan distance or Minkowski distance
Randomly select nonmedoid object O′
compute total cost S of swapping initial medoid object to O′
If S<0, then swap initial medoid with the new one (if S<0 then there will be new set of medoids)
repeat steps 2 to 5 until there is no change in the medoid.
6.Explain Grid – based methods.
Grid based method consist of a gird structure formed by quantifying the object space into a finite number of cells. It is an approach of representing data object using a multi-resolution grid data structure on which all of clustering operations are performed. The benefit of grid-based methods is that it processed operations quickly without taking much time, as it depends only on the number of cells in each dimensions in the quantized space and does not rely on the number of data objects.
Grid – based approach are,
a) wave cluster : a wavelet transform is used to cluster object
b) STING : the grid cells contain statistical information which is reviewed by STING
c) CLIQUE : it is used for clustering in high-dimensional data region.
7.Discuss model based clustering methods.
Model based methods build a cluster on the basis of a model that receives the best data among others. A density function is built by model based algorithms to locate clusters. The density function defines the spatial distribution of the data points. On the basis of statistics which include noise or outliers model based algorithms can automatically find the number of clusters, which powerful clustering methods. We select clustering algorithms on the basis of The particular purpose of the application Data available.
Model based clustering are:
a) COBWEB
b) Expectation maximization (EM)
c) SOM
8.Discuss briefly Agglomerative and Divisive analysis
Agglomerative Clustering : Initially it starts with every object forming a separate group and then successively combining the objects or groups that are near to one another or until end condition is satisfied. This method is also referred as ‘bottom-up’ approach.
Divisive Clustering
Initially it starts with entire objects in a single cluster and then on successive iterations a cluster is broken down into smaller clusters until each cluster has single object or until end condition is satisfied. This method is also referred as ‘top-down’ approach.
9.Explain Partitional Methods
k-means clustering
The k-means algorithm assigns each point to the cluster whose center (also called centroid) is nearest. The center is the average of all the points in the cluster — that is, its coordinates are the arithmetic mean for each dimension separately over all the points in the cluster.
Example: The data set has three dimensions and the cluster has two points: X = (x1, x2, x3) and Y = (y1, y2, y3). Then the centroid Z becomes Z = (z1, z2, z3), where z1 = (x1 + y1)/2 and z2 = (x2 + y2)/2 and z3 = (x3 + y3)/2.
The algorithm steps are:
Choose the number of clusters, k.
Randomly generate k clusters and determine the cluster centers, or directly generate k random points as cluster centers.
Assign each point to the nearest cluster center.
Recompute the new cluster centers.
Repeat the two previous steps until some convergence criterion is met (usually that the assignment hasn't changed).
The main advantages of this algorithm are its simplicity and speed which allows it to run on large datasets. Its disadvantage is that it does not yield the same result with each run, since the resulting clusters depend on the initial random assignments. It minimizes intra-cluster variance, but does not ensure that the result has a global minimum of variance. Another disadvantage is the requirement for the concept of a mean to be definable which is not always the case. For such datasets the k-medoids variant is appropriate.
Fuzzy c-means clustering
In fuzzy clustering, each point has a degree of belonging to clusters, as in fuzzy logic, rather than belonging completely to just one cluster. Thus, points on the edge of a cluster, may be in the cluster to a lesser degree than points in the center of cluster. For each point x we have a coefficient giving the degree of being in the kth cluster uk(x). Usually, the sum of those coefficients for any given x is defined to be 1:
With fuzzy c-means, the centroid of a cluster is the mean of all points, weighted by their degree of belonging to the cluster:
The degree of belonging is related to the inverse of the distance to the cluster center:
then the coefficients are normalized and fuzzyfied with a real parameter m > 1 so that their sum is 1. So
For m equal to 2, this is equivalent to normalising the coefficient linearly to make their sum 1. When m is close to 1, then cluster center closest to the point is given much more weight than the others, and the algorithm is similar to k-means.
The fuzzy c-means algorithm is very similar to the k-means algorithm:
Choose a number of clusters.
Assign randomly to each point coefficients for being in the clusters.
Repeat until the algorithm has converged (that is, the coefficients' change between two iterations is no more than ε, the given sensitivity threshold) :
Compute the centroid for each cluster, using the formula above.
For each point, compute its coefficients of being in the clusters, using the formula above.
The algorithm minimizes intra-cluster variance as well, but has the same problems as k-means, the minimum is a local minimum, and the results depend on the initial choice of weights. The Expectation-maximization algorithm is a more statistically formalized method which includes some of these ideas: partial membership in classes. It has better convergence properties and is in general preferred to fuzzy-c-means.
QT clustering algorithm
QT (quality threshold) clustering (Heyer, Kruglyak, Yooseph, 1999) is an alternative method of partitioning data, invented for gene clustering. It requires more computing power than k-means, but does not require specifying the number of clusters a priori, and always returns the same result when run several times.
The algorithm is:
The user chooses a maximum diameter for clusters.
Build a candidate cluster for each point by including the closest point, the next closest, and so on, until the diameter of the cluster surpasses the threshold.
Save the candidate cluster with the most points as the first true cluster, and remove all points in the cluster from further consideration. Must clarify what happens if more than 1 cluster has the maximum number of points ?
Recurse with the reduced set of points.
The distance between a point and a group of points is computed using complete linkage, i.e. as the maximum distance from the point to any member of the group (see the "Agglomerative hierarchical clustering" section about distance between clusters).
Locality-sensitive hashing
Locality-sensitive hashing can be used for clustering. Feature space vectors are sets, and the metric used is the Jaccard distance. The feature space can be considered high-dimensional. The min-wise independent permutations LSH scheme (sometimes MinHash) is then used to put similar items into buckets. With just one set of hashing methods, there are only clusters of very similar elements. By seeding the hash functions several times (eg 20), it is possible to get bigger clusters.
Graph-theoretic methods
Formal concept analysis is a technique for generating clusters of objects and attributes, given a bipartite graph representing the relations between the objects and attributes. Other methods for generating overlapping clusters (a cover rather than a partition) are discussed by Jardine and Sibson (1968) and Cole and Wishart (1970).
Spectral clustering
Given a set of data points A, the similarity matrix may be defined as a matrix S where Sij represents a measure of the similarity between points . Spectral clustering techniques make use of the spectrum of the similarity matrix of the data to perform dimensionality reduction for clustering in fewer dimensions.
One such technique is the Shi-Malik algorithm, commonly used for image segmentation. It partitions points into two sets (S1,S2) based on the eigenvector v corresponding to the second-smallest eigenvalue of the Laplacian matrix
L = I − D − 1 / 2SD − 1 / 2
of S, where D is the diagonal matrix
This partitioning may be done in various ways, such as by taking the median m of the components in v, and placing all points whose component in v is greater than m in S1, and the rest in S2. The algorithm can be used for hierarchical clustering by repeatedly partitioning the subsets in this fashion.
A related algorithm is the Meila-Shi algorith, which takes the eigenvectors corresponding to the k largest eigenvalues of the matrix P = SD − 1 for some k, and then invokes another (e.g. k-means) to cluster points by their respective k components in these eigenvectors.
Agglomerative hierarchical clustering
This method builds the hierarchy from the individual elements by progressively merging clusters. In our example, we have six elements {a} {b} {c} {d} {e} and {f}. The first step is to determine which elements to merge in a cluster. Usually, we want to take the two closest elements, according to the chosen distance.
Optionally, one can also construct a distance matrix at this stage, where the number in the i-th row j-th column is the distance between the i-th and j-th elements. Then, as clustering progresses, rows and columns are merged as the clusters are merged and the distances updated. This is a common way to implement this type of clustering, and has the benefit of caching distances between clusters. A simple agglomerative clustering algorithm is described in the single-linkage clustering page; it can easily be adapted to different types of linkage (see below).
Suppose we have merged the two closest elements b and c, we now have the following clusters {a}, {b, c}, {d}, {e} and {f}, and want to merge them further. To do that, we need to take the distance between {a} and {b c}, and therefore define the distance between two clusters. Usually the distance between two clusters A and B is one of the following:
The maximum distance between elements of each cluster (also called complete linkage clustering):
Max{d(x,y): x € A, y € B}
The minimum distance between elements of each cluster (also called single-linkage clustering):
Min {d (x, y): x € A, y € B}
The mean distance between elements of each cluster (also called average linkage clustering, used e.g. in UPGMA):
The sum of all intra-cluster variance.
The increase in variance for the cluster being merged (Ward's criterion).
The probability that candidate clusters spawn from the same distribution function (V-linkage).
Each agglomeration occurs at a greater distance between clusters than the previous agglomeration, and one can decide to stop clustering either when the clusters are too far apart to be merged (distance criterion) or when there is a sufficiently small number of clusters (number criterion).
Thursday, May 7, 2009
DMDW FAQS with solutions for JNTU BTECH and MCA students unit 1
1. Define Data Mining. What are the fundamentals of Data mining?
Data mining, the extraction of hidden predictive information from large databases, is a powerful new technology with great potential to help companies focus on the most important information in their data warehouses. Data mining tools predict future trends and behaviors, allowing businesses to make proactive, knowledge-driven decisions. The automated, prospective analyses offered by data mining move beyond the analyses of past events provided by retrospective tools typical of decision support systems. Data mining tools can answer business questions that traditionally were too time consuming to resolve. They scour databases for hidden patterns, finding predictive information that experts may miss because it lies outside their expectations
Fundamentals of Data mining:
Data mining techniques are the result of a long process of research and product development. This evolution began when business data was first stored on computers, continued with improvements in data access, and more recently, generated technologies that allow users to navigate through their data in real time. Data mining takes this evolutionary process beyond retrospective data access and navigation to prospective and proactive information delivery. Data mining is ready for application in the business community because it is supported by three technologies that are now sufficiently mature:
Massive data collection
Powerful multiprocessor computers
Data mining algorithms
Commercial databases are growing at unprecedented rates. A recent META Group survey of data warehouse projects found that 19% of respondents are beyond the 50 gigabyte level, while 59% expect to be there by second quarter of 1996.1 In some industries, such as retail, these numbers can be much larger. The accompanying need for improved computational engines can now be met in a cost-effective manner with parallel multiprocessor computer technology. Data mining algorithms embody techniques that have existed for at least 10 years, but have only recently been implemented as mature, reliable, understandable tools that consistently outperform older statistical methods.
In the evolution from business data to business information, each new step has built upon the previous one. For example, dynamic data access is critical for drill-through in data navigation applications, and the ability to store large databases is critical to data mining. From the user’s point of view, the four steps listed in Table 1 were revolutionary because they allowed new business questions to be answered accurately and quickly.
2. Define KDD. What are the steps in KDD.
Knowledge discovery is defined as ``the non-trivial extraction of implicit, unknown, and potentially useful information from data''. a clear distinction between data mining and knowledge discovery is drawn. Under their conventions, the knowledge discovery process takes the raw results from data mining (the process of extracting trends or patterns from data) and carefully and accurately transforms them into useful and understandable information. This information is not typically retrievable by standard techniques but is uncovered through the use of AI techniques.
Although there are many approaches to KDD, six common and essential elements qualify each as a knowledge discovery technique. The following are basic features that all KDD techniques share:
All approaches deal with large amounts of data
Efficiency is required due to volume of data
Accuracy is an essential element
All require the use of a high-level language
All approaches use some form of automated learning
All produce some interesting results
Large amounts of data are required to provide sufficient information to derive additional knowledge. Since large amounts of data are required, processing efficiency is essential. Accuracy is required to assure that discovered knowledge is valid. The results should be presented in a manner that is understandable by humans. One of the major premises of KDD is that the knowledge is discovered using intelligent learning techniques that sift through the data in an automated process. For this technique to be considered useful in terms of knowledge discovery, the discovered knowledge must be interesting; that is, it must have potential value to the user.
KDD provides the capability to discover new and meaningful information by using existing data. KDD quickly exceeds the human capacity to analyze large data sets. The amount of data that requires processing and analysis in a large database exceeds human capabilities, and the difficulty of accurately transforming raw data into knowledge surpasses the limits of traditional databases. Therefore, the full utilization of stored data depends on the use of knowledge discovery techniques.
The usefulness of future applications of KDD is far-reaching. KDD may be used as a means of information retrieval, in the same manner that intelligent agents perform information retrieval on the web. New patterns or trends in data may be discovered using these techniques. KDD may also be used as a basis for the intelligent interfaces of tomorrow, by adding a knowledge discovery component to a database engine or by integrating KDD with spreadsheets and visualizations.
3. Define Data warehouse. Explain Multidimensional model?
Data warehouse:
Data warehouse is a Subject oriented, Integrated, Time variant, and non volatile collection of data for managers decision making process.
Multi-dimensional model is an integral aspect of the On-line Analytical Processing which also known as OLAP.
Due to the fact that OLAP is online it provides information quickly, iterative queries are often posed during interactive sessions.
Due to the analytical nature of OLAP the queries are often complex. The multi-dimensional model is used to solve this kind of complex queries. The model is important because it applies simplicity.
This helps users understand the databases and enables software to plot a course through the databases effectively.
Multi-dimensional data models are made up of logical cubes, measures, and dimensions. Within the models you can also find hierarchies, levels, and attributes.
The straightforwardness of the model is essential due to the fact that is identifies objects that represent real world entities.
The analysts know what measures they want to see, what dimensions and attributes make the data important, and in what ways the dimensions of their work is organized into levels as well as hierarchies.
4. Define data mining. What are the major issues of Data Mining?
Data mining, the extraction of hidden predictive information from large databases, is a powerful new technology with great potential to help companies focus on the most important information in their data warehouses. Data mining tools predict future trends and behaviors, allowing businesses to make proactive, knowledge-driven decisions. The automated, prospective analyses offered by data mining move beyond the analyses of past events provided by retrospective tools typical of decision support systems. Data mining tools can answer business questions that traditionally were too time consuming to resolve. They scour databases for hidden patterns, finding predictive information that experts may miss because it lies outside their expectations
Major Issues
One of the key issues raised by data mining technology is not a business or technological one, but a social one. It is the issue of individual privacy. Data mining makes it possible to analyze routine business transactions and glean a significant amount of information about individuals buying habits and preferences.
Another issue is that of data integrity. Clearly, data analysis can only be as good as the data that is being analyzed. A key implementation challenge is integrating conflicting or redundant data from different sources. For example, a bank may maintain credit cards accounts on several different databases. The addresses (or even the names) of a single cardholder may be different in each. Software must translate data from one system to another and select the address most recently entered.
A hotly debated technical issue is whether it is better to set up a relational database structure or a multidimensional one. In a relational structure, data is stored in tables, permitting ad hoc queries. In a multidimensional structure, on the other hand, sets of cubes are arranged in arrays, with subsets created according to category. While multidimensional structures facilitate multidimensional data mining, relational structures thus far have performed better in client/server environments. And, with the explosion of the Internet, the world is becoming one big client/server environment.
Finally, there is the issue of cost. While system hardware costs have dropped dramatically within the past five years, data mining and data warehousing tend to be self-reinforcing. The more powerful the data mining queries, the greater the utility of the information being gleaned from the data, and the greater the pressure to increase the amount of data being collected and maintained, which increases the pressure for faster, more powerful data mining queries. This increases pressure for larger, faster systems, which are more expensive.
5. Explain the construction and implementation of Multi Dimensional Model.
Multi-dimensional model is an integral aspect of the On-line Analytical Processing which also known as OLAP.
Due to the fact that OLAP is online it provides information quickly, iterative queries are often posed during interactive sessions.
Due to the analytical nature of OLAP the queries are often complex. The multi-dimensional model is used to solve this kind of complex queries. The model is important because it applies simplicity.
This helps users understand the databases and enables software to plot a course through the databases effectively.
Multi-dimensional data models are made up of logical cubes, measures, and dimensions. Within the models you can also find hierarchies, levels, and attributes.
The straightforwardness of the model is essential due to the fact that is identifies objects that represent real world entities.
The analysts know what measures they want to see, what dimensions and attributes make the data important, and in what ways the dimensions of their work is organized into levels as well as hierarchies.
6. Explain the Architecture of Data mining with a neat diagram?
Architecture for Data Mining
To best apply these advanced techniques, they must be fully integrated with a data warehouse as well as flexible interactive business analysis tools. Many data mining tools currently operate outside of the warehouse, requiring extra steps for extracting, importing, and analyzing the data. Furthermore, when new insights require operational implementation, integration with the warehouse simplifies the application of results from data mining. The resulting analytic data warehouse can be applied to improve business processes throughout the organization, in areas such as promotional campaign management, fraud detection, new product rollout, and so on. Figure 1 illustrates an architecture for advanced analysis in a large data warehouse.
Figure 1 - Integrated Data Mining Architecture
The ideal starting point is a data warehouse containing a combination of internal data tracking all customer contact coupled with external market data about competitor activity. Background information on potential customers also provides an excellent basis for prospecting. This warehouse can be implemented in a variety of relational database systems: Sybase, Oracle, Redbrick, and so on, and should be optimized for flexible and fast data access.
An OLAP (On-Line Analytical Processing) server enables a more sophisticated end-user business model to be applied when navigating the data warehouse. The multidimensional structures allow the user to analyze the data as they want to view their business – summarizing by product line, region, and other key perspectives of their business. The Data Mining Server must be integrated with the data warehouse and the OLAP server to embed ROI-focused business analysis directly into this infrastructure. An advanced, process-centric metadata template defines the data mining objectives for specific business issues like campaign management, prospecting, and promotion optimization. Integration with the data warehouse enables operational decisions to be directly implemented and tracked. As the warehouse grows with new decisions and results, the organization can continually mine the best practices and apply them to future decisions.
This design represents a fundamental shift from conventional decision support systems. Rather than simply delivering data to the end user through query and reporting software, the Advanced Analysis Server applies users’ business models directly to the warehouse and returns a proactive analysis of the most relevant information. These results enhance the metadata in the OLAP Server by providing a dynamic metadata layer that represents a distilled view of the data. Reporting, visualization, and other analysis tools can then be applied to plan future actions and confirm the impact of those plans.
7. Explain the architecture of Data warehouse with a neat diagram.
Data Warehouse Architecture (with a Staging Area)
We need to clean and process your operational data before putting it into the warehouse. You can do this programmatically, although most data warehouses use a staging area instead. A staging area simplifies building summaries and general warehouse management.
Figure 1-3 Architecture of a Data Warehouse with a Staging Area
Although the architecture in the above figure is quite common, you may want to customize your warehouse's architecture for different groups within your organization. You can do this by adding data marts, which are systems designed for a particular line of business. The above figure illustrates an example where purchasing, sales, and inventories are separated. In this example, a financial analyst might want to analyze historical data for purchases and sales.
8. Write a short note on Data mart, Metadata with an example?
A data mart is a subset of an organizational data store, usually oriented to a specific purpose or major data subject, that may be distributed to support business needs. Data marts are analytical data stores designed to focus on specific business functions for a specific community within an organization. Data marts are often derived from subsets of data in a data warehouse, though in the bottom-up data warehouse design methodology the data warehouse is created from the union of organizational data marts.
Metadata (meta data, or sometimes metainformation) is "data about other data", of any sort in any media. An item of metadata may describe an individual datum, or content item, or a collection of data including multiple content items and hierarchical levels, for example a database schema. In data processing, metadata is definitional data that provides information about or documentation of other data managed within an application or environment. The term should be used with caution as all data is about something, and is therefore "metadata" in a sense, and vice versa.
For example, metadata would document data about data elements or attributes, (name, size, data type, etc) and data about records or data structures (length, fields, columns, etc) and data about data (where it is located, how it is associated, ownership, etc.). Metadata may include descriptive information about the context, quality and condition, or characteristics of the data. It may be recorded with high or low granularity.
9. Differentiate OLAP and OLTP?
OLTP OLAP
Current data Current and historical data Short database transactions Long database transactions Online update/insert/delete Batch update/insert/delete Normalization is promoted De normalization is promoted High volume transactions Low volume transactions Transaction recovery is necessary Transaction recovery is not necessary
10. Explain the operations of OLAP?
Think of OLAP operations (ROLLUP, CUBE, and GROUPING SETS) as follows:
· A list of prefixes is constructed for the query. A prefix is a subset of the items in the group-by-list.
A prefix is constructed by excluding one or more of the rightmost items from those in the query's group-by-list. The remaining columns are called the prefix columns.
Take the following ROLLUP query as an example: SELECT year (order_date) Year, quarter (order_date) Quarter, count (*) OrdersFROM sales_orderGROUP BY ROLLUP (Year, Quarter)ORDER BY Year, Quarter
In the example above the group-by-list includes two variables (Year and Quarter).GROUP BY ROLLUP (Year, Quarter)
11. Explain Fact table and Dimensional table?
In data warehousing, a fact table consists of the measurements, metrics or facts of a business process. It is often located at the centre of a star schema, surrounded by dimension tables.
Fact tables provide the (usually) additive values that act as independent variables by which dimensional attributes are analyzed. Fact tables are often defined by their grain. The grain of a fact table represents the most atomic level by which the facts may be defined. The grain of a SALES fact table might be stated as "Sales volume by Day by Product by Store". Each record in this fact table is therefore uniquely defined by a day, product and store. Other dimensions might be members of this fact table (such as location/region) but these add nothing to the uniqueness of the fact records. These "affiliate dimensions" allow for additional slices of the independent facts but generally provide insights at a higher level of aggregation (a region contains many stores).
Fact Table The centralized table in a star schema is called as FACT table. A fact table typically has two types of columns: those that contain facts and those that are foreign keys to dimension tables. The primary key of a fact table is usually a composite key that is made up of all of its foreign keys. Fact tables store different types of measures like additive, non additive and semi additive measures
Dimension Table
a dimension table is one of the set of companion tables to a fact table.
The fact table contains business facts or measures and foreign keys which refer to candidate keys (normally primary keys) in the dimension tables.
The dimension tables contain attributes (or fields) used to constrain and group data when performing datawarehousing queries.
Over time, the attributes of a given row in a dimension table may change. For example, the shipping address for a company may change. Kimball refers to this phenomenon as Slowly Changing dimensions. Strategies for dealing with this kind of change are divided into three categories:
Type One - Simply overwrite the old value(s).
Type Two - Add a new row containing the new value(s), and distinguish between the rows using Tuple- versioning techniques.
Type Three - Add a new attribute to the existing row.
12. Define Database server?
A computer in a LAN dedicated to database storage and retrieval. The database server is a key component in a client/server environment. It holds the database management system (DBMS) and the databases. Upon requests from the client machines, it searches the database for selected records and passes them back over the network.A database server and file server may be one and the same, because a file server often provides database services. However, the term implies that the system is dedicated for database use only and not a central storage facility for applications and files. See client/server.
13. What is Graphical user Interface. Explain the types of Graphical user interfaces.
A graphical user interface (GUI, pronounced) is a type of user interface which allows people to interact with electronic devices such as computers; hand-held devices such as MP3 Players, Portable Media Players or Gaming devices; household appliances and office equipment with images rather than text commands. A GUI offers graphical icons, and visual indicators, as opposed to text-based interfaces, typed command labels or text navigation to fully represent the information and actions available to a user. The actions are usually performed through direct manipulation of the graphical elements
14. Explain different types of databases.
Types of databases:
i) Relational Databases
A relational database is a database that groups data using common attributes found in the data set. The resulting "clumps" of organized data are much easier for people to understand. For example, a data set containing all the real estate transactions in a town can be grouped by the year the transaction occurred; or it can be grouped by the sale price of the transaction; or it can be grouped by the buyer's last name; and so on.
ii) Data warehouse
Data warehouse is a repository of an organization's electronically stored data. Data warehouses are designed to facilitate reporting and analysis.
This definition of the data warehouse focuses on data storage. However, the means to retrieve and analyze data, to extract, transform and load data, and to manage the data dictionary are also considered essential components of a data warehousing system. Many references to data warehousing use this broader context. Thus, an expanded definition for data warehousing includes business intelligence tools, tools to extract, transform, and load data into the repository, and tools to manage and retrieve metadata.
iii) Operational databases
Operational Databases are very important to a business. These databases allow a business to enter, gather, and retrieve specific company information. Operational databases can be known by another name – production database. Known by different names, users can misunderstand what the database is supposed to be used for within a business. For instance, transaction database could mean that the information stored focuses on financial information even though it may not. Operational databases can store different types of information such as training status, personal employee information, and previous proposal information. Storing information in a centralized area can increase retrieval time for users. Operational databases are important when information is needed quickly. An important feature of storing information in an operational database is the ability to share information across the company. Another feature of an operational database is how much information can be stored that pertains to a business. Depending on the type of operational database being used will determine how much information it can hold.
iv) Object – Relational databases
An object-relational database (ORD) or object-relational database management system (ORDBMS) is a database management system (DBMS) similar to a relational database, but with an object-oriented database model: objects, classes and inheritance are directly supported in database schemas and in the query language. In addition, it supports extension of the data model with custom data-types and methods.
v) Spatial databases
A spatial database is a database that is optimized to store and query data related to objects in space, including points, lines and polygons. While typical databases can understand various numeric and character types of data, additional functionality needs to be added for databases to process spatial data types. These are typically called geometry or feature. The Open Geospatial Consortium created the Simple Features specification and sets standards for adding spatial functionality to database systems.
15. Differentiate Heterogenous and legacy data bases.
A Heterogeneous Database System is an automated (or semi-automated) system for the integration of heterogeneous, disparate database management systems to present a user with a single, unified query interface.
Heterogeneous database systems (HDBS) are computational models and software implementations that provide heterogeneous database integration [1,2]
Data across constituent databases may be related but different. Perhaps a database system must be able to integrate genomic and proteomic data. They are related - a gene may have several protein products - but the data is different (nucleotide sequences and amino acid sequences, or hydrophilic/phobic amino acid sequence and positive/negatively charge amino acids). There may be many ways of looking at semantically similar, but distinct datasets.
The system may also be required to present 'new' knowledge to the user. Relationships may be inferred between data according to rules specified in domain ontologies.
Legacy data is data that you already have and use. Most often, this takes the form of records in an existing database on a system in current use. At mindwrap., the questions we are most frequently asked concern how to use these legacy records, how to "image enable" an existing database, or how Optix can interact with an existing system. Often, the existing database forms an important part of daily work and will remain in place after Optix is installed.
Optix can help you leverage your legacy data in a variety of ways. Because Optix stores all of its management tables, workflow tables, and user-level attribute tables in an underlying database such as Oracle™, Informix™, or Sybase™, it is relatively easy to copy a legacy database into Optix by offloading records and onloading them into the Optix database. Using our index screen design tool, you can generate an indexing and query screen to match any existing record layout.
16. Explain classification of data mining systems.
Data mining includes database systems, machine learning, visualization and information science. Due to diversity of disciplines contributing to data mining, data mining research is expected to generate a large variety of data mining systems to have clear classification of data mining system, will help distinguish between such systems and identify those that best match their needs. It may be classified as:
1. Classification according to the kind of databases mined.
2. Classification according to the kind of knowledge mined.
3. Classification according to the kind of techniques utilized.
4. Classification according to applications adapted.
17. Define Schema. Illustrate star schema with an example.
Schema may refer to:
Model or Diagram
Schematic, a diagram that represents the elements of a system using abstract, graphic symbols
Axiom schema, in formal logic
The star schema (sometimes referenced as star join schema) is the simplest style of data warehouse schema. The star schema consists of a few fact tables (possibly only one, justifying the name) referencing any number of dimension tables. The star schema is considered an important special case of the snowflake schema.
Consider a database of sales, perhaps from a store chain, classified by date, store and product. The image of the schema to the right is a star schema version of the sample schema provided in the snowflake schema article.
Fact_Sales is the fact table and there are three dimension tables Dim_Date, Dim_Store and Dim_Product.
Each dimension table has a primary key on its Id column, relating to one of the columns of the Fact_Sales table's three-column primary key (Date_Id, Store_Id, Product_Id). The non-primary key Units_Sold column of the fact table in this example represents a measure or metric that can be used in calculations and analysis. The non-primary key columns of the dimension tables represent additional attributes of the dimensions (such as the Year of the Dim_Date dimension).
18.Explain fact table. Illustrate star schema with an exaple?
Fact Table The centralized table in a star schema is called as FACT table. A fact table typically has two types of columns: those that contain facts and those that are foreign keys to dimension tables. The primary key of a fact table is usually a composite key that is made up of all of its foreign keys. Fact tables store different types of measures like additive, non additive and semi additive measures
A star schema consists of fact tables and dimension tables. Fact tables contain the quantitative or factual data about a business--the information being queried. This information is often numerical, additive measurements and can consist of many columns and millions or billions of rows. Dimension tables are usually smaller and hold descriptive data that reflects the dimensions, or attributes, of a business. SQL queries then use joins between fact and dimension tables and constraints on the data to return selected information.
Simple star schemas
Any table that references or is referenced by another table must have a primary key, which is a column or group of columns whose contents uniquely identify each row. In a simple star schema, the primary key for the fact table consists of one or more foreign keys. A foreign key is a column or group of columns in one table whose values are defined by the primary key in another table. In IBM Red Brick Warehouse, you can use these foreign keys and the primary keys in the tables that they reference to build STAR indexes, which improve data retrieval performance.
When a database is created, the SQL statements used to create the tables must designate the columns that are to form the primary and foreign keys.
The following figure illustrates the relationship of the fact and dimension tables within a simple star schema with a single fact table and three dimension tables. The fact table has a primary key composed of three foreign keys, Key1, Key2, and Key3, each of which is the primary key in a dimension table. Nonkey columns in a fact table are referred to as data columns. In a dimension table, they are referred to as attributes.
Figure 11. Simple Star Schema
In the figures used to illustrate schemas:
· The items listed within the box under each table name indicate columns in the table.
· Primary key columns are labeled in bold type.
· Foreign key columns are labeled in italic type.
· Columns that are part of the primary key and are also foreign keys are labeled in bold italic type.
· Foreign key relationships are indicated by lines connecting tables.
Although the primary key value must be unique in each row of a dimension table, that value can occur multiple times in the foreign key in the fact table--a many-to-one relationship.
A concept hierarchy defines a sequence of mappings from a set of low-level concepts to higher level, more general concepts.
Types of concept hierarchy:
1. Schema hierarchy: A schema hierarchy is a total or partial order among attributes in the database schema. Schema hierarchies may formally express existing semantic, relationships between attributes. Typically, a schema hierarchy specifies a data warehouse dimension.
2. Set-grouping hierarchies: A set-grouping hierarchy organizes values for a given attribute or dimension into groups of constants or range values. A total or partial order can be defined among groups. Set-grouping hierarchies can be used to refine or enrich schema-defined hierarchies, when the two types of hierarchies are combined. They are typically used for defining small sets of object relationships.
19. Define concept hierarchy. Explain the types of concept hierarchies.
A concept hierarchy defines a sequence of mappings from a set of low-level concepts to higher level, more general concepts.
Types of concept hierarchy:
1. Schema hierarchy: A schema hierarchy is a total or partial order among attributes in the database schema. Schema hierarchies may formally express existing semantic, relationships between attributes. Typically, a schema hierarchy specifies a data warehouse dimension.
2. Set-grouping hierarchies: A set-grouping hierarchy organizes values for a given attribute or dimension into groups of constants or range values. A total or partial order can be defined among groups. Set-grouping hierarchies can be used to refine or enrich schema-defined hierarchies, when the two types of hierarchies are combined. They are typically used for defining small sets of object relationships.
20.Differentiate ROLAP and MOLAP?
MOLAP (multidimensional OLAP) tools utilize a pre-calculated data set, commonly referred to as a data cube, that contains all the possible answers to a given range of questions. MOLAP tools feature very fast response, and the ability to quickly write back data into the data set (budgeting and forecasting are common applications). Primary downsides of MOLAP tools are limited scalability (the cubes get very big, very fast when you start to add dimensions and more detailed data), inability to contain detailed data (you are forced to use summary data unless your data set is very small), and load time of the cubes. The most common examples of MOLAP tools are Hyperion (Arbor) Essbase and Oracle (IRI) Express. MOLAP tools are best used for users who have "bounded" problem sets (they want to ask the same range questions every day/week/month on an updated cube, e.g. finance).
ROLAP (relational OLAP) tools do not use pre-calculated data cubes. Instead, they intercept the query and pose the question to the standard relational database and its tables in order to bring back the data required to answer the question. ROLAP tools feature the ability to ask any question (you are not limited to the contents of a cube) and the ability to drill down to the lowest level of detail in the database. Primary downsides of ROLAP tools are slow response and some limitations on scalability (depending on the technology architecture that is utilized). The most common examples of ROLAP tools are MicroStrategy and Sterling (Information Advantage). ROLAP tools are best used for users who have "unbounded" problem set (they don't have any idea what they want to ask from day to day; e.g., marketing). It is very important to pay close attention to the underlying architecture of ROLAP tools, as some tools are very "scalability challenged."
Data mining, the extraction of hidden predictive information from large databases, is a powerful new technology with great potential to help companies focus on the most important information in their data warehouses. Data mining tools predict future trends and behaviors, allowing businesses to make proactive, knowledge-driven decisions. The automated, prospective analyses offered by data mining move beyond the analyses of past events provided by retrospective tools typical of decision support systems. Data mining tools can answer business questions that traditionally were too time consuming to resolve. They scour databases for hidden patterns, finding predictive information that experts may miss because it lies outside their expectations
Fundamentals of Data mining:
Data mining techniques are the result of a long process of research and product development. This evolution began when business data was first stored on computers, continued with improvements in data access, and more recently, generated technologies that allow users to navigate through their data in real time. Data mining takes this evolutionary process beyond retrospective data access and navigation to prospective and proactive information delivery. Data mining is ready for application in the business community because it is supported by three technologies that are now sufficiently mature:
Massive data collection
Powerful multiprocessor computers
Data mining algorithms
Commercial databases are growing at unprecedented rates. A recent META Group survey of data warehouse projects found that 19% of respondents are beyond the 50 gigabyte level, while 59% expect to be there by second quarter of 1996.1 In some industries, such as retail, these numbers can be much larger. The accompanying need for improved computational engines can now be met in a cost-effective manner with parallel multiprocessor computer technology. Data mining algorithms embody techniques that have existed for at least 10 years, but have only recently been implemented as mature, reliable, understandable tools that consistently outperform older statistical methods.
In the evolution from business data to business information, each new step has built upon the previous one. For example, dynamic data access is critical for drill-through in data navigation applications, and the ability to store large databases is critical to data mining. From the user’s point of view, the four steps listed in Table 1 were revolutionary because they allowed new business questions to be answered accurately and quickly.
2. Define KDD. What are the steps in KDD.
Knowledge discovery is defined as ``the non-trivial extraction of implicit, unknown, and potentially useful information from data''. a clear distinction between data mining and knowledge discovery is drawn. Under their conventions, the knowledge discovery process takes the raw results from data mining (the process of extracting trends or patterns from data) and carefully and accurately transforms them into useful and understandable information. This information is not typically retrievable by standard techniques but is uncovered through the use of AI techniques.
Although there are many approaches to KDD, six common and essential elements qualify each as a knowledge discovery technique. The following are basic features that all KDD techniques share:
All approaches deal with large amounts of data
Efficiency is required due to volume of data
Accuracy is an essential element
All require the use of a high-level language
All approaches use some form of automated learning
All produce some interesting results
Large amounts of data are required to provide sufficient information to derive additional knowledge. Since large amounts of data are required, processing efficiency is essential. Accuracy is required to assure that discovered knowledge is valid. The results should be presented in a manner that is understandable by humans. One of the major premises of KDD is that the knowledge is discovered using intelligent learning techniques that sift through the data in an automated process. For this technique to be considered useful in terms of knowledge discovery, the discovered knowledge must be interesting; that is, it must have potential value to the user.
KDD provides the capability to discover new and meaningful information by using existing data. KDD quickly exceeds the human capacity to analyze large data sets. The amount of data that requires processing and analysis in a large database exceeds human capabilities, and the difficulty of accurately transforming raw data into knowledge surpasses the limits of traditional databases. Therefore, the full utilization of stored data depends on the use of knowledge discovery techniques.
The usefulness of future applications of KDD is far-reaching. KDD may be used as a means of information retrieval, in the same manner that intelligent agents perform information retrieval on the web. New patterns or trends in data may be discovered using these techniques. KDD may also be used as a basis for the intelligent interfaces of tomorrow, by adding a knowledge discovery component to a database engine or by integrating KDD with spreadsheets and visualizations.
3. Define Data warehouse. Explain Multidimensional model?
Data warehouse:
Data warehouse is a Subject oriented, Integrated, Time variant, and non volatile collection of data for managers decision making process.
Multi-dimensional model is an integral aspect of the On-line Analytical Processing which also known as OLAP.
Due to the fact that OLAP is online it provides information quickly, iterative queries are often posed during interactive sessions.
Due to the analytical nature of OLAP the queries are often complex. The multi-dimensional model is used to solve this kind of complex queries. The model is important because it applies simplicity.
This helps users understand the databases and enables software to plot a course through the databases effectively.
Multi-dimensional data models are made up of logical cubes, measures, and dimensions. Within the models you can also find hierarchies, levels, and attributes.
The straightforwardness of the model is essential due to the fact that is identifies objects that represent real world entities.
The analysts know what measures they want to see, what dimensions and attributes make the data important, and in what ways the dimensions of their work is organized into levels as well as hierarchies.
4. Define data mining. What are the major issues of Data Mining?
Data mining, the extraction of hidden predictive information from large databases, is a powerful new technology with great potential to help companies focus on the most important information in their data warehouses. Data mining tools predict future trends and behaviors, allowing businesses to make proactive, knowledge-driven decisions. The automated, prospective analyses offered by data mining move beyond the analyses of past events provided by retrospective tools typical of decision support systems. Data mining tools can answer business questions that traditionally were too time consuming to resolve. They scour databases for hidden patterns, finding predictive information that experts may miss because it lies outside their expectations
Major Issues
One of the key issues raised by data mining technology is not a business or technological one, but a social one. It is the issue of individual privacy. Data mining makes it possible to analyze routine business transactions and glean a significant amount of information about individuals buying habits and preferences.
Another issue is that of data integrity. Clearly, data analysis can only be as good as the data that is being analyzed. A key implementation challenge is integrating conflicting or redundant data from different sources. For example, a bank may maintain credit cards accounts on several different databases. The addresses (or even the names) of a single cardholder may be different in each. Software must translate data from one system to another and select the address most recently entered.
A hotly debated technical issue is whether it is better to set up a relational database structure or a multidimensional one. In a relational structure, data is stored in tables, permitting ad hoc queries. In a multidimensional structure, on the other hand, sets of cubes are arranged in arrays, with subsets created according to category. While multidimensional structures facilitate multidimensional data mining, relational structures thus far have performed better in client/server environments. And, with the explosion of the Internet, the world is becoming one big client/server environment.
Finally, there is the issue of cost. While system hardware costs have dropped dramatically within the past five years, data mining and data warehousing tend to be self-reinforcing. The more powerful the data mining queries, the greater the utility of the information being gleaned from the data, and the greater the pressure to increase the amount of data being collected and maintained, which increases the pressure for faster, more powerful data mining queries. This increases pressure for larger, faster systems, which are more expensive.
5. Explain the construction and implementation of Multi Dimensional Model.
Multi-dimensional model is an integral aspect of the On-line Analytical Processing which also known as OLAP.
Due to the fact that OLAP is online it provides information quickly, iterative queries are often posed during interactive sessions.
Due to the analytical nature of OLAP the queries are often complex. The multi-dimensional model is used to solve this kind of complex queries. The model is important because it applies simplicity.
This helps users understand the databases and enables software to plot a course through the databases effectively.
Multi-dimensional data models are made up of logical cubes, measures, and dimensions. Within the models you can also find hierarchies, levels, and attributes.
The straightforwardness of the model is essential due to the fact that is identifies objects that represent real world entities.
The analysts know what measures they want to see, what dimensions and attributes make the data important, and in what ways the dimensions of their work is organized into levels as well as hierarchies.
6. Explain the Architecture of Data mining with a neat diagram?
Architecture for Data Mining
To best apply these advanced techniques, they must be fully integrated with a data warehouse as well as flexible interactive business analysis tools. Many data mining tools currently operate outside of the warehouse, requiring extra steps for extracting, importing, and analyzing the data. Furthermore, when new insights require operational implementation, integration with the warehouse simplifies the application of results from data mining. The resulting analytic data warehouse can be applied to improve business processes throughout the organization, in areas such as promotional campaign management, fraud detection, new product rollout, and so on. Figure 1 illustrates an architecture for advanced analysis in a large data warehouse.
Figure 1 - Integrated Data Mining Architecture
The ideal starting point is a data warehouse containing a combination of internal data tracking all customer contact coupled with external market data about competitor activity. Background information on potential customers also provides an excellent basis for prospecting. This warehouse can be implemented in a variety of relational database systems: Sybase, Oracle, Redbrick, and so on, and should be optimized for flexible and fast data access.
An OLAP (On-Line Analytical Processing) server enables a more sophisticated end-user business model to be applied when navigating the data warehouse. The multidimensional structures allow the user to analyze the data as they want to view their business – summarizing by product line, region, and other key perspectives of their business. The Data Mining Server must be integrated with the data warehouse and the OLAP server to embed ROI-focused business analysis directly into this infrastructure. An advanced, process-centric metadata template defines the data mining objectives for specific business issues like campaign management, prospecting, and promotion optimization. Integration with the data warehouse enables operational decisions to be directly implemented and tracked. As the warehouse grows with new decisions and results, the organization can continually mine the best practices and apply them to future decisions.
This design represents a fundamental shift from conventional decision support systems. Rather than simply delivering data to the end user through query and reporting software, the Advanced Analysis Server applies users’ business models directly to the warehouse and returns a proactive analysis of the most relevant information. These results enhance the metadata in the OLAP Server by providing a dynamic metadata layer that represents a distilled view of the data. Reporting, visualization, and other analysis tools can then be applied to plan future actions and confirm the impact of those plans.
7. Explain the architecture of Data warehouse with a neat diagram.
Data Warehouse Architecture (with a Staging Area)
We need to clean and process your operational data before putting it into the warehouse. You can do this programmatically, although most data warehouses use a staging area instead. A staging area simplifies building summaries and general warehouse management.
Figure 1-3 Architecture of a Data Warehouse with a Staging Area
Although the architecture in the above figure is quite common, you may want to customize your warehouse's architecture for different groups within your organization. You can do this by adding data marts, which are systems designed for a particular line of business. The above figure illustrates an example where purchasing, sales, and inventories are separated. In this example, a financial analyst might want to analyze historical data for purchases and sales.
8. Write a short note on Data mart, Metadata with an example?
A data mart is a subset of an organizational data store, usually oriented to a specific purpose or major data subject, that may be distributed to support business needs. Data marts are analytical data stores designed to focus on specific business functions for a specific community within an organization. Data marts are often derived from subsets of data in a data warehouse, though in the bottom-up data warehouse design methodology the data warehouse is created from the union of organizational data marts.
Metadata (meta data, or sometimes metainformation) is "data about other data", of any sort in any media. An item of metadata may describe an individual datum, or content item, or a collection of data including multiple content items and hierarchical levels, for example a database schema. In data processing, metadata is definitional data that provides information about or documentation of other data managed within an application or environment. The term should be used with caution as all data is about something, and is therefore "metadata" in a sense, and vice versa.
For example, metadata would document data about data elements or attributes, (name, size, data type, etc) and data about records or data structures (length, fields, columns, etc) and data about data (where it is located, how it is associated, ownership, etc.). Metadata may include descriptive information about the context, quality and condition, or characteristics of the data. It may be recorded with high or low granularity.
9. Differentiate OLAP and OLTP?
OLTP OLAP
Current data Current and historical data Short database transactions Long database transactions Online update/insert/delete Batch update/insert/delete Normalization is promoted De normalization is promoted High volume transactions Low volume transactions Transaction recovery is necessary Transaction recovery is not necessary
10. Explain the operations of OLAP?
Think of OLAP operations (ROLLUP, CUBE, and GROUPING SETS) as follows:
· A list of prefixes is constructed for the query. A prefix is a subset of the items in the group-by-list.
A prefix is constructed by excluding one or more of the rightmost items from those in the query's group-by-list. The remaining columns are called the prefix columns.
Take the following ROLLUP query as an example: SELECT year (order_date) Year, quarter (order_date) Quarter, count (*) OrdersFROM sales_orderGROUP BY ROLLUP (Year, Quarter)ORDER BY Year, Quarter
In the example above the group-by-list includes two variables (Year and Quarter).GROUP BY ROLLUP (Year, Quarter)
11. Explain Fact table and Dimensional table?
In data warehousing, a fact table consists of the measurements, metrics or facts of a business process. It is often located at the centre of a star schema, surrounded by dimension tables.
Fact tables provide the (usually) additive values that act as independent variables by which dimensional attributes are analyzed. Fact tables are often defined by their grain. The grain of a fact table represents the most atomic level by which the facts may be defined. The grain of a SALES fact table might be stated as "Sales volume by Day by Product by Store". Each record in this fact table is therefore uniquely defined by a day, product and store. Other dimensions might be members of this fact table (such as location/region) but these add nothing to the uniqueness of the fact records. These "affiliate dimensions" allow for additional slices of the independent facts but generally provide insights at a higher level of aggregation (a region contains many stores).
Fact Table The centralized table in a star schema is called as FACT table. A fact table typically has two types of columns: those that contain facts and those that are foreign keys to dimension tables. The primary key of a fact table is usually a composite key that is made up of all of its foreign keys. Fact tables store different types of measures like additive, non additive and semi additive measures
Dimension Table
a dimension table is one of the set of companion tables to a fact table.
The fact table contains business facts or measures and foreign keys which refer to candidate keys (normally primary keys) in the dimension tables.
The dimension tables contain attributes (or fields) used to constrain and group data when performing datawarehousing queries.
Over time, the attributes of a given row in a dimension table may change. For example, the shipping address for a company may change. Kimball refers to this phenomenon as Slowly Changing dimensions. Strategies for dealing with this kind of change are divided into three categories:
Type One - Simply overwrite the old value(s).
Type Two - Add a new row containing the new value(s), and distinguish between the rows using Tuple- versioning techniques.
Type Three - Add a new attribute to the existing row.
12. Define Database server?
A computer in a LAN dedicated to database storage and retrieval. The database server is a key component in a client/server environment. It holds the database management system (DBMS) and the databases. Upon requests from the client machines, it searches the database for selected records and passes them back over the network.A database server and file server may be one and the same, because a file server often provides database services. However, the term implies that the system is dedicated for database use only and not a central storage facility for applications and files. See client/server.
13. What is Graphical user Interface. Explain the types of Graphical user interfaces.
A graphical user interface (GUI, pronounced) is a type of user interface which allows people to interact with electronic devices such as computers; hand-held devices such as MP3 Players, Portable Media Players or Gaming devices; household appliances and office equipment with images rather than text commands. A GUI offers graphical icons, and visual indicators, as opposed to text-based interfaces, typed command labels or text navigation to fully represent the information and actions available to a user. The actions are usually performed through direct manipulation of the graphical elements
14. Explain different types of databases.
Types of databases:
i) Relational Databases
A relational database is a database that groups data using common attributes found in the data set. The resulting "clumps" of organized data are much easier for people to understand. For example, a data set containing all the real estate transactions in a town can be grouped by the year the transaction occurred; or it can be grouped by the sale price of the transaction; or it can be grouped by the buyer's last name; and so on.
ii) Data warehouse
Data warehouse is a repository of an organization's electronically stored data. Data warehouses are designed to facilitate reporting and analysis.
This definition of the data warehouse focuses on data storage. However, the means to retrieve and analyze data, to extract, transform and load data, and to manage the data dictionary are also considered essential components of a data warehousing system. Many references to data warehousing use this broader context. Thus, an expanded definition for data warehousing includes business intelligence tools, tools to extract, transform, and load data into the repository, and tools to manage and retrieve metadata.
iii) Operational databases
Operational Databases are very important to a business. These databases allow a business to enter, gather, and retrieve specific company information. Operational databases can be known by another name – production database. Known by different names, users can misunderstand what the database is supposed to be used for within a business. For instance, transaction database could mean that the information stored focuses on financial information even though it may not. Operational databases can store different types of information such as training status, personal employee information, and previous proposal information. Storing information in a centralized area can increase retrieval time for users. Operational databases are important when information is needed quickly. An important feature of storing information in an operational database is the ability to share information across the company. Another feature of an operational database is how much information can be stored that pertains to a business. Depending on the type of operational database being used will determine how much information it can hold.
iv) Object – Relational databases
An object-relational database (ORD) or object-relational database management system (ORDBMS) is a database management system (DBMS) similar to a relational database, but with an object-oriented database model: objects, classes and inheritance are directly supported in database schemas and in the query language. In addition, it supports extension of the data model with custom data-types and methods.
v) Spatial databases
A spatial database is a database that is optimized to store and query data related to objects in space, including points, lines and polygons. While typical databases can understand various numeric and character types of data, additional functionality needs to be added for databases to process spatial data types. These are typically called geometry or feature. The Open Geospatial Consortium created the Simple Features specification and sets standards for adding spatial functionality to database systems.
15. Differentiate Heterogenous and legacy data bases.
A Heterogeneous Database System is an automated (or semi-automated) system for the integration of heterogeneous, disparate database management systems to present a user with a single, unified query interface.
Heterogeneous database systems (HDBS) are computational models and software implementations that provide heterogeneous database integration [1,2]
Data across constituent databases may be related but different. Perhaps a database system must be able to integrate genomic and proteomic data. They are related - a gene may have several protein products - but the data is different (nucleotide sequences and amino acid sequences, or hydrophilic/phobic amino acid sequence and positive/negatively charge amino acids). There may be many ways of looking at semantically similar, but distinct datasets.
The system may also be required to present 'new' knowledge to the user. Relationships may be inferred between data according to rules specified in domain ontologies.
Legacy data is data that you already have and use. Most often, this takes the form of records in an existing database on a system in current use. At mindwrap., the questions we are most frequently asked concern how to use these legacy records, how to "image enable" an existing database, or how Optix can interact with an existing system. Often, the existing database forms an important part of daily work and will remain in place after Optix is installed.
Optix can help you leverage your legacy data in a variety of ways. Because Optix stores all of its management tables, workflow tables, and user-level attribute tables in an underlying database such as Oracle™, Informix™, or Sybase™, it is relatively easy to copy a legacy database into Optix by offloading records and onloading them into the Optix database. Using our index screen design tool, you can generate an indexing and query screen to match any existing record layout.
16. Explain classification of data mining systems.
Data mining includes database systems, machine learning, visualization and information science. Due to diversity of disciplines contributing to data mining, data mining research is expected to generate a large variety of data mining systems to have clear classification of data mining system, will help distinguish between such systems and identify those that best match their needs. It may be classified as:
1. Classification according to the kind of databases mined.
2. Classification according to the kind of knowledge mined.
3. Classification according to the kind of techniques utilized.
4. Classification according to applications adapted.
17. Define Schema. Illustrate star schema with an example.
Schema may refer to:
Model or Diagram
Schematic, a diagram that represents the elements of a system using abstract, graphic symbols
Axiom schema, in formal logic
The star schema (sometimes referenced as star join schema) is the simplest style of data warehouse schema. The star schema consists of a few fact tables (possibly only one, justifying the name) referencing any number of dimension tables. The star schema is considered an important special case of the snowflake schema.
Consider a database of sales, perhaps from a store chain, classified by date, store and product. The image of the schema to the right is a star schema version of the sample schema provided in the snowflake schema article.
Fact_Sales is the fact table and there are three dimension tables Dim_Date, Dim_Store and Dim_Product.
Each dimension table has a primary key on its Id column, relating to one of the columns of the Fact_Sales table's three-column primary key (Date_Id, Store_Id, Product_Id). The non-primary key Units_Sold column of the fact table in this example represents a measure or metric that can be used in calculations and analysis. The non-primary key columns of the dimension tables represent additional attributes of the dimensions (such as the Year of the Dim_Date dimension).
18.Explain fact table. Illustrate star schema with an exaple?
Fact Table The centralized table in a star schema is called as FACT table. A fact table typically has two types of columns: those that contain facts and those that are foreign keys to dimension tables. The primary key of a fact table is usually a composite key that is made up of all of its foreign keys. Fact tables store different types of measures like additive, non additive and semi additive measures
A star schema consists of fact tables and dimension tables. Fact tables contain the quantitative or factual data about a business--the information being queried. This information is often numerical, additive measurements and can consist of many columns and millions or billions of rows. Dimension tables are usually smaller and hold descriptive data that reflects the dimensions, or attributes, of a business. SQL queries then use joins between fact and dimension tables and constraints on the data to return selected information.
Simple star schemas
Any table that references or is referenced by another table must have a primary key, which is a column or group of columns whose contents uniquely identify each row. In a simple star schema, the primary key for the fact table consists of one or more foreign keys. A foreign key is a column or group of columns in one table whose values are defined by the primary key in another table. In IBM Red Brick Warehouse, you can use these foreign keys and the primary keys in the tables that they reference to build STAR indexes, which improve data retrieval performance.
When a database is created, the SQL statements used to create the tables must designate the columns that are to form the primary and foreign keys.
The following figure illustrates the relationship of the fact and dimension tables within a simple star schema with a single fact table and three dimension tables. The fact table has a primary key composed of three foreign keys, Key1, Key2, and Key3, each of which is the primary key in a dimension table. Nonkey columns in a fact table are referred to as data columns. In a dimension table, they are referred to as attributes.
Figure 11. Simple Star Schema
In the figures used to illustrate schemas:
· The items listed within the box under each table name indicate columns in the table.
· Primary key columns are labeled in bold type.
· Foreign key columns are labeled in italic type.
· Columns that are part of the primary key and are also foreign keys are labeled in bold italic type.
· Foreign key relationships are indicated by lines connecting tables.
Although the primary key value must be unique in each row of a dimension table, that value can occur multiple times in the foreign key in the fact table--a many-to-one relationship.
A concept hierarchy defines a sequence of mappings from a set of low-level concepts to higher level, more general concepts.
Types of concept hierarchy:
1. Schema hierarchy: A schema hierarchy is a total or partial order among attributes in the database schema. Schema hierarchies may formally express existing semantic, relationships between attributes. Typically, a schema hierarchy specifies a data warehouse dimension.
2. Set-grouping hierarchies: A set-grouping hierarchy organizes values for a given attribute or dimension into groups of constants or range values. A total or partial order can be defined among groups. Set-grouping hierarchies can be used to refine or enrich schema-defined hierarchies, when the two types of hierarchies are combined. They are typically used for defining small sets of object relationships.
19. Define concept hierarchy. Explain the types of concept hierarchies.
A concept hierarchy defines a sequence of mappings from a set of low-level concepts to higher level, more general concepts.
Types of concept hierarchy:
1. Schema hierarchy: A schema hierarchy is a total or partial order among attributes in the database schema. Schema hierarchies may formally express existing semantic, relationships between attributes. Typically, a schema hierarchy specifies a data warehouse dimension.
2. Set-grouping hierarchies: A set-grouping hierarchy organizes values for a given attribute or dimension into groups of constants or range values. A total or partial order can be defined among groups. Set-grouping hierarchies can be used to refine or enrich schema-defined hierarchies, when the two types of hierarchies are combined. They are typically used for defining small sets of object relationships.
20.Differentiate ROLAP and MOLAP?
MOLAP (multidimensional OLAP) tools utilize a pre-calculated data set, commonly referred to as a data cube, that contains all the possible answers to a given range of questions. MOLAP tools feature very fast response, and the ability to quickly write back data into the data set (budgeting and forecasting are common applications). Primary downsides of MOLAP tools are limited scalability (the cubes get very big, very fast when you start to add dimensions and more detailed data), inability to contain detailed data (you are forced to use summary data unless your data set is very small), and load time of the cubes. The most common examples of MOLAP tools are Hyperion (Arbor) Essbase and Oracle (IRI) Express. MOLAP tools are best used for users who have "bounded" problem sets (they want to ask the same range questions every day/week/month on an updated cube, e.g. finance).
ROLAP (relational OLAP) tools do not use pre-calculated data cubes. Instead, they intercept the query and pose the question to the standard relational database and its tables in order to bring back the data required to answer the question. ROLAP tools feature the ability to ask any question (you are not limited to the contents of a cube) and the ability to drill down to the lowest level of detail in the database. Primary downsides of ROLAP tools are slow response and some limitations on scalability (depending on the technology architecture that is utilized). The most common examples of ROLAP tools are MicroStrategy and Sterling (Information Advantage). ROLAP tools are best used for users who have "unbounded" problem set (they don't have any idea what they want to ask from day to day; e.g., marketing). It is very important to pay close attention to the underlying architecture of ROLAP tools, as some tools are very "scalability challenged."
DMDW FAQS with solutions for JNTU BTECH and MCA students unit 3
1. Explain various Data mining primitives.
Data mining task primitives are used to describe data mining query which are given as input to data mining system. Every task in data mining is defined in the form of data mining query. These task primitives enables the user to communicate interactively during the discovery stage. Data mining should be an interactive process
Users must be provided with a set of primitives to be used to communicate with the data mining system. Incorporating these primitives in a data mining query language
More flexible user interaction. Foundation for design of graphical user interface
Standardization of data mining industry and practice
Data mining primitives:
Task-relevant data
Type of knowledge to be mined
Background knowledge
Pattern interestingness measurements
Visualization of discovered patterns
2. What is concept hierarchy in Data mining. Discuss different types of hierarchies in data mining.
A concept hierarchy defines a sequence of mappings from a set of low-level concepts to higher level, more general concepts.
Types of concept hierarchy:
1. Schema hierarchy: A schema hierarchy is a total or partial order among attributes in the database schema. Schema hierarchies may formally express existing semantic, relationships between attributes. Typically, a schema hierarchy specifies a data warehouse dimension.
2. Set-grouping hierarchies: A set-grouping hierarchy organizes values for a given attribute or dimension into groups of constants or range values. A total or partial order can be defined among groups. Set-grouping hierarchies can be used to refine or enrich schema-defined hierarchies, when the two types of hierarchies are combined. They are typically used for defining small sets of object relationships.
3.Define confidence and support interestingness measure with formula.
4.List out discuss different types of coupling schemas.
1. No coupling : No coupling means that a DM system will not utilize any function of a DB or DW system. It may fetch data from a particular source, process the data using some data mining algorithms, and then store the mining results in another file.
2. Loose coupling: Loose coupling means that a DM system will use some facilities of a DB or DW system, fetching data from a data repository managed by these systems, performing data mining and then storing the mining results either in a file or in a designated place in a database or data warehouse.
3. Semi tight coupling : Semi tight coupling means that besides linking a DM system to a DB/DW system, efficient implementations of a few essential data mining primitives can be provided in the DM/DW system.
4. Tight coupling : Tight coupling means that a DM system is smoothly integrated into the DB/DW system.
5. Give an example for each type of concept hierarchy.
Schema hierarchy
E.g., street < color="#33ffff">6.Write DMQL syntax for association and classification
Association
Mine_Knowledge_Specification ::= mine associations [as pattern_name]
Classification
Mine_Knowledge_Specification ::= mine classification [as pattern_name] analyze classifying_attribute_or_dimension
7. Define the various ways of carrying out the presentation and visualization specification.
Different backgrounds/usages may require different forms of representation
E.g., rules, tables, crosstabs, pie/bar chart etc.
Concept hierarchy is also important
Discovered knowledge might be more understandable when represented at high level of abstraction
Interactive drill up/down, pivoting, slicing and dicing provide different perspective to data
Different kinds of knowledge require different representation: association, classification, clustering, etc.
9. Write DMQL syntax for prediction and association.
Prediction
Mine_Knowledge_Specification ::= mine prediction [as pattern_name] analyze prediction_attribute_or_dimension {set {attribute_or_dimension_i= value_i}}
Association
Mine_Knowledge_Specification ::= mine associations [as pattern_name
10, 11. Give an example for location, age hierarchy with dmql syntax.
use database AllElectronics_db
use hierarchy location_hierarchy for B.address
mine characteristics as customerPurchasing
analyze count%
in relevance to C.age, I.type, I.place_made
from customer C, item I, purchases P, items_sold S, works_at W, branch
where I.item_ID = S.item_ID and S.trans_ID = P.trans_ID
and P.cust_ID = C.cust_ID and P.method_paid = ``AmEx''
and P.empl_ID = W.empl_ID and W.branch_ID = B.branch_ID and B.address = ``Canada" and I.price >= 100
with noise threshold = 0.05
display as table
define hierarchy age_hierarchy for age on customer as
{age_category(1), ..., age_category(5)} := cluster(default, age, 5) < color="#33ffff">12. Explain Rule – based hierarchies. With example
rule-based hierarchies
define hierarchy profit_margin_hierarchy on item as
level_1: low_profit_margin <> $50) and ((price - cost) <= $250)) level_1: high_profit_margin <> $250
13. Explain set – grouping hierarchies with example.
set-grouping hierarchies
define hierarchy age_hierarchy for age on customer as
level1: {young, middle_aged, senior} < color="#33ffff">14. Discuss the various classification of data mining system
15. Write an DMQL query to compare the students majoring in science with students majoring in arts.
use database AllElectronics_db
use hierarchy location_hierarchy for B.address
mine characteristics as customerPurchasing
analyze count%
in relevance to C.age, I.type, I.place_made
from customer C, item I, purchases P, items_sold S, works_at W, branch
where I.item_ID = S.item_ID and S.trans_ID = P.trans_ID
and P.cust_ID = C.cust_ID and P.method_paid = ``AmEx''
and P.empl_ID = W.empl_ID and W.branch_ID = B.branch_ID and B.address = ``Canada" and I.price >= 100
with noise threshold = 0.05
display as table
16. state DMQL syntax for Task relevant Data specification and knowledge to be mined specification.
use database database_name, or use data warehouse data_warehouse_name
from relation(s)/cube(s) [where condition]
in relevance to att_or_dim_list
order by order_list
group by grouping_list
having condition
Characterization
Mine_Knowledge_Specification ::= mine characteristics [as pattern_name] analyze measure(s)
Discrimination
Mine_Knowledge_Specification ::= mine comparison [as pattern_name] for target_class where target_condition {versus contrast_class_i where contrast_condition_i} analyze measure(s)
Association
Mine_Knowledge_Specification ::= mine associations [as pattern_name]
Data mining task primitives are used to describe data mining query which are given as input to data mining system. Every task in data mining is defined in the form of data mining query. These task primitives enables the user to communicate interactively during the discovery stage. Data mining should be an interactive process
Users must be provided with a set of primitives to be used to communicate with the data mining system. Incorporating these primitives in a data mining query language
More flexible user interaction. Foundation for design of graphical user interface
Standardization of data mining industry and practice
Data mining primitives:
Task-relevant data
Type of knowledge to be mined
Background knowledge
Pattern interestingness measurements
Visualization of discovered patterns
2. What is concept hierarchy in Data mining. Discuss different types of hierarchies in data mining.
A concept hierarchy defines a sequence of mappings from a set of low-level concepts to higher level, more general concepts.
Types of concept hierarchy:
1. Schema hierarchy: A schema hierarchy is a total or partial order among attributes in the database schema. Schema hierarchies may formally express existing semantic, relationships between attributes. Typically, a schema hierarchy specifies a data warehouse dimension.
2. Set-grouping hierarchies: A set-grouping hierarchy organizes values for a given attribute or dimension into groups of constants or range values. A total or partial order can be defined among groups. Set-grouping hierarchies can be used to refine or enrich schema-defined hierarchies, when the two types of hierarchies are combined. They are typically used for defining small sets of object relationships.
3.Define confidence and support interestingness measure with formula.
4.List out discuss different types of coupling schemas.
1. No coupling : No coupling means that a DM system will not utilize any function of a DB or DW system. It may fetch data from a particular source, process the data using some data mining algorithms, and then store the mining results in another file.
2. Loose coupling: Loose coupling means that a DM system will use some facilities of a DB or DW system, fetching data from a data repository managed by these systems, performing data mining and then storing the mining results either in a file or in a designated place in a database or data warehouse.
3. Semi tight coupling : Semi tight coupling means that besides linking a DM system to a DB/DW system, efficient implementations of a few essential data mining primitives can be provided in the DM/DW system.
4. Tight coupling : Tight coupling means that a DM system is smoothly integrated into the DB/DW system.
5. Give an example for each type of concept hierarchy.
Schema hierarchy
E.g., street < color="#33ffff">6.Write DMQL syntax for association and classification
Association
Mine_Knowledge_Specification ::= mine associations [as pattern_name]
Classification
Mine_Knowledge_Specification ::= mine classification [as pattern_name] analyze classifying_attribute_or_dimension
7. Define the various ways of carrying out the presentation and visualization specification.
Different backgrounds/usages may require different forms of representation
E.g., rules, tables, crosstabs, pie/bar chart etc.
Concept hierarchy is also important
Discovered knowledge might be more understandable when represented at high level of abstraction
Interactive drill up/down, pivoting, slicing and dicing provide different perspective to data
Different kinds of knowledge require different representation: association, classification, clustering, etc.
9. Write DMQL syntax for prediction and association.
Prediction
Mine_Knowledge_Specification ::= mine prediction [as pattern_name] analyze prediction_attribute_or_dimension {set {attribute_or_dimension_i= value_i}}
Association
Mine_Knowledge_Specification ::= mine associations [as pattern_name
10, 11. Give an example for location, age hierarchy with dmql syntax.
use database AllElectronics_db
use hierarchy location_hierarchy for B.address
mine characteristics as customerPurchasing
analyze count%
in relevance to C.age, I.type, I.place_made
from customer C, item I, purchases P, items_sold S, works_at W, branch
where I.item_ID = S.item_ID and S.trans_ID = P.trans_ID
and P.cust_ID = C.cust_ID and P.method_paid = ``AmEx''
and P.empl_ID = W.empl_ID and W.branch_ID = B.branch_ID and B.address = ``Canada" and I.price >= 100
with noise threshold = 0.05
display as table
define hierarchy age_hierarchy for age on customer as
{age_category(1), ..., age_category(5)} := cluster(default, age, 5) < color="#33ffff">12. Explain Rule – based hierarchies. With example
rule-based hierarchies
define hierarchy profit_margin_hierarchy on item as
level_1: low_profit_margin <> $50) and ((price - cost) <= $250)) level_1: high_profit_margin <> $250
13. Explain set – grouping hierarchies with example.
set-grouping hierarchies
define hierarchy age_hierarchy for age on customer as
level1: {young, middle_aged, senior} < color="#33ffff">14. Discuss the various classification of data mining system
15. Write an DMQL query to compare the students majoring in science with students majoring in arts.
use database AllElectronics_db
use hierarchy location_hierarchy for B.address
mine characteristics as customerPurchasing
analyze count%
in relevance to C.age, I.type, I.place_made
from customer C, item I, purchases P, items_sold S, works_at W, branch
where I.item_ID = S.item_ID and S.trans_ID = P.trans_ID
and P.cust_ID = C.cust_ID and P.method_paid = ``AmEx''
and P.empl_ID = W.empl_ID and W.branch_ID = B.branch_ID and B.address = ``Canada" and I.price >= 100
with noise threshold = 0.05
display as table
16. state DMQL syntax for Task relevant Data specification and knowledge to be mined specification.
use database database_name, or use data warehouse data_warehouse_name
from relation(s)/cube(s) [where condition]
in relevance to att_or_dim_list
order by order_list
group by grouping_list
having condition
Characterization
Mine_Knowledge_Specification ::= mine characteristics [as pattern_name] analyze measure(s)
Discrimination
Mine_Knowledge_Specification ::= mine comparison [as pattern_name] for target_class where target_condition {versus contrast_class_i where contrast_condition_i} analyze measure(s)
Association
Mine_Knowledge_Specification ::= mine associations [as pattern_name]
Wednesday, May 6, 2009
Interview Questions for BTECH freshers and Database developers
ORACLE
1. If a package is created with two functions having same name but with different arguments as follows
create package overload_eg is
function test(c_val char) return char;
function test(c_val varchar2) return varchar2;
Now which function will be executed when call to function a is made as follows
SELECT overload_eg.test(‘x’) FROM dual;
ANS : It will return an error ORA-06553: PLS-307: too many declarations of 'A' match this call.
2. What is the new feature in import & export utility in Oracle 8I
You can Import or Export partition
3. What is the difference between truncate & delete command
TRUNCATE
It is a DDL command
It is faster than delete command. Because it doesn’t fires tables delete trigger.
It doesn’t generates rollback inforation.
DELETE
It is a DML command.
It is slower than truncate command.
It generates rollback inforation.
Truncate command is used to remove all rows from table or cluster and reset the storage parameters to the values when table or cluster were created.
4. What are the possible impacts on database objects when a table is dropped.
When table is dropped
· It invalidates table dependent objects like views, synonym etc.
· When table is recreated it requires you to regrant object privileges on the table.
· You need to recreate indexes, integrity constraints & triggers.
All the above problems doesn’t occur if we truncate the table.
Difference Between Synonym & View
1. View can be based on multiple tables. Synonym can be based on single table.
2. View can be for tables, view, or synonym. Synonym can be for any schema object.
1. Delete the duplicate rows from the table.
DELETE FROM emp a
WHERE Where rowid > (SELECT min (rowid)
FROM emp b
WHERE a.empno = b.empno)
2. To find the nth maximum salary.
SELECT sal FROM emp a Where &no = (SELECT count(distinct sal)
FROM emp b
WHERE a.sal <= b.sal) 3. To find the maximum 3 salaries : SELECT sal FROM emp a Where &no > (SELECT count(distinct sal)
FROM emp b
WHERE a.sal <> (SELECT count(distinct sal)
FROM emp b
WHERE a.sal < b.sal)
5. Give a query to retrieve last 5 rows from emp table.
SELECT * FROM emp
WHERE rownum < 6
ORDER BY rownum DESC
6. What will be the answer of SELECT * FROM emp WHERE rownum = 20;
No rows selected. Because query is successful, when WHERE clause is evaluated to TRUE. In above case when query retrieves first record the ROWNUM is 1. The WHERE condition is evaluated to FALSE. Hence no rows are returned.
7. Give a query to retrieve rows from 3 to 6.
8. Emp table contains 6 rows.
Dept table contains 10 rows.
What will be the output of SELECT * FROM emp, dept
9. Emp table contains 0 rows.
Dept table contains 10 rows.
What will be the output of SELECT * FROM emp, dept
10. What will be the output of ROUND(10, -2)
11. What will be the output of SQRT(-4)
12. What is the result of sum (500 + '50')?
550. Oracle doses implicit data conversion.
13. What is the result of NVL(SAL, 1000) 1000
An error FROM Keyword not found where expected is displayed.
What are joins? Give its example.
A) Equijoins
An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns.
B) Self Joins
A self join is a join of a table to itself. This table appears twice in the FROM clause
C) Outer Joins
The outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and those rows from one table for which no rows from the other satisfy the join condition.
What do you mean by TRANSACTION.
How to view the list of PROCEDURES. (Like select * from tab for list of TABLES).
Will INDEX on a TABLE WILL occupy space in the DATABASE.
What happens when someone updates the row while processing and what will you do for that?
Mutating Error encountered.
What are the consequences of dropping a table?
It invalidates the table’s dependent objects such as views.
The integrity constraints are lost.
All the indexes, triggers are lost.
What is Normalization? How many normal forms are there?
What is the format of ROWID
Every row in a nonclustered table of an Oracle database is assigned a unique ROWID that corresponds to the physical address of a row's row piece. In the case of clustered tables, rows in different tables that are in the same data block can have the same ROWID. Each table in an Oracle database internally has a pseudocolumn named ROWID. The Oracle Server uses an extended ROWID format, which supports features such as table partitions, index partitions, and clusters. The extended ROWID includes the following information:
· Data object (segment) identifier
· Datafile identifier
· Block identifier
· Row identifier
Oracle uses a ROWID datatype to store the address (rowid) of every row in the database.
· Physical rowids store the addresses of rows in ordinary tables (excluding index-organized tables), clustered tables, table partitions and subpartitions, indexes, and index partitions and subpartitions.
· Logical rowids store the addresses of rows in index-organized tables.
A single datatype called the universal rowid, or UROWID, supports both logical and physical rowids, as well as rowids of foreign tables
What is PSUDEOCOLUMNS? Name them?
· ROWID
· ROWNUM
· CURR_VAL : returns the current value of a sequence.
· NEXT_VAL : increments the sequence and returns the next
· value.
· LEVEL : For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root node, 2 for a child of a root, and so on. A root node is the highest node within an inverted tree. A child node is any non–root node. A parent node is any node that has children. A leaf node is any node without children.
Which statement is suitable
Select sum(c1) +sum(c2) from emp
Select sum ((c1) + (c2)) from emp
Select sum(c1) +sum(c2) from emp
What is Rollback segments?
What is Rollback forwarding?
Where NEXTVAL &CURRVAL cannot be used?
You can use CURRVAL and NEXTVAL in these places
· The SELECT list of a SELECT statement that is not contained in a Subquery, snapshot or view
· The SELECT list of a subquery in an INSERT statement
· The VALUES clause of an INSERT statement
· The SET clause of an UPDATE statement
You cannot use CURRVAL and NEXTVAL in these places
· A subquery in a DELETE, SELECT, or UPDATE statement
· In View or Snapshot.
· SELECT statement with the DISTINCT operator, GROUP BY or ORDER BY clause
· Query conaining UNION, INTERSECT, or MINUS set operator
· The WHERE clause of a SELECT statement
· DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
· The condition of a CHECK constraint
What are the set operators?
Union, Union All, Intersect, Minus.
What is FOR UPDATE & FOR UPDATE OF clause?
The FOR UPDATE clause locks the rows selected by the query. Once you have selected a row for update, other users cannot lock or update it until you end your transaction. The FOR UPDATE clause cannot be used with the following other
constructs:
· DISTINCT operator
· GROUP BY clause
· Set operators
· Group functions
In case of joins where columns from 2 or more tables are involved, FOR UPDATE clause will lock rows from all tables appearing in the join condition. In this case if you want to lock rows of specific table only then make use of FOR UPDATE OF clause and mention table to be locked.
The following statement locks rows in the EMP, & DEPT tables.
SELECT empno, sal, comm FROM emp, dept
WHERE job = ’CLERK’ AND emp.deptno = dept.deptno
AND loc = ’NEW YORK’ FOR UPDATE;
The following statement locks rows only from EMP table.
SELECT empno, sal, comm FROM emp, dept
WHERE job = ’CLERK’ AND emp.deptno = dept.deptno
AND loc = ’NEW YORK’ FOR UPDATE OF emp;
Stored Procedures, Functions and packages.
A stored procedure or function is a PL/SQL program unit that
Has a name
· Can take parameters, and return values
· Are stored in the data dictionary n compiled format.
· Can be invoked by many users
Stored procedures offer you advantages in the following areas:
· Development
· Integrity
· Security
· Performance
· Memory allocation
A package is a group of PL/SQL types, objects, and stored procedures and functions. The specification part of a package declares the public types, variables, constants, and subprograms that are visible outside the immediate scope of the package. The body of a package defines the objects declared in the specification, as well as private objects that are not visible to applications outside the package.
What is PL / SQL table
Can we Import Constraints?
Is there any way to write trigger on schema?
What is the difference between Trigger & Procedure?
1. You need to call procedure explicitly. Where as Trigger fires automatically when any DML statement is issued on table.
2. You can pass parameters a procedure. You can not pass parameters to a Trigger.
3. You can write COMMIT in procedure but not in TRIGGER.
What is the difference between Function & Procedure?
1. Function returns value with a RETURN statement. We can not use RETURN statement in procedure. In Procedure we can return value using
2. You can use function in SQL statement. But you can not use procedure in select statement.
What is Co-related sub-query?
A correlated subquery is a subquery that is evaluated once for each row processed by the parent statement. the parent table is referred in the where clause of the sub-query .
What are the steps involved in starting & stopping the oracle database? Explain briefly.
Starting an Instance.
Mounting the Database
Opening a Database
What is the function of SQL*Net ?
If the user and server processes are on different computers of a network or if the user processes connect to shared server processes through dispatcher processes, the user process and server process communicate using SQL*Net. Dispatchers are optional background processes, present only when a multi–threaded server configuration is used. SQL*Net is Oracle’s interface to standard communications protocols that allows for the proper transmission of data between computers.
REPORTS
1. What is the use of placeholder column.
2. Can you assign value to a placeholder column from PL/SQL trigger of a formula column item?
3. Can you assign value to a placeholder column from Format trigger of a text item on layout?
4. Can you change value of a parameter at runtime whose List of values property contains static values or values from a select statement. - Yes
5. What are the different types of parameters in report. – User & System
6. What is data parameter.
7. How many groups you can have in matrix report. - 4
8. What is the use of Distribution property in layout section.
9. What is the use of additional layout.
10. Can you call a report from a report. - Yes
11. What is the use of SRW package.
12. What is External Query Source File property of a query.
The External Query Source File property is the name of a query stored in the database or a file whose SELECT statement should be used as the SELECT statement for the current query. Enter a valid external query source file name not to exceed 1K. The external query source file you reference cannot exceed 32K in length. If it does, it will be truncated and a warning raised. If the query is stored in a file, you can prefix a path to the query name. If a path is not prefixed to the filename, Report Builder uses its file path search order to find the file. If the query is stored in the database, you can prefix DB: to it to indicate that it is stored in the database. For example:
DB:myquery
If you prefix DB: to the query name, the Report Builder will go directly to the database to find the query. If you are not connected to the database, it will not be able to find the query and will issue a message to that effect.
If you update the external query source file, you must ensure that the update does not invalidate the report definition. For example, if the report has a column for DEPTNO and you delete DEPTNO from the SELECT list of the external query source file, the report cannot run.
If you change the query in source file the query in report gets changed automatically. But you need to recompile the report again.
If you delete the source file and try to open the report dependent on that file you get the following error message
REP-1062 Unable to find external query.
When you specify an external query source file name, the SELECT statement of the external query appears in the SQL Query Statement field as read-only. To modify it, you must open the external query source file.
13. What is template.
Templates define common characteristics and objects that you want to apply to multiple reports. For example, you can define a template that includes the company logo and sets fonts and colors for selected areas of a report.
When you create a report through the Report Wizard, you have the option of applying a template (.tdf file) to the report. When you choose a template, objects in the margin area of a template are imported into the same locations in the current report section, overwriting any existing objects.
1. If a package is created with two functions having same name but with different arguments as follows
create package overload_eg is
function test(c_val char) return char;
function test(c_val varchar2) return varchar2;
Now which function will be executed when call to function a is made as follows
SELECT overload_eg.test(‘x’) FROM dual;
ANS : It will return an error ORA-06553: PLS-307: too many declarations of 'A' match this call.
2. What is the new feature in import & export utility in Oracle 8I
You can Import or Export partition
3. What is the difference between truncate & delete command
TRUNCATE
It is a DDL command
It is faster than delete command. Because it doesn’t fires tables delete trigger.
It doesn’t generates rollback inforation.
DELETE
It is a DML command.
It is slower than truncate command.
It generates rollback inforation.
Truncate command is used to remove all rows from table or cluster and reset the storage parameters to the values when table or cluster were created.
4. What are the possible impacts on database objects when a table is dropped.
When table is dropped
· It invalidates table dependent objects like views, synonym etc.
· When table is recreated it requires you to regrant object privileges on the table.
· You need to recreate indexes, integrity constraints & triggers.
All the above problems doesn’t occur if we truncate the table.
Difference Between Synonym & View
1. View can be based on multiple tables. Synonym can be based on single table.
2. View can be for tables, view, or synonym. Synonym can be for any schema object.
1. Delete the duplicate rows from the table.
DELETE FROM emp a
WHERE Where rowid > (SELECT min (rowid)
FROM emp b
WHERE a.empno = b.empno)
2. To find the nth maximum salary.
SELECT sal FROM emp a Where &no = (SELECT count(distinct sal)
FROM emp b
WHERE a.sal <= b.sal) 3. To find the maximum 3 salaries : SELECT sal FROM emp a Where &no > (SELECT count(distinct sal)
FROM emp b
WHERE a.sal <> (SELECT count(distinct sal)
FROM emp b
WHERE a.sal < b.sal)
5. Give a query to retrieve last 5 rows from emp table.
SELECT * FROM emp
WHERE rownum < 6
ORDER BY rownum DESC
6. What will be the answer of SELECT * FROM emp WHERE rownum = 20;
No rows selected. Because query is successful, when WHERE clause is evaluated to TRUE. In above case when query retrieves first record the ROWNUM is 1. The WHERE condition is evaluated to FALSE. Hence no rows are returned.
7. Give a query to retrieve rows from 3 to 6.
8. Emp table contains 6 rows.
Dept table contains 10 rows.
What will be the output of SELECT * FROM emp, dept
9. Emp table contains 0 rows.
Dept table contains 10 rows.
What will be the output of SELECT * FROM emp, dept
10. What will be the output of ROUND(10, -2)
11. What will be the output of SQRT(-4)
12. What is the result of sum (500 + '50')?
550. Oracle doses implicit data conversion.
13. What is the result of NVL(SAL, 1000) 1000
An error FROM Keyword not found where expected is displayed.
What are joins? Give its example.
A) Equijoins
An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns.
B) Self Joins
A self join is a join of a table to itself. This table appears twice in the FROM clause
C) Outer Joins
The outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and those rows from one table for which no rows from the other satisfy the join condition.
What do you mean by TRANSACTION.
How to view the list of PROCEDURES. (Like select * from tab for list of TABLES).
Will INDEX on a TABLE WILL occupy space in the DATABASE.
What happens when someone updates the row while processing and what will you do for that?
Mutating Error encountered.
What are the consequences of dropping a table?
It invalidates the table’s dependent objects such as views.
The integrity constraints are lost.
All the indexes, triggers are lost.
What is Normalization? How many normal forms are there?
What is the format of ROWID
Every row in a nonclustered table of an Oracle database is assigned a unique ROWID that corresponds to the physical address of a row's row piece. In the case of clustered tables, rows in different tables that are in the same data block can have the same ROWID. Each table in an Oracle database internally has a pseudocolumn named ROWID. The Oracle Server uses an extended ROWID format, which supports features such as table partitions, index partitions, and clusters. The extended ROWID includes the following information:
· Data object (segment) identifier
· Datafile identifier
· Block identifier
· Row identifier
Oracle uses a ROWID datatype to store the address (rowid) of every row in the database.
· Physical rowids store the addresses of rows in ordinary tables (excluding index-organized tables), clustered tables, table partitions and subpartitions, indexes, and index partitions and subpartitions.
· Logical rowids store the addresses of rows in index-organized tables.
A single datatype called the universal rowid, or UROWID, supports both logical and physical rowids, as well as rowids of foreign tables
What is PSUDEOCOLUMNS? Name them?
· ROWID
· ROWNUM
· CURR_VAL : returns the current value of a sequence.
· NEXT_VAL : increments the sequence and returns the next
· value.
· LEVEL : For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root node, 2 for a child of a root, and so on. A root node is the highest node within an inverted tree. A child node is any non–root node. A parent node is any node that has children. A leaf node is any node without children.
Which statement is suitable
Select sum(c1) +sum(c2) from emp
Select sum ((c1) + (c2)) from emp
Select sum(c1) +sum(c2) from emp
What is Rollback segments?
What is Rollback forwarding?
Where NEXTVAL &CURRVAL cannot be used?
You can use CURRVAL and NEXTVAL in these places
· The SELECT list of a SELECT statement that is not contained in a Subquery, snapshot or view
· The SELECT list of a subquery in an INSERT statement
· The VALUES clause of an INSERT statement
· The SET clause of an UPDATE statement
You cannot use CURRVAL and NEXTVAL in these places
· A subquery in a DELETE, SELECT, or UPDATE statement
· In View or Snapshot.
· SELECT statement with the DISTINCT operator, GROUP BY or ORDER BY clause
· Query conaining UNION, INTERSECT, or MINUS set operator
· The WHERE clause of a SELECT statement
· DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
· The condition of a CHECK constraint
What are the set operators?
Union, Union All, Intersect, Minus.
What is FOR UPDATE & FOR UPDATE OF clause?
The FOR UPDATE clause locks the rows selected by the query. Once you have selected a row for update, other users cannot lock or update it until you end your transaction. The FOR UPDATE clause cannot be used with the following other
constructs:
· DISTINCT operator
· GROUP BY clause
· Set operators
· Group functions
In case of joins where columns from 2 or more tables are involved, FOR UPDATE clause will lock rows from all tables appearing in the join condition. In this case if you want to lock rows of specific table only then make use of FOR UPDATE OF clause and mention table to be locked.
The following statement locks rows in the EMP, & DEPT tables.
SELECT empno, sal, comm FROM emp, dept
WHERE job = ’CLERK’ AND emp.deptno = dept.deptno
AND loc = ’NEW YORK’ FOR UPDATE;
The following statement locks rows only from EMP table.
SELECT empno, sal, comm FROM emp, dept
WHERE job = ’CLERK’ AND emp.deptno = dept.deptno
AND loc = ’NEW YORK’ FOR UPDATE OF emp;
Stored Procedures, Functions and packages.
A stored procedure or function is a PL/SQL program unit that
Has a name
· Can take parameters, and return values
· Are stored in the data dictionary n compiled format.
· Can be invoked by many users
Stored procedures offer you advantages in the following areas:
· Development
· Integrity
· Security
· Performance
· Memory allocation
A package is a group of PL/SQL types, objects, and stored procedures and functions. The specification part of a package declares the public types, variables, constants, and subprograms that are visible outside the immediate scope of the package. The body of a package defines the objects declared in the specification, as well as private objects that are not visible to applications outside the package.
What is PL / SQL table
Can we Import Constraints?
Is there any way to write trigger on schema?
What is the difference between Trigger & Procedure?
1. You need to call procedure explicitly. Where as Trigger fires automatically when any DML statement is issued on table.
2. You can pass parameters a procedure. You can not pass parameters to a Trigger.
3. You can write COMMIT in procedure but not in TRIGGER.
What is the difference between Function & Procedure?
1. Function returns value with a RETURN statement. We can not use RETURN statement in procedure. In Procedure we can return value using
2. You can use function in SQL statement. But you can not use procedure in select statement.
What is Co-related sub-query?
A correlated subquery is a subquery that is evaluated once for each row processed by the parent statement. the parent table is referred in the where clause of the sub-query .
What are the steps involved in starting & stopping the oracle database? Explain briefly.
Starting an Instance.
Mounting the Database
Opening a Database
What is the function of SQL*Net ?
If the user and server processes are on different computers of a network or if the user processes connect to shared server processes through dispatcher processes, the user process and server process communicate using SQL*Net. Dispatchers are optional background processes, present only when a multi–threaded server configuration is used. SQL*Net is Oracle’s interface to standard communications protocols that allows for the proper transmission of data between computers.
REPORTS
1. What is the use of placeholder column.
2. Can you assign value to a placeholder column from PL/SQL trigger of a formula column item?
3. Can you assign value to a placeholder column from Format trigger of a text item on layout?
4. Can you change value of a parameter at runtime whose List of values property contains static values or values from a select statement. - Yes
5. What are the different types of parameters in report. – User & System
6. What is data parameter.
7. How many groups you can have in matrix report. - 4
8. What is the use of Distribution property in layout section.
9. What is the use of additional layout.
10. Can you call a report from a report. - Yes
11. What is the use of SRW package.
12. What is External Query Source File property of a query.
The External Query Source File property is the name of a query stored in the database or a file whose SELECT statement should be used as the SELECT statement for the current query. Enter a valid external query source file name not to exceed 1K. The external query source file you reference cannot exceed 32K in length. If it does, it will be truncated and a warning raised. If the query is stored in a file, you can prefix a path to the query name. If a path is not prefixed to the filename, Report Builder uses its file path search order to find the file. If the query is stored in the database, you can prefix DB: to it to indicate that it is stored in the database. For example:
DB:myquery
If you prefix DB: to the query name, the Report Builder will go directly to the database to find the query. If you are not connected to the database, it will not be able to find the query and will issue a message to that effect.
If you update the external query source file, you must ensure that the update does not invalidate the report definition. For example, if the report has a column for DEPTNO and you delete DEPTNO from the SELECT list of the external query source file, the report cannot run.
If you change the query in source file the query in report gets changed automatically. But you need to recompile the report again.
If you delete the source file and try to open the report dependent on that file you get the following error message
REP-1062 Unable to find external query.
When you specify an external query source file name, the SELECT statement of the external query appears in the SQL Query Statement field as read-only. To modify it, you must open the external query source file.
13. What is template.
Templates define common characteristics and objects that you want to apply to multiple reports. For example, you can define a template that includes the company logo and sets fonts and colors for selected areas of a report.
When you create a report through the Report Wizard, you have the option of applying a template (.tdf file) to the report. When you choose a template, objects in the margin area of a template are imported into the same locations in the current report section, overwriting any existing objects.
Monday, May 4, 2009
Database Interview Questions (SQL, Oracle, Pro*C/C++)
Database Questions
1.What are the different types of joins?
2.Explain normalization with examples.
3.What cursor type do you use to retrieve multiple recordsets?
4.Diffrence between a "where" clause and a "having" clause
5.What is the difference between "procedure" and "function"?
6.How will you copy the structure of a table without copying the data?
7.What is the difference between outer join and inner join?
8.How to find out the database name from SQL*PLUS command prompt?
9.Tadeoffs with having indexes
10.Talk about "Exception Handling" in PL/SQL?
11.What is the diference between "NULL in C" and "NULL in Oracle?"
12.What is Pro*C? What is OCI?
13.Give some examples of Analytical functions.
14.What is the difference between "translate" and "replace"?
15.What is DYNAMIC SQL method 4?
16.How to remove duplicate records from a table?
17.What is the use of ANALYZing the tables?
18.How to run SQL script from a Unix Shell?
19.What is a "transaction"? Why are they necessary?
20.Explain Normalizationa dn Denormalization with examples.
21.When do you get contraint violtaion? What are the types of constraints?
22.How to convert RAW datatype into TEXT?
23.Difference - Primary Key and Aggregate Key
24.How functional dependency is related to database table design?
25.What is a "trigger"?
26.Why can a "group by" or "order by" clause be expensive to process?
27.What are "HINTS"? What is "index covering" of a query?
28.What is a VIEW? How to get script for a view?
29.What are the Large object types suported by Oracle?
30.What is SQL*Loader?
31.Difference between "VARCHAR" and "VARCHAR2" datatypes.
32.What is the difference among "dropping a table", "truncating a table" and "deleting all records" from a table.
33.Difference between "ORACLE" and "MICROSOFT ACCESS" databases.
34.How to create a database link ?
35.What are the different types of joins?
36.Explain normalization with examples.
37.What cursor type do you use to retrieve multiple recordsets?
38.What is TEMP table space in Oracle, what is rollback segment
39.How do we find row chaining?
40.Pattern matching operators ?
41.Features in oracle 9i and 10g ?
42.Why truncating table is faster than delete?
43.Write copy commit syntax?
44.Convert Zulu time zone to US Eastern time zone
45.What is Difference between - union and union all ?
46.Difference - Group by, Order by clause?
47.Which Ranking functions are available?
48.Difference - Decode, NVL, NVL2 ?
49.How can we call stored procedure in SQL query ?
50.What are the restrictions on calling PL/SQL from SQL ?
51.Why EXISTS is preferable to distinct?
1.What are the different types of joins?
2.Explain normalization with examples.
3.What cursor type do you use to retrieve multiple recordsets?
4.Diffrence between a "where" clause and a "having" clause
5.What is the difference between "procedure" and "function"?
6.How will you copy the structure of a table without copying the data?
7.What is the difference between outer join and inner join?
8.How to find out the database name from SQL*PLUS command prompt?
9.Tadeoffs with having indexes
10.Talk about "Exception Handling" in PL/SQL?
11.What is the diference between "NULL in C" and "NULL in Oracle?"
12.What is Pro*C? What is OCI?
13.Give some examples of Analytical functions.
14.What is the difference between "translate" and "replace"?
15.What is DYNAMIC SQL method 4?
16.How to remove duplicate records from a table?
17.What is the use of ANALYZing the tables?
18.How to run SQL script from a Unix Shell?
19.What is a "transaction"? Why are they necessary?
20.Explain Normalizationa dn Denormalization with examples.
21.When do you get contraint violtaion? What are the types of constraints?
22.How to convert RAW datatype into TEXT?
23.Difference - Primary Key and Aggregate Key
24.How functional dependency is related to database table design?
25.What is a "trigger"?
26.Why can a "group by" or "order by" clause be expensive to process?
27.What are "HINTS"? What is "index covering" of a query?
28.What is a VIEW? How to get script for a view?
29.What are the Large object types suported by Oracle?
30.What is SQL*Loader?
31.Difference between "VARCHAR" and "VARCHAR2" datatypes.
32.What is the difference among "dropping a table", "truncating a table" and "deleting all records" from a table.
33.Difference between "ORACLE" and "MICROSOFT ACCESS" databases.
34.How to create a database link ?
35.What are the different types of joins?
36.Explain normalization with examples.
37.What cursor type do you use to retrieve multiple recordsets?
38.What is TEMP table space in Oracle, what is rollback segment
39.How do we find row chaining?
40.Pattern matching operators ?
41.Features in oracle 9i and 10g ?
42.Why truncating table is faster than delete?
43.Write copy commit syntax?
44.Convert Zulu time zone to US Eastern time zone
45.What is Difference between - union and union all ?
46.Difference - Group by, Order by clause?
47.Which Ranking functions are available?
48.Difference - Decode, NVL, NVL2 ?
49.How can we call stored procedure in SQL query ?
50.What are the restrictions on calling PL/SQL from SQL ?
51.Why EXISTS is preferable to distinct?
History of SQL
SQL (Structured Query Language) (pronounced /ɛskjuːˈɛl /) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.
SQL is a standard interactive and programming language for querying and modifying data and managing databases. Although SQL is both an ANSI and an ISO standard, many database products support SQL with proprietary extensions to the standard language. The core of SQL is formed by a command language that allows the retrieval, insertion, updating, and deletion of data, and performing management and administrative functions. SQL also includes a Call Level Interface (SQL/CLI) for accessing and managing data and databases remotely.
The first version of SQL was developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s. This version, initially called SEQUEL, was designed to manipulate and retrieve data stored in IBM's original relational database product, System R. IBM patented their version of SQL in 1985, while the SQL language was not formally standardized until 1986, by the American National Standards Institute (ANSI). Subsequent versions of the SQL standard have been released as International Organization for Standardization (ISO) standards.
Originally designed as a declarative query and data manipulation language, variations of SQL have been created by SQL database management system (DBMS) vendors that add procedural constructs, control-of-flow statements, user-defined data types, and various other language extensions. With the release of the SQL:1999 standard, many such extensions were formally adopted as part of the SQL language via the SQL Persistent Stored Modules (SQL/PSM) portion of the standard.
Common criticisms of SQL include a perceived lack of cross-platform portability between vendors, inappropriate handling of missing data (see Null (SQL)), and unnecessarily complex and occasionally ambiguous language grammar and semantics.
SQL is a standard interactive and programming language for querying and modifying data and managing databases. Although SQL is both an ANSI and an ISO standard, many database products support SQL with proprietary extensions to the standard language. The core of SQL is formed by a command language that allows the retrieval, insertion, updating, and deletion of data, and performing management and administrative functions. SQL also includes a Call Level Interface (SQL/CLI) for accessing and managing data and databases remotely.
The first version of SQL was developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s. This version, initially called SEQUEL, was designed to manipulate and retrieve data stored in IBM's original relational database product, System R. IBM patented their version of SQL in 1985, while the SQL language was not formally standardized until 1986, by the American National Standards Institute (ANSI). Subsequent versions of the SQL standard have been released as International Organization for Standardization (ISO) standards.
Originally designed as a declarative query and data manipulation language, variations of SQL have been created by SQL database management system (DBMS) vendors that add procedural constructs, control-of-flow statements, user-defined data types, and various other language extensions. With the release of the SQL:1999 standard, many such extensions were formally adopted as part of the SQL language via the SQL Persistent Stored Modules (SQL/PSM) portion of the standard.
Common criticisms of SQL include a perceived lack of cross-platform portability between vendors, inappropriate handling of missing data (see Null (SQL)), and unnecessarily complex and occasionally ambiguous language grammar and semantics.
TSQL in ORACLE
This site will primarily focus on T-SQL used with Microsoft SQL Server. Most examples are for Microsoft SQL Server 2005, but most will work for earlier and later editions. The site will be updated as new editions of SQL Server are released. The Pages that aren't not linked in the navigation bar have not yet been completed. All Pages should be completed by the end of June 2008. For those just starting with SQL Server, some definitions are provided below:
SQL stands for Structured Query Language. It is most often pronounced "sequel" or sometimes pronounced by spelling out the letters "S-Q-L". SQL is a query language that is used to interact and perform tasks with relation databases. This language was originally developed by IBM in the 1970s.
T-SQL (Transact SQL) is a proprietary SQL extension used by Microsoft SQL Server and Sybase SQL Server. T-SQL adds extensions for procedural programming.
Flow control
Keywords for flow control in Transact-SQL include BEGIN and END, BREAK, CONTINUE, GOTO, IF and ELSE, RETURN, WAITFOR, and WHILE.
IF and ELSE allow conditional execution. This batch statement will print "weekend" if the current date is a weekend day, or "weekday" if the current date is a weekday.
IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1 PRINT 'It is the weekend.'ELSE PRINT 'It is a weekday.'
BEGIN and END mark a block of statements. If more than one statement is to be controlled by the conditional in the example above, we can use BEGIN and END like this:
IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1BEGIN PRINT 'It is the weekend.' PRINT 'Get some rest!'ENDELSEBEGIN PRINT 'It is a weekday.' PRINT 'Get to work!'END
WAITFOR will wait for a given amount of time, or until a particular time of day. The statement can be used for delays or to block execution until the set time.
RETURN is used to immediately return from a stored procedure or function.
BREAK ends the enclosing WHILE loop, while CONTINUE causes the next iteration of the loop to execute. An example of a WHILE loop is given below.
Local variables
Local variables are so named because they're local to the script executing them. Transact SQL doesn't support user-defined global variables.
DECLARE will declare a variable, giving it a name and a type. The SET statement can be used to provide a value, and the variable may be used in a statement by referencing its name.
This script declares a variable as an integer, initializes it, then uses WHILE to execute a loop.
DECLARE @Counter INT
SET @Counter = 10
WHILE @Counter > 0
BEGIN
PRINT 'The count is ' + CONVERT(VARCHAR(10), @Counter)
SET @Counter = @Counter - 1
END
The body of the loop will print a message including the value of the variable, and then decrement the counter.
A variable can be initialized as the result of a statement, like this:
DECLARE @ArticleCount INT
SELECT @ArticleCount = COUNT(*) FROM Articles
INSERT INTO SizeLog (SampleTime, ArticleCount) VALUES (GETDATE(), @ArticleCount)
which will get the count of rows in the Articles table, then insert a row including that count and the current clock time into the SizeLog table.
Changes to DELETE and UPDATE statements
In Transact-SQL, both the DELETE and UPDATE statements allow a FROM clause to be added, which allows joins to be included.
This example deletes all users who have been flagged with the 'Idle' flag.
DELETE users
FROM users AS u
JOIN user_flags AS f
ON u.id=f.id
WHERE f.name = 'Idle'
BULK INSERT
BULK INSERT is a Transact-SQL statement that implements a bulk data-loading process, inserting multiple rows into a table, reading data from an external sequential file. Use of BULK INSERT results in better performance than processes that issue individual INSERT statements for each row to be added.
SQL stands for Structured Query Language. It is most often pronounced "sequel" or sometimes pronounced by spelling out the letters "S-Q-L". SQL is a query language that is used to interact and perform tasks with relation databases. This language was originally developed by IBM in the 1970s.
T-SQL (Transact SQL) is a proprietary SQL extension used by Microsoft SQL Server and Sybase SQL Server. T-SQL adds extensions for procedural programming.
Flow control
Keywords for flow control in Transact-SQL include BEGIN and END, BREAK, CONTINUE, GOTO, IF and ELSE, RETURN, WAITFOR, and WHILE.
IF and ELSE allow conditional execution. This batch statement will print "weekend" if the current date is a weekend day, or "weekday" if the current date is a weekday.
IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1 PRINT 'It is the weekend.'ELSE PRINT 'It is a weekday.'
BEGIN and END mark a block of statements. If more than one statement is to be controlled by the conditional in the example above, we can use BEGIN and END like this:
IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1BEGIN PRINT 'It is the weekend.' PRINT 'Get some rest!'ENDELSEBEGIN PRINT 'It is a weekday.' PRINT 'Get to work!'END
WAITFOR will wait for a given amount of time, or until a particular time of day. The statement can be used for delays or to block execution until the set time.
RETURN is used to immediately return from a stored procedure or function.
BREAK ends the enclosing WHILE loop, while CONTINUE causes the next iteration of the loop to execute. An example of a WHILE loop is given below.
Local variables
Local variables are so named because they're local to the script executing them. Transact SQL doesn't support user-defined global variables.
DECLARE will declare a variable, giving it a name and a type. The SET statement can be used to provide a value, and the variable may be used in a statement by referencing its name.
This script declares a variable as an integer, initializes it, then uses WHILE to execute a loop.
DECLARE @Counter INT
SET @Counter = 10
WHILE @Counter > 0
BEGIN
PRINT 'The count is ' + CONVERT(VARCHAR(10), @Counter)
SET @Counter = @Counter - 1
END
The body of the loop will print a message including the value of the variable, and then decrement the counter.
A variable can be initialized as the result of a statement, like this:
DECLARE @ArticleCount INT
SELECT @ArticleCount = COUNT(*) FROM Articles
INSERT INTO SizeLog (SampleTime, ArticleCount) VALUES (GETDATE(), @ArticleCount)
which will get the count of rows in the Articles table, then insert a row including that count and the current clock time into the SizeLog table.
Changes to DELETE and UPDATE statements
In Transact-SQL, both the DELETE and UPDATE statements allow a FROM clause to be added, which allows joins to be included.
This example deletes all users who have been flagged with the 'Idle' flag.
DELETE users
FROM users AS u
JOIN user_flags AS f
ON u.id=f.id
WHERE f.name = 'Idle'
BULK INSERT
BULK INSERT is a Transact-SQL statement that implements a bulk data-loading process, inserting multiple rows into a table, reading data from an external sequential file. Use of BULK INSERT results in better performance than processes that issue individual INSERT statements for each row to be added.
Subscribe to:
Posts (Atom)