Karen: Well, I hope I'm not leading you down the rabbit hole here.
Question 1 **************************** Dr. Codd appears to be freeing us from having to indicate 'levels' with column structures. In his text: "... Q1, Q2, ...,Qn denote immediate properties of each particular SUBORDINATION" I take that to mean 'HOW <subordinate-part> is joined into <superior-assembly>'. SUB_P# could represent a part or a 'sub-assembly'. SUP_P# certainly being an assembly containing parts and sub-assemblies; both values drawn from a table(s) describing each item in detail. No additional levels involved. So in his construction, these might be reasonable values for Qx: Q1: Quantity of SUB_P# in SUP_P# Q2: Joining method (threading, welding, painting) Q3: Resultant Weight Q4: Resultant Shipping volume Q5: Workflow ID ... Qn: Whatever This allows not only a BOM, but a 'Bill of Fabrication' and other metrics useful to manufacture. Question 2 ***************************** That's a thought of mine, extrapolating from yesterday's 'SQL Help' example and a few snippets from Dr. Codd. (Let's just say I'm hopeful). " I claim to have a solution to the general bill-of-materials problem, one that is very concise, that protects the user from iterative and recursive programming, and that provides pertinent integrity constraints as well as manipulative power. However, the recursive join described in Chapter 5 is not a complete solution to this problem." And then, maddeningly: "The more complete solution will, of course, be published later." Bear in mind that this book is Dr. Codd's strong recommendation for which feature set DBMS vendors MUST include. The following sounds AWFULLY LIKE what you showed yesterday: The recursive join of Chapter 5: "The recursive join is an operator with one operand. This operand is a relation that represents a directed graph [map of parent-to-child relationships]. One of the columns of this relation plays a subordinate (SUB) role, while another plays a superior (SUP) role. Each tuple [row] represents an edge [connection] of a directed graph [parent-value-node to child-value-node], and by convention this edge is directed from the node identified by the SUP component down to the node identified by the SUB component. Because joins are normally applied to pairs of relations, it is convenient to think of the single operand as two identical relations. The recursive join acts on this pair of identical relations by matching each SUB value in one operand to a SUP value in the second operand. It yields all of the pairs of identifiers for nodes that are connected by paths in the acylic graph [a map of all the node interconnections], NO MATTER WHAT THE PATH LENGTHS [levels] ARE [emphasis mine]." [Codd 1990, Ch.5, Sect.5-10, p140] Wait ... there's more! And it throws a lot more light. And it looks a lot like R:Base today. If you like, I'll be happy to scan off the relevant sections and send them to you. Gotta run, yours: Bruce Chitiea SafeSectors, Inc. > -------- Original Message -------- > Subject: [RBASE-L] - Re: Exploding BOMs, Revisited > From: [email protected] > Date: Thu, March 22, 2012 2:09 pm > To: [email protected] (RBASE-L Mailing List) > > > Didn't even wait for a bathroom break to read this! > > My comments: I don't understand what his Q1-Qn column are for in his > table. I'm hoping he doesn't mean that you would need to create a finite > amount > of columns for levels.... > > I'm interested in your quote: "and self-joining allows any part to be > tracked to n depth." Is that just a thought of yours, or did Codd have an > example of how a self-join would achieve that? > > Karen > > > In a message dated 3/22/2012 1:23:41 PM Central Daylight Time, > [email protected] writes: > > All: > > > > Bathroom reading, perhaps. > > > > Last week's thread dealt with the question of how one might process a > > BOM of any depth; but the introduction of 'nested sets' and Karen's use > > yesterday of a recursive self-join ("SQL Help") tripped a memory. > > > > Dr. Codd dealt with the BOM question very early on in his development > > and exposition of the relational model, and gave the issue a meaningful > > review (Ch.28) in his book: 'The Relational Model for Database > > Management Version 2' (Codd, E.F. Addison-Wesley 1990). > > > > In response to critics who held forth that the relational model could > > not handle BOM-type hierarchies, Dr. Codd wrote (p453): > > > > "A hierarchy may be an adequate representation in a few manufacturing > > environments, but in many - probably most - it is not adequate. In these > > latter environments, a particular type of part may be an immediate > > component of several types of parts, not just one. A second, > > all-too-rapid conclusion is that a DBMS is needed that exposes > > network-structured data to users. > > > > "In fact, in 1970 I presented [Codd 1970] an extremely simple > > representation of product structure in the relational model by means of > > the COMPONENT relation: > > > > COMPONENT (SUB_P# SUP_P# Q1 Q3 ...Qn) > > > > "where SUB_P# denotes subordinate part number, SUP_P# denotes superior > > part number, and Q1, Q2, ..., Qn denote immediate properties of each > > particular subordination. > > > > "Incidentally, if (p1,p2,q1,q2,...,qn) is a row of the COMPONENT > > relation, then part p1 is an IMMEDIATE component of part p2." [And > > here's the rub:] "The fact that part p is a non-immediate component of a > > part p7 (say) is not directly represented in the COMPONENT relation. A > > fact of this type can be easily derived by the RECURSIVE JOIN operator > > ... > > > > "In a computer-oriented sense, this kind of representation in a relation > > is adequate for all kinds of networks, whether they happen to be pure > > hierarchies, acyclic nets, or nets in which cycles may recur..." > > > > "...the relational representation is probably not the best for use by > > human beings, for whom graphs drawn as pictures appear to be more > > comprehensible and suitable. However, that subject can be discussed > > separately, and handled by separate code, when presenting data to people > > in a form more consumable by people (e.g., the formatting of reports)-it > > has very little relevance to mechanizing the management of data." > > > > **************** > > > > What Dr. Codd appears to say is that ANY LEVEL of BOM subordination can > > be achieved with the simple "COMPONENT" structure described above. No > > extension (add'l columns) is required to recognize n LEVELS of > > subordination; and self-joining allows any part to be tracked to n > > depth. > > > > And he addresses the applicability of 'nested sets' to this application. > > It didn't take long last week to figure out that a) the nested set > > approach is the 'visual', 'human-suitable' approach which he considers > > representational, b) that the right/left adjacency proposed within the > > nested set model wraps a layer of 'metadata' around base data values, > > and c) where a given part appears in two or more nesting paths, the > > model chokes, at best requiring that an additional layer of 'metadata' > > be constructed around data values. > > > > Interestingly, the nested set approach does seem to have value in > > situations where the child-parent relationships are eternally fixed, for > > example, in a condominium complex where the number of buildings and the > > number of units within each building will never (barring catastrophe) > > change; or where PHYSICAL adjacency needs to be described, for instance, > > in a mapping application. Something to play with. > > > > Thanks to Karen, Ben, A.G. and others who sent me down this path. > > > > Yours in learning > > > > Bruce Chitiea > > SafeSectors, Inc. > > > > > >
