× Limited Time Offer ! FLAT 20-40% off - Grab Deal Before It’s Gone. Order Now
Connect With Us
Order Now

MIS602 IT Report

Task Instructions

Please read and examine carefully the attached MIS602_Assessment 2_Data Implementation_ Case study and then derive the SQL queries to return the required information. Provide SQL statements and the query output for the following:

1 List the total number of customers in the customers table.

2 List all the customers who live in any part of CLAYTON. List only the Customer ID, full name, date of birth and suburb.

3 List all the staff who have resigned.

4 Which plan gives the biggest data allowance?

5 List the customers who do not own a phone.

6 List the top two selling plans.

7 What brand of phone(s) is owned by the youngest customer?

8 From which number was the oldest call (the first call) was made?

9 Which tower received the highest number of connections?

10 List all the customerIDs of all customers having more than one mobile number.
Note: Only CustomerId should be displayed.

11 The company is considering changing the plan durations with 24 and 36 days to 30 days.
(a) How many customer will be effected? Show SQL to justify your answer.
(b) What SQL will be needed to update the database to reflect the upgrades?

12 List the staffId, full name and supervisor name of each staff member.

13 List all the phone numbers which have never made any calls. Show the query using:
i. Nested Query
ii. SQL Join

14 List the customer ID, customer name, phone number and the total number of hours the customer was on the phone during August of 2019 from each phone number the customer owns. Order the list from highest to the lowest number of hours.

15 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.

16 List all the plans and total number of active phones on each plan.

17 List all the details of the oldest and youngest customer in postcode 3030.

18 Produce a list of customers sharing the same birthday.

19 Find at least 3 issues with the data and suggest ways to avoid these issues.

20 In not more 200 words, explain at least two ways to improve this database based on what you have learned in weeks 1-8.

Solution

Introduction

A database management system for assignment help can be defined as the program that is mainly used for defining, developing, managing as well as controlling database access. A successful Information System gives precise, convenient and significant data to clients so that it very well may be utilized for a better decision-making process. The decision-making process should be founded on convenient and appropriate information and data so that the decisions can be based on the business objective. The role of DBMS in an information system is to minimize and eliminate data redundancy and also maximize data consistency (Saeed, 2017). In this assessment, the case study of a mobile phone company has been given. The phone as well as its plans are sold by employees to their clients with some specific features. The calls are charged on the basis of minutes in cents. The plan durations start from month. The main purpose of this assessment is to understand requirement for various data information requests from given database structure and develop SQL statements for the given queries.

Database Implementation

As the database is designed on the basis of the given ERD diagram, it's time to implement the database design. MySQL database has been used for implementing the database (Eze & Etus, 2014). The main reason for using MySQL for this database implementation is a free version is available on internet. This database engine is flexible with many programming languages. A good security is also provided with MySQL database (Susanto & Meiryani, 2019).

Entity Relationship Diagram

The given ERD diagram for this mobile phone company database is as following:

Implementation of Database for Mobile Phone Company

Database and Table Creation

Create schema Mobile_Phone_Company;
Use Mobile_Phone_Company;

Table Structure

Staff



Customer



Mobile



Plan



Connect



Calls



Tower



Data Insertion

Staff

Customer



Mobile

Plan



Connect



Calls



Tower

SQL Queries

1. Total number of customers
Select Count(CustomerID) from Customer;



2. Customers in CLAYTON
Select CustomerID, Concat(Given,' ',Surname) as FullName, DOB, Suburb from customer
Where suburb like 'CLAYTON';



3. Resigned Staff

Select * from staff where Resigned is not null ;



4. Biggest Data Allowance Plan

SELECT PlanNAme, BreakFee, Max(DataAllowance), MonthlyFee,
PlanDuration, CallCharge from PLAN ;



5. Customers who don’t have phone

SELECT CustomerID, CONCAT(Given,' ',Surname) AS FullName, DOB, Suburb
from Customer WHERE NOT EXISTS (Select CustomerID from Mobile
WHERE Mobile.CustomerID=Customer.CustomerID);



6. Top two selling plans

