Certifiable Q&A for March 16, 2001

Welcome to Certifiable, your exam prep headquarters. Here you'll find questions about some of the tricky areas that are fair game for the certification exams. Following the questions, you'll find the correct answers and explanatory text. We change the questions weekly.

Questions (March 16, 2001)
Answers (March 16, 2001)

My primary goal for this column has been to spur you to explore different areas of a product's functionality in hopes that such exploration will better prepare you for the problems that arise in the real world but aren't necessarily covered on the exams. For a well-prepared person who has experience using a product, the exam should be a formality. Understanding why the answer is correct is much more important than knowing the correct answer. Just remember that if you understand how something works, you can usually determine why it isn't working the way it should.

That statement holds true for exam questions as well. Even if you have never seen the situation described in an exam question, knowing how the product operates in similar situations lets you make educated guesses about which answers might be correct. That should improve your success rate on exams. Interestingly, more product knowledge also improves your success rate when you troubleshoot problems at work, and your success at work has a lot more impact on your career than your success on any certification exam.

This week's questions apply to topics for Exam 70-029: Designing and Implementing Databases with Microsoft SQL Server 7.0.

Questions (March 16, 2001)

Question 1
In SQL Server 7.0, how do you ensure that a supplier won't be deleted from the Suppliers table if that supplier has products in the Products table?

1. Create a DELETE trigger on the Suppliers table that will roll back the transaction if the supplier has any products in the Products table.
2. Create a DELETE trigger on the Products table that will roll back the transaction if the supplier has any products in the Products table.
3. Create an UPDATE trigger on the Suppliers table that will roll back the transaction if the supplier has any products in the Products table.
4. Create an UPDATE trigger on the Products table that will roll back the transaction if the supplier has any products in the Products table.

Question 2
Using the VIEW definition below, which statement updates the price and au_ord columns?

CREATE VIEW titleview
AS
select title, au_ord, au_lname, price, ytd_sales, pub_id
from authors, titles, titleauthor
where authors.au_id = titleauthor.au_id
   AND titles.title_id = titleauthor.title_id 

1. You can't update a VIEW that contains columns from two or more tables.
2. You can't use the UPDATE statement with a VIEW.
3. UPDATE titleview SET price=21.95 WHERE title = 'Computer Phobic and Non-Phobic Individuals: Behavior Variations'
4. UPDATE titleview SET au_ord=2 WHERE title = 'Computer Phobic and Non-Phobic Individuals: Behavior Variations' AND au_lname = 'karsen'
5. UPDATE titleview SET price=21.95, au_ord = 2 WHERE title = 'Computer Phobic and Non-Phobic Individuals: Behavior Variations' AND au_lname = 'karsen'

Question 3
You plan to query a table mainly by selecting a specified range of order number values that could include up to 50 percent of the table. Which type of index is recommended?

1. Clustered
2. Nonclustered
3. Unique
4. Composite

Answers (March 16, 2001)

Answer to Question 1
The correct answer is A—Create a DELETE trigger on the Suppliers table that will roll back the transaction if the supplier has any products in the Products table. Triggers enforce referential integrity by letting you control INSERT, UPDATE, and DELETE operations based on data found in other tables. Because triggers can roll back a transaction if they encounter an error or a business rule violation, you can use them to make one last check before the transaction completes.

In this case, the business rule is that you can't remove a supplier from the database as long as the supplier has products in the Products table. A trigger can either roll back a DELETE operation on the Suppliers table, or it can execute a DELETE operation on the Products table to adhere to the business rule. Called a "cascading delete," the latter option is available in SQL Server 2000 for maintaining referential integrity.

Answer to Question 2
The correct answer is C:

UPDATE titleview SET price=21.95 WHERE title = 'Computer Phobic and Non-Phobic
Individuals: Behavior Variations'
UPDATE titleview SET au_ord=2 WHERE title = 'Computer Phobic and Non-Phobic Individuals:
Behavior Variations' AND au_lname = 'karsen'

All versions of SQL Server limit updates through views to one table. If a view references columns from two or more tables, the UPDATE statement must make changes to the columns of only one table.

Answer to Question 3
The correct answer is A—Clustered. Because clustered indexes physically arrange records in the table according to the index, they provide better performance than nonclustered indexes for queries that retrieve data within a specified range. For example, if last names are indexed alphabetically using a clustered index, the names Lattimore, Lawson, and Lewis are stored in adjacent rows. To find all the records with last names that start with L, SQL Server searches for last names greater than or equal to L and less than M. With a clustered index in place, after SQL Server finds the first record, it can keep reading records until it finds one with a last name that starts with M. The physical arrangement of records according to the values in the index columns makes clustered indexes ideal for improving performance of queries that return a large percentage of the rows in a table. In contrast, the structure of nonclustered indexes makes them superior for searches that return only a small percentage of records from the table.