QUESTION 1
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

QUESTION 2
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: BC

QUESTION 3
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

QUESTION 4
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

QUESTION 5
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

QUESTION 6
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

QUESTION 7
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

QUESTION 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

QUESTION 9
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

QUESTION 10
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

http://www.passleader.com/70-451.html

QUESTION 11
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

QUESTION 12
You are designing a data storage solution for a transactional application.You need to ensure that each row in a table records the date and the time that the row was written. The time must be as precise as possible. Which data type should you use?

A.    datetime
B.    datetime2
C.    smalldatetime
D.    timestamp

Answer: B

QUESTION 13
You need to create an application that will represent the relationship between managers and employees. You must achieve this goal by using the minimum amount of tables. What should you do?

A.    Create one table that contains the hierarchyid data type.
B.    Create one table that contains the uniqueidentifer data type.
C.    Create two tables. Establish a foreign key relationship between the tables.
D.    Create two tables. Create a trigger that maintains the relationship between the two tables.

Answer: A

QUESTION 14
You are a database developer. You plan to design a database solution by using SQL Server 2008. The database contains a large table that is infrequently updated. Users execute a query against the table. The query requires the execution of a complex calculation that involves multiple columns for a given row. You discover that the query performance is poor because the query is CPU intensive. You need to reduce the effect of this query on the server. What should you do?

A.    Create a computed column on the table.
B.    Create a persisted computed column on the table.
C.    Create an index on each field used by the calculation.
D.    Create a view on the table that includes the calculation.

Answer: B

QUESTION 15
You are a database developer. You plan to design a database solution by using SQL Server 2008. The database will store multilingual data. The database will contain a table that has 100 million rows. The table will contain 1,000 columns that are based on the nvarchar(max) data type. For each column, only 2 percent of the rows will be populated. You need to design the table to optimize storage space. What should you do?

A.    Use row compression.
B.    Use NTFS file system compression to reduce the disk space used.
C.    Define the columns as sparse columns.
D.    Change the column data types to varchar(max).

Answer: C

QUESTION 16
You are a database developer. You plan to design a database solution by using SQL Server 2008. A table in a database will store large image files (20-50 MB in size). You have the following business requirements:
– The image files are accessible by applications that use Win32 APIs.
– The image files are part of the database backup.
– You need to identify an appropriate strategy to store the image files.
Which strategy should you use?

A.    Use an image data type.
B.    Use the varbinary(max) data type.
C.    Use the varbinary(max) data type along with the FILESTREAM attribute.
D.    Store the image file in a file system. Use a varchar data type to store the file location in the database.

Answer: C

QUESTION 17
You are designing a document repository application that will contain over 100,000 documents. The repository will have the following specifications:
– Documents can be associated to 30 different properties
– Most documents will have less than 10 properties defined
– You need to design a single table for the application.
– The solution must use the minimum the amount of storage space.
What should you include in the design?

A.    an XML data type
B.    nvarchar() null
C.    sparse columns
D.    varchar(max) not null

Answer: C

QUESTION 18
You are a database developer. You plan to design a database solution by using SQL Server 2008. The database contains two tables named Supplier and Product. There is a foreign key constraint between the Supplier and Product tables on the SupplierID column. The Supplier table contains a row that has the SupplierID value as 0. The 0 value indicates that the supplier is deleted. Certain transactions delete the supplier records from the Supplier table. You need to ensure that if a supplier is deleted, the SupplierID value in the Product table is set to 0. What should you do?

A.    Create a FOR DELETE trigger on the Product table that updates the SupplierID value to 0 in the Products table for the deleted supplier.
B.    Create a FOR DELETE trigger on the Supplier table that updates the SupplierID value to 0 in the Products table for the deleted supplier.
C.    Create a default constraint on the SupplierID column in the Product table that sets the value to 0. Set the ON DELETE property of the foreign key constraint to NULL.
D.    Create a default constraint on the SupplierID column in the Product table that sets the value to 0. Set the ON DELETE property of the foreign key constraint to Default.

Answer: D

QUESTION 19
You are a database developer. You create a database that uses SQL Server 2008 in an enterprise environment. You plan to import data from an external source into a table. You need to ensure that the following tasks are accomplished:
– The import is successfully completed even if it encounters rows that fail foreign key constraints.
– The rows that fail the foreign key constraints during import are inserted into a separate table.
What should you do?

A.    Use CHECK constraints.
B.    Use an AFTER trigger.
C.    Use an INSTEAD OF trigger.
D.    Disable the foreign keys during the import process.

Answer: C

QUESTION 20
You are a database developer for a retail application. You create a database by using SQL Server 2008 in a distributed enterprise environment that has multiple servers. The same database is implemented on all the servers. The database contains a table that has a surrogate key. You need to ensure that the following requirements are met:
– The surrogate key is unique across all servers.
– The index on the surrogate key is not fragmented because of INSERT operations.
What should you do?

A.    Use the timestamp data type.
B.    Use the bigint data type. Use the IDENTITY property in the column definition.
C.    Use the uniqueidentifier data type. Use the NEWID() function in a default constraint.
D.    Use the uniqueidentifier data type. Use the NEWSEQUENTIALID() function in a default constraint.

Answer: D

Share Free PassLeader 70-451 Study Materials And Exam Braindumps

http://www.passleader.com/70-451.html