Chapter 9-12 Database Test #3

What does DSS mean in a data warehouse context? A. Decision Support System. B. Decision Security System. C. Data Storage System. D. Data Support Service.

A. Decision Support System.

What type of subquery is executed before the outer query and is executed only once?

Ordinary, Type 1, non-correlated sub-query

What type of subquery is nested inside another outer query from which it uses values?

Type 2, correlated sub-query

In what type of join are all columns from each table that is joined, and an instance for each row of each table?

Union join

A fact is a _________________________ about a specific event.

VALUE OR MEASUREMENT

In what clauses can you place a subquery?

WHERE clause, a HAVING clause, or a FROM clause of a SELECT statement.

What is a Full Outer Join?

a join in which rows that do not have matching values in common columns are nonetheless included in the result table

What relational operation causes two or more tables with a common domain to be combined into a single table?

a relational operation that causes two or more tables with a common domain to be combined into a single table or view aka INNER JOIN

Which of the following is a wide-column store ? a) Cassandra b) Riak c) MongoDB d) Redis

a) Cassandra

Which of the following is the simplest type of NoSQL database? a) Key-value b) Wide-column c) Document

a) Key-value

What functions can return the results as a document, or may write the results to collections. a) MapReduce b) Mapper c) ReduceMap d) none of the above

a) MapReduce

Which of the following statements is incorrect? a) Non or Post Relational databases require that schemas be defined before you can add data b) NoSQL databases are built to allow the insertion of data without a predefined schema c) NewSQL databases are built to allow the insertion of data without a predefined schema

a) Non or Post Relational databases require that schemas be defined before you can add data

Which of the following is not a NoSQL database ? a) SQL Server b) MongoDB c) Cassandra d) None of the mentioned

a) SQL Server

NoSQL databases are used mainly for handling large volumes of ______________ data. a) unstructured b) structured c) semi-structured

a) unstructured

What is a Full Outer Join? a. A join that combines the effect of applying both Left and Right Outer Joins. b. A join Where a result set will have no NULL values c. A join where only a single row will be produced in the result set containing fields populated from joined tables d. a and c e. b and c

a. A join that combines the effect of applying both Left and Right Outer Joins.

What is the UNION operator? a. An SQL operator that allows you to stack datasets b. An SQL operator that joins two datasets side-by-side c. UNION displays multiple SELECT statements

a. An SQL operator that allows you to stack datasets

What SQL statement returns varying results based upon the evaluation of expressions? a. CASE b. DIFFERENCE c. DISTINCT d. UNION

a. CASE

What SQL statements are used in large, multiuser database systems to control transactions, i.e., sequences of changes to a database? a. COMMIT and ROLLBACK b. DCL and DML c. ROLLBACK and ROLLFORWARD d. UPDATE and COMMIT

a. COMMIT and ROLLBACK

The degree to which the administration of a database is ________ dictates the skills and personnel required to manage databases.

automated

Which of the following is a NoSQL Database Type ? a) SQL b) Document databases c) JSON d) All of the mentioned

b) Document databases

What programming language is used to write custom functions to perform the map and reduce operations? a) Java b) Javascript c) JSON

b) Javascript

"Sharding" a database across many server instances can be achieved with: a) LAN b) SAN c) MAN

b) SAN

In what clause(s) can you place a subquery? a. A subquery can be nested inside the IN or WHERE clauses of an outer SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. b. A subquery can be nested inside the WHERE or HAVING clause of an outer SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. c. A subquery can be nested only inside the WHERE or HAVING clause of an outer or inner SELECT subquery.

b. A subquery can be nested inside the WHERE or HAVING clause of an outer SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery.

What are the two basic functions of a query optimizer? a. Calculate cost and execution time for a query. b. Determine Join order and Join method. c. Minimize cost and execution time for a query. d. Maximizes the execution plan that provides an optimum method of execution.

b. Determine Join order and Join method.

When are two relations union-compatible? a. If they have the same attributes and the same columns b. If they have the same number of attributes and each attribute is from the same domain c. If they have the same number of rows and columns d. If they have the same number of rows and columns and each attribute is from the same domain

b. If they have the same number of attributes and each attribute is from the same domain

The _______ operator takes the results of two queries and returns only rows that appear in both result sets. a. Union b. Intersect c. Difference d. Projection

b. Intersect

The _________ operation allows the combining of two relations by merging pairs of tuples, one from each relation, into a single tuple. a. Select b. Join c. Union d. Intersection

b. Join

