CS514 Final study questions
When a key-value database allows multiple values per key, how is each value identified?
By using a name for each value.
Database queries are commonly implemented using a declarative programming language because of which two main reasons?
Easier programming and faster execution
Which statements should replace XXX and YYY to execute the SELECT statement when the teacherID value is valid?
IF teachID IS NOT NULL THEN, END IF
Refer to the column information produced by SHOW COLUMNS FROM Supplier; statement. CountryId is a foreign key that references the CountryId column in the Country table. Which statement correctly inserts Oshkosh Bgosh?
INSERT INTO Supplier (CompanyName, ContactName, CountryId)VALUES ('Oshkosh Bgosh', 'Martina Perry', 2);
An organization deploys computer processing power and storage through a cloud provider's _____ services.
IaaS
Branches that are similar in length in an index hierarchy are _____.
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 _____.
block
Elements can be nested in _____.
both XML and JSON
Hash index entries are assigned to _____.
buckets
A single-level index is a file that contains column values and pointers to _____ containing the column _____.
rows, value
A database _____ is the implementation of database requirements in SQL with CREATE TABLE statements.
schema
MONEY is an example of a _____ type.
semantic
A replicated database maintains at least _____ or more replicas on _____ storage devices.
two, separate
Choose the recovery log that is generated by the schedule below.
1. start T1 2. start T2 3. update T1, AID, Aoriginal, Anew 4. commit T1 5. update T2, BID, Boriginal, Bnew 6. undo T2, BID, Boriginal 7. rollback T2
In a relational database model, an attribute refers to which concept?
A uniquely named tuple position
Which SQL statement adds a new column Fax to the Supplier table? Correct!
ALTER TABLE SupplierADD Fax VARCHAR(30);
Which SQL statement deletes the City column from the Supplier table?
ALTER TABLE SupplierDROP City;
What data type stores binary values?
BLOB
What is a characteristic of hash structure in a key-value database?
Buckets are stored on different machines, enabling horizontal scaling.
Refer to the Teacher and Class tables. Which foreign key action updates the TeacherID for the Web Development course when Bryan McNeal's TeacherID is updated to 45672?
CASCADE
In a document database, a ________ assigns documents to shards.
It is NOT key-value
Which type is complex?
JSON
Which information would be acceptable in a key-value database?
Key = car make, value = Ford
Which of the following is NOT a replica type?
Query
Refer to the initial Class table, SQL transaction, and final Class table, below. Which SAVEPOINT identifier is used to produce the final Class table?
S2
_____ allows all executed database instructions to be rolled back and to be restored in a prior transaction state.
SAVEPOINT
The _____ SQL statement does not alter any database data.
SELECT
A standardized spatial reference system is identified with a/an _____.
SRID
Refer to the Teacher and Class tables. What is the result of the SET NULL foreign key action when Bryan McNeal's TeacherID is changed to 45672?
The Web Development TeacherID is set to NULL.
What is wrong with the syntax of this XML?
The XML syntax is correct.
When a table is updated and the index block has no free space for a new index entry, what happens?
The block splits.
In a MongoDB shell, what happens when the following command executes? db.movies.insert({ name: "What About Bob?", year: 1991 })
The document is added to the collection with a generated _id
Why would mysql.connector.connect() throw an exception in a Connector/Python program?
The function was called with a username that does not have access to the database.
A database system has a database called onlineShop. What is the result of a CREATE statement that tries to create onlineShop a second time?
The statement produces an error that the database already exists.
A database administrator creates a new relational database, and a primary key is assigned within a table to assist with governing data. What does the administrator accomplish with this structural rule?
Unique identification of individual rows in the table
Which two rules apply to primary keys?
Values must be unique and may not be NULL
A big data application generates 20,000 data entries per second. This rate is called what?
Velocity
How are attributes documented in an entity-relationship diagram?
Within an entity rectangle.
An object-relational database is _____.
a relational database that supports an object type
The time required to read or write the first byte of data is known as _____.
access time
When working with multi-tables, a column that is available in all interleaved tables is known as a _____?
cluster key
A buffer manager retains _____ from the _____ to reduce data access time.
data blocks, file system
During database design, an ER _____ and _____ are usually developed in parallel.
diagram, glossary
A NoSQL database that represents data using XML or JSON is called a _____ database.
document
In the query processor layer, the query optimizer generates a/an _____.
execution plan
A NULL value represents _____ data.
missing
A database designer depicts a database as a graph with _____ representing entities.
vertices
Data was not saved before a system was accidentally powered off. This data was located in _____.
volatile memory
In a primary/secondary replica update for a distributed database, secondary nodes are updated _____.
with independent local transactions
Strict consistency is enforced in some document databases by directing read queries to _____.
the primary replica
In the MongoDB query, how is every student EXCEPT Larry selected? { name: { _____: _____ } }
$ne, "Larry"
In MySQL, a column has a SET type with 16 possible elements. How many bytes does each column value require?
2
Refer to the schedule below. The initial value of B is 7 and C is 3. What is the value of A after conflicting schedule executes?
2
How many bytes does POINT(18.5 -20) require in internal MySQL format?
24
Vertices in a graph database are similar to _____ in a relational table. Which language is created specifically for graph databases?
Gremlin
When performing a search, which scan type is fastest?
Index
What is a characteristic of a logical index?
Index does not change when a row moves to a new block.
________ means each vertex in a graph database contains a pointer to all connected vertices.
Index-free adjacency
What are the performance characteristics of a heap table?
Insert, delete, and update operations are optimized, but not read operations.
During database design, an entity with many optional attributes often becomes a(n) ______ entity.
Supertype
In Oracle Database, the object type _____.
can define either an individual column or an entire table
Operational data changes _____, while analytic data is updated _____.
in real time, at fixed intervals
Due to infrequent updates, many distributed databases use a _____ catalog.
replicated
A _____ is a collection of values with no inherent order.
set
Which statements should replace XXX and YYY in the Connector/Python program?
row[1], row[0]
Vertices in a graph database are similar to _____ in a relational table.
rows
When an entity-relationship model is implemented in SQL, an entity typically becomes a _____.
table
In two dimensions, a minimum bounding rectangle is the _____?
the smallest rectangle aligned with the X- and Y-axes that contains a spatial value
A database designer installs MySQL Community Edition to create a database. Which account does the designer use to gain full control of MySQL?
Root
Refer to the Teacher and Class tables. When Bryan McNeal was deleted from the Teacher table, the TeacherID for the Web Development course was automatically changed to 11234. Which foreign key action is the TeacherID likely using?
SET DEFAULT
A ____ is a sequential order of database instructions for multiple transactions.
Schedule
Which isolation level prevents dirty, non-repeatable, and phantom reads?
Serializable
Which approach splits data sets across multiple systems?
Sharding
_______ entities have many common attributes and relationships.
Similar
In terms of database architecture, which component translates the query processor instructions into low-level file-system commands and is responsible for indexing the data?
Storage manager
What does SQL stand for?
Structured Query Language
What design consideration would apply to a database that has special performance requirements?
Structuring data properly on storage media.
In database APIs, ____ a query is a term used when compiling happens before executing the query.
preparing
A column, or group of columns, that serves as the unique identifier in a relational database table is called a/an _____.
primary key
In a logical index, pointers to table blocks are replaced with _____ values.
primary key
Which of the following values violates the CHECK constraint below?
(456, "Sarah Mcgraw", 61, "12301 270th Pl, Seattle, WA 98126")
Evaluate the given data and determine the correct result from the statement.
485
When considering an attribute maximum, which term represents when each attribute instance describes at most one entity instance?
Unique
What should be added so NULL values are not allowed in ProductName?
VARCHAR(50) NOT NULL
Choose the best data types to replace XXX and YYY.
VARCHAR(50), DECIMAL(8, 2)
_____ architecture is an example of a modern multi-tier architecture.
Web
To fulfill data processing requirements in a growing transactional database, _____ scales up by increasing processing power of a limited number of systems.
vertical scaling
A NoSQL database that represents data as a key with multiple values is called a _____ database.
wide column
How many columns are created by the SQL statement below?
6
Assume the lastName value is obtained from a submitted form. Which values should replace XXX and YYY in the PHP code?
:lastName , "lastName"
Which option is found on the Schemas tab in MySQL Workbench?
A list of available databases.
A primary replica has failed in a document database. What happens as a result?
A secondary replica becomes active
What links a host programming language to a database system?
API
Which of the following database programming techniques is commonly used with object-oriented programming languages?
API
Refer to the table and schedule below. The UPDATE statement in transaction T1 holds an exclusive lock on the Class table. With strict two-phase locking, when does the SELECT statement in transaction T2 execute?
After the ROLLBACK statement in T1 executes
Refer to the stored procedure. What is the correct call syntax to get the number of classes taught by Maggie Wilson with ID - 45631 through a stored procedure call from the command line?
CALL TeacherClassLoad(45631, @numberofClasses)
Which of the following is a characteristic of a B-tree index, but not a B+tree index?
Column values do not repeat at lower levels.
Which database role focuses on database storage, response times, and optimization?
Designer
The _____ keyword helps the compiler identify embedded SQL statements in a program.
EXEC SQL
_____ is a recovery technique that creates a nearly synchronized backup of the primary database on another database server.
Hot backup
Which data type should a database use to store negative numbers?
INT
When using the MySQL Workbench GUI, which icon will execute an SQL statement?
Lightning bolt
A _____ is a part of the concurrency system that monitors, grants, and releases locks.
Lock Manager
How does a database system protect data when concurrent transactions occur?
Preventing multiple transactions with the same data at the same time.
Using a cloud-based database comes with risk. _____ is a regulatory risk.
Privacy
Which of the following database programming techniques is an extension of the SQL language that is typically used for database applications?
Procedural SQL
The entity Book includes Paperback, Hardcover, and Digital books. The attribute ChapterCount applies to both PaperBack and HardCover but not Digital. Digital has a TextToSpeech attribute. Which entity type is Digital?
Subtype
Which column is best to replace XXX in the SQL statement below?
SupplierId
What is the result of a relational operation?
Table
Which table type interleaves rows of two or more tables in the same storage area?
Table clusters
A user creates a table by using a SQL statement. The data type VARCHAR(11) is part of the statement. What does the value of (11) represent?
The number of characters allowed for the data type.
If a database system is processing three queries as part of a transaction and the third query fails, what happens to the transaction?
The successful query results are reversed, and the transaction is canceled.
Which deadlock management technique automatically rolls back a transaction when a lock is not released in a fixed period of time?
Timeout
What is the purpose of an entity synonym?
To reflect common name usage of the entity.
Which MySQL layer interacts directly with database users and administrators?
Tools
A database contains several relationships. Which is a valid relationship name? Correct!
Toys-Contains-Dolls
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?
Transaction failure
A recovery system should manage which three failure scenarios?
Transaction failure, System failure, and Storage media failure
MySQL stores JSON values as _____.
an internal binary format
In the Reservation table below, a room may be reserved several times, so the RoomNumber column is not unique. To ensure that a room can only be reserved by only one guest for the day, the minimal primary key consists of which columns?
(RoomNumber, DateOfStay)
How many types are possible for elements of a JSON document?
6
Refer to the schedule below. The initial value of B is 7 and C is 3. What is the value of A after the schedule executes?
8
When using the MySQL Command-Line Client, which character ends a command?
;
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.
Which of the following is not true about the INSERT statement?
A single INSERT statement can only add one row.
A developer wishes to link to a database with a utility created in C. Which MySQL component does the developer use for the link?
API
A database team plans the creation of a new database. During which phase does the team capture data requirements?
Analysis
What is the fan-out for a multi-level index where index entries are 32 bytes and index blocks are 10 kilobytes?
Approximately 300
In the following ER diagram, what does 'AlbumTitle' represent?
Attribute
Which MySQL component retains data blocks from storage in main memory for possible reuse?
Buffer manager
In MySQL, which of the following SQL operations always commits immediately?
CREATE
What is the correct statement for creating a database called reservationDB?
CREATE DATABASE reservationDB;
Which wide-column database has no single point of failure and is always available?
Cassandra
Which of the following is an example of database programming?
Creating a web application using Java and SQL
When using a SQL statement to create a table, which data type is used to store a fractional value?
DECIMAL
A _____ constraint is used to define a column value that is not NULL when no value is provided.
DEFAULT
What is the correct statement for deleting a database?
DROP DATABASE <databaseName>;
What is the correct SQL statement for deleting the table Supplier?
DROP TABLE Supplier;
Which language defines statements used for creating and dropping tables?
Data Definition Language
The statement below is an example from which SQL sublanguage?
Data Query Language
In a PHP application using PDO, a _____ consists of the host, port, and name of the database that will be used to establish a connection to the database.
Data Source Name
An organization has a main office and three satellite locations. Data specific to each location is stored locally in which configuration?
Distributed
_____ allows a programmer to create an SQL query with a PREPARE statement at run-time.
Dynamic SQL
Which NoSQL database uses tables that consist of a key and a group of items?
DynamoDB
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.
In the Entity-Has-Attribute relationship, what is a required attribute?
Each entity instance has at least one attribute instance.
Which of the following is a set of things in entity-relationship modeling?
Entity
Which of the following required keywords specifies if the trigger will affect all rows of the table?
FOR EACH ROW
Which statement is not a special case of foreign keys? Correct!
Foreign key referring to a foreign key in the same table
Which of the following does NOT appear in the Oracle Database's CREATE TYPE statement below?
Function body
A database admin configures replication for a distributed database. Which technique applies updates to any available node?
Group replication
Which table type might use the modulo function to scramble row locations?
Hash
Which graph database physical structure might combine a graph and a relational database?
Layered
A database administrator is updating one of the tables in the database. Which of the following techniques can prevent other database administrators from doing concurrent transactions to the same table?
Locking
Elements of the _____ type can be repeated and are not ordered.
MULTISET
Which concept relates to software that reads and writes data in a database?
Management system
All spare parts belong to a product. A product must exist before spare parts become available and when a product is removed, spare parts are also removed. What type of entity is Product?
Master entity
What is the greatest number of instances of one entity that can relate to a single instance of another entity?
Maximum
In MySQL, what is the result of TRUE AND NULL?
NULL
What occurs when a distributed database experiences a network error and nodes cannot communicate?
Network partition
Assume that databaseConnection is a valid connection to the Faculty database in a Connector/Python program. What is the output of the code below?
No change on the Teacher table because the dbQuery has not been executed.
Which type of database system is optimized for big data?
NoSQL
What foreign key action should be added to ensure that if a supplier is removed from the Supplier table, the products associated with the same supplier are also removed?
ON DELETE CASCADE
A developer wants to create an application using the PHP language. Which of the following APIs can the developer use to interact with the database?
PDO
Select the correct WKT syntax for a polygon.
POLYGON( (0 0, 20 10, 10 20, 0 0) )
An organization uses a database and application development tools through a cloud provider's _____ services.
PaaS
Which principle defines data independence?
Physical design never affects query results.
In the SQL code below, which of the following is an identifier?
Product
Which role focuses on creating software that interacts with a database?
Programmer
Which term describes the meaning of an attribute value?
Qualifier
What does a user that interacts with a database use to read and write data?
Query language
Which statement should replace XXX?
READS SQL DATA
Refer to the Teacher and Class tables. To maintain referential integrity, which foreign key action rejects the deletion of the row containing Rosa Lopez?
RESTRICT
In which isolation level can dirty reads occur?
Read uncommitted
Refer to the sequence below. What is the isolation level of transaction E?
Repeatable read
A database administrator uses which two SQL statements to view and then modify existing customer balances with a late fee?
SELECT, UPDATE
_____ is an ISO standard that extends procedural SQL for use in stored procedures.
SQL/PSM
The analysis phase of database design includes which process?
Specifying requirements that are not dependent on a specific database system.
A database designer wants to create three tables: Supplier, Product, and Country. The Supplier table has a CountryId column with values that must appear in the Country table's CountryId column. The Product table has an auto-increment column. Which table's CREATE TABLE statement(s) must specify a FOREIGN KEY?
Supplier
In MySQL, what is the result of TRUE OR NULL?
TRUE
What are two characteristics of an efficient bitmap index?
The database can quickly determine the block containing a table row from the index row number. Any indexed column contains relatively few distinct values.
Which popular ER model diagramming convention for cardinality depicts "many" in a relationship?
Three lines, called crow's feet, at the end of a relationship
In a relational database model, which concept refers to a finite sequence of values that are derived from defined domains?
Tuple
Refer to the Supplier table. Which statement correctly changes Adan Stevens to Maria Stevens?
UPDATE Supplier SET ContactName = 'Maria Stevens' WHERE SupplierId = 5;
Which document type is commonly used by document databases?
XML
With column-oriented storage, a block stores values for _____.
a single column
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.
atomicity
A transaction _____ indicates the starting and ending statement of a database transaction.
boundary
What is converted into 0, 1, and M specifications during database design?
business rules
A _____ index is an index on a non-unique sort column.
clustering
The PosgreSQL ARRAY type is an example of a _____ type.
collection
A document database stores data as _____, while a relational database stores data as _____.
collections, tables
A _____ is a collection of values that are of the same type.
column
In the recovery log below, line 6 is a/an _____.
compensation record
In an object-programming language, a class is like a _____, and an object is like a database _____.
composite type, row
A/An _____ is a rule enforced on a table's data.
constraint
Within each row of a wide column database, all columns of a family are stored ____ to optimize performance of read queries.
contiguously
A/An _____ will retrieve rows from a query that has multiple rows in the result set.
cursor
In a Connector/Python program, the _____ method prevents _____, which is a type of attack that causes a program to execute malicious SQL statements.
cursor.execute(), SQL injection
Which of the following Connector/Python cursor properties returns the number of items affected by a query?
cursor.rowcount
A database admin uses a SHOW statement to retrieve information about objects in a database. This information is contained in a _____.
data dictionary
For reporting purposes, an organization establishes a(n) _____, which is separate from the operational database.
data warehouse
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 an online bank application that transfers funds from a checking account to a savings account, the _____ property ensures that the changes made to each account will not be lost due to a computer failure.
durability
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 _____.
elasticity
A database management system reads and writes data in a database, and _____.
ensures consistency and availability
Data warehouses should be refreshed periodically. To start, all data should be _____.
extracted to a temporary database
Refer to the Teacher and Class tables. The TeacherID in the Class table references the TeacherID in the Teacher table. The TeacherID in the Class table is a/an _____.
foreign key
A wide column database is similar to a relational database except that it does not support _____.
foreign keys and joins
When data is produced and stored as numeric, textual, or visual information, it varies in _____.
format
A foreign key in which all columns are NULL is known as a _____ foreign key?
fully NULL
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.
function
A complex type _____.
has a richer internal structure than a simple type
Programming languages fall into either _____ or _____ paradigms.
imperative, declarative
In some ER modeling conventions, _____ entities are called strong and _____ entities are called weak.
independent, dependent
During a data _____ phase, a database administrator refreshes a data warehouse and replaces incompatible keys with consistent values.
integration
In an online bank application that transfers funds from a checking account to a savings account, the _____ property ensures that another transaction sees the transferred funds in one account or the other, but not in both, nor in neither.
isolation
Distinguishing independent and dependent entities is a(n) _____ process.
iterative
UPDATE, SELECT, and ORDER BY are _____ in an SQL statement.
keywords
When creating an ER diagram, a dependency relationship is drawn with an arrow pointing to a _____ entity.
master
An R-tree has a ____ index with index entries containing ____.
multi-level, MBRs
In MySQL, spatial values _____ numeric values.
must be converted to numeric values using spatial functions prior to comparing with
For integrity purposes, a distributed transaction requires all or _____ nodes to be updated in order to complete the transaction.
no
When only one value exists in each cell, a table is known to be _____.
normalized
A database designer interviews a sales team that will be using a new database. During the interview, the designer documents entities as _____, and relationships as _____.
nouns, verbs
What should be added to the SQL statements to produce the Result table below?
onlineShop, TABLES
A value that is used in a computation is known as a/an _____.
operand
Paperback and hardcover books are mutually exclusive subsets of books. PaperBackBook and HardCoverBook _____ the supertype entity Book.
partition
The sort column in a sorted table determines the _____ row order.
physical
An application may use a _____ API, which in turn uses a _____ API to access unsupported data sources.
primary, secondary
API defines ____ for applications written in a procedural programming language and _____ for applications in object-oriented languages.
procedure calls, classes
When a user interacts with a database, they can use a _____ to modify data with commands.
query language
A document that sequentially writes all the database operations is known as a/an _____.
recovery log
An operational database contains tables in third normal form. This design reduces _____.
redundancy
A magnetic disk groups data into _____.
sectors
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 reads index blocks _____.
sequentially
One goal of object-relational database technology is to _____.
simplify database programming
When concurrently processing instructions, a shared memory computer works well with a common data set in a _____ memory space.
single
The bottom level of a multi-level index is a _____index.
sorted single-level
Entities that share common attributes and relationships become part of a new _____ entity.
supertype
A _____ is a type of UNIQUE constraint applied to two or more columns.
table-level constraint
A relational database uses _____ to structure all data.
tables
When an entity-relationship model diagram is implemented within SQL, entities typically become _____, and relationships typically become _____.
tables, foreign keys
A database _____ is a unit of work that is treated as a whole. It is either completed as a unit or failed as a unit.
transaction
In order to run a program with Embedded SQL, the compiler _____.
translates the SQL code to the host language first, and then to an executable program
A _____ database stores related data as column families.
wide column
A relationship minimum is the least number of instances of one entity that relates to a single instance of another entity. On an ER diagram, minima are usually specified as a _____ or _____.
zero, one
