University of Sussex Informatics
Databases
Assignment 2 (Deadline 3.12.15, 4pm)
Autumn 2015
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- westernpoint(50.0,-5.8)tonorth-easternpoint(60.0,1.8). [6marks]
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
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
Procedural SQL
[9 marks]
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
[6 marks]
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