Question With Answer : 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition
1. You are a database developer for the A. Datum Corporation. You are creating a database that will store statistics for 15 different high school sports. This information will be used by 50 companies that publish sports information on their Web sites.
Each company's Web site arranges and displays the statistics in a different format. You need to package the data for delivery to the companies.
What should you do?
A. Extract the data by using SELECT statements that include the FOR XML clause.
B. Use the sp_makewebtask system stored procedure to generate HTML from the data returned by SELECT statements.
C. Create Data Transformation Services packages that export the data from the database and place the data into tab-delimited text files.
D. Create an application that uses SQL_DMO to extract the data from the database and transform the data into standard electronic data interchange (EDI) files.
Ans:A
2. You are a database developer for a mail order company. The company has two SQL Server 2000 computers named CORP1 and CORP2. CORP1 is the online transaction processing server. CORP2 stores historical sales data. CORP2 has been added as a linked server to CORP1.
The manager of the sales department asks you to create a list of customers who have purchased floppy disks. This lists will be generated each month for promotional mailings. Floppy disks are representatives in the database with a category ID of 21.
You must retrieve this information from a table named SalesHistory. This table is located in the Archive database, which resides on CORP2. You need to execute this query from CORP1.
Which script should you use?
A. EXEC sp_addlinkedserver 'CORP2', 'SQL Server'
GO
SELECT CustomerID FROM CORP2. Archive.dbo.SalesHistory
WHERE CategoryID = 21
B. SELECT CusomerID FROM OPENROWSET ('SQLOLEDB', 'CORP2'; 'p*word', 'SELECT CustomerID FROM Archive.dbo.SalesHistory WHERE CategoryID = 21')
C. SELECT CustomerID FROM CORP2.Archive.dbo.SalesHistory
WHERE CategoryID = 21
D. EXEC sp_addserver 'CORP2'
GO
SELECT CustomerID FROM CORP2.Archive.dbo.SalesHistory
WHERE CategoryID = 21
Ans:C
3. You are a database developer for Trey Research. You create two transactions to support the data entry of employee information into the company's database. One transaction inserts employee name and address information into
the database. The other transaction inserts employee demographics information into the database. This transaction is less important than the first transaction.
The database administrator has notified you that the database server occasionally encounters errors during periods of high usage. Each time this occurs, the database server randomly terminates one of the transactions.
You must ensure that when the database server terminates one of these transactions, it never terminates the more important transaction.
What should you do?
A. Set the DEADLOCK_PRIORITY to LOW for the transaction that inserts the employee name and address information.
B. Set the DEADLOCK_PRIORITY to LOW for the transaction that inserts the employee demographics information.
C. Add conditional code that checks for server error 1205 on the transaction that inserts the employee name and address information. If this error is encountered, restart the transaction.
D. Add the ROWLOCK optimizer hint to the data manipulation SQL statements within the transactions.
E. Set the transaction isolation level to SERIALIZABLE for the transaction that inserts the employee name and address information.
Ans:B
4. You are a database developer for your company'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 nonclustered 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.
Ans:C
5. You are a database developer for your company's SQL Server 2000 database.You update several stored procedures in the database that create new end-of-month reports for the sales department.
The stored procedures contain complex queries that retrieve data from three or more tables. All tables in the database have at least one index.
Users have reported that the new end-of-month reports are running much slower than the previous version of the reports.
You want to improve the performance of the reports.
What should you do?
A. Create a script that contains the Data Definition Language of each stored procedure. Use this script as a workload file for the Index Tuning Wizard.
B. Capture the execution of each stored procedure in a SQL Profiler trace. Use the trace file as a workload file for the Index Tuning Wizard.
C. Update the index statistics for the tables used in the stored procedures.
D. Execute each stored procedure in SQL Query Analyzer, and use the Show Execution Plan option.
E. Execute each stored procedure in SQL Query Analyzer, and use the show Server Trace option.
Ans:E
6. You are a database developer for Wide World Importers. You are creating a database that will store order information. Orders will be entered in a client/server application. Each time a new order is entered, a unique order number must be assigned.
Order numbers must be assigned in ascending order. An average of 10,000 orders will be entered each day.
You create a new table named Orders and add an OrderNumber column to this table.
What should you do next?
A. Set the data type of the column to UniqueIdentifier.
B. Set the data type of the column to int, and set the IDENTITY property for the column.
C. Set the data type of the column to int. Create a user-defined function that selects the maximum order number in the table.
D. Set the data type of the column to int. Create a NextKey table, and add a NextOrder column to the table. Set the data type of the NextOrder column to int. Create a stored procedure to retrieve and update the value held in the NextKey
Ans:B
7. You are a database developer for a technical training center. Currently, administrative employees keep records of students, instructors, courses, and classroom assignments only on paper.
The training center wants to eliminate the use of paper to keep records by developing a database to record this information. You design the tables for this database.
Your design is shown in the exhibit:
You want to promote quick response times for queries and minimize redundant data. What should you do?
A. Create a new table named Instructors. Include an InstructorID column, and InstructorName column, and an OfficePhone column. Add an InstructorID column to the Courses table.
B. Move all the columns from the Classroom table to the Courses table, and drop the Classroom table.
C. Remove the PRIMARY KEY constraint from the Courses table, and replace the PRIMARY KEY constraint with a composite PRIMARY KEY constraint based on the CourseID and CourseTitle.
D. Remove the ClassroomID column, and base the PRIMARY KEY constraint on the ClassroomNumber and ClassTime columns.
Ans:A
8. You are designing a database that will contain customer orders. Customers will be able to order multiple products each time they place an order.
You review the database design in the exhibit:
You want to promote quick response times for queries and minimize redundant data.
What should you do? (Each correct answer presents part of the solution. Choose two)
A. Create a new order table named OrdersDetail. Add OrderID, ProductID, and Quantity columns to this table.
B. Create a composite PRIMARY KEY constraint on the OrderID and ProductID columns of the Orders table
C. Remove the ProductID and Quantity columns from the Orders table
D. Create a UNIQUE constraint on the OrderID column of the Orders table.
E. Move the UnitPrice column from the Products table to the Orders table.
Ans:A,C
9. You are the database developer for a publishing company.
You create the following stored procedure to report the year-to-date sales for a particular book title:
CREATE PROCEDURE get_sales_for_title
%title varchar(80), @ytd_sales int OUTPUT
AS
SELECT @ytd_sales = ytd_sales
FROM titles
WHERE title = @title
IF @@ROWCOUNT = 0
RETURN(-1)
ELSE
RETURN(0)
You are creating a script that will execute this stored procedure. If the stored procedure executes successfully, it should report the year-to-date sales for the book title.
If the stored procedure fails to execute, it should report the following message:
"No Sales Found"
How should you create the script?
A. DECLARE @retval int
DECLARE @ytd int
EXEC get_sales_for_title 'Net Etiquette', @ytd
IF @retval < 0
PRINT 'No sales found'
ELSE
PRINT 'Year to date sales: ' + STR (@ytd)
GO
B. DECLARE @retval int
DECLARE @ytd int
EXEC get_sales_for_title 'Net Etiquette', @ytd OUTPUT
IF @retval < 0
PRINT 'No sales found'
ELSE
PRINT 'Year to date sales: ' + STR (@ytd)
GO
C. DECLARE @retval int
DECLARE @ytd int
EXEC get_sales_for_title 'Net Etiquette',@retval OUTPUT
IF @retval < 0
PRINT 'No sales found'
ELSE
PRINT 'Year to date sales: ' + STR (@ytd)
GO
D. DECLARE @retval int
DECLARE @ytd int
EXEC @retval = get_sales_for_title 'Net Etiquette', @ytd OUTPUT
IF @retval < 0
PRINT 'No sales found'
ELSE
PRINT 'Year to date sales: ' + STR (@ytd)
GO
Ans:D
10. You are a database developer for a container manufacturing company. The containers produced by your company are a number of different sizes and shapes.
The tables that store the container information are shown in the exhibit:
A sample of the data stored in the tables is shown in this exhibit:
Periodically, the dimensions of the containers change. Frequently, the database users require the volume of a container. The volume of a container is calculated based on information in the shape and size tables.
You need to hide the details of the calculation so that the volume can be easily accessed in a SELECT query with the rest of the container information.
What should you do?
A. Create a user-defined function that requires ContainerID as an argument and returns the volume of the container.
B. Create a stored procedure that requires ContainerID as an argument and returns the volume of the container.
C. Add a column named volume to the container table. Create a trigger that calculates and stores volume in this column when a new container is inserted into the table.
D. Add a computed column to the container table that calculates the volume of the container.
Ans: A