Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Sep 2008
    Posts
    8

    I need Help Urgent

    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)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2008
    Posts
    8

    Hi

    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.

  4. #4
    Join Date
    Sep 2008
    Posts
    8

    Vehicle Type Table

    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?

  5. #5
    Join Date
    Sep 2008
    Posts
    8

    Vehicle Type

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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by arkabus
    ...or do i need more entities for Price, Cost Expected Disposable Revenue and all the others.
    those things are attributes, not entities
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2008
    Posts
    8

    Thanks

    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?

  8. #8
    Join Date
    Sep 2008
    Posts
    8
    Can i assume there is a entity called Employee Adding info to the entity Vehicles and create another entity..

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by arkabus
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Sep 2008
    Posts
    8

    It doesnt!

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

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by arkabus
    Here i cant find any other entities except Vehicle.
    you're not looking hard enough
    Each make and model has an expected fuel consumption, ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2008
    Posts
    29
    Quote Originally Posted by arkabus
    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?
    Hi there,

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

  13. #13
    Join Date
    Sep 2008
    Posts
    8

    Got it!

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

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Sep 2008
    Posts
    38
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •