ER-D, Data Warehousing, DBLC, and Database Administration for a company’s Sales Force

Background: A company wants to maintain data on field operations of its sales force. Specifically, it wants to know which of its sales reps are calling on which customers, when and where the visits take place, and a summary of what happened during the call. Instructions: 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, Cust name, Salesrepno, Sales rep name, Calldate, Callcity, Call summary} Where Custno = Customer Number; Cust name = Customer Name; Salesrepno = Sales Representative Number; Sales rep name = 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; Call summary = Summary of what took place at the call (customer places order, customer requests more information, customer says “no thanks”, etc.) Note that partial credit will be given for designs that may have errors, but are substantially correct.

Case A: Assume the following:

  1. No more than one call by a given sales rep 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 sales rep may make several calls to different customers on the same day.
  4. Only one sales rep will call on a customer at one time.
  5. A customer may be called upon by different sales reps.


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 sales rep. Other assumptions from Case A apply.

Case C: Change Assumption No. 5 from Case A so that only one specific sales rep will call on a given customer. That is, the company assigns a specific sales reps to each customer. Other assumptions from Case A apply. Part 3: Essays (30 points) Instructions: Review the three essay topics given below.

[12:27:09 PM] asad iqbal: part b: write essay on 1. Data warehousing 2. SDLC

Order Now