CS331 Exercise Questions Part 2
Embedded SQL consists of: A) hard-coded SQL statements included in a program written in another language. B) SQL encapsulated inside of other SQL statements. C) SQL written into a front-end application. D) SQL translated to a lower-level language.
A
In SQL, a(n) ________ subquery is a type of subquery in which processing the inner query depends on data from the outer query. A) correlated B) paired C) natural D) inner
A
Informational and operational data differ in all of the following ways EXCEPT: A) level of detail. B) normalization level. C) scope of data. D) data quality.
A
The MERGE command: A) allows one to combine the INSERT and UPDATE operations. B) allows one to combine the INSERT and DELETE operations. C) joins 2 tables together. D) none of the above.
A
The UNION clause is used to: A) combine the output from multiple queries into a single result table. B) join two tables together to form one table. C) find all rows that do not match in two tables. D) none of the above.
A
The ________ DBA view shows information about all users of the database in Oracle. A) DBA_USERS B) USERS C) DBA_VIEWS D) DBA_INDEXES
A
The major advantage of data propagation is: A) real-time cascading of data changes throughout the organization. B) duplication of non-redundant data. C) the ability to have trickle-feeds. D) none of the above.
A
The outer join syntax does not apply easily to a join condition of more than ________ tables. A) two B) three C) four D) any number of
A
User-defined transactions can improve system performance because: A) transactions are processed as sets, reducing system overhead. B) transactions are mapped to SQL statements. C) speed is improved due to query optimization. D) all of the above.
A
Which of the following is a basic method for single field transformation? A) Table lookup B) Cross-linking entities C) Cross-linking attributes D) Field-to-field communication
A
________ duplicates data across databases. A) Data propagation B) Data duplication C) Redundant replication D) A replication server
A
________ use the result of the outer query to determine the processing of the inner query. A) Correlated subqueries B) Outer subqueries C) Inner subqueries D) Subqueries
A
20) A database action that results from a transaction is called a(n) 1.A) transition. 2.B) event. 3.C) log entry. 4.D) journal happening.
B
24) Every key used to join the fact table with a dimension table should be a ________ key. 1.A) primary 2.B) surrogate 3.C) foreign 4.D) secondary
B
25) The level of detail in a fact table determined by the intersection of all the components of the primary key, including all foreign keys and any other primary key elements, is called the 1.A) span. 2.B) grain. 3.C) selection. 4.D) aggregation.
B
3) When we consider data in the data warehouse to be time-variant, we mean 1. A) that the time of storage varies. 2. B) data in the warehouse contain a time dimension so that they may be used to study trends and changes. 3. C) that there is a time delay between when data are posted and when we report on the data. 4. D) none of the above.
B
31) ________ is/are a new technology which trade(s) off storage space savings for computing time. 1.A) Dimensional modeling 2.B) Column databases 3.C) Fact tables 4.D) Snowflake schemas
B
32) A class of database technology used to store textual and other unstructured data is called 1.A) mySQL. 2.B) NoSQL. 3.C) KnowSQL. 4.D) PHP
B
37) Going from a summary view to progressively lower levels of detail is called data 1.A) cubing. 2.B) drill-down. 3.C) dicing. 4.D) pivoting.
B
39) Which of the following data-mining techniques searches for patterns and correlations in large data sets? 1.A) Case reasoning 2.B) Rule discovery 3.C) Signal processing 4.D) Neural nets
B
40) Which of the following data-mining applications identifies customers for promotional activity? 1.A) Population profiling 2.B) Target marketing 3.C) Usage analysis 4.D) Product affinity
B
A ________ is a temporary table used in the FROM clause of an SQL query. A) correlated subquery B) derived table C) view table D) none of the above
B
A join that is based upon equality between values in two common columns with the same name and where one duplicate column has been removed is called a(n): A) equi-join. B) natural join. C) multivariate join. D) inner join
B
All of the following are guidelines for better query design EXCEPT: A) understand how indexes are used in query processing. B) use a lot of self-joins. C) write simple queries. D) retrieve on the data that you need.
B
All of the following are new data types added in SQL:200n EXCEPT: A) BIGINT. B) BIT. C) MULTISET. D) XML.
B
All of the following are ways to consolidate data EXCEPT: A) application integration. B) data rollup and integration. C) business process integration. D) user interaction integration.
B
Conformance means that: A) data have been transformed. B) data are stored, exchanged or presented in a format that is specified by its metadata. C) data are stored in a way to expedite retrieval. D) none of the above.
B
Data governance can be defined as: A) a means to slow down the speed of data. B) high-level organizational groups and processes that oversee data stewardship. C) a government task force for defining data quality. D) none of the above
B
Data quality ROI stands for: A) return on investment. B) risk of incarceration. C) rough outline inclusion. D) none of the above
B
Data quality problems can cascade when: A) data are not deleted properly. B) data are copied from legacy systems. C) there is redundant data storage and inconsistent metadata. D) there are data entry problems.
B
Event-drive propagation: A) provides a means to duplicate data for events. B) pushes data to duplicate sites as an event occurs. C) pulls duplicate data from redundant sites. D) none of the above
B
Explicit commands to manage transactions are needed when: A) a transaction consists of just one SQL command. B) multiple SQL commands must be run as part of a transaction. C) autocommit is set to off. D) none of the above
B
Extensions defined in SQL-99 that include the capability to create and drop modules of code stored in the database schema across user sessions are called: A) stored procedures. B) Persistent Stored Modules. C) flow control modules. D) none of the above.
B
What results would the following SQL statement produce? select owner, table_name from dba_tables where table_name = 'CUSTOMER'; A) A listing of all customers in the customer table B) A listing of the owner of the customer table C) A listing of the owner of the customer table as well as customers D) An error message
B
While triggers run automatically, ________ do not and have to be called. A) trapdoors B) routines C) selects D) updates
B
13) An operational data store (ODS) is a(n) 1.A) place to store all unreconciled data. 2.B) representation of the operational data. 3.C) integrated, subject-oriented, updateable, current-valued, detailed database designed to serve the decision support needs of operational users. 4.D) small-scale data mart
C
15) All of the following are unique characteristics of a logical data mart EXCEPT 1.A) logical data marts are not physically separate databases, but rather a relational view of a data warehouse. 2.B) the data mart is always up-to-date since data in a view is created when the view is referenced. 3.C) the process of creating a logical data mart is lengthy. 4.D) data are moved into the data warehouse rather than a separate staging area.
C
21) Data that are never physically altered once they are added to the store are called ________ data. 1.A) transient 2.B) override 3.C) periodic 4.D) complete
C
A join in which rows that do not have matching values in common columns are still included in the result table is called a(n): A) natural join. B) equi-join. C) outer join. D) union join.
C
A named set of SQL statements that are considered when a data modification occurs are called: A) stored procedures. B) treatments. C) triggers. D) trapdoors.
C
An approach to filling a data warehouse that employs bulk rewriting of the target data periodically is called: A) dump mode. B) overwrite mode. C) refresh mode. D) update mode.
C
An interactive command that can be used to dynamically control a user session for appropriate integrity measures is: A) rollback. B) rollforward. C) set autocommit. D) expunge.
C
Data federation is a technique which: A) creates an integrated database from several separate databases. B) creates a distributed database. C) provides a virtual view of integrated data without actually creating one centralized database. D) provides a real-time update of shared data
C
Data quality is important for all of the following reasons EXCEPT: A) it minimizes project delay. B) it aids in making timely business decisions. C) it provides a stream of profit. D) it helps to expand the customer base.
C
EXISTS will take a value of ________ if the subquery returns an intermediate results table which contains one or more rows. A) FALSE B) 1 C) TRUE D) undefined
C
Establishing IF-THEN-ELSE logical processing within an SQL statement can be accomplished by: A) using the if-then-else construct. B) using the immediate if statement. C) using the CASE keyword in a statement. D) using a subquery.
C
In the ________ approach, one consolidated record is maintained and all applications draw on that one actual "golden" record. A) Persistent B) Identity Registry C) Federated D) Integration Hub
C
16) The real-time data warehouse is characterized by which of the following 1.A) It accepts near-real time feeds of transaction data. 2.B) Data are immediately transformed and loaded into the warehouse. 3.C) It provides near-real-time access for the transaction processing systems to an enterprise data warehouse. 4.D) All of the above
D
18) All of the following are some beneficial applications for real-time data warehousing EXCEPT 1.A) just-in-time transportation. 2.B) e-commerce. For example, an abandoned shopping cart can trigger an email promotional message. 3.C) fraud detection in credit card transactions. 4.D) data entry.
D
22) Which of the following is an objective of derived data? 1.A) ease of use for decision support systems 2.B) faster response time for user queries 3.C) support data mining applications 4.D) All of the above
D
26) Conformed dimensions allow users to do the following 1.A) share nonkey dimension data. 2.B) query across fact tables with consistency. 3.C) work on facts and business subjects for which all users have the same meaning. 4.D) all of the above.
D
27) Factless fact tables may apply when 1.A) we are tracking events. 2.B) we are tracking sales. 3.C) we are taking inventory of the set of possible occurrences. 4.D) both A and C.
D
29) All of the following are ways to handle changing dimensions EXCEPT 1.A) overwrite the current value with the new value. 2.B) for each dimension attribute that changes, create a current value field and as many old value fields as we wish. 3.C) create a new dimension table row each time the dimension object changes. 4.D) create a snowflake schema.
D
30) ________ is an ill-defined term applied to databases where size strains the ability of commonly used relational DBMSs to manage the data. 1.A) Mean data 2.B) Small data 3.C) Star data 4.D) Big data
D
35) Rotating the view of a multidimensional database for a particular data point is called data 1.A) cubing. 2.B) drill-down. 3.C) dicing. 4.D) pivoting.
D
36) Which of the following is true of data visualization? 1.A) It is easier to observe trends and patterns in data. 2.B) Correlations and clusters in data can be easily identified. 3.C) It is often used in conjunction with data mining. 4.D) All of the above
D
4) Which of the following advances in information systems contributed to the emergence of data warehousing 1.A) Improvements in database technology, particularly the relational data model 2.B) Advances in computer hardware, especially affordable mass storage and parallel computer architectures 3.C) Advances in middleware products that enabled enterprise database connectivity across heterogeneous platforms 4.D) All of the above
D
8) Operational and informational systems are generally separated because of which of the following factors 1.A) A data warehouse centralizes data that are scattered throughout disparate operational systems and makes them readily available for decision support applications. 2.B) A properly designed data warehouse adds value to data by improving their quality and consistency. 3.C) A separate data warehouse eliminates contention for resources that results when informational applications are confounded with operational processing. 4.D) All of the above.
D
A characteristic of reconciled data that means the data reflect an enterprise-wide view is: A) detailed. B) historical. C) normalized. D) comprehensive
D
A join in which the joining condition is based on equality between values in the common columns is called a(n): A) equi-join. B) unilateral join. C) natural join. D) both A and C.
D
A new set of analytical functions added in SQL:2008 is referred to as: A) OLAF functions. B) MOLAP functions. C) average functions. D) OLAP functions.
D
A procedure is: A) stored within the database. B) given a unique name. C) called by name. D) all of the above.
D
A technique using artificial intelligence to upgrade the quality of raw data is called: A) dumping. B) data reconciliation. C) completion backwards updates. D) data scrubbing.
D
An operation to join a table to itself is called a: A) sufficient-join. B) inner join. C) outer join. D) self-join.
D
Data may be loaded from the staging area into the warehouse by following: A) SQL Commands (Insert/Update). B) special load utilities. C) custom-written routines. D) all of the above.
D
High quality data are data that are: A) accurate. B) consistent. C) available in a timely fashion. D) all of the above.
D
If the DBA wishes to describe all tables in the database, which data dictionary view should be accessed in Oracle? A) dba_tab_privs B) dba_tab_comments C) dba_table_label D) dba_tables
D
Loading data into a data warehouse involves: A) appending new rows to the tables in the warehouse. B) updating existing rows with new data. C) purging data that have become obsolete or were incorrectly loaded. D) all of the above.
D
One characteristic of quality data which pertains to the expectation for the time between when data are expected and when they are available for use is: A) currency. B) consistency. C) referential Integrity. D) timeliness.
D
TQM stands for: A) Thomas Quinn Mann, a famous data quality innovator. B) Total Quality Manipulation. C) Transforming Quality Management. D) Total Quality Management.
D
The ________ clause is used to combine the output from multiple queries into a single result table. A) INTERSECT B) DIVIDE C) COLLATE D) UNION
D
The process of combining data from various sources into a single table or view is called: A) extracting. B) updating. C) selecting. D) joining.
D
The process of transforming data from a detailed to a summary level is called: A) extracting. B) updating. C) joining. D) aggregating.
D
User-defined data types: A) can be a subclass of a standard type. B) can behave as an object. C) can have defined functions and methods. D) can have all of the above.
D
There is a special operation in SQL to join a table to itself.
False
ETL is short for Extract, Transform, Load.
TRUE
The uncontrolled proliferation of spreadsheets, databases and repositories leads to data quality problems.
TRUE
An SQL query that implements an outer join will return rows that do not have matching values in common columns
True
Quality data can be defined as being: A) unique. B) inaccurate. C) historical. D) precise.
A
All of the following are part of the coding structure for triggers EXCEPT: A) event. B) condition. C) selection. D) action.
C
RANK and DENSE-RANK are examples of: A) ceilings. B) door functions. C) window functions. D) moving functions.
C
The most commonly used form of join operation is the: A) outer join. B) union join. C) equi-join. D) natural join.
D
Which of the following are key steps in a data quality program? A) Conduct a data quality audit B) Apply TQM principles and practices C) Estimate return on investment D) All of the above
D
Which type of index is commonly used in data warehousing environments? A) Join index B) Bit-mapped index C) Secondary index D) Both A and B
D
50) Independent data marts do not generally lead to redundant data and efforts
FALSE
A data governance committee is always made up of high-ranking government officials.
FALSE
A data stewardship program does not help to involve the organization in data quality.
FALSE
Dirty data saves work for information systems projects.
FALSE
Generally, records in a customer file never become obsolete.
FALSE
Using an outer join produces this information: rows that do not have matching values in common columns are not included in the result table.
False
47) An independent data mart is filled with data extracted from the operational environment without the benefit of a data warehouse
TRUE
In order to find out what customers have not placed an order for a particular item, one might use the NOT qualifier along with the IN qualifier.
True
When EXISTS or NOT EXISTS is used in a subquery, the select list of the subquery will usually just select all columns as a placeholder because it doesn't matter which columns are returned.
True
10) One characteristic of independent data marts is complexity for end users when they need to access data in separate data marts. This complexity is caused by not only having to access data from separate databases, but also from 1.A) the possibility of a new generation of inconsistent data systems, the data marts themselves. 2.B) lack of user training. 3.C) denormalized data. 4.D) incongruent data formats.
A
19) Data that are detailed, current, and intended to be the single, authoritative source of all decision support applications are called ________ data. 1.A) reconciled 2.B) subject 3.C) derived 4.D) detailed
A
2) The characteristic that indicates that a data warehouse is organized around key high-level entities of the enterprise is 1. A) Subject-oriented 2. B) integrated. 3. C) time-variant. 4. D) nonvolatile.
A
28) An expanded version of a star schema in which all of the tables are fully normalized is called a(n) 1.A) snowflake schema. 2.B) operational schema. 3.C) DSS schema. 4.D) complete schema.
A
34) OLAP tools that use the database as a traditional relational database are called 1.A) ROLAP tools. 2.B) MOLAP tools. 3.C) slice and dice. 4.D) none of the above.
A
5) Which of the following factors drive the need for data warehousing 1.A) Businesses need an integrated view of company information. 2.B) Informational data must be kept together with operational data. 3.C) Data warehouses generally have better security. 4.D) None of the above.
A
7) Informational systems are designed for all of the following EXCEPT 1.A) running a business in real time. 2.B) supporting decision making. 3.C) complex queries. 4.D) data mining.
A
Dynamic SQL: A) is used to generate appropriate SQL code on the fly as an application is processing. B) is quite volatile. C) is not used widely on the Internet. D) creates a less flexible application
A
1) The analysis of summarized data to support decision making is called 1. A) operational processing. 2. B) informational processing. 3. C) artificial intelligence. 4. D) data scrubbing.
B
12) A dependent data mart 1.A) is filled with data extracted directly from the operational system. 2.B) is filled exclusively from enterprise data warehouse with reconciled data. 3.C) is dependent upon an operational system. 4.D) participates in a relationship with an entity.
B
14) A logical data mart is a(n) 1.A) data mart consisting of only logical data. 2.B) data mart created by a relational view of a slightly denormalized data warehouse. 3.C) integrated, subject-oriented, detailed database designed to serve operational users. 4.D) centralized, integrated data warehouse.
B
A method of capturing only the changes that have occurred in the source data since the last capture is called ________ extract. A) static B) incremental C) partial D) update-driven
B
A type of join where a table is joined to itself is called a(n): A) unary join. B) self-join. C) unnatural join. D) pinned join.
B
A type of query that is placed within a WHERE or HAVING clause of another query is called a: A) master query. B) subquery. C) superquery. D) multi-query
B
External data sources present problems for data quality because: A) data are not always available. B) there is a lack of control over data quality. C) there are poor data capture controls. D) data are unformatted.
B
In order for two queries to be UNION-compatible, they must: A) both have the same number of lines in their SQL statements. B) both output compatible data types for each column and return the same number of rows. C) both return at least one row. D) all of the above.
B
In order to embed SQL inside of another language, the ________ statement must be placed before the SQL in the host language. A) GET SQL B) EXEC SQL C) RUN SQL D) SQL SQL
B
One major advantage of the outer join is that: A) information is easily accessible. B) information is not lost. C) the query is easier to write. D) all of the above.
B
One simple task of a data quality audit is to: A) interview all users. B) statistically profile all files. C) load all data into a data warehouse. D) establish quality metrics
B
The methods to ensure the quality of data across various subject areas are called: A) Variable Data Management. B) Master Data Management. C) Joint Data Management. D) Managed Data Management.
B
A join operation: A) brings together data from two different fields. B) causes two tables with a common domain to be combined into a single table or view. C) causes two disparate tables to be combined into a single table or view. D) is used to combine indexing operations.
B) causes two tables with a common domain to be combined into a single table or view.
) In which of the following situations would one have to use an outer join in order to obtain the desired results? A) A report is desired that lists all customers who placed an order. B) A report is desired that lists all customers and the total of their orders. C) A report is desired that lists all customers, the total of their orders during the most recent month, and includes customers who did not place an order during the month (their total will be zero). D) There is never a situation that requires only an outer join.
C
17) ________ technologies are allowing more opportunities for real-time data warehouses 1.A) Web 2.B) MOLAP 3.C) RFID 4.D) GPS
C
23) A star schema contains both fact and ________ tables. 1.A) narrative 2.B) cross functional 3.C) dimension 4.D) starter
C
33) The use of a set of graphical tools that provides users with multidimensional views of their data is called 1.A) on-line geometrical processing (OGP). 2.B) drill-down analysis. 3.C) on-line analytical processing (OLAP). 4.D) on-line datacube processing (ODP).
C
38) Which of the following data-mining techniques identifies clusters of observations with similar characteristics? 1.A) Case reasoning 2.B) Rule discovery 3.C) Clustering and signal processing 4.D) Neural nets
C
6) Which of the following organizational trends does not encourage the need for data warehousing 1.A) Multiple, nonsynchronized systems 2.B) Focus on customer relationship management 3.C) Downsizing 4.D) Focus on supplier relationship management
C
9) A data mart is a(n) 1.A) enterprisewide data warehouse. 2.B) smaller system built upon file processing technology. 3.C) data warehouse that is limited in scope. 4.D) generic on-line shopping site.
C
One way to improve the data capture process is to: A) allow all data to be entered manually. B) provide little or no training to data entry operators. C) check entered data immediately for quality against data in the database. D) not use any automatic data entry routines.
C
SQL provides the ________ technique, which involves placing an inner query within the WHERE or HAVING clause of an outer query. A) grouping B) joining C) subquery D) union
C
SQL-invoked routines can be: A) procedures. B) functions. C) all of the above. D) none of the above.
C
The best place to improve data entry across all applications is: A) in the users. B) in the level of organizational commitment. C) in the database definitions. D) in the data entry operators.
C
________ differs from array because it can contain duplicates. A) BIGINT B) XML C) MULTISET D) TABLE
C
________ takes a value of true if a subquery returns an intermediate results table which contains one or more rows. A) IN B) HAVING C) EXISTS D) EXTENTS
C
11) All of the following are limitations of the independent data mart EXCEPT 1.A) separate extraction, transformation, and loading processes are developed for each data mart. 2.B) data marts may not be consistent with one another. 3.C) there is no capability to drill down into greater detail in other data marts. 4.D) it is often more expedient to build a data mart than a data warehouse.
D
All of the following are advantages of SQL-invoked routines EXCEPT: A) flexibility. B) efficiency. C) sharability. D) security.
D
All of the following are popular architectures for Master Data Management EXCEPT: A) Identity Registry. B) Integration Hub. C) Persistent Object. D) Normalization.
D
All of the following are tasks of data cleansing EXCEPT: A) decoding data to make them understandable for data warehousing applications. B) adding time stamps to distinguish values for the same attribute over time. C) generating primary keys for each row of a table. D) creating foreign keys.
D
42) The development of the relational data model did not contribute to the emergence of data warehousing
FALSE
44) When multiple systems in an organization are synchronized, the need for data warehousing increases
FALSE
46) A separate data warehouse causes more contention for resources in an organization
FALSE
48) A data mart is a data warehouse that contains data that can be used across the entire organization
FALSE
Quality data are not essential for well-run organizations.
FALSE
Quality data does not have to be unique.
FALSE
Retention refers to the amount of data that is not purged periodically from tables.
FALSE
Total quality management (TQM) focuses on defect correction rather than defect prevention.
FALSE
A subquery in which processing the inner query depends on data from the outer query is called a codependent query.
False
An equi-join is a join in which one of the duplicate columns is eliminated in the result table.
False
EXISTS takes a value of false if the subquery returns an intermediate result set.
False
One major disadvantage of the outer join is that information is easily lost.
False
The following query will execute without errors. select customer.customer_name, salesman.sales_quota from customer where customer.salesman_id = (select salesman_id where lname = 'SMITH');
False
The natural join is very rarely used.
False
41) Advances in computer hardware, particularly the emergence of affordable mass storage and parallel computer architectures, was one of the key advances that led to the emergence of data warehousing
TRUE
43) The need for data warehousing in an organization is driven by its need for an integrated view of high-quality data
TRUE
45) Informational systems are designed to support decision making based on historical point-in-time and prediction data
TRUE
49) Organizations adopt data mart architectures because it is easier to have separate, small data warehouses than to get all organizational parties to agree to one view of the organization in a central data warehouse
TRUE
A data quality audit helps an organization understand the extent and nature of data quality problems.
TRUE
A data steward is a person assigned the responsibility of ensuring the organizational applications properly support the organization's enterprise goals for data quality.
TRUE
Completeness means that all data that are needed are present.
TRUE
Data quality is essential for SOX and Basel II compliance.
TRUE
A correlated subquery is executed once for each iteration through the outer loop.
True
A join in which the joining condition is based on equality between values in the common column is called a(n) equi-join.
True
A natural join is the same as an equi-join, except that it is performed over matching columns that have been defined with the same name, and one of the duplicate columns is eliminated.
True
Joining tables or using a subquery may produce the same result.
True
The following SQL statement is an example of a correlated subquery. select first_name, last_name, total_sales from salesman s1 where total_sales > all (select total_sales from salesman s2 where s1.salesman_id != s2.salesman_id);
True
The following queries produce the same results. select customer_name, customer_city from customer, salesman where customer.salesman_id = salesman.salesman_id and salesman.lname = 'SMITH'; select customer_name, customer_city from customer where customer.salesman_id = (select salesman_id from salesman where lname = 'SMITH');
True
The joining condition of an equi-join is based upon an equality.
True