DRM study guide 2

What will be returned when the following SQL query is executed? Select driver_no, count(*) as num_deliveries from deliveries group by driver_no having count(*) > 2; Question options: A listing of all drivers A listing of all drivers who made more than 2 deliveries as well as a count of the number of deliveries A listing of all drivers who made more than 2 deliveries A listing of the number of deliveries greater than 2

A listing of all drivers who made more than 2 deliveries as well as a count of the number of deliveries

What will be returned when the following SQL statement is executed? Select driver_no, count(*) as num_deliveries from deliveries where state = 'MA' group by driver_no; Question options: A listing of each driver who made deliveries to state = 'MA' as well as the number of deliveries that each driver has made to that state A listing of all drivers who made deliveries to state = 'MA', sorted by driver number A count of all of the deliveries made to state = 'MA' by all drivers None of the above

A listing of each driver who made deliveries to state = 'MA' as well as the number of deliveries that each driver has made to that state

In which of the following situations would one have to use an outer join in order to obtain the desired results? Question options: A report is desired that lists all customers and the total of their orders. A report is desired that lists all customers who placed an order. 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). There is never a situation that requires only an outer join.

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).

A(n) ________ is a set of application routines that programs use to direct the performance of procedures by the computer's operating system. Question options: LAN RPC MOM API

API

Which of the following advances in information systems contributed to the emergence of data warehousing? Question options: Advances in computer hardware, especially affordable mass storage and parallel computer architectures Improvements in database technology, particularly the relational data model Advances in middleware products that enabled enterprise database connectivity across heterogeneous platforms All of the above

All of the above

Which of the following have affected the way businesses use computing systems to meet the demand of the competitive marketplace? Question options: Networking advances GUI evolutions Communication changes All of the above

All of the above

Which of the following is an objective of derived data? Question options: support data mining applications faster response time for user queries ease of use for decision support systems All of the above

All of the above

Operational and informational systems are generally separated because of which of the following factors? Question options: A properly designed data warehouse adds value to data by improving their quality and consistency. A data warehouse centralizes data that are scattered throughout disparate operational systems and makes them readily available for decision support applications. A separate data warehouse eliminates contention for resources that results when informational applications are confounded with operational processing. All of the above.

All of the above.

The real-time data warehouse is characterized by which of the following? Question options: Data are immediately transformed and loaded into the warehouse. It accepts near-real time feeds of transaction data. It provides near-real-time access for the transaction processing systems to an enterprise data warehouse. All of the above.

All of the above.

Which of the following are key steps in a data quality program? Question options: Apply TQM principles and practices. Conduct a data quality audit. Estimate return on investment. All of the above.

All of the above.

Which of the following is true of data visualization? Question options: It is often used in conjunction with data mining. It is easier to observe trends and patterns in data. Correlations and clusters in data can be easily identified. All of the above.

All of the above.

What does the following SQL statement do? Alter Table Customer_T Add (Type Varchar (2)); Question options: Alters the Customer_T table to accept Type 2 Varchars Alters the Customer_T table, and adds a field called "Type" Alters the Customer_T table to be a Type 2 Varchar Alters the Customer_T table by adding a 2-byte field called "Varchar"

Alters the Customer_T table, and adds a field called "Type"

________ is the process of assigning pieces of application code to clients or servers. Question options: Modularizing programs Program breakup Code distribution Application partitioning

Application partitioning

________ is an ill-defined term applied to databases where size strains the ability of commonly used relational DBMSs to manage the data. Question options: Mean data Big data Small data Star data

Big data

Which of the following factors drive the need for data warehousing? Question options: Businesses need an integrated view of company information. Data warehouses generally have better security. Informational data must be kept together with operational data. None of the above.

Businesses need an integrated view of company information.

What does the following SQL statement do? Update Product_T Set Unit_Price = 775 Where Product_ID = 7 Question options: Changes the length of the Unit_Price field to 775 Changes the price of a unit called Product_T to 7 Updates the Product_T table to have a unit price of 775 Changes the unit price of Product 7 to 775

Changes the unit price of Product 7 to 775

________ is/are a new technology which trade(s) off storage space savings for computing time. Question options: Dimensional modeling Snowflake schemas Fact tables Column databases

Column databases

Which of the following is NOT a method for storing XML documents? Question options: Use a BLOB or CLOB to store the entire XML file Use an XML native database Convert to text Shredding

Convert to text

________ is a set of commands used to control a database, which includes security. Question options: DPL DML DCL DDL

DCL

________ is a set of commands used to update and query a database. Question options: DCL DDL DPL DML

DML

In order to embed SQL inside of another language, the ________ statement must be placed before the SQL in the host language. Question options: SQL SQL RUN SQL EXEC SQL GET SQL

