Term Project
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.
2) An Access file that contains all the tables outlined in the ERD with both entity integrity (through PK) and referential entity (through FK) properly enforced in the relationship diagram. Each table should have at least 1 test record. When you insert these test records, make sure they are consistent with the business rules and constraints you specified in the proposal. For example, if you specified the “Price” attribute to be numeric and not null, then the column should not contain either a string value or a null value. Similarly, if you specified that “Each vendor must supply at least one product”, then you need to make sure that every vendor that appears in the Vendor table must show up at least once in the Product table.
3) The Visio file that contains the updated ERD (include the Visio file even if no changes are made).
Final submission for this assignment will include a Word Doc (.docx), a Access file (.aacdb), and a Visio diagram (.vsdx).
For Term Project Deliverable work, click here
