PrepAway - Latest Free Exam Questions & Answers

Category: 70-451

Exam 70-451: PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008

Which two actions should you perform?

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database supports a Web site and captures user interactions. These interactions are stored in the Activity table of the User_Activity database. Data older than six months is archived to the Activity table of the Archive_Activity database on a different instance of SQL Server 2008. The structure of the Activity table is as shown in the following table.

You plan to design a solution that allows a single query to generate a report that summarizes user interactions for the last 12 months. You need to ensure that the solution is implemented. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

What should you do?

You are a database developer. You develop solutions by using SQL Server 2008 in an enterprise environment. An application contains two stored procedures. The tasks performed by the stored procedures are as shown in the following table.

You discover that the procedures occasionally throw foreign key violation errors:
* IncludeDetails throws an error when it inserts records into the ProductInfo table.
* ImportNewProducts throws an error when it inserts records into the ProductCurrentPrice table.
You must make sure that the two requirements below can be met:
* If an error occurs in the INSERT statement of ProductInfo, records inserted into ProductHeader and ProductCurrentPrice are committed.
* If an error occurs in the INSERT statement of ProductCurrentPrice, all transactions are rolled back.
What should you do?

what should you do to implement these requirements to maintain data integrity?

You intend to use SQL Server 2008 to create a database solution. You are creating a database to support the office manager. The exhibit below shows the structure of the database.

The database you design must meet the requirements below:
* First, an employee can be given more than one task.
* Second, the task is deleted upon completion and when deleted, the associated assignment is deleted.
* Third, the employee link to the assignment is replaced with a NULL value when an employee is no longer available to complete a task.
So what should you do to implement these requirements to maintain data integrity?

So what should you do?

You intend to use SQL Server 2008 to create a database which will be use by a scheduling application. The following information is stored in this database:
* Rooms
* Classes
* Students
* Teachers
You have to consider the following facts in the design plan:
* Each room can host one or more classes;
* Each class can be in one or more rooms;
* Each teacher can teach one or more classes;
* Each student can register for one or more classes.
Look at the entities below:
* Rooms;
* Classes;
* Students;
* Teachers;
* ClassesTeachers;
* ClassesStudents
You identify these entities for the database design. In order to ensure normalization, you have to design thedatabase. So what should you do?

So what should you specify in the full-text query?

You intend to use SQL Server 2008 to create a database solution. The full-text search component is installed in the database which supports a Web site.

You intend to create a table named Courses which has the structure above. On the basis of the CourseTitle field, users of the Web site will search for courses. When the search is launched for a course by a user, a full-text must be constructed to ensure the compliances below:
* when the exact search phrase is found, rows are returned;
* rows are in order of how well they match with the search phrase.
So what should you specify in the full-text query?

What should you recommend?

You have a table named Books that contains information about books. Books has the columns in the following table.

You plan to create several queries that will filter on Title and ISBN. The queries will return values from Title,ISBN, and Description. You need to recommend an indexing solution to meet the following requirements:
* Minimize the amount of time required to return the results of the planned queries
* Minimize the number of indexes
What should you recommend?

What should you do?

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database contains a table named Claims. The structure of the Claims table is as shown in the following table.

Only two percent of the claims are open at any point in time. You discover that queries on claims that have an Open status take a long time to execute. You need to optimize the performance of the claim-processing queries. What should you do?

What should you do?

You are a database developer. You plan to design a database solution by using SQL Server 2008. A database contains a table named Person. The structure of the table is as shown in the following exhibit.

The table has the following indexes:
* A unique clustered index on the PersonID column named IX_Person_PersonID
* A nonclustered index on the FirstName and LastName columns named IX_Person_FirstName_LastName
* A nonclustered index on the PersonType column named IX_Person_PersonType that has FirstName and LastName as included columns
The table contains approximately 700,000 records. The approximate number of records for each PersonType is 3,000. You execute the following query:

SELECT P.FirstName, P.LastName
FROM Person P
WHERE P.PersonType = ‘DR’

You plan to analyze the performance of the query by using an execution plan. You need to ascertain that the indexes are used optimally. What should you do?

What should you do?

You have a table named Table1. A sample of the data in Table1 is shown in the following table.

There is a defined nonclustered index on the SalesOrderNumber column.The following query executes against the table:

WHERE ‘SO3’ = LEFT(SalesOrderNumber,3)

You need to minimize the amount page I/O that is generated when the query runs. What should you do?


Page 1 of 1012345...10...Last »