Hi,

I am a student. I desperately need help with this database stuff as I have no clue what to do or where to begin.


Business Description

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.



Requirements

Essential

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?



Desirable

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?

Deliverables
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.
(createSchema.sql)
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.
(queries.sql)

gee, that's an interesting assignment

what sort of help were you looking for?

surely you don't expect us just to hand you the answers

can we see what effort you have made into creating the class diagram?

So far I have made a basic Table with colums including Vehicles, Make, Model, Fuel Consumption, Price, Cost, Expected Disposable Revenue and so on.

I am trying to model this in UML. So far I have 3 entities Makes, Model and Vehicles. But I am kind of confused..as in is this enough or do i need more entities for Price, Cost Expected Disposable Revenue and all the others.

I am just getting really confused with this entity (Vehicle Type)

as in Motor Cycle, Van, Trucks and so on. How will I add info to this table?

I am thinking I should create 3 different Tables i.e Motorcycle, Van and Trucks and make these into seperate entities.

those things are attributes, not entities

but the thing is if an entity is "Vehicle" the user requirements hasnt really given much in terms of creating a relationship with another entity.

I can create a Entity Vehicle add all the attributes such as Make, model and so on...but then what??

Can a ER diagram or Class diagram just have one entity without relationships?

Can i assume there is a entity called Employee Adding info to the entity Vehicles and create another entity..

show me in your requirements (in post #1) where it mentions employees

see on previous occasions we learnt to model on general systems. Here i cant find any other entities except Vehicle.

you're not looking hard enough

Hi there,

Remember, that not everything related to a vehicle is an attribute...

I have made Vehicle, Make and Model entities! and added a link attribute to both make and model.

added a link attribute to both make and model?? what is a "link attribute?"

also, you need one more entity, to cover the weekly odometer readings

I could answer the first question by adding an entity weekly_fuel_consmp, but can anyone explain how the second and third questions be answered in requirements?