Provide two types of database failures

Assignment Help Database Management System

Reference no: EM132678416 , Length: word count:2000

ISYS2038 Database Design and Development - RMIT University

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)
These two files are available for download on Canvas | Assignments | Assessment Task 3: Individual Final Assessment | Individual Assignment - Case Study, Specifications and Dataset for the purpose of applying data analytics. Further 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
• Provide an Entity Relationship Diagram (ERD). For each entity, provide at least one attributes/ fields with a maximum of two attributes (excluding primary key).
• Provide a Relational Model 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 apply third normal form (3NF) and show consistency in the use of notation.
• 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, create 3 queries 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
o One of the 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 Explain and justify its business purpose and business value or impact
o Provide the SQL script
o Explain the design of SQL script
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 spending score of customers for Stella Pty. 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 two types of database failures that Stella Pty. Ltd. can potentially encountered and discuss the recovery technique(s) for each type of database failure.

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)

Attachment:- Assignment Case Study.rar

Reference no: EM132678416

Questions Cloud

Explain how regional trading groups influence organizations : Choose either the TPP or the T-TIP free trade agreement and describe which other countries have signed on and why the U.S. Senate should ratify or not ratify.

Discuss audit sampling for tests of details of balances : Discuss audit sampling for tests of details of balances and for tests of controls and substantive tests of transactions. How do they differ?

What are the advantages to having a strong currency : Many financial newspapers or websites say that the U.S. dollar is the strongest currency in the world. Think about the factors that affect exchange rates.

What are the tax implications of the distribution to curly : At the beginning of the current year, both Moe and Larry each own 50% of Sitcom Corporation.What are the tax implications of the $130,000 distribution to Curly?

Provide two types of database failures : Provide two types of database failures that Stella Pty. Ltd. can potentially encountered and discuss the recovery technique(s) for each type of database failure

What option should raymond choose to receive prize : Raymond has just won a lottery and he has been given the choice to receive the $1 million prize in one of the following ways:

Which three countries would be first targets and why : Which 3 countries would be your first targets and why? What kinds of strategies and products fit with those countries' segments of customers?

Explain role of secondary data in gaining customer insights : Explain the role of secondary data in gaining customer insights.Where do marketers obtain secondary data, and what are the potential problems

Recommend the key methods that you would use to acquire : Outline a system for identifying the functional areas in which changes might be necessary in order to improve the hospital's service value.

User Account

All Pages