Database Multiple Questions

A(n) ________________________ is one in which all data integrity constraints are satisfied.

7 5
1794 110 5 6
1795 110 [removed] NULL COMMIT ****

 

A(n) _______________ optimizer uses a set of preset conditions and points to determine the best approach to execute a query. The conditions assign a “cost” to each SQL operation; the costs are then added to yield the cost of the execution plan.

[removed] a. user-based
[removed] b. query-based
[removed] c. rule-based
[removed] d. cost-based

What database statistics measurements are typical of environment resources?

[removed] a. The logical and physical disk block size
[removed] b. The location and size of data files
[removed] c. The number of extends per data file
[removed] d. All of these choices are correct.

____________________ describes a process on the server side that will properly configure the DBMS environment to respond to clients’ requests in the fastest way possible, while making optimum use of existing resources.

[removed] a. User performance tuning
[removed] b. Entity performance tuning
[removed] c. DBMS performance tuning
[removed] d. SQL performance tuning

How are database statistics obtained?

[removed] a. Automatically by the DBMS.
[removed] b. Manually by the DBA.
[removed] c. All of these choices are correct.
[removed] d. None of these choices are correct.

Which of the following is NOT a phase the DBMS goes through when processing a query?

[removed] a. Parsing
[removed] b. Execution
[removed] c. Compiling
[removed] d. Fetching

 

A(n) _______________ uses 0s and 1s to represent the existence of a value or condition.

[removed] a. B-tree index
[removed] b. bitmap index
[removed] c. hash index
[removed] d. reverse index

What database statistics measurements are typical of tables?

[removed] a. Row length
[removed] b. Number of rows and number of columns
[removed] c. Number of disk blocks used
[removed] d. All of these choices are correct.

______________ refers to the number of different values a column could possibly have.

[removed] a. Data measurement
[removed] b. Data clarity
[removed] c. Data access
[removed] d. Data sparsity

 

The term __________________ refers to a number of measurements gathered by the DBMS to describe a snapshot of the database objects’ characteristics.

[removed] a. entity statistics
[removed] b. query statistics
[removed] c. database statistics
[removed] d. user statistics

Which of the following is NOT one of the general guidelines for creating and using indexes?

[removed] a. Declare indexes in join columns other than PK or FK
[removed] b. Use indexes in small tables or tables with low sparsity
[removed] c. Declare primary and foreign keys
[removed] d. All of these choices are guidelines.

Which of the following phase is where the DBMS runs the SQL query using the chosen plan?

[removed] a. Fetching
[removed] b. Compiling
[removed] c. Execution
[removed] d. Parsing

_____________ database systems are optimized to store large portions (if not all) of the database in primary (RAM) storage rather than secondary (disk) storage.

[removed] a. In-sync
[removed] b. In-memory
[removed] c. All of these choices are correct.
[removed] d. None of these choices are correct.

In simple terms, the DBMS processes queries in ________ phase(s).

[removed] a. four
[removed] b. one
[removed] c. two
[removed] d. three

Which of the following phase is where the DBMS analyzes the SQL query and chooses the most efficient access/execution plan?

[removed] a. Fetching
[removed] b. Parsing
[removed] c. Execution
[removed] d. Compiling

RAID level _______________ is when the data and the parity are striped across separate drives, providing good read performance and fault tolerance via parity data.

[removed] a. 1
[removed] b. 5
[removed] c. 0
[removed] d. 3

Optimizer ______ are special instructions for the optimizer that are embedded inside the SQL command text.

[removed] a. hints
[removed] b. costs
[removed] c. facts
[removed] d. rules

Which of the following is NOT one of the steps you would follow when formulating a query?

[removed] a. Determine how to join the tables
[removed] b. Identify the source tables
[removed] c. Determine the order in which to display the output
[removed] d. Determine the query output

What query optimization factors should you keep in mind if you intend to write conditional expressions in SQL code?

