MIS 555 Exam 2

A time dimension table tracks throughout a day by minute. How many rows does the table contain? 1440 60 24 240

1440

In MySQL, a column has a SET type with 16 possible elements. How many bytes does each column value require?

2

What is the fan-out for a multi-level index where index entries are 32 B and index blocks are 10 KB Approximately 500 Approximately 200 Approximately 300 Approximately 400

300

How many types are possible for elements of a JSON document?

6

A date dimension table tracks data for two years. How many rows does the table contain? 24 730 365 12

730

How does a list partition assign rows to partition? A specified number of partitions assigned with positive integer values. A return value from a partition expression that uses VALUES IN. A return value from a partition expression that specifies MAXVALUE. A value that is determined automatically by the database.

A return value from a partition expression that uses VALUES IN

A developer wishes to link to a database with a utility created in C. Which MySQL component does the developer use for the link? Connector Workbench Command-Line API

API

A database ___ is a unit of work that is treated as a whole; either completed or failed

transaction

The username and password for the database in the configuration file of a web application is incorrect, so the web application cannot connect to the database. Which failure scenario best describes this example? Task failure Storage media failure System failure Transaction failure

transaction

A recovery system should manage which three failure scenarios? Transaction failure, System failure, and Application failure Transaction failure, Program failure, and Storage media failure Transaction failure, System failure, and Storage media failure Transaction failure, Memory failure, and Storage media failure

transaction, system, storage media

A replicated database maintains at least _____ or more replicas on _____ storage devices. two, propagated three, distributed three, parallel two, separate

two, separate

Data was not save before a system was accidentally powered off. This data was located in: non-volatile memory magnetic storage media flash storage volatile memory

volatile memory

What is a concern with using an in-memory database? Volatility Interference Privacy Redundancy

volatile memory

_____ architecture is an example of a modern multi-tier architecture. Web Browser Server Desktop

web

How are blocks read with a single-level index scan? A scan reads all index blocks to find table blocks that contain selected rows. The table blocks are then read. A scan is initiated to read all table blocks that contain index blocks with selected data. A scan is initiated to read all table blocks. Index blocks are then read. The search reads one index block plus selected table blocks.

scan reads all blocks to find table blocks that contain selected rows. the table blocks are then read.

What table structure might use the modulo function to locate rows Sorted Cluster Hash Heap

Hash

An organization deploys computer processing power and storage through a cloud provider's _____ services. NaaS SaaS IaaS PaaS

Iaas

A ---- is a sequential order of database instructions for multiple transaction

schedule

A magnetic disk groups data into blocks sectors kilobytes pages

sectors

MONEY is an example of a _____ type.

semantic

In _____ data, each element is named, and element types are not explicitly declared.

semistructured

In order to locate rows selected by a query, an index scan read index blocks: in reverse order in order of key values sequentially randomly

sequentially

Which isolation level prevents dirty, non-repeatable, and phantom reads? Read committed Serializable Repeatable read Read uncommitted

serializable

When a table is updated and the index block has no free space for a new index entry, what happens? The entry is removed. A new block is merged. The block splits. An error results.

the block splits

Which type is complex? TEXT BLOB JSON DATE

JSON

In MySQL with InnoDB, how are primary and secondary indexes created?

Primary indexes are automatically crated for each PK; secondary indexes are created automatically for all FK

Using a cloud-based database comes with risk. _____ is a regulatory risk. Elasticity Privacy Security Reliability

Privacy

In a primary/secondary replica update for a distributed database, secondary nodes are updated _____. with independent local transactions to any available single node as a single distributed transaction at the same time as the primary node replica

with independent local transactions

_____ allows all executed database instructions to be rolled back and to be restored in a prior transaction state. RELEASE SAVEPOINT COMMIT SET TRANSACTION SAVEPOINT

SAVEPOINT

MySQL stores JSON values as _____.

an internal binary format

In an online bank application that transfers funds from a checking account to a savings account, if a debit is made successfully from the checking account, the _____ property ensures that the corresponding credit is made to the savings account. consistency atomicity isolation durability

atomicity

In a federated database, a(n) _____ database operates independently of other databases. In-memory Heterogeneous Autonomous Embedded

autonomous

Branches that are similar in length in an index hierarchy are: balanced dense sparse primary

balanced

