Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due

1. Inventory and Supplier Management Perspective

This perspective focuses on inventory, supplier orders, and cost tracking. The base model only allows for bottle management. This perspective would allow for other supplies to be managed. For example, corks, screw tops, labels and boxes. This perspective is to be still focused on the bottling side of production. It is not for the growing or harvesting supplies.

• ER Model Considerations:

o Relationship between bottling supplies, suppliers, and orders.

o Track supplier deliveries and bottling supplies inventory levels.

o Connection between wine production and bottling supplies.

o An aspect of sustainability reporting: The following are two examples. Your group can consider these or apply your own ideas. 1) Sourcing sustainable packaging - answering questions such as what sustainable packing products are used and has the wine company improved its use of sustainable packing.2) Cork versus screw tops – trends in spoilage of a product due to bottling practices.

2. Customer and Sales Perspective

This perspective focuses on customer orders, wine sales, and revenue trends. Currently, the base model allows for only Australian customers. This perspective would allow for the expansion of sales to at least one more country. The group may propose which country. It is not required that the group become experts in exporting wine to this country; rather the system could record sales and ship correctly to that country.

• ER Model Considerations:

o Relationship between customers, orders, and products.

o Track order status (Pending vs. Shipped).

o Connect wine sales data to customer preferences.

o Domestic sales versus international sales

o An aspect of sustainability reporting:  The following are two examples. Your group can consider these or apply your own ideas. 1) impact on sales – answering questions such as do wines that have a sustainability certification sell better and do the wine company’s customers prefer sustainable wines; or 2) Sustainability practices for transportation – has there been a reduction of emissions for delivery of the products to customers?

Project Purpose

The purpose of this assessment is to test your ability to:

-     develop your ability to work in a team

-     engage in critical thinking and analytical skills by justifying the implementation of a new system.

-     develop models such as ER diagram to capture important aspects ofa system which need to be stored in the database for the given scenario.

-     use MySQL to implement the model and retrieve specific subsets of information from the designed database. that effectively supports the given scenario.

Assignment Specification and Requirements

Your database design consultancy team has been asked to design and develop a well-structured database for a winery based on the background narrative in the case study document, one selected perspective (explained previously), Your team will need to name the wine company, and this will become your team's name. You are required to write a professional business report that includes or addresses the following (please read in conjunction with the rubric):

1. Project planning: Create a task assignment table for your two feedback iterations and final submission. The table's left column should list each task, and the right column should specify the responsible team member, their allocated hours, and the task's completion date. For shared tasks, designate one individual responsible for the final deliverable.  For better marks, outline contingency plans for potential project challenges. For example, you may choose to outline your team's contingency plans if unexpected challenges arise that could impact the project's timeline, resources, or quality.

2. Project proposal (justification for the application). Describe the benefits of implementing the proposed system. This is to state your group’s selected perspective with its proposed sustainability initiative and briefly why this will set your system apart from others.  (200-500 words).

3. Functionality of your database design (max  1000 words): Explain the database design proposed for this case study, justifying its structure and how it will resolve the identified business problems. Detail how the redesign will minimize data quality issues  while maintaining security and privacy. The design areas to be covered should include but are not limited to how your database addresses: normalisation, OLTP relational database design guidelines, input controls and security.

4. Entity Relational (ER) diagram: Create an Entity-Relational (ER) diagram based on the case study. If specific aspects of the case study are unclear, you may note your assumptions under the ER diagram. However, ensure that these assumptions do not conflict with or violate any details provided in the case study. ER diagrams must be computer generated using MySQL Workbench and should follow UML class diagram notation.

5. Data dictionary: provide a data dictionary that includes details for each attribute such as the attribute name, data type, domain, null/not-null, uniqueness, primary key, foreign key(s) and definition and the purpose of each attribute. All table names are to be lowercase and attribute names are tobe lowerCamelCase. Demonstrated knowledge of keys including compound keys is required. The data dictionary must be organised by table.

Note: this is not the output from MySQL. For this assessment, a Word-based data dictionary is required.

