[SOLVED] mysqlDatabases Assignment 2

University of Sussex Informatics

Databases
Assignment 2 (Deadline 7.12.17, 4pm)

Autumn 2017

This coursework must be submitted electronically via E-submission. You have to answer all 15 questions. Your answers must be uploaded in one file using the template file published on the StudyDirect page. This template contains essential comments to steer the automatic testing rig. Please follow the instructions carefully and do not remove any comment line from that template.

Dont write your name anywhere within the file but include your candidate numberinthefirstlineasacomment(e.g. 30455)justincase.

YOU MUST WORK ON THE ASSIGNMENT ON YOUR OWN! The standard rules for collusion and plagiarism apply and any cases discovered will be reported and investigated.

Detailed Instructions (follow carefully)

This assignment refers to an implementation of the firewood sales company database as designed in the the first assignment (see StudyDirect). To be able to answer the questions of this second assignment you must first run the SQL script a2-setup.sql that defines the tables that your code will rely on1. It is available from our StudyDirect site. For the completion of this assignment it will be necessary to inspect the table structure set up by this script. Do not modify the structure of the tables in the given script when you write your answers unless told to do so.

Note that a few data records have been inserted into the tables to help you test your answers. It is recommended that you test your code with additional sample data you insert into the tables yourself. However, do not include any of the test data or the corresponding insert statements in your submission. Also, you must not include the code of a2-setup.sql in your answer. 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 on our ITS server. If you

1Note that the implementation is a translation of the E/R model given in the model answers of Assignment 1, with a few minor deviations: address information has been moved up into the FW Person schema. For salespeople we added a bonusAmount column and a bonusDate column. Their purpose will become clear further below.

1

test it on other servers you might get different behaviour. Dont forget that in MySQL table names are case sensitive.
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 marks.

Each question does only allow one SQL statement as answer. Copy this statement directly below the corresponding question comment, e.g. @@01 if you answer Question 1, on a new line in the template file. Do not remove any of the template comments as they drive the testing rig. If you cannot answer a question just leave the space empty below the question number comment. You can submit partial answers but they must be syntax correct and run. The marks you get for those depends on the quality of the answer. Where a query is very complex, you might wish to add comments to help the marker appreciate what you have done in case your query is not correct.

1. Write SQL code to set up table FW Lorry according to the following Relational Schema:

FW Lorry(vehicleRegNo,make,model,maxLoad,accessories) primary key vehicleRegNo

You must accommodate the following requirements:

1. (a) For table and column names you must pick the names used in the schema above (otherwise you will lose marks as tests will fail).
2. (b) The vehicle registration number may contain letters and is al- ways 7 characters long.
3. (c) The make of the lorry is always one of the following: Volvo, Ashok, Ford, Hyundai, Iveco, MAN, Mercedes-Benz, Scania, Skoda, Tata. The company does not use any other. When or- dered, they should be ordered alphabetically on their string val- ues. Comparison, however, should be normal string comparison (like standard collation prescribes for strings).
4. (d) The accessories can be any combination of the following four strings: Combination, Dual, EOBR, Tandem (which stand for combination truck, dual wheel usage on truck, electronic on- board recording system, and use of tandem axle on truck).

   2

(e) The maximum load is measured in tons and never greater than 99.9 tons. We only keep one digit after the decimal point. The default load is 40.0 tons.

Your code must execute without syntax error and without runtime error assuming that all other tables have been set up by the script a2-setup.sql which you must not include. [7 marks]

2. Write SQL code to set up table FW TransportRequirement ac- cording to the following Relational Schema:

FW TransportRequirement(number,order no,lorry,transport quantity) primary key (number,order no)
foreign key order no references FW SalesOrder(order no)
foreign key lorry references FW Lorry(vehicleRegNo)

You must accommodate the following requirements:

