This assignment refers to a relational implementation of the online grocery databaseforthecompanyS-A-D(Shop-And-Deliver)asdesignedinthethe rst assignment (see StudyDirect). For this second assignment you must rst run the SQL script a2-setup.sql available from our StudyDirect site. This denes the tables that your code will rely on. Load the le 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 rst 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 thatyoutestyourcodewithadditionalanddifferentsampledata. However, do not include any statements you used for testing in your nal 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 purposes. Make sure you get the expected results on our ITS server. If you test it on other servers (that you install yourself for instance) you might get differentbehaviour. Donot handinlesin.docor.pdfformatastheycant be run in SQL and will be awarded 0 marks. Make sure you x 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. Foreveryquestionyouanswer,writeyourSQLstatementafterthecomment that contains the corresponding question number in the template. You must notremoveanyofthosecommentsastheywillguidethetestingprocedure. Ifyoudonotprovideananswerforaquestionleavethecorrespondingspace blank but leave the comment in.
You must not deviate from the requested order and name of the columns in queries. Notethatforeveryquerythenamesandorderofcolumnsisclearly speciedinthepaper. Anychangewillmostlikelyconfusethetestingscript and cost you some marks.
Sopleasecomplywiththerulessetoutaboveotherwiseyouwilllosemarks just for not allowing the tests to run properly. Happy SQL coding!
1. WriteSQLcodetosetuptable SAD OrderDelivery accordingto 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 thatallothertableshavebeensetupbyrunningscripta2-setup.sql which you must not include in the submission. The data types (domains)youchooseforthecolumnsshouldbemostappropriateforthe datatheywillcontain. Youmusthoweveraccommodatethefollowing requirements:
2. Change the type of the customerName column to a variable length stringofatmost35charactersandensurethatitcannotbenull. Use just one SQL command (so in particular you must not recreate the entire table)
For each of the tasks specied below write one single SQL query, respectively, that solves the task. You can use nested queries (ie. subselects and subqueries) wherever you like. You must not CREATE any tables of any form and you must not use (nor declare) any stored proceduresorfunctions. You mustproducecolumnheadingsasspeciedwitheachquery. Donotchangeorderornameofthecolumnsas this will cause tests to fail which will cost you marks. Double check thattheyareexactlyasspecied. Itisimportantthatyourquerieswill 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-Ds delivery area from the database. The delivery area, in terms of (latitude,longitude) coordinates, is in the rectangle (including borders) reaching from southwestern point (50.0,-5.8) to north-eastern point (60.0,1.8).
4. Add 2,000 miles to the current mileage of the van with number plate GS60ERQ on the database
5. List all locations whose name contains the string port (in exactly this lowercase spelling). The headings must look like this:
locName latitude longitude
6. List all drivers who are at least 40 years old. Include their rst name, last name, and date of birth. The headings must look like this:
firstName lastName dob
7. Findouthowmanylocationsarestoredonthedatabase. Theheadings must look like this:
numLocations
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
9. FindoutwhichroutesstopatBrightonPier. Providetheroutenumber and name. The heading must look like this:
routeNo routeName
10. Find those locations which have not been used in any stops. List the location name only. The heading must look like this:
locName
11. Find out for each route how many stops it has. List route number, name,andthenumberofstops. Presenttheresultintheorderofroute numbers starting with the smallest. The headings must look like this:
routeNo routeName numStops
12. For each driver on the database nd the location (or locations, as it could be more than one) that the driver has stopped at most often. Givethedriversrstandlastname,thenameofthelocationandcorresponding number of stops. Order the result by the number of stops with the highest number appearing rst. 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.
13. Find out, on which days did the number of stops made by all drivers outnumberthedailyaverageofstops(madebyalldrivers)duringcalendar 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
Forthecreationofstoredprocedures,youneedtochangeyourdelimiter. Note that in the submission template this has already been done foryouandsetto $$ whichyoumustusetoterminateyourprocedure denition. Strictly name your procedure as indicated in the question.
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 submission and make sure it does not use tables or columns that do not exist.
14. Write a stored procedure assignRoute that, given a drivers national insurance number, assigns an admissible vehicle and an admissibleroutetothespecieddriverforthenextday(i.e.tomorrow)by insertingacorrespondingrowintotableSAD 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 picktheroutewiththesmallestpossibleroutenumberandthevehicle 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 produce 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 implemented in MySQL.
Finally, if the driver already has a route and vehicle assigned for the next day, the procedure should not do anything.