인테넷에 검색하면 Microsoft 70-451 (PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008 ) 시험덤프공부자료가 헤아릴수 없을 정도로 많이 검색됩니다. 그중에서Pass4Test의  Microsoft 70-451 (PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008 ) 제품이 인지도가 가장 높고 가장 안전하게 시험을 패스하도록 지름길이 되어드릴수 있습니다.

 

 

NO.1 You have a SQL Server Integration Services (SSIS) package that contains an Execute Process task.

You need to schedule the SSIS package to run on a regular basis.

What should you do?

A. Create a credential and a login. Configure a SQL Server Agent job to run the package by using the

login.

B. Create a credential and a proxy. Configure a SQL Server Agent job to run the package by using the

proxy.

C. Create a login and map a user to the login. Add the user to the db_owner role. Configure a SQL Server

Agent job to run the package by using the login.

D. Create a login and map the user to a login. Add the user to the db_securityadmin role. Configure a SQL

Server Agent job to run the package by using the login.

Answer: B

 

NO.2 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?

A. Create a foreign key relationship. Set Cascade Delete to Null.

B. Create a foreign key relationship. Set Cascade Delete to True.

C. Create a trigger on Table1_Details that fires on the Delete action.

D. Create a stored procedure that deletes all related items from Table1_Details.

Answer: B

 

NO.3 You are a database developer. You plan to design a database solution by using SQL Server 2008. You

create a table that contains information about Web pages that are added to a Web site. The Web site has

a home page and contains various other Web pages. The home page is the root page of the site. All

pages except the root page have a link to an upper-level page.

The table must support the following design considerations:

Records of the Web pages that are linked to a particular page can be quickly retrieved.

The position of a Web page in a collection of linked pages can be quickly retrieved.

Changing the links to the upper-level pages is a rare requirement. You need to ensure that the table is

designed appropriately.

What should you use?

A. Use the XML data type.

B. Use the hierarchyid data type.

C. Use a Parent/Child mechanism that references the same table.

D. Use a Parent/Child mechanism that references one or more additional tables.

Answer: B

 

NO.4 You need to design a database solution that meets the following capabilities:

Executes SQL Server Integration Services (SSIS) packages

Executes Transact-SQL

Schedules tasks

Sends alerts

Which SQL Server component should you use?

A. Notification Services

B. Service Broker

C. SQL Mail

D. SQL Server Agent

Answer: D

 

NO.5 You plan to create a Service Broker solution. The solution will transport data from one queue to

another queue.

You need to identify which message type must be used to transport binary data. The solution must

minimize the amount of data transported.

Which message type should you use?

A. EMPTY

B. NONE

C. VALID_XML WITH SCHEMA COLLECTION

D. WELL_FORMED_XML

Answer: B

 

NO.6 You are designing a database table for a content management system. Users will store images and

videos in the database.You need to ensure that the database can store files that are 20 MB or less.

The solution must minimize the amount of space required to store the data.

Which data type should you use?

A. binary(20)

B. varbinary(20)

C. varbinary(max)

D. XML

Answer: C

 

NO.7 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?

A. Design an Entity Data Model that contains the EMPLOYEES and ADDRESS entities.

B. Create a view on the database to include full-time employees and their address details.

C. Re-design the underlying database model to include employee and address information in one table.

D. Design a conceptual Entity Data Model that contains an entity named EMPLOYEE_ADDRESS. Ensure

that this entity contains information about employees and their addresses.

Answer: D

 

NO.8 You are a database developer. You plan to design a database solution by using SQL Server 2008.

You have a Web site supported by a database that has the full-text search component installed.

You plan to create a table named Courses that will have the following structure.

Users of the Web site will search for courses based on the CourseTitle field. You need to construct a

full-text query that ensures the following compliances when a user launches the search for a course:

Rows are returned when the exact search phrase is found.

Rows are in order of how well they match with the search phrase.

What should you specify in the full-text query?

A. A FREETEXT predicate

B. A CONTAINS predicate

C. A FREETEXTTABLE function

D. A CONTAINSTABLE function

Answer: D

 

NO.9 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?

A. Create DDL INSERT triggers on the Employee, Task, and Assignment entities.

B. Create CHECK constraints on the TaskID and EmployeeID attributes in the Assignment entity.

C. Create Foreign Keys constraints on the TaskID and EmployeeID attributes in the Assignment entity.

D. Create Foreign Keys constraints on the TaskID and EmployeeID attributes in the Task and Employee

entities respectively. Reference the Assignment entity, and specify the appropriate On Delete action.

