Results 1 to 5 of 5

Thread: Design Help!

  1. #1
    Join Date
    Nov 2004
    Posts
    9

    Unanswered: Design Help!

    hi I'm creating a project and i just need some guidance on if my ERD is designed right so i can move on to creating tablesTips and welcomed!...thx...Here are the business rules.

    The Ocean Ford dealership wishes to manage data about their car sales. They are interested in keeping information about their customers. They wish to record data about all customers, whether they purchase a car or not – this data includes, name, address, phone and what marketing tool brought them into the dealership (recent newspaper ad about a particular vehicle, TV commercial, word of mouth or radio commercial).

    When customers leave without purchasing a vehicle, the dealership wishes to ensure a methodical follow up strategy is taken. The dealership wants to record the following:
    (1) the date and salesperson who places a telephone call to the customer thanking them for their visit and inviting them to return, as well as the customer’s response;
    (2) The date and the name of the sales manager who places a telephone call to the customer thanking them for their visit and inviting them to return, as well as the customer’s response to the telephone call and;
    (3) The date a letter from the owner or Ocean Ford is sent to the customer thanking them for their visit.

    When customers purchase a new vehicle the dealership wishes to record for tracking purposes whether the car purchased is on order or is in current inventory. When a customer purchases a previously owned vehicle they wish to record whether the customer purchases an extended warranty and the mileage at the time of sale.

    Ocean Ford also wishes to maintain data about their inventory new and previously owned. Data about cars, trucks, SUVs and minivans include: vehicle identification number, model, exterior color, interior color and year. The cars, Focus and Mustang, both have the additional attribute, package; while the Five Hundred model has the unique attribute, body style. Trucks have a few unique attributes (with the exception of the 2005 E series): cab, box, drive and doors. The SUVs, Escape, Explorer, and Excursion, have the unique attributes, drive and engine. The SUVs, Explorer Sport Track and Expedition, just has the attribute, drive. Although Ocean Ford is contemplating adding commercial trucks to their inventory, currently there are no other types of vehicles in Ocean Ford’s inventory. Obviously a vehicle can be only one kind of vehicle at a particular time.

    A customer may purchase one or more new or used vehicles from inventory and each vehicle is purchased by one or more customers (a new car in inventory may be traded in the next year, entered into inventory and then sold to another customer.) The organization is interested in the date of purchase, amount of sale and salesperson(s) completing the sale.
    Attached Thumbnails Attached Thumbnails ERD.jpg  

  2. #2
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122

    Home work?

    So what's your question?

    This looks like a homework assignment. I have no intention of doing your homework but i will consider answering specific questions if you are actually having difficulties with an issue.
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  3. #3
    Join Date
    Nov 2004
    Posts
    9
    my questions are do i break the vehicle table into new and used or do i just put is as an attribute of VEHICLE?

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by hello_world
    my questions are do i break the vehicle table into new and used or do i just put is as an attribute of VEHICLE?
    Hi hello_world,

    Though I am no SQL pro I have designed a few databases and things like that depend on the individual normally. But my view is that you set that as an Attribute of the Vehicle table. REASON: If you were to make a NewVehicle table and UsedVehicle table, that to me seems a bit redundant. My thing would be to make that an Attribute of Each Vehicle. Then, later when the vehicle is traded back in you only change that attribute which then makes it as though you placed it into a separate table. Also, for reporting purposes, you can base your report on both New and Used and get two views of the data still.
    Like I stated, that is the way I would do it and I am not a pro at this at all. But I have done something like this in the past and it worked well for me.

    have a nice one,
    BUD

  5. #5
    Join Date
    Nov 2004
    Posts
    9
    Thanks for the help Bud...This is what I concluded
    Attached Thumbnails Attached Thumbnails ERD.jpg  

Posting Permissions

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