Re: Hierarchical Model and its Relevance in the Relational Model
Date: Mon, 2 Feb 2015 03:46:17 -0800 (PST)
Message-ID: <29bc5e77-a12a-430d-bf09-fa966f8d63d0_at_googlegroups.com>
James
> On Monday, 2 February 2015 10:49:58 UTC+11, James K. Lowden wrote:
> On Sat, 31 Jan 2015 18:38:26 -0800 (PST)
> Derek Asirvadem <derek.asirvadem_at_gmail.com> wrote:
>
> > > It would seem that once you introduce the average programmer to
> > > a hierarchical filesystem, you can never wean him of the notion that
> > > that's the "natural" structure for data.
> >
> > Could you please give an example of what one of those guys did, that
> > you consider to be incorrect
>
> In two cases that I had direct contact with, the database design
> reflected some form of object orientation. It used the table structure
> to implement an elaborated version of the entity-attribute-value table,
> and self-joins to construct what you or I would have designed as
> tables. Naturally, the design was, er, designed to be "flexible".
>
> The resulting databases were impossible to understand, comprised solely
> abstract nouns, and defeated the system's ability to enforce integrity
> constraints. Queries were tedious to read and executed poorly. When
> I was approached for ways to make it faster (because speed is the only
> thing that every application programmer and his manager understands),
> they were nonplussed when I suggested a Dumpster, a clean slate, and
> class in database design, not necessarily in that order.
It looks like you are side-stepping the request, maybe not. Excellent description, etc, but that is neither hierarchical nor non-hierarchical: any EAV structure may, or may not be an hierarchy. And that description is not specific enough to prove anything one way or the other.
I am not a proponent of EAV, but I have corrected a few EAV systems from the dumpster; added a bit of science; some constraints (you most definitely can add constraints, once the EAV structures have been elevated); etc.
Over here, we do not "write" SQL. I think it was 1993 when I stopped doing that. That is one of the myths that theoreticians have about us, manufacture any fiction to keep the myth alive, to feel inflated. /They/ are used to typing strings of relation definitions, and /they/ think the same of SQL. Yeah, sure THEY write SQL. They do not understand IDEs or models, so they are clueless re what they do, what their purpose is, etc. We push a button on the screen and GENERATE SQL, hundreds of tables, procs, functions, of the current version of the model, with on click.
Tedious. Huh ? It is a data sub-language. If the column names are meaningful, they are long, then the SQL generated is long. If you use an IDE, it fills all that in as you type. The better IDEs provide drop-downs and point-and-click. Costs a lousy fifty bucks. When I walk into a site, if the customer doesn't have one, I down-load a free version of an IDE that I do know, usually the sexy functions are blocked, but the basic functions are all I need.
So, yeah, sure, it may well be tedious for those who live in the dark ages, and type everything, but it is more than silly to apply that impediment to the rest of the planet.
The users, of course, have modern reporting tools, such that the NEVER type.
Now the key element in your example is that they did not use Views. That eases the EAV problem greatly. And if you understand that we GENERATE View definitions, we do not type them, and mess with their hair, then most of the pain (after elevation), most of the tedium, the monotony that you guys mythologise about, is removed.
On one occasion I wrote an extension to the Sybase catalogue, so that we could automate the construction of views, but with good planning and minimising EAV, you don't need to go that far. So I didn't bother to publish it.
Back to the request for example. Ok, so you can't give me an example of an implementation where the person incorrectly implemented an hierarchy for non-hierarchical data (because he though hierarchies were "natural" or whatever); nor one of how you corrected it.
And you won't do the exercise, which would demonstrate several things re your knowledge of the RM, one aspect specifically being hierarchies, as prescribed in the RM.
I looked at your nonSqLite link a bit more. Very good work. But for our purposes, there is no model, and it is not clear to me what structures are in the database (relational or not). I am not sure what a "virtual table" in that platform is (yes, I can guess). Perusing the code, it looks to me that you are using ROW_IDS, which is anti-relational. To be clear, you are /treating/ the data as hierarchical, and your /program code/ handles it, but your /data/, which clearly is hierarchical, is not implemented as hierarchical data. (If I have got any of that wrong, please correct me.) Not a surprise, given that you think hierarchies are external to the RM. I would say that stands as evidence that you do not implement the RM (ie. besides the non-implementation of the hierarchical component of the RM)
To sum:
- given that you reject the HM as HM, the concept of the HM (you have not given me a word that you prefer) - given that you have no specific examples of improper implementation of hierarchies, or corrections thereto - given that you consider hierarchies and relational handling of them to be outside the RM - given that the example of your work presented as "hierarchical" is contrary to the RM, and absent the HM component of the RM - given that you will not demonstrate your ability to implement classic hierarchical data in a Relational DatabaseI have to say:
- to that extent, you are severely hindered in the implementation of hierarchies in Relational Databases - (others are crippled, because have a slew of hindrances, you have just one, and it is a major one)
Therefore my statement:
> First, I happily acknowledge that you are definitely not in the difficult-to-impossible category re implementation of hierarchical data. Actually as an implementer, period.
is incorrect. I retract it.
You are definitely a capable implementer, period. With regard to proper perception of, and the implementation of hierarchies, as they appear in data, and as prescribed in the Relational Model, which has been proved to be difficult-impossible to the theoreticians in this space (including those who write books on the subject), your declarations re your ability have not been demonstrated. Further, you refuse to execute an exercise that will demonstrate those declarations.
Let's try another avenue, in order to avoid stalling on this point. I want to close the issue re the perception and implementation of an hierarchy. Since you are so busy with the frying pan, let me do the work. Give me a detailed example (enough detail to implement; definitions in technical English, not gibberish), of an hierarchy that you or your mentors (authors whom you drink up) declare to be impossible to implement or outside the RM, and I will give you, overnight, the hierarchy, with full (i) integrity (ii) power and (iii) speed, that only the RM can provide, and within the express framework of the RM.
That is second preference, because although demonstrating my skills, and puncturing one of your myths, it is not identifying where your impediment lies; it is not removing it, which the exercise would do, which is why that remains first preference. I don't need to be cured. I don;t get anything out of demonstrating something that I have demonstrated a couple of dozen times.
> Yes, I remember Codasyl, too, and Charles Bachman Overdrive. ;-)
Yes, Charles was never as good without Turner.
> Acknowledged, those systems modelled data as hierarchy. And they were
> useful, sure. I really doubt, though, that they construed their
> systems as being based on "the" (or even "a") hierarchical model. The
> idea was simply implicit.
I have already provided a fair amount of evidence that it wasn't "simply implicit".
> > We didn't have software to draw models on PCs, but we had excellent
> > hand-drawn models, a set of symbols, notation, rules, stencils, etc.
> > given to us by authorities in our field.
>
> Yes, which you very much needed, as I'm sure you also remember,
> both because of the lack of data independence, and because navigating
> the hierarchy required having a clear picture in mind of the database's
> structure.
That is two separate points.
Data Independence. Huh ? That is nonsense. Before the RM, we had the concept, well and truly. It was well-known that a "good" program was "good" precisely because it had data independence, and a "bad" program was "bad" precisely because it had no data independence. That is separate to the fact that, because we worked with IMS or TOTAL or ISAM (for that matter), there were limits to the level of data independence that we could implement. A good programmer implemented the highest level of data independence that the file or database access method allowed. The result was, we could change one or two programs in the system, or one or two files, without having to change the whole system (ie. all the programs that made up the system). Whereas a poor programmer wrote systems unaware of Data Indepence, such that you could not change such systems with changing a large part of it.
In my apprenticeship, working for a TimeShare service bureau, I spent half my life upgrading programs written by customers, such that they had data independence, such that we could make point changes.
We had another form of data independence, within the set of files taken as a unit, or within the HM database alone, or the NM database alone, separate to the programs. If the files were designed well, within the limits of the platform of course, it improved the data independence, if they were poorly designed, it reduced it.
Then the RM came along. Separate to the notion of sets and relations, yes, it gave us specific prescriptions and prohibitions, and a couple were specifically re data independence. We could not employ any of that, until 1984, when a genuine RDBMS platform was delivered, so until then we were implementing pre-relational data independence, a well-understood concept. And after 1984, sure, we implemented a much greater level of data independence, that of the RM.
And again, those of us who followed the RM as Law, implemented way more Data Independence, etc, than those of you, who picked and chose the bits of the RM that you understood and accepted. Refer my interaction with Jan. He is now accepting far more from the RM, and that deems his work from ten and twenty years ago dead set wrong. Meanwhile over on this side, we did not take twenty years to accept it, we always accepted it.
Clear picture in mind. Well that is my point, not yours. You need a "clear picture in mind" if you are going to implement ANYTHING, if we are going to navigate ANYTHING. A database today, a pre-relational file system or database. A network topology. That is why we have IDEF1X, a standard. Instead of funny pictures in Visio and UML. In those pre-relational we have different diagrams to provide us with a "clear picture in mind", and a standard for the symbols, which were available as stencils at the newsagent. It proves once again, we had a MODEL.
Yes, the vertical screen made people who hadn't seen one, just stop and stare. The first one had just the one icon, a page with a dog-ear.
> the distinguishing
> feature of the relational model that it's an inferential system.
Yes. Agreed. As long as you do not mean ONLY inferential. It is perfect for OLTP as well as DSS/OLAP, in the same database.
> If
> we define a "model" as something with an algebra or that supports first
> order logic, then we have to exclude the hierarchical model from the
> set.
I think we have argued that one to death. Yes, agreed. But it is silly to expect that level of definition from something that pre-dates the first occurrence of that level of definition. Very, very, silly. Look, the electric guitar was invented, say in 1960. It is silly, very silly, to say that guitars prior to 1960 were not precise because they did not have the precision that was required to use electricity. You are not that silly.
Second, you keep repeating your definition of "model". Sure it serves your purpose. Sure, I agree, that under that definition blah blah blah. But the problem is, you are denying that any other definition could be possible, you are denying historical facts, physical evidence. Very unscientific. There, you are crossing over from your esteemed scientific person into a religious freak.
> > We had both the HM and the NM as models, with a set notation, and
> > they certainly had a scientific and theoretical basis.
>
> I would like to know more about that. To my knowledge it's not
> possible.
Done to death. Evidence given. Read again.
It is not "to your knowledge", it is despite your knowledge, and to your religious convictions.
> > So what ? A scientific person can, look at a graph, a tree, and
> > instantly determine that it has integrity or not. What theory is he
> > practising ? What algebra is he using ?
>
> None, and he's completely at a loss for making any logical inferences
> from it.
Well, let me assure you that when I look at a graph, a tree, and instantly determine that it has integrity or not, I am practising a scientific theory. And further, I make logical inferences from that. And further, I am not the only person on the planet who can do that. Ok, I accept that you can't.
And I don't have the time to explain how it is done. Rest assured, I do not take drugs; or drop ACID; or drink the Kool-Aid.
One specific thing that a person who is reasonably familiar with the RM would understand: In the scientific exercise above, I am applying Codd's __Hierachical Normal Form__. But you are in denial that such exists.
> I'm saying is that [the] mathematics [for graph theory] doesn't exist. If 44 years hasn't been
> long enough, I suspect "a year or three" more won't be, either.
You missed my point. And you are mixing up my comments re the void of theoreticians in the field of databases vs the field of graph theory.
For the relational database field, they have never tried, because, like you, they have blind spot there, where hierarchies sit, because your religious scripture says it does not exist. For the forty four years since relational theory has made made public.
Re the graph theory field, I have no idea what their timeframes are, it just isn't that forty four years. While it would be fantastic if they applied Relational Theory to graph theory, etc, etc, they are under no obligation to do so. And that concerns The Relational Theory, after Codd. If you are talking about one of the 56 "relational theories" other than Codd's, that are so beloved of theoreticians who produce nothing in this space, then it is a Very Good Thing that the graph theorists do not touch it with a ten foot pole. They have not succumbed to some strange religion.
Let's put it another way, strictly from the pov of a scientific high end implementer. After I understood Codd's RM, I perceive all data as Relational. I don't need a RDBMS, I implement all data in Relational form, 3NF on steroids (all the NFs that are written plus all the NFs that will be defined in the future. In any language or platform. I have done in ISAM, COBOL, DIBOL, various incarnations of C; awk, etc. When I describe data to people, outside an RDB context, I give them an IDEF1X data model (minus the attribute level), and they love it.
The RM is about a new way of perceiving data. It is not about an RDBMS. No data is excluded. I believe you will agree with me 100% thus far.
Now for the kicker. Please leave your religion for a few minutes and think scientifically. Since no data is excluded, that includes hierarchical data. Therefore hierarchical data is included in the RM. Don't ask for the Lemma.
> As far as I know, there's nothing like e.g. SQL EXISTS for
> graphs. There's no way to say "find all <graph expression> where
> <subgraph expression> = <graph expression>". Sure, there are ways to
> iterate over the nodes. There are even things like XPATH to find nodes
> having particular properties. But the solutions are all ad hoc and
> complex, and still come nowhere near what RM does.
So sure, if I were to implement a bunch of point sets for a customer, I would translate all that data into RM terms, give them vectors, dimensions, or whatever they need to IDENTIFY their data, and implement it in a RDBMS. It isn't ad hoc, it is formal. It does everything the RM states can be done for data. No funny constructs. All data instantly available via a single SELECT.
I realise you mean theory, above, but my point is, I don't need a theoretician to define it theoretically, in order to implement it. And that identifies yet another point where the theoreticians are crippled (note the other thread). Norbert believes (unscientifically) that he has way more work to do, before he can implement. All of you guys cannot understand that I can implement tomorrow, if you can define it in English today, supported by the evidence that consists of the entire physical universe (ie. other have done the same). You spend your lives defining something in gibberish, with all sorts of artificial limitations, that you impose upon yourself. This is why I am saying it is a religious issue for you guys, and the religion is false.
> I don't feel qualified to comment on [Norbert's] work. I don't know anything
> about topological spaces. I was waiting to see how the dust settles.
I am certainly not qualified either. He asked for, and I gave, an informal, non-theoretical, practitioner's review. You guys just do not get that painting rectangles on screen is twenty-to-thirty-year-old hat. Oh, oh, wait. It is me who doesn't understand. And even worse, it is you who can't communicate.
> I think we just mean different things by "hierarchical model", and by
> "model". I still say Codd invented the term simply to contrast it with
> his work. If you believe otherwise, please cite one paper in database
> research before 1969 that refers to it.
>
> I acknowledge the existence of IMS et al., and recognize that the data
> were modelled on a hierarchy. I'm only saying that "model" lacked any
> of the expressive power of the RM, to the extent they're not really
> comparable.
Beside Done to Death; Read Again; Silly Requests; Denial of Evidenced Historical Facts; etc, you are contradicting yourself, severely, between your two paras.
And I never compared the HM and the RM. The RM superceded the HM, while retaining everything that we had learned about data up to that point (not, the storage, Christ help me, not the literal model, Baby Jesus, but the essence). If one were to compare, which is stupid, the HM loses badly, for all the reasons you have mentioned. Plus one reason (at least) that you are unaware of. It was not a win-or-lose affair, it was a succession. Like the auto-mobile-carriage was a succession of the horse-and-carriage. Comparing the two is silly.
> Hence it's a pretense even to call it a "model".
For you. Not for the rest of the planet.
> > Ok. So then what, in your considered opinion, is the natural or
> > "natural" structure for data, or is there none ?
>
> Not to be sophistic about it, but I would say that data [hierarchies?] don't occur in
> nature and have no natural structure.
That sounds like you think that data (is therefore) fragments, isolated from each other, with oh, a relationship here, and ah, another relationship there. The very opposite of the RM.
> As a human construct, they
> have no more "natural" structure than do novels or cities.
Ok. So, according to you, there is no relation between novels or cities or novels-and-cities. And you say, you got that from the RM. Gee, I got the exact opposite. But hey, I am just a dumb practitioner, you guys are inflated theoreticians who produce nothing. I can't match that level of success.
> > Question for you. In one para or less, what is your considered
> > opinion of the Alice book.
>
> You're on record as calling it an "abortion".
Yes. And a Cancer Causing Agent, to the extent that it is used as a "textbook". Ready evidence: it has poisoned your mind re hierarchies are "not possible" in the RM.
> I don't know what about
> it earned your emnity.
I won't enumerate here, but to sum up, it is based on lies, Straw Man arguments, denial of reality, denial or the RM. To name just a few. And I can provide evidence for each of those charges. One really stand-out point is the chapter on the "relational model": 70% everything but, and in the remaining 30%, none of the main concepts of the RM. Fraud. Crime of Omission. Crime of Commission. Misrepresentation.
> I have an interest in database theory, and
> query language complexity. I would like to see SQL replaced by a
> language that is simpler, more expressive, less verbose,
All very good ideas, but about twenty years too late, in that, while all that mattered twenty years ago, it doesn't matter now. We do not type SQL. We have machinery that does that for us. We work ate a level of productivity than is beyond the imagination of typists and their mythical, untested, ideas about the planet.
> and that better
> represents relational algebra.
Yes, I would definitely like that.
Given your demonstrated skills, I have a suggestion. Spend a month or three, and write a C program, that parses a text string (that is supposed to be one of the 56 RAs that you guys have, and produces SQL verbs, that are executed against any SQL (or NonSQL) platform, and you are home and hosed. It can run only any SQL platform, and any range of functions that the platform provides. Use libraries for the parsing of course, otherwise it will take years.
That way, you can implement FOL, SOL, 42OL.
Just do not, please, under any circumstances, implement anything less than the simple FOL in the RM.
The TTM cult have produced nothing for twenty years, not even 1% of SQL, but they promise to exceed it. They have a Toy Language semi-described, nothing defined yet. ANd of course they keep changing it. I hear they are once again, arguing about Typing, after dropping it unresolved four years ago (when I was there): they haven't figured out that the Typing is already in the one-and-only place it should be, the database. They are still worshipping at the Tower of Babel, it should be no problem to beat them to it.
Just don't build a monolith, we are in the age of components and layered software. Babel is about two thousand years out-of-date. It should be a straight program that runs on a PC (Unix and Windoze, in whatever order suits you), that calls a resident SQL client library.
> I read Part E with great interest,
> although I don't claim to have mastered the subject.
I haven't got to that yet. I can only handle small doses because the dishonesty and fraud, which happens on every single page, sometimes every paragraph on the page, drives me to commit sin, ie. more than the bad words, vomiting and spasms. So I have to stop, perform an act of contrition, and write it up for my next confession. But that doesn't tell the story, because we can't keep committing the same sin over and over, it means we are not sincere about giving it up. We are supposed to avoid the occasion of sin, which means maintaining a state of Grace is very simple: don't read the book. But for the sake of identifying and dealing with the cancer in my beloved profession, I have a dispensation to enter the world of the devil, and deal with his children. Of course, I believe in material penance, and that takes a lot of time. So the sum is, for every six or seven pages that I do read and ingest from the cursed book, not counting time spent in the toilet, I have to perform about 12 days of labour.
Basically, it is the penance that I have to do to redress all the sinning I have done in my life, which I gratefully accept, so while there is no problem re motivation, etc, there is a problem with the rate of progress. Compared to my three years penance that I performed at the TTM Kingdom Hall, in some ways it is worse, in other ways better. Eg. you can't pin a TweedlDum slave down via email (which btw is one reason I appreciate this interchange), in their own church, and of course, they never go outside. Vs AHV, where the crime, the evidence, is in black and white, permanent, so I can him the freaks down, and stitch them up. Having just typed that, I realise now that my time at TTM was training, so, thanks be to God.
Thus far, I am up to ch 11, with parts of 14 and 15, and ch 21.
I won't be reading part E.
And it must be said, you and I are reading it from opposite sides of the fence. You are infected, enslaved, lapping it up, for the divine juice that you have been tricked into thinking it is. I am protected by the real thing, handling it with a mask and gloves, like the dangerous contagion it is. All tolerable, my small contribution to humanity, in order to arrest the enslavement, the subversion, of good young minds.
> I think it was from that book that I learned that there are some
> problems that cannot be expressed in FOL. Hierarchies are in fact an
> example: some problems require recursion, which, as you know, is
> inexpressible in relational algebra.
Excellent! We might have found another avenue (given you rprivate definition, and that you refuse the exercise) to deal with Hierarchies vis-a-vis the Relational Model. Here, I will happily do all the work, there is nothing for you to do, except to communicate the need, the problem to me.
First, let's get that last clause in your para out of the road. Yes, I know, but what A RA or THE RA can or cannot do, is irrelevant. No idea why you think it is otherwise.
Second, isn't it amazing how the Holy Ghost works! Here I am, 57 years old, and still sometimes just blown away by that. There I was, just four paras further up, believing that the thread had stalled due to your refusals, but I praised God, spat out the devil, and acknowledged the work for what it is, and boom, four small paras later *you* give *me* an opening. Let me go and say a prayer and return.
Ok. So, given the previous interaction and stall, we need to draw up some boundaries. There has to be clear success or failure. None of this unscientific refusal to accept established definitions, no denial of physical evidence.
- So will you please confirm, if I show you how hierarchies are dealt with in the RM, handled properly by a reasonable implementer, implementing the RM, then it will prove that hierarchies are catered for in the RM, and that application of the RM re hierarchies is as easy as you see it being done ?
- This one might be harder. If you see (ie. not deny) that the constructs and concepts that I use, that are in the RM, are in the HM, will you concede, graciously, that the HM (in essence, in concept, in spirit) exists in the RM ?
- Therefore the HM lives in the RM ?
- And since you having been denying it thus far, therefore, you did not previously know [3]
- Given [1][2], there was that content that you did not previously know.
- Therefore, to that extent [1][2], you did not know the RM.
- If I give you a full solution as Relational Db tables, and a data model to go with it, using an existing commercial SQL platform, and SQL DDL, will you concede that the solution is executed in current, Relational, SQL, RDBMS ?
- If I give you a full solution including the code required to perform whatever tasks you give me (which you perceive as currently being impossible), using an existing commercial SQL platform, and SQL DML (ie. 100% SQL, a calling language used for calling and presentation convenience only, no subject code), will you concede that the solution is executed in current, Relational, SQL, RDBMS ?
- Therefore whatever your teachers (other than Codd) taught you about the combination (FOL; Hierarchies: the RM; can't do), is wrong, wrong, wrong.
- ANd the corollary, given that my solution is 100% RM, that Codd is right.
Iff you agree to the above, then please give me a real world example problem to work with, otherwise, please discuss.
Single relations are unacceptable, that is the classic trick that frauds use to erect their Straw Man arguments, we prefer lots of detail, even ten tables. In the three case studies that I use for my courses, each starts with ten or so tables, as we progress, it expands to 20, 30 tables, and it finishes with 80 tables. Nothing of value can be taught or learned, from a few relations. Except fraud.
> I have
> not read a theoretical treatment of SQL's recursion.
Why does one need a "theoretical treatment" of recursion ??? Do you have a "theoretical treatment" of the recursion in C ? For awk scripts ?
SQL is a data sub-language, period. It is not a language, why would anyone expect a full-program capability from a data sub-language ??? That is as stupid as writing a cursor in SQL, which is a set-processing engine (at least in the commercial end of the market).
You might be making the same mistake as Jan, that I enumerated in detail ? You two are reading the same books, lapping up the same poison. Failure to observe the scientific Architectural Principle in the industry, of differentiating /Data/ vs /Program/.
> It seems like a
> good job to me, a minimal extension.
No idea what you mean. The recursion is in the server, not the SQL code. Same as for an awk script: the recursion is in Unix, not in the awk script. There is no recursion "in" SQL. Of course, both the awk script, and the SQL function, must be written with the awareness THAT it will be executed recursively, but that is not recursion IN SQL or the awk script. We have had it since 1984.
> But it also seems harder to use
> than necessary.
How would you know ?
Or, are you talking about some freeware/shareware/vapourware NonsSql that has recursion IN the NonSql ? Such as those that have "deferred constraints checking", etc. Hilarious. They have read the same book, lapped up the same poison, ignored the same scientific Architectural Principle, and written puke into their program.
-- But thank you for answering my question, and your excellent comments. I do not mean to take your answer apart, I was just adding my comments under yours.Received on Mon Feb 02 2015 - 12:46:17 CET
> > In one sentence or less, how relevant is it to the field of
> > Relational Database design.
>
> Not relevant at all.
Excellent. Like Harry Potter's Book of Magicke. Only for HP fans. Not for the Christened ones. Cheers Derek