Question With Answer : 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition
31. You are a database developer for a toy manufacturer. Each employee of the company is assigned to either an executive, administrative, or labor position. The home page of the company intranet displays company news that is customized for each position type.
When an employee logs on to the company intranet, the home page identifies the employee's position type and displays the appropriate company news. Company news is stored in a table named News, which is located in the corporate database.
The script that was used to create the News table is shown:
CREATE TABLE News
(
NewsID int NOT NULL
NewsText varchar (8000) NOT NULL
EmployeePositionType char (15) NOT NULL
DisplayUntil datetime NOT NULL
DateAdded datetime NOT NULL DEFAULT (getdate( ))
CONSTRAINT PK_News PRIMARY KEY (NewsID)
)
Users of the intranet need to view data in the news table, but do not need to insert, update, or delete data in the table.
You need to deliver only the appropriate data to the intranet, based on the employee's position type.
What should you do?
A. Create a view that is defined to return the rows that apply to a specified position type.
B. Create a stored procedure that returns the rows that apply to a specified position type.
C. Grant SELECT permissions on the EmployeePositionType column for each position type.
D. Grant permission on the News table for each position type.
Ans:B
32. You are a database developer for a Company that produces an online telephone directory.
A table named PhoneNumbers is shown in the exhibit:
After loading 100,000 names into the table, you create indexes by using the following script:
ALTER TABLE [dbo]. [phonenumbers] WITH NOCHECK ADD
CONSTRAINT[PK_PhoneNumbers]PRIMARY KEY CLUSTERED (
[FirstName],
[LastName],
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX
[IX_PhoneNumbers] ON [dbo].[phonenumbers](
[PhoneNumberID]
) ON [PRIMARY]
GO
You are testing the performance of the database. You notice that queries such as the following take a long time to execute:
Return all names and phone numbers for persons who live in a certain city and whose last name begin with 'W'
How should you improve the processing performance of these types of queries? (Each correct answer presents part of the solution. Choose two)
A. Change the PRIMARY KEY constraint to use the LastName column followed by the FirstName column.
B. Add a nonclustered index on the City column.
C. Add a nonclustered index on the AreaCode, Exchange, and Number columns.
D. Remove the unique index from the PhoneNumberID column.
E. Change the PRIMARY KEY constraints to a nonclustered index.
F. Execute an UPDATE STATISTICS FULLSCAN ALL statement in SQL Query Analyzer.
ANS: A,B
33. You are a database developer for an insurance company. You are tuning the performance of queries in SQL Query Analyzer.
In the query pane, you create the following query:
SELECT P.PolicyNumber, P.IssueState, AP.Agent
FROM Policy AS P JOIN AgentPolicy AS AP
ON (P.PolicyNUmber = AP.PolicyNumber)
WHERE IssueState = 'IL'
AND PolicyDate BETWEEN '1/1/2000' AND '3/1/2000'
AND FaceAmount > 1000000
You choose display estimated execution plan from the query menu and execute the query.
The query execution plan that generated is shown below:
What should you do?
A. Rewrite the query to eliminate BETWEEN keyword
B. Add a join hint that includes the HASH option to the query
C. Add the WITH (INDEX(0)) table hint to the policy table
D. Update statistics on the Policy table
E. Execute the DBCC DBREINDEX statement on the policy table.
Ans:D
34. You are a database developer for an SQL Server 2000 database. You are planning to add new indexes, drop some indexes, and change other indexes to composite and covering indexes.
For documentation purposes, you must create a report that shows the indexes used by queries before and after you make changes.
What should you do?
A. Execute each query in SQL Query Analyzer, and use the SHOWPLAN_TEXT option. Use the output for the report.
B. Execute each query in SQL Query Analyzer, and use the Show Execution Plan option. Use the output for the report
C. Run the Index Tuning Wizard against a Workload file. Use the output for the report
D. Execute the DBCC SHOW_STATISTICS statement. Use the output for the report
Ans:A
35. You are a database developer for a hospital. You are designing a SQL Server 2000 database that will contain physician and patient information. This database will contain a table named Physicians and a table named Patients.
Physicians treat multiple patients. Patients have a primary physician and usually have a secondary physician. The primary physician must be identified as the primary physician. The Patients table will contain no more than 2 million rows.
You want to increase I/O performance when data is selected from the tables. The database should be normalized to the third normal form.
Which script should you use to create the tables?
A. CREATE TABLE Physicians
(
Physicians ID int NOT NULL CONSTRAINT PK_Physician PRIMARY KEY CLUSTERED
LastName_varchar(25) NOT NULL
)
GO
CRETAE TABLE Patient
(
PatientID bigint NOT NULL CONSTRAINT PK_Patients PRIMARY KEY CLUSTERED,
LastName varchar (25) NOT NULL,
FirstName varchar (25) NOT NULL,
PrimaryPhysician int NOT NULL,
SecondaryPhysician int NOT NULL,
CONSTRAINT PK_Patients_Physicians1 FOREIGN KEY (PrimaryPhysician) REFERENCES Physicians (PhysicianID),
CONSTRAINT PK_Patients_Physicians2 FOREIGN KEY (SecondaryPhysician) REFERENCES Physicians (PhysicianID)
)
B. CREATE TABLE Patient
(
Patient ID smallint NOT NULL CONSTRAINT PK_Patient PRIMARY KEY CLUSTERED,
LastName_varchar(25) NOT NULL,
FirstName varchar (25) NOT NULL,
PrimaryPhysician int NOT NULL,
SecondaryPhysician int NOT NULL,
)
GO
CRETAE TABLE Physicians
(
PhysicianID smallint NOT NULL CONSTRAINT PK_Physician PRIMARY KEY CLUSTERED,
LastName varchar (25) NOT NULL,
FirstName varchar (25) NOT NULL,
CONSTRAINT PK_Physicians_Patients FOREIGN KEY (PhysicianID) REFERENCES Patients (PatientID)
)
C. CREATE TABLE Patients
(
PatientID bigint NOT NULL CONSTRAINT PK_Patients PRIMARY KEY CLUSTERED,
LastName varchar (25) NOT NULL,
FirstName varchar (25) NOT NULL,
)
GO
CREATE TABLE Physicians
(
PhysicianID int NOT NULL CONSTRAINT PK_Physician PRIMARY KEY CLUSTERED,
LastName varchar (25) NOT NULL,
FirstName varchar (25) NOT NULL,
)
GO
CREATE TABLE PatientPhysician
(
PatientPhysicianID bigint NOT NULL CONSTRAINT PK_PatientsPhysician PRIMARY KEY CLUSTERED,
PhysicianID int NOT NULL,
PatientID int NOT NULL,
PrimaryPhysician bit NOT NULL,
FOREIGN KEY (PhysicianID) REFERENCES Physicians (PhysicianID),
FOREIGN KEY (PatientID) REFERENCES Patients (PatientID)
)
D. CREATE TABLE Patients
(
PatientID int NOT NULL PRIMARY KEY,
LastName varchar (25) NOT NULL,
FirstName varchar (25) NOT NULL,
)
GO
CREATE TABLE Physicians
(
PhysicianID int NOT NULL PRIMARY KEY,
LastName varchar (25) NOT NULL,
FirstName varchar (25) NOT NULL,
)
GO
CREATE TABLE PatientPhysician
(
PhysicianID int NOT NULL REFERENCES Physicians (PhysicianID),
PatientID int NOT NULL REFERENCES Patients (PatientID), PrimaryPhysician bit NOT NULL,
CONSTRAINT PK_PatientsPhysician PRIMARY KEY (PhysicianID, PatientID)
)
Ans:D
36. You are the database developer for your company's SQL Server 2000 database. This database contains a table named Invoices. You are a member of the db_owner role.
Eric, a member of the HR database role, created the Trey_Research_Updateinvoices trigger on the Invoices table. Eric is out of the office, and the trigger is no longer needed.
You execute the following statement in the sales database to drop the trigger:
DROP TRIGGER Trey Research_UpdateInvoices
You receive the following error message:
Cannot drop the trigger 'Trey Research_UpdateInvoices', because it does not exist in the system catalog.
What should you do before you can drop the trigger?
A. Add your login name to the HR database role.
B. Qualify the trigger name with the trigger owner in the DROP TRIGGER statement.
C. Disable the trigger before executing the DROP TRIGGER statement.
D. Define the trigger number in the DROP TRIGGER statement.
E. Remove the text of the trigger from the sysobjects and syscomments system tables.
Ans:B
37. You have designed the database for a Web site that is used to purchase concert tickets. During a ticket purchase, a buyer views a list of available tickets, decides whether to buy the tickets, and then attempts to purchase the tickets. This list of available tickets is retrieved in a cursor.
For popular concerts, thousands of buyers might attempt to purchase tickets at the same time.
Because of the potentially high number of buyers at any one time, you must allow the highest possible level of concurrent access to the data.
How should you design the cursor?
A. Create a cursor within an explicit transaction, and set the transaction isolation level to REPEATABLE READ.
B. Create a cursor that uses optimistic concurrency and positioned updates. In the cursor, place the positioned UPDATE statements within an explicit transaction.
C. Create a cursor that uses optimistic concurrency. In the cursor, use UPDATE statements that specify the key value of the row to be updated in the WHERE clause, and place the UPDATE statements within an implicit transaction.
D. Create a cursor that uses positioned updates. Include the SCROLL_LOCKS argument in the cursor definition to enforce pessimistic concurrency. In the cursor, place the positioned UPDATE statements within an implicit transaction.
Ans: B
38. You are a database developer for a company that conducts telephone surveys of consumer music preferences. As the survey responses are received from the survey participants, they are inserted into a table named SurveyData.
After all of the responses to a survey are received, summaries of the results are produced.
You have been asked to create a summary by sampling every fifth row of responses for a survey. You need to produce the summary as quickly as possible.
What should you do?
A. Use a cursor to retrieve all of the data for the survey. Use the FETCH RELATIVE 5 statement to select the summary data from the cursor.
B. Use a SELECT INTO statement to retrieve the data for the survey into a temporary table. Use a SELECT TOP 1 statement to retrieve the first row from the temporary table.
C. Set the query rowcount to five. Use a SELECT statement to retrieve and summarize the survey data.
D. Use a SELECT TOP 5 statement to retrieve and summarize the survey data.
Ans: A
39. You are a database developer for a lumber company. You are performing a one-time migration from a flat-file database to SQL Server 2000. You export the flat-file database to a text file in comma-delimited format.
The text file is shown in the Import file below:
1111, '*4 Interior', 4, 'Interior Lumber', 1.12
1112, '2*4 Exterior', 5, 'Exterior Lumber', 1.87
2001, '16d galvanized',2, 'Bulk Nails', 2.02
2221, '8d Finishing brads',3, 'Nails', 0.01
You need to import this file into SQL Server tables named Product and Category.
The product and category tables are shown in the product and Category Tables exhibit:
You want to import the data using the least amount of administrative effort.
What should you do?
A. Use the bcp utility, and specify the -t option.
B. Use the BULK INSERT statement, and specify the FIRE_TRIGGERS argument.
C. Use the SQL-DMO BulkCopy2 object and set the TableLock property to TRUE.
D. Use data transformation services to create two Transform Data tasks. For each task, map the text file columns to the database columns.
Ans:D
40. You are a database developer for a database named Accounts at Woodgrove Bank. A developer is creating a multi-tier application for the bank. Bank employees will use the application to manage customer accounts.
The developer needs to retrieve customer names from the accounts database to populate a drop-down list box in the application. A user of the application will use the list box to locate a customer account.
The database contains more than 50,000 customer accounts. Therefore, the developer wants to retrieve only 25 rows as the user scrolls through the list box. The most current list of customers must be available to the application at all times.
You need to recommend a strategy for the developer to use when implementing the drop-down list box.
What should you recommend?
A. Create a stored procedure to retrieve all of the data that is loaded into the list box.
B. Use an API server-side cursor to retrieve the data that is loaded into list box.
C. Retrieve all of the data at once by using a SELECT statement, and then load the data into the list box.
D. Use a Transact-SQL server-side cursor to retrieve the data is loaded into the list box.
Ans: B