Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012

    Unanswered: parts of car database - unrealistic amount of counts!

    Hey guys! im creating an autoparts and their respective materials database.

    Ive got about 6000 data rows of 20 cars.

    One row looks like that:
    PartID:trunk carpet (trunk) area: trunk material: wool car: Mazda R306 Weigh: 1 pounds

    This is how I designed the database in access:

    Here is my logic behind the design: one car has many areas (rear, trunk) and one area can belong to many cars
    One area has many parts, whereby one part belongs to only one area in the car (trunk carpet belongs to area trunk)

    One parts is made out of many materials, but one material can included in many parts.

    The n:M table between materials and parts has the vehicle_id and the weigh of a part included to be clearly identified.

    Now Ive got some problem: If I connect the vehicle table with the Parts_materials_vehicle table and count the vehicles, I get a number ouf 3000 vehicles! (but there are only 20).

    Furthermore I want to see the average amount of the respective material in all vehicles.

    This is my select:
    SELECT Material.Material_Name, Count(Vehicle.Vehicle_ID) AS AnzahlvonVehicle_ID, Avg(Parts_Materials_Vehicle.Weight_Each) AS MittelwertvonWeight_Each
    FROM Vehicle INNER JOIN (Material INNER JOIN Parts_Materials_Vehicle ON Material.Material_ID = Parts_Materials_Vehicle.Material_ID) ON Vehicle.Vehicle_ID = Parts_Materials_Vehicle.Vehicle_ID
    GROUP BY Material.Material_Name;

    but the result is obviously wrong because the number of cars is about 3000 as well.

    Ive got a mistake somewhere, I dont know in the design or in the SQL code.

    Could someone tell me how I can prevent counting all lines in the parts_materials_vehicle database when I SELECT count(vehicles) FROM Vehicle v, Parts_Materials_Vehicle pmv WHERE V.Vehicle_ID = pmv.Vehicle_ID

    Thank you very much!
    Attached Thumbnails Attached Thumbnails erdcar.png  

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    As far as I can see from the database diagram, you created a cartesian product. There is something wrong with the relationships (see attachement). I'm not sure whether its only in the JOIN operations or if the problem lies in your database organisation.
    Attached Thumbnails Attached Thumbnails erdcar.jpg  
    Have a nice day!

  3. #3
    Join Date
    Feb 2012
    Thank you for your answer! But even if I would delete the layer_vehicle table, I would still count an unrealistic amount of cars if I type
    SELECT count(vehicle_id)
    FROM materials_parts_vehicle m, vehicle.v
    WHERE m.vehicle_id = v.vehicle_id

    and I cant use the SELECT count(distinct vehicle_id) in access.

    Maybe the error lies in either the materials_parts_vehicle or vehicle layer?

Posting Permissions

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