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.

MITS4002 Object Oriented Software Development Assignment Sample

This assessment item relates to the unit learning outcomes as in the unit descriptors. This checks your understanding about object-oriented software development. This assessment covers the following LOs. LO1 Demonstrate understanding of classes, constructors, objects, data types and instantiation; Convert data types using wrapper methods and objects. LO2 Independently analyse customer requirements and design object-oriented programs using scope, inheritance, and other design techniques; Create classes and objects that access variables and modifier keywords. Develop methods using parameters and return values. LO3 Demonstrate adaptability in building control and loop structures in an object-oriented environment; Demonstrate use of user defined data structures and array manipulation

Project: Comparing Loans

Problem Description:

Write a program that lets the user enter the loan amount and loan period in number of years and displays the monthly and total payments for each interest rate starting from 5% to 8%, with an increment of 1/8. Here is a sample run:

<Output>

Loan Amount: 10000

Design: (Describe the major steps for solving the problem.)

Coding: (Copy and Paste Source Code here. Format your code using Courier 10pts)

Output screenshot: (Paste your output screenshot here)

Testing: (Describe how you test this program)

Submit the following items for assignment help

1. Submit this Word document with solution via LMS (you must submit the program regardless of whether it complete or incomplete, correct or incorrect)
Hint:

1. Can you get the first four rows manually? This will help you understand how to compute the numbers mathematically.

2. Can you write a program to produce the first four rows? This will help you see the pattern.

3. Can you generalize it in a loop to produce all the rows?

4. Finally, format the output correctly.

Solution

Program Design
Step-1 START
Step-2 Initialize required variables
Step-3 Initialize rate = 5%
Step-4 User input: loan amount into amt
Step-5 User input: time in years into yrs
Step-6 If amt or yrs <= 0, print error message and GOTO step 15
Step-7 Set r = rate as backup
Step-8 Display header labels
Step-9 While value of rate < = 8, GOTO steps 10, else GOTO step 15
Step-10 Set rate = r/(100*12) for monthly rate calculation
Step-11 Calculate monthlyPayment as per formula
Step-12 Calculate totalPayment as per formula monthlyPayment*12*yrs
Step-13 Display r, monthlyPayment and totalPayments
Step-14 Increase r by 0.125 and GOTO step-9
Step-15 STOP

Coding

Output Screenshot

Testing

The testing phase of the program was carried out at the very end. At first, the sample data provided with the assignment sample was tested with the same set of inputs. Then, random data was entered to test the output, which was then tallied against output of trustworthy online Loan Calculators found online.

Firstly, the code was tested against erroneous inputs like negative values for amount and years, or if the user enters 0 for these variables.

Finally, on successfully testing for at least 3-4 sets of data, the testing phase was concluded to be a success. Some screenshots of testing results are presented below.

MIS501 Principles of Programming Learning Activity Assignment Sample

Qustion

Learning Activity 3.1: Loops

There are a number of activities in this module and you are expected to complete all of these in the labs or in class or in your own time. The first of these is to reproduce any and all of the worked examples in the module content and to review the readings and videos. Then, work your way through these exercises:

1. Write a program to prompt for and read a sequence of non-negative numbers (with a negative number to end input).

Example:

Enter a number (negative to quit): 10.0 Enter a number (negative to quit): 7.5 Enter a number (negative to quit): 3.5 Enter a number (negative to quit): -1

2. Write a program to prompt for and read a sequence of non-negative numbers (with a negative number to end input) and then print the least non- negative input number. If the first number read is negative, print "No numbers.". Round the least number off to one decimal place.
Example:

Enter a number (negative to quit): 10.0

Enter a number (negative to quit): 7.5 Enter a number (negative to quit): 3.5 Enter a number (negative to quit): -1 Least number is: 3.5

3. Write a guessing game where the user has to guess a secret number. After every guess, the program tells the user whether his number was too large or too small. At the end the number of tries needed should be printed. You may count only as one try if the user inputs the same number consecutively. Note that for this question, you need to use a combination of loops and conditional statements.

Please upload one of your answers to the 3.1 discussion forum to discuss with your peers.

Learning Activity 3.2: Loops advanced

There are a number of activities in this module and you are expected to complete all of these in the labs or in class or in your own time. The first of these is to reproduce any and all of the worked examples in the module content and to review the readings and videos. Then, work your way through these exercises:

1. Write a program that asks the user for a counter upper bound, and prints the odd numbers between 1 and the user inserted value.

2. Given an asset original price, the percentage with which the asset depreciates every year and the number of years elapsed, write an algorithm that calculates the actual value of the asset at the moment.

Assume that the depreciation percentage is fixed but each year it is calculated on the current actual value of the asset.

3. Given an asset original price, the percentage with which the asset depreciates every year and the actual value of the asset, write an algorithm that calculates the number of years elapsed for the asset to reach this value. Assume that the depreciation percentage is fixed but each year it is calculated on the current actual value of the asset.

4. Write an algorithm that helps investors estimate their bank account balance after a certain number of years. The program should ask the investor to insert the original account balance and the number of years after which they would like to estimate their balance. Note that a Sydney banks offer a fixed yearly interest of 2%.

5. Write an algorithm that helps a frequent traveller employee redeem his expenses. The program should ask the employee about the number of days he has travelled. The program will then repeatedly ask the employee to insert his daily expenses for each day he travelled independently. The program should print out to total sum of the expenses.

