Building On The ERD Developed In Deliverable 1 ✓ Solved

Building On The ERD Developed In Deliverable 1 The Group Will Continu

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 use of 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. After you list the 10 functionalities, indicate which of the above requirements each functionality satisfies, e.g.: find out the average number of products purchased by customers from Florida (#2, #4, #5). 2) An Access file that contains all the tables outlined in the ERD with both entity integrity (through PK) and referential integrity (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), an Access file (.aacdb), and a Visio diagram (.vsdx).

Sample Paper For Above instruction

Introduction

The development of a comprehensive database system requires meticulous planning, including the creation of an Entity-Relationship Diagram (ERD), defining functionalities through queries, and ensuring data integrity through proper table design. Building on the initial ERD, this project involves expanding the data model, designing suitable queries to meet specific functional requirements, populating tables with test data, and updating the ERD to reflect any modifications.

Project Objectives

The primary objectives of this project include:

  • Expanding the ERD to incorporate new entities and relationships as necessary.
  • Designing and implementing 10 specific queries that demonstrate core functionalities, including calculations, groupings, and entity interactions.
  • Developing a robust Access database with all tables, relationships, and data integrity constraints.
  • Updating the ERD diagram to reflect the current database schema accurately.

Methodology

This project follows a structured approach beginning with the review of the initial ERD, gathering feedback, and making necessary adjustments. The functional queries are devised based on business requirements, balancing mathematical operations and entity relationships. After designing the queries, test data are inserted into the tables, verifying compliance with predefined constraints. Finally, the ERD diagram is updated and saved in Visio format.

Expanded Functionalities in Query Design

The specific functionalities planned for implementation cover various aspects of the ERD, ensuring all entities are involved, and utilizing various SQL features such as groupings, aggregates, counts, and self-joins. The following subsections detail these functionalities and their specific requirements.

Functionalities List

  1. Retrieve total number of products supplied by each vendor (uses Group By, aggregates, involving Vendor and Product entities). (Satisfies requirement #4, #5)
  2. Find the most profitable product purchased by customers from Florida (involves Orderline, Order, Customer, and Product entities). (Satisfies requirement #2, #4, #5)
  3. List customers who purchased more than 5 products (uses Count, involving Customer and Order entities). (Satisfies requirement #6)
  4. Calculate the average price of products supplied by each vendor (uses AVG, Group By, involving Vendor and Product). (Satisfies requirement #4, #5)
  5. Show total sales per region (involves Customer and Order entities, using sum functions). (Satisfies requirement #4)
  6. Identify repeat suppliers by self-joining Vendor table on the Supplier ID (self-join, unary relationship). (Satisfies requirement #7)
  7. List products supplied by vendors and their subtypes (supertype/subtype relationship). (Satisfies requirement #8)
  8. Show the highest priced product supplied by each vendor (use Max function, Group By). (Satisfies requirement #4, #5)
  9. Determine the number of orders placed by each customer (use Count, involving Customer and Order). (Satisfies requirement #6)
  10. Retrieve products with minimal price in each category (use Min function, Group By). (Satisfies requirement #4, #5)

Database Implementation

The database design adheres strictly to the relational model, enforcing primary keys, foreign keys, and referential integrity constraints to maintain data consistency. Each table contains at least one test record reflecting realistic business data, such as accurate prices, vendor assignments, and valid customer details.

Updating the ERD

The ERD has been reviewed and updated, if necessary, to incorporate new relationships or entities identified during the query planning phase. The Visio diagram reflects these changes and is included with the final deliverables.

Conclusion

The consistent application of data modeling principles, careful design of queries, and diligent population of test data ensure the robustness of the database system. The final submission encompassing the Word document, Access database, and Visio diagram will provide a comprehensive foundation for ongoing system development.

References

  • Elmasri, R. & Navathe, S. B. (2015). Database Systems: The Complete Book. Pearson.

Related Assignments