Problem 1: Campus Magazine Subscription Agency Data Model
The Campus Magazine Subscription Agency (CMSA) distributes order forms offering reduced rates on magazine subscriptions for students, faculty, and staff at universities and colleges. When CMSA receives a subscription order form, all customer data must be recorded, including name, university affiliation, address, and phone number. In addition, the company records the requested starting date and length of subscription for each magazine ordered as well as the date the order form was received. Order forms are distributed on various campuses by local representatives; each campus has only one rep. 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., University of Oklahoma, Adams Hall outside computer labs). The rep informs the agency which form numbers are placed in which location. When reps change, new forms are issued, but previous reps still receive commissions for any orders that come in on their forms; it's important to CMSA to keep a history of which rep has worked on which campus during what time periods and to know the name of the current rep for a given campus.
When orders are received, CMSA knows who posted the order form and is entitled to the commission. In addition, they provide reports back to the representatives showing which locations resulted in the most sales. 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.
Assume that no taxes are collected and that everyone supplies a credit card number with each order. 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 rates or payments to reps, or anything else not explicitly mentioned here.
1. Build a data model (ERD) that matches the description given here.
2. For each of the assumptions below, answer the question based on your model and explain how/where your model shows this assumption.
a. Are all sales representatives paid the same commission rate?
b. Are all order forms recorded in your system or only those used to place an order?
c. Are all subscriptions placed on the same order form charged to the same credit card?
d. 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)?
3. Name two threats the data model protects against and describe how it does so.
4. Name two threats the data model does not protect against and suggest potential controls.
Problem 2
FlyAKite is a small manufacturing company that manufactures specialty kites. They manufacture and sell 200 kites of different sizes and shapes. They have several wholesale customers that order kites from them and FlyAKite needs to keep track of these orders. In particular, they need to know who ordered, when, how many kites of each type, and the delivery type (e.g., ground shipping, overnight). Ina Cloud, the owner of the company, allows her customers to customize the patterns on the kites. However, this customization adds an additional 10% to the price of the kite. It is very important to know how many kites of each type the company has available on hand so that they manage customer expectations at the time of the order.
To increase profits, FlyAKite also wants to keep better track of their spending. They have several approved vendors for their raw materials (e.g., wood, string, paper, paint, glue). They need to keep track of their purchase orders and the prices that different vendors charge for their products. Because FlyAKite is a small manufacturing company, Ina herself is in charge of these purchase orders. A few times in the past, it turned out that the owner paid the same invoice twice - make sure that the database tracks whether an invoice was paid or not so this doesn't happen again.
Ina designs most of the kites herself - she is a passionate ‘kiter' who attends kite-running competitions. She needs to keep track of what material and how much of each is used to make each kite design and also the pattern that is painted on the kite.
1. Develop an ERD for FlyAKite. Color-code your ERD to show which parts of it belong to which accounting cycle.
2. Which accounting cycles are represented in your ERD?
3. For each of the assumptions below, answer the question based on your model and explain how your model shows this assumption.
a. Are all customers invoiced the same way?
b. Does FlyAKite deliver partial orders?
c. Does FlyAKite sell anything that they do not make themselves?
4. Name two threats the data model protects against and describe how it does so.
5. Name two threats the data model does not protect against and suggest potential controls.