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
