University of Sussex Autumn 2015
Informatics
Databases
Assignment 2 (Deadline 3.12.15, 4pm)
The coursework assigned below should be submitted electronically on our
StudyDirect site by 4 pm on Thursday, 3rd of December 2015. You have
to answer all questions. Your answers must be submitted online in one
(ASCII) file. You must use the template published on the StudyDirect page
that provides necessary comments for automatic testing (simply download
and rename it).
YOU MUST WORK ON THE ASSIGNMENT ON YOUR OWN! The stan-
dard rules for collusion and plagiarism apply and any cases discovered
will be reported and investigated. Furthermore read the instructions below
carefully.
Detailed Instructions you must follow carefully
This assignment refers to a relational implementation of the online grocery
database for the company S-A-D (Shop-And-Deliver) as designed in the the
first assignment (see StudyDirect). For this second assignment you must
first run the SQL script a2-setup.sql available from our StudyDirect
site. This defines the tables that your code will rely on. Load the file into
HeidiSQL with the help of the feature Tools–>Load SQL file. It
is important that you inspect the code in this script and understand how
it implements the requirements outlined in the first assignment. Do not
modify the structure of the tables in the given script when you write your
answers unless you are explicitly asked to do so.
A few rows of data have been inserted into the tables. It is recommended
that you test your code with additional and different sample data. However,
do not include any statements you used for testing in your final submission
(unless explicitly asked for) as this will confuse the testing scripts. Please
note that your code will be tested not only on the small data set provided,
but on other data sets as well and you only receive full marks if your query
runs correctly on all data sets. SQL queries are only considered correct if
they work for all possible data
In the following, SQL always refers to the MySQL 5.1 dialect and all your
code must run on our ITS server where it will be tested for marking pur-
poses. Make sure you get the expected results on our ITS server. If you
1
test it on other servers (that you install yourself for instance) you might get
different behaviour. Do not hand in files in .doc or .pdf format as they can’t
be run in SQL and will be awarded 0 marks. Make sure you fix any syntax
errors before you submit. Format your code properly so that it is readable.
Queries with syntax errors will receive 0 marks.
Do not include any parts of the code of a2-setup.sql in your answer.
For every question you answer, write your SQL statement after the comment
that contains the corresponding question number in the template. You must
not remove any of those comments as they will guide the testing procedure.
If you do not provide an answer for a question leave the corresponding space
blank but leave the comment in.
You must not deviate from the requested order and name of the columns in
queries. Note that for every query the names and order of columns is clearly
specified in the paper. Any change will most likely confuse the testing script
and cost you some marks.
So please comply with the rules set out above otherwise you will lose marks
just for not allowing the tests to run properly. Happy SQL coding!
SQL as Data Definition Language
1. Write SQL code to set up table SAD OrderDelivery according to
the following Relational Schema:
SAD OrderDelivery(routeNo, stopNo, orderNo,
deliveryDateExpected, deliveryTime,
customerName, creditCardNo, customerRanking)
primary key (routeNo, stopNo, orderNo)
foreign key (routeNo, stopNo) references SAD Stop(routeNo,stopNo)
foreign key (deliveryDateExpected) references SAD Workday(theDate)
Your code must execute without syntax or runtime error, assuming
that all other tables have been set up by running script a2-setup.sql
which you must not include in the submission. The data types (do-
mains) you choose for the columns should be most appropriate for the
data they will contain. You must however accommodate the following
requirements:
(a) For table and column names you must pick exactly the names
used in the schema above (otherwise you will lose marks as tests
will fail).
2
(b) orderNo is a positive number smaller than 229.
(c) deliveryTime refers to a time in format HH:MM:SS.
(d) customerName is a string of variable length never longer that 40
characters.
(e) creditCardNo is a 16 character string and values for this attribute
must not be omitted.
(f) customerRanking is one of the following strings: platinum, gold,
occasional, one-off.
(g) Where applicable, equip any foreign key constraints with con-
straint names of your choosing.
(h) Ensure that a stop used in any order delivery can never be deleted
from the SAD Stop table.
(i) Ensure that if a workday is deleted from the SAD Workday ta-
ble the corresponding expected date in an order delivery is set to
null automatically.
[14 marks]
2. Change the type of the customerName column to a variable length
string of at most 35 characters and ensure that it cannot be null. Use
just one SQL command (so in particular you must not recreate the
entire table). [6 marks]
SQL as Data Manipulation Language
For each of the tasks specified below write one single SQL query,
respectively, that solves the task. You can use nested queries (ie. sub-
selects and subqueries) wherever you like. You must not CREATE
any tables of any form and you must not use (nor declare) any stored
procedures or functions. You must produce column headings as spec-
ified with each query. Do not change order or name of the columns as
this will cause tests to fail which will cost you marks. Double check
that they are exactly as specified. It is important that your queries will
work correctly with any possible data. All references to time, where
not explicit, are relative and refer to the time of running the query.
3. Remove locations that are outside of S-A-D’s delivery area from the
database. The delivery area, in terms of (latitude,longitude) coor-
dinates, is in the rectangle (including borders) reaching from south-
western point (50.0,-5.8) to north-eastern point (60.0,1.8). [6 marks]
3
4. Add 2,000 miles to the current mileage of the van with number plate
GS60ERQ on the database. [6 marks]
5. List all locations whose name contains the string port (in exactly this
lowercase spelling). The headings must look like this:
locName latitude longitude
[5 marks]
6. List all drivers who are at least 40 years old. Include their first name,
last name, and date of birth. The headings must look like this:
firstName lastName dob
[6 marks]
7. Find out how many locations are stored on the database. The headings
must look like this:
numLocations
[3 marks]
8. List all the drivers (nisNo and last name) who were working on the
19th of November 2015 together with their route (name) and vehicle
(number plate). The headings must look like this:
nisNo lName route vehicle
[6 marks]
9. Find out which routes stop at Brighton Pier. Provide the route number
and name. The heading must look like this:
routeNo routeName
[6 marks]
10. Find those locations which have not been used in any stops. List the
location name only. The heading must look like this:
4
locName
[6 marks]
11. Find out for each route how many stops it has. List route number,
name, and the number of stops. Present the result in the order of route
numbers starting with the smallest. The headings must look like this:
routeNo routeName numStops
[6 marks]
12. For each driver on the database find the location (or locations, as it
could be more than one) that the driver has stopped at most often.
Give the driver’s first and last name, the name of the location and cor-
responding number of stops. Order the result by the number of stops
with the highest number appearing first. In cases of equal numbers of
stops sort alphabetically by location name. The headings must look
like this:
fName lName locName numStops
If a driver has not stopped anywhere yet, the corresponding value in
locName must be the string unknown. [8 marks]
13. Find out, on which days did the number of stops made by all drivers
outnumber the daily average of stops (made by all drivers) during cal-
endar week 47 of 2015. For each such day produce the date and the
number of stops made that day. Note that we are interested in stops
alone, their locations are irrelevant. The headings must look like this:
workDay numStops
[9 marks]
Procedural SQL
For the creation of stored procedures, you need to change your delim-
iter. Note that in the submission template this has already been done
for you and set to $$ which you must use to terminate your procedure
definition. Strictly name your procedure as indicated in the question.
5
You are not allowed to include or use any stored routine declaration
other than the one asked for. Note that syntactically correct routines
can still throw runtime errors. So please test your routine before sub-
mission and make sure it does not use tables or columns that do not
exist.
14. Write a stored procedure assignRoute that, given a driver’s na-
tional insurance number, assigns an admissible vehicle and an admis-
sible route to the specified driver for the next day (i.e. “tomorrow”) by
inserting a corresponding row into table SAD DriverAssignment.
‘Admissible’ means here that the vehicle and route have not already
been used by another driver on that day. If there is a choice, you must
pick the route with the smallest possible route number and the vehicle
with the smallest number plate (in the lexicographic order).
If the driver does not exist, produce an error message “’procedure
Driver’ does not exist”. If the next day is not a workday, according to
the entries in SAD Workday, produce an error message “’procedure
Day’ does not exist.” Should there be no possible route available pro-
duce error message “’procedure Route’ does not exist”, and if there
is no possible vehicle available produce error message “’procedure
Vehicle’ does not exist”. In order to create those messages, please
consult Lecture 17, which explains how error handling can be imple-
mented in MySQL.
Finally, if the driver already has a route and vehicle assigned for the
next day, the procedure should not do anything. [13 marks]
6