INFO20003 Assignment 2
This assignment is primarily about writing SQL queries.
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.
Copyright By PowCoder代写 加微信 powcoder
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.
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 yo ur 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 humanreadable (i.e., do not have everything on one line and indent where appropriate!) and the output of your query must be humanfriendly. 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.
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.
3. Write a series of SQL queries (i.e., multiple SQL statements) to appropriately change your location to 123 Fake Street, Fakeville, Melbourne, 9999.
4. How many iOS apps does Gougle sell that have a distribution cost that is less than 20% of the price?
5. How many iOS apps were released during each past decade? I.e., do not include 20102016.
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”.
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.
8. What are the 10 most purchased software that have titles starting with a lowercase “i” or lowercase “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.
9. Which software has not been purchased yet? Show the domain name of this
10. How many different version 1 software titles are currently installed by consumers
living in Australia?
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.
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.
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com