Please upload one of your answers to the 3.2 discussion forum to discuss with your peers.

Solution

## Thread activity 3.1

## Thread activity 3.2

COIT20245 Introduction To Programming Assignment Sample

Assignment Brief

For this assignment, you are required to develop a Menu Driven Console Java Program to demonstrate you can use Java constructs including input/output via the console, Java primitive and built-in types, Java defined objects, arrays, selection and looping statements and various other Java commands. Your program must produce the correct results.

The code for the menu and option selection is supplied: GradingSystemMenu.java and is available on the unit website, you must write the underlying code to implement the program. The menu selections are linked to appropriate methods in the given code. Please spend a bit of time looking at the given code to familiarize yourself with it and where you have to complete the code. You will need to write comments in the supplied code as well as your own additions.

Assignment Specification

You have completed the console program for processing grade of students for COIT20245. We are going to extend this application so the students name, student number, marks and grades can be stored in an array of objects, do not use ArrayList.

The program will run via a menu of options, the file GradingSystemMenu.java has been supplied (via the Moodle web site) which supplies the basic functionality of the menu system.

Look at the code supplied and trace the execution and you will see the menu is linked to blank methods (stubs) which you will implement the various choices in the menu.

Student class

First step is to create a class called Student (Student.java).

The Student class will be very simple it will contain seven private instance variables:

o studentName as a String o studentID as a String
o assignmentOneMarks as double o assignmentTwoMarks as double o projectMark as double
o individualTotalMarks as double
o grade as a String

The numeric literal values, like P=50.00, HD=85.00 must be represented as constants.

The following public methods will have to be implemented:

o A default constructor

o A parameterised constructor o Five set methods (mutators) o Five get methods (accessors)

o A method to calculate total marks and return student’s total marks as double – calculateIndividualTotalMarks(). This calculation will be the same as in assignment one.

o A method to calculate grade and return the grade as String – calculateGrade(). This calculation will be the same as in assignment one. Use constants for all numeric literals.

Note: Following basic database principles, calculated values are not usually stored, so in this case we will not store the grade as a instance variable, but use the calculateGrade() method when we want to determine the grade.

GradingSystemMenu class

Once the Student class is implemented and fully tested we can now start to implement the functionality of the menu system.

Data structures

For this assignment we are going to store the student’s name, student number and assessment marks an array of Student objects.

Declare an array of Student objects as an instance variable of GradingSystemMenu class the array should hold ten students.

You will need another instance variable (integer) to keep track of the number of the students being entered and use this for the index into the array of Student objects.

Menu options

1. Enter students name, student number and assessment marks: enterStudentRcord()
You will read in the student’s name, student number and assessment marks as you did in assignment one.

Data validation (you can implement this after you have got the basic functionality implemented) You will need to validate the user input using a validation loop.

The student’s name and student number cannot be blank i.e. not null and the assessments marks needs to be within the range of (0-assessment weighting), the same as assignment one.

When entering record of student’s name, student number and assessments marks, the student have been entered successfully into five local variables you will need to add these values into the student object array, you will also need to increment a counter to keep track of the number of students you have entered and the position in the array of the next student to be entered.

When the maximum number of students record is reached do not attempt to add any more student’s record and give the following error message:

When the student details have been successfully entered, display the details of the student and the charge as follows

Note: For the next two options, display all and statistics you should ensure at least one student’s record has been entered and give an appropriate error message if it there are no students record entered.

Display all student’s name, student number, assessment marks and grade:

displayAllRecordsWithGrade()

When this option is selected display all the records with grade which have been entered so far.

3. Display statistics: display Statistics()

When this option is selected you will display the statistics as per detailed in assignment one document. You can loop through your array of objects to calculate this information.

Remember the welcome and exit messages as per assignment one.

Solution

Menu class Interfaces

GradingSystemMenu app = new GradingSystemMenu();

This line in the main method is used to create an object of the GradingSystemMenu class. Then this class object is used to call the processingGradeingSystem() method. This method is responsible to handle the menu display and menu choice entry from the users for assignment help
int choice = getMenuItem();

This line inside the processingGradeingSystem() method is used to get the user entered menu choice. Also, inside getMenuItem(), the menu is displayed to the user and input is taken using Scanner class object. This choice input is returned to the processingGradeingSystem() method. This repeats in a loop till user enters choice for EXIT. The flowchart below better defines the flow of actions in this context.

Student and GradingSystemMenu class

GradingSystemMenu class uses the enterStudentRcord() method to take entry of all student records and then create a Student object after validation. In order to create the object, the student class constructor is called with user entered values. These objects are then saved inside the students[] array of type Student Class. This array is then used all across the program for various purposes.

Class Diagram

Reflection Report

It took me about 4 to 5 hours in order to complete the programming assignment as a whole. In the first few minutes of this time, I carefully studied the assignment requirements and then downloaded and read through the documented code on the GradingSystemMenu,java file. This helped me to get started with the assignment.

I did not face any noticeable problem with this assignment as the functions were very clearly documented using the todo comments.

Testing Screenshots

Test Invalid menu input

Test Option 2 with no records

Test Option 3 with no records

Test Blank Name and ID

Test Assignment marks range validation

Test Data Record Entry and display

Test Option 2

Test Option 3

Test Option 4