Database Design Final

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

$ne, "Larry"

given the autos collection, what condition sets all fields to true?

$set: {sold: true}

what does the following statement return? SELECT SUBSTRING('Excellent', 3, 4);

'cell'

what does the following statement return? SELECT DATEDIFF('2020-11-04', '2020-11-09');

-5

refer to the following tables. How many rows are in the table defined by the following relational algebra expression? Employee Union Student

0

Choose the recovery log that it generated by the schedule below. T1: Read B A = B +1 write A comment T2: read C B = C - 2 write B rollback

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, Borginal 7. rollback T2

a time dimension table tracks throughout a day by minute. how many rows does the table contain?

1440

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? (look at #1)

2

how many rows are in the table defined by the following relational algebra expression?

2

how many bytes does POINT(1.5-20) require in an internal MySQL format?

24

a date dimension table tracks data for two years. how many rows does the table contain?

730

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? (look at #10)

8

which character ends a command?

;

which statement replaces X and Y in the PHP code to call the stored procedure below?

?, @teacherLoadCount

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

Which of the following database programming techniques is commonly used with object-oriented programming languages?

API

A database team plans the creation of a new database. During which phase does the team capture data requirements?

Analysis

a table is in ________ normal form when all dependencies are on unique columns

Boyce-Codd

What is a characteristic of hash structure in a key-value database?

Buckets are stored on different machines, enabling horizontal scaling.

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

By using a name for each value.

refer to the stored procedure. What is the correct call syntax to get the number of classes taught by Maggie Wilson with ID- 45631

CALL TeacherClassLoad(45631, @numberofClasses)

In MySQL, which of the following SQL operations always commits immediately?

CREATE

the following tables describes parts and companies that supply parts. the following query selects all possible combinations of part names and supplier names. what ________ completes the query?

CROSS JOIN Supplier

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.

in a PHP application using PDO, a ________ consists of the host, port, and name of the database will be used to establish a connection to the database

Data Source Name

Which NoSQL database uses tables that consist of a key and a group of items?

DynamoDB

the ______ keyword helps the compiler identify embedded SQL statements in a program

EXEC SQL

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 is a set of things in entity-relationship modeling?

Entity

a _______ partition requires a partition expression with positive integer values

Hash

which statements should replace X and Y to execute the SELECT statement when the teacherID value is valid?

IF teachID IS NOT NULL, END IF

Refer to the Product table. Complete the SQL statement to select all products with sizes 0-3M, 3-6M, and 6-9M. SELECT ProductName, Quantity FROM Product WHERE Size ____ ('0-3M', '3-6M', '6-9M');

IN

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

IaaS

________ means each vertex in a graph database contains a pointer to all connected vertices.

Index-free adjacency

Which storage engine provides full support for transaction management and locking?

InnoDB

which type is complex?

JSON

Which information would be acceptable in a key-value database?

Key = car make, value = Ford

The following table lists parts. Many parts are contained in another part, called an assembly, and tracked in the AssemblyID column. If a part is not contained in another part, the AssemblyID is NULL. CREATE TABLE Part (PartID SMALLINT, PartName VARCHAR(30) AssemblyID SMALLINT, PRIMARY KEY (PartID), FOREIGN KEY (AssemblyID) REFERENCES Part(PartID)); The following self-join selects on row for each part. Each row contains the names of the part and the assembly, if any, containing the part. What XXX completes the query? SELECT P.PartName AS Part, A.PartName AS Assembly FROM Part PXXX;

LEFT JOIN Part A ON P.AssemblyID = A.PartID

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

Lock Manager

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

refer to the tables. which products are selected by the query below? SELECT ProductName FROM Product WHERE SupplierID IN (SELECT SupplierID) FROM Supplier WHERE CountryID = 2);

Onesies set, Sunsuit, Romper

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

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

Privacy

Complete the ORDER BY clause to sort the Products by ProductName alphabetically, then in decreasing Quantity. SELECT ProductName, Size, Quantity, SupplierID FROM Product ORDER BY _________;

ProductName, Quantity DESC

which statement should replace XXX? CREATE FUNCTION GetTeacherID(teacherLastName VARCHAR(20)) RETURNS INT XXX BEGIN DECLARE t_id INT; SELECT TeacherID INTO i_id FROM Teacher WHERE LastName = teacherLastName; END;

READS SQL DATA

refer to the tables. which query produces the result table below?

SELECT S.OrderID, C.CustomerName FROM Sales S INNER JOIN Customer C ON S.CustomerID = C.CustomerID

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

SQL/PSM

a standardized spatial reference system is identified with a/an _________

SRID

Refer to the tables. What should be added to the SELECT clause to produce the result table below? SELECT ____ AS TotalSales FROM Sales S INNER JOIN Product P on S. ProductId = P.ProductId

SUM(P.UnitPrice * S.Quantity)

a database administrator wants to correlate pairs of rows from the same table. which type of join should the database administrator use?

Self join

Which approach splits data sets across multiple systems?

Sharing

_______ entities have many common attributes and relationships.

Similar

An entity named Stadium contains a primary key SeatNumber and a plural attribute SeatReservation which records spectator seat assignments. During implementation, the plural attributes move to a new table named _____.

StadiumSeatReservation

Given the Oracle Database statement below, what is PlaceType? CREATE TYPE HomeType UNDER PlaceType ( Bedrooms INT, Bathrooms INT, OVERRIDING MEMBER FUNCTION SquareFeet RETURN INT );

Supertype

What is wrong in the following query? SELECT Sales.OrderId, Customer.FirstName, Customer.LastName FROM Sales INNER JOIN CustomerON CustomerID = CustomerID;

The CustomerID prefix is missing

What is wrong with the syntax of this XML?

The XML syntax is correct.

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 contains several relationships. which is a valid relationship name?

Toys-Contains-Dolls

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

Web

which document type is commonly used by document databases?

XML

an object relational database is _______

a relational database that supports an object type

how are books read with a single level index scan?

a scan reads all index to find table blocks that contain selected rows. The table blocks are then read

With column-oriented storage, a block stores values for _____.

a single column

The time required to read or write the first byte of data is known as ________.

access time

Refer to the tables. The Product's Quantity column stores the stockroom's product quantity before any products are sold. Which products are selected by the query below? SELECT ProductNameFrom Product P Where Quantity > (SELECT SUM(Quantity) FROM Sales WHERE ProductID = P. ProductID);

all products that are in stock

in Connector/J, executeQuery(statement) executes the SQL code associated with the statement object. Prior to execution, executeQuery() ______________

always compiles the SQL

MySQL stores JSON values as _____.

an internal binary format

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

the database designer creates a(n) _____ when no suitable single-column or composite primary key exists

artificial key

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

in a federated database, a(n) _______ database operates independently of other databases

autonomous

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

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

boundary

Hash index entries are assigned to _____.

buckets

Which MySQL component retains data blocks from storage in main memory for possible reuse?

buffer manager

in Oracle database, the object type ________

can define either an individual column or an entire table

in a multitable, a ______ column is available in all interleaved tables

cluster key

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

clustering

a document database stores data as _____, while a relational database stores data as _______

collections, tables

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

a database administrator normalizes a table to Boyce-Codd normal form. this involves identifying dependencies on non-unique _________

columns

a hierarchy in a dimension table contains a series of ________ where each has a _______ relationship to the next

columns, one-many

In the recovery log below, line 6 is a/an _____.

compensation record

Within each row of a wide column database, all columns of a family are stored ____ to optimize performance of read queries.

contiguously

in a data lake, data is often loaded ________ rather than _______

continuously, periodically

A/an _________ is a subquery that contains a reference to a table column that also appears in the outer query

correlated subquery

which of the following is an example of database programming?

creating a web application using Java and SQL

What type of join is executed by the following relational algebra expression? Department X Department.Manager = Employee.IDEmployee

cross

A _______ at the end of a relationship depicts cardinality "many"

crow's foot symbol

a/an ___________ will receive 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 foofthe following Connector/Python cursor properties returns the number of items affected by a query?

cursor.rowcount

raw data that is copied from multiple data sources is called what?

data lake

For reporting purposes, an organization establishes a(n) _______, which is separate from the operational database.

data warehouse

what command results in an empty students collection?

db.students.remove({})

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

What can simplify and accelerate SELECT queries with tables that experience infrequent use?

denormalization

which table type do plural attributes become?

dependent

a meaningless primary key contains no ________

descriptive information

in a data warehouse that uses a dimensional design, a studentID column is found in which table?

dimension

an organization has a main office and 3 satellite locations. Data specific to each location is stored locally in which configuration?

distributed

an organization has a main office and three satellite locations. Data specific to each location is stored locally in which configuration?

distributed

a NoSQL database that represents data using XML or JSON is called a _______ database

document

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

easier programming and faster execution

a retailer using a cloud-based transaction database is experiencing sales fluctuations through the year. the ability for cloud resources to adapt to such change in utilization is called __________

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

which of the following is a set of things in an entity-relationship modeling?

entity

in the query processor layer, the query optimizer generates a/an _________

execution plan

data warehouses should be refreshed periodically. To start, all data should be _________

extracted to a temporary database

a primary key of a ______ is the composite of the foreign keys that referecne a _______

fact table, dimension table

given the autos collection, complete the statement to return all documents

find, $gte: 2012

in a one-one relationship, the ______ key is often placed in the table with fewer rows, this minimizes the number of _______ values

foreign, NULL

refer to the tables below. which query produces the result table below? SELECT DISTINCT Size FROM Product;

four rows

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

which of the following does NOT appear in the Oracle Database's CREATE TYPE statement below? CREATE TYPE NameType AS OBJECT()

function body

the __________ method of the __________ class creates a connection object in Connector/J

getConnection(), DriverManager

where is the list of attribute types?

glossary

Which language is created specifically for graph databases

gremlin

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

group replication

a complex type ________

has a richer internal structure than a simple type

which table structure might use the modulo function to locate rows?

hash

a ______ assigns each new row to a group of linked blocks, called a bucket

hash table

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 sercer

hot backup

When creating an ER diagram, an identifying relationship always connects a(n) _____ entity and a(n) _____ entity.

identifying, weak

SELECT DISTINCT Size FROM Product;

ignores duplicates

programming languages fall into either ______ or _______ paradigms

imperative, declarative

operational data changes _______, while analytic data is updated ________

in real time, at fixed intervals

The heap table structure is optimized for _____

inserting new rows

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

integration

denormalization eliminates ______ queries, and therefore, query performance is improved

join

In the SQL query below, the Teacher table is the _________-. SELECT LastName, FirstName, CourseTitle FROM Teacher INNER JOIN Class ON Teacher.TeacherID = Cass.TeacherID

left table

a _________ is a part of the currency system that monitors, grants, and releases locks

lock manager

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

when implementing a __________ relationship, a new table contains two foreign keys, refering to the primary keys of the related tables

many-many

elements of the __________ type can be repeated and are not ordered.

multiset

In MySQL, spatial values _____ numeric values

must be converted to numeric values using spatial functions prior to comparing with

which 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

Redundancy may occur when a column depends on a _____ column.

non-unique

in relational algebra, comparable types have the same ______

number of columns and data types

the sort column in a sorted table determines the ________ row order

physical

In database APIs, ____ a query is a term used when compiling happens before executing the query.

preparing

in a logical index, pointers to table blocks are replaced with _______ values

primary key

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

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

procedure calls, classes

which of the following is NOT a replica type?

query

in a document database, a _______ assigns documents to shards

range function

in which isolation level can dirty reads occur?

read uncommitted

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

which relational algebra operation is denoted by the greek letter p?

rename

Refer to the sequence below. What is the isolation level of transaction E?

repeatable read

Due to infrequent updates, many distributed databases use a _____ catalog.

replicated

Each course must be offered by at least one department. In the Department-Offers-Course relationship, Department is a(n) _____ entity.

required

Given the connection object conn, the following Connector/J code fragment prints the result as a query. What is XXX? (Block of code starting with:) Statement statement = conn.CreateStatement();

result.next()

which statements should replace XXX an 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]

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