What join returns records from the right table that have no matching key in the left table in the result set? a. Left outer join b. Right outer join c. Full outer join d. Cross join

b. Right outer join

What are conditional CASE expressions? a. PL/SQL expression introduced in SQL-92. b. SQL statement that handles If/Then logic. c. Works like Else If in other programming languages.

b. SQL statement that handles If/Then logic.

An _______________________ database is a database management system that primarily relies on main memory for computer data storage.

in-memory

A database security program should include the regular review of permissions granted to ___________ accounts and used by __________ processes.

individually owned automated

One technique for evaluating database security involves performing _____________ or penetration tests against the database.

vulnerability assets

What is the intention of DBA automation? A. Enable DBAs to focus on proactive activities like performance and service level management B. Increase the number of databases and reduce standardization of database schema C. Reduce the number of DBAs needed to operate a database.

A. Enable DBAs to focus on proactive activities like performance and service level management

What is the purpose of the HAVING clause?

A SQL statement can be included within another SQL statement

What are the four types of NoSQL data models? A) Column, Document, Graph, Key Value B) Column, Document, In-memory, Key Value C) Column, Document, Key Value, Network

A) Column, Document, Graph, Key Value

What is JSON? A) Java Simple Object Notation B) JavaScript Object Notation C) JavaScript Object Numbering

A) Java Simple Object Notation

What is the input to each of the two phases of the MapReduce algorithm? A) Key-value pairs. B) Relations. C) XML and JSON.

A) Key-value pairs.

What type of relationship exists between a dimension and fact table in a Star schema? A) Many-to-many, from dimension tables to a central fact table. B) One-to-one C) One-to-many, from dimension table to the fact table.

A) Many-to-many, from dimension tables to a central fact table.

What are major characteristics of a Database Administrator's work? A. Complex, repetitive, time-consuming and requires specialized training B. Easy, rewarding, time-consuming and requires specialized training C. High stress, demanding, tedious and requires an advanced degree D. Low stress, repetitive, and intellectually challenging

A. Complex, repetitive, time-consuming and requires specialized training

What data from the operational environment is most commonly extracted and loaded into a data warehouse? A. Current detail data. B. Older detail data. C. Lightly summarized data. D. Highly summarized data.

A. Current detail data.

What is an open-source DBMS? A. Free or nearly free database software whose source code is publicly available B. Free or nearly free database software, but source code is not publicly available C. Competitive compared to PC-oriented packages, but are not fully SQL compliant D. Clones of proprietary DBMS that have limited features

A. Free or nearly free database software whose source code is publicly available

Which of the following is the preferred way to recover a database after a transaction in progress terminates abnormally? A. Rollback B. Rollforward C. Switch to a duplicate database D. Reprocess transactions

A. Rollback

What is backward recovery? A. Where the before-images are applied to the database B. Where the after-images are applied to the database C. Where the after-images and before-images are applied to the database D. Switching to an alternative, existing copy of the database

A. Where the before-images are applied to the database

What does data transformation include? A. a process to change data from a detailed level to a summary level. B. a process to change data from a summary level to a detailed level. C. joining data from one source into various sources of data. D. separating data from one source into various sources of data

A. a process to change data from a detailed level to a summary level.

What does database transaction durability ensure in the event of a system failure? A. a transaction is not lost once it has been committed. B. a transaction is completed uninterrupted. C. a transaction is reviewed before its is executed. D. a transaction is saved before the failure occurs.

A. a transaction is not lost once it has been committed.

What is the purpose of a query tool? A. data retrieval. B. information delivery. C. information exchange. D. run routine reports.

A. data retrieval.

Generally, a star schema is composed of __________ fact table(s)? A. one. B. two. C. three. D. four.

A. one.

After the database designers complete the logical design, what Database design does a DBA typically create? A. physical B. machine C. logical D. normalized

A. physical

2. The data Warehouse is__________. A. read only. B. write only. C. read and write.

A. read only.

What is Apache Hadoop? A) A post-relational database management system. B) An open-source software framework that supports data-intensive distributed applications. C) A proprietary software framework for large scale processing of data. D) Software for MapReduce in-memory processing.

B) An open-source software framework that supports data-intensive distributed applications.

What are the two processes ensure that distributed databases remain up-to-date and current? A) Backups and Sharding. B) Duplication and Replication. C) Parity and RAID.

B) Duplication and Replication.

What is ETL? A) Enter, Transact and Load actions to populate a data warehouse B) Extract, Transform and Load actions to populate a data warehouse C) Extract, Transact and Load actions to populate a database

