Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: vehicle to autoparts relationships

Re: vehicle to autoparts relationships

From: Neo <neo55592_at_hotmail.com>
Date: 25 Nov 2006 22:46:43 -0800
Message-ID: <1164523603.595378.323160@h54g2000cwb.googlegroups.com>


>> 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 - 00:46:43 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US