70-545 Q & A / Study Guide

Best Microsoft MCTS Certification, Microsoft MCITP Training at certkingdom.com

---

QUESTION 1
You are employed as a database developer at Certkingdom.com. You make use of SQL Server 2008 to
develop database strategies.
You are in the process of developing a strategy that has three tables named Certkingdom1, Certkingdom2, and
Certkingdom3. The Certkingdom1 and Certkingdom2 have the integer and varchar data type column types configured
respectively. Certkingdom3 has both the integer and varchar data types configured as column types.
You have configured rowlevel compression on Certkingdom1, and page-level compression on Certkingdom2 and
Certkingdom3.
Which of the following describes the reason for this configuration?

A. It allows for the reduction of disk space usage, while modifying the data types in the tables of the database
B. It allows for the reduction of disk space usage without modifying the data types in the tables of the database
C. It allows for the increase of disk space usage, while modifying the data types in the tables of the database
D. It allows for the increase of disk space usage, while modifying the data types in the tables of the database

Answer: B

Explanation:

---

QUESTION 2
You are employed as a database developer at Certkingdom.com. You make use of SQL Server 2008 to
develop database strategies.
You have received instructions to design a database strategy that includes a table which hosts
data imported from an outside source. This data includes a field named TransactionTime that
should be configured to make use of hh:mm:ss[.n] format.
You have been informed that the data type selected for the TransactionTime field must allow for
storage to be kept to a minimum.
You, therefore, make use of the time(1) data type.
Which of the following is TRUE with regards to the time data type?

A. The default fractional precision is 7 (100ns).
B. The default fractional precision is 3 (100ns).
C. The time produced by the time data type is not time zone aware and is based on a 24-hour clock.
D. The time produced by the time data type is time zone aware and is based on a 12-hour clock.

Answer: A,D

Explanation:

---

QUESTION 3
You are employed as a database developer at Certkingdom.com. You make use of SQL Server 2008 to
develop database strategies.
You have received instructions to design a strategy that has two tables named CertkingdomVendor and
CertkingdomItem. You have configured a foreign key constraint between the CertkingdomVendor and CertkingdomItem
tables on the CertkingdomVendorID column.
You have configured the CertkingdomVendor table to display a 0 value for the CertkingdomVendorID when a
vendor is removed. You also want make sure that the CertkingdomVendorID value in the CertkingdomItem table is
set to 0 when a vendor is removed.
You then create a default constraint on the CertkingdomVendorID column in the CertkingdomItem table, which is
used to set the value to 0.
Which of the following actions should you take NEXT?

A. You should consider setting the ON DELETE property of the foreign key constraint to Null.
B. You should consider setting the ON DELETE property of the foreign key constraint to Default.
C. You should consider setting the ON DELETE property of the foreign key constraint to Cascade.
D. You should consider setting the ON DELETE property of the foreign key constraint to No
Action.

Answer: B

Explanation:

---

QUESTION 4
You are employed as a database developer at Certkingdom.com. You make use of a SQL Server 2008
instance to develop database strategies.
You are in the process of designing a database strategy that makes use of the Integration
Services and Microsoft Sync Framework SQL Server components.
Which of the following is TRUE with regards to the use of these components?

A. Microsoft Sync Framework allows for collaboration and offline access for applications, services, and devices.
B. Microsoft Sync Framework prevents collaboration and offline access for applications, services, and devices.
C. Integration Services allows for the merging of data from Heterogeneous Data Stores.
D. Integration Services prevents the merging of data from Heterogeneous Data Stores.

Answer: A,C

Explanation:

---

QUESTION 5
You are employed as a database developer at Certkingdom.com. You make use of SQL Server 2008 to
develop database strategies.
You have received instructions to design a strategy that helps Certkingdom.com’s administration manager.
You have created three entities named User, UserTask, and UserAssignment. You have
configured the User entity to make use of the UserID attribute, the UserTask entity to make use of
the UserTaskID attribute, and the UserAssignment entity to make use of the UserAssignmentID attribute.
You have been informed that the strategy must allow for users to be assigned multiple tasks. You
also need to ensure that a task is deleted when it has been completed, and that the assignment
linked to that task is also deleted. Furthermore, a NULL value has to replace the user reference to
the user assignment as soon as a user becomes unavailable to finish a task.
Which combination of the following actions should you take? (Choose all that apply.)

A. You should consider configuring Foreign Key constraints on the UserTaskID and UserID
attributes in the UserAssignment entity.
B. You should consider configuring Foreign Key constraints on the UserTaskID and UserID
attributes in the UserTask and User entities respectively.
C. You should consider referencing the UserTask and User entities respectively.
D. You should consider referencing the UserAssignment and User entities respectively.
E. You should consider specifying the On Delete property as NULL.
F. You should consider specifying the On Delete property as Default.

Answer: A,C,E

Explanation:

Best Microsoft MCTS Certification, Microsoft MCITP Training at certkingdom.com

70-451 Q & A / Study Guide

Best Microsoft MCTS Certification, Microsoft MCITP Training at certkingdom.com

---

QUESTION 1
You work as a database developer at Certkingdom.com. You need to plan a SQL Server 2008 database
that will be accessed by mobile users.
The database must be able to profile data before importing it from heterogeneous data sources,
including Microsoft Office Excel, Microsoft SQL Server 2000, Microsoft SQL Server 2005, and
CSV files. In addition, Certkingdom.com's mobile users must have collaboration and offline capabilities
and they must be able to use heterogeneous data stores.
How should you plan your database if you want your design to use the least amount of
administrative effort?