B) Extract, Transform and Load actions to populate a data warehouse

What are the four attributes of a data warehouse specified by Inmon? A) Facts, Dimensions, Subjects, Time Variant. B) Integrated, Nonvolatile, Subject-Oriented, Time Variant. C) Integrated, Subject-Oriented, Tested, Time Variant. D) Nonvolatile, Subject-Oriented, Tested, Time Variant

B) Integrated, Nonvolatile, Subject-Oriented, Time Variant.

What technology created by Google is the foundation for Hadoop? A) Hive. B) MapReduce. C) Oozie.

B) MapReduce.

After a DBMS is purchased, who has primary responsibility for installation and maintenance? A. IT manager B. DBA C. Vendor D. Systems Administrator

B. DBA

1. __________ is a subject-oriented, integrated, time-variant, nonvolatile collection of data in support of management decisions. A. Data Mining. B. Data Warehouse. C. Web Mining. D. Text Mining.

B. Data Warehouse.

If both data and database administration exist in an organization, the database administrator is responsible for which of the following? A. Data modeling B. Database design C. Metadata creation D. None of the above.

B. Database design

Data is stored, retrieved and updated in a ____________ environment. A. OLAP. B. OLTP. C. Business Intelligence (bi).

B. OLTP.

What are the four steps in the proactive cycle to improve security? A. Steps are: 1) analysis, 2) control, 3) detection, and 4) prevention B. Steps are: 1) prevention, 2) detection, 3) analysis, and 4) control C. Steps are: 1) detection, 2) analysis, 3) correction, and 4) control

B. Steps are: 1) prevention, 2) detection, 3) analysis, and 4) control

What is the extract process? A. capturing all of the data contained in various operational systems. B. capturing a subset of the data contained in various operational systems. C. capturing all of the data contained in various decision support systems. D. capturing a subset of the data contained in various decision support systems.

B. capturing a subset of the data contained in various operational systems.

What is the main organizational justification for implementing a data warehouse? A. cheaper ways of handling data movement. B. decision support. C. storing large volumes of data. D. providing access to data.

B. decision support.

What do dimension tables commonly describe in a star schema? A. descriptive entities. B. relevant facts. C. units of measures. D. data domains.

B. relevant facts.

An operational system is _____________. A. used to run the business in real time and is based on historical data. B. used to run the business in real time and is based on current data. C. used to support decision making and is based on current data. D. used to support decision making and is based on historical data.

B. used to run the business in real time and is based on current data.

What are the four ACID guarantees for transactions in a database? A) Atomic, Consistent, Identified, Durable. B) Atomicity, Concurrency, Integrity, Durability. C) Atomicity, Consistency, Isolation, Durability.

C) Atomicity, Consistency, Isolation, Durability.

What are the two major approaches for storing data in a data warehouse? A) Data mart and operational data store. B) Denormalized and flat file. C) Dimensional and normalized. D) Star schema and snowflake schema.

C) Dimensional and normalized.

What company originally developed the NoSQL database BigTable? A) Amazon. B) Facebook. C) Google. D) Twitter.

C) Google.

What are two approaches to scaling up (out) a NoSQL database? (very challenging question) A) Clusters and In-Memory. B) Distributed and Parallel processing. C) Master-slave and Sharding. D) Vertical and Horizontal partitioning.

C) Master-slave and Sharding.

Which of the following statements is true of a data warehouse? A) Can be updated by end users. B) Contains numerous naming conventions and formats. C) Organized around important subject areas. D) Contains only current data.

C) Organized around important subject areas.

What is the relationship between operational data and a data warehouse? A) The data warehouse consists of many data marts and operational data B) The data warehouse is used as a source for the operational data C) The operational data are used as a source for the data warehouse

C) The operational data are used as a source for the data warehouse

In general, who determines the access privileges for a user and enters the appropriate authorization rules in the DBMS catalog to ensure users only access a database in appropriate ways? A. Security consultant B. Data Administrator C. DBA D. IT manager

C. DBA

What does the acronym EIS commonly mean? A. Extensible interface system. B. Executive interface system. C. Executive information system. D. Enterprise information system.

C. Executive information system.

What term is used for descriptions of the data contained in the data warehouse? A. Relational data. B. Operational data. C. Metadata. D. Informational data.

C. Metadata.

A traditional data administrator performs which of the following roles? A. Tune database performance B. Establish backup and recovery procedures C. Resolve data ownership issues D. Protect the security of the database.

C. Resolve data ownership issues

How is a data warehouse organized? A. by end users. B. by naming conventions and formats C. around important subject areas. D. around departments and functions

