Question With Answer : 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition
81. You are the developer of a database named Inventory. You have a list of reports that you must create. These reports will be run at the same time. You write queries to create each report. Based on the queries, you design and create the indexes for the database tables.
You want to ensure that you have created useful indexes.
What should you do?
A. Run sql trace, and use the Objects event classes.
B. Run the Index Tuning Wizard against a workload file that contains the queries used in the reports.
C. Run System Monitor, and use the SQLServer:Access Methods counter.
D. Execute the queries against the tables in SQL Query Analyzer, and use the SHOWPLAN_TEXT option.
Ans: B
82. You are a database developer for your company's SQL server 2000 database.
You use the following script to create a view named Employee in the database:
CREATE VIEW Employee AS
SELECT P.SSN, P.LastName, P.FirstName, P. Address, P.city, P.State, P.Birthdate, E.EmployeeID, E.Department, E.Salary
FROM Person AS P JOIN Employees AS E ON (P.SSN = E.SSN)
The view will be used by an application that inserts records in both the Person and Employees tables.
The script that was used to create these tables is shown in the exhibit:
You want to enable the application to issue INSERT statements against the view.
What should you do?
A. Create an AFTER trigger on the view.
B. Create an INSTEAD OF trigger on the view.
C. Create an INSTEAD OF trigger on the Person and Employees tables.
D. Alter the view to include the WITH CHECK option.
E. Alter the view to include the WITH SCHEMA BINDING option.
Ans: B
83. You are a database developer for Wide World Importers. You are creating a table named Orders for the company's SQL Server 2000 database. Each order contains an order ID, an order date, a customer ID, a shipper ID, and a ship date.
Customer services representatives who take the orders must enter the order date, customer ID, and shipper ID when the order is taken. The order ID must be generated automatically by the database and must be unique.
Orders can be taken from existing customers only. Shippers can be selected only from an existing set of shippers. After the customer service representatives complete the order, the order is send to the shipping department for final processing.
The shipping department enters the ship date when the order is shipped.
Which script should you use to create the Orders table?
A. CREATE TABLE Orders
(
Order ID uniqueidenfitier PRIMARY KEY NOT NULL,
OrderDate, datetime NULL,
CustomerID char(5) NOT NULL FOREIGN KEY REFERENCES Customer (Customer ID),
ShipperID int NOT NULL FOREIGN KEY REFERENCES Shippers(shipperID),
ShipDate datetime Null
)
B. CREATE TABLE Orders
(
Order ID int identity (1, 1)PRIMARY KEY NOT NULL,
OrderDate, datetime NOT NULL,
CustomerID char(5) NOT NULL FOREIGN KEY REFERENCES Customer (Customer ID),
ShipperID int NOT NULL FOREIGN KEY REFERENCES Shippers(shipperID),
ShipDate datetime Null
)
C. CREATE TABLE Orders
(
Order ID int identity (1, 1)PRIMARY KEY NOT NULL,
OrderDate, datetime NULL,
CustomerID char(5) NOT NULL FOREIGN KEY REFERENCES Customer (Customer ID),
ShipperID int NULL
ShipDate datetime Null
)
D. CREATE TABLE Orders
(
Order ID uniqueidenfitier PRIMARY KEY NOT NULL,
OrderDate, datetime NOT NULL,
CustomerID char(5) NOT NULL FOREIGN KEY REFERENCES Customer (Customer ID),
ShipperID int NOT NULL FOREIGN KEY REFERENCES Shippers(shipperID),
ShipDate datetime Null
)
Ans:B
84. You are a database developer for Luce Publishing. The company stores its sales data in a SQL Server 2000 database. This database contains a table named Orders.
There is currently a clustered index on the table, which is generated by using a customer's name and the current date. The Orders table currently contains 750,000 rows, and the number of rows increased by 5 percent each week.
The company plans to launch a promotion next week that will increase the volume of inserts to the Orders table by 50 percent.
You want to optimize inserts to the Orders table during the promotion.
What should you do?
A. Create a job that rebuilds the clustered index each night by using the default FILLFACTOR.
B. Add additional indexes to the Orders table.
C. Partition the Orders table vertically.
D. Rebuild the clustered index with a FILLFACTOR of 50.
E. Execute the UPDATE STATISTICS statement on the Orders table.
Ans:D
85. You are designing your company's Sales database. The database will be used by three custom applications. Users who require access to the database are currently members of Microsoft Windows 2000 groups.
Users were placed in the Windows 2000 groups according to their database access requirements. The custom applications will connect to the sales database through application roles that exist for each application.
Each application role was assigned a password.
All users should have access to the Sales database only through the custom applications. No permissions have been granted in the database.
What should you do?
A. Assign appropriate permissions to each Windows 2000 group.
B. Assign appropriate permissions to each application role.
C. Assign the Windows 2000 groups to the appropriate application role.
D. Provide users with the password to the application role
Ans:B
86. You are a database developer for your company's database named Insurance.
You execute the following script in SQL Query Analyzer to retrieve agent and policy information:
SELECT A.LastName, A.FirstName, A.CompanyName, P.PolicyNumber
FROM Policy AS P JOIN AgentPolicy AS AP
ON (P.PolicyNumber = AP.PolicyNumber)
JOIN Agents AS A ON (A.AgentID= AP.AgentID)
The query execution plan that is generated is shown below:
The information received when you move the pointer over the Table Scan icon is shown below:
You want to improve the performance of this query.
What should you do?
A. Change the order of the tables in the FROM clause to list the Agent table first
B. Use a hash join hint to join the Agent table in the query.
C. Create a clustered index on the AgentID column of the Agent table.
D. Create a nonclustered index on the AgentID column of the Agent table.
Ans: C
87. You are a database consultant. One of your customers reports slow query response times for a SQL Server 2000 database, particularly when table joins are required.
Which steps should you take to analyze this performance problem?
To answer, Simulate the Drag and Drop style and list in steps for the appropriate transaction order choices beside the appropriate transaction steps. (Use only order choices that apply)
A. Use SQL Profiler to create a trace based on the SQL Profiler Tuning template.
B. Specify that the trace data be saved to a file, and specify a maximum file size.
C. Specify a stop time for the trace.
D. Run the trace.
E. Use the trace file as an input to the Index Tuning Wizard.
F. Replay the trace and examine the output.
G. Use SQL Profiler to create a trace based on the SQL Profiler Standard Template.
Ans: A,B,C,D,E,F
Step 1 Use SQL Profiler to create a trace based on the SQL Profiler Tuning Template
Step 2 Specify that the trace data be saved to a file, and specify a maximum file size.
Step 3 Specify a stop time for the trace.
Step 4 Run the trace.
Step 5 Use the trace file as an input to the Index Tuning Wizard.
Step 6 Replay the trace, and examine the output.
Step 7 (none)
The answer (G) is Wrong and is Not used.
88. You are a database developer for Wingtip Toys. The company stores its sales information in a SQL Server 2000 database. This database contains a table named Orders. You want to move old data from the orders table to an archive table.
Before implementing this process, you want to identify how the query optimizer will process the INSERT statement.
You execute the following script in SQL Query Analyzer:
SET SHOWPLAN_TEXT ON
GO
CREATE TABLE Archived_Orders_1995_1999
(
OrderID int,
CustomerID char (5),
EmployeeID int,
OrderDate datetime,
ShippedDate datetime
)
INSERT INTO Archived_Orders_1995_1999
SELECT OrderID, CustomerID, EmployeeID, OrderDate, ShippedDate
FROM SalesOrders
WHERE ShippedDate < DATEADD (year, -1, getdate())
You receive the following error message:
Invalid object name 'Archived_Orders_1995_1999.'
What should you do to resolve the problem?
A. Query the Archived_Orders_1995_1999 table name with the owner name.
B. Request CREATE TABLE permissions.
C. Create the Archived_Orders_1995_1999 table before you execute the SET SHOWPLAN_TEXT ON statement.
D. Change the table name to ArchivedOrders.
Ans:C
89. You are a database developer for WoodGrove Bank. The company has a database that contains human resources information. You are designing transactions to support data entry into this database.
The script for two of the transactions that you designed are shown in the exhibit:
While testing these scripts, you discover that the database server occasionally detects a deadlock condition.
What should you do?
A. In Transaction 2, move the UPDATE Customer statement before the UPDATE CustomerPhone statement
B. Add the SET DEADLOCK_PRIORITY LOW statement to both transactions
C. Add code that checks for server error 1205 to each script. If this error is encountered, restart the transaction in which it occurred.
D. Add the SET LOCK_TIMEOUT 0 statement to both transactions
Ans: A
90. You are a database developer for a company that compiles statistics for baseball teams. These statistics are stored in a database named Statistics.
The Players of each team are entered in a table named Rosters in the Statistics database.
The script that was used to create the Rosters table is shown in the exhibit:
Each baseball team can have a maximum of 24 players on the roster at any one time.
You need to ensure that the number of players on the team never exceeds the maximum.
What should you do?
A. Create a trigger on the Rosters table that validates the data
B. Create a rule that validates the data
C. Create an update view that includes the WITH CHECK OPTION clause in its definition
D. Add a CHECK constraint on the Rosters table to validate the data
Ans:A