INFO90002 Database Systems and Information Modelling Assignment 2, 2018 s1
Assignment 2: SQL
You are the SQL programmer for a company that operates a (fictional) city subway system.
Your job is to write SQL queries that answer questions posed by management.
The subway has three train lines, named “Northern”, “Western” and “Eastern”, each of which starts
at the “City” station. Along each line are several stations. Each station has an id and a name, and a
sequence number along the line. On a given line, station 0 is City, station 1 is the ‘first’ station
travelling outbound etc., until the station furthest from the city, which has the highest sequence
number. The subway map in figure 1 shows the three lines and the stations on each line. Since the
city station is on all lines, it is marked as being on line ‘0’ (note that there is not an actual line 0).
Some stations are in “Zone 1”; the rest are in “Zone 2”.
Customers take journeys from one train station to another. The cost of a journey depends on which
zones the customer travels through.
Journeys entirely within zone 1, or entirely within zone 2, cost $1.
Journeys that start in one zone and end in another cost $2.
Journeys that start in zone 2 on one line, travel through the city,
and end in zone 2 on another line, cost $3.
INFO90002 Database Systems and Information Modelling Assignment 2, 2018 s1
Data Model
The following ER diagram describes the database schema which has been implemented.
Setup Script.
To set up the database in your MySQL server, download the file asst2-setup.sql from LMS and run it
in Workbench. This script. creates the schema and database tables and populates them with data.
Note the comments near the start of the script. – it needs to be different depending on whether you
run it on the UniMelb server or your own computer. If it is your own computer, you will want to
create a new schema to host the tables.
The SQL queries required
Over the page are listed 10 questions for you to answer. Write one SQL statement per question. Do
not use views or temporary tables to answer the questions. UNION is allowed.
Where it would improve readability, order your output and use aliases. Format large numbers and
fractions appropriately.
Beside each question is a maximum mark which reflects the difficulty of that question.
Your total score will be scaled to 10% of your overall marks in the subject.
Your work will be assessed on the correctness and simplicity of the queries that you write. (A query
that produces correct output but is more complex than it needs to be, for example joining more tables
than is necessary, may not achieve full marks, even if it produces the correct results.)
INO9000 Database Systems and Information Modelling Assignment 2, 2018 s1
1. How many usernames contain the letter A? (1)
2. What is the average journey length, in seconds? (1)
3. List the stations on the Eastern line (excluding City),
in outbound order. (2)
4. On which day of the week (Monday, Tuesday etc.) are the most
journeys made? (2)
5. List any stations at which no passenger has started or ended a
journey. Show the station id and name. (3)
6. List the details of each journey, along with its cost. (3)
7. List the station ids, along with the number of journeys that started
or stopped at each station. (4)
8. List the journeys that ended at the last station on the line.
(the station with the highest sequence number for a line) (4)
9. For each journey, show how many stations it passed through.
(Count the end station but not the start station.) (5)
10. List the usernames of customers who have travelled on all lines. (5)
INFO90002 Database Systems and Information Modelling Assignment 2, 2018 s1
Submit a single PDF showing your ten answers to LMS by midnight on the due date of Monday 30th
April, at the start of week 9.
Ensure that you place your student number at the top of every page of your submission.
For each question, present an answer in the following format:
Show the question number and question in black text.
Show your answer (the SQL statement) in blue text (not a screen shot)
Show a screenshot from Workbench showing output of 10 or fewer lines.
Show how many rows were returned, in red text
For example:
7. List all users with the last name ‘Altman’
SELECT *
FROM User
WHERE lastName = 'Altman';
5 Rows Returned
SQL queries must be formatted in an easy-to-read manner. This means writing keywords in all-caps,
placing most clauses on new lines, and indenting subqueries. For example, this is acceptable:
SELECT DISTINCT saleId
FROM Sale
WHERE departmentID IN
(SELECT departmentId FROM Department
WHERE floor = 2);
whereas this is not acceptable:
select distinct saleId FROM Sale where departmentID IN (SELECT departmentId from department
WHERE Floor = 2);;
INFO90002 Database Systems and Information Modelling Assignment 2, 2018 s1
Academic Honesty
This assignment must be your own work. Plagiarism - the copying of another's work without proper
acknowledgment - is not permitted. Nor is allowing another person to copy your work. Work
submitted for assessment purposes must be the independent work of the student concerned. Please
refer to http://academichonesty.unimelb.edu.au/ for details about academic honesty.
Academic misconduct occurs when students portray someone else's work as their own. There are
many ways in which academic misconduct can occur. Some of these are:
Sham Paraphrasing: Material copied verbatim from text, with source acknowledged in-line,
but represented as paraphrased.
Illicit Paraphrasing: Material paraphrased from text without in-line acknowledgement of
source.
Other Plagiarism: Material copied from another student's assignment with the knowledge of
the other student.
Verbatim Copying: Material copied verbatim from text without in-line acknowledgement of
the source.
Recycling: Same assignment submitted more than once for different subjects.
Ghost Writing: Assignment written by third party and represented as own work.
Purloining: Assignment copied from another student's assignment or other person's papers
without that persons knowledge.
The University is committed to graduating students with "a profound respect for truth, and for the
ethics of scholarship... we want our graduates to be capable of independent thought, to be able to do
their own work, and to know how to acknowledge the work of others" (Professor Peter McPhee).
As such, the university takes a dim view of students who are not able to correctly acknowledge the
work of others, or who try to pass this work off as their own.
All students should check the web site http://academichonesty.unimelb.edu.au/ which provides
practical advice to students about how to avoid academic misconduct.