70-441 Designing Database Solutions by Using Microsoft SQL Server 2005 Practice Exam

Demo Question 1.

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 8 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. The domain controller for the network is named SF. DC01 and is located in New York. Each office currently maintains its own database for investors and investments in a single Microsoft Access table. Each office is also has its own Accounting Department that is responsible for auditing and billing at that office. The accounting application can export data from the Microsoft Access table as a comma-delimited file. The file has the following format: InvestorName, CreditCard, ExpireDate, Commission, AdminCharges, Total Account administration charges are calculated manually at the end of each month while commission is calculated manually when the customer cashes out his or her investments. Stanford Finance maintains its own Web site that runs on a Microsoft Windows Server 2003 Web server named SF. SR03. Internet Information Service (IIS) 6.0 is running on SF. SR03. The company plans to add a SQL Server 2005 database server named SF. DB04 to host the investment tracking database. 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. Investors must specify the type of investment they want when applying online. If the type of investment they want is not available, the other types of investments that are available should be displayed. Investors must also be able to search for investments by city. 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 an investor provides his or her credit card number, the credit card number must be verified using a third-party Component Object Model (COM) component. The guest should be sent an e-mail when the credit card is verified or declined. The administrator at each office must be able to continue to access investment data for the local investors. 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 manager of the Investment departments can change commission rates, but only for the office they work at. All rate changes must be logged to a separate database table. The before rate, after rate, the manager's name, and the reason for the price change must be logged. When an investor makes an investment, they need to leave their emergency contact information and beneficiary name. They must also make a deposit by check or by cash. Monthly administration fees are charged against their credit card. Investors must be able to request a tax invoice up to 90 days after the cash out. After that time the detailed information must still be archived for accounting purposes. 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. Reporting The Board of Directors wants a monthly report that details the performance of the investments at each office. The report must include information about the customer satisfaction level based on the responses to the online system. TECHNICAL REQUIREMENTS: Security Credit card data must be encrypted at all times and the online investment system must be protected against SQL Injection attacks. Availability Communication between offices will take place via a virtual private network (VPN). In the event of a WAN link failure, investment, investment auditing, and accounting information must still be available at each office. Reporting Information from the online investment satisfaction questionnaire must be copied to the database server in the New York office for monthly reporting. Maintenance The online investment system will be deployed for the New York office first. After a month period, the online investment system will be extended to Boston, Chicago, and Atlanta. The online system will be extended to London and Manchester a moth later and to Lisbon and Madrid a month after that. System performance must be monitored at each phase to determine whether additional resources are required. The administrator at each office will be responsible for ensuring that the data from existing systems is imported to the SQL Server 2005 database on the first day of each month. Each office should store information about their local investors, their investment portfolios, and their investment history locally. The IT director has mandated that data must be normalized to the third normal form (3NF. . Topic 3, Stanford Finance (13 Questions) You need to design the table that will store the investor's responses to the online investment satisfaction questionnaire. You need to ensure that the table meets Stanford Finance's reporting requirements. What table should you implement?

A. SurveyResponse1.
B. SurveyResponse2.
C. SurveyResponse3.
D. SurveyResponse4.


Display Answer

Answer: A

Explanation: The SurveyResponse1 table meets the reporting requirements as it allows the Board of Directors details of the performance of the investments at each office. Incorrect