courtneys CIS 251 Final

Refer to figure 10-1.) Which column or columns in each table are foreign keys? A. Orders: none; OrderLineItems: OrderID and ProductID; Products: none B. Orders: OrderID; OrderLineItems: OrderID and OrderSequence; Products: ProductID C. Orders: none; OrderLineItems: OrderID and OrderSequence; Products: none X D. Orders: OrderID; OrderLineItems: OrderSequence; Products: ProductID

A. Orders: none; OrderLineItems: OrderID and ProductID; Products: none

What method can be used to handle multiple NOT criteria? OUTER JOIN NOT IN NOT EXISTS GROUP BY/HAVING All of these can handle multiple NOT criteria.

All of these can handle multiple NOT criteria.

Which of the following is a keyword used with the DELETE statement? DELETE FROM WHERE All three of these are keywords used in the DELETE statement.

All three of these are keywords used in the DELETE statement.

Which of the following diagrams best represents the relationship between a table of customers and a table of orders placed by customers? A B C D

B

Which of the following diagrams best represents the relationship between a table of customers and a table of orders placed by customers? A B C D

B

Which of the following diagrams best represents the relationship between a table of students and a table of classes for which the student is registered? A B C D

C

If a transaction is used for a DELETE statement, what keyword is used to permanently delete the rows specified by the DELETE statement? START TRANSACTION COMMIT ROLLBACK

COMMIT

What HAVING criteria can be used to eliminate rows for the multiple NOT criteria? COUNT >= 1 COUNT = 0 SUM = 0 SUM >= 1

COUNT = 0

What is a possible method for ensuring that the correct rows are deleted from the database before the DELETE statement is run? Creating a SELECT statement and then transferring the WHERE clause to the DELETE statement Using system logs Using transactions

Creating a SELECT statement and then transferring the WHERE clause to the DELETE statement

Which statement is used to remove data from the database? SELECT UPDATE INSERT DELETE

DELETE

When using IN to solve the multiple matches criteria, you will need to use multiple __________ criteria. OR X EXISTS IN EXCEPT

IN

Which method is similar to the EXISTS method when solving the multiple matches criteria? INNER JOIN IN GROUP BY/HAVING

IN

The NOT EXISTS works similarly to what other method of solving the multiple NOT criteria? OUTER JOIN NOT IN GROUP BY/HAVING

NOT IN

In an OUTER JOIN, what value does the database system return for any nonmatched columns? NULL Empty Zero

NULL

The set membership IN answers what condition? AND NOT OR LIKE

OR

What method is not used to solve the multiple matches criteria? INNER JOIN IN EXISTS GROUP BY/HAVING OUTER JOIN

OUTER JOIN

FIGURE 10-1 (Refer to figure 10-1.) Which column or columns in each table should be defined as the primary key? Orders: OrderID; OrderLineItems: OrderID; Products: ProductID A Orders: OrderID and OrderDate; OrderLineItems: OrderID and OrderSequence; Products: ProductID B Orders: OrderID; OrderLineItems: OrderID and OrderSequence; Products: ProductID C Orders: OrderID; OrderLineItems: OrderID, OrderSequence, and ProductID; Products: ProductID and ProductName

Orders: OrderID; OrderLineItems: OrderID and OrderSequence; Products: ProductID

What would keep the DELETE statement without the WHERE clause from deleting all the rows in a table? Transactions Referential integrity constraints System logs

Referential integrity constraints

To handle multiple NOT criteria, the OUTER JOIN must be combined with a ___________. Set membership predicate Range predicate Subquery UNION

Subquery

Which of the following is not a good guideline for deciding when to create an index for a column? The column is a foreign key. The column is frequently updated. The column is frequently used in search conditions or joins. The column contains a large number of distinct values.

The column is frequently updated.

What restriction is there when using a view in the DELETE statement? The view is insertable The view is deletable The view is updatable The view is dynamic

The view is updatable

The DELETE statement can use a table name or what other database item name? View Stored procedure Function Report

View

If which clause is missing in a DELETE statement it will delete all rows in the table? DELETE FROM WHERE HAVING

WHERE

What clause of a DELETE statement can have a subquery in it? DELETE FROM WHERE

WHERE

Which clause is used to limit the rows deleted in the DELETE statement? DELETE FROM WHERE

WHERE

To be in the first normal form, each cell in a table must contain a unique value a non-unique value a single, scalar value a non-redundant value

a single, scalar value

To enforce referential integrity for a delete operation, a MySQL database can return an error instead of deleting any rows set the foreign key values in the related rows to NULL delete the related rows in the foreign key table a, b, or c a or c

a, b, or c

Which of the following does not violate the referential integrity of a database? inserting a new row into a table with a foreign key that doesn't match a primary key in the related table updating a foreign key with a value that doesn't match a primary key in the related table updating a primary key in a primary key table without also updating the foreign keys for the related rows in all related tables deleting a row in a foreign key table without deleting the related row in the related primary key table

deleting a row in a foreign key table without deleting the related row in the related primary key table

To be in the third normal form, each non-key column must depend only on the primary key each non-key column must not depend on the primary key each non-key column must contain repeating values all of the above

each non-key column must depend only on the primary key

To be in the third normal form, each non-key column must depend only on the primary key each non-key column must not depend on the primary key each non-key column must contain repeating values all of the above

each non-key column must depend only on the primary key

Denormalization typically results in all but one of the following. Which one is it? larger tables X redundant data more complicated SQL coding reduced performance

more complicated SQL coding

To apply the second normal form, you move columns that don't depend on the entire primary key to another table and establish a relationship between the two tables. This reduces redundancy but makes maintenance more difficult reduces redundancy and makes maintenance easier increases redundancy but makes maintenance easier increases redundancy but makes the data more consistent

reduces redundancy and makes maintenance easier

When using NOT IN to solve the multiple NOT criteria, you can use a(n) ___________ to generate the list of items to exclude. subquery range predicate OUTER JOIN UNION

subquery

When you identify the data elements in a new database, you typically subdivide data elements into the largest practical components the smallest practical components components that can be easily parsed each time you use them

the smallest practical components