Overview In this assignment you will logically design a relational database for a small extermination business that is located in New York. This is an INDIVIDUAL ASSIGNMENT. DO NOT BUILD THIS DATABASE IN ACCESS, ONLY DO THE LOGICAL DESIGN. Pay close attention to the Background information and the Logical Design Criteria, because this is where you will find everything necessary to complete the logical design 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 Deliverables 1. Entity Relationship Logical Data Model: A visual model which represents each table (entity) and the relationships between the tables. This diagram can be completed with MS. Word, Visio, Smartdraw or some other graphics program of your choice. However, copy and paste your diagram back into Word along with the other two deliverables. 2. Field List: Shows the fields (attributes) for each table (entity) and identify the primary key (by underlining it) Identify any Alternate Keys, Secondary Keys and all Foreign Keys. The field list should include all fields which are found in the problem. We will use the Relational Database Shorthand from the Power Point Presentation "Database Design - Using ER Diagrams". 3. Technical Summary: The technical summary provides verbal support for the relational data model. The technical summary should answer the question, "Why this logical data design and not some other?" or "Why are these relationships one-to-many or many-to-many?" This is the portion where you get to talk the database language and explain/support your Entity Relationship Logical Data Model. At Least 1 page 12 pt font double-spaced. Overview In this assignment you will logically design a relational database for a small extermination business that is located in New York. This is an INDIVIDUAL ASSIGNMENT. DO NOT BUILD THIS DATABASE IN ACCESS, ONLY DO THE LOGICAL DESIGN. Pay close attention to the Background information and the Logical Design Criteria, because this is where you will find everything necessary to complete the logical design 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 Deliverables 1. Entity Relationship Logical Data Model: A visual model which represents each table (entity) and the relationships between the tables. This diagram can be completed with MS. Word, Visio, Smartdraw or some other graphics program of your choice. However, copy and paste your diagram back into Word along with the other two deliverables. 2. Field List: Shows the fields (attributes) for each table (entity) and identify the primary key (by underlining it) Identify any Alternate Keys, Secondary Keys and all Foreign Keys. The field list should include all fields which are found in the problem. We will use the Relational Database Shorthand from the Power Point Presentation "Database Design - Using ER Diagrams". 3. Technical Summary: The technical summary provides verbal support for the relational data model. The technical summary should answer the question, "Why this logical data design and not some other?" or "Why are these relationships one-to-many or many-to-many?" This is the portion where you get to talk the database language and explain/support your Entity Relationship Logical Data Model. At Least 1 page 12 pt font double-spaced