I desperately need help with this database stuff as I have no clue what to do or where to begin.
Red Rocket Deliveries has a fleet of vehicles which costs them a lot of money to run and maintain. Most of relevant information is currently recorded, but it can not be effectively analysed, because it is scattered amongst various paper and computer based systems. In order to better understand and manage this expense, Red Rocket intends to establish a database. The business operates several different kinds of vehicle, including motorcycles, vans and trucks. The expected lifecycle of a vehicle depends on its make and model. Each make and model has an expected fuel consumption, number of kilometres between services, average service cost, purchase price, expected disposal revenue and maximum years or kilometres of service. This information is used to predict expenses. Actual expenditures are also be recorded. Each fuel purchase is associated with a particular vehicle. Each vehicle service is recorded, and at the end of each week, the odometer reading of each vehicle is recorded.
1. How much did we spend on fuel last week?
2. List all purchases of fuel that exceed the capacity of the vehicle for which it was purchased.
3. Which vehicles are due for service?
4. How much would it cost to replace all of our motorcycles with Harley-Davidson Sportsters?
1. Which vehicles are consuming at least 20% more fuel than estimated?
2. List the average price of fuel by the day of the week on which it was purchased.
3. Which vehicles will be due for service at the end of next week, assuming they travel the same number of kilometres as last week?
4. For each make and model of vehicle, give the average cost of parts in the first year of service and in the last year of service.
5. Which motorcycles will reach the end of their service life in the next 6 months, assuming they continue their average weekly mileage?
We require a small demonstration database and queries which provide the information
listed above. Specifically, we require:
1. A UML class diagram modelling the required data. The main model
elements should be defined and explained in the submission text.
2. SQL code to create a database schema corresponding to the class diagram.
3. SQL code to populate the database with test data that adequately demonstrates
the required queries. (populate.sql)
4. SQL queries to provide each item of information listed in the system requirements.