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
