EGR 325 Final Exam

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 bytes and index blocks are 10 kilobytes?

Approximately 300

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? CREATE PROCEDURE TeacherClassLoad(IN teachID INT, OUT numberOfClasses INT) SELECT COUNT(*) INTO numberOfClasses FROM Class WHERE TeacherID = teachID;

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 language defines statements used for creating and dropping tables?

Data Definition Language, DDL

Database queries are commonly implemented using a declarative programming language because of which two main reasons?

Easier programming and faster execution

Which table type might use the modulo function to scramble row locations?

Hash

When performing a search, which scan type is fastest?

Index

Which concept relates to software that reads and writes data in a database?

Management System

Which type of database system is optimized for big data?

NoSQL

Select the correct WKT syntax for a polygon.

POLYGON( (0 0, 20 10, 10 20, 00) )

Which principle defines data independence?

Physical design never affects query results

How does a database system protect data when concurrent transactions occur?

Preventing multiple transactions with the same data at the same time

Which role focuses on creating software that interacts with a database?

Programmer

What does a user that interacts with a database use to read and write data?

Query language

A standardized spatial reference system is identified with a/an _____.

SRID

Which isolation level prevents dirty, non-repeatable, and phantom reads?

Serializable

The analysis phase of database design includes which process?

Specifying requirements that are not dependent on a specific database system

Which document type is commonly used by document databases?

XML

Given a table named store with 5 fields: store_id, address, city, state, zipcode, why would the following insert command not work? insert into store values ('234 Park Street')

You must specify the fields to insert if you are only inserting some of the fields.

An object-relational database is _____.

a relational database that supports an object type

An entity type name should be all of the following EXCEPT:

as short as possible

The property by which subtype entities possess the values of all attributes of a supertype is called:

attribute inheritance

A _____ index is an index on a non-unique sort column.

clustering

A/An _____ will retrieve rows from a query that has multiple rows in the result set.

cursor

What command results in an empty students collection?

db.students.remove({})

To eliminate duplicate rows in a query, the ________ qualifier is used in the SQL Select command.

distinct

The logical representation of an organization's data is called a(n):

entity relationship diagram

In the query processor layer, the query optimizer generates a/an _____.

execution plan

A pair of related values in a database is a(n) _____.

fact

The first part of an SQL query to be read is the ________ statement.

from

The _____ method of the _____ class creates a connection object in Connector/J.

getConnection(), DriverManager

The last part of an SQL query to be read is the ________ statement.

order by

Which of the following is NOT a replica type?

query

When a user interacts with a database, they can use a _____ to modify data with commands.

query language

Vertices in a graph database are similar to _____ in a relational table.

rows

A view may not be updated directly if it contains:

the having clause

In the MongoDB query, how is every student EXCEPT Larry selected? { name: { _____: _____ } }

$ne, "Larry"

Given the autos collection, what condition sets all sold fields to true? db.autos.update({ price: {$gte 13500} },{ _____ }, { multi: true })

$set: { sold: true }

How many bytes does POINT(18.5 -20) require in internal MySQL format?

24

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

6

In which of the following situations would one have to use an outer join in order to obtain the desired results?

A report is desired that lists all customers and the total of their orders during the most recent month, and includes customers who did not place an order during the month (their total will be zero)

What links a host programming language to a database system?

API

What does the following SQL statement do? Alter Table Customer_T Add (Type Varchar (2));

Alters the Customer_T table and adds a field called "Type"

The following stored procedure computes the total of employee salaries: CREATE PROCEDURE TotalSalary(OUT total INT) SELECT SUM(Salary) INTO total FROM Employee; Given a connection object conn, the following Connector/J code fragment executes the stored procedure. What XXX and YYY complete the code? XXX statement = conn.prepareCall("{call TotalSalary(?)}"); statement.YYY(1, java.sql.Types.INTEGER); statement.executeQuery();

CallableStatement, registerOutParameter

Which of the following counts ONLY rows that contain a value?

Count(column_name)

Which database role focuses on database storage, response times, and optimization?

Designer

The EmployeeWorkspace table has a composite key of (EmployeeID, WorkspaceID). WorkHours depends on (EmployeeID, WorkspaceID), and EmployeeLastName depends on EmployeeID. Which column must be removed so EmployeeWorkspace is in second normal form?