[removed] a. Equality comparisons are faster than inequality comparisons
[removed] b. Numeric field comparisons are faster than character, date, and NULL comparisons
[removed] c. Whenever possible, transform conditional expressions to use literals
[removed] d. All of these choices are correct.

What type of SQL statement updates the data dictionary tables or system catalog?

[removed] a. DDL
[removed] b. DML
[removed] c. DBL
[removed] d. None of these choices are correct.

 

Most performance-tuning activities focus on doing what to the number of I/O operations, which are much slower than reading data from the data cache?

[removed] a. maximizing
[removed] b. replacing
[removed] c. converting
[removed] d. minimizing

 

A(n) _______________ optimizer uses sophisticated algorithms based on the statistics about the objects being accessed to determine the best approach to execute a query.

[removed] a. cost-based
[removed] b. user-based
[removed] c. rule-based
[removed] d. query-based

 

You use ________________ to provide both performance improvement and fault tolerance, and a balance between them as part of DBMS performance tuning.

[removed] a. Optimizer mode
[removed] b. RAID
[removed] c. Sort cache
[removed] d. None of these choices are correct.

Which of the following are recommendations you would make for managing the data files in a DBMS with many tables and indexes?

[removed] a. Put high-usage end-user tables in their own table spaces
[removed] b. Evaluate the creation of indexes based on the access patterns
[removed] c. Create independent data files for the system, indexes and user data table spaces
[removed] d. All of these choices are correct.

Which of the following is an example of an optimizer hint used in standard SQL?

[removed] a. MULTI_ROWS
[removed] b. NO_ROWS
[removed] c. I_ROWS
[removed] d. FIRST_ROWS

___________________ describes a process on the client side that will generate an SQL query to return the correct answer in the least amount of time, using the minimum amount of resources at the server end.

[removed] a. User performance tuning
[removed] b. Database performance tuning
[removed] c. SQL performance tuning
[removed] d. Entity performance tuning

 

DBMS performance tuning at the server end focuses on setting the parameters used for:

[removed] a. Data cache.
[removed] b. SQL cache.
[removed] c. sort cache.
[removed] d. All of these choices are correct.

 

Given the following SQL Query, which columns would you recommend to be indexed?

SELECT InvoiceNumber, InvoiceDate, Invoice_Total, Invoice_Paid,
Invoice_Total – Invoice_Paid as Balance
FROM Invoice
WHERE Invoice_Date >= “2015-07-20” and Salesman_Id = “JR”
ORDER BY DESC Invoice_Total

Drag the correct answers to one of the three pockets.

 

invoice Number

Invoice Date

Invoice_Total

Invoice_Paid

Salesman_id

ou work for a large non-profit that raises money for awareness of and research to cure a very painful childhood disease. They have a database table with over 100,000 names, addresses, and basic demographic information about donors to their cause.

Match the following columns in the table based on sparsity.

 

 

 

 

Donor_MaritalStatus http://cnow.apps.ng.cengage.com/media/img/pt/dhtml/matching/transparent-blueL.gif http://cnow.apps.ng.cengage.com/ilrn/bca/resources/matching/image?width=68&height=555 http://cnow.apps.ng.cengage.com/media/img/pt/dhtml/matching/transparent-blueR.gif High Sparsity
Donor_BirthDate http://cnow.apps.ng.cengage.com/media/img/pt/dhtml/matching/transparent-blueL.gif http://cnow.apps.ng.cengage.com/media/img/pt/dhtml/matching/transparent-blueR.gif Low Sparsity
Donor_NumberOfChildren http://cnow.apps.ng.cengage.com/media/img/pt/dhtml/matching/transparent-blueL.gif
Donor_LastName http://cnow.apps.ng.cengage.com/media/img/pt/dhtml/matching/transparent-blueL.gif
Donor_Gender http://cnow.apps.ng.cengage.com/media/img/pt/dhtml/matching/transparent-blueL.gif
Donor_ZipCode http://cnow.apps.ng.cengage.com/media/img/pt/dhtml/matching/transparent-blueL.gif
Donor_CellPhoneNumber http://cnow.apps.ng.cengage.com/media/img/pt/dhtml/matching/transparent-blueL.gif

