I wanted to see if I could get some help with creating this logical design database. The entities I have come up with are: Technicians, Customers, Chemicals, and Transactions. I'm not sure if they have the correct names and if there should be more than just 4 entities along with their respective attributes and if there are any foriegn keys, alternate keys, secondary keys, et cetera...

Any and all help would be GREATLY appreciated! Thank you in advance

The assignment is based on the following criteria:

BACKGROUND

No More Bugs Exterminators was started in 2000 and has grown rapidly through word-of-mouth advertising each year. Up to this point, I have been doing all of my own bookkeeping by hand, but as my business continues to grow, it's becoming more time consuming and difficult to continue this practice. To make my life easier, I would like to implement a computerized customer management and billing system. I need your help designing the database.

We specialize in such bugs as roaches, ants, spiders, termites, and Japanese beetles. Each technician travels to a specific customer's residence, whose service are is defined by the customer's Zip Code. The technician inspects the house, and then decides which chemicals to apply. Each customer is only charged for the chemicals used that specific day for their specific job. Each chemical has its own price and the customer is billed based on the amount of the chemical used.

LOGICAL DESIGN CRITERIA

The logical and relational requirements for the database that you are designing for the No More Bugs Exterminators Database are as follows:

1. Each technician is assigned to only one service area based on the customer's zip code, and each service area is covered by only one technician.

2. The customer's address information should be stored.

3. The technician's name and service area should be stored.

4. The different type of chemicals along with their price per pound should be stored.

5. Some customers require exterior service only and that should be noted.

6. A specific Chemical can only be used on the same residence once on a given date. For example: the technician may use Ant Killer at three different houses on the same date, but cannot use the Ant Killer twice at the same house on the same date.

7. Need to be able to store the actual transaction that occurred at a specific house on a specific date, i.e... keep a log of all of the specific chemicals used and the quantity per pound for each specific house during a specific service call (transaction).

8. Technicians visit some customers on a regular schedule and others on an as needed basis. Either way, the system should be able to keep track of historical customer data, i.e.... how many times last month, last quarter, last year did a specific customer receive services?

9. Determine which fields need to be split out for searching purposes.

10. Determine which fields can be calculated in a query and do not store that data.

11. Keep duplication to a minimum, by only duplicating on Primary Key/Foreign Key relationships.

Required Information Needed

1. Entity Relationship Logical Data Model

2. DBDL Field List with tables in third normal form (3NF)