Dr. Poole LU Database CSIS 326 Final

Uncommitted dependency problem

- Uncommitted dependency occurs when a transaction is allowed to retrieve or (worse) update a record that has been updated by another transaction, but which has not yet been committed by that other transaction.

The effect of a rollback command in a transaction is to

- undo all changes to the database resulting from the execution of the transaction

The undo operation

- writes old values from the log back to the database

SELECT CID FROM RENTALS GROUP BY CID HAVING COUNT (DISTINCT MAKE) = (SELECT COUNT(*) FROM RENTCOST) The execution of this query produces the following number of rows:

0

SELECT CID, CNAME FROM CUSTOMER WHERE 0 = (SELECT COUNT(*) FROM RENTALS WHERE CUSTOMER.CID = RENTALS.CID) The execution of this query produces the following number of rows:

1

A table MOVIES in an object-oriented database consists of the attributes title (the primary key), year-produced, and actor-name; actor-name is a repeating group; different movies may have different numbers of actors. The table MOVIES violates the following normal form (choose the lowest one):

1NF

In a table in 1NF in which the only candidate key is a single attribute:

2NF may not be violated

If a three-way relationship represented by a table that should actually be represented by two binary relationships, the table violates the following NF (choose the lowest one that applies):

4NF

Assume each node of the B-tree is full. The number of disk accesses to retrieve a record will approximately be:

5

SELECT DISTINCT CID, CNAME FROM CUSTOMER WHERE CID IN (SELECT CID FROM RENTALS WHERE RETURN IN (SELECT BIRTHPLACE FROM CUSTOMER)) The number of distinct CNAMEs shown by the execution of this query is:

5

If a three-way relationship represented by a table that should actually be represented by three binary relationships, the table violates the following NF (choose the lowest one that applies):

5NF

SELECT CID, CNAME FROM CUSTOMER WHERE EXISTS (SELECT CID FROM RENTALS WHERE CUSTOMER.CID = RENTALS.CID AND PICKUP = 'CARY') The nested subquery will be processed the following number of times:

6

Which of the following is not a good candidate for a clustering index?

A column with unique values used in equality conditions

inconsistent retrievals

A concurrency control problem that arises when a transaction-calculating summary functions over a set of data while other transactions are updating the data, yielding erroneous results.

lost-update problem

A problem that exists in database applications in which two users update the same data item, but only one of those changes is recorded in the data. Can be resolved using locking.

When a logical record requested by an application is already in memory, which of the following is not true?

A transfer from storage into memory will take place anyway

A table that satisfies 2NF:

Always satisfies 1NF

A table that satisfies BCNF:

Always satisfies 3NF

SELECT DISTINCT CID, CNAME FROM CUSTOMER WHERE MAKE IN (SELECT MAKE FROM RENTALS WHERE RETURN = 'ERIE') The CNAMEs shown by the execution of this query are:

An error occurs

ACID stands for

Atomicity, Consistency, Isolation, Durability

A Type II nested query may not reference a column in the outer query.

FALSE

A type I query may reference a column in the outer query.

FALSE

COUNT is the only aggregate function which can be affected by the presence of null values in the aggregate column(s).

FALSE

Durability of a transaction means that once a transaction is successfully completed, changes resulting from it are stored in the database on permanent storage forever.

FALSE

In a Btree file structure, only the pointers to the data records are stored in that file; the actual data records are always stored in a different physical file.

FALSE

In a hash file, when a collision occurs and there is no free space in the physical record to be written to, the logical record is written to the end of the file with a pointer to it.

FALSE

In the flowing query, the value for NumRows and the value for NumFaculty would always have to be the same. SELECT COUNT(*) AS NumRows, COUNT(FacSSN) AS NumFaculty FROM Offering

FALSE

Index selection involves choices about two kinds of indexes, Btree and bitmap

FALSE

Instead of following the SQL:1999 specification, Oracle uses the keywords BEGIN TRANSACTION to explicitly start a transaction.

FALSE

Locks can be placed on a record or a table, but not the full database.

FALSE

Most difference problems can be solved by either using a Type I nested query, a Type II nested query, or an inequality join, and the choice is a matter of preference.

FALSE

Once all of the functional dependencies have been analyzed and a table is in BCNF, it is safe to assume that the table does not contain any redundancies that would cause modification anomalies.

FALSE

One of the disadvantages of using normalization as a refinement tool instead of an initial design tool is that M-N relationships can often be overlooked with this approach.

FALSE

The "ultimate normal form" is considered to be 5NF.

FALSE

To implement parallel processing capabilities usually requires upgrading to a more expensive DBMS, but no additional hardware is usually needed.

FALSE

The main objective of checkpoints is to reduce the number and duration of database backups.

FALSE - main objective is to ensure that data retrieved looks okay

SELECT DISTINCT CUSTOMER.CID, CNAME FROM CUSTOMER, RENTALS, RENTCOST WHERE CUSTOMER.CID = RENTALS.CID AND RENTALS.MAKE = RENTCOST.MAKE AND NOT EXISTS (SELECT * FROM RENTALS R, RENTCOST C WHERE R.MAKE = C.MAKE AND RENTALS.CID = R.CID AND RENTCOST.COST <> C.COST) The execution of this query lists the following CNAMEs:

GREEN, MARTIN, SIMON

SELECT CNAME, MAKE, RTN FROM CUSTOMER RIGHT JOIN RENTALS ON CUSTOMER.CID = RENTALS.CID WHERE RENTALS.CID > 20 The distinct values appearing in the column CNAME of the result of this query are:

JONES, MARTIN, SIMON, null value

SELECT CID FROM RENTALS GROUP BY CID HAVING COUNT (DISTINCT MAKE) = (SELECT COUNT(*) FROM RENTCOST) The meaning of this query is the following:

List the CID of all customers who rent all cars listed in RENTCOST

A table in 1NF in which the unique candidate key consists of two of its three attributes:

May violate 2NF

A table in 1NF in which the unique candidate key consists of all the attributes of the table:

May violate 4NF

Which of the following does not improve execution of SQL queries?

Move simple comparisons from the "WHERE" clause to the "HAVING" clause

Which of the following is not typically used in establishing application profiles?

Number of "drop" performed by the subform of a form

For a violation of 2NF to occur in a table in 1NF, the following condition(s) must exist:

Part of a key determines a nonkey attribute

Which of the following will usually not result in a faster execution of an SQL query?

Perform projections after expensive joins

Which of the following statements is incorrect about RAID technology?

RAID-5 is preferred to RAID-1 when the database requires frequent data modifications

3NF/BCNF is the most important normal form in practice because normal forms higher than this involve other kinds of dependencies that are less common and more difficult to understand.

TRUE

An alternative method of formulating a full outer join would be to take the union of the results tables from two one-sided outer joins.

TRUE

An operating system failure affects all active transactions, while a device failure affects all active transactions plus all committed transactions that have been recorded on the disk that failed.

TRUE

Balancing concurrency control overhead with potential interference problems can be achieved by a selecting an appropriate isolation level for transactions.

TRUE

Both uncommitted dependency and inconsistent retrieval problems can occur in situations where only one transaction is writing to part of the database.

TRUE

By default, constraints are enforced immediately after each INSERT, UPDATE, and DELETE statement, but this may be overridden by the DBA with a constraint timing clause.

TRUE

Due to the extra joins involved, splitting a table because it has multiple candidate keys can degrade query performance.

TRUE

Each operation in an access plan has a corresponding cost formula that estimates the physical record accesses and CPU operations, and these formulas are used by the query optimization component to evaluate different access plans.

TRUE

In index matching on composite indexes, columns are matched left to right, and matching stops when the next column in the index does not match.

TRUE

In selecting an index, a column used in highly selective range conditions is a good choice for a nonclustering index.

TRUE

In simple conditions involving a single comparison of two columns or column expression, a null value results if either column or column expression in the comparison is null.

TRUE

In the query transformation task of database language translation, a query that has already been analyzed for syntax and semantics errors is transformed into a relational algebra query.

TRUE

Most optimization components assume that combinations of columns are statistically independent, therefore most DBMSs do not maintain statistics on column combinations.

TRUE

One advantage of normalization is that data modification operations usually execute faster.

TRUE

SQL supports one-sided outer join operations with the keywords LEFT JOIN and RIGHT JOIN in queries.

TRUE

SQL:2003 uses three-valued logic to evaluate the result of compound conditions.

TRUE

Since constraint checking must occur before the end of a transaction, if a larger transaction is divided into a number of smaller transactions it may be difficult to check some important constraints.

TRUE

Since the objective of physical database design is to minimize the combined demand of all applications using the database, it is usually necessary to balance the sometimes conflicting needs of both retrieval and update applications.

TRUE

To avoid modification anomalies in a database, it is better to modify the table design than to work around them by writing code or using dummy values.

TRUE

Type I nested queries cannot be used when the result table contains any columns from the nested query.

TRUE

Using Boyce-Codd normal form (BCNF) allows you to perform the normalization process in one step, as opposed to using 2NF and 3NF which requires two normalization steps.

TRUE

A violation of BCNF is typical of the following condition(s) on a table:

The table has two candidate keys that share a common attribute

- After the crash of a disk containing the database, the following three tools will be needed for recovery:

database backup, log, checkpoint table

The optimistic approach works well when there are

few hotspots

A transaction is a

group of SQL statements defining a user-oriented task

The storage level is close to:

hardware and operating system

As the bank audit transaction reads Mary's savings balance, $100, Mary transfers $50 to her checking, making it $250, and the audit transaction completes with the combined value of $350 in both accounts. This is called:

incorrect summary

In a DBMS environment, which of the following is a usually critical factor in disk access time?

magnetic head movement

- Force writing lets

o The DBMS decide when writing to a disk takes place o The DBMS write a buffer containing updated data to disk even in the buffer is not full o The DBMS decide when to write the content of the log to disk

Assume each node of the B-tree is half-full (m = number of keys per node = 50). The number of disk access to retrieve a record will approximately be:

one more than for m = 99

- Which of the following isolation levels does not prevent the uncommitted dependency problem?

read uncommitted

It is possible to detect deadlocks in a database system but the cost of this

slower overall processing

A large database typically has tables in the 10's, 100's, or 1000's?

thousands

The redo operation

writes new values from the log to the database