EXEC SQL

Which of the following finds all groups meeting stated conditions? Question options: Having Where Select Find

Having

The methods to ensure the quality of data across various subject areas are called: Question options: Master Data Management. Managed Data Management. Joint Data Management. Variable Data Management.

Master Data Management.

________ is/are any of several classes of software that allow an application to interoperate with other software without requiring the user to understand all software involved. Question options: Interface managers Middleware User interface enhancers MPP

Middleware

All of the following are popular architectures for Master Data Management EXCEPT: Question options: Persistent Object. Integration Hub. Identity Registry. Normalization.

Normalization.

To get all the customers from Hawaii sorted together, which of the following would be used? Question options: Order By Sort Having Group By

Order By

Which of the following is an advantage of stored procedures? Question options: The layers get cleaner. Stored procedures result in fatter clients. Data integrity improves when fewer applications access the procedure. Performance improves for compiled SQL statements.

Performance improves for compiled SQL statements.

DDL is typically used during which phases of the development process? Question options: Analysis Physical design Implementation All of the above

Physical design

Which of the following data-mining techniques searches for patterns and correlations in large data sets? Question options: Rule discovery Signal processing Case reasoning Neural nets

Rule discovery

The ________ is the structure that contains descriptions of objects such as tables and views created by users. Question options: schema SQL catalog master view

Scema

Which of the following will produce the minimum of all standard prices? Question options: Select min(standard_price) from Product_V; Select min(Standard_Price) from Product_V where Standard_Price = min(Standard_Price); Select standard_price from Product_V where Standard_Price = min; Select Standard_Price from min(Product_V);

Select min(standard_price) from Product_V;

What does the following SQL statement do? Select * From Customer Where Cust_Type = "Best" Question options: Selects the "*" field from the Customer table for each row with a customer labeled "Best" Selects all the fields from the Customer table for each row with a customer labeled "*" Selects fields with a "*" in them from the Customer table Selects all the fields from the Customer table for each row with a customer labeled "Best"

Selects all the fields from the Customer table for each row with a customer labeled "Best"

SOAP stands for: Question options: Simple Object Access Protocol Method. what you wash your hands with. Strategic Operational Advanced Planning. Simple ODBC Access Protocol.

Simple Object Access Protocol Method.

EXISTS will take a value of ________ if the subquery returns an intermediate results table which contains one or more rows. Question options: FALSE undefined TRUE 1

TRUE

What result set will the following query return? Select Item_No, description from item where weight > 100 and weight < 200; Question options: The Item_No and description for all items weighing less than 100 The Item_No for all items weighing more than 200 The Item_No and description for all items weighing between 101 and 199 The Item_No for all items weighing between 101 and 199

The Item_No and description for all items weighing between 101 and 199

What results will be produced by the following SQL query? Select sum(standard_price) as Total_Price from Product_V where Product_Type = 'WOOD'; Question options: The total price of all products that are of type wood The total price of all products The Standard_Price of any wood product in the table The Standard_Price of the first wood product in the table

The total price of all products that are of type wood

The ________ clause is used to combine the output from multiple queries into a single result table. Question options: DIVIDE INTERSECT COLLATE UNION

UNION

An XML-based grammar used to describe a Web service is called: Question options: Web Service Description Language (WSDL). WIDL. Business-to-Business (B2B). WISL.

Web Service Description Language (WSDL).

The promise of Web services is the development of a standardized communication system using: Question options: XPath. XST. XML. HTML.

XML.

________ facilitates the ability of applications to query relational data along with associated structured data. Question options: XQuery XSLT SOAP XPath

XQuery

Given a table named store with 5 fields: store_id, address, city, state, zipcode, why would the following insert command not work? insert into store values ('234 Park Street') Question options: It would work just fine. There is no table keyword. You must specify the fields to insert if you are only inserting some of the fields. None of the above.

You must specify the fields to insert if you are only inserting some of the fields.

Service-oriented architectures (SOA) are: Question options: a collection of services that communicate with each other in some manner. a set of tiers designed to serve each other. architecture designed for the military. none of the above.

a collection of services that communicate with each other in some manner.

Extensible Markup Language (XML) is: Question options: a database language. a product used for database middleware. a scripting language that allows the creation of customized tags to enable easier sharing of data across organizations. none of the above.

a scripting language that allows the creation of customized tags to enable easier sharing of data across organizations.

The process of transforming data from a detailed to a summary level is called: Question options: updating. joining. extracting. aggregating.

aggregating.

A procedure is: Question options: stored within the database. called by name. given a unique name. all of the above.

all of the above.

A view may not be updated directly if it contains: Question options: derived columns and expressions in the SELECT clause. uses the GROUP BY or HAVING clause. the DISTINCT keyword. all of the above.