1. (a) For table and column names you must pick the names used in the schema above (otherwise you will lose marks as tests will fail).
2. (b) For each order there are never more than 255 transport require- ments.
3. (c) The type of the transport quantity column must be like the one for the maximum load for lorries given in the question 1e) above, however the default must now be 0.
4. (d) When an order is deleted, all transportation requirement records for this order shall be deleted automatically as well.
5. (e) When an orders order number changes (which will rarely hap- pen), then the change is automatically and consistently performed in the affected transport requirements.
6. (f) When a lorry is deleted from the database, the transport require- ments affected should remain on the database (not affecting the deletion of the lorry otherwise).
7. (g) If and where applicable equip the foreign constraints with con- straint names of your choosing.

Your code must execute without syntax error and without runtime error assuming that all other tables have been set up by the script a2-setup.sql which you must not include. [11 marks]

3

Additional Instructions (Queries) Questions 313

For each of the tasks specified below write one single SQL query, respectively, that solves the task. You can use nested queries (i.e. subselects) wherever you like. You must not use (nor declare) any stored procedures or functions or any tables for Questions 313. You must produce column headings as specified with each query. Do not change order or name of the columns as this will cause tests to fail which will cost you marks. It is important that your queries will work correctly with any data (according to the schema). All references to time, when not explicit, are relative and refer to the time of running the query.

3. Without recreating any tables, add to FW Person a column dob (use the exact same spelling please) for their date of birth. [4 marks]
4. Remove leading 0s from all house numbers in FW Person. Instead of performing a complex string manipulation using single-row string functions, it may be easier to simply use the expression

   TRIM(LEADING 0 FROM ? )

   where you need to replace the question mark by the corresponding expression required for this question. [5 marks]

5. Remove all instances of the client phone number 01273007007 from the database. Clients who gave this number were joking. It is not a valid phone number. [4 marks]
6. List the person identifier of all those salespeople who managed to achieve the following feat at least once: sell more than 200 cubic metres in one single order. Sort the result table alphabetically. The headings must look like this:

   salesPerson_id

   [5 marks]

7. For all salespeople, list their person id, last name and monthly sales target. The table must be ordered by last name. Rows with equal last names must be ordered by monthly sales target with the highest listed first. The headings must look like this:

   4

 person_id lastname monthly_sales_target

[6 marks]

8. List how many orders have been placed in the last 20 days. Here the last 20 days is supposed to refer to the 20 days up to, and including the day the query is run. Your result table should contain one column only. The heading must look like this:

    number_orders

   [6 marks]

9. List all the clients whose first name starts with the same two letters as their last name. Letters with different capitalisation are to be con- sidered different in this case, so a and A are not the same. The result table should include person id, their first and last name. The headings must look like this:

    person_id firstnamelastname

   [6 marks]

10. For each order, compute its total order price which is the sum of the quantities in the order multiplied by the price per cubic metre (col- umn price in FW SalesOrder). For instance, order number 101 has a total price of 5055.00. Your result table should contain two columns: the order number and the total price. The result table should be sorted by column total with the highest price appearing first (at the top). The headings must look exactly like this:

     order_nototal

    [6 marks]

11. For each salesperson, list their turnover for the current quarter. i.e. their sales worth in pounds which is the sum of volume price for each of their individual sales made in this quarter. This means in particular that in joint orders you should disregard the sales of other salespeople. The term quarter refers to the standard meaning in

    5

business where a year is divided into four quarters (the first covering January to March, and so on).

Your result table should contain three columns: person identifier, their name as one column consisting of initial of first name followed by a space, followed by their last name, e.g. N Nobody (and not Nelly Nobody), and the total sales for the salesperson in the current quarter. The headings must look exactly like this:

 person_idnamequarterly_sales

Hint: You are expected to consult the MySQL manual here to find the right date functions that deal with quarters which we have not used so far. [8 marks]

12. For every salesperson compute the average percentage of their contri- butions of order quantity (i.e. volume). For instance, assume a sales- person has sold a quantity of 40 m3 in a first order where the total overall quantity was 200 m3, and that they sold twice 10 m3 to two different customers, i.e. a quantity of 10 + 10 = 20 m3 in a second order with 20 m3 being the orders total. Assume further that this salesperson was not involved in any other order. Then their average percentage would be calculated like so:

    (40/200 + 20/20)/2 100 = 0.6 100 = 60.

    Note that the average to be computed is the average of percentage points, not the average of quantities themselves. Dont round any intermediate results but the final percentage should be rounded to an integer value using MySQL function ROUND. Salespeople who have not sold anything should not appear in the list. The headings must look exactly like this:

     person_idavg_vol

    [6 marks]

