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

 MIS602 Data Modelling and Database Design Assessment Sample

Question

The following case study models a mobile phone company. The company has a number of phones that are sold by staff to various clients. Each phone comes with a plan and each plan has a number of features specific to that plan including:

• a call charge in cents per minute (this does not apply to all plans)
• a plan duration in months
• a break fee if the customer leaves the plan before the end of the plan duration
• a monthly data allowance in gigabytes
Assumptions that are made for the assignment are:
• mobile phones are locked to a plan for the length of the plan duration


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. Your focus should be providing the output as meaningful and presentable possible. Please note extra marks will be awarded for presentation and readability of SQL queries.

Please note all the SQL queries should be generated using MySQL server either using MySQL workbench or MySQL Command Line Client.

Provide SQL statements and the query output for the following:

1 Find all the customers whose surname or given name contains the string ‘IND’.

2 Find the total number of female staffs currently working in the company?

3 List all the staff who have resigned after 2018 who were living in any part of CARLTON.

4 List all the staff who gets a pay rate below-average pay rate?

5 Find the supervisor name of the youngest staff.

6 List the most popular plan. If there are more plans (ties), you should display both.

7 List the total number of plans sold by each staff including their name.

8 List the customer id, name, phone number and the details of the staff who sold the plan
to the customer?

9 List the all the staff (staffid and name) who are active not having any supervisor
assigned.

10 How many calls were made in total during the weekends of 2019?

11 The company is considering giving a 10% increase in payrate to all the staff joined before
2012.
(a) How many staff will be affected? Show your SQL query to justify your answer.
(b) What SQL will be needed to update the database to reflect the pay rate increase?

12 Which tower (Towerid, Location) was used by the customer 20006 to make his/her first
call.

13 List all the unique customers (CustomerId, name) having a same colour phone as
CustomerId 20008.

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

15 i. Create a view that shows the popularity of the plans based on number of plans sold.
ii. Use this view in a query to determine the most popular plan.

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

17 Write an SQL query to join all the seven tables presented in this database taking at least
two columns from each table.

18 List the details of the youngest customer (CustomerId, name, dob, postcode) in postcode
3030 along with total time spent on calls in minutes. Assume call durations are in Seconds.

19 After evaluating all the tables, explain with reasons whether any of the tables violate the
conditions of 3rd Normal Form.

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

 

Answer

Q1 Find all the customers whose surname or given name contains the string ‘IND’.
select * from customer where Surname like '%IND%' or Given like '%IND%';

 

Q2 Find the total number of female staffs currently working in the company?
select count(*) as 'Female Staff' from staff where Sex ='F';

 

Q3 List all the staff who have resigned after 2018 who were living in any part of CARLTON.
select * from staff where year(Resigned)>=2018 and Suburb like '%CARLTON%';

 

Q4 List all the staff who gets a pay rate below average pay rate?
select * from staff where RatePerHour <= (select avg(RatePerHour) from staff);

 

Q5 Find the supervisor name of the youngest staff.
select Given from staff order by DOB desc limit 1;

 

 

Q6 List the most popular plan. If there are more plans (ties), you should display both.
select PlanName, count(*) as CountPlan from mobile group by PlanName order by CountPlan desc limit 1;

 

Q7 List the total number of plans sold by each staff including their name.
select staff.Given, count(*) as 'Number of plans' from mobile left join staff on staff.StaffID = mobile.StaffID group by mobile.StaffID;

 

Q8 List the customer id, name, phone number and the details of the staff who sold the plan to the customer?
select c.CustomerID, c.Given, m.PhoneNumber, s.* from mobile as m left join customer as c on c.CustomerID = m.CustomerID left join staff as s on s.StaffID=m.StaffID;

 

Q9 List the all the staff (staffid and name) who are active not having any supervisor assigned.
Select staffID, Given from staff where SupervisorID=0 and Resigned is null;

 

Q10 How many calls were made in total during the weekends of 2019?
select count(*) as 'Total calls' from calls where weekday(CallDate) in (5,6) and year(CallDate)=2019;

 

