MIS 4200 Homework 1 Problem 1: Salvation Army (40 points) The local Salvation Army currently has a database system that keeps track of donors,...

Problem 1: Salvation Army (40 points)
The local Salvation Army currently has a database system that keeps track of donors, donations (each donation will get a receipt), and donated items. They have asked you to evaluate their system and make modifications. The data model of the current system is provided in Figure 1. A sample receipt is also provided (see Figure 2).

Figure 1: Data model for current system

The Salvation Army provides a receipt for each donation. The items received are always a little different, so items have always been treated as though they are all different. The Salvation Army now wants to change its design to maintain information about groups of items. Donated items can be categorized into groups such as "clothes," "books," "furniture," "videos," "appliances," etc. The estimated age, price and current value are always different, however, depending on the actual item donated.

The Salvation Army wants a robust database that will keep track of donors, donations1, items, and groups. They have asked you to modify their existing data model to incorporate the idea of groups and make any other changes you think would improve the model (i.e., any missing data, anything ambiguous about the current model).

Deliverable:

Recommended data model for new conceptualization. Your model should be able to capture everything in the sample receipt and required changes the Salvation Army wishes to make. Please list any assumptions you make.

Figure 2: Sample receipt

Problem 2: Campus Magazine Subscription Agency Data Model (60 points)

The Campus Magazine Subscription Agency (CMSA) distributes order forms offering reduced rates on magazine subscriptions for students, faculty, and staff at universities and colleges. For each magazine, CMSA stores the item ID, magazine title, number of issues/year, and the price. When CMSA receives a subscription order form, all customer data must be recorded, including name, university affiliation, address, state, city, zip code, and phone number. Order forms are distributed at different campuses by local representatives. Representatives are given sets of order forms with two codes, one indicating the unique form number and the other showing the representative’s personal code (to enable CMSA to track rep success). Each set is placed in a unique location (e.g., Student Union at EIU). The rep informs the agency which form numbers are placed in which location. Each rep typically use multiple locations to increase subscription. When reps change, new forms are issued, but previous reps still receive commissions for any orders that come in on their forms. Each campus has only one rep at a time. Information stored for each rep include personal code, name, phone number, the start and end dates for their appointment at CMSA.

When orders are received, CMSA knows who posted the order form and is entitled to the commission (i.e., commission=order total*commission rate). In addition, they provide reports back to the representatives showing which locations resulted in the most sales. The location information is important because if some locations are not working well, new locations can be tried. The system also includes some basic demographic data about each campus, including number of students. They also know whether it is a community college or university. This allows them to produce reports comparing sales per 1,000 students across different campuses and campus types.

To simplify the situation a bit, you can ignore all taxes and assume that all subscriptions are paid via credit card. All orders are new subscriptions, not renewals, but customers can place more than one order and orders can be for more than one magazine. You do not have to include data on receipt of payment from the credit card company, when subscription orders were sent to the magazine publishers, or when they were paid and how much commission (in dollars) to reps, or anything else not explicitly mentioned either here. Build a data model that matches the description given here.

Deliverable: Build a data model that matches the description given here.

Stated assumptions: State your assumptions about the following (and how your model accommodates your assumption):

1. Are all sales representatives paid at the same commission rate?

2. Do all order forms get recorded in your system, or only those on which an order was placed?

3. Are all subscriptions placed on the same order form charged to the same credit card?

4. Are all subscriptions placed on the same order form for the same duration (i.e., do they all have the same start date and end date)?