The data warehouse is usually a ___________ database optimized for data analysis and query processing.

[removed] a. stand-alone
[removed] b. read-only
[removed] c. small sized
[removed] d. view-based

Which of the following are problems when operational data are integrated into the data warehouse?

[removed] a. Use of synonyms and homonyms.
[removed] b. Use of different coding schemes.
[removed] c. Incompatible data formats.
[removed] d. All of these choices are correct.

A(n) ___________________ is an arrangement of computerized tools used to assist managerial decision making.

[removed] a. Database Support System
[removed] b. Decision Support System
[removed] c. User Support System
[removed] d. Managerial Support System

A(n) ___________________ is a web-based system in BI that presents key business performance indicators or information in a single, integrated view with clear and concise graphics.

[removed] a. web interface
[removed] b. dashboard
[removed] c. cloud
[removed] d. micro storage device

The _______ clause in a materialized view indicates that the view rows are populated right after the command is entered.

[removed] a. NOT
[removed] b. IMMEDIATE
[removed] c. COMPLETE
[removed] d. DEFERRED

The ____________ is a data-modeling technique used to map multidimensional decision support data into a relational database.

[removed] a. data warehouse
[removed] b. smart star
[removed] c. managerial view
[removed] d. star schema

 

__________ uses star schemas while _____________ uses data cubes.

[removed] a. DOLAP, ROLAP
[removed] b. ROLAP, MOLAP
[removed] c. MOLAP, ROLAP
[removed] d. ROLAP, DOLAP

The fact table from the star schema diagram below is ____________.

[removed] a. ORDER
[removed] b. TIME
[removed] c. VENDOR
[removed] d. PRODUCT

Which of the following is a basic component of the BI architecture?

[removed] a. Data visualization
[removed] b. Data analytics
[removed] c. Data store
[removed] d. All of these choices are correct.

A(n) _______ in a star schema is a numeric measurement or value that represents a specific business aspect or activity.

[removed] a. attribute hierarchy
[removed] b. fact
[removed] c. dimension
[removed] d. attribute

 

Which SQL extension is used with the GROUP BY clause to generate aggregates by different dimensions?

[removed] a. CUBE
[removed] b. STAR
[removed] c. ROLLUP
[removed] d. BUILD

Which of the following are common characteristics of operational data?

[removed] a. Highly normalized data model
[removed] b. Atomic-detailed data
[removed] c. High-update volumes
[removed] d. All of these choices are correct.

 

One of the main characteristics of OLAP is ____________.

[removed] a. advanced database support
[removed] b. easy to use end user interfaces
[removed] c. multidimensional data analysis techniques
[removed] d. All of these choices are correct.

Which of the following techniques is NOT used to optimize data warehouse design?

[removed] a. Denormalizing dimensional tables
[removed] b. Partitioning and replicating tables
[removed] c. Denormalizing fact tables
[removed] d. Maintaining multiple fact tables

The _______ clause in a materialized view indicates when the rows are actually populated.

[removed] a. DEFERRED
[removed] b. REFRESH
[removed] c. BUILD
[removed] d. IMMEDIATE

Which type of OLAP would be best suited for the relational database environment?

[removed] a. ROLAP
[removed] b. MOLAP
[removed] c. DOLAP
[removed] d. None of these choices are correct.

A modern BI system provides which of the following distinctive reporting styles?

[removed] a. advanced data analytics
[removed] b. both “advanced reporting” and “advanced data analytics”
[removed] c. advanced reporting
[removed] d. None of these choices are correct.

A(n) _______ in a star schema is a qualifying characteristic that provides additional perspective to a fact.

[removed] a. dimension
[removed] b. attribute
[removed] c. fact
[removed] d. attribute hierarchy

 

Decision support data requires managers to _________ and to ________ the data in different situations.

