Microsoft Exam 070-229
Installing, Configuring and Administering Microsoft
SQL Server 2000, Enterprise Edition
For reference, you can read this book:
MCSE Training Kit: Microsoft SQL Server 2000 Database Design and Implementation (0-7356-1248-X)
Here is Chapter and Appendix Overview:
This self-paced training course combines notes, hands-on procedures, and review questions to teach you how to design and implement databases with SQL Server 2000. It is designed to be completed from beginning to end, but in some cases you
can choose a customized track and complete only the sections that interest you. (See the next section, “Finding the Best Starting Point for You,” for more information.) If you choose the customized track option, see the “Before You Begin” section in each chapter. Any hands-on procedures that require preliminary work from preceding chapters refer to the appropriate chapters.
Part 1 is divided into the following sections and chapters:
■ The “About This Book” section contains a self-paced training overview and introduces the components of this training. Read this section thoroughly to get the greatest educational value from this self-paced training and to plan which
lessons you will complete.
■ Chapter 1, “Introduction to Microsoft SQL Server 2000,” introduces you to SQL Server 2000 and explains what SQL Server is. The chapter provides a cohesive overview of SQL Server so that you can understand how all the pieces
fit together.
■ Chapter 2, “Using Transact-SQL on a SQL Server Database,” introduces you to Transact-SQL and provides details about how to create and execute Transact-SQL statements in order to manage a SQL Server database and its data. The chapter also introduces you to the SQL Server programming tools that allow you to use Transact-SQL to interface with the database.
■ Chapter 3, “Designing a SQL Server Database,” introduces you to the process of creating a SQL Server database. It describes the basic concepts of database design and provides information about planning a database, identifying system
requirements, and developing a logical data model.
■ Chapter 4, “Implementing SQL Server Databases and Tables,” explains how to create and manage a SQL Server database. It then discusses data types and how to identify which ones to use when creating a table. The chapter also describes
how to create these tables—using the data type information—and how to modify the tables after they have been created.
■ Chapter 5, “Implementing Data Integrity,” provides an overview of the various methods that you can use to maintain data integrity and a description of the types of data integrity that you will find in a SQL Server database. The chapter also
provides detailed information about the various types of integrity constraints that you can use to enforce data integrity and how to implement them in a database.
■ Chapter 6, “Accessing and Modifying Data,” provides detailed information about four Transact-SQL statements (SELECT, INSERT, UPDATE, and DELETE) and describes how each statement is used in Query Analyzer to retrieve and modify data. This chapter also introduces you to other methods for adding, modifying, and deleting data.
■ Chapter 7, “Managing and Manipulating Data,” describes more techniques for managing and manipulating data, including how to import and export data, how to manipulate heterogeneous data, how to use Transact-SQL cursors, and how to extract data in XML format.
■ Chapter 8, “Implementing Stored Procedures,” introduces you to the types of stored procedures available in SQL Server 2000 and how to create, execute, and alter them. You are also introduced to programming stored procedures.
■ Chapter 9, “Implementing Triggers,” introduces you to triggers and how to use them to extend data integrity and implement complex business logic. You will learn when it is appropriate to implement triggers and when basic constraints will suffice. You will also learn how to program triggers and how to use system commands and functions commonly used in trigger programming.
■ Chapter 10, “Implementing Views,” introduces you to views and the various functionality that they support. You will learn how to use views to insert, update, and modify data.
■ Chapter 11, “Implementing Indexes,” introduces you to the structure and purpose of indexes and the types and characteristics of indexes. You will learn how to determine when an index is appropriate, the type of index to create, and how to create it.
■ Chapter 12, “Managing SQL Server Transactions and Locks,” introduces you to the fundamentals of transactions and locks and describes how transactions and locks are used to process data modifications.
■ Chapter 13, “Designing and Admini stering SQL Server 2000 Security,” introduces you to SQL Server security. You will learn how to design SQL Server security to accommodate user requirements and protect the database from unauthorized access.
■ Chapter 14, “SQL Server Monitoring and Tuning,” examines how to use SQL Profiler to monitor a database system and explores methods of improving database performance through partitioning and index tuning.
Following Part 2 you will find:
■ The Appendix, “Questions and Answers,” lists all of the review questions from the book and the suggested answers.
■ The Glossary provides definitions for SQL Server terminology.
Sample Question and Answer (Q&A) for exam 70-229:
QUESTION 1:
You are designing for a large grocery store chain. The partial database schema is shown in the Partial Database Schema Exhibit. The script that was used to create the Customers table is shown in the Script for Customers Table Exhibit. The store managers want to track customer demographics so they can target advertisements and coupon promotions to customers. These advertisements and promotions will be based on the past purchases of existing customers. The advertisements and promotions will target buying patterns by one or more of these demographics: gender, age, postal code, and region. Most of the promotions will be based on gender and age. Queries will be used to retrieve the customer demographics information. You want the query response time to be as fast as possible. What should you do?
A. Add indexes on the PostalCode, State, and DateOfBirth columns of the Customers table.
B. Denormalize the Customers table
C. Create a view on the Customers, SalesLineItem, State, and Product tables.
D. Create a function to return the required data from the Customers table.
Answer: B.
QUESTION 2:
You work as database developer at certkiller.blogspot.com. The Certkiller Sales personal want to track sales by multiple parameters such as age, country, etc to be able to spot relevant sales patterns. To produce such information you need to join four tables from the highly normalized database.
How should you make the query response time as fast as possible in this scenario?
A. Create a view on the four tables. Create an index on the view.
B. Denormalize the database design.
C. Further normalize the table.
D. Add more indexes on the relevant columns in the tables that are joined.
E. Improve performance by using functions to implement the query.
Answer: B.
QUESTION 3:
You are a database developer for Proseware, Inc. You are implementing a database for the database of the company's human resources department. This database will store employee information. You create a table named EmployeeContact that contains the following columns: HomePhone, BusinessPhone, FaxNumber, and EmailAddress You must ensure that each record contains a value for either the HomePhone column or the BusinessPhone column. What should you do?
A. Create a rule that disallows null values. Bind the rule to both the HomePhone and BusinessPhone columns.
B. Create a rule that prevents null values from being entered into both the HomePhone
and BusinessPhone columns. Bind the rule to the table.
C. Add CHECK constraints on the HomePhone and BusinessPhone columns that prevent null values from being entered into the columns.
D. Add a CHECK constraint on the table to validate that at least one of the values entered into the HomePhone and BusinessPhone columns is non-null.
E. Create a trigger that counts the number of items entered without a value in the HomePhone column and then counts the number of items entered without a value in the BusinessPhone column. Configure the trigger so that if one or more rows are found that meet these conditions, the trigger will cancel the data modification.
Answer: D.
QUESTION 4:
You are a database developer for Itexamworld 's SQL Server 2000 online transaction processing database. Many of the tables have 1 million or more rows. All tables have a clustered index. The heavily accessed tables have at least one non-clustered index. Two RAID arrays on the database server will be used to contain the data files. You want to place the tables and indexes to ensure optimal I/O performance. You create one filegroup on each RAID array. What should you do next?
A. Place tables that are frequently joined together on the same filegroup. Place heavily accessed tables and all indexes belonging to those tables on different filegroups.
B. Place tables that are frequently joined together on the same filegroup. Place heavily accessed tables and the nonclustered indexes belonging to those tables on the same filegroup.
C. Place tables that are frequently joined together on different filegroups. Place heavily accessed tables and the nonclustered indexes belonging to those tables on different filegroups.
D. Place tables that are frequently joined together on different filegroups. Place heavily accessed tables and the nonclustered indexes belonging to those tables on the same filegroup.
Answer: C.
QUESTION 5:
You are a database developer for a shipping company. You have a SQL Server 2000 database that stores order information. The database contains tables named Order and OrderDetails. The database resides on a computer that has four 9-GB disk drives
available for data storage. The computer has two disk controllers. Each disk controller controls two of the drives. The Order and OrderDetail tables are often joined in queries. You need to tune the performance of the database.
What should you do? (Each correct answer presents part of the solution. Choose two.)
A. Create a new filegroup on each of the four disk drives.
B. Create the clustered index for the Order table on a separate filegroup from the nonclustered indexes.
C. Store the data and the clustered index for the OrderDetail table on one filegroup, and create the nonclustered indexes on another filegroup.
D. Create the Order table and its indexes on one filegroup, and create the OrderDetail table and its indexes on another filegroup.
E. Create two filegroups that each consists of two disk drives connected to the same controller.
Answer: D, E.
Posted by certkiller,
Saturday, April 14, 2007 7:30 PM
| 0 comments |
Installing, Configuring and Administering Microsoft
SQL Server 2000, Enterprise Edition
For reference, you can read this book:
MCSE Training Kit: Microsoft SQL Server 2000 Database Design and Implementation (0-7356-1248-X)
Here is Chapter and Appendix Overview:
This self-paced training course combines notes, hands-on procedures, and review questions to teach you how to design and implement databases with SQL Server 2000. It is designed to be completed from beginning to end, but in some cases you
can choose a customized track and complete only the sections that interest you. (See the next section, “Finding the Best Starting Point for You,” for more information.) If you choose the customized track option, see the “Before You Begin” section in each chapter. Any hands-on procedures that require preliminary work from preceding chapters refer to the appropriate chapters.
Part 1 is divided into the following sections and chapters:
■ The “About This Book” section contains a self-paced training overview and introduces the components of this training. Read this section thoroughly to get the greatest educational value from this self-paced training and to plan which
lessons you will complete.
■ Chapter 1, “Introduction to Microsoft SQL Server 2000,” introduces you to SQL Server 2000 and explains what SQL Server is. The chapter provides a cohesive overview of SQL Server so that you can understand how all the pieces
fit together.
■ Chapter 2, “Using Transact-SQL on a SQL Server Database,” introduces you to Transact-SQL and provides details about how to create and execute Transact-SQL statements in order to manage a SQL Server database and its data. The chapter also introduces you to the SQL Server programming tools that allow you to use Transact-SQL to interface with the database.
■ Chapter 3, “Designing a SQL Server Database,” introduces you to the process of creating a SQL Server database. It describes the basic concepts of database design and provides information about planning a database, identifying system
requirements, and developing a logical data model.
■ Chapter 4, “Implementing SQL Server Databases and Tables,” explains how to create and manage a SQL Server database. It then discusses data types and how to identify which ones to use when creating a table. The chapter also describes
how to create these tables—using the data type information—and how to modify the tables after they have been created.
■ Chapter 5, “Implementing Data Integrity,” provides an overview of the various methods that you can use to maintain data integrity and a description of the types of data integrity that you will find in a SQL Server database. The chapter also
provides detailed information about the various types of integrity constraints that you can use to enforce data integrity and how to implement them in a database.
■ Chapter 6, “Accessing and Modifying Data,” provides detailed information about four Transact-SQL statements (SELECT, INSERT, UPDATE, and DELETE) and describes how each statement is used in Query Analyzer to retrieve and modify data. This chapter also introduces you to other methods for adding, modifying, and deleting data.
■ Chapter 7, “Managing and Manipulating Data,” describes more techniques for managing and manipulating data, including how to import and export data, how to manipulate heterogeneous data, how to use Transact-SQL cursors, and how to extract data in XML format.
■ Chapter 8, “Implementing Stored Procedures,” introduces you to the types of stored procedures available in SQL Server 2000 and how to create, execute, and alter them. You are also introduced to programming stored procedures.
■ Chapter 9, “Implementing Triggers,” introduces you to triggers and how to use them to extend data integrity and implement complex business logic. You will learn when it is appropriate to implement triggers and when basic constraints will suffice. You will also learn how to program triggers and how to use system commands and functions commonly used in trigger programming.
■ Chapter 10, “Implementing Views,” introduces you to views and the various functionality that they support. You will learn how to use views to insert, update, and modify data.
■ Chapter 11, “Implementing Indexes,” introduces you to the structure and purpose of indexes and the types and characteristics of indexes. You will learn how to determine when an index is appropriate, the type of index to create, and how to create it.
■ Chapter 12, “Managing SQL Server Transactions and Locks,” introduces you to the fundamentals of transactions and locks and describes how transactions and locks are used to process data modifications.
■ Chapter 13, “Designing and Admini stering SQL Server 2000 Security,” introduces you to SQL Server security. You will learn how to design SQL Server security to accommodate user requirements and protect the database from unauthorized access.
■ Chapter 14, “SQL Server Monitoring and Tuning,” examines how to use SQL Profiler to monitor a database system and explores methods of improving database performance through partitioning and index tuning.
Following Part 2 you will find:
■ The Appendix, “Questions and Answers,” lists all of the review questions from the book and the suggested answers.
■ The Glossary provides definitions for SQL Server terminology.
Sample Question and Answer (Q&A) for exam 70-229:
QUESTION 1:
You are designing for a large grocery store chain. The partial database schema is shown in the Partial Database Schema Exhibit. The script that was used to create the Customers table is shown in the Script for Customers Table Exhibit. The store managers want to track customer demographics so they can target advertisements and coupon promotions to customers. These advertisements and promotions will be based on the past purchases of existing customers. The advertisements and promotions will target buying patterns by one or more of these demographics: gender, age, postal code, and region. Most of the promotions will be based on gender and age. Queries will be used to retrieve the customer demographics information. You want the query response time to be as fast as possible. What should you do?
A. Add indexes on the PostalCode, State, and DateOfBirth columns of the Customers table.
B. Denormalize the Customers table
C. Create a view on the Customers, SalesLineItem, State, and Product tables.
D. Create a function to return the required data from the Customers table.
Answer: B.
QUESTION 2:
You work as database developer at certkiller.blogspot.com. The Certkiller Sales personal want to track sales by multiple parameters such as age, country, etc to be able to spot relevant sales patterns. To produce such information you need to join four tables from the highly normalized database.
How should you make the query response time as fast as possible in this scenario?
A. Create a view on the four tables. Create an index on the view.
B. Denormalize the database design.
C. Further normalize the table.
D. Add more indexes on the relevant columns in the tables that are joined.
E. Improve performance by using functions to implement the query.
Answer: B.
QUESTION 3:
You are a database developer for Proseware, Inc. You are implementing a database for the database of the company's human resources department. This database will store employee information. You create a table named EmployeeContact that contains the following columns: HomePhone, BusinessPhone, FaxNumber, and EmailAddress You must ensure that each record contains a value for either the HomePhone column or the BusinessPhone column. What should you do?
A. Create a rule that disallows null values. Bind the rule to both the HomePhone and BusinessPhone columns.
B. Create a rule that prevents null values from being entered into both the HomePhone
and BusinessPhone columns. Bind the rule to the table.
C. Add CHECK constraints on the HomePhone and BusinessPhone columns that prevent null values from being entered into the columns.
D. Add a CHECK constraint on the table to validate that at least one of the values entered into the HomePhone and BusinessPhone columns is non-null.
E. Create a trigger that counts the number of items entered without a value in the HomePhone column and then counts the number of items entered without a value in the BusinessPhone column. Configure the trigger so that if one or more rows are found that meet these conditions, the trigger will cancel the data modification.
Answer: D.
QUESTION 4:
You are a database developer for Itexamworld 's SQL Server 2000 online transaction processing database. Many of the tables have 1 million or more rows. All tables have a clustered index. The heavily accessed tables have at least one non-clustered index. Two RAID arrays on the database server will be used to contain the data files. You want to place the tables and indexes to ensure optimal I/O performance. You create one filegroup on each RAID array. What should you do next?
A. Place tables that are frequently joined together on the same filegroup. Place heavily accessed tables and all indexes belonging to those tables on different filegroups.
B. Place tables that are frequently joined together on the same filegroup. Place heavily accessed tables and the nonclustered indexes belonging to those tables on the same filegroup.
C. Place tables that are frequently joined together on different filegroups. Place heavily accessed tables and the nonclustered indexes belonging to those tables on different filegroups.
D. Place tables that are frequently joined together on different filegroups. Place heavily accessed tables and the nonclustered indexes belonging to those tables on the same filegroup.
Answer: C.
QUESTION 5:
You are a database developer for a shipping company. You have a SQL Server 2000 database that stores order information. The database contains tables named Order and OrderDetails. The database resides on a computer that has four 9-GB disk drives
available for data storage. The computer has two disk controllers. Each disk controller controls two of the drives. The Order and OrderDetail tables are often joined in queries. You need to tune the performance of the database.
What should you do? (Each correct answer presents part of the solution. Choose two.)
A. Create a new filegroup on each of the four disk drives.
B. Create the clustered index for the Order table on a separate filegroup from the nonclustered indexes.
C. Store the data and the clustered index for the OrderDetail table on one filegroup, and create the nonclustered indexes on another filegroup.
D. Create the Order table and its indexes on one filegroup, and create the OrderDetail table and its indexes on another filegroup.
E. Create two filegroups that each consists of two disk drives connected to the same controller.
Answer: D, E.
Labels: mcdba, mcsa, mcse, microsoft
Read more!
