Assignment: Database Design
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. Ned's 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 client's 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.
Task
• Update the ERD to include the design of the new Entities. Use Visio to create the ERD.
• State the business rule relationships of the new entities.
• 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).
• Change Ned's Access Database to include the new entities. Ensure that referential integrity rules are enforced between the new entities to ensure integrity of the database.