EmployeeLastName

Which of the following questions is answered by the SQL statement? Select Count (Product_Description) from Product_T;

How many products have product descriptions in the Product Table?

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

IaaS

What are the performance characteristics of a heap table?

Insert, delete, and update operations are optimized, but not read operations

Assume that databaseConnection is a valid connection to the Faculty database in a Connector/Python program. What is the output of the code below? dbCursor = databaseConnection.cursor() dbQuery = ('UPDATE Teacher SET FirstName = "Mary" WHERE TeacherID=45631') databaseConnection.commit() dbCursor.close() dbConnection.close()

No change on the Teacher table because the dbQuery has not been executed.

In which isolation level can dirty reads occur?

Read uncommitted

_____ is an ISO standard that extends procedural SQL for use in stored procedures.

SQL/PSM

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 design consideration would apply to a database that has special performance requirements?

Structuring data properly on storage media

When a table is updated and the index block has no free space for a new index entry, what happens?

The block splits

Which statement is NOT an advantage of using a view table?

The creation of a new base table is always up to date

In a relational database model, which concept refers to a finite sequence of values that are derived from defined domains?

Tuple

In Connector/J, what is an advantage of executing SQL with a PreparedStatement instead of a Statement?

Using PreparedStatement helps prevent SQL injection attacks.

Which two rules apply to primary keys?

Values must be unique and may not be null

Branches that are similar in length in an index hierarchy are _____.

balanced

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

base

Elements can be nested in _____.

both XML and JSON

A transaction _____ indicates the starting and ending statement of a database transaction.

boundary

Hash index entries are assigned to _____.

buckets

In the recovery log below, line 6 is a/an _____. 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

compensation record

An attribute that uniquely identifies an entity and consists of a composite attribute is called a(n):

composite identifier

A buffer manager retains _____ from the _____ to reduce data access time.

data blocks, file system

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

deadlock

When using a SQL statement to create a table, which data type is used to store a fractional value?

decimal

Geographic data is _____.

defined with reference to the surface of the earth

A NoSQL database that represents data using XML or JSON is called a _____ database.

document

Any create command may be reversed by using a(n) ________ command.

drop

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

When using MySQL, it is possible to use an AFTER trigger to update NEW values.

false

