PrepAway - Latest Free Exam Questions & Answers

Category: 70-451 (v.1)

Exam 70-451: PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008 (update October 14th, 2015)

Which two SQL Server components should you use?

You are a database solutions architect. Your company plans to develop a solution by using
a SQL Server 2008 instance. The solution has the following business requirements: ·Import
data from various data sources such as Microsoft Office Excel, Microsoft SQL Server 2000,
Microsoft SQL Server 2005, and CSV files. ·Profile the source data before it is imported.
·Provide collaboration and offline capabilities to mobile users. ·Allow mobile users to use
heterogeneous data stores. You need to configure the appropriate SQL Server components
to accomplish the business requirements. You want to achieve this goal by using the
minimum amount of administrative effort. Which two SQL Server components should you
use? (Each correct answer presents part of the solution. Choose two.)

You need to ensure that the table meets the following requirements…?

You have a table in a database that contains 30 million rows. You need to ensure that the
table meets the following requirements: ·Enables queries on all string values within the table
·Enables queries to be returned by order of proximity ·Minimizes the amount of time
required to execute queries What should you do?

You need to design the database to ensure normalization

You are a database developer. You plan to create a database by using SQL Server 2008.

The database will store information about students, teachers, classes, and rooms in a
school. The database will be used by a scheduling application. In the design plan, the
following facts have to be considered: ·Each teacher can teach one or more classes. ·Each
student can register for one or more classes. ·Each class can be in one or more rooms.
·Each room can host one or more classes. You identify the following entities for the
database design: ·Students ·Teachers ·Classes ·Rooms ·ClassesStudents
·ClassesTeachers You need to design the database to ensure normalization. What should
you do?

You need to implement the business requirements to maintain data integrity

You are a database developer. You plan to design a database solution by using SQL Server
2008. You are creating a database to support the office manager. Your database model has
the following structure. The database design has the following business requirements: ·An
employee can be assigned more than one task. ·Upon completion, the task is deleted.
·When a task is deleted, the associated assignment is deleted. ·When an employee is no
longer available to complete a task, the employee link to the assignment is replaced with a
NULL value. You need to implement the business requirements to maintain data integrity.
What should you do?

You need to ensure that when an item is removed from Table1, all related items are removed from Table1_Details

You have a database that contains two tables named Table1 and Table1_Details.
Table1_Details contains details about items in Table1. You need to ensure that when an
item is removed from Table1, all related items are removed from Table1_Details. You must
achieve this goal by using the minimum amount of Transact-SQL code. What should you
do?

You need to ensure that the reports will not affect query performance on the OLTP database

You are designing a database for a reporting solution that is based on data from an Online
Transaction Processing (OLTP) database. The reports will contain aggregated data. You
need to ensure that the reports will not affect query performance on the OLTP database.
The solution must minimize the use of joins when performing the aggregate calculations.
What should you do?

You need to optimize the performance of the application to meet the following requirement…

You have a legacy application. You do not have access to the application source code. The
application has a large denormalized table that contains 100 columns. The application uses
stored procedures and views to perform all data manipulation language (DML) activities on
the table. You need to optimize the performance of the application to meet the following
requirement: ·Reduce I/O ·Minimize the storage requirements ·Optimize insert, update, and
delete operations What should you do?

You need to design a data access layer to simplify the extraction process

You are a database developer. You develop a database application for a SQL Server 2008
instance. The instance hosts a third-party database. You are not allowed to modify the
database schema. The database contains two tables that are as shown in the following
diagram. You plan to extract address information about full-time employees based on the
FullTimeIndicator flag. You need to design a data access layer to simplify the extraction
process. What should you do?


Page 1 of 1412345...10...Last »