A user-defined type is defined in terms of a _____ type.

base

A relational database uses row-oriented storage to store an entire row within one: Table block page sector

block

Elements can be nested in _____.

both XML and JSON

A transaction _____ indicates the starting and ending statement of a database transaction. scope boundary limit coverage

boundary

Hash index entries are assigned to _____: clusters values blocks buckets

buckets

Which MySQL component retains data blocks from storage in main memory for possible reuse? Buffer manager Cache manager Storage manager Connection manager

buffer manager

In a multi-table, a ____ column is available in interleaved tables hash function cluster key sort bucket

cluster key

A ___ index is an index on a non-unique sort column sorted primary clustering secondary

clustering

The PosgreSQL ARRAY type is an example of a _____ type.

collection

Which of the following is a characteristic of a B-tree index, but not a B+ index? Pointers to table blocks appear only in the bottom level. All column values appear in the bottom level. Column values do not repeat at lower levels. Column values are occasionally repeated in the index.

column values do not repeat at lower levels

A hierarchy in a dimension table contains a series of _____ where each has a _____ relationship to the next. columns, one-many indexes, one-many columns, many-many rows, many-many

columns, one-many

A buffer manager retains _____ from the _____ to reduce data access time. data blocks, file system files, file system free space, cache entries data blocks, cache

data blocks, file system

A database admin uses a SHOW statement to retrieve information about objects in a database. This information is contained in a _____. index file system storage manager data dictionary

data dictionary

A retailer is using a cloud-based transaction database and is experiencing sales fluctuations through the year. The ability for cloud resources to adapt to such change in utilization is called _____. privacy reliability elasticity security

elasticity

A database designer suggests using a database that is packaged with a programming language. Which of the following databases is a good choice? Embedded database Federated database Autonomous database In-Memory database

embedded database

In the query processor layer, the query optimizer generates a/an _____. primary key data import execution plan connection

execution plan

Data warehouses should be refreshed periodically. To start, all data should be _____. integrated the data into a uniform structure extracted to a temporary database cleansed of any errors restructured for optimization

extracted to a temporary database

A primary key of a _____ is the composite of the foreign keys that reference a _____. dimension table, fact table dimension hierarchy, dimension table fact table, dimension table star schema, fact table

fact table, dimension table

A column contains grades from 0 to 10, but a WHERE clause specifies values from 0 to 100. A ___ index can be used to modify the column values and process the queries logical single-level multi-level function

function

A database admin configures replication for a distributed database. Which technique applies updates to any available node? Group replication Master/slave Primary/secondary Parallel system

group replication

A complex type _____. has multiple values per table cell, from the perspective of the database system cannot be used in a join query is defined by the user, not the database system has a richer internal structure than a simple type

has a richer internal structure than a simple type

A ___ partition requires a partition expression with positive integer values hash list horizontal vertical

hash

A ___ assigns each row to a group of linked blocks, called a bucket table cluster hash table heap table sorted table

hash table

A subset of table rows is called a: vertical partition cluster fragment horizontal partition

horizontal partition

_____ is a recovery technique that creates a nearly synchronized backup of the primary database on another database server. Data backup Cold backup Hot backup Storage backup

hot backup

the _____ property ensures that another transaction sees the transferred funds in one account or the other, but not in both, nor in neither. consistency durability isolation atomicity

isolation

A _____ is a part of the concurrency system that monitors, grants, and releases locks. Lock Administrator Lock System Lock Manager Lock Optimizer

lock manager

Which of the following techniques can prevent other database administrators from doing concurrent transactions to the same table?

locking

The sort column in a sorted table determines the ______ row order: dynamic linked physical key

physical

In a logical index, pointers to a table blocks are replaced with foreign key composite key primary key artificial key

primary key values

Which of the following is NOT a replica type? database query data table

query

In which isolation level can dirty reads occur? Serializable Read uncommitted Repeatable read Read committed

read uncomitted

A document that sequentially writes all the database operations is known as a/an _____. application log task log recovery log system log

recovery log

An operational database contains tables in third normal form. This design reduces _____. reference time interference normalization redundancy

redundancy

Due to infrequent updates, many distributed databases use a _____ catalog. parallel private primary replicated

replicated

A single-level index is a file that contains column values and pointers to ___ containing the column___ rows, value tables, key tables, rows blocks, key