[removed] a. understand, acquire
[removed] b. accept, reject
[removed] c. drill down, roll up
[removed] d. input, output

Multidimensional _________________ refers to the processing of data in which data are viewed as part of a multidimensional structure, one in which data are related in many different ways.

[removed] a. data analysis
[removed] b. user requirements
[removed] c. organization layout
[removed] d. data charts

which of the following is NOT a major component of the Business Intelligence framework?

[removed] a. Management
[removed] b. People
[removed] c. Location
[removed] d. Processes

Which of the following is NOT a key performance indicator in BI?

[removed] a. Earnings per share
[removed] b. Employee turnover
[removed] c. Same-store profits
[removed] d. All of these choices are key performance indicators.

A data cube that grows to n number of dimensions is known as a _____________.

[removed] a. cloud cube
[removed] b. multidimensional cube
[removed] c. hypercube
[removed] d. special cube

On which level of the BI framework is data analytics used?

[removed] a. data visualization
[removed] b. monitoring and alerting
[removed] c. queries and reporting
[removed] d. All of these choices are correct.

A(n) _______ in a star schema provides a top-down data organization that is sued for aggregation and drill-down/roll-up data analysis.

[removed] a. dimension
[removed] b. fact
[removed] c. attribute
[removed] d. attribute hierarchy

 

_______ has a larger database size than __________ .

[removed] a. DOLAP, ROLAP
[removed] b. ROLAP, MOLAP
[removed] c. MOLAP, ROLAP
[removed] d. ROLAP, DOLAP

 

_______ measures the density of the data held in the data cube; it is computed by dividing the total number of actual values in the cube by the total number of cells.

[removed] a. Mass
[removed] b. Sparsity
[removed] c. Density
[removed] d. Volume

Which SQL extension is useful when you want to compute all possible subtotals within groupings based on multiple dimensions?

[removed] a. BUILD
[removed] b. ROLLUP
[removed] c. STAR
[removed] d. CUBE

Which SQL extension is used with the GROUP BY clause to generate aggregates by the listed columns, including the last one?

[removed] a. CUBE
[removed] b. ROLLUP
[removed] c. BUILD
[removed] d. STAR

The ______________ table in the star schema represented below is NOT a dimension.

[removed] a. VENDOR
[removed] b. ORDER
[removed] c. PRODUCT
[removed] d. TIME

A(n) _______ in a star schema is often used to search, filter, or classify facts.

[removed] a. fact
[removed] b. dimension
[removed] c. attribute
[removed] d. attribute hierarchy

A ___________ is a small, single-subject data warehouse subset that provides decision support to a small group of people.

[removed] a. data warehouse
[removed] b. managerial view
[removed] c. database system
[removed] d. data mart

 

Which type of OLAP system works faster for large data sets with predefined dimensions?

[removed] a. ROLAP
[removed] b. OLAP
[removed] c. MOLAP
[removed] d. All of these choices are correct.

The _______ clause in a materialized view lets you indicate when and how to update the view when new rows are added to the base tables.

[removed] a. DEFERRED
[removed] b. IMMEDIATE
[removed] c. REFRESH
[removed] d. BUILD

Which of the following is NOT a dissemination format of BI that started in the 2000s?

[removed] a. Mobile BI
[removed] b. Portals
[removed] c. Spreadsheets
[removed] d. Dashboards

 

A _____ view is a dynamic table that not only contains the SQL query command to generate the rows, it stores the actual rows.

[removed] a. composite
[removed] b. user’s
[removed] c. virtual
[removed] d. materialized

______ integrates people and processes using technology in order to add value to the business.

[removed] a. Knowledge
[removed] b. Business Intelligence
[removed] c. Database Design
[removed] d. Information

_____ is a framework that allows a business to transform data into information, information into knowledge, and knowledge into wisdom.

[removed] a. Information
[removed] b. Business Intelligence
[removed] c. Database Design
[removed] d. Knowledge

From the data analyst’s point of view, decision support data differ from operational data in what three areas?