Q11a The company is considering giving a 10% increase in payrate to all the staff joined before 2012.
How many staff will be affected? Show your SQL query to justify your answer.
select count(*) as 'Total staff' from staff where year(Joined)<=2012;

 

Q11b What SQL will be needed to update the database to reflect the pay rate increase?
update staff set RatePerHour = RatePerHour*1.10 where year(Joined)<=2012;

 

Q12 Which tower (Towerid, Location) was used by the customer 20006 to make his/her first call.
select t.TowerID, t.Location from mobile as m left join calls as c on c.MobileID=m.MobileID left join connect as cn on c.CallsID=cn.CallsID left join tower as t on t.TowerID=cn.TowerID where CustomerID=20006 order by c.CallDate desc limit 1;


Q13 List all the unique customers (CustomerId, name) having a same colour phone as CustomerId 20008.
select distinct(m.CustomerID), c.Given from mobile as m left join customer as c on c.CustomerID=m.CustomerID where PhoneColour in (select PhoneColour from mobile where CustomerID=20008);

 

Q14 List the CustomerID, Customer name, phone number and the total duration customer was on the phone during the month of August, 2019 from each phone number the customer owns. Order the list from highest to the lowest duration.

select cs.CustomerID,cs.Given,m.PhoneNumber, sum(c.CallDuration) as TotalDuration from calls as c left join mobile as m on m.MobileID=c.MobileID left join customer as cs on cs.CustomerID=m.CustomerID where year(c.CallDate)=2019 and month(c.CallDate)=8 group by m.MobileID order by TotalDuration desc;

 

Q15a Create a view that shows the popularity of the plans based on number of plans sold.
create view popularity_of_plan as select PlanName, count(*) as TotalCount from mobile group by PlanName order by TotalCount desc;

Q15b Use this view in a query to determine the most popular plan.
select * from popularity_of_plan;

 

Q16 List all the plans and total number of active phones on each plan.
select PlanName, count(*) as 'Active phones' from mobile where Cancelled is null group by PlanName;

 

Q17 Write an SQL query to join all the seven tables presented in this database taking at least two columns from each table.
select cst.Given,cst.Sex, m.PhoneNumber, m.PhoneColour, s.RatePerHour,s.Joined, p.PlanName,p.CallCharge, cl.CallDuration,cl.CallTime, cn.ConnectID, cn.TowerID, t.Bandwidth, t.Location from customer as cst left join mobile as m on m.CustomerID=cst.CustomerID left join staff as s on s.StaffID=m.StaffID left join plan as p on p.PlanName=m.PlanName left join calls as cl on cl.MobileID=m.MobileID left join connect as cn on cn.CallsID=cl.CallsID left join tower as t on t.TowerID=cn.TowerID;

 

Q18 List the details of the youngest customer (CustomerId, name, dob, postcode) in postcode 3030 along with total time spent on calls in minutes. Assume call durations are in seconds.

select sum(CallDuration)/60 as 'Time Spend in calls' from calls where MobileID=(select mobile.MobileID from customer left join mobile on customer.CustomerID=mobile.CustomerID where Postcode=3030 order by DOB desc limit 1);

 

Q19 After evaluating all the tables, explain with reasons whether any of the tables violate the conditions of 3rd Normal Form.

Plan table has no unique identity if we want to change the name of the plan, we can’t change that because we are using primary key as plan name. We have to add primary key to plan table and use as foreign keys in other tables like mobile table.

 

Q20 In not more 200 words, explain at least two ways to improve this database based on what we have learned in 1st - 8 th Week.
We can combine surname field and given field as Name field in customer table and staff table. We don’t need date and time data type to DOB field, Joined field and Resigned field, use just date data type for those fields. Validate Phone field and Phonenumber field using only specific character in it. Remove data usage field from calls table. There is no use of data usage.

 

Fill the form to continue reading

Download Samples PDF

Related Sample

Assignment Services