rows, value

What approach can a database admin use to assess the effectiveness of indexes when investigating slow queries? Run EXPLAIN on queries Partition large tables Change the primary key Inspect query logs

run EXPLAIN on queries

Which deadlock management technique automatically rolls back a transaction when a lock is not released in a fixed period of time?

timeout

What are the two characteristics of an efficient bitmap index? Indexes change only when primary key values are updated. Physical indexes change whenever a row moves to a new block. Each bucket initially has one block. Any additional blocks are allocated and linked to the initial block. The WHERE clause may specify any values in upper case. The column used in a WHERE clause may contain mixed upper and lower case characters. The database can quickly determine the block containing a table row from the index row number. Any indexed column contains relatively few distinct values.

1. database can quickly determine the block containing a table row from the index row number 2. any indexed column contains relatively few distinct values

In MySQL, which of the following SQL operations always commits immediately? SELECT DELETE INSERT CREATE

CREATE

What is the difference between the ENUM type and the SET type?

Each ENUM value has exactly one element; each SET value can have many elements

When a table is assigned to a tablespace, where is the index stored? In the same tablespace as the table. Within the table. In a separate tablespace file. In an adjacent tablespace.

In the same tablespace as the table

Which storage engine provides full support for transaction management and locking? MySQL InnoDB MyISAM MEMORY

InnoDB

Elements of the _____ type can be repeated and are not ordered.

MULTISET

An organization uses a database and application development tools through a cloud provider's _____ services. PaaS SaaS IaaS NaaS

PaaS

What object maps one or more tables to a single file? Vertical partition Tablespace Shard Horizontal partition

Tablespace

Which MySQL layer interacts directly with database users and administrators?

Tools

With column-oriented storage, a block stores values for: non-key columns a single column all table columns key columns only

a single column

The time required to read or write the first byte of data is known as: volatility random-access access time transfer rate

access time

In a data lake, data is often loaded _____ rather than _____. continuously, in parallel periodically, continuously periodically, in parallel continuously, periodically

continuously, periodically

During a data _____ phase, a database administrator refreshes a data warehouse and replaces incompatible keys with consistent values. cleansing restructuring extraction integration

data integration

Raw data that is copied from multiple data sources is called what? Data lake Replicated catalog Federated database Dimension hierarchy

data lake

For reporting purposes, an organization establishes a(n) _____, which is separate from the operational database. data warehouse autonomous database embedded database star schema

data warehouse

the _____ property ensures that the changes made to each account will not be lost due to a computer failure. consistency durability isolation atomicity

durability

In a _____, all transactions come to a halt and remain at a standstill until one of the transactions is aborted.

deadlock

Geographic data is _____.

defined with reference to the surface of the earth

In a data warehouse that uses a dimensional design, a StudentID column is found in which table? Star Parallel Dimension Schema

dimension

An organization has a main office and three satellite locations. Data specific to each location is stored locally in which configuration? Parallel Shared Distributed Private

distributed

Operational data changes _____, while analytic data is updated _____. in real time, at fixed intervals at fixed intervals, in real time at fixed intervals, per a schedule in real time, as it changes

in real time, at fixed intervals

When performing a search, which scan type is fastest? Row Cluster Table Index

index

What is a characteristic of logical index? Each bucket initially has one block. Index does not change when a row moves to a new block. Any additional blocks are allocated and linked to the initial block. The block containing a table row is determined from the index row number.

index does not change when a row moves to a new block

The heap table structure is optimized for: inserting new rows reading all rows with primary key between two fixed values deleting all rows with primary key between two fixed values updating all occurrences of a specific value of a column, in all rows

inserting new rows

What occurs when a distributed database experiences a network error and nodes cannot communicate? Consistent records Inconsistent transactions Database failure Network partition

network partition

For integrity purposes, a distributed transaction requires all or _____ nodes to be updated in order to complete the transaction. connected no shared primary

no

When concurrently processing instructions, a shared memory computer works well with a common data set in a _____ memory space. parallel distributed cache single

single

The bottom level of a multi-level index is a sorted single-level bottom-level block unsorted single-level primary and clustering

sorted single level

In MySQL with InnoDB tables with a PK have a ____ structure, while those without a PK have a ___ structure heap, sorted sorted, heap cluster, heap sorted, hash

sorted, heap