PART1 Background

Ned Smith, a local business owner, has contracted you to create an Access Database that can be used to store information for his company. A conversation with Ned has revealed that he is interested in digitizing parts of the business and want to begin collecting some data on his customers. The collection process will be largely manual and collection will occur when the customer calls the business to order the service. Ned states that he would like to keep track of all of customers that order the companys service as well as potential customers that call for inquiries. He would also like to be able to contact customers to re-schedule work as well as offer special promotions. Primary modes of communication will be by phone, however, Ned would like to be able to send an email to all of his clients to offer special promotions and discounts. Ned has also indicated that he likes to add a bit of personal touch to his business so would like to keep track of his customers birthdays so that he can send birthday cards to them. He would also like to be able to offer special discounts to people celebrating milestone birthdays so would also like to keep track of their age. Ned has also indicated that he has sales people that obtain new customers for the business. He would like to keep track of all of his sales people as well as their phone numbers. Ned would like to keep track of what sales person is responsible for each customer as well as how much revenue the customer has generated to date for the company. Ned also sends all of his sales people birthday and Christmas cards so would like to track all relevant information to do that for his sales people as well. Finally, salespeople receive a 10,20 and 30-year award for service. Ned would like to keep track this in order to plan for the occasions.

Assignment Create an Access Database for Neds organization in order to store the data he would like to capture. Select the best data type for each required piece of information. Make sure the database will satisfy all of the requirements Ned has specified.

PART 2 Background Ned Smith, would like to further extend the database design you had previously provided his company. Based on use of the database, Ned has expressed that there are some shortcomings that he would like to have addressed in a new version of the database. Due to the added complexity of the new design, it has been decided that your company will create ER models to communication with Ned and the various database users to ensure the database is designed properly. The additions Ned is looking for is regarding communications data. Ned has expressed that many of his customers and salespeople have both home phones and cell phones and potentially other phone numbers that he would like to capture. Ned would like for an option to store multiple email addresses for both his customers and salespeople, both may have personal and work email addresses. Assignment Create an ER model using a Crows Foot diagram to illustrate Neds database design and the relationships between entities. Use Visio to create the ER diagram. State the business rule relationships between the entities. Change Neds Access Database to adhere to the proper design for Salespeople and customers if necessary. Also make changes to include the new entitie(s) would like to store. Select the best data type for each required piece of information. Submission Submit a word document containing the Crows foot diagram as well as the business rules for each relationship. Submit a completed access database with the necessary changes to Neds database.

PART 3 Background Ned Smith, along with his employees, are impressed with the efficiency the new database design provides them. It allows employees to quickly locate contact information of both clients and salespeople saving valuable time for other more important tasks. Ned was so impressed with the improvements that he decided he would again like to digitize an additional piece of the business to provide greater efficiencies and capabilities. Ned has several vehicles that the salespeople will use to make sales calls to potential and existing clients. Due to budget constraints, not all salespeople will have their own vehicle so they need to share a vehicle when making their sales calls. For scheduling and insurance purposes, Ned needs to keep track of who is currently traveling in which vehicles. Some salespeople will also travel to other cities so may be gone for extended periods of time. Neds business currently owns 4 trucks and 3 cars that are shared amongst his staff. Each vehicle has different capacity so can accommodate different amounts of passengers. The process is currently a manual process in which one or more salespeople will request the use of a vehicle. The vehicle may be used for a short afternoon meeting at a clients office or for a 4-day trip across the province. The data is currently written down in a book and is updated when the car is returned. Ned would like to begin tracking his vehicles and which salespeople are currently traveling together in the vehicle. Some of the details of the vehicles to track are, the passenger capacity to determine how many people can travel in the vehicle, the VIN number of the vehicle as well as the year, make and model and the date the vehicle was last serviced. He would also like to differentiate between trucks and cars as sometimes the salespeople will need to pack promotional material to take to client sites which requires extended storage space. Assignment 1) Update the ERD to include the design of the new Entities. Use Visio to create the ERD. 2) State the business rule relationships of the new entities. 3) List all primary and foreign keys found in the new database and identify any alternate candidate keys that could be used as a primary key for each entity (if any). 4) Change Neds Access Database to include the new entities. Ensure that referential integrity rules are enforced between the new entities to ensure integrity of the database. Submission Submit a word document containing answers and diagrams for questions 1-3 above. Submit a completed access database containing the necessary changes to Neds database.

specifically need the work for part 3