Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Databases

Summer, 2023 paper

• Please upload your submission on Canvas. The submission deadline is 4pm on August 7th.

• Your answers should be given in the file submission.sql file.

• You are not allowed to share or discuss solutions with other students. The university rules for collusion and plagiarism apply and any cases discovered will be reported and investigated.

• All questions should be answered.

Detailed Instructions (follow carefully)

The questions below make reference to an implementation of a firewood sales company database. To be able to answer the questions you must first run the SQL script setup.sql that defines the tables that your code will rely on. You will need 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. You should 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 setup.sql in your answer.

Each question expects only one SQL statement as answer. Copy this state-ment directly below the corresponding question comment, e.g. --Question 1 if you answer Question 1, on a new line in the template file. If you cannot answer a question just leave the space empty below the question number comment. Where a query is very complex, you might wish to add com-ments 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:

(a) For table and column names you must pick the names used in the schema above.

(b) The vehicle registration number may contain letters and is al-ways 7 characters long.

(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 makes. When ordered, they should be ordered alphabetically on their string val-ues. Comparison, however, should be normal string comparison (like standard collation prescribes for strings).

(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).

(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. [9 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:

(a) For table and column names you must pick the names used in the schema above.

(b) For each order there are never more than 255 transport require-ments.

(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.

(d) When an order is deleted, all transportation requirement records for this order shall be deleted automatically as well.

(e) When an order’s order number changes (which will rarely hap-pen), then the change is automatically and consistently performed in the affected transport requirements.

(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).

(g) If and where applicable, equip the foreign constraints with con-straint names of your choosing. [11 marks]

Additional Instructions (Queries) Questions 3–13

For each of the tasks specified below write one single SQL query, respectively, that solves the task. You can use nested queries (i.e. sub-selects) wherever you like. You must not use (nor declare) any stored procedures or functions or any tables for Questions 3–13. You must produce column headings as specified with each query. 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. [5 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. [5 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:

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 firstname lastname [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_no total [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 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_id name quarterly_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 order’s 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. Don’t 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_id avg_vol [8 marks]

Additional Instructions (Stored Procedures) Questions 13–14

For developing answers to Questions 13–14 you can use any delimiter you like.

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 13 in your answer to Question 14.

13. Write a stored function individualSalesUpTo that, given a sales-person’s 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. [10 marks]

14. Write a stored procedure setBonuses that, given a date as argu-ment 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 a suitable error message. Illegal dates include dates that do not follow the stan-dard 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.

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 particular, it must not throw any error either). In this case one does know the procedure will have been run already successfully before. [10 marks]