rows

a single level index is a file that contains column values and pointers to ______ containing the column ______

rows, value

what approach can a database administrator use to assess the effectiveness of indexes when investigating slow queries?

run EXPLAIN on queries

a _______ is a sequential order of database instructions for multiple transactions

schedule

A magnetic disk groups data into _______.

sectors

MONEY is an example of a ________ type

semantic

in _______ data, each element is named, and element types are not explicitly declared

semistructured

in ________ data, each element is named, and elements are not explicitly declared

semistructured

in order to locate rows selected by a query, an index scan reads index blocks ________

sequentially

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

serializable

which approach splits data sets across multiple systems?

sharding

One goal of object-relational database technology is to _____.

simplify database programming

the bottom level of a multi-level index is a ______ index

sorted single-level

in MySQL with InnoDB, tables with a primary key have a ______ structure, while those without a primary key have a _____ structure

sorted, heap

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

attributes of a_____ entity apply to all ____ entities

supertype, subtype

when an entity-relationship model is implemented in SQL, an entity typically becomes a ________

table

What object maps one or more tables to a single file?

tablespace

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? T1 | T2 UPDATE Class | SET TeacherID = 32412 | | SELECT CourseTitle | FROM Class | WHERE ClassID = 80;ROLLBACK; |

the SELECT statement in T2 will never execute

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

