Database Final Exam
A big data application generates 20,000 data entries per second. This rate is called what? a. velocity b. volume c. variety d. veracity
a
A database administrator normalizes a table to Boyce-Codd normal form. This involves identifying dependencies on non-unique ____. a. columns b. indexes c. rows d. relationships
a
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? a. PDO b. ADO.NET c. SQL/CLI d. JDBC
a
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. a. weak b. strong c. subtype d. identifying
a
Geographic data is ____. a. defined with reference to the surface of the earth b. not supported in MySQL c. another name for geometric data d. any two-dimensional spatial data
a
Given the Oracle Database statement below, what is PlaceType? CREATE TYPE HomeType UNDER PlaceType ( Bedrooms INT, Bathrooms INT, OVERRIDING MEMBER FUNCTION SquareFeet RETURN INT ); a. Supertype b. Object that does not use NOT FINAL keywords c. Subtype d. Table that implements the PlaceType object
a
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. a. atomicity b. durability c. consistency d. isolation
a
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'); a. IN b. BETWEEN c. NOT IN d. =
a
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? T1 | T2 read B | A = B + 1 | write A | commit | | read C | B = C - 2 | write B | commit a. 8 b. 2 c. 4 d. 7
a
Refer to the tables below. Which join completes the SQL query and produces the result table below? SELECT CourseTitle, FirstName, LastName FROM Class _____ Teacher ON Class.TeacherID = Teacher.TeacherID; a. INNER JOIN b. LEFT JOIN c. RIGHT JOIN d. FULL JOIN
a
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 a. SUM(P.UnitPrice * S.Quantity) b. MAX(P.UniPrice * S.Quantity) c. P.UnitPrice * S.Quantity d. P.UnitPrice + S.Quantity
a
Vertices in a graph database are similar to ____ in a relational table. Which language is created specifically for graph databases? a. Gremlin b. Python c. GraphAccelerator d. SQL
a
Which NoSQL database uses tables that consist of a key and a group of items? a. DynamoDB b. Oracle NoSQL c. Redis d. SQL
a
_____ is a recovery technique that creates a nearly synchronized backup of the primary database on another database server. a. hot backup b. storage backup c. data backup d. cold backup
a
A ____ is a part of the concurrency system that monitors, grants, and releases locks. a. lock optimizer b. lock manager c. lock system d. lock administrator
b
A database administrator wants to correlate pairs of rows from the same table. Which type of join should the database administrator use? a. cross-join b. self-join c. left join d. full join
b
A database contains several relationships. Which is a valid relationship name? a. Manager-Department-Manages b. Toys-Contains-Dolls c. Manufactures-Provides-Widgets d. IsSuppliedBy-Vendors-Manufacturers
b
A database designer suggests using a database that is packaged with a programming language. Which of the following databases is a good choice? a. Federated database b. Embedded database c. Autonomous database d. In-Memory database
b
A meaningless primary key contains no ____. a. large values b. descriptive information c. character values d. numeric values
b
A relational database uses row-oriented storage to store an entire row within one ____. a. table b. block c. sector d. page
b
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? a. The item appears in the base table and the view table b. The item appears in the base table but not in the view table c. The INSERT accepts the data and displays an error d. The INSERT rejects the data with no error
b
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? a. The item appears in the base table and the view table b. The item appears in the base table but not in the view table c. The INSERT rejects the data with no error d. The INSERT accepts the data and displays an error
b
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() a. There would be an error. b. No change on the Teacher table because the dbQuery has not be executed. c. The Teacher table is updated with the new values. d. No result because no database connection was created.
b
Database queries are commonly implemented using a declarative programming language because of which two main reasons? a. ability to reuse classes and easier to learn b. easier programming and faster execution c. requires no translator and directly understood by the computer d. less storage requirements and can be easily modified
b
During a data ____ phase, a database administrator refreshes a data warehouse and replaces incompatible keys with consistent values. a. restructuring b. integration c. cleansing d. extraction
b
Elements that can be nested in ____. a. neither XML nor JSON b. both XML and JSON c. JSON but not XML d. XML but not JSON
b
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. a. PDO Object b. Data Source Name c. Data Connection d. PDO Exception
b
In a multi-table, a ____ column is available in all interleaved tables. a. bucket b. cluster key c. sort d. hash function
b
In the MongoDB query, how is every student EXCEPT Larry selected? { name: { ____: ____ } } a. $—, "Larry" b. $ne, "Larry" c. $eq, "Larry" d. $!=, "Larry"
b
In the SQL query below, the Teacher table is the _____. SELECT LastName, FirstName, CourseTitle FROM Teacher INNER JOIN Class ON Teacher.TeacherID = Class.TeacherID a. right table b. left table c. source table d. main table
b
MONEY is an example of a ____ type. a. integer b. semantic c. character d. binary
b
Programming languages fall into either ____ or ____ paradigms. a. declarative, object-oriented b. imperative, declarative c. declarative, functional d. imperative, object-oriented
b
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; a. CALL TeacherClassLoad(45631, numberofClasses()) b. CALL TeacherClassLoad(45631, @numberofClasses) c. CALL TeacherClassLoad("45631", "numberofClasses") d. CALL TeacherClassLoad("45631", @numberofClasses)
b
Refer to the tables below. Which query produces the result table below? SELECT DISTINCT Size FROM Product; a. none b. four rows c. three rows d. five rows
b
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 ProductName From Product P Where Qunatity > (SELECT SUM(Quantity) FROM Sales WHERE ProductID = P. ProductID); a. All of the products in the database b. All products that are in stock c. No products are selected d. All products that are sold-out
b
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); a. Onesies set, Pajama set, Shorts set b. Onesies set, Sunsuit, Romper c. No Products d. Pajama set, Shorts set
b
Select the correct WKT syntax for a polygon. a. POLYGON( (0 0) (20 10) (10 20) (0 0) ) b. POLYGON( (0 0, 20 10, 10 20, 0 0 ) ) c. POLYGON( 0 0 ; 20 10; 10 20; 0 0 ) d. POLYGON(0 0, 20 10, 10 20, 0 0)
b
The ____ keyword helps the compiler identify embedded SQL statements in a program. a. PROCESS SQL b. EXEC SQL c. RUN SQL d. EMBED SQL
b
What does the following statement return? SELECT DATEDIFF('2020-11-04', '2020-11-09'); a. 0 b. -5 b. '2020-11-05' d. 5
b
What isolation level prevents dirty, non-repeatable, and phantom reads? a. read uncommitted b. serializable c. read committed d. repeatable read
b
What object maps one or more tables to a single file? a. vertical partition b. tablespace c. horizontal partition d. shard
b
Which of the following does NOT appear in the Oracle Database's CREATE TYPE statement below? CREATE TYPE NameType AS OBJECT ( ... ) a. Property names and types b. Function body c. Procedure names and parameters d. Function names and return types
b
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 ('FristName', XXX, 'Last Name', YYY) dbCursor.close() a. row[0], row[1] b. row[1], row[0] c. 'FirstName', 'LastName' d. row['firstname'], row['lastname']
b
____ allows a programmer to create an SQL query with a PREPARE statement at run-time. a. Static SQl b. Dynamic SQL c. Embedded SQL d. Complex SQL
b
A database admin configures replication for a distributed database. Which technique applies updates to any available node? a. Parallel system b. Master/slave c. Group replication d. Primary/secondar
c
A document that sequentially writes all the database operations is known as a/an ____. a. application log b. task log c. recovery log d. system log
c
A time dimension table tracks throughout a day by minute. How many rows does the table contain? a. 240 b. 24 c. 1440 d. 60
c
API defines ____ for applications written in procedural language and ____ for applications in object-oriented languages. a. function calls, models b. sql calls, objects c. procedure calls, classes d. application calls, entities
c
Due to infrequent updates, many distributed databases use a ____ catalog. a. parallel b. primary c. replicated d. private
c
In Connector/J, executeQuery(statement) executes the SQL code associated with the statement object. Prior to execution, executeQuery() ____. a. only compiles the SQL when called with an optional second argument of 'true' b. does not compile the SQL c. always compiles the SQL d. compiles the SQL unless the same SQL was compiled in the previous executeQuery() call
c
In order to locate rows selected by a query, an index scan reads index blocks ____. a. randomly b. in reverse order c. sequentially d. in order of key values
c
In order to run a program with Embedded SQL, the compiler ____. a. converts all statements to SQL and compiles the result to an intermediate language b. compiles the SQL code to machine language and then to an executable program c. translates the SQL code to the host language first, and then to an executable program d. ignores all the non-SQL syntax, runs the code and exits the program after running
c
In two dimensions, a minimum bounding rectangle is the ____? a. the rectangle area aligned with the X- and Y- axes that contains all spatial values b. the minimum area of a polygon or multipolygon c. the smallest rectangle aligned with the X- and Y-axes that contains a spatial value d. the sum of the spatial values contained within a rectangle
c
Refer to the following tables: How many rows are in the table defined by the following relational algebra expression? Employee Union Student a. 2 b. 1 c. 0 d. 5
c
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; | a. At the same time as the UPDATE statement in T1 executes b. Immediately after the UPDATE statement in T1 executes c. The SELECT statement in T2 will never execute d. After the ROLLBACK statement in T1 executes
c
Relationship minima are usually specifies as ____ or ____. a. one, two b. minima, one c. zero, one d. one, many
c
The ____ method of the ____ class creates a connection object in Connector/J. a. getConnection(), Connection b. connect(), Database c. getConnection(), DriverManager d. create(), Connection
c
The database designer creates a(n) ____ when no suitable single-column or composite primary key exists. a. natural key b. foreign key c. artificial key d. master key
c
The heap table structure is optimized for ____. a. updating all occurrences of a specific value of a column in all rows b. reading all rows with primary key between two fixed values c. inserting new rows d. deleting all rows with primary key between two fixed values
c
Using a cloud-based database comes with risk. ____ is a regulator risk. a. Elasticity b. Reliability c. Privacy d. Security
c
What is wrong in the following query? SLECT Sales.OrderId, Customer.FirstName, Customer.LastName FROM Sales INNER JOIN Customer ON CustomerID = CustomerID; a. The columns in the SELECT clause are in the wrong order b. The Customer table does not appear in the FROM clause c. The CustomerID prefix is missing d. Nothing is wrong in the query
c
What is wrong with the syntax of this XML? <Menu> <Item> <Name>Green salad</Name> <Description?Lettuce, tomatoes, green peppers</Description> <Price>7.00</Price> </Item> <Item> <Name> Greek salad</Name> <Description>Cucumbers, tomatoes, onions, feta cheese</Description> <Price>8.40</Price> </Item> <Item> <Name> Cobb salad</Name> <Description>Eggs, bacon, lettuce, tomatoes, green onions</Description> <Price>11.00</Price> </Item? </Menu> a. Tag names cannot be capitalized. b. Tags cannot be nested inside other tags. c. The XML syntax is correct. d. XML cannot contain multiple tags with the same name.
c
What occurs when a distributed database experiences a network error and nodes cannot communicate? a. Consistent records b. Database failure c. Network partition d. Inconsistent transactions
c
What type of join is executed by the following relational algebra expression? Department X Department.Manager = Employee.IDEmployee a. inner b. left c. cross d. full
c
When an entity-relationship model is implemented in SQL, an entity typically becomes a ____. a. primary key b. column c. table d. foreign key
c
Which document type is commonly used by document databses? a. DOCX b. SQL c. XML d. XLSX
c
Which of the following is an example of database programming? a. Creating a database using a database server administration tool b. Creating a static web page c. Creating a web application using Java and SQL d. Creating tables and columns using SQL in a database administration tool
c
Which statement is NOT an advantage of using a view table? a. optimal SELECT statements can be saved in a view table b. sensitive table data can be hidden from users and programmers c. the creation of a new base table is always up to date d. complex SELECT statements can be saved in a view table
c
With column-oriented storage, a block stores values for ____. a. key columns only b. all table columns c. a single column d. non-key columns
c
A NoSQL database that represents data as a key with multiple values is called a ____ database. a. graph b. key-value c. document d. wide column
d
A ____ assigns each row to a group of linked blocks, called a bucket. a. table cluster b. heap table c. sorted table d. hash table
d
A _____ is a sequential order of database instructions for multiple transactions. a. query b. path c. transaction d. schedule
d
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 ____. a. privacy b. security c. reliability d. elasticity
d
A single-level index is a file that contains column values and pointers to ____ containing the column____. a. tables, key b. blocks, key c, tables, rows d. rows, value
d
A standardized spatial reference system is identified with a/an ____. a. data type b. standard coordinate system c. latitude and longitude d. SRID
d
A table is in ____ normal form when all dependencies are on unique columns. a. first b. second c. third d. Boyce-Codd
d
A/An ____ will retrieve rows from a query that has multiple rows in the result set. a. fetch b. shared variable c. index d. cursor
d
An operational database contains tables in third normal form. This design reduces ____> a. normalization b. interference c. reference time d. redundancy
d
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 ____ ; a. ProductName DESC, Quantity DESC b. ProductName, Quantity c. ProductName DESC, Quantity d. ProductName, Quantity DESC
d
Denormalization eliminates ____ queries, and therefore, query performance is improved. a. select b. create c. delete d. join
d
For integrity purposes, a distributed transaction requires all or ____ nodes to be updated in order to complete the transaction. a. primary b. connected c. shared d. no
d
How are blocks read with a single-level index scan? a. The search reads one index block plus selected table blocks. b. A scan is initiated to read all table blocks. Index blocks are then read. c. A scan is initiated to read all table blocks that contain index blocks with selected data. d. A scan reads all index blocks to find table blocks that contain selected rows. The table blocks are then read.
d
How many bytes does POINT(18.5-20) require in an internal MySQL format? a. 32 b. 20 c. 16 d. 24
d
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. a. consistency b. atomicity c. durability d. isolation
d
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? T1 | T2 | read C | B = C -2 | write read C | A = B + 1 | write A commit | commit a. 7 b. 8 c. 4 d. 2
d
Refer to the tables. Which query produces the result table below? a. SELECT S.OrderId, C.CustomerName INNER JOIN Customer C ON CustomerId = Customer Id FROM Sales S; b. SELECT S.OrderId, C.CustomerName FROM Sales INNER JOIN Customer ON S.CustomerId = C.CustomerId; c. SELECT S.OrderId, C.CustomerName FROM Sales S INNER JOIN Customer C ON CustomerId = CustomerId; d. SELECT S.OrderId, C.CustomerName FROM Sales S INNER JOIN Customer C ON S.CustomerId = C.CustomerId
d
Strict consistency is enforced in some document databases by directing read queries to _____. a. all subsequent replicas b. the secondary replica c. the primary replica d. all replicas
d
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 P XXX; a. CROSS JOIN PART A b. LEFT JOIN Part A ON P.PartID = A.PartID c. INNER JOIN Part A ON P.PartID = A.AssemblyID d. LEFT JOIN Part A ON P.AssemblyID = A.PartID
d
To fulfill data processing requirements in a growing transactional database, ____ scales up by increasing processing power of a limited number of systems. a. extensive replication b. horizontal scaling c. table partitioning d. vertical scaling
d
Vertices in a graph database are similar to ____ in a relational table. a. relationships b. columns c. keys d. rows
d
Where is the list of attribute types? a. table b. diagram c. relationship d. glossary
d
Which of the following Connector/Python cursor properties returns the number of items affected by a query? a. cursor.count b. cursor.column_names c. cursor.rows d. cursor.rowcount
d
Which of the following database programming techniques is commonly used with object-oriented programming languages? a. Procedural SQL b. Embedded SQL c. Functional Programming d. API
d
Which table structure might use the modulo function to locate rows? a. heap b. cluster c. sorted d. hash
d
Within each row of a wide column database, all columns of a family are stored ____ to optimize of read queries. a. non-contiguously b. decrementally c. incremementally d. contiguously
d
in a Connector/Python program, the ____ method prevents ____, which is a type of attack that causes a program to execute malicious SQL statements. a. cursor.rollback(), privilege escalation b. cursor.close(), phishing c. cursor.commit(), brute-force d. cursor.execute(), SQL injection
d