C. around important subject areas.

What is detailed data in a fact table called? A. homogeneous data. B. attribute data. C. atomic data. D. reporting data.

C. atomic data.

What type of relationships exist in a star schema between dimension and the fact table? A. many-to-many. B. one-to-one. C. one-to-many. D. many-to-one.

C. one-to-many.

In what environment can data be updated? A. data warehouse. B. data mining. C. operational. D. informational.

C. operational.

What type of data in a data warehouse is never found in the operational database environment? A. normalized. B. informational. C. summary. D. denormalized.

C. summary.

What is changing the database design to improve the performance called? A. refining the design B. optimizing the design C. tuning the design D. modifying the design

C. tuning the design

CHAPTER 9

CHAPTER 9

With __________________________________ , application owners do not have to install and maintain the database on their own. Instead, a provider takes responsibility for installing and maintaining the database, and application owners pay according to their usage.

CLOUD DATABASE (database as a service)

________________ causes all data changes in a transaction to be made permanent.

COMMIT

Apache _________________________ is a database that uses JSON for documents and JavaScript for MapReduce queries.

COUCHDB

What is business intelligence? A) A set of theories, methodologies, processes, architectures, and technologies that transform information into meaningful and useful data for business purposes. B) A type of data warehouse used for competitive intelligence activities. C) An umbrella term to describe "concepts and methods to improve business decision making by using fact-based support systems." D) A and C

D) A and C

What is data scrubbing? A) A process to reject data from the data warehouse and to create the necessary indexes B) A process to load the data in the data warehouse and to create the necessary indexes C) A process to upgrade the quality of data after it is moved into a data warehouse D) A process to upgrade the quality of data before it is moved into a data warehouse

D) A process to upgrade the quality of data before it is moved into a data warehouse

What is the term used in data warehousing for a value or measurement? A) Attribute B) Dimension C) Domain D) Fact

D) Fact

What is the normal form of a Fact table in a Star schema? A) Completely denormalized B) Normalized to at least 3rd normal form C) Normalized to 2nd normal form D) Partially denormalized to speed query performance

D) Partially denormalized to speed query performance

What is an example of a post-relational database that is NOT a NoSQL database? A) BigTable. B) CouchDB. C) Hive. D) SQLite.

D) SQLite.

What is a common time horizon for data stored in a Data warehouse? A. Current, most recent year. B. 2-4 years. C. 5-6 years. D. 6 or more years.

D. 6 or more years.

What test is used to assess an online transaction processing environment? A. BASE. B. Benchmark. C. OLTP. D. ACID.

D. ACID.

What tasks are Business Intelligence and data warehousing systems used for in an organization? A. Forecasting. B. Reporting. C. Analysis of large volumes of product sales data. D. All of the above.

D. All of the above.

What is the purpose of backups? A. Provide a structure for the storage and recovery of data. B. Recover data after it is lost by data deletion or corruption. C. Recover data from an earlier time, according to a user-defined data retention policy. D. B and C. E. All of the above.

D. B and C.

Which of the following is NOT a property of database transactions? A. Atomicity B. Consistency C. Durability D. Idenfiable

D. Idenfiable

What is data scrubbing? A. a process to reject data from the data warehouse and to create the necessary indexes. B. a process to load the data in the data warehouse and to create the necessary indexes. C. a process to upgrade the quality of data after it is moved into a data warehouse. D. a process to improve the quality of data before it is moved into a data warehouse

D. a process to improve the quality of data before it is moved into a data warehouse

What are characteristics of an active data warehouse architecture? A. at least one data mart. B. data that has extracted from multiple internal and external sources. C. near real-time data updates. D. all of the above.

D. all of the above.

How is isolation of transactions achieved? A. avoiding simultaneous transactions. B. storing updates permanently. C. preventing system failure. D. concurrency control.

D. concurrency control.

In the bottom-up approach to data warehouse design, ___________________ are first created to provide reporting and analytical capabilities for specific business processes.

DATA MARTS

Which of the following topics are part of an administrative policy to secure a database? A. Authentication policies B. Limiting particular areas within a building to only authorized people C. Backup procedures D. A and C. E. All of the above.

E. All of the above.

Who is responsible for running queries and reports against data warehouse tables? A. DBA. B. Software applications. C. End users. D. Database analysts. E. C and D

E. C and D

What is an XML database? A. A category of NoSQL databases. B. A database that allows data to be specified in eXtensible Markup Language format. C. A type of document-oriented database. D. A and B. E. All of the above.

