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?

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?

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?

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?

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database will contain three tables. The structure of the three tables is as shown in the following table.

You need to minimize disk space usage without altering the data types in the tables of the database. What should you do?

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?

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?

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?