13. The quantity sold to one client in one order shall be called individual sales quantity. Two (different) salespeople are called co-sellers if they made both sales for one and the same order (in the sample data provided UYT00158U and ZAS10158U are co-sellers for example). Note that nobody is considered to be co-seller of themselves. Find all

    6

salespeople whose average individual sales quantity per order (con- sidering only orders they have been selling for) is greater than the maximum individual sales quantity of all of their co-sellers.

The result table should have three columns: the person identifier of the salesperson, their average individual sales quantity, and the maximum individual sales quantity of the respective co-sellers. The headings must look exactly like this:

 id avg_salesmax_cosales

[9 marks]

Additional Instructions (Stored Procedures) Questions 1415

For developing answers to Questions 1415 you can use any delimiter you like. But do not declare the delimiter in the submission file and remove the delimiter symbol from the end of each routine declaration in the submission file. This is important for the automatic testing.

Note that successfully declaring a stored procedure does not necessar- ily mean it runs without error. You need to run and test your proce- dures to ensure that. Strictly name the stored procedures as indicated in the question. You are not allowed to include any other stored rou- tine definitions, but you can use the answer from Question 14 in your answer to Question 15.

14. WriteastoredfunctionindividualSalesUpTothat,givenasales- persons identifier as first argument and two dates as second and third argument, respectively, returns as INTEGER the turnover (i.e. sales worth in pounds) of the specified salesperson between the specified first and second date, including the sales on both dates. If the spec- ified salesperson does not exist the function must return -1. If the salesperson does exist but any of the two specified dates is not a valid date the function must return -2.

Illegal dates include dates that do not follow the standard MySQL date format, dates with incorrect month or day values, e.g. 2017-12-88, dates where years are larger than 9999, and in particular 0000-00-00. If the second date is actually before the first date, the turnover to be

returned is simply 0.

[7 marks]

7

15. WriteastoredproceduresetBonusesthat,givenadateasargument sets up the bonuses for all salespeople for the month of the given date as explained below. For instance, if the given date is 2016-09-08 then the bonuses are to be computed for September 2016. If the date is illegal the procedure should abort with an error and error message:

PROCEDURE accountname.date does not exist
where accountname is the name of the ITS account running the pro-

cedure, so when you run it this will be your account name.

Illegal dates include dates that do not follow the standard MySQL date format, dates with incorrect month or day values, e.g. 2017-12-88, dates where years are larger than 9999, and in particular 0000-00-00. Please ensure that the execution is actually giving an error and that the error message is exactly as described here (otherwise testing will fail and you will lose marks). Note that you must not attempt to produce

a string on the screen using a SELECT statement. In order to create those error messages, please consult Lecture 17, which explains how an error can be raised (simulated) in MySQL 5.1.

The bonuses for a particular month are set up as follows: if the monthly target of a salesperson is met or surpassed by their individual sales for that month and their bonus amount is currently zero then their bonus amount is set to 10 per cent of the difference between actual sales for this month and target sales. This difference is then rounded down to the nearest integer. If their current bonus is not zero then their bonus amount is set to 15 per cent of the difference between actual sales for this month and the target sales. Again, this difference is rounded down to the nearest integer.

If the monthly target of a salesperson is met or surpassed by their individual sales for the given month, the bonus date is set to the last day of this month. Hint: Use MySQL function LAST DAY.

If the monthly sales target has not been met the bonus amount is reset to zero. The bonus date remains unchanged in that case.

There is one more requirement you need to implement (in cases where the procedure does not throw an error already): If there is any bonus date recorded in FW SalesPerson that is already in the month passed to the procedure as argument, or if there is a bonus date even later than that argument, the procedure should do nothing at all (in

8

particular, it must not throw any error either). In this case one does know the procedure will have been run already successfully before.

9

[10 marks]