For the schema below, write the queries:

 

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.

Queries

1. Trip 73450. How many items were transported during trip 73450?

2. Dead-On Thirty. Find the trip in which exactly 30 items were transported.

3. Tristan Crumbie. Which two companies did Tristan Crumbie deliver to between the 21 th and 22nd of May?

4. What are they doing? Which driver was responsible for the shortest trip (shortest 

duration, not distance)?

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.

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.

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.

8. A, B, but not C. Which vehicles have never transported anything in category C? Only 

show vehicles whose registration plate begins with B.

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)?

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.

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

12. Unused trucks. List the registration numbers of the trucks that were not in use between 

1 and 5 May inclusive.

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.

14. Keeping busy. Find the driver who transported the largest number of items in a single 

week.

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?

manifest customer PK reference company_name PK barcode category PK category description produces FK1 trip_id address 0... 1..1 defines_requirements for I town 1..1 requirement FK2 pickup_customer_ref 0.. FK3 delivery_customer_ref post_code FK4 category orders telephone weight 0... 1..1 0..* contact_fname contact_sname contact_email 1..1 is delivered_in vehicle PK vehicle_id trip PK trip_id registration is_used_on departure_date FK1 model return_date 1..1 0.. body FK1 vehicle_id year FK2 employee_no 0..* 0..* model 1..1 PK model make kerb gvw is_example_of 1..1 driver PK employee_no first_name is responsible_for last_name ni_no telephone mobile hazardous goods