SELECT Mobile.PlanName, BreakFee, DataAllowance, MonthlyFee, PlanDuration, CallCharge,
COUNT(Mobile.PlanName)
FROM Mobile, Plan WHERE
Mobile.PlanName=Plan.PlanName
GROUP BY
Mobile.PlanName, BreakFee, DataAllowance, MonthlyFee, PlanDuration, CallCharge;



7. Brand owned by youngest customers

SELECT BrandName from mobile WHERE
CustomerID = (SELECT customerid From Customer where
Mobile.customerid=customer.customerid and
dob=(select max(dob) From Customer) );



8. The first call made by which number

SELECT mobile.phonenumber, calls.calldate
from mobile, calls where
calls.mobileid=mobile.mobileid and
calls.calldate=(select min(calldate) from calls);



9. Tower that received the highest number of connections

SELECT * from Tower WHERE
MaxConn=(Select Max(MaxConn) from Tower);



10. Customers who have more than one mobile number.

SELECT CustomerID from mobile
Group By CustomerID HAVING Count(PhoneNumber)>1;



11. (a) Number of customers affected

SELECT Count(CustomerID) from Mobile, plan where
mobile.planname=plan.planname and
planduration in(24,36);



(b) Update database
Update Plan set planduration=30
where planduration in (24,36);

12. Staff members

Select S1.StaffID, CONCAT(S1.Given,' ',S1.Surname) AS FullName,
CONCAT(S2.Given,' ',S2.Surname) AS SupervisorName From
Staff S1, Staff S2 where
S2.staffid=s1.supervisorid;



13. Phone number which have not made any call

Nested Query
SELECT PhoneNumber from mobile
where not exists
(Select MobileID from calls where calls.mobileid=mobile.mobileid);

SQL Join

SELECT PhoneNumber from mobile Left Join
calls on calls.mobileid=mobile.mobileid
where not exists
(Select MobileID from calls where calls.mobileid=mobile.mobileid);



14. List the customer ID, customer name, phone number and the total number of hours the customer was on the phone during August of 2019 from each phone number the customer owns. Order the list from highest to the lowest number of hours.

select mobile.customerid, CONCAT(Customer.Given,'',Customer.Surname) AS FullName,
mobile.phonenumber, sum(calls.callduration) as NoOfHours, calls.calldate
from calls, mobile, customer where
calls.mobileid=mobile.mobileid and
mobile.customerid=customer.customerid and
month(calls.calldate)=8 and year(calls.calldate)=2019 group by
mobile.customerid, Customer.Given, Customer.Surname,
mobile.phonenumber, calls.callduration
Order by calls.callduration desc;

15. (i) View Creation

Create or Replace View view_color as Select PhoneColour, Count(MobileID) AS MobileNum From Mobile
Group By PhoneColour;

(ii) View Query

Select PhoneColour, Min(MobileNum) from view_color ;

16. Active phone plans

Select mobile.planname, count(mobile.phonenumber) from
mobile, plan where mobile.planname=plan.planname and
mobile.cancelled is not null
group by mobile.planname;



17. Oldest and youngest customer

Select * from customer where dob =
(select max(dob) from customer)

UNION

Select * from customer where dob =
(select min(dob) from customer);



18. Customers with same birthdays

select c.customerid, c.given, c.surname, c.DOB from customer c group by dob having count(dob)>1 order by dob;

Issues with the data

The main issues with the data are as following:

- The relationship in the staff table for defining a supervisor is complicated as it is self joined to maintain the relationship.
- The overall relationship between tower, plan, mobile and calls is very complicated.
- A clean data policy is not used for data insertion.

Ways to improve the database

As we know that the third normalized form has been used to define the database structure, but still some steps can be possible to improve this database. Its difficult to make a self join relationship in a single table, that is used in the Staff table. Hence, a different table can be used for making a list of supervisors. On the other hand, a mobile has a plan and make calls and the calls are made from the tower listed in the tower table.

Secondly, in order to secure this database, an authorized data access is required. It implies that only that much data could be retrieved that is required. The full data access privileges must be given to the administrator or a top management official who actual requires all data reports in order to make better decision making.

References

Fill the form to continue reading

Download Samples PDF

Related Sample

Assignment Services