70-442 Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 Practice Exams

Demo Question 2.

Stanford Finance, Scenario BACKGROUND. Company overview Stanford Finance is a private company that specializes in the provision of investment and asset management services for its clients. Stanford Finance has ten offices located in New York, Boston, Chicago, Atlanta, London, Manchester, Madrid, and Lisbon. Each office is currently responsible for maintaining its own customers. Stanford Finance offers its customers a number of investment services, including investment in real estate, equities, and private share listing. Planed Changes The company wants to create an online investment tracking system that will centralize investment portfolios at the eight offices. The company also wants to implement automated investment through its public Web site. EXISTING ENVIRONMENT: The Stanford Finance network consists of a single Windows Server 2003 Active Directory domain that is protected by a firewall. The domain controller for the network is named SF. DC01 and is located behind the firewall in New York. The network also contains a SQL Server 2005 database server named SF. DB04 that is protected by the firewall. SF. DB04 hosts two databases named SF_Accounts and SF_Personnel. Both Microsoft Windows and Microsoft SQL Server authentication is configured on SF. DB04. Each employee who needs access to SF. DB04 has a login. Guest access is to the online investment system is allowed through an application role. Three database roles named Managers, Consultants, and Posters have also been created. BUSINESS REQUIREMENTS: General Requirements Each office offers four different kinds of investments: real estate, equities, private share listing, and derivatives. Each type of investment can be either fixed deposit or monthly deposit fixed term. Prospective investors must be able to search for investment opportunities by city based on type of investment, minimum required deposit, and other features. Prospective investors must be able to enter a word or phrase and SQL Server should return a match of any tense of that word. Investors must provide a credit card, address, e-mail, and phone number when applying online. This information must be kept confidential at all times. Only employees in the Stanford Finance Accounting department should have access to the investors' credit card information. Also, when a potential investor makes an online investment, the investment opportunity must be reserved immediately so that it is not available to other potential investors. If the investor provides his or her credit card number, the credit card must be verified using a third-party Component Object Model (COM) component. If the investor's credit card is declined, the investor should be sent an e-mail and the investment must be rolled back. Investment consultants at an office must also be able to access the investment database for that office from a Web application. They must have up-to-date information about investment availability whenever they query for availability. Commission on investments differs from one city to the other and the company often offers special rates at specific offices. When an investor cashes out his or her investment, the commission must be based on the initial commission charged when the investor signed up. Only the office manager can change commission rates, but only for the office they manage. When an investor cashes out, they can pay commission by cash, check, or credit card. Commission should not be deducted from the investment pay out. If they cash out online, they must pay by credit card. The online system should provide five questions about investor satisfaction but investors can choose not to respond to these questions. The investor's response to the questions must be saved to the database. TECHNICAL REQUIREMENTS: Application Development The Stanford Finance wants to use Visual Studio 2005 and ASP.NET 2.0 to develop Web applications for the company. Performance Investment consultants must be able to determine the availability of an investment opportunity using as few round trips to the server as possible. When an investor provides his or her credit card number, the credit card must be verified and authorized using a third-party Component Object Model (COM) component. When the credit card authorization system is busy or the lines are down, the investor should not be required to wait while their credit card is authorized. They should be sent an e-mail when the credit card is verified or declined. Security SF. DB04 must be protected against SQL Injection attacks. Topic 2, Stanford Finance (14 Questions) You need to design the procedure that will reserve investments. The procedure must be used by both Web pages that allow potential investors to invest online and Web pages that allow investment consultants to secure investments for their customers. You need to ensure that your solution meets Stanford Finance's technical and business requirements. What should you do?

A. Start a transaction. Decrement the investment opportunity availability. Add the investment reservation. Call the credit card authorization component. Commit or rollback the transaction.
B. Start a transaction. Add the investment reservation. Decrement the investment opportunity availability. Send a credit card authorization message to a Service Broker queue. Commit the transaction. Write a queue reader that reverses the changes if credit card authorization fails.
C. Decrement the investment opportunity availability. Start a transaction. Add the investment reservation. Call the credit card authorization component. Commit or rollback the transaction.
D. Send a credit card authorization message to a Service Broker queue. Write a queue reader that starts a transaction. Decrement the investment opportunity availability. Add the investment reservation. Commit or rollback the transaction.


Display Answer

Answer: B

Explanation: You need to reserve the investment without waiting for credit card authorization. Therefore, you should use a Service Broker queue to allow the transaction to be committed immediately while a separate asynchronous process verifies the credit card data. Incorrect