Scenario
A haulage company manages its information using a relational database whose ER diagram is shown at the end of this document. The database provided contains information from a typical six-month period of operation. To fully understand the database structure, you will need to know some details about the way the company operates, and some of the terminology used:
A single trip may last between 2 and 10 days
Trips are grouped by their start dates. A trip starting in May for example is therefore deemed a “May trip” even if it ends in June.
During a trip the driver visits a number of customer sites to pick up items of cargo, which are then delivered to other customers
Items may be picked up from a customer early in the trip, and other items delivered back to the same customer later on
Routing is complex and is handled separately. This means you do not need to consider the relative location of customers on any given trip or the order in which they are visited
Some items are fragile, and must therefore have their condition checked and signed off by both pickup and delivery customers
Some items are hazardous, and may only be transported by drivers with appropriate qualifications
Manifest is the term used for a list of the items of cargo in transit
Each manifest item is identified by a barcode which is used for checking and billing
The kerb weight of the vehicle is its unladen (empty) weight
GVW stands for gross vehicle weight. This is the maximum allowable laden weight for the vehicle.
Section One - SELECT Statements
The haulage database is available in MySQL format in Moodle as is the ER diagram in both png and drawio format.
Choose any five questions to answer according to your ability. Questions 1 – 5 are worth 6 marks each, 6 – 10 are worth 9 marks, and 11 – 15 are worth 12 marks.
Each solution is a single SQL statement, which must be compatible with MySQL. Hard-coded values should be avoided except when the value is included in the question. The target answer has been provided for each question. The output from your SQL statement should reproduce the results provided, including formatting and column order. Row ordering should also be respected when asked for in the question.
1. Trip 73450. How many items were transported during trip 73450?
Output:
+-------+
| Items |
+-------+
| 6 |
+-------+
2. Dead-On Thirty. Find the trip in which exactly 30 items were transported.
+---------+
| trip_id |
+---------+
| 73303 |
+---------+
3. Tristan Crumbie. Which two companies did Tristan Crumbie deliver to between the 21 th and 22 nd of May?
+-------------------------+
| company_name |
+-------------------------+
| Elephantine Ltd. |
| Temerarious & Co |
+------------------------+
4. What are they doing? Which driver was responsible for the shortest trip (shortest duration, not distance)? (A correct answer should avoid hardcoding the values shown below)
+------------+------------+------------+
| first_name | last_name | days |
+------------+------------+------------+
| Albert | Phillimore | 0 |
+------------+ -------------+----------+
5. The Low Five. Find the five (5) towns where we do the least business – i.e. the one where the fewest number of items are picked up and/or delivered. Order the result by number of items.
+------------+---------+
| town | Items |
+------------+---------+
| Canterbury | 40 |
| Whitehaven | 45 |
| Manchester | 49 |
| Axbridge | 52 |
| Ely | 53 |
+------------+-------+
6. Most Used. Find the five trucks that are most used during the six months covered by the data, which have a non-blank (non-null) value in the body column. Order by the number of trips on which they were used.
+--------+--------------+--------------+---------+
| make | body | registration | trips |
+--------+--------------+--------------+--------+
| Scania | Chassis | KN10WDG | 26 |
| Scania | Curtainsider | BD09FNE | 25 |
| MAN | Box | BR58BXG | 24 |
| MAN | Box | BR58BXC | 23 |
| MAN | Box | BR58BXE | 23 |
+--------+--------------+--------------+--------+
7. Customer satisfaction. Each quarter the company emails the FIVE customers with the highest number of pickups (not manifest items) to check they are happy with the service. List the top FIVE customers for the first quarter (January, February, and March inclusive). Order by Pickups and then Reference number.
+-----------+------------------------+---------+
| reference | company_name | Pickups |
+-----------+-------------------------+---------+
| 94 | Isostere Retail | 19 |
| 204 | Intrados Industrial | 19 |
| 12 | Afforest Group | 18 |
| 186 | Megathermic Inc. | 18 |
| 186 | Megathermic Inc. | 18 |
+-----------+----------------------+--------------+
8. A, B, but not C. Which vehicles have never transported anything in category C? Only show vehicles whose registration plate begins with B.
+----------+------------------+
| model | registration |
+----------+------------------+
| P230 4x2 | BD10AYV |
| R270 6x2 | BD08AOC |
| TGM | BR57BXF |
| TGM | BR58BXE |
| TGM | BR58BXV |
| TGM | BR58BXM |
+------------- +--------------+
9. Travelling light. Usually, the sequence of pickups and deliveries has to be carefully managed so as not to exceed the vehicle’s capacity at any point. However, if the total weight of manifest items for the whole trip does not exceed the limit, these checks can be skipped. How many trips can proceed without checking in quarter 1 (see Q7)?
+--------------+
| COUNT(*) |
+--------------+
| 183 |
+--------------+
10. Average number of trips. What is the average number of trips per model of vehicle in each month? To calculate this, you should divide the number of trips in each month by the number of different (non-blank) values in the model field. You should not hardcode the number of values in the model field. Order the results by month.
+------------+-------+
| trip_month | trips |
+------------+-------+
| January | 14.1 |
| February | 13.1 |
| March | 14.1 |
| April | 13.4 |
| May | 13.8 |
| June | 13.6 |
| July | 1.0 |
+------------+-------+
11. Dangerous driving. For all trips where hazardous good were transported, find the percentage of each category of item in the manifest. Sort in descending order of the percentage of hazardous items. (NB Outputis abbreviated – in your submission, all 48 rows should be included.)
+---------+------+------+------+
| trip_id | A | B | C |
+---------+------+------+------+
| 73832 | 44% | 0% | 56% |
| 73404 | 60% | 0% | 40% |
| 73773 | 63% | 0% | 38% |
| 73551 | 64% | 0% | 36% |
| 73013 | 67% | 0% | 33% |
| … | … | … | … |
| 74059 | 96% | 0% | 4% |
| 73049 | 96% | 0% | 4% |
+---------+------+------+------+
12. Unused trucks. List the registration numbers of the trucks that were not in use between 1 and 5 May inclusive.
+--------------+
| registration |
+--------------+
SDU 567M |
| PY56 BZU |
| PY58 UHB |
| PW09 EKX |
| PY12 RSV |
+--------------+
13. Bonus. If a driver works more than 24 days in any one month, they are paid at a higher rate for the extra days. List the drivers who qualify for bonus payments for each month in the data and include the number of extra days worked. Drivers who are not eligible for a bonus should not be shown. Order by month and number of days descending.
+-------+--------------------+------+------------+
| Month | Name | Days | Bonus days |
+-------+--------------------+------+------------+
| March | Oscar Nutten | 27 | 3 |
| March | Daniel Miliffe | 26 | 2 |
| June | Lee Rookledge | 28 | 4 |
| June | Durant Kewzick | 26 | 2 |
+-------+---------------------+------+------------+
14. Keeping busy. Find the driver who transported the largest number of items in a single week.
+------------+-----------------+-------+-------------------+
| first_name | last_name | items | weekno |
+------------+----------------+---------+------------------+
| Shakir | Johansson | 45 | 21 |
+------------+---------------+-----------+-----------------+
15. Capacity factor.100% capacity is when every truck is in use every day. If some trucks are idle, the capacity factor is less than 100%. What is the total capacity factor for the company for the time period covered by the data?
+-----------+
| capacity |
+-----------+
| 42% |
+-----------+
Section 2 – Database Design
You are required to extend the haulage system to include customer account managers.
The following facilities are required:
Only drivers are currently recorded – a new staff role of customer account manager (CAM) is needed.
Every customer must be assigned a CAM, and one CAM can serve several customers
CAMs may change over time – it must be possible to find out who was the CAM at the time of any trip, even if that person has subsequently left the company
It must be possible to identify a customer’s current CAM
In addition, the following facilities for CAMs to handle customers’ queries are required (10 points):
The details of any initial customer enquiry must be recorded
The CAM’s response must be recorded (potentially at a later date than the original query)
Both the customer and the CAM can add follow-up entries to the query history
Queries must be marked as closed when no further action is required
You are required to submit:
An update of the ER diagram to include tables/attributes to support these additional features
A proof of the modified design in the third normal form
SQL statements (which must be compatible with MySQL) to:
a. alter the database to include any additional tables and attributes required
b. insert sample data for at least three customers and their CAMs
c. query the database to retrieve the information needed to illustrate the above requirements.
Database Security
Provide and discuss a set of recommendations that application developers should follow to prevent unauthorised access to data when using this database in an application (max. 1000 words)
Database you can find from: https://github.com/Realcode4you/Database-MySQL.git
Send your requirement details at realcode4you@gmail.com if you need help in any other database related help.