Building on the ERD
Building on the ERD developed in deliverable 1. The group will continue to work on the project and turn in the following files (with new requirements highlighted in bold text):
a) A MS Word document that contains the same five sections of the group proposal (no individual proposal needed). Include my feedback for deliverable 1 and highlight any changes made in response to the feedback. In Particular, section 5 should be expanded to include a list of 10 specific functionalities that you plan to implement using queries in Access (to be done in deliverable 3). Build your queries with the following specific requirements (Note that you may have the one query that satisfies multiple requirements below):
1) The 10 functionalities should cover all entities in your ERD (with the exception that only one of the subtypes needs to be covered). In other words, there should be no entities that are not involved in any proposed functionality.
2) At least 5 functionalities should involve more than one entity (Example: show the total number of the products supplied by customer X) - this functionality involves at least the Product and Customer entities).
3) At least 2 functionalities should involve more than two entities (Example: Show the most profitable product purchased by customers from Florida - this functionality involves at least the Orderline, Order, and Customer entities).
4) At least 5 functionalities should involve the use of mathematical functions such as Sum, AVG, Min, Max.
5) At least 2 functionalities should involve the "Group By" feature (Example: Display the average prices of the products supplied by each vendor).
6) At least 1 functionality should involve the use of the Count feature (Example: Count the number of vendors who have supplied more than one product).
7) At least 1 functionality should involve the entity that exhibits the unary relationship (i.e. a self-join is needed).
8) At least 1 functionality should involve both the supertype and one of the subtypes
Sample Solution
Proposed Functionalities
Introduction
In this section, we will propose 10 specific functionalities that we plan to implement using queries in Access. These functionalities will cover all entities in our ERD, with the exception that only one of the subtypes needs to be covered. At least 5 functionalities will involve more than one entity.
Functionalities
Here is a list of 10 specific functionalities that we plan to implement using queries in Access:
- Generate a list of all products that are supplied by a specific customer. This functionality will involve the Product and Customer entities.
- Find the total sales of a specific product for a given time period. This functionality will involve the Product and Sale entities.
- Identify the top 10 customers by sales for a given time period. This functionality will involve the Customer and Sale entities.
- Find the top 10 best-selling products for a given time period. This functionality will involve the Product and Sale entities.
- Generate a list of all employees who have sold a specific product. This functionality will involve the Employee, Product, and Sale entities.
- Find the average sales per employee for a given time period. This functionality will involve the Employee and Sale entities.
- Generate a list of all customers who have not made a purchase in the past 6 months. This functionality will involve the Customer and Sale entities.
- Find the total inventory value for a specific product. This functionality will involve the Product and Inventory entities.
- Identify the products that are low in stock. This functionality will involve the Product and Inventory entities.
- Generate a list of all orders that are due to be shipped within the next week. This functionality will involve the Order and OrderItem entities.
Changes Made in Response to Feedback
We have incorporated the following changes to our ERD in response to your feedback:
- We have added an attribute called
ProductTypeIDto theProductentity. This attribute will allow us to identify the type of product, such as a book, DVD, or clothing item. - We have added a
SubtypeIDattribute to theCustomerentity. This attribute will allow us to identify the subtype of customer, such as a retail customer or a wholesale customer. - We have added a
Quantityattribute to theOrderItementity. This attribute will allow us to track the quantity of each product that is ordered.
We have also made the following changes to our proposed functionalities in response to your feedback:
- We have added a new functionality to generate a list of all products that are low in stock. This functionality will involve the
ProductandInventoryentities. - We have modified the functionality to find the top 10 customers by sales for a given time period to include the subtype of customer. This will allow us to see which type of customer is generating the most sales.
- We have modified the functionality to find the top 10 best-selling products for a given time period to include the type of product. This will allow us to see which type of product is selling the best.
We believe that these changes will make our ERD and proposed functionalities more robust and useful.
Conclusion
We are confident that we can implement the 10 proposed functionalities using queries in Access. We will provide examples of these queries in Deliverable 3.