what are the 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

in MongoDB shell, what happens when the following command executes?

the document is added to the collection with a generated _id

An Inventory view table is defined with a WHERE clause that specifies ShelfNumber = 25. An INSERT adds a new item to ShelfNumber 20. What happens when the WITH CHECK OPTION is not specified in the CREATE VIEW statement?

the item appears in the base table but not in the view table

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

the primary replica

in two dimensions, a minimum bonding rectangle is the _____?

the smallest rectangle aligned with the X- and Y- axes that contains a spatial value

A table has:- columns A, B, C, D- candidate keys (A, B) and C- primary ket (A,B) If D --> A is the only dependency , aside from dependencies on candidate keys, the table is in _____ normal form.

third but not Boyce-Codd

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

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

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 3 failure scenarios?

transaction failure, system failure, and storage media failure

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(n) ________ attribute instance describes at most one entity instance

unique

Each value of a _____ attribute describes at most one entity instance.

unique

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

using PreparedStatement helps prevent SQL injection attacks

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 graph with ______ representing entities

verticies

Data was not saved before a system was accidentally powered off. This data was located in _____.

volatile memory

a model has Shipment, Product, and Shipper entities. The Shipment entity has no unique attributes. Each shipment is identified by ProductNumber, ShipperCode, and ShipmentDate. Shipment is a(n) ______ entity

weak

a NoSQL database that represents data as a key with multiple values is called a ________ database

wide column

a _______ database stores related data as column families

wide column

relationship minima are usually specified as ______ or _______

zero, one