Question With Answer : 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition
11. You are a database developer for a hospital. There are four supply rooms on each floor of the hospital, and the hospital has 26 floors. You are designing an inventory control database for disposable equipment.
Certain disposable items must be kept stored at all times. As each item is used, a barcode is scanned to reduce the inventory count in the database.
The supply manager should be paged as soon as a supply room has less than the minimum quantity of an item.
What should you do?
A. Create a stored procedure that will be called to update the inventory table. If the resulting quantity is less than the restocking quantity, use the xp_logevent system stored procedure to page the supply manager.
B. Create an INSTEAD OF UPDATE trigger on the inventory table. If the quantity in the inserted table is less than the restocking quantity, use SQLAgentMail to send an e-mail message to the supply manager's pager.
C. Create a FOR UPDATE trigger on the inventory table. If the quantity in the inserted table is less than the restocking quantity, use the xp_sendmail system stored procedure to page the supply manager.
D. Schedule the SQL server job to run at four-hour intervals. Configure the job to use the @notify_level_page = 2 argument. Configure the job so that it tests each item's quantity against the restocking quantity. Configure the job so that it returns a false value if the item requires restocking. This will trigger the paging of the supply manager.
Ans: C
12. You are the developer of a database that supports time reporting for your company. Usually there is an average of five users accessing this database at one time, and query response times are less than one second.
However, on Friday afternoons and Monday mornings, when most employees enter their timesheet data, the database usage increases to an average of 50 users at one time.
During these times, the query response times increase to an average of 15 to 20 seconds.
You need to find the source of the slow query response times and correct the problem.
What should you do?
A. Use the sp_lock and sp_who system stored procedures to find locked resources and to identify processes that are holding locks. Use this information to identify and redesign the transactions that are causing the locks.
B. Query the sysprocesses and sysobjects system tables to find deadlocked resources and to identify which processes are accessing those resources. Set a shorter lock timeout for the processes that are accessing the deadlock resources.
C. Query the sysprocesses system table to find which resources are being accessed. Add clustered indexes on the primary keys of all of the tables that are being accessed.
D. Use the sp_monitor system stored procedure to identify which processes are being affected by the increased query response times. Set a less restrictive transaction isolation level for these processes.
Ans: A
13. You are a database developer for an insurance company. The insurance company has a multi-tier application that is used to enter data about its policies and the owners of the policies.
The policy owner information is stored in a table named Owners.
The script that was used to create this table is shown:
CREATE TABLE Owners
(
OwnerID int IDENTITY (1, 1) NOT NULL
FirstName char(20) NULL
LastName char(30) NULL
BirthDate date NULL
CONSTRAINT PK_Owners PRIMARY KEY (Owner ID)
)
When information about policy owners is entered, the owner's birth date is not included; the database needs to produce a customized error message that can be displayed by the data entry application.
You need to design a way for the database to validate that the birth date is supplied and to produce the error message if it is not.
What should you do?
A. Add a CHECK constraint on the BirthDate column.
B. Create a rule, and bind the rule to the BirthDate column.
C. Alter the Owners table so that the BirthDate column does not allow null.
D. Create a trigger on the Owners table that validates the BirthDate column
Ans:D
14. You are the database developer for a large brewery. Information about each of the brewery's plants and the equipment located at each plant is stored in a database named Equipment.
The plant information is stored in a table named Location, and the equipment information is stored in a table named Parts.
The scripts that were used to create these tables are shown:
CREATE TABLE Location
(
LocationID int NOT NULL
LocationName char (30) NOT NULL UNIQUE
CONSTRAINT PK_Location PRIMARY KEY (LocationID)
)
CREATE TABLE Parts
(
PartID int NOT NULL
LocationID int NOT NULL
PartName char (30) NOT NULL
CONSTRAINT PK_Parts PRIMARY KEY (PartID)
CONSTRAINT FK_PartsLocation FOREIGN KEY (Location ID)
REFERENCES Location (LocationID)
)
The brewery is in the process of closing several existing plants and opening several new parts. When a plant is closed, the information about the plant and all of the equipment at that plant must be deleted from the database.
You have created a stored procedure to perform this operation.
The stored procedure is shown below:
CREATE PROCEDURE sp_DeleteLocation @LocName char(30) AS
BEGIN
DECLARE @PartID int
DECLARE crs_Parts CURSOR FOR
SELECT p.PartID
FROM Parts AS p INNER JOIN Location AS 1
ON p.LocationID = @LocName
WHERE 1.LocationName = @LocName
OPEN crs_Parts
FETCH NEXT FROM crs_Parts INTO @PartID
WHILE (@@FETCH_STATUS <> -1)
BEGIN
DELETE Parts WHERE CURRENT OF crs_Parts
FETCH NEXT FROM crs_Parts INTO @PartID
END
CLOSE crs_Parts
DEALLOCATE crs_Parts
DELETE Location WHERE LocatioName = @LocName
END
This procedure is taking longer than expected to execute. You need to reduce the execution time of the procedure.
What should you do?
A. Add the WITH RECOMPILE option to the procedure definition.
B. Replace the cursor operation with a single DELETE statement.
C. Add a BEGIN TRAN statement to the beginning of the procedure, and add a COMMIT TRAN statement to the end of the procedure.
D. Set the transaction isolation level to READ UNCOMMITTED for the procedure.
E. Add a nonclustered index on the PartID column of the parts table.
Ans: B
15. You are a database developer for an insurance company. Information about the company's insurance policies is stored in a SQL Server 2000 database.
You create a table named policy for this database by using the script shown:
CREATE TABLE Policy
(
PolicyNumber int NOT NULL DEFAULT (0)
InsuredLastName char (30) NOT NULL
InsuredFirstName char (20) NOT NULL
InsuredBirthDate dattime NOT NULL
PolicyDate datetime NOT NULL
FaceAmount money NOT NULL
CONSTRAINT PK_Policy PRIMARY KEY (PolicyNumber)
)
Each time the company sells a new policy, the policy must be assigned a unique policy number. The database must assign a new policy number when a new policy is entered.
What should you do?
A. Create an INSTEAD OF INSERT trigger to generate a new policy number, and include the policy number in the data inserted into the table.
B. Create an INSTEAD OF UPDATE trigger to generate a new policy number, and include the policy number in the data inserted into the table.
C. Create an AFTER UPDATE trigger to generate a new policy number, and include the policy number in the data inserted into the table.
D. Replace the DEFAULT constraint with a AFTER INSERT trigger that generates a new policy number and includes the policy number in the data inserted into the table.
Ans:A
16. You are a member of a database development team for a telecommunications company.
Another developer on the team, Marc, has created a table named Customers in the Corporate database. Because the table contains confidential information, he has granted SELECT permissions on the table only to the other members of your team.
You are developing an application that will allow employees in the marketing department to view some of the information in the Customers table. These employees are all members of the Marketing database role.
To support this application, you create a view named vwCustomers on the Customers table. After creating the view, you grant SELECT permissions on the view to the Marketing role.
When members of the Marketing role attempt to retrieve data from the view, they receive the following error message:
SELECT permission denied on object 'Customers', database 'Corporate', owner 'Mare'
You must ensure that the members of the Marketing role can only use the vwCustomers view to access the data in the Customers table.
What should you do?
A. Add the marketing role to the sysadmin fixed server role.
B. Transfer the ownership of the vwCustomers view to the marketing role.
C. Instruct Marc to transfer the ownership of the Customers table to each member of the marketing role.
D. Instruct Marc to grant the users SELECT permissions on the Customers table.
E. Drop the vwcustomers view. Instruct Marc to re-create the view and to grant SELECT permissions on the view to the marketing role.
Ans:E
17. You are designing your Company's SQL Server 2000 sales database, which will be accessed by a custom application. Customer service and marketing employees require SELECT, INSERT, and UPDATE permissions on all tables in the Sales database.
In addition to using the custom application, the marketing employees will use Microsoft Excel to retrieve data from the sales database to create charts.
Customer service employees belong to Microsoft Windows 2000 group named CSR, and marketing employees belong to a Windows 2000 group named Marketing. You want the customer service employees to access the sales database only from within the custom application.
You want to allow the marketing employees to use both the custom application and Excel to access the sales database. No permissions have been granted in the database.
What should you do?
A. Create a database role named Marketing for the marketing employees and a database role named customer service for the customer service employees. Add the Windows 2000 groups to the user-defined database roles. Grant SELECT permissions on all tables in the database to the marketing database role. Grant SELECT, INSERT, and UPDATE permissions on all tables in the database to the Customer Service database role.
B. Create one application role to be used by both the customer service and marketing employees. Grant SELECT permissions on all tables in the database to the Windows 2000 marketing group. Grant SELECT, INSERT, and UPDATE permissions on all tables in the database to the Windows 2000 CSR group.
C. Create an application role for the custom application. Grant SELECT, INSERT and UPDATE permissions to the application role. Create a database role named Marketing for the marketing employees. Add the Windows 2000 Marketing group to the Marketing database role. Grant SELECT permissions on all tables in the database to the marketing database role.
D. Create one application role for the customer service employees. Create a second application role for the marketing employees. Grant SELECT permissions on all tables in the database to the application role to be used by the marketing employees. Grant SELECT, INSERT, and UPDATE permissions on all tables in the database to the application role to be used by the customer service employees.
E. Create one application role for the customer service employees. Create a second application role for the marketing employees to access the sales database by using the custom application. Create a third application role for the marketing employees to use when retrieving data from Excel. Grant SELECT permissions on all tables in the database to both application roles to be used by the marketing employees. Grant SELECT, INSERT, and UPDATE permissions on all tables in the database to the application role to be used buy the customer service employees.
Ans:C
18. You are a database developer for an online book retailer. Customers use the company's web site to place orders for books. As orders are entered, they are inserted into a database named BookOrders.
During a nightly batch process, the order information is transferred to a database named Reports.
The Reports database includes a table named orders and a table named LineItem. The Order table contains basic information about the orders. The LineItem table contains information about the individual items in the orders.
The order and LineItem tables are shown in the exhibit:
Customers must be able to use the Company's web site to view orders stored in the Reports database. Customers should be able to see only their own orders. Customers should not be able to modify the orders.
The primary key values of the orders are not relevant to the customers and should not be visible.
What should you do?
A. Create a view that displays the order information for a customer.
B. Create a stored procedure that retrieves the order information for a customer.
C. Create a scalar user-defined function that retrieves the order information for a customer.
D. Grant SELECT permissions on the Order and LineItem tables to the customers.
Ans:B
19. You are a database developer for a large travel company. Information about each of the company's departments is stored in a table named Department. Data about each of the company's travel agents and department managers is stored in a table named Employees.
The SQLLogin column of the Employees table contains the database login for the travel agent or department manager.
The department and employees table are shown in the exhibit:
Each department manager has been added to the managers database role. You need to allow members of this database role to view all of the data in the department table. Members of this role should be able to insert or update only the row that pertains to their department.
You grant the managers database role SELECT permissions on the department table.
What should you do next?
A. Create a trigger on the department table that checks whether the database login of the user performing the insert or update operation belongs to a member of that department.
B. Create a view that includes all columns in the department table and the SQLLogin column from the employees table (Include the WITH CHECK OPTION clause in the view definition).
C. Grant INSERT and UPDATE permissions on the department table.
D. Grant INSERT and UPDATE permissions on the SQLLogin column of the employees table.
Ans:B
20. You are a database developer for your company's database named sales. The database contains a table named Orders.
The script that was used to create the table is shown:
CREATE TABLE Orders
(
OrderID int NOT NULL
CustomerID char (5) NOT NULL
OrderDate datetime DEFAULT GETDATE ( ) NULL
ShippedDate datetime NULL
Freight money NULL
ShipName varchar (40) NULL
)
GO
CREATE CLUSTERED INDEX IX_OrderID ON Orders (OrderID)
GO
CREATE NONCLUSTERED INDEX IX_CustomerID ON Orders (CustonmerID)
An application will execute queries like the following to retrieve orders for a customer:
SELECT OrderID, CustomerID, OrderDate
FROM Orders WHERE CustomerID = 'WHITC'
ORDER BY OrderDate DESC
The query execution plan that is generated is shown in the Query Execution Plan exhibit:
You want this query to execute as quickly as possible.
What should you do?
A. Create a nonclustered index on the OrderDate column.
B. Create a clustered index on the OrderDate column.
C. Change the clustered index on the OrderID column to a nonclustered index.
D. Change the nonclustered index on the CustomerID column to include the OrderDate column.
Ans:D