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 9, 2001)Answers (March 9, 2001)
Exams 70-029, Designing and Implementing Databases with Microsoft SQL Server 7.0, and 70-229, Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition, are the only exams that count for all three premium certifications: MCSE, MCSD, and Microsoft Certified Database Administrator (MCDBA). Because both exams count for the MCSE and MCDBA certifications, SQL Server can be a good choice as an area of study for your electives.
Exams 70-029 and 70-229 focus on how to design a database and how to implement that design. Fortunately, the differences between SQL Server 7.0 and SQL Server 2000 are relatively small for the topics that these exams cover. That means that if you study for Exam 70-029, you're in effect also preparing for Exam 70-229. Studying for the latter consists mostly of reading through the "What's New" section in Books Online to learn how the syntax of the SELECT, INSERT, UPDATE, and DELETE statements have changed.
The SQL Server exams are by no means easy, but they're worth the effort. Passing them offers the added bonus of helping you attain not one, but two, premium certifications. For me, that was too good a deal to pass up!
Question 1
What is the purpose of database normalization?
- To ensure that all columns in a table have a default value
- To ensure that all tables have a unique index
- To reduce data duplication and ensure data integrity
- To create tables from the logical database design
Question 2
Your organization serves the Western United States only. You are designing the Contact Management database. The Customer table contains each customer's address. Before a row is inserted into the Customer table, you want to ensure that the state name is one of the following: AK, AZ, CA, ID, NV, OR, WA. How do you accomplish this?
- Use the Unique constraint in the Create Table statement to ensure that all state names are unique.
- Give a list of the valid state names to the end users so they can enter the correct spellings of the state names.
- Use the Default constraint in the Create Table statement so that the system can enter a default value in case an invalid value is given.
- Use the Check constraint in the Create Table statement to enforce data integrity.
Question 3
The Sales table contains a salesperson#, a product#, a price, and a quantity for each sale made. Which SELECT statement should you use to query the table to determine which salespeople sold $10,000 or more worth of products?
- SELECT salesperson# FROM sales GROUP BY salesperson# HAVING SUM(price * quantity) >= $10000
- SELECT salesperson# FROM sales WHERE sales * price >= 10000
- SELECT salesperson# FROM sales WHERE sales * price
- SELECT salesperson# FROM sales WHERE price * quantity >= $10000 GROUP BY salesperson#
Answer to Question 1
The correct answer is C—To reduce data duplication and ensure data integrity. Normalization is the starting point for database design. Normalization's purpose is to reduce redundancy and preserve data integrity by creating tables with fewer columns, fewer null values, and less information duplication. Normalized tables make the database easier to maintain because you need to change information in relatively few places, which minimizes inconsistencies.
Answer to Question 2
The correct answer is D—Use the Check constraint in the Create Table statement to enforce data integrity. Although a column's data type can specify the broad range of values the column can hold, you often need to limit the range to a specific subset of values. For example, a two-character column could hold the values AA, AB, AK, or AZ. Of those four, only AK and AZ are state name abbreviations. Check constraints manage data integrity by limiting the possible values that a column can hold. If a column that holds the state postal code in an address has a Check constraint that specifies the 50 state codes, insertions that contain other letter combinations generate an error. In this question, a Check constraint that specifies only AK, AZ, CA, ID, NV, OR, and WA ensures that only those state codes can be entered into the Customer table.
Answer to Question 3
The correct answer is A—SELECT salesperson# FROM sales GROUP BY salesperson# HAVING SUM(price * quantity) >= $10000. Each row in the table represents one sale. To get totals for each salesperson, we must GROUP BY the salesperson#. The HAVING clause lets us eliminate groups that don't meet the criteria. The WHERE clause affects only individual rows, and nothing in these rows determines whether the row needs to be eliminated.