all of the above.

Data may be loaded from the staging area into the warehouse by following: Question options: special load utilities. custom-written routines. SQL Commands (Insert/Update). all of the above.

all of the above.

High-quality data are data that are: Question options: available in a timely fashion. accurate. consistent. all of the above.

all of the above.

Loading data into a data warehouse involves: Question options: purging data that have become obsolete or were incorrectly loaded. appending new rows to the tables in the warehouse. updating existing rows with new data. all of the above.

all of the above.

SQL-invoked routines can be: Question options: functions. procedures. all of the above. none of the above.

all of the above.

The benefits of a standardized relational language include: Question options: reduced training costs. application longevity. cross-system communication. all of the above.

all of the above.

The MERGE command: Question options: joins 2 tables together. allows one to combine the INSERT and UPDATE operations. allows one to combine the INSERT and DELETE operations. none of the above.

allows one to combine the INSERT and UPDATE operations.

Web services: Question options: are a set of standards based upon HTML. are a set of emerging standards for protocols for automatic communication between software over the Web. are a set of services available to all on the Web. none of the above

are a set of emerging standards for protocols for automatic communication between software over the Web.

A join in which the joining condition is based on equality between values in the common columns is called a(n): Question options: natural join. unilateral join. equi-join. both A and C.

both A and C.

A join operation: Question options: is used to combine indexing operations. brings together data from two different fields. causes two disparate tables to be combined into a single table or view. causes two tables with a common domain to be combined into a single table or view.

causes two tables with a common domain to be combined into a single table or view.

One way to improve the data capture process is to: Question options: provide little or no training to data entry operators. check entered data immediately for quality against data in the database. not use any automatic data entry routines. allow all data to be entered manually.

check entered data immediately for quality against data in the database.

A characteristic of reconciled data that means the data reflect an enterprise-wide view is: Question options: comprehensive. historical. normalized. detailed.

comprehensive.

In SQL, a(n) ________ subquery is a type of subquery in which processing the inner query depends on data from the outer query. Question options: natural correlated paired inner

correlated

The command for creating a database is: Question options: create schema. create authorization. create table. create view.

create schema.

The SQL command ________ defines a logical table from one or more tables or views. Question options: create relationship create view create table alter table

create view

All of the following are tasks of data cleansing EXCEPT: Question options: generating primary keys for each row of a table. creating foreign keys. adding time stamps to distinguish values for the same attribute over time. decoding data to make them understandable for data warehousing applications.

creating foreign keys.

Data quality problems can cascade when: Question options: data are not deleted properly. data are copied from legacy systems. there are data entry problems. there is redundant data storage and inconsistent metadata.

data are copied from legacy systems.

Conformance means that: Question options: data have been transformed. data are stored in a way to expedite retrieval. data are stored, exchanged or presented in a format that is specified by its metadata. none of the above.

data are stored, exchanged or presented in a format that is specified by its metadata.

When we consider data in the data warehouse to be time-variant, we mean: Question options: data in the warehouse contain a time dimension so that they may be used to study trends and changes. that there is a time delay between when data are posted and when we report on the data. that the time of storage varies. none of the above.

data in the warehouse contain a time dimension so that they may be used to study trends and changes.

A data mart is a(n): Question options: smaller system built upon file processing technology. data warehouse that is limited in scope. generic on-line shopping site. enterprisewide data warehouse.

data warehouse that is limited in scope.

If the DBA wishes to describe all tables in the database, which data dictionary view should be accessed in Oracle? Question options: dba_table_label dba_tab_comments dba_tables dba_tab_privs

dba_tables

A star schema contains both fact and ________ tables. Question options: dimension cross functional starter narrative

dimension

A client PC that is responsible for processing presentation logic, extensive application and business rules logic as well as many DBMS functions is called a(n): Question options: file processor. database server. fat client. file server.

fat client.

In the ________ approach, one consolidated record is maintained, and all applications draw on that one actual "golden" record. Question options: integration hub federated identity registry persistent

federated

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: Question options: span. aggregation. grain. selection.

grain.

Embedded SQL consists of: Question options: hard-coded SQL statements included in a program written in another language. SQL encapsulated inside of other SQL statements. SQL written into a front-end application. SQL translated to a lower-level language.

hard-coded SQL statements included in a program written in another language.

The best place to improve data entry across all applications is: Question options: in the database definitions. in the data entry operators. in the users. in the level of organizational commitment.

in the database definitions.

A method of capturing only the changes that have occurred in the source data since the last capture is called ________ extract. Question options: incremental partial static update-driven

incremental

The analysis of summarized data to support decision making is called: Question options: artificial intelligence. data scrubbing. informational processing. operational processing.

informational processing.

