Well, class work any way.
Main Topics
Browse All TopicsI'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.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Probably shouldn't resubmit since we know it's homework.
For information on homework here and EE check out: http://www.experts-exchang
For guidence I'm afraid I'll need more than "the ERD drawings". Do you already have the design and need help creating the ERD with Visio or similar product?
If you are asking for help with the design itself, I'm guessing the first hang-up is 'third normal form'?
I'm at home now and probably won't be able to respond later but had a few minutes. Since you provided some work and mentioned it's rather urgent I wanted to get back to you tonight. I just briefely went over the requirements so don't take any of this as 100% accurate (not that you will EVER get two designers to agree 100%).
Not sure about this one because I tend to over-engineer everything but Callsummary looks like it might be a pick-list. Depending on the class it might be argued this is another entity and FK constraint in the Call entity.
You might also look at a state and state-city lookup table. Again, depends on how complex the class is.
Case-A: looks OK with one exception. Remove callCity from the call entity because of requirement 2.
Case-B: looks to me like the curent case-A.
Case-C: Unless I'm oversimplifying it but take new case-A and add move salesrepno from Call to Customer?
Thanks for accepting but are you sure you don't want to leave it open longer to see if any other experts have more information?
Remember I didn't look into the requirements in great detail. I'd hate for you to get a C or worse based only on my inputs. Now if you get an A, I'll take full credit.
Let me know and I can unaccept the question.
Business Accounts
Answer for Membership
by: slightwvPosted on 2009-11-02 at 12:11:12ID: 25723228
This sounds a lot like homework.