A. You should make use of the Analysis Services and the Notification Services.
B. You should make use of the SQL Server Agent.
C. You should make use of the Integration Services and the Microsoft Sync Framework.
D. You should make use of the Service Broker and SQL Mail.

Answer: C

---

QUESTION 2
Certkingdom.com has hired you to design a SQL Server 2008 database for its online retail application. The
database must be able to run both Transact-SQL statements as well as SQL Server Integration
Services (SSIS) packages. Certkingdom.com plans on running scheduled maintenance tasks on the
database and they want the database to send alerts and notifications to the network
administrators.
How should you design this database?

A. You should make use of the Analysis Services and the Notification Services.
B. You should make use of the Reporting Services.
C. You should make use of SQL Server Agent.
D. You should make use of the Service Broker and the Notification Services.

Answer: C

---

QUESTION 3
Certkingdom.com hires you as their database administrator of their SQL Server 2008 database
infrastructure. You need to optimize a very large database table for query execution against string
data. The database contains several million rows of data. You need to ensure that the queries are
performed in order of proximity and are completed in the least amount of time possible.
How should you configure the database?

A. You should create a partitioned view on the table.
B. You should create a nonclustered index on the table.
C. You should make use of the Analysis Services.
D. You should enable Full-Text-Search.

Answer: D

---

QUESTION 4
You work as a database developer at Certkingdom.com. You need to design a SQL Server 2008 database
named Sales. The Sales database will have tables named Customers with an identity column
named CustomerID, Products with and identity column named ProductID, SalesReps with an
identity column named RepID, Orders with an identity column named OrderID, and Invoices with
an identity column named InvoiceID.
Sales representatives are assigned to specific customers with each Sales Representative being
assigned to more than one customer.
You need to ensure that the database is normalized and that it represents the relationship
between the Sales Representatives and the customers.
How should you design your database?

A. You should create a foreign key constraint between the SalesReps and Customers tables.
B. You should make use of the hierarchyid data type on the SalesReps table.
C. You should add a SalesRep2Customer table with foreign key constraints to the SalesReps and
Customers tables.
D. You should make use of a table-valued function on the SalesReps table.
E. You should make use of a view based on the SalesReps and Customers tables.

Answer: C

Explanation:

---

QUESTION 5
Certkingdom.com has hired you as their database administrator of their SQL Server 2008 database
infrastructure. Certkingdom.com has a Sales database that contains tables named Orders, OrderDetails,
and Inventory. The OrderDetails table stores details for Orders table.
You need to ensure that when rows on the Orders table are moved to an archive and deleted from
the Orders table, the related detail in the OrdersDetails table is also deleted.
How would you accomplish this using the least lines of Transact-SQL code as possible?

A. You should create a filtered index on the OrderDetails table.
B. You should create a foreign key relationship between the Orders and OrderDetails tables.
C. You should create a stored procedure that deletes the related data on the OrderDetails table.
D. You should create a trigger on the Orders table that executes the stored procedure.
E. You should set Cascade Delete to False.
F. You should make use of the Service Broker.
G. You should set Cascade Delete to True.

Answer: B,G

---

Best Microsoft MCTS Certification, Microsoft MCITP Training at certkingdom.com

Microsoft issues release candidate of System Center 2012

Microsoft on Tuesday said the release candidate of System Center 2011, software that companies use to manage their cloud services, is now available.

Best CCIE Training and CCIE Exams and more Cisco exams log in to Certkingdom.com

Customers are already managing more than 100,000 virtual machines using the software as part of an early access program, Satya Nadella, president of Microsoft's Server and Tools Business, said during a video conference about the news.

Using System Center, an organization can manage its internal private cloud as well as applications and services it is running in public clouds. This hybrid approach -- where a company may run an application in a public cloud but retain the corresponding database on a private cloud, for example -- is increasingly common.

System Center 2012 combines eight former products into one and addresses one common complaint customers have, said Brad Anderson, corporate vice president of Microsoft's Management and Security Division. "We hear a lot that licensing is complex," he said. System Center 2012 aims to simplify licensing by combining several former products into one and by offering just two different versions.

In addition, Microsoft bases its license on a slightly different model than its competitors, he said. Customers pay for System Center 2012 on a per-server basis, rather than a per-virtual machine basis. That can keep costs down for people who are running many virtual machines per server.

During the video conference about the release, Microsoft showcased a few companies that have already been using System Center. They include Luftansa, T. Rowe Price and Unilver. Those companies said that System Center 2012 has let them quickly expand their infrastructure and automate the management of their systems.

Microsoft says that using a tool like System Center can help organizations more efficiently manage their cloud environments. The company typically hears from customers who have full time IT administrators managing 30 to 40 servers. But Microsoft itself, which operates huge data centers to run services like Bing, Azure and Hotmail, finds that each of its IT workers, using System Center 2012, manages as many as 5,000 servers.

System Center 2012 has also expanded to allow IT administrators to manage non-Microsoft devices, including mobile phones. Rather than thinking of devices as being at the center of the management process, System Center 2012 puts the user in the middle, said Anderson. That means a manager can set policies for an employee that applies no matter what device the employee is using, including Android phones, iPhones or a variety of tablets.

The release candidate of System Center 2012 is available immediately. Provided users don't find any major bugs, the next step should be general availability of the software.