Coca Cola Co. in Atlanta, Georgia produces a wide range of products that are delivered to the customers once a week. The Coca Cola Co. keeps the information about the employees, products, and customers in a database. The database considers the following set of tables:
1. The company keeps the following information about each customer: customer identification number, name, address, X (longitude) and Y (latitude) coordinates of their location.
2. Each employee has an employee identification number, name, address (the address consists of: city, state and zip code), sex, birthday, position in the company, date joined the company, and number of years working for the company.
3. Each product has a product identification number, price and number of units produced per day.
4. For tax purposes, the Coca Cola Co. will extend the data kept for each employee to include the information about their dependents. This information consists of dependent’s name, birthday and age.
5. The company has decided to keep information about their suppliers. This information includes: a supplier identification number, address, name of the contact person, X (longitude) and Y (latitude) coordinates of their location.
6. The company keeps a fleet of vehicles to facilitate the distribution of the products to the customers. Each vehicle has an identification number, model, and capacity.
Draw an ER diagram for the Coca Cola Co and add relationship cardinalities. Identify the: (a) key attributes for each entity (b) composite attributes (c) derived attributes, if any.