Examine the relational model and ER diagram

Assignment Help Other Subject

Reference no: EM132942373

MIS602 Data Modelling and Database Design - Torrens University Australia

Assessment - Database Implementation

Learning Outcome 1: Design solutions applying relational database techniques to complex problems and communicate these solutions to all stakeholders.

Task Summary
In this assessment, you are required to demonstrate your ability to understand the requirements for various data information requests from an existing database and develop appropriate SQL statements to satisfy those requirements.

Context
Being able to query a database is a fundamental skill that is required by all information systems professionals who work with relational databases. In this assessment, you will utilize the basic query skills that are typically used to extract information for analysis, reporting and data cleansing in a data management setting. Timely provisioning of key business information promotes effective communication and enhanced solution delivery.

Task Instructions
1. Please read the attached MIS602_Assessment 2_Data Implementation_ Case study.

2. Examine the relational model and ER diagram and derive the SQL queries to return the required information.

3. Provide SQL statements and the query output for the following:

1. List all the customers who live in any part of CAULFIELD. List only the Customer ID, full name, date of birth and suburb
2. List all of the active staff. Show their Staff ID, full name and weekly salary assuming that they work a 38 hour week
3. Which plan has the most expensive contract to break?
4. Which brands of mobile phone does this company sell? List only the unique brand names
5. Which customer is not able to purchase a phone? Use a query to explain why. Hint: Review the customer data
6. How many of each phone plan have been sold?
7. What is the average age of an Apple phone user?
8. What are the first and most recent mobile phone purchases?
9. i. For calls made in 2018 how many calls were made on the weekend?
ii. For calls made in 2018 how many calls were made on each day of the weekend?

10. Provide a listing of the utilization of each tower and its location i.e. how busy each tower is based on the number of connections. Put the busiest tower at the top of the list
11. Did any users on the ‘Large' plan exceed their monthly allowance during August 2018?
12. The company is upgrading all their 3G towers from to 5G.
i. How many towers will be upgraded?
ii. what SQL will be needed to update the database to reflect the upgrades?
13. i. List the full name, join date, resigned date of each staff member and name, join date and resigned date of their manager
ii. What do you observe with the data?
14. How much revenue was generated in 2017 by each plan from call charges. Format the output as currency i.e. $123.45
15. List the customers who made phone calls longer than 200 minutes
16. Which customers have more than one mobile phone? List the customer name, suburb and state. Order by the customer name
17. Are there any mobile phone plans that are currently unused? This can be obtained a number of ways. Demonstrate this by using the following two query types
i. Nested subquery
ii. Outer join
18. List the oldest and the youngest customers in the postcodes 3000 and 3102. Show the customer full name age and suburb details
19. i. Create a view that shows the popularity of each phone colour
ii. Use this view in a query to determine the least popular phone colour
20. The billing team is getting returned mail because of bad customer addresses. This is causing a loss in revenue.
i. Review the customer data and find at least 3 issues
ii. Provide the SQL statements to correct the data problems

4. The database used for this assignment is available in ‘MIS602_Assessment 2_Database SQL Files' folder. Please download the files from this folder.

5. All statements and first 10 lines of output need to be neatly presented. Please save all your SQL statements and output into a single word document and submit it via the Assessment link in the main navigation menu.

Referencing
It is essential that you use appropriate APA style.

Attachment:- Database Implementation.rar

Reference no: EM132942373

Questions Cloud

Difference between metric and non-metric variables : Discuss the major issues relating to the types of variables used and the sample size required in the application of discriminant analysis.

Compute the company wacc for magellan corporation : Compute the company's WACC. Is this WACC considered reasonable given the assumptions and other relevant information? Explain

Different means of responding to corporate insolvency : There are a variety of ways to respond to a situation in which a company is unable to pay its debts other than liquidating the company.

Compute the cost of capital of the stock to firm : The dividend rate is 7.5%, and the par value of the stock is $105. Compute the cost of capital of the stock to your firm. Show all work

Examine the relational model and ER diagram : Examine the relational model and ER diagram and derive the SQL queries to return the required information

How much will be in arrears at the end of atlanta hawks inc : Dividends were in arrears for 2 yhe shareholders' equity of Atlanta Hawks Inc., as of December 31, 2018 consist. How much will be in arrears at the end of 2018?

How do customer relationship management systems : How do customer relationship management systems help organizations achieve customer intimacy?

Find effect on the total additional paid in capital would be : The original issue of Ordinary Share of P 30 par was replaced by P 30 stated value. As a result, the effect on the total Additional Paid In Capital would be

Strategies for competing in fragmented industries : Discuss the reasons for supply-side fragmentation and strategies for competing in fragmented industries. Use two appropriate examples in your discussion.

User Account

All Pages