E. All of the above

A _________________ is a value or measurement about a specific event.

FACT

In what type of join are the rows that do not have matching values in common columns nonetheless included in the result table?

Full Outer Join

Apache _____________________ is an open-source software framework that supports data-intensive distributed applications. It supports parallel running of applications on large clusters of commodity hardware. It derives from Google's MapReduce and Google File System (GFS) papers.

HADOOP

The SQL ______________ clause includes a predicate used to filter rows resulting from the GROUP BY clause.

HAVING

The concept of ___________ was introduced into SQL to handle "missing data" in the relational model.

NULL

What is relational algebra?

Relational algebra is a formal system for manipulating relations.

What relational algebra operator is called "bowtie" because of the symbol used to represent it in notation?

The natural join (written ⋈) is a binary operator that is written as (R ⋈ S) where R and S are relations. ⋈ is called "bowtie". The result of the natural join is the set of all combinations of tuples in R and S that are equal on their common attribute names.

The _________________________ is the simplest style of data mart schema. It consists of one or more fact tables referencing any number of dimension tables.

STAR SCHEMA

Queries can be nested so that the results of one query can be used in another query via a relational operator or aggregation function. A nested query is also known as a _____________.

SUBQUERY

What is SQL?

Structured Query Language. SQL it is the standard language for relational database management systems.

The UNION compatible rule means the tables have same number of columns and ____________________? a. corresponding attributes have compatible data types b. corresponding columns have identical data types and lengths c. corresponding rows have the same data types d. rows are ordered and have corresponding attributes

b. corresponding columns have identical data types and lengths

Most NoSQL databases support automatic __________, meaning that you get high availability and disaster recovery a) processing b) scalability c) replication

c) replication

What type of join is needed when you wish to include rows that do not have matching values? a. Equi-join b. Natural join c. Outer join d) Any of the above.

c. Outer join

What types of data stores are used to store information about networks, such as social connections. a) Key-value b) Wide-column c) Document d) Graph

d) Graph

Which of the following statements is true? a) Documents can contain many different key-value pairs, or key-array pairs, or even nested documents b) MongoDB can link to only proprietary programming languages and development environments c) When compared to relational databases, NoSQL databases are more scalable and provide superior performance d) a and c e( All of the above.

d) a and c

In what type of join are the rows that do not have matching values in common columns nonetheless included in the result table? a. Cross Join b. Inner Join c. Nested Join d. Outer Join

d. Outer Join

How many queries can be nested in a Where clause of an Outer query? a. A maximum of two b. A maximum of 16 levels of nesting is possible, but depends upon available memory and the length of the query. c. Generally a maximum of 32 levels of nesting, but the limit varies based on available memory and the complexity of other expressions in the query. d. Unlimited number only constrained by computer memory, DBMS and processor speed.

d. Unlimited number only constrained by computer memory, DBMS and processor speed.

What is an advantage of using a subquery rather than a query with DISTINCT when both can yield the correct answer? a. A query will run more efficiently if you use the subquery to eliminate duplicates b. Use of subqueries reduces the hierarchy found in execution which can be useful c. Using subqueries makes it easier to read and understand the processing d. a and c e. All of the above.

d. a and c

A _____________________ is a subquery that uses values from the outer query. The subquery is evaluated once for each row processed by the outer query.

database query (a correlated subquery)

How does a Left Join differ from a Right Join?

· Left Join: all rows are retrieved from the left side table even if there is no match in the right table · Right Join: all rows are retrieved from the right side table even if there is no match in the left table

What is the Relational Algebra notation for UNION, INTERSECTION, and MINUS Operators?

•The UNION operation is A ∪ B, it joins or includes all tuples that are in A or in B, eliminating duplicate tuples. •INTERSECTION operation A ∩ B, includes tuples that are only in A and B. In other words only tuples belonging to both A and B, or shared by both A and B are included in the result. •MINUS operation includes tuples from one Relation that are not in another Relation. Let the Relations be A and B, the MINUS operation A MINUS B is denoted by A - B, it results in tuples that are in A and not in B.

What are the 3 fundamental Relational Algebra operators?

•The select operator (written σ) picks tuples, like the SQL WHERE clause picks rows. • The project operator (written π) picks attributes like the SQL SELECT clause. It is also a unary operator that takes a single relation or expression as its operand. • The natural join (written ⋈) is a binary operator that is written as (R ⋈ S) where R and S are relations. ⋈ is called "bowtie". The result of the natural join is the set of all combinations of tuples in R and S that are equal on their common attribute names.