Given the autos collection, complete the statement to return all documents. db.autos._____({ year: { _____ } }) [

find, $gte: 2012

Which of the following required keywords specifies if the trigger will affect all rows of the table?

for each row

Which goes in the table on the "many" side of a one-many relationship?

foreign key

In a one-one relationship, the _____ key is often placed in the table with fewer rows. This minimizes the number of _____ values.

foreign, null

Which of the following can produce scalar and vector aggregates?

group by

A database admin configures replication for a distributed database. Which technique applies updates to any available node?

group replication

A subset of table rows is called a _____.

horizontal partition

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

hot backup

A(n) ________ is the relationship between a weak entity type and its owner.

identifying relationship

Which statements should replace XXX and YYY to execute the SELECT statement when the teacherID value is valid? CREATE PROCEDURE TeacherClassLoad(IN teachID INT) BEGIN DECLARE numberOfClasses INT; XXX SELECT COUNT(*) INTO numberOfClasses FROM Class WHERE TeacherID = teachID; YYY; END;

if teachID is not null then, end if

Programming languages fall into either _____ or _____ paradigms.

imperative, declarative

A _____ is a part of the concurrency system that monitors, grants, and releases locks.

lock manager

A relationship where the minimum and maximum cardinality are both one is a(n) ________ relationship.

mandatory one

A student can attend five classes, each with a different professor. Each professor has 30 students. The relationship of students to professors is a ________ relationship.

many-to-many

When a database stores view data, it uses a _____ that depends on data in a corresponding _____.

materialized view, base table

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

What occurs when a distributed database experiences a network error and nodes cannot communicate?

network partition

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

no

All _____ columns depend on the _____ for a table to be in first normal form.

non-key, primary key

A database administrator uses _____ to eliminate redundancy. This is done by decomposing a table into two or more tables in higher normal form.

normalization

When only one value exists in each cell, a table is known to be _____.

normalized

Using a cloud-based database comes with risk. _____ is a regulatory risk.

privacy

Which statement should replace XXX? CREATE PROCEDURE TeacherClassLoad(IN teachID INT) BEGIN DECLARE numberOfClasses INT; XXX SELECT COUNT(*) INTO numberOfClasses FROM Class WHERE TeacherID = teachID; YYY; END;

reads sql data

Given the connection object conn, the following Connector/J code fragment prints the result of a query. What is XXX? Statement statement = conn.createStatement(); String sql = "SELECT ID, Name FROM Employee"; ResultSet result = statement.executeQuery(sql); while ( XXX ) { System.out.println( "Employee ID: " + result.getInt("ID") + "Employee Name: " + result.getString("Name")); }

result.next()

Which statements should replace XXX and YYY in the Connector/Python program? dbCursor = databaseConnection.cursor() dbQuery = ('SELECT LastName, FirstName FROM Teacher' 'WHERE TeacherId = %s') teacherData = (12346) dbCursor.execute(dbQuery, teacherData) for row in dbCursor.fetchall(): print('FirstName', XXX, 'Last Name', YYY) dbCursor.close()

row[1], row[0]

A single-level index is a file that contains column values and pointers to _____ containing the column _____.

rows, value

_____ allows all executed database instructions to be rolled back and to be restored in a prior transaction state.

savepoint

A database _____ is the implementation of database requirements in SQL with CREATE TABLE statements.

schema

A database administrator uses which two SQL statements to view and then modify existing customer balances with a late fee?

select, update

Relational databases are heavily based on the mathematical principle of:

set theory

Which of the following is NOT a keyword that could be used to define a parameter in a stored procedure?

static

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

timeout

Which MySQL layer interacts directly with database users and administrators?

tools

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 SELECT Statement must always be coupled with any cursor definition.

true

If stored procedure parameters have no keyword then it is assumed the parameter is an INPUT parameter.

true

In procedural SQL the BEGIN and END statements are comparable to curly braces in imperative programming languages like Java.

true

In an E-R diagram, there are/is ________ business rule(s) for every relationship.

two

Which relational algebra expression is equivalent to the following SQL statement? SELECT PassengerName FROM Booking WHERE TicketPrice 1000;

Π(PassengerName)(σ(TicketPrice<1000)(Booking))

API defines ____ for applications written in a procedural programming language and _____ for applications in object-oriented languages.

procedure calls, classes

Redundancy is possible in a _____ normal form table when one non-key column depends on another non-key column.

second

The _____ SQL statement does not alter any database data.

select

With SQL, how do you select all the records from a table named "Person" where the value of the column "FirstName" starts with an "a"?

select * from Person where FirstName like 'a%'

MONEY is an example of a _____ type.

semantic

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

semistructured

Which approach splits data sets across multiple systems?

sharding

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

The following code is an example of a: SELECT CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode FROM Customer_T WHERE Customer_T.CustomerID = (SELECT Order_T.CustomerID FROM Order_T WHERE OrderID = 1008);

subquery

The primary key of a _____ table is identical to the primary key of a supertype table.

subtype

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

Strict consistency is enforced in some document databases by directing read queries to _____.

the primary replica

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 big data application generates 20,000 data entries per second. This rate is called what?

velocity

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 database designer depicts a database as a graph with _____ representing entities.

vertices

An entity type whose existence depends on another entity type is called a ________ entity.

weak

_____ architecture is an example of a modern multi-tier architecture.

web

A NoSQL database that represents data as a key with multiple values is called a _____ database.

wide column

In a primary/secondary replica update for a distributed database, secondary nodes are updated _____.

with independent local transactions

A view table provides which benefit when created in a database with multiple tables?

A consolidated view of specific data that does not modify the underlying source tables

What will be returned when the following SQL query is executed? Select driver_no, count(*) as num_deliveries from deliveries group by driver_nohaving count(*) > 2;

A listing of all drivers who made more than 2 deliveries as well as a count of the number of deliveries

When creating a new stored procedure using SQL from the command line the following command is needed because DELIMITER //

The default delimiter is needed when adding the stored procedure code so an alternate delimiter must be defined

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

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

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

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

When a key-value database allows multiple values per key, how is each value identified?

by using a name for each value

A(n) ________ constraint is a type of constraint that addresses whether an instance of a supertype must also be an instance of at least one subtype.

completeness

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.

consistency