Data Modelling and Database Design MIS602

Task Summary

This final assessment requires you to produce a robust and flexible database that is able to accurately store information about the business mentioned in the case study. You also need to develop the queries and reports to provide data insights which in turn drive business decisions. Your submission should include:

a) Appropriate modeling plus any business rules or assumptions

b) SQL commands to • physically implement your logical model

• populate the database

• retrieve meaningful information

c) Visualizations that can be presented at a board meeting

Context

This assignment brings together all the learning gained over the duration of this subject. The skills and knowledge you’ve gained in this subject form a vital part of your ability to traverse the major disciplines in the data and database fields. You will be able to interact with analysts, developers, database administrators, and managers using a common language which is vital if an organization is to function effectively. These skills are the groundwork for further career progression in data analysis and information management.

Case Study

Boat & Beat (B&B) is a small start-up company that sells small boats and their spare parts operating in Wagga Wagga, New South Wales. B&B keeps its models and spare parts in showrooms across the city. At this stage, if the customers want to view their models, the only option is to visit one of the showrooms. It has been managing the business activities and data using excel sheets. However, managing their business without a proper database and a front-end application causes many redundancies and inefficiencies in addition to not being able to reach prospective customers. They also envisioned that through their e-commerce site, prospective customers would be able to view the 3-D designs of their boats in addition to being able to buy the spare parts online.

With the aim of expanding the business, the senior management decided to investigate the option of implementing a database system along with hosting its own e-commerce website to sell its product range. Accordingly, you are hired to design the logical model of their backend relational database for this system so that they can better track and record their operational data in addition to being able to retrieve data faster for business decision making.

Task Instructions

1. Case Study

Please read the Case study above.

2. Database Case Study Report

Based on the information provided in the case study create a functioning database that can be queried to meet the requirements of the business.

3. The database case study report should include the following elements:

a) ER diagram, relational model, and associated business rules and assumptions

b) SQL commands to: • Create tables (at least 3 tables)

• Insert sample data rows into each table ▪ You may use MySQL Editor to insert data
• At least two SQL queries demonstrating knowledge of any two from the following:
• GROUP BY query
• JOIN query
• NESTED query

For one of the queries, explain the business value and how the information presented would help decision-making.

c) At least one visualization with an explanation of its business value. For each visualization explain its business value.

Referencing

It is essential that you use the appropriate APA style for citing and referencing research. Please see more information on referencing here http://library.laureate.net.au/research_skills/referencing

Submission Instructions

Your LF will guide you on how to submit.

Academic Integrity Declaration

We declare that except where we have referenced, the work we are submitting for this assessment task is our own work. We have read and are aware of Torrens University Australia Academic Integrity Policy and

Procedure viewable online at

http://www.torrens.edu.au/policies-and-forms

We are aware that we need to keep a copy of all submitted material and their drafts, and we will do so accordingly.