QUESTION 1:

Use the following descriptions of the operations of RC_Models Company to complete this exercise.

Customers use the Web site to select the products and to pay by credit card. If a product is not currently available, it is placed on back order at the customer's discretion. (Back orders are not charged to a customer until the order is shipped.) When a customer completes a transaction, the invoice is printed and the products listed on the invoice are pulled from inventory for shipment. (The invoice includes a shipping charge.) The printed invoice is enclosed in the shipping container. The customer credit card charges are transmitted to the CC Bank, at which RC_Models Company maintains a commercial account. (Note: The CC Bank is not part of the RC_Models database.)

RC_Models Company tracks customer purchases and periodically sends out promotional materials. Because the management at RC_Models Company requires detailed information to conduct its operations, numerous reports are available. Those reports include, but are not limited to, customer purchases by product category and amount, product turnover, and revenues by product and customer. If a product has not recorded a sale within four weeks of being stocked, it is removed from inventory and scrapped.

Many of the customers on the RC_Models customer list have bought RC_Models products. However, RC_Models Company also has purchased a copy of the FineScale Modeler magazine subscription list to use in marketing its products to customers who have not yet bought from RC_Models Company. In addition, customer data are recorded when potential customers request product information.

RC_Models Company orders its products directly from the manufacturers. For example, the plastic models are ordered from Tamiya, Academy, Revell/Monogram, and others. Decals are ordered from Aeromaster, Tauro, WaterMark, and others. (Note: Not all manufacturers in the RC_Models Company database have received orders.) All orders are placed via the manufacturers' Web sites, and the order amounts are automatically handled through RC_Models' commercial bank account with the CC Bank. Orders are automatically placed when product inventory reaches the specified minimum quantity on hand. (The number of product units ordered depends on the minimum order quantity specified for each product.)

TASK

a. Given the brief description of RC_Models Company operations, write all applicable business rules for instance
A customer may generate many invoices.
Each invoice is generated by only one customer.
Some customers have not (yet) generated an invoice

b. Identify all entities, relationships, optionalities, connectivities, and cardinalities. (Hint: Use the identified business rules to implement your ERD in the same format.)

c. Draw the fully labeled and implementable Entity Relationship Diagram (ERD based on the business rules you wrote in Part (a) of this problem. Include all entities, relationships, optionalities, connectivities, and cardinalities.

d. Draw ta fully labeled and implementable Class Diagram (UML) for the entire system.

QUESTION 2.

1. The following data structure and constraints exist for a magazine publishing company:

a. The company publishes one regional magazine in each region: Florida (FL), South Carolina (SC), Georgia (GA), and Tennessee (TN).

b. The company has 300,000 customers (subscribers) distributed throughout the four states listed in Part a.

c. On the first of each month, an annual subscription INVOICE is printed and sent to each customer whose subscription is due for renewal. The INVOICE entity contains a REGION attribute to indicate the state (FL,SC, GA, TN) in which the customer resides:

CUSTOMER (CUS_NUM, CUS_NAME, CUS_ADDRESS, CUS_CITY, CUS_ZIP, CUS_SUBSDATE)

INVOICE (INV_NUM, INV_REGION, CUS_NUM, INV_DATE, INV_TOTAL)

The company's management is aware of the problems associated with centralized management and has decided to decentralize management of the subscriptions into the company's four regional subsidiaries. Each subscription site will handle its own customer and invoice data. The management at company headquarters, however, will have access to customer and invoice data to generate annual reports and to issue ad hoc queries such as:

. List all current customers by region.
. List all new customers by region.
. Report all invoices by customer and by region.

TASK

Given those requirements, how must you partition the database?

2. Given the scenario and the requirements in Question 2, answer the following questions:

a. What recommendations will you make regarding the type and characteristics of the required database system?

b. What type of data fragmentation is needed for each table?

c. What criteria must be used to partition each database?

d. Design the database fragments. Show an example with node names, location, fragment names, attribute names, and demonstration data.

e. What type of distributed database operations must be supported at each remote site?

f. What type of distributed database operations must be supported at the headquarters site?

OBJECTIVE

. To test students' capabilities of understanding on Database Systems and differentiate an ERD from UML.

. Students should be able to apply advanced Database Concepts such as distributed database systems in real life situations.

EXPECTED OUTCOME

. Students are expected to follow the given instructions and derive solutions, that is to produce error-free queries, that are to be executed and produce required output/function

. Students are expected to come up with proper diagrams showing a distributed database system.