6. Data cleaning process and creation of test data: two critical parts of a database project are how current data will be converted which includes cleaning the data, and the creation of test data. The wine company has supplied you with examples of some of the data that they currently use. Please see the spreadsheet in the folder. Note: it is fictious.

It is essential to address any errors present in the dataset. You are required to cleanse and filter this data so that data imported into the new database is accurate, complete, and consistent. Use the cleansed data to populate part of the new database and add any required additional data. This section of the report must present a plan. It must show the parts of the plan completed and give recommendations to minimise dirty data in the future. Explain how each table in the database was imported and checked as part of quality control and if needed cleansed. Include the SQL scripts that were used to cleanse the data as part of the explanation with before and after screenshots of examples of the data for each table, this includes any data that needed to be manually cleaned and why.

Not all data has been provided. The data provided is only sufficient for you to demonstrate your understanding of the data  cleaning process. You will need to add enough data to thoroughly test your database design and proposed queries. Explain how the test data was created with examples and how it supports the testing process.

7. SQL queries (reports): You are required to generate eight (8) SQL statements that retrieve data from the database system to address key insights required by management as part of the case study provided.  Briefly explain how the results of the queries can help improve management’s  planning and/or decision-making processes. These questions must be insightful rather than daily transactions such as finding the email address of a particular customer. SQL query requirements to be marked:

- At least six of the queries are based on the group’s selected perspective.

- Queries are to use multiple tables that demonstrate an understanding of the types of joins (no single table queries).

- Dates (at least four of the queries must use data belonging to a time period in at least three different ways. For example, one week or comparing last year’s data with this year’s data. Demonstrates understanding of dates and selecting the correct data.)

- Two of the queries address the sustainability reporting that your group is proposing.

- At least one query is based on a view where the view creation is submitted as part of the query.

- The query code is to be submitted in Word in a form readable by Turnitin. That is, it is not a screenshot or a picture.

- A screenshot of the output for each query from MySQL is to also be included.

Remember you want to demonstrate your SQL skills so include as many of the skills that you have learnt while not having overcomplicated code and creating meaningful output.

8. Generative AI (GenAI) use and prompt engineering process: Briefly explain how you used GenAI through the process of completing this assignment. Specify which GenAI tools you used (e.g., ChatGPT) and for what specific purposes (e.g., brainstorming, drafting, code generation, validation, etc.).  Provide three examples of prompts, clearly showing  the progression from your initial attempts to the prompts that produced satisfactory results. For each example, explain the  issues you encountered and how you revised the prompt to overcome those issues. How do you validate your results? Reflect on what you learned about effective prompt engineering through this process. If you chose not to use GenAI, please briefly explain your reasoning. (200- 500 words).

Formatting and Resources

Use a word processor to prepare your submissions and include all diagrams, which you can prepare using MySQL Workbench (UML class diagram notation). These diagrams are to be submitted in your single report file for each submission.

The instructions for submission formatting are:

1-  Written components: Word Times New Roman font, 12-point font, single-spaced (tables may use 10pt font)

2-  Database tool: MySQL

3-  Referencing Style: APA please see Library guides

4-  All submissions must have  a front coversheet that clearly identifies each student that contributed to the submission. Please use enrolled names only. Please include your group name.

Submissions

First Iteration Submission:

Your first iteration is to include the following and one Word document uploaded to the submission point:

•   Project Plan

•   Project Proposal ensuring that you state which perspective you team has selected and your sustainability initiative.

•   Draft of the section Functionality of your database design

•   Draft of ERD

•   Draft of Data Dictionary

Second Iteration Submission:

Your second iteration is to include as much of the final submission as possible to gain maximum value from the oral feedback and be uploaded to the submission point:

Final submission:

Your final submission will be the above report submitted through the provided Turnitin submission point and the MySQL database with data and queries submitted through the assignment submission point. Details of how to submit will be placed on Blackboard with the submission points.

Both components will be marked using the marking rubric. The final individual mark is determined from the feedback from group members and your tutor.