Answer: C

 

NO.10 You are a database developer. You develop a task management application that connects to a SQL

Server 2008 database named TaskDB.

Users log on to the application by using a SQL Server login. The application contains a module named

Task that assigns tasks to users. Information about these tasks is stored in the Tasks table of the TaskDB

database.

The Tasks table contains multiple columns. These include the CloseDate and EstimatedTime columns.

Users assigned to a database role named User1 can update all task information columns except the

CloseDate and the EstimatedTime columns in the Tasks table.

Administrative users assigned to a database role named Task_Admin can update all task information in

the Tasks table.

You need to design a strategy to meet the security requirements. Which two actions should you perform?

(Each correct answer presents part of the solution. Choose two.)

A. Add the Task_Admin role to the db_accessadmin fixed database role.

B. Grant Update permissions on the Tasks table to the Task_Admin role.

C. Grant Update permissions on the Tasks table to the User1 role for each column except the CloseDate

and EstimatedTime columns.

D. Create an INSTEAD OF trigger on the Tasks Table. Use the Is_Member function to prevent the User1

role from updating the CloseDate and EstimatedTime columns.

Answer: B, C

 

NO.11 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?

A. Create a filtered index.

B. Create a clustered index.

C. Configure Service Broker.

D. Configure a Full-Text-Search.

Answer: D

 

NO.12 You need to provide a developer the ability to create and modify database diagrams from an existing

database by using SQL Server Management Studio. The solution must minimize the amount of

permissions assigned to the developer.

What should you do?

A. Add the developer to the sysadmin role.

B. Add the developer to the db_owner role.

C. Grant the developer the CREATE TABLE permission only.

D. Grant the developer the CREATE SCHEMA permission only.

Answer: B

 

NO.13 You need to create a Service Broker solution.

Which object should you create first?

A. Contract

B. Dialog

C. Message Type

D. Services

Answer: C

 

NO.14 You plan to deploy a new application.

The application will perform the following operations:

Create a new database

Add new logins

Back up the new database

You need to configure a login to support the deployment of the new application. The solution must ensure

that the application uses the most restrictive permissions possible.

What should you do?

A. Add the login to the sysadmin server role.

B. Add the login to the dbcreator and securityadmin server roles.

C. Add the login to the diskadmin and securityadmin server roles. Once the database is created, add a

user to the db_backupoperator database role.

D. Add the login to the diskadmin and serveradmin server roles. Once the database is created, add a user

to the db_backupoperator database role.

Answer: B

 

NO.15 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.)

A. Analysis Services

B. Reporting Services

C. Integration Services

D. Notification Services

E. Microsoft Sync Framework

Answer: C, E

 

NO.16 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?

A. Add a persisted computed column.

B. Create indexed views in the OLTP database.

C. Create partitioned tables in the OLTP database.

D. Create a new denormalized database based on the OLTP database.

Answer: D

 

NO.17 You are designing a maintenance strategy for a database that contains several views. The views will

be assigned custom permissions.

You need to recommend a solution that will allow developers to modify the views without affecting the

views' existing permissions.

What should you recommend?

A. Create a new view.

B. Alter the existing view.

C. Rename the existing view.

D. Drop the existing view and then recreate the view.

Answer: B

 

NO.18 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?

A. Create nonclustered indexes on all columns in the table.

B. Create new stored procedures that use the existing views.

C. Create new views. Perform DML activities against the views.

D. Create smaller tables. Update the views and stored procedures.

Answer: D

 

NO.19 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?

A. 1. Add a new entity named TeachersStudents.

2. Establish a relationship between the Teachers and Students entities by using the TeachersStudents

entity.

B. 1. Add a new entity named ClassesRooms. 2. Establish a relationship between the Classes and

Rooms entities by using the ClassesRooms entity.

C. 1. Add a new entity named TeachersRooms. 2. Establish a relationship between the Teachers and

Rooms entities by using the TeachersRooms entity

D. 1. Create a new entity named StudentsRooms. 2. Establish a relationship between the Students and

Rooms entities by using the StudentsRooms entity.

Answer: B

 

NO.20 You are a database solutions architect. Your company plans to develop a solution by using a SQL

Server 2008 instance.

You design a new database that contains a table to store Microsoft Office documents.

You have the following business requirements:

The documents are part of the database backup.

The snapshots of the database are used.

You need to use an appropriate data type to store the documents.

Which data type should you use?

A. varchar(max)

B. nvarchar(max)

C. varbinary(max)

D. varbinary(max) by using the FILESTREAM attribute

Answer: C