An operational data store (ODS) is a(n): Question options: integrated, subject-oriented, updateable, current-valued, detailed database designed to serve the decision support needs of operational users. small-scale data mart. representation of the operational data. place to store all unreconciled data.

integrated, subject-oriented, updateable, current-valued, detailed database designed to serve the decision support needs of operational users.

A dependent data mart: Question options: is filled exclusively from the enterprise data warehouse with reconciled data. is dependent upon an operational system. participates in a relationship with an entity. is filled with data extracted directly from the operational system.

is filled exclusively from the enterprise data warehouse with reconciled data.

Dynamic SQL: Question options: creates a less flexible application. is used to generate appropriate SQL code on the fly as an application is processing. is not used widely on the Internet. is quite volatile.

is used to generate appropriate SQL code on the fly as an application is processing.

All of the following are limitations of the independent data mart EXCEPT: Question options: there is no capability to drill down into greater detail in other data marts. it is often more expedient to build a data mart than a data warehouse. separate extraction, transformation, and loading processes are developed for each data mart. data marts may not be consistent with one another.

it is often more expedient to build a data mart than a data warehouse.

Data quality is important for all of the following reasons EXCEPT:______ Question options: it minimizes project delay. it helps to expand the customer base. it aids in making timely business decisions. it provides a stream of profit.

it provides a stream of profit.

The process of combining data from various sources into a single table or view is called: Question options: joining. extracting. updating. selecting.

joining.

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): Question options: inner join. equi-join. multivariate join. natural join.

natural join.

The use of a set of graphical tools that provides users with multidimensional views of their data is called: Question options: on-line analytical processing (OLAP). on-line geometrical processing (OGP). drill-down analysis. on-line datacube processing (ODP).

on-line analytical processing (OLAP).

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): Question options: equi-join. union join. outer join. natural join.

outer join.

A Web server: Question options: always contains a database. is considered to be part of the firewall. is used only to host Web pages. processes client requests and returns HTML pages to the client.

processes client requests and returns HTML pages to the client.

Data federation is a technique which: Question options: provides a virtual view of integrated data without actually creating one centralized database. creates an integrated database from several separate databases. creates a distributed database. provides a real-time update of shared data.

provides a virtual view of integrated data without actually creating one centralized database.

The major advantage of data propagation is: Question options: the ability to have trickle-feeds. real-time cascading of data changes throughout the organization. duplication of non-redundant data. none of the above.

real-time cascading of data changes throughout the organization.

Data that are detailed, current, and intended to be the single, authoritative source of all decision support applications are called ________ data. Question options: detailed subject reconciled derived

reconciled

A(n) ________ is a module of code written in SQL or some proprietary language to run business rules on a server. Question options: stored procedure select module select procedure SQL program

stored procedure

A type of query that is placed within a WHERE or HAVING clause of another query is called a: Question options: master query. multi-query. superquery. subquery.

subquery.

Every key used to join the fact table with a dimension table should be a ________ key. Question options: primary surrogate secondary foreign

surrogate

The XML shown in the figure below describes some data from the Pine Valley Furniture case study. The following XPath expression returns for results: /furniturecompany/product/[finish = "Cherry"]/standardprice Question options: a description of all products with a cherry finish. the standard price of products with a cherry finish. all products with a cherry finish. nothing.

the standard price of products with a cherry finish.

External data sources present problems for data quality because: Question options: there is a lack of control over data quality. there are poor data capture controls. data are unformatted. data are not always available.

there is a lack of control over data quality.

A PC configured to handle user interface with little or no local storage is called a: Question options: thin client. server. fat client. workstation.

thin client.

User-defined transactions can improve system performance because: Question options: speed is improved due to query optimization. transactions are mapped to SQL statements. transactions are processed as sets, reducing system overhead. all of the above.

transactions are processed as sets, reducing system overhead.

A named set of SQL statements that are considered when a data modification occurs are called: Question options: treatments. stored procedures. trapdoors. triggers.

triggers.

All of the following are characteristics of cloud technologies EXCEPT: Question options: broad network access. unlimited bandwidth. rapid elasticity. on-demand self-service IT capabilities.

unlimited bandwidth.

All of the following are concerns about developing a Web services approach EXCEPT: Question options: reliability. user acceptability. transaction speed. security.

user acceptability.

Establishing IF-THEN-ELSE logical processing within an SQL statement can be accomplished by: Question options: using the CASE keyword in a statement. using the immediate if statement. using a subquery. using the if-then-else construct.

using the CASE keyword in a statement.

Multiple values returned from an SQL query that includes an aggregate function are called: Question options: agates. vector aggregates. scalar aggregates. summations.

vector aggregates.

RANK and DENSE-RANK are examples of: Question options: window functions. ceilings. door functions. moving functions.

window functions.