Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2011
    Posts
    3

    How to draw ERD diagram (CrowFoot Model)

    Pls anybody help me with that how to draw ERD diagram (CrowFoot Model) for following case. I have drew this case but m not sure that is correct or not that's why.



    Queen Star is a company that rents out heavy duty trucks. Presently, they own 10 trucks. Each truck
    is identified by the vehicle registration number. Besides renting out these trucks, the company also owns
    10 containers. These containers vary in their load capacity, dimension and function (for example: for dry
    cargo or for refrigerated cargo). Each container also has a model number and a unique code used for
    administrative purpose.
    The company has fifteen employees consisting of 1 manager, 5 clerks, 2 mechanics and 10 drivers.
    One of the main responsibilities of a clerk is to manage the booking of the vehicles. When a customer
    calls up to make a booking, the clerk will first ask for the customer number or name. If it is the first time
    that the customer is making a booking with Muscle Movers, the clerk will ask for the customer’s details for
    registration purposes. These details include customer name, business name (if applicable), customer
    address, contact number, fax number, and credit card number. When making a booking, the customer
    will indicate the booking start date, the booking end date, and the purpose which the truck will be used for.
    The purposes can be for: house shifting, business shifting, dry cargo transportation, refrigerated cargo
    transportation and rugged terrain transportation. Not every combination of trucks and containers are
    suitable for all purposes. Thus the clerk has to go through a list to determine which combinations are
    suitable for the specified purpose. Then, the availability of those combinations is checked and the hire
    rate of each available combination is presented to the customer. The customer may decide to accept the
    hired rate immediately or may call back later with a decision. If the customer calls back later, he/she will
    need to provide a booking number previously given by the clerk. Only when the customer has agreed on
    the hired rate, the clerk will assign that combination to the booking. A driver with a license type higher or
    equivalent to the one required by the truck assigned is then scheduled. If the customer does not call back
    with a decision, it is identified as an uncompleted booking. All uncompleted bookings are kept for
    administrative purposes.
    On every start date, the assigned driver will hook up the allocated truck with the container. Unless the
    customer has specified a different meeting address, he will reach the customer address by 8 am sharp.
    The truck must be back in the company by 10 pm on the end date. The customer is then charged based
    on the following formula:
    Number of days booked * (driver hire rate + truck_container hire rate).
    The drivers are paid on every fortnight. On top of the basic salary for each employee, each driver is also
    paid an additional of 10% * the number of days he has driven within that 2 weeks * driver hire rate.
    Attached Thumbnails Attached Thumbnails DSAP_Assignment 1_Task 1_Page_1.jpg  
    Last edited by singha2011; 02-13-11 at 07:25. Reason: to upload sample diagram

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    booking_detail has both truck_vehicle_code FK and container_vehicle_code FK

    this is wrong, it should have only vehicle_code FK

    also, can a single booking really have multiple booking_details???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2011
    Posts
    3

    Please

    Dear Rudy

    Thanks very much for your reply, If, please draw new diagram for me with your idea, thanks in advance...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, i was wrong, you probably do need both FKs in the booking_detail, as the booking requires both a truck and a container

    but then the container isn't a vehicle, so that supertype structure is wrong

    also, there's no way i'm drawing a new diagram for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2011
    Posts
    3

    Sorry

    Sorry Dear, I did not mean that you was wrong. I just only want to know more detail to solve the problem which I made wrong, actually I m very poor in ERD and can't solve fully for this problem, please forgive me that I made you upset.
    Last edited by singha2011; 02-13-11 at 09:13.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, don't worry, darling, you did not make me upset

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

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