INFO20003 Assignment 2 solution




5/5 - (6 votes)

This assignment is primarily about writing SQL queries.
The case
Gougle is a software development company. Gougle maintains a database which stores the
information needed to:
1. Allow consumers to browse software developed by Gougle.
2. Keep track of which consumers have purchased licenses for their software and if
those consumers currently have the software installed.
The database stores the name, current version, price, distribution cost, description, platform
(e.g. iOS, OS X, Windows, Android, PlayStation), website and year of release of different
software products. Consumers are able to search for software based on these attributes. For
each consumer we store their first name, last name, email and location. Consumer locations
are recorded, which includes the previous known locations of consumers.
The database stores information about the various staff members who were involved the
development of different software products. This information is used by the company for HR
purposes and is also displayed to browsing consumers, as there are a few “rock star”
developers working for Gougle that the public may wish to know about. Please note that
Gougle management actively encourage career growth amongst their employees. As such, it
is common that staff involved in the development of one software title will change jobs for the
next software development project. For example, a Graphic Designer may learn software
engineering and change their official title between projects and perhaps even return to
graphic designing if they lack talents as a Software Engineer.
The database must also store the price of software and the estimated cost of distribution.
Often the cost of distribution is the cost of producing a CD or DVD. Although sometimes the
cost of distribution is very small when, for example, the software is only available for
download rather than physical distribution on CDs/DVDs.
A consumer purchases licence/s for software, paying the software price. Although a
consumer may choose to not install the software at the time of purchase. When software is
installed or uninstalled on a hardware platform the Gougle server is notified. You may
assume that this notification reliably reaches Gougle’s servers in a timely manner.
Your Task
The database for the case study above has already been designed and partially
implemented using MySQL. The data model is shown below. The design of the database is
not great. However, you need to work with what has been proposed. Your job is to write SQL
code that the company’s application software will execute. Below you will find the
specifications for SQL that you must write.
Setup Scripts
To help you program your scripts you are provided with a early version of the database. To
set up the database in your MySQL server, download the file assignment2.sql from LMS and
run it in the MySQL Workbench application.
This script creates the schema and database tables and populates them with data. You
should add ‘dummy’ data of your own to help test your queries.
Data model
The following schema describes the database which has been partially implemented.
The SQL queries required
In this section there are questions for you to answer. Your answer must be a single SQL
SELECT statement per question, unless otherwise stated.
Beside each question is a maximum mark which somewhat reflects the difficulty of that
question. The marks total to 50, however this will be scaled down to match the marks
available for this assignment.
Your SQL must be human­readable (i.e., do not have everything on one line and indent
where appropriate!) and the output of your query must be human­friendly. A small proportion
of the marks will be devoted to this.
1. All the tables needed to record consumer information have not been implemented in
the assignment2.sql file! Write a series of SQL statements (i.e., multiple SQL
statements) to create the missing tables as per the specifications above.
[8 Marks]
2. Using the tables you created in the previous question, write a series of SQL
statements (i.e., multiple SQL statements) to populate the database with dummy data
for 5 consumers. One of these consumers must be you! You should make up a fake
location, but otherwise use your real details. The other 4 consumers must be your 4
favourite characters from your 4 favourite movies. Make sure each of the added
consumers have at least one software licence. Include the SQL to add software
licenses in your answer.
[9 Marks]
3. Write a series of SQL queries (i.e., multiple SQL statements) to appropriately change
your location to 123 Fake Street, Fakeville, Melbourne, 9999.
[7 Marks]
4. How many iOS apps does Gougle sell that have a distribution cost that is less than
20% of the price?
[1 mark]
5. How many iOS apps were released during each past decade? I.e., do not include
[3 Marks]
6. List all of the Software Developer/s and how many iOS apps they have created. The
official job title of a software developer is “Software Developer”.
[1 Mark]
7. Gougle management is interested in keeping track of the most active and least active
coders (Software Developers). List who has written code for the most software and
who has written code for the least software. Your answer should show how many
development projects each of the listed staff have worked on.
[7 Marks]
8. What are the 10 most purchased software that have titles starting with a lower­case
“i” or lower­case “e”? For example, “iTunes”, “iMovie”, “eMovie” and so on. This
information is needed to display on the Gougle website and is also used to determine
pay bonuses for staff.
[4 Marks]
9. Which software has not been purchased yet? Show the domain name of this
[2 Marks]
10. How many different version 1 software titles are currently installed by consumers
living in Australia?
[1 Marks]
11. List the top 10 locations in which our paying consumers currently live. For each,
show the full location and number of occupants. This information will be used to send
handwritten letters to groups of consumers.
[5 Marks]
12. Which consumers have never had a location recorded in our system?
[2 Marks]
Formatting requirements for your submission
Submit a single PDF showing your answers to the above questions.
For each question, present an answer in the following format:
1. First, show the question number and question.
2. Second, show your answer (the SQL statement) in text (not a screen shot). Format
your SQL for readability: capitalise appropriately, indent, use new lines, syntax
highlighting and so on.
For example:
1. List all staff with the last name ‘Harrop’
SELECT FirstName, LastName
FROM Staff
WHERE LastName = ‘Harrop’
ORDER BY FirstName;
You may comment your answer, if you wish.