Reference no: EM132680004

Case Study:

Stella Pty. Ltd. is a local clothing store that sells women clothing. The owner of the company, namely Stella Corlini has envisaged the importance of technology in supporting business operations and decision making. At the moment, there are four stores in Collingwood, Brunswick, Richmond and Melbourne CBD, and there are about 50 staff including part-timers employed by Stella.

Stella is keen to keep a track of the customer profiles and their purchase details. In the current stores, each store purchases items from over 20 different suppliers across the world. The stores currently stock a wide range of elegant outfits, including formal suits and shirts, wedding outfits, and casual attire. Suits, shirts, and casual outfits such as sports jackets, casual shirts, shorts, denim wears, etc. are delivered by various suppliers. In addition to providing the popular brands, Stella Pty. Ltd. also has its own collection of accessories such as shoes, wallets, belts, caps, hats, necklaces, earrings, etc. The store managers are responsible for the purchases and they deal directly with the suppliers. Once purchases are made, these items are then delivered by the suppliers directly to the stores.

While all brands are displayed in the shopfront of each store, each store also has a storeroom to keep extra stock (for example, items in different sizes, colours, etc.). In the storerooms, each brand of item is stored in distinct spots and shelf locations. Items are delivered using various mediums including ship, air, rail, and courier van. Stella would like the database management system to be able to collect data and produce business reports that could support her and her employees for their business decision-makings. The database should be able to encapsulate current sales, invoice details, customer profiles, supplier details, shipping details, item details, and employee details.

To date, Stella has managed this all herself using Google Sheets and emails to keep track of all of the details. However, as her business has expanded, it has become difficult to manage everything and extract valuable insights about her business. As such, she wants a dedicated database management system built specifically to her needs, which has the capability of capturing all of the current and future data associated with the business. She has contracted Best Innovative Solutions (BIS) Pty. Ltd. to carry out this work, with you assigned to design and build a database that meets her specification and also deliver analytics solutions.

Task:

1) Background
In this assignment you are required to demonstrate and apply the concepts and knowledge covered in Week 1 - Week 11. For the purposes of the report, assume that you are employed as a business analyst at Best Innovative Solution (BIS) Pty Ltd. You are assigned to investigate the questions from a new client and provide the solution.

The dataset about Stella Pty. Ltd. has been collected and you are given the following two files for the purpose of applying data analytics:

• dataset (StellaData.xlsx)
• header description (StellaDataset-header-description.txt)

Information about the dataset is listed below:

• The dataset and header description are stored in two separate files.
• The dataset consists of binary, categorical and numerical data.
• There are 7 different attributes and 1000 cases (or instances).
• There are some missing values in the dataset.
• The dataset covers mainly on the aspect of customer related data.

2) Questions from new client
You need to provide: (a) answers to all the following questions based on the case study, (b) word count on the cover page of your report.

Question 1 Data Modelling
•

For each entity, provide at

least one attributes/ fields with a maximum of two attributes (excluding primary key).
• and show the most appropriate attributes/ fields, primary keys and foreign keys. In addition, include the data type for each attribute/ field listed in each table/ entity.

• You should of notation.

and show consistency in the use

• If you discover while drawing the diagrams that the narrative of case study is incomplete, then provide justifications, assumptions, or reasonable explanations to complete the case study along with your diagrams. You could include the justifications, assumptions or reasonable explanations under Appendix section.

Question 2 SQL Scripts
• Based on data modelling from Question 1,

that have the

following requirements. Note that each of the query can cover one or more of the requirements.
o One of the 3 queries should have a calculation
o One of the 3 queries should make use of Group By
eo ofOthne 3 queries should include a scalar function (one that returns a value)
o One of the 3 queries should demonstrate Nested Query (could be either standard or correlated sub-query)
o One of the 3 queries should demonstrate joining tables
o One of the 3 queries should demonstrate the creation and testing of Trigger with both valid and invalid test data
• For each query,
o and business value or impact
o o
o Make use of table(s) with sample data to show potential result/ outcome of query

Question 3 Data Analytics with Orange
After you have dealt with the missing values, discuss how you would predict the
Ltd. using the knowledge of data visualisation and regression. In your explanations, include the screenshots of using Orange to produce the visualisation and prediction model. In addition, explain how your results from data visualisation and regression could help Stella Pty. Ltd. to expand its business.

Question 4 Database Administration Approaches

Provide encountered and

that Stella Pty. Ltd. can potentially

Question 5 Big Data and Analytics
Suggest two varieties of data sources that Stella Pty. Ltd. can use to further improve the understanding of its business. Provide justifications for each of the suggested data source.

3) Report
Word Limit: 1500 - 2000 words +/- 10% (excluding Table of Content, Appendix, figures, tables and screenshots, anything beyond word limit will not be marked) Font Size: 11pt or 12pt
Font Style: Calibri or Times New Roman
Spacing: Single or 1.5 Spacing
Document Margins: 2.5cm for top, bottom, left and right
Spacing: Single or 1.5 spacing

Attachment:- DBMS_1dba.rar