[removed] a. volume, time, reply.
[removed] b. time, granularity, dimensionality.
[removed] c. usage, support, consistency.
[removed] d. differentiation, degree, support.

A(n) ______________ is an integrated, subject-oriented, time-variant and non-volatile database that provides support for decision-making.

[removed] a. data warehouse
[removed] b. managerial view
[removed] c. database system
[removed] d. data store

In the ROLLUP SQL extension, the ____________ of the column list within GROUP BY ROLLUP is very important.

[removed] a. inclusion
[removed] b. placement
[removed] c. order
[removed] d. multiplication

 

Which of the following is a basic component of the star schema?

[removed] a. Dimensions
[removed] b. Facts
[removed] c. Attributes
[removed] d. All of these choices are correct.

 

The power of multidimensional analysis resides in its ability to focus on specific _______ of the cube.

[removed] a. pictures
[removed] b. modelings
[removed] c. slices
[removed] d. users

The ___________ component of the BI architecture performs data analysis and data-mining tasks using the data in the data store.

[removed] a. data monitoring and alerting
[removed] b. data analytics
[removed] c. data visualization
[removed] d. data store

You have been asked by a client to normalize the driver dimension and to create multiple fact tables for the receipts that a driver collects for each of the new aggregation levels.  Fill in the blanks with the field names used in the star schema to accomplish this task.

Facts:
A driver is assigned to one and only one office.
An office is in to only one region and may have many drivers.
A region is located in one and only one country and a country can have many regions.

http://cnow.apps.ng.cengage.com/ilrn/books/cmdsin12h/images/dnds/ch13-2_quest.png

 

Drag the correct answers in alphabetical order in to corresponding pockets.

Fill in the blanks with words that would best complete the passage.

Date

 

Customer_ID

 

Receipt_Count

 

Region_ID

 

Driver_Name

 

Office_ID

 

Customer_ID

 

Receipt_Count

 

Country_ID

 

Country_ID

 

Receipt_Total

 

Country_ID

 

Receipt_Total

 

Office_ID

 

Office_Name

 

Country_Name

Customer_ID

 

Driver_ID

 

Customer_ID

 

Receipt_Count

 

Receipt_Count

 

Office_ID

 

Driver_ID

 

Region_ID

 

Receipt_Total

 

Region_ID

 

Region_Name

 

Date

 

Receipt_Total

 

Date

 

Date

Match the following terms with a short description of that term:

 

 
Query and reporting http://cnow.apps.ng.cengage.com/media/img/pt/dhtml/matching/transparent-blueL.gif http://cnow.apps.ng.cengage.com/ilrn/bca/resources/matching/image?width=68&height=521 http://cnow.apps.ng.cengage.com/media/img/pt/dhtml/matching/transparent-blueR.gif Presentation of the data in innovative and easy to understand ways.
Data visualization http://cnow.apps.ng.cengage.com/media/img/pt/dhtml/matching/transparent-blueL.gif http://cnow.apps.ng.cengage.com/media/img/pt/dhtml/matching/transparent-blueR.gif Data warehouse or data mart optimized for data analysis and query speed.
Data analytics http://cnow.apps.ng.cengage.com/media/img/pt/dhtml/matching/transparent-blueL.gif http://cnow.apps.ng.cengage.com/media/img/pt/dhtml/matching/transparent-blueR.gif Collect, filter, and integrate internal and external data into data stores.
ETL tools http://cnow.apps.ng.cengage.com/media/img/pt/dhtml/matching/transparent-blueL.gif http://cnow.apps.ng.cengage.com/media/img/pt/dhtml/matching/transparent-blueR.gif This tool assists the user in choosing the best model for understanding the data or for discovery of relationships within the data.
Data store http://cnow.apps.ng.cengage.com/media/img/pt/dhtml/matching/transparent-blueL.gif http://cnow.apps.ng.cengage.com/media/img/pt/dhtml/matching/transparent-blueR.gif Selection and retrieval of the data.
Data monitoring and alerting