Dbms

Distributed DBMS Case Study 1

Case Study 1: Gold Finch Mail Order Company The Gold Finch Mail Order Company, established in 1938, maintains a mailing list of approximately 50,000 addresses to which it sends fall catalogs. It receives about 30,000 orders a year, with one-third of them arriving in November and December. Located in Frostbite Falls, Minnesota, Gold Finch maintains a relational database containing the tables below. This database is used for numerous applications, including catalog distribution, order entry, order shipment, and inventory. Because all orders are prepaid, Gold Finch does not have any accounts receivable tables in its database. Customer (CustomerID,CustomerName,StreetAddress,City,State,ZipCode) Item (ItemID,ItemName,Description,QuantityOnHand,CatalogPrice) Order (CustomerID,InvoiceNo,InvoiceTotal) InvoiceItem (InvoiceNo,ItemId,NumberOrdered,TotalCostOfNumberOrdered)

Case Study 1: Red Robin Mail Order Company The Red Robin Mail Order Company, located in Freesprite, Arizona, has been in the mail newspaper supplements in major metropolitan areas, and receives approximately 130,000 orders a year, half of which arrive in April and May, the other half which arrive more or less evenly during remaining months. The Red Robin Company maintains several files, including those below that are used for catalog distribution, order entry, order shipment, and inventory in addition to numerous other applications. Like Gold Finch, Red Robin maintains no accounts receivable files. Customer (Customer,Address,City,State,ZipCode) Order (Customer,InvoiceTotal, NumberOfItems, the repeating group: (ItemId,ItemCost,NumberOrdered)) Item (ItemId,QuantityOnHand,CatalogPrice)

Case Study 1 Last year, the Red Robin Mail Order Company bought Gold Finch. It plans to maintain separate operations in Arizona and Minnesota, but wishes to combine order entry operations and move them to New York. Customers will call a toll-free number to place orders. Telephone operators need on-line access to inventory information for both Arizona and Minnesota so they can inform customers about the availability of products. The operators generate shipping invoices, which are electronically transferred to Minnesota and/or Arizona where the orders are filled and shipped within four hours. Customers give operators their credit card numbers and are billed via their credit card billing statements. Because of the large investment in current applications, the current DBMS in Minnesota and the current file system in Arizona will be retained. You have been hired as a consultant to design a distributed DBMS that supports the operations in Arizona and Minnesota, as well as the new order entry function in New York City. More…

Case Study 1, cont. 1.

Which of the following capabilities do the order entry operators in New York need? Needed

Not Needed

Up-to-date results Atomicity of transactions involving multiple sites Coordinated backup and recovery Data location and replication transparency Automatic data translation Automatic data merging

More…

Case Study 1, cont. 2. What data sharing architecture do you recommend?

More…

Case Study 1, cont. 3. Design the external schemas for use in Arizona, Minnesota, and New York.

More…

Case Study 1, cont. 4. What are the principle characteristics of the distributed execution manager? a. No update, single-site update, or multisite update? b. What form of distributed concurrency control is needed? c. What type of commit protocols are needed?

More…

Case Study 1, cont. 5. Define mappings between external schemas and existing databases.

More…

Case Study 1, cont. 6. What other advice would you give the Red Robin Company?