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

Demo Question 3.

National Retailers, Scenario COMPANY OVERVIEW Background National Retailers operates as a retailer that specializes in vintage dolls and handmade porcelain dolls as well as action figures. The National Retailers Company has four factories: one each in Chicago, Los Angeles, Miami, and Dallas. Chicago hosts the National Retailers head quarters. Apart from the four factories, National Retailers has sales representatives that works in 50 cities across the United States. These sales representatives telecommute and usually place orders from customers with the closest factory. Salespeople are paid on commission. Commission checks are generated monthly and include commission on all orders paid for in that month. Products and Services on offer National Retailers operates as a retailer that specializes in vintage dolls, handmade porcelain dolls, and action figures. National Retailers sells mostly to department stores, discount stores, and specialty stores. In future National Retailers hope to expand business and thus wants to start selling their exclusive doll line on their Web site. Future Plans Since its inception, National Retailers has grown rapidly from humble beginnings with a single factory in Chicago to a multi-location company that currently has four factories supplying the whole of the United States. The current database instruction is unable to handle the National Retailers requirements. As a result product shipments have been delayed due to stocking levels for popular products not meeting the demand. In the mean time the less popular doll ranges have been stored for too long and some has even been shipped to customers by mistake. To this end National Retailers wants to upgrade its current system to Microsoft SQL Server 2005. They also want to centralize operations to accommodate more effective management of the factories as well as the order fulfillment process. The National Retailers management wants to expand business by selling their exclusive doll line on the company Web site. EXISTING ENVIRONMENT Existing Application and Supporting Environment The National Retailers network consists or two Active Directory forests, namely Corporate and Manufacturing respectively. Each of the factories represents its own domain. Currently all domain controllers are running Microsoft Windows 2000 Server. There are five servers that are used as database servers. All database servers are running Microsoft SQL Server 2000. NRCH. DB02 enjoys membership of the corporate domain. All other database servers belong to the Manufacturing domain. The following table illustrates the current Database Servers and their specifications: The Chicago head quarters have two database administrators and three network administrators. These employees vacillate between the head quarters and the factories when required. At present the inventory application allows users to enter criteria. It also builds Transact-SQL queries dynamically by concatenating the data entered by users. At present the order management application calls stored procedures that accept a Transact-SQL string as the only parameter. Connectivity between office and factories Only the Chicago office is connected to the Internet. The National Retailers factories are not currently connected to the Internet. A server named IS

A. Chicago, in the Chicago office is configured with Internet Security and Acceleration Service (IS. This ISA server separates the internal network from the Internet. The factories are making use of dedicated links to connect to the Chicago office. Each factory is responsible for the management of its own product tracking. They all make use of the same custom application for product tracking. The following table illustrates the current database storage requirements: Regularly at the end of each month, reports are generated manually and e-mailed to the Chicago office. Monthly sales and invoice aging information is included in these reports. The Chicago office handles debt collection. All customers with an outstanding balance over 60 days are also sent to the Chicago office. The accounting department makes use of a collections application that imports XML data. The ProductLine and ProductLineDetails tables contain information about each product and the parts and components it contains. The ProductLineDetails table contains a foreign key constraint that references the Parts and Components table. The Parts and Components table contains a foreign key constraint that references the Suppliers table. The Orders and OrderDetails tables contain information about each order. The Orders table has a foreign key constraint that references the Customers table. The OrderDetails table has a foreign key constraint that references the Products table. The Products table is referenced by the ProductLine table's foreign key constraint. Envisaged Environment The National Retailers management wants all database servers to be upgraded to Microsoft SQL Server 2005. The future online Sales application will be outsourced. NRCH. DB02 will host a database to support the e-commerce application. The e-commerce application will make use of Simple Object Access Protocol (SOAP) to retrieve product information and submit orders. Consolidation of data and revision of the existing applications will be done when required. The factories will not be provided with Internet access. BUSINESS REQUIREMENTS An annual growth rate of 10% per annum is envisaged for the next five years. Accessibility of data: Accounting data All accounts data, i.e. accounts receivable and payable, must remain immediately accessible for a period of six months after the end of the fiscal year in which the invoice was paid. After this period, the same data must be stored for another seven year period. Usually this data will then hardly be accessed if ever. Information about customer payments and invoices must be available in the corporate office within 24 hours of the order shipping. Manufacturing data Manufacturing data and product quality data must be accessible for a five year period. In reality however, data older than 6 months is rarely accessed. Sales data National Retailers Sales Representatives must continue to be allowed to place orders via the nearest factory. In the event of a factory unable to fulfill the order, product inventory at other factories should be checked and an e-mail order sent to the manager of the factory that has the product in stock. The manager will then verify inventory and submit the order locally. Sales Representatives should also be allowed to check the current commissions due to them. National Retailers wants its sales representatives to be able to place orders on a Web site. In the event of a salesperson attempts to place an order for a customer who has a balance over 60 days past due, an e-mail should be sent to the salesperson and the customer, informing them about order rejection. Product data Industry regulations require an audit trail for all components and parts that go into a doll or action figure. The audit trail must be able to use the UPC number on the package to trace all the components and parts, who supplied the components and parts, and what date the components and parts were shipped. Components and parts information must be entered at the time the product line is created and cannot be altered after the quality inspection. Attempts to modify components and parts information after the inspection time must be logged. Only product lines with an intact audit trail can be shipped. If an audit trail is not available, the product line must be destroyed. The product line application includes a verification routine that checks a specific product line's audit trail. The application makes use of structured error handling to react to a product line that must be destroyed. A product line takes two days to complete. All components and parts are collected during the first half a day. A custom application automatically logs the components and parts information to the database when components and parts are collected. The Chicago factory can produce two lines at once. They are synchronized so that the second line is always started one day after the first line. TECHNICAL REQUIREMENTS Online customers have to be catered for. Thus credit card numbers must be encrypted in the database and when sent across the Internet. Availability: The tables related to tracking a product line must be available as quickly as possible if the system fails during the factory's hours of operation. The accounting and sales tables do not require quick restoration. The tables related to tracking a product line must support point-in-time recovery. In the event of a factory's database server down during business hours, the inventory and order management application must be able to use one of the other factories' database servers until the local server is recovered. Topic 1, National Retailers (11 Questions) You work as a contract developer for National Retailers. You are currently working on the online order application the National Retailers database developer has informed you that the product parts and components will be stored in a xml data type column. National Retailers employees are allowed to request product information regarding a doll or action figure line by clicking a button on the Web form. The data will only be retrieved if the employee requests details. You need to design the appropriate data access technologies and must thus choose the object that you need to use to store the query results for product parts and component information. What should you do? Make use of a DataSet object.
B. Make use of an XmlElement object.
C. Make use of an XmlDocument object.
D. Make use of an XmlDocumentFragment object.


Display Answer

Answer: D

Explanation: An XmlDocumentFragment object can store either a well-formed XML document or a fragment of a document. This would be consistent with the xml data type which has the ability to store well-formed documents as well as fragments. 1. The National Retailers management wants to expand business by selling their exclusive doll line on the company Web site Incorrect