Re: vehicle to autoparts relationships

>> What is reference 123?
> ... supplier's reference no..

Ok, thanks.

Below dbd example models parts that make up a Civic LE and Civic SE. Each has a different engine configuration and transmission however some parts like starter and gears are shared. Each part can be supplied be multiple suppliers. Each supplier has a different part# and cost. Queries return all parts of each car. Queries return total cost of car based on its parts. From Honda, one can drill down to supplier parts. >From Suppliers, one can drill down to Honda parts. If anyone wants to browse the populated db (fits on a floppy), email me.

CivicLE
  EngineCfg1
    1.5L Engine

       starter1
       carb1
  Manual Transmission
       gear1

CivicSE
  EngineCfg2
    1.6L Engine

       starter1
       carb2
  Automatic Transmission
       gear1
       gear2

Following manufacturers supplier parts for above: mfg1
  gear1
  gear2
  carb1
  starter1

mfg2
  gear1
  gear2
  carb2
  starter1

(new 'part#)
(new 'cost)

(new 'gear1 'gear)
(set gear1 part# (val+ 'hG1))
(set gear1 cost (val+ '10))

(new 'gear2 'gear)
(set gear2 part# (val+ 'hG2))
(set gear2 cost (val+ '20))

(new 'starter1 'starter)
(set starter1 part# (val+ 'hSt1))
(set starter1 cost (val+ '100))

(new 'carb1 'carburetor)
(set carb1 part# (val+ 'hC1))
(set carb1 cost (val+ '300))

(new 'carb2 'carburetor)
(set carb2 part# (val+ 'hC2))
(set carb2 cost (val+ '400))

(new 'eng_1.5L 'engine)
(set eng_1.5L part starter1)
(set eng_1.5L part carb1)

(new 'eng_1.6L 'engine)
(set eng_1.6L part starter1)
(set eng_1.6L part carb2)

(new 'engCfg1 'engineConfig)
(set engCfg1 part eng_1.5L)

(new 'engCfg2 'engineConfig)
(set engCfg2 part eng_1.6L)

(new 'trans_manual 'transmission)
(set trans_manual part# (val+ 'hTm))
(set trans_manual part gear1)

(new 'trans_auto 'transmission)
(set trans_auto part# (val+ 'hTa))
(set trans_auto part gear1)
(set trans_auto part gear2)

(new 'civic_le 'civic 'car)
(set civic_le part engCfg1)
(set civic_le part trans_manual)

(new 'civic_se 'civic 'car)
(set civic_se part engCfg2)
(set civic_se part trans_auto)

(new 'make 'verb)

(new 'honda 'mfg)
(set honda make civic_le)
(set honda make civic_se)

(new 'supplierFor 'verb)
(new 'supplier 'verb)
(set supplierFor reciprocal supplier)
(set supplier reciprocal supplierFor)

(new 'mfg1 'mfg)
(set mfg1 make (block (new 'gear1_m1 'gear)

                      (set (it) part# (val+ 'm1G1))
                      (set (it) cost (val+ '7))
                      (setWRR (it) supplierFor gear1)
                      (return (it))))


(set mfg1 make (block (new 'gear2_m1 'gear)


                      (set (it) part# (val+ 'm1G2))
                      (set (it) cost (val+ '17))
                      (setWRR (it) supplierFor gear2)
                      (return (it))))


(set mfg1 make (block (new 'starter1_m1 'starter)


                      (set (it) part# (val+ 'm1S))
                      (set (it) cost (val+ '70))
                      (setWRR (it) supplierFor starter1)
                      (return (it))))


(set mfg1 make (block (new 'carb1_m1 'carburetor)


                      (set (it) part# (val+ 'm1C1))
                      (set (it) cost (val+ '270))
                      (setWRR (it) supplierFor carb1)
                      (return (it))))

(new 'mfg2 'mfg)
(set mfg2 make (block (new 'gear1_m2 'gear)

                      (set (it) part# (val+ 'm2G1))
                      (set (it) cost (val+ '8))
                      (setWRR (it) supplierFor gear1)
                      (return (it))))


(set mfg2 make (block (new 'gear2_m2 'gear)


                      (set (it) part# (val+ 'm2G2))
                      (set (it) cost (val+ '18))
                      (setWRR (it) supplierFor gear2)
                      (return (it))))


(set mfg2 make (block (new 'starter1_m2 'starter)


                      (set (it) part# (val+ 'm2S))
                      (set (it) cost (val+ '80))
                      (setWRR (it) supplierFor starter1)
                      (return (it))))


(set mfg2 make (block (new 'carb2_m2 'carburetor)


                      (set (it) part# (val+ 'm2C2))
                      (set (it) cost (val+ '380))
                      (setWRR (it) supplierFor carb2)
                      (return (it))))

(; Get civic_le parts)
(; Gets engCfg1, eng_1.5L, starter1, carb1, trans_manual, gear1)
(getRel civic_le part *)

(; Get civic_se parts)
(; Gets engCfg2, eng_1.6L, starter1, carb2, trans_auto, gear1, gear2)
(getRel civic_se part *)

(; Get sum of civic_le part costs)
(; Return 410)
(sum (get (getElemLast (getRel civic_le part *)) cost *))

(; Get sum of civic_se part costs)
(; Returns 530)
(sum (get (getElemLast (getRel civic_se part *)) cost *))
Received on Sun Nov 26 2006 - 07:46:43 CET