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