Question With Answer : 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition
51. You are a database developer for Wingtip Toys.
You have created an order entry database that includes two tables, as shown in the exhibit:
Users enter orders into the entry application. When a new order is entered, the data is saved to the Order and LineItem tables in the order entry database.
You must ensure that the entire order is saved successfully.
Which script should you use?
A. BEGIN TRANSACTION Order
INSERT INTO Order VALUES (@ID, @CustomerID, @OrderDate)
INSERT INTO LineItem VALUES (@ItemID, @ID, @ProductID, @Price)
SAVE TRANSACTION Order
B. INSERT INTO Order VALUES (@ID, @CustomerID, @OrderDate)
INSERT INTO LineItem VALUES (@ItemID, @ID, @ProductID, @Price)
IF (@@Error = 0)
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
C. BEGIN TRANSACTION
INSERT INTO Order VALUES (@ID, @CustomerID, @OrderDate)
IF (@@Error = 0)
BEGIN
INSERT INTO LineItem
VALUES (@ItemID, @ID, @ProductID, @Price)
IF (@@Error = 0)
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
END
ELSE
ROLLBACK TRANSACTION
END
D. BEGIN TRANSACTION
INSERT INTO Order VALUES (@ID, @CustomerID, @OrderDate)
IF (@@Error = 0)
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
BEGIN TRANSACTION
INSERT INTO LineItem VALUES (@ItemID, @ID, @ProductID, @Price)
IF (@@Error = 0)
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
Ans:C
52. You are a database developer for a vacuum sales company. The company has a database named Sales that contains tables named VacuumSales and Employee.
Sales information is stored in the VacuumSales table. Employee information is stored in the Employee table. The Employee table has a bit column named IsActive. This column indicates whether an employee is currently employed.
The Employee table also has a column named EmployeeID that uniquely identifies each employee. All sales entered into the VacuumSales table must contain an employee ID of a currently employed employee.
How should you enforce this requirement?
A. Use the Distributed Transaction Coordinator to enlist the employee table in a distributed transaction that will roll back the entire transaction if the employee ID is not active.
B. Add a CHECK constraint on the EmployeeID column of the VacuumSales table.
C. Add a Foreign KEY constraint on the EmployeeID column of the VacuumSales table that references the EmployeeID column in the Employee table.
D. Add a FOR INSERT trigger on the Vacuumsales table. In the trigger, join the Employee table with the inserted table based on the EmployeeID column, and test the IsActive column.
Ans: D
53. You are a database developer for an online brokerage firm. The prices of the stocks owned by customers are maintained in a SQL Server 2000 database.
To allow tracking of the stock price history, all updates of stock prices must be logged. To help correct problems regarding price updates, any error that occurs during an update must also be logged.
When errors are logged, a message that identifies the stock producing the error must be returned to the client application.
You must ensure that the appropriate conditions are logged and that the appropriate messages are generated.
Which procedure should you use?
A. CREATE PROCEDURE UpdateStockPrice @StockID int, @Price decimal
AS BEGIN
DECLARE @Msg varchar(50)
UPDATE Stocks SET CurrentPrice = @Price
WHERE STockID = @ StockID
AND CurrentPrice <> @ Price
IF @@ERROR <> 0
RAISERROR ('Error %d occurred updating Stock %d.', 10, 1, @@ERROR, @StockID) WITH LOG
IF @@ROWCOUNT > 0
BEGIN
SELECT @Msg = 'Stock' + STR (@StockID) + 'updated to' + STR (@Price) + '.'
EXEC master. . xp_LOGEVENT 50001, @Msg
END
END
B. CREATE PROCEDURE UpdateStockPrice @StockID int, @Price decimal
AS BEGIN
UPDATE Stocks SET CurrentPrice = @Price
WHERE STockID = @ StockID
AND CurrentPrice <> @ Price
IF @@ERROR <> 0
PRINT 'ERROR' + STR(@@ERROR) + 'occurred updating Stock' +STR (@StockID)+ '.'
IF @@ROWCOUNT > 0
PRINT 'Stock' + STR (@StockID) + 'updated to' + STR (@Price) + '.'
END
C. CREATE PROCEDURE UpdateStockPrice @StockID int, @Price decimal
AS BEGIN
DECLARE @Err int, @RCount int, @Msg varchar(50)
UPDATE Stocks SET CurrentPrice = @Price
WHERE STockID = @ StockID
AND CurrentPrice <> @ Price
SELECT @Err = @@ERROR, @RCount = @@ROWCOUNT
IF @Err <> 0
BEGIN
SELECT @Mag = 'Error' + STR(@Err) + 'occurred updating Stock' + STR (@StockID) + '.'
EXEC master..xp_logevent 50001, @Msg
END
IF @RCOUNT > 0
BEGIN
SELECT @Msg = 'Stock' + STR (@StockID) + 'updated to' + STR (@Price) + '.'
EXEC master. . xp_LOGEVENT 50001, @Msg
END
END
D. CREATE PROCEDURE UpdateStockPrice @StockID int, @Price decimal AS BEGIN
DECLARE @Err int, @RCount int, @Msg varchar (50)
UPDATE Stocks SET CurrentPrice = @Price
WHERE STockID = @StockID
AND CurrentPrice <> @Price
SELECT @Err = @@ERROR, @RCount = @@ROWCOUNT
If @Err <> 0
RAISEERROR ('Error %d occurred updating Stock %d.', 10, 1, @Err, @StockID) WITH LOG
If @RCount > 0
BEGIN
SELECT @Msg = 'Stock' + STR (@StockID) + 'update to' + STR (@Price) + '.'
EXEC master. . xp_logevent 50001, @Msg
END
END
Ans: D
54. You are a database developer for a loan servicing company. You are designing database transactions to support a new data entry application.
Users of the new database entry application will retrieve loan information from a database. Users will make any necessary changes to the information and save the updated information to the database.
How should you design these transactions? (see Exhibit, Drag and Drop style)
A. Retrive loan information from the database.
B. The user reviews and modifies one piece of loan information.
C. Begin a transaction.
D. Save the update information in the database.
E. Commit the transaction.
F. Repeat the modify and update process for each piece of loan information.
G. The user reviews and modifies all of the loan information.
H. Roll back the transaction.
Ans: A,B,C,D,E,F
55. You are a developer for a company that leases trucks. The company has created a Web site that customer can be used to reserve trucks. You are designing the SQL server 2000 database to support the Web site.
New truck reservations are inserted into a table named Reservations. Customers who have reserved a truck can return to the Web site and update their reservation. When a reservation is updated, the entire existing reservation must be copied to a table named History.
Occasionally, customers will save an existing reservation without actually changing any of the information about the reservation. In this case, the existing reservation should not be copied to the History table.
You need to develop a way to create the appropriate entries in the History table.
What should you do?
A. Create a trigger on the reservations table to create the History table entries.
B. Create a cascading referential integrity constraint on the reservations table to create the History table entries.
C. Create a view on the reservations table. Include the WITH SCHEMA BINDING option in the view definition.
D. Create a view on the Reservations table. Include the WITH CHECK OPTION clause in the view definition.
Ans:A
56. You are a database developer for Proseware,Inc. The company has a database that contains information about companies located within specific postal codes. This information is contained in the Company table within this database.
Currently, the database contains company data for five different postal codes. The number of companies in a specific postal code currently ranges from 10 to 5,000. More companies and postal codes will be added to the database over time.
You are creating a query to retrieve information from the database. You need to accommodate new data by making only minimal changes to the database. The performance of your query must not be affected by the number of companies returned.
You want to create a query that performs consistently and minimizes future maintenance.
What should you do?
A. Create a stored procedure that requires a postal code as a parameter. Include the WITH RECOMPILE option when the procedure is created.
B. Create one stored procedure for each postal code.
C. Create one view for each postal code.
D. Split the company table into multiple tables so that each table contains one postal code. Build a partitioned view on the tables so that the data can still be viewed as a single table.
Ans: A
57. You are a database developer for Woodgrove Bank. You are implementing a process that loads data into a SQL Server 2000 database. As a part of this process, data is temporarily loaded into a table named Staging.
When the data load process is complete, the data is deleted from this table. You will never need to recover this deleted data.
You need to ensure that the data from the Staging table is deleted as quickly as possible.
What should you do?
A. Use a DELETE statement to remove the data from the table.
B. Use a TRUNCATE TABLE statement to remove the data from the table.
C. Use a DROP TABLE statements to remove the data from the table.
D. Use an updatable cursor to access and remove each row of data from the table.
Ans:B
58. You are a database developer for an automobile dealership. You are designing a database to support a Web site that will be used for purchasing automobiles. A person purchasing an automobile from the Web site will be able to customize his or her order by selecting the model and color.
The manufacturer makes four different models of automobiles. The models can be ordered in any one of five colors. A default color is assigned to each model.
The models are stored in a table named Models, and the colors are stored in a table named Colors.
These tables are shown in the exhibit:
You need to create a list of all possible model and color combinations.
Which script should you use?
A. SELECT m.ModelName, c.ColorName
FROM Colors AS c FULL OUTER JOIN Models AS m
ON c.ColorID = m.ColorID
ORDER BY m.ModelName, c.ColorName
B. SELECT m.ModelName, c.ColorName
FROM Colors AS c CROSS JOIN Models AS m
ORDER BY m.ModelName, c.ColorName
C. SELECT m.ModelName, c.ColorName
FROM Colors AS m INNER JOIN Colors AS c
ON m.ColorID = c.ColorID
ORDER BY m.ModelName, c.ColorName
D. SELECT m.ModelName, c.ColorName
FROM Colors AS c LEFT OUTER JOIN Models AS m
ON c.ColorID = m.ColorID
UNION
SELECT m.ModelName, c.ColorName
FROM Colors AS c RIGHT OUTER JOIN Models AS m
ON c.ColorID = m.ColorID
ORDER BY m.ModelName, c.ColorName
E. SELECT m.ModelName
FROM Models AS m
UNION
SELECT c.ColorName
FROM Colors AS c
ORDER BY m.ModelName
Ans:B
59. You are a database developer for Adventure Works. A large amount of data has been exported from a human resources application to a text file.
The format file that was used to export the human resources data is shown below:
1 SQLINT 0 4 "," 1 EmployeeID ""
2 SQLCHAR 0 50 "," 2 FirstName SQL.Latin1_Gen...
3 SQLCHAR 0 50 "," 3 LastName SQL.Latin1_Gen...
4 SQLCHAR 0 10 "," 4 SSN SQL.Latin1_Gen...
5 SQLDATETIME 0 8 "," 5 Hire_Date ""
You need to import that data programmatically into a table named Employee.
The Employee table is shown in the exhibit table:
You need to run this import as quickly as possible.
What should you do?
A. Use SQL-DMO and Microsoft Visual Basic Scripting Edition to create a table object. Use the ImportData method of the table object to load the table.
B. Use SQL-DMO and Microsoft Visual Basic Scripting Edition to create a database object. Use the CopyData property of the database object to load the table.
C. Use data transformation services and Microsoft Visual Basic Scripting edition to create a Package object. Create a connection object for the text file. Add a BulkInsertTask object to the Package object. Use the Execute method of the package object to load the data.
D. Use data transformation services and Microsoft Visual Basic Scripting edition to create a Package object. Create a connection object for the text file. Add a BulkInsertTask2 object to the Package object. Use the Execute method of the ExecuteSQLTask2 object to load the data.
Ans: C
60. You are a database developer for an insurance company. The company has a database named Policies. You have designed stored procedures for this database that will use cursors to process large result sets.
Analysts who use the stored procedures report that there is a long initial delay before data is displayed to them. After the delay, performance is adequate. Only data analysts, who perform data analysis, use the Policies database.
You want to improve the performance of the stored procedures.
Which script should you use?
A. EXEC sp_configure 'cursor threshold', 0
B. EXEC sp_dboption 'Policies' SET CURSOR_CLOSE_ON_COMMIT ON
C. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
D. ALTER DATABASE Policies SET CURSOR_DEFAULT LOCAL
Ans:A