TopicQuestions Option (A) Option (B)
DM
A data warehouse is which of the
following? Can be updated by end users
Contains numerous naming conventions and formats.
DM
A star schema has what type of relationship
between a dimension and fact table? Many-to-many One-to-one
DM Fact tables are which of the following? Completely demoralized Partially demoralized DM
A snowflake schema is which of the
following types of tables? Fact Dimension
DM
A goal of data mining includes which of the following?
To explain some observed event
or condition To confirm that data exists DM
OLAP databases are called decision support
system ? TRUE FALSE
DM In Star Schema Dimension tables are Short and Fat Long and Thin DM The data in Data Warehouse is generally Clean Data Dirty Data
DM Choose two
Ralph Kimball believes that portions of data can be
combined based on relevance of data and can be used for reporting
Inmon believes that portions of data can be combined based on relevance of data and can be used for reporting
DM
In which type of SCD(Slowly changing
dimensions) do we preserve history of data: Type One Type Two
ETL During ETL load we generally have Unsorted data for Aggregator Sorted data for Aggregator
DM
Sequence of jobs to load data in to warehouse
First load data into fact tables then dimension tables, then Aggregates if any
First load data into dimension tables, then fact tables, then Aggregates if any
DM Snowflaking means Normalizing the data Denormalizing the data
OLAP
Drill Across generally use the following
join to generate report Self Join Inner Join
DM In general data in Data Warehousing is Normalized Denormalized DM Consolidated data mart is First level data mart Second level data mart DM
In 4 step dimensional process, declaring
gain of business process is First Step Second Step
DM Dimensions are Confirmed when They are different
They are either same or one is subset of another
DM
You need to create an index on the SALES table, which is 10 GB in size. You want your index to be spread across many tablespaces, decreasing contention for index lookup, and increasing scalability and manageability.Which type of index would
be best for this table? bitmap unique
DM Which of the following statements is true?
A data warehouse is useful to all organisations that currently use OLTP's
A data warehouse is valuable only if the organisation has an interest in analysing historical data.
DM Analytical processing is
the act of using software to analyse highly consolidated data, often to view the changes over time.
the act of exporting data into a spreadsheet for analysis
DM Which of the following statements is true?
The fact table of a data warehouse is the main store of descriptions of the transactions stored in a DWH
The fact table of a data warehouse is the main store of all of the recorded transactions over time.
DM
Which of the following is associated with a
data warehouse A relation A flat file
DM Which of the following statements is true?
The more data a data warehouse has, the better it is
A data warehouse
automatically makes a copy of every transaction recorded in an OLTP system
DM A data warehouse
must import data from
transactional systems whenever significant changes occur in the transactional data.
takes regular copies of transaction data
DM Granularity refers to
The number of fact tables in a data warehouse
The level of detail of the data descriptions held in a data warehouse
DM Dimensionality refers to
The level of detail of data that is held in the fact table
The data that describes the transactions in the fact table.
DM
The main organisational justification for implementing a data warehouse is to provide
lagre scale transaction processing
Cheaper ways of handling transactions
OLAP OLTP stands for On Line Transaction Processing On Line Terminal Protocol DM Data in a data warehouse in a flat file format
must be in normalised form to at least 3NF
DM A data warehouse needs to be time varient Subject orientated
DM Transaction processing is
the act of processing individual transactions
the act of analysing each transaction to verify that it is valid
OLAP OLAP stands for On Line Analytical Protocol
On Line Abstraction Processing
DM
What is a formal way to express data relationships to a database management
system? Attributes Entity identifier
DM
What is a technique for documenting the relationships between entities in a database
environment? Attributes Entity identifier
DM
What indicates having the potential to contain more than one value for an attribute
at any given time? Constraint Single-valued
DM
Which relationship is between two entities in which an instance of entity A can be related to zero, one, or more instances of entity B and entity B can be related to zero,
DM
Which of the following uses a series of logically related two-dimensional tables or files to store information in the form of a
database? Database Database management system
OLAP
All of the following terms describe OLAP, except
The gathering of input
information Processing input information DM
Which tool is used to help an organization
build and use business intelligence? Data warehouse Data mining tools DM What does the data dictionary identify? Field names Field types
DM
Which of the following is a data
manipulation tool? File generators Query by example tool
DM
When gathering business information requirements, you should focus only on the requirements provided by the business
groups. TRUE FALSE
OLAP
One difference between the design of online transaction processing (OLTP) and online analytical processing (OLAP) systems is that the OLTP system design is
optimized for getting data into the database. TRUE FALSE
DM
Designing a data warehouse in first normal
form (1NF) is not recommended. TRUE FALSE
DM
Cardinality is defined as the number of
relationships existing between entities. TRUE FALSE
DM
It is not important to include metadata when
designing a data warehouse. TRUE FALSE
DM
There is no need to include a time
dimension in the data warehouse. TRUE FALSE
DM
The level of granularity you choose for the time dimension has no significant impact on
the size of your database. TRUE FALSE
DM
Surrogate keys are generated on tables in the data warehouse after the table is
populated. TRUE FALSE
DM
To improve performance, all tables in the
data warehouse should be indexed. TRUE FALSE
DM
Fact tables are often referred to as the
measures of business performance. TRUE FALSE
DM
Dimension tables are used to provide descriptions of the business subjects and descriptive information about each row in
the fact table. TRUE FALSE
DM
A high level of granularity means more detail; a low level of granularity mean less
detail. TRUE FALSE
DM
One method of managing the history in dimension tables is to drop the dimension
DM
You do not need to be concerned with maintaining the history of changing data in
the dimension tables. TRUE FALSE
DM
Effective use of summaries is the best technique for improving performance in
data warehouses. TRUE FALSE
DM
Summary data cannot be combined with
detailed fact data. TRUE FALSE
DM
When choosing a level of summarization, there are two approaches: summarizing the entire dimension, or summarizing part of the dimension and partially improving
performance TRUE FALSE
DM
Table partitioning splits the storage of a
table into smaller individual units. TRUE FALSE
DM
Denormalization is the factor that increases
the sparseness in a database. TRUE FALSE
OLAP
What are the actual data values that occupy the cells as defined by the dimensions
selected? Nesting Aggregation
OLAP
The term that defines filtering data in an
OLAP cube is ___________ . dicing slicing
OLAP
What is an item that matches a specific
description or classification? Category Measure
OLAP
The cube structure in OLAP achieves the
__________ functionality. shared information
OLAP
Aggregation provides OLAP with
__________ multidimensional data pre-calculated data
OLAP What is the acronym that defines OLAP? FHTMI FASMI
OLAP
__________ in OLAP allows you to define
a subcube of the original space. Dicing Slicing
OLAP
What term in OLAP defines changing the dimensional orientation of the report from
the cube data? Dicing Slicing
OLAP
The _________ in OLAP enable you to drill-up or drill-down to view different
levels of detail about your data. dimensions measures
OLAP When you nest in OLAP, you _________ .
select multiple cube
aggregations select multiple cube measures
ETL Which of the following describes ETL?
A process that transforms information using a common set of enterprise definitions
A process that loads information into a data warehouse
DM What is data mining
A particular attribute of information
The common term for the representation of
DM
A collection of related data fields is called a
____. byte record
DM A DBMS is a(n) ____.
interface between the database
and application programs data repository
DM
A(n) ____ is a generalized class of people, places, or things for which data is collected,
stored, and maintained record entity
DM
Which attribute would make the best
primary key? Social security number Last name
DM
The ____ data model follows a treelike
structure. distributed hierarchical
DM
The most popular database model currently
in use is the ____. relational model hierarchical model
DM
A primary key is a field or set of fields that
uniquely identifies a record. TRUE FALSE
DM
One of the goals of a DBMS is to increase data redundancy thereby making it less
vulnerable to hackers. TRUE FALSE
DM
A Data Warehouse would most likely be
part of a(n) ERP System Small MIS System
DM Data Mining would most likely be used
to streamline a Transaction
Processing System to model data in a DBMS
DM Which of the following is a valid key field A Book Title House number + Street Name
DM A Table Can only store data of one type Consists of Alphanumeric data
DM
A RDBMS cannot store data without knowing the data type. Which of the following statements are true?
A Logical data type can store three values, TRUE, UNKNOWN and FALSE
Numerical data can be stored in different formats
DM
A FLAT FILE database management system is
A database design that only has one table in it
A DBMS that can only have simple data tables in it
DM
Assume you are extending the design of The College Student Records System to include details on each classroom. The college is never likely to have more than ten classrooms and definitely not ever going to have more than 25 classrooms. What data
type would you select Numeric - Byte Numeric - Single
OLAP A report must
be exported to a word processor for printing
be based on an underlying data source (a table or a query)
OLAP
The layout of a report is independant of the
number of records held in a table or query True FALSE
OLAP A report is used to
produce output that is ready for e-mailing
produce output that is ready for publication on the Web (HTML)
DM
The rule that prohibits transitive
DM
The rule that requires that each non-key field (attribute) should be fully functionally
dependent on the primary key is Third Normal Form First Normal Form
DM
The rule that specifies that there should be no repeating fields and that fields should be
atomic is third normal form second normal form
DM
The process of combining two tables in a
relational database is known as a Join a Combine
DM The ER model is meant to
enable low level descriptions of
data replace relational design
DM The Entity Relation Model models Entities Relationships
DM
Which of the following statements best decribes the function of an entity relation model?
An ER model provides a view of the logic of the data and not the physical implementation.
An ER model is concerned primarily with a physical implementation of the data and secondly with the logical view DM SQL stands for Sequential Question Language Structured Query Language
DM
Which of the following are elements of
SQL? Data Query Language Data Definition Language
DM
Consider the table (STUDREC). Which of the following statements will list columns INIT, SNAME, GENDER and KIDS (in that order) for all students who have more then 1 child.
SELECT init, sname, gender, kids FROM studrec WHERE kids <1;
SELECT init, sname, gender, kids FROM studrec WHERE kids >1;
OLAP A typical data warehouse consists of … Staging area Data Marts
OLAP
What are the three layers of Data warehouse architecture?
Data staging layer, Data Extract layer, Data transactional layer
Data Modelling layer, Data Accesses layer, Data Storage layer
OLAP Staging Area comes under which layer? Data Storage layer Data Access layer
OLAP
What are Limitations of Traditional
techniques ? Extensive programming Redundant reporting
OLAP Different categories of Data Access are? Web Access Data Mining
OLAP OLAP stands for Online Access Processing Online Analytic Processing
OLAP
A process that uses a variety of statistical and artificial intelligence frameworks to
discover patterns and relationships in data Data Access Process Data Mining Process
OLAP
A category of data access solutions in which information is viewed through a web
browser Data Access Process Data Mining Process
OLAP What is importance of Data Access?
Businesses today face challenges like
Data Access is the ‘last mile’ that enables decision makers to
OLAP What are different types of reporting? Transaction Systems Reporting
Enterprise Data Warehouse Reporting
OLAP
In Transaction Systems Reporting, Reporting Tool has a native connectivity to
? Views Tables
OLAP
An enterprise data warehouse (EDW) is designed to
To combine data from multiple OLTP systems
To provide consolidated and cleansed data to an array of data marts
OLAP Examples of Managed Query Tool Business Objects MS Query OLAP Which are the OLAP features ?
Multidimensional viewing Capabilities
Time Intelligence - Time Series analysis
OLAP OLAP system is Decision support
Relatively standardized and simple queries returning relatively few records
OLAP What is measure? Is not a number represents factual data
OLAP What is ROLAP?
Data is stored in multidimensional cubes
Support for large databases with good performance
SQL Which one is DDL command? Insert Update
SQL
How many types of Normalization rules are
there? 4 5
SQL Which are pseudocolumns CURRVAL NEXTVAL
SQL
Can you use select in FROM clause of SQL
select ? YES NO
SQL
Describe the use of %ROWTYPE in PL/SQL ?
It allows you to associates a variable with a single column type
It allows you to associate a variable with an entire table row
SQL How many types of triggers are there? 9 10
SQL
What is the default ordering of an ORDER
BY clause in a SELECT statement? Descending Ascending
SQL Union All returns
All rows selected by either query
All rows selected by either query and including duplicates
ETL What is ETL process?
ETL is the set of processes by which data is extracted from various sources, transformed and loaded into target systems
ETL is the set of processes by which data is extracted from various sources and loaded into target systems ETL What is Importance of ETL?
Closely integrated with RDBMS’s
High speed loading of target data warehouses
ETL Which are ETL Activities ?
Data Extraction, Data transformation, Data loading
Data Extraction, Data Extraction – Cleanup, Data loading
ETL Data Extraction Methods are Incremental Extraction Real Time Extraction ETL Which are the examples of ETL tools? Informatica PowerCenter Ab Initio
ETL What is Bulk Load?
Format of Archived data different from operational data
It limits your ability to recover because no database logging occurs
ETL
What do you mean by Source alteration stage in ETL ?
perform a variety of
transformations unique to the source, depending on business requirements
performs the access and extraction of data from the source system and builds a temporal view of the data at the time of extraction ETL
What are the different types of Commit
intervals? Target-based commit Source-based commit
ETL Which is the first step of the ETL process ? Data Extraction – Cleanup Data Extraction
ETL Which is not pros of Batch Extraction ?
Quick and relatively easy to write scripts for doing exports and imports
Does not usually require additional hardware ETL
Which tool does not support
Change-Data-Capture Feature ? Ascential Data Stage XE Informatica PowerCenter
DW What is Data Warehouse ?
Data Warehouse is integarted of data in support of management's decisions
A data warehouse is a subject-oriented, integrated,
nonvolatile, time-variant collection of data in support of management's decisions DW What is the Need of Data Warehousing ? To store Operational Data
Better business intelligence for end-users
DW
Which one is not Characteristic of Data
Mart ? Restrictive, non extensible Short life/tactical
DW
Which is the information need for recent
data ? ODS OLTP
DW
What type of Data Structure Characteristic
does Data Warehousing has ? Detailed Summarized
DW
What are Components of a Data Warehouse
Architecture ? Data Cleansing tool ETL tool
DW What is use of Data Cleaning Tools ?
Clean up source data in-place on the host
Generate and maintain centralized metadata DW What is the use of Data Mining Tools ? Slice and Dice What If analysis
DM What is Database ?
A known fact that can be recorded and that have implicit meaning
The data is perceived by the user as tables
DM What is Data Model ?
A collection of concepts that can be used to describe the structure of a database
Representation of a set of business requirements in a standard structured framework understood by the users DM
Which Data Modelling approach suit for
corporate data Warehouse ? Dimensional Approach Entity Relational Approach DM
What are the different types of relationship
notations ? IEX IDFIX
DM What is Physical Data Model ? Conceptual
Geared for performance and may consists of redundant data DM What are different types of Data Model ? Physical model, Logical model, Hybrid model
DM
Can we have multiple foreign keys in a
Option (C) Option (D)
Answer s Organized around important
subject areas. Contains only current data. C
One-to-many All of the above B
Completely normalized Partially normalized C
Helper All of the above D
To analyze data for expected
relationships To create a new data warehouse A A
Long and Fat Short and thin A
Clean and Dirty Data None of above A
Inmon believes that DW is built and should be used for
reporting.
Ralph Kimball believes that DW is built and should be used for
reporting. B and D
Type Three None of above B
Does not matter if we use Sorted or Unsorted data for
Aggregation None of the Above B
First Aggregates then load data into dimension tables, then fact tables
Does not matter if we load either of fact, dimensions, or
aggregates B
None of Above A
Outer Join None of the Above C
None of Above C
All of these None of Above B
Third Step Fourth Step B
When they can be compared
mathematically None of these B
partitioned reverse Key C
A data warehouse is valuable to thiose organisations that need to keep an audit trail of their activities
A data warehouse is necessary to all those organisations that
the act of using a relational database to produce reports giving data summaries on a regular basis (e.g. monthly)
the act of sumarising data on a regular basis (e.g. month end
summaries) A
A fact table describes the granularity of data held in a DWH
A fact table describes the
transactions stored in a DWH B A hierachical and/or network
structure A star schema D
A data warehouse is a relatively straighttforward thing to set up.
Adding data for the sake of it may well degrade the effectiveness of data
warehouseing analysis D
takes regular copies of
transaction data and stores it in a way that is optimised for query and reporting
has to work on live transactional data to provide up to date and
vaild results C
The level of detail of the data stored in a data warehouse.
The number of dimensions in a
data warehouse C
The level of detail that is held in the Data Warehouse
The number of dimension tables that exist in a star schema B
Storing large volumes of data Decision support D On Line Terminal Processing On Line Transaction Protocol A can be normalised but often isn't
must be in normalised form to at
least 2NF C
Capable of integrating data from
a wide variety of sources non-volatile A,B,C,D
the act of analysing transactions on a regular basis (e.g. monthly)
the act of processing, recording and storing individual
transactions in a database D On Line Abstraction Protocol On Line Analytical Processing D
Data model Entity-relationship diagram C
Data model Entity-relationship diagram D
All of the above None of the above D
Data warehouse None of the above D Updating existing information to
reflect to the gathered and
processed information None of the above D
Database management systems All of the above D
Field formats All of the above D
Structure question language All of the above B
B A B A B B B B B A A A B
B A B A A A Dimensions Measures D rotating nesting B Dimension Nest A collection multidimensional D
nested data slow data retrieval B
ASFMI MASHF B
Rotating Nesting A
Rotating Nesting C
nesting aggregation A
select multiple cube dimensions select multiple cube slices C A process that extracts
information from internal and
external databases All of the above D
The process of analyzing data to extract information not offered by the raw data alone
Uses a variety of techniques to find patterns and relationships in large volumes of information and infer rules from them that predict future behavior and
character bit B
knowledge base unique group of records A
attribute file B
First name Age A
network relational B
network model object model A
A
B
DBMS Expert system A
to help transform data into useful information that can be used by a DSS
to help transform data from different sources so that they can be stored in a single Data
Warehouse. C
Car Registration number
Initials + Family Name + Date
of Birth C
Consists of Rows and Columns Cannot be empty B Some DBMS's can use DATE
data types
A Character (Text) data type can contain 0,1,2,3,4,5,6,7,8 and 9 B,C,D A DBMS that can only have one
table in it
A DBMS that contains records that have a large number of
fields in them C
Numeric - Integer Numeric - Long integer A
Be redefined each time it is used be password protected B
A
produce output that is formatted for display on a computer screen
produce output formatted for
print D
Second Normal Form None of the Above C
first normal form None of the Above C
a Relate a Construc A
be close to a users perception of the data
enable detailed descriptions of
data query processing C
Entities, Relationships and
Processes Entities and Relationships D
An ER model is entirely concerned with modelling the physical implemetation
An ER model is concerned primarily with a logical view of the data and secondly with the physical implementation A Structured Question Language Sequential Query Language B
Data Modification Language Data Manipulation Language A,B,D
SELECT init, sname, gender, kids FROM studrec WHERE kids >'1';
SELECT init sname, gender, kids FROM studrec WHERE
kids >1; D
Analytical environment All of the above A Data Extraction layer, Data
Accesses layer, Data Storage
layer None C
Data Extract layer None D
SQL does not have a natural way of providing flexible view reorganizations that will
transpose the data All of the above D
Both A and B None C
Both A and B None B
Web Access Process None B
Web Access Process Reporting C
Both A and B None C
OLAP OLTP D
Both A and B None C
Microsoft Access All of the above A
Only A Both A and B D
Both A and B None A
description of subject Both B and C B
Good to access pre-aggregated data
Compilation intensive
architecture B
Drop Select C
6 7 B
ROWID All of the above D
A It allows you to associate a
variable with an entire table
column Both A and C B
11 12 D
B All distinct rows selected by
both queries
All rows selected by the first
query but not the seconds B
Both A and B None A
Both A and B Only A C
Data Extraction, Data loading
Data Extraction, Data
transformation, Data Extraction – Cleanup, Data loading D
Full Extraction All of the above D
Business Objects Both A and B D
Lengthy and Complex process All of the above B
performs final formatting of data to produce load-ready files for the target table; identifies and segregates rows to be inserted vs. updated (if applicable); applies remaining technical meta data tagging; and processes data into the RDBMS
final stage, uses the load- ready files from Stage 4 to build aggregation tables needed to improve query performance
against the warehouse A
Only A Both A and B D
Data transformation Data loading B
Not event driven--does not facilitate notification or change in another application at the time of a change in first application
Almost all applications provide utilities for exporting and
importing C
Ab Initio All of the above B
Both A and B None B
Used by Operational users Both B and C B
Project Orientation Flexible, extensible D
OLAP All of the above A
Detailed and Summarized Detailed and lightly summarized C
Data Modelling tool All of the above D
Automatic generation of data
extract programs All of the above A
Dill Down Static Reports B
It is designed, built, and populated with data for a
specific purpose All of the above C
Both A and B None A
Both A and B None B
IE Both B and C C
Both A and B None B
