I'm in a bit of a time crunch with this one and I am NO DBA. Three scenerios. Points go to whoever can answer all three with an ERD.
A company wants to maintain data on field operations of its sales force. Specifically, it wants to know which of its salesreps are calling on which customers, when and where the visits take place, and a summary of what happened during the call.
This question has three cases, A, B and C. In each case, assume the relation, T, shown below is in first normal form. Then, do the following:
1) Derive a workable logical design that supports the company's requirements.
2) Map (translate) your design into a relational database model. Make sure that all tables are in third normal form and that you indicate the primary key by underscoring the column name(s) and all foreign keys (if any exist) by bold font for each table in your design.
T = {Custno, Custname, Salesrepno, Salesrepname, Calldate, Callcity, Callsummary}
Where Custno = Customer Number;
Custname = Customer Name;
Salesrepno = Sales Representative Number;
Salesrepname = Sales Representative Name;
Calldate = Date on which a sales representative calls on (has a meeting with) a
customer;
Callcity = City where a call takes place;
Callsummary = Summary of what took place at the call (customer places order,
customer requests more information, customer says no thanks , etc.)
Case A: Assume the following:
1. No more than one call by a given salesrep to a given customer will occur on the same day.
2. The Callcity" is always at the customer's headquarters location, which does not change.
3. A salesrep may make several calls to different customers on the same day.
4. Only one salesrep will call on a customer at one time.
5. A customer may be called upon by different salesreps.
Case B:
Remove Assumption No. 2 from Case A, and redo your logical design. That is, the "Callcity" may be any city that is agreeable to both the customer and salesrep. Other assumptions from Case A apply.
Case C:
Change Assumption No. 5 from Case A so that only one specific salesrep will call on a given customer. That is, the company assigns a specific salesreps to each customer. Other assumptions from Case A apply.
