Re: Fails Relational, Fails Third Normal Form

James

> On Monday, 16 February 2015 12:08:01 UTC+11, James K. Lowden wrote:
> > On Thu, 12 Feb 2015 06:55:38 -0800 (PST) Derek Asirvadem <derek.asirvadem_at_gmail.com> wrote:
>
> Having read over your post several times, ISTM we're more in vociferous
> agreement than not.

Yes, very well put.

And also, there are a few subjects, entire subjects, that we have little in common about.

> Your focus is on designing a database -- a
> particular one, based (of course) on a known enterprise of interest.
> The academics you malign

Excuse me. There are no academics, no theoreticians, no scientists, in this field. There hasn't been, since Codd left.

Sure, there are a few pseudo-scientists, fragmented theoreticians, isolated academics, in this field. Yes, they have produced nothing. I don't normally malign or insult anyone. However, when these sub-humans start (a) their various and sundry frauds, (b) talking down to and insulting practitioners (who have produced something substantial; over twenty databases for me; millions if you count all of us), which is an insult to the mind, yes, that does get up my nose, and yes, I insult them in return. Never as a start, it would be a sin.

> are describing -- and still searching for --
> a way to do that automatically. You have no particular beef
> with them except that from your point of view they've produced "zero"
> in twenty years,

Er, 2015 minus 1970 equals forty five years.

> while all along you've been doing just fine, thanks,
> without said algorithm.

Yes, for those same forty five years. Without said algorithm which is stupid as detailed previously, and with Codd's algorithm, which is sound. Without their non-FD-fragments and with Codd's FD/3NF. Without their pipe dreams, and with existing modelling tools. Yes.

So, your statement is false. I and millions of other RM adherents have been doing fine, precisely because we have been served by other RM adherents, including vendors. We are doing fine in the absence of a stupid algorithm, yes.

Beef. I have many, and I have posted to that effect. Crippled. Frauds (two levels, first by using private definitions and therefore isoalting themselves; second, by propagating falsities about the science that does exist). The dozen or so book-writers commit an additional, third fraud. Interfering with what they are clueless about. Damaging the industry, that exists solely due to the capabilities of others. Failing to provide an exposition of the RM, and instead demeaning it. Fiddling around with non-FD_fragments, which was supposed to produce some marvellous result that would save the planet, and not producing a result of any kind. Dreaming about things (such as automating the description/design of a db), which is completely outside their scope, as well as outside their demonstrated capability (it is twenty years for that one, yes). Not an exhaustive list, but a good start.

Other than that small herd, no, I have no beef.

> I think there's a there there. We should be able to generate
> normalized designs from a set of columns and constraints.

Pipe dreams. Outside their area of responsibility. Outside their demonstrated capability. There is just too many steps to think of, that you are hilariously reducing to a single step. Based on an absurd, and stupid, algorithm that has not produced a key. Three weeks, not one single key amongst the lot of you. Previously detailed. Please read.

> I also think there's essentially no academic interest in the hard part:

Exactly right. There never is. Academics who produce results, who work the hard part, work for vendors.

The other kind put sticky brown stuff in their pipe and smoke it. Then they contemplate the curling smoke. Then they write relational algebra to describe said curls.

They've never kissed a girl.

> the problem of devising a convenient notation (never mind tool) for
> capturing constraints. To the extent they consider it at all, language
> people consider it a database problem, and database people likewise
> consider it out of scope, either a trivial non-problem or one belonging
> to language design, not database theory.

Ok, but you are counting only the theoreticians have have produced nothing. Those who have produced something, do not take either of those positions. Eg. Codd didn't, he covered both sides.

Eg. My colleagues and I don't. I have no problem at all with the existing tools, precisely because they are tools, not a db design engine. It is no problem at all to draw up this or that diagram, using a recognisable notation, to cover some aspect that an IDEF1X Diagram doesn't convey (the IDEF1X Model having those aspects implemented).

> Absent academic results, industry produces the language poverty we
> currently live with: SQL, with all its warts, and proprietary GUI tools
> for design.

Poverty ???

You are living on another planet. There ain't no poverty here. Sure, I haven't answered your points re SQL in your previous posts, but that does not mean we are not served. (I am working on the response.)

Take the RM, Codd invented it, IBM and Sybase built it. Just like the RM, Codd invented the data sub-language for accessing the RDb, IBM built it, and Sybase built it better. And now, in case you haven't noticed, there are thirty or so SQLs and Non-SQLs and Pretend-SQLs.

There ain't no poverty here on this planet. We are well-served. We have the tehory from forty-five years ago, and the implementation platforms are very good, and constantly improving. They have taken the RM to the end, and implemented all of it (the bits that the pig-poop-eaters say is "incomplete"; the natural extensions that are exposed only from faithful and devout use).

The only poverty that I know of is the staggeringly isolated theoreticians who allege that they serve this space, who [repeat above] are (a) ignorant of what we do have and (b) dream about what we should/could have. Waste of oxygen. Those creatures wail about SQL, as if it was their dream that doesn't exist, and moan about it not having the features that their dream that doesn't exist has. The word is schizophrenic. The argument is Straw Man, ok, a second generation form of Straw Man, from the same freaks who have forty five years experience with the Straw Man.

Meanwhile, back at the farm, we just take up the tool, and use it to do the job. We don't cry about the tool not being a twenty-something beauty queen. There ain't no poverty here on this farm. We are not interested in a tractor that would drive itself and plough the acreage, while we sit in front of the telly with a remote control.

Notice, by the Grace of God, I live in the lap of abundance. Notice, you are trying to tell me that I live in poverty. Epic fail. You have to sell that incoherent idea first, in order to justify a replacement.

> I doubt you find much to disagree with there,

Refer above.

> except that you'd give
> SQL higher marks,

From your previous post (the SQL part which remains unanswered), I believe that if you marked SQL against what it is declared to do, and not what it is not declared to do, which is your ongoing lament, you and I would give it the same marks.

> and you have automatic database design generation
> filed under "pending results".

Worse. "No results in forty five years, twenty five years since results were promised". Additionally stamped "No results expected, due absence of academics in the field". Moved to the archives, stamped "Pipe dream, required steps never thought out."

> Below I try to clarify a few points and offer some particulars on the
> super/subtype design we used for securities.

Hopefully some meat, in this boiled rice diet.

> Finally, I put it to you
> that the design both obeys all rules of normalization and illustrates
> the need for deferred constraint enforcement.
>
> > > Actually, I'm sure you agree. By "DBMS" I mean "database management
> > > system". It's a machine, and it has no concept of meaning.
> >
> > Ok, so you don't means DBMS.
>
> I'm using the term "DBMS" literally, in the way I daresay everyone on
> c.d.t. understands it. I wouldn't dream of using a private definition
> any more than you would!

Sure. But it is a dare, and it is drawing a long bow. Given what you really mean (preceding para, not repeated), you are clearly treating it as the theoretical machine, and only the theoretical machine.

> > You mean the theoretical concept of a DBMS. Abstracted to the point
> > where those statements can be true. Ok, I agree.
>
> It's not abstract in the least. I'm describing the very DBMS you're
> using at your current place of employ.

Stop being silly. You are describing the theoretical machine, quite transparently to serve your theoretical purpose, and you have limited the description to machines that do exist. Fine with me, so far. But when you declare that the two are the same, you leave the norm, and enter the asylum.

My Sybase ASE is about one million, may be two million, TIMES different to your in-theory-we-should machine.

> Maybe you find it useful to think of the DBMS as "understanding"
> things, of enforcing rules as you conceive them according to the
> meaning you attach to the labels you attach to the columns. That's OK
> with me.

Ok, we are on the same page.

> It's something else entirely to claim that fiction is somehow
> more real, more concrete, than the actual reality: that the machine is
> only a machine.

Now you are running around in a circle. You stated, and I agreed, that the machine is only machine. No idea what you are alluding to re the "fiction".

> It can no more appreciate the meaning of your database
> than it can the beauty of the Mona Lisa.

Ok, back to the same page.

> > Here I am defining two discrete levels of meaning:
> > - one within the table itself, relative to all the keys and
> > attributes in the table (at whatever level of progress; whatever
> > iteration)
> > - a second, within the context of all the other tables in the
> > cluster, that it could possibly relate to (ditto); reworking the keys
> > overall; the hierarchy; etc
>
> Thank you for explaining that, because I think it will help clear up a
> misunderstanding.
>
> I cannot accept these defintions of "meaning". I don't dispute they're
> important. They're just not what "meaning" means in the context of
> database design.
>
> When I use the word "meaning", I'm talking about what real-world
> objects the terms in the database represent. Remembering that the
> machine is just a machine,

(Good, we are three times on that one page)

> recognize that every label is arbitrary; to
> the machine, the column may as well be "x" as "price". But we human
> beings associate meaning with "price". To us, it means something. In
> fact, often it means several somethings.
>
> The art of database design is to develop a single shared meaning for
> all the relevant terms in the enterprise of interest.

Whoa. That is a massive new declaration, with no supporting arguments. I am not saying that I reject it, just that it is a nice idea, unformed at this stage.

> (IMO that fact and its importance is generally under-appreciated. I've
> never seen it discussed in any depth in any database-design or
> project-management treatise, and I've read a bunch.)
>
> Until and unless we know what e.g. "price" means, we don't know where it
> belongs in the database schema. Probably it needs to be associated
> with a time, or at least a date. It may be qualified to distinguish it
> as the open/close/high/low/composite/bid/ask/trade/purchase price. Is
> the price relative to an exchange? Does it apply to a class of bonds
> rather than a particular one? And so on. That is the meaning we use
> when we design our databases. That is how we know what's a key and
> what's not, etc. Meaning doesn't derive from the database design;
> meaning exists without the database (in both senses) and is expressed
> in the design.

Whoa. After that [six paras] lead-up, I was expecting you define what /you/ mean by "meaning", given that you reject what we mean by "meaning", while acknowledging that our meaning is important. You didn't.

First, I agree with most of that.

If I take your para above as almost-definitive, then my first-level "meaning" includes all of it, every single bit. And anything else in that vein.

So I am at a loss to understand why you excised that out of my "meaning", or considered it different. My "meaning" includes all of your "meaning", PLUS:

a.  differentiation at the intra-table level vs the extra-table level
b.  determination of the Keys DURING the Normalisation/modelling exercise, which is limited by [a], hence the iterations; the back-and-forth
c.  rejection of your non-FD_fragments, which would reverse the process and stand it on its head (not coincidentally, the way the devil is depicted in religious texts)

> That is the meaning we use
> when we design our databases.

Yes.

> That is how we know what's a key and
> what's not, etc.

Nonsense. That is how you do it, and you don't follow the RM, you have only RFSs.

That is not the way we do it. We follow the RM, and we work with IDENTIFIERS, PRIMARY KEYS, etc, that identify Atomic Facts. All three of which you have demonstrated, you (all of you) have no experience with.

So we are executing a form of Normalising/Modelling that is beyond you. The entities we arrive at in the first phase (Key Determination; Atomic Facts), will be completely different to the entities that you arrive at. The fact that I solved Köhler's and Hidders' problem in this first phase alone (no attributes, no second phase), stands as evidence that you (plural) have no understanding of it.

The second phase, now that we have correct Keys, now that we have solid pegs to hang our attributes on, is easy, and you have articulated that well, we are in agreement there. Yes, yes, all types of meaning, ours and mine combined. And notably, again, two levels, if we don't have good pegs to hang our saddles on, we have to build them first.

> And that is what we mean when we speak of "meaningful results" from a
> query: a logical conclusion derived from a set of facts stored in the
> database. To the machine, a bunch of tuple-types, devoid of meaning. To
> us, yesterday's returns, the very stuff the enterprise of interest is
> interested in.

Ok.

You still have to keep whatever meaning there is in the column names, etc, and improve them during the process, etc. But you stripped all that out at step 1 (for academic reasons, yes ?).

And now you want the machine that is devoid of meaning, to answer a query that has a lot of meaning.

Ok.

Just don't try to sell that machine outside, in the real world.

> > > I'm not being pejorative: The "jumping out" is the practice of
> > > associating meanings with (column) names and deciding what
> > > identifies what.
> >
> > There you go with that word again. Yes. Agreed.
> >
> > Which is why I say your algorithm is stupid because it strips the
> > meaning out of the names that require the meaning to be maintained;
> > rolled over the tongue; and determined; in the context of all the
> > tables in the cluster; not alone. It is self-crippling.
>
> Count me out. I design databases much as you do.

(The evidence is, no you don't, our databases could not be more different.)

> I determine which
> columns are unique (PK, UNIQUE), which attributes are required and
> which not (NULL), what cardinality (FK). Print, review, repeat.
>
> Implicit in that process is a set of functional dependencies. I know
> they're there; I could isolate them if need be. But they don't drive
> my process. They're a formal model for my "is this a property of
> that?" question. As you put it,
>
> > Whereas the Codd algorithm retains the meaning, finds the Key first,
> > then uses the FD to validate the determined Key, then the attributes,
> > etc. So yours is bottom-up, devoid of the meaning that you claim is
> > relevant, and ours is top-down down with meaning, and the meaning
> > gets clarified (eg. we improve the names, improving and
> > discriminating the meaning) during the process.
>
> Everyone agrees that the problem of discovering the FDs is
> non-trivial.

You mean your non-FD-fragments ? Sure, for the fools that use such, who have the process inverted, sure, it is non-trivial. And hard. And they dream of a machine that would do it for them.

You mean the real FDs with der Codd algorithm ? Nonsense. The process is trivial. How do you think, in two evidenced instances (PDFs with summary results posted; details in several threads), I was able to solve the entire problem, and elimiante the entire proposal, in ten minutes flat ? Magic ? Intuition (codeword for not setting brains in the toilet while working) ? No, just the algorithm, the method.

We do not "discover" non-FD-fragments. We Determine Keys, and then use the FD Definition to validate the Keys.

THe FDs (or the non-FD_fragments) do not drive our process. Fact determination, of which Key Determination is central, drives our process.

You guys have zero understanding of the process, you have a theoretical understanding of a few fragments of the process.

> Beyond the problem of knowing there is such a thing,
> there's the trick of extracting them from the incoherent thinking that
> passes for "business rules" in every organization. (That's not a
> remark on anyone's intelligence or seriousness of purpose. These are
> human beings; no system exists to ensure referential integrity within
> or among our skulls.)

Excuse me, such a system most certainly exists, and further, it works brilliantly. But you guys don't want to hear about it, you deny that system.

Without that system, which cannot be employed in the requirements analysis phase, although far less people there than here deny it, then:

We don't need tricks. We teach the users as we go, what we are doing, repeating what we think they said back to them, we show them the model (it is an official tool for communication), and we go back and forth. Many iterations, with meaning (all senses) increased with each iteration. Each side gets educated about the other side.

For me, it is not an extraction process, it is a relationship building process that starts at the beginning, and ends, not with the database delivery but at the sign-off three months later. All the meaning is built up during that period (six months to 24 months), and built up consistently, resolving issues, errors, personalities, communication styles.

> > > > - I am saying the theoreticians in the RDB space are stupid
> > > > because they are using a hammer for a task that calls for an axe.
>
> You would agree that if FDs were sprinkled down from the sky, that it
> would be nice to apply an algorithm to them to generate your database
> design.

If you non-FD-fragments were sprinkled down from the sky, I would stay inside, and make sure the dog was inside. The tree-huggers and the meth-heads can run around collecting them. I have stated severally, that I couldn't care less about the algorithm, because (a) it is stupid and (b) has produced zero results in three full weeks. And for the new purpose you present in this post, hilarious, because nothing less than a full-blown Artificial Intelligence system can "generate a database design". you cannot substitute the human mind with a stupid algorithm or three, you need full AI.

> What you're mocking isn't the logical process they would
> employ but that they are, as it seems to you, working on a process with
> no input.

I am mocking both, and the mocking only came later, after repeated attempts to shut the stupid creature down, for being too stupid to contemplate, failed. I also mocked the fact that you excised the meaning at the beginning, and now you are crying about it being absent (no input) at this stage, where emeaning is as you claim, important.

> The difficulty, in your experience, isn't in deriving a
> design from a set of FDs, but in discovering them in the first place.

Please stop putting words in my mouth. Just read the words I gave. I do not think that my powers of expressing the written word are lacking.

1. I didn't say "difficulty", I said "stupid", "not thought through". I said "impossible", especially given the demonstrated prowess of the people concerned.
2. I didn't say "deriving a design from a set of Non-FD-fragments", I said your whole non-FD-fragment method is stupid, stupid, stupid. And if and when their purpose is described to me (ie. other than what you have given, which is, "gee, golly, gosh, they are really important"), I will entertain the formation of another considered opinion. I also said you have the process backwards, reversed.
3. I didn't say "discovering non-FD-fragments". I said, I have no problem at all, "discovering" FDs, the process is trivial. I said I DETERMINE the Keys first, and use the then TRIVIALLY identified FDs second, to validate the keys and attributes.
4. I said, there is nothing that Codd and Derek's FDs have in common with your Non-FD-fragments. I said, they cannot be compared. Do not apply anything that I said about FDs to your non-FD-fragments, it will elevate them, artificially and fraudulently.

> > Wouldn't it be better to retain both levels of meaning, all the way
> > through ?
>
> If you're designing a database, sure. If you're designing an algorithm
> to design a database, no.

That is too funny to contemplate. But I am sure you had a straight face when you wrote, and you still do when reading this. I am not sure which is funnier, the staggering statement, or the fact the you said it with a straight face.

> I think the theoretical problem with the "Codd and Derek method" is
> that it's not exactly an algorithm. The process terminates when you're
> satisfied with it, when you don't see any more problems. An algorithm
> terminates when a specific condition is met. For a machine to evaluate
> it, that condition has to be clearer than "looks good to me".

That is a very flimsy, and single-marginal-point, method for suggesting that it is not an algorithm.

You cannot write an algorithm than terminates when the <total_unresolved_conditions> equals zero ? After giving it a long list of specific quantifiable conditions to be resolved ?

But you are going to write an algorithm that designs a database. Without meaning. That answers queries that are heavily imbued with meaning.

It is an algorithm. Read all about it in the RM, and in books that describe Normalisation (NOT the NFs).

> > Having just one algorithm , that is tractable is not an economical
> > position to be in.
>
> Perhaps. And if your method is an algorithm, that's the position
> you're in, no? Because there's no two ways to do it, right? ;-)

Well, if the algorithm is good, sure. If you haven't canvassed the alternatives, no. But if the algorithm is bad (and yours in abysmal, zero results), don't waste your time, stop. And address the problem again, so as to have more clarity; more definition of the problem; and the algorithms will come from that.

> > Any problem is tractable.
>
> You are of course aware that some problems are not tractable. I assume
> you mean something like "all databases are designable", which I'm sure
> is true.

No. I meant all defined problems are tractable.

If there is a problem that you consider intractable, I say, flatly, you simply have not defined the problem. During that scientific process of defining the problem, you will identify measures, methods, such that the problem is tractable, and the progress made can be tangibly measured.

I agree, your non-FD_fragments, backwards approach, to the "problem" of identifying keys (let alone designing a database), is intractable. Precisely because it is ill-defined. Further, I repeat, there are at least 12 large steps (separate and discrete "problems") that you have collapsed into a single magical flute. Ill-defined squared, cubed.

> > > ERwin is a good tool, the best I ever saw. [...] It would be nice
> > > to define a relationship-rule in a symbolic library, and be able to
> > > apply it to a given set of tables.
> >
> > You must have been using a very old un-maintained version. By 1995,
> > it had all that and more. The macro language is much more powerful.
>
> We were using the current version. I guess I wasn't clear wrt the
> macro language. Yes, the ERwin macro language was quite powerful (and
> arcane). It was also trapped in the ERwin tool. I would have liked to
> have had a database-design macro language independent of the tool,
> something like m4 for databases.
>
> [long ERwin discussion omitted]
>
> I concede I was oversimplifying to some extent, and found ERwin guilty
> of not solving a problem it wasn't designed to solve,

Good that you see that. Now see if you notice, you are doing tthe same thing with SQL.

> namely checking
> the model for normalization. To the extent it allows the designer to
> work at a higher level of abstraction and avoid repeating things,
> that's great. We remain a long way away from a tool that does
> automatically that which could be automated, but which ERwin leaves to
> the human brain.

Sure. But some of us are not waiting for that magical mystical tool.

> > > I wrote macros to generate triggers to enforce relationships that
> > > couldn't be declared, such as sub/supertype relationships that
> > > required both parts of a two-part entity, where the first part was
> > > fixed and the second part was in one of N mutually exclusive
> > > tables.
> >
> > So, after reading your para four times, and I really want to
> > understand the problem, I still have no idea what the problem is.
> > Would you please give me a better description
>
> Since you ask, I will indulge you.

Oh come on. Sure, you are indulging me by answering my question. But that was predicated by you raising a "problem". So the fact is, I am indulging you.

> I don't say it's the only or best
> way. I haven't thought of a better one, but I haven't addressed myself
> to the problem in 15 years.
>
> The Securities table has {ID, Type, Name, Active}. The ID is internally
> assigned because as you know we have an entire industry whose central
> product has no universally recognized identifier.

Correct, but unfairly stated.

There is a good identifier, but it is limited to the exchange. If you trade globally, sure, you have no universal Identifier. If you understand the first sentence, the identifier is obvious ( ExchangeCode, {Ticker | ASXCode |Etc} ). But you don't have Relational Keys. Therefore you are limited to surrogates (not "surrogate keys", please, ain't no such thing.) Can't blame that on the lack of an universal identifier. There ain't no universal standards body for trades, either, which is a pre-requisite for an universal identifier.

Contrast that with ISBN.

> Type is a
> discriminator, one of {'equity', 'bond', 'swap', etc.}; they reflect
> the enterprise of interest, not the whole securities market. Name is
> obvious, and Active distinguishes between instruments currently traded
> and those expired (e.g. an option past its exercise date). We called
> this a supertype for ERwin purposes.

The question begs, what did you call it for human data modelling purposes; for SQL DDL purposes ?

Both humans and ERwin demand the type of basetype::subtype relationship. I presume they are Exclusive.

I presume they are
> Naturally there are other attributes to capture, and they vary by
> instrument type. For each Type in Securities there is a table to hold
> that instrument's particulars: Equities, Bonds, Swaps, etc. We called
> this a subtype in ERwin, although I'm not sure how useful it is to
> call Equities a "subtype" of Securities.

Well, for the model you have described, it IS a subtype of Security, and the useful purpose is to ensure that you do not confuse yourself.

> The rule is that a security is represented by a pair of rows, one in
> Securities and one in the subtype table. There *must* be two rows,
> and only two rows, and the subtype table must be the one indicated by
> Securities.Type.
>
> Declare that, Kemosabe! ;-)

Pfffft.

I thought you were going to give me a serious problem, arrange a loan for you. This is so, so, pedestrian.

So the "declare that" is limited to the scope of: a. Standard SQL
b. Declarations

Correct ?

1. Tonto, please check this code out http://www.softwaregems.com.au/Documents/Tutorial/Subtype%20ValidateExclusive_fn.sql http://www.softwaregems.com.au/Documents/Tutorial/Subtype_CHECK.sql
2. If that is not descriptive enough, check the discussion at the link in either of those files.
3. If you still have a headache, post again, and I will write a prescription, further details, in reply.

> Of course, you could weaken the rule.

Unacceptable. Rules are made to be pivots for further rules, they are not made to be set aside.

> You could eliminate the Type
> column, and require each subtype have a FK relationship to Securities.

Hang on. You have to have that regardless, otherwise you have no RI between the basetype and the subtypes. You did say you use DRI, this is a mundane use of DRI.

> But that would not require a Securities row to have a related subtype,
> and would not prevent one Securities row from being referred to by more
> than one subtype table (e.g., "being" both Equity and Bond).

What ?

I read that four times (three times is my limit, and I extended myself for you), and I do not believe that I understand what you are trying to convey.

Possibilities ...

1. On the face of it, I change my presumption, they are Non-exclusive. Done. But that seems too simple for your numerous words.
2. Assuming that a single Security can "be" an "Equity and a Bond" at the same time (which is totally and violently illegal here, so my mind is fighting the notion, which I have to quell), then why can't the subtyping be Non-exclusive AND the FK be present ??? If they are the same Security, they would have the same SecurityID.
3. So that means that you MIGHT mean, the Equity-Security and the Bond-Security have different SecurityIDs. But that means, they can't "be" the same Security, that statement is false. In that case, you have gross Normalisation errors, which I think is unliely.

Ok, I give up, please explain.

> And, as a
> practical matter, you couldn't get the security's type without scanning
> the union of subtypes looking for the appearance of the ID.

Definitely, for that as well as definitive reasons, do not drop Security.Type.

> You could also put all securities in a single table with the union of
> all security attributes, all NULL, and apply the mother of all CHECK
> constraints to get them right.

Ridiculous. Unnonomalised. Unacceptable.

> Although I'm pretty sure SQL Server 7.0
> at the time didn't support CHECK.

Nah, CHECK has been standard for two decades.

CHECK that allows a subquery is fairly recent.

> We were pretty spartan with declared
> integrity constraint checking in any case, for feasibility reasons.
> bulk loads were vetted for correctness before and sanity-checked
> afterward, and interactive updates were required to use stored
> procedures (some of which used temporary tables as implicit
> parameters). But I wouldn't use a single table, even today;
> attributes with an optional relationship to the key usually should be in
> separate tables.

Yes. Optional or different relationship to the Key. Real FDs again.

> Aside: some years later, I looked into an incident of the kind that had
> by then acquired a name: "SQL injection". Of course I soon discovered
> they weren't using stored procedures, and had granted (possibly
> administrative) access rights to little Bobby Tables via the
> webserver. We have an entire industry built on application-developer
> paranoia based on willful ignorance of How to Use the DBMS. I
> understand the tech-politics that motivate the situation, but not why
> management abdicates its stewardship of the firm's data. [end aside]
>
> Now that I've described the design, I want to take you to task.

Be my guest.

> You've posted a few colorful rants about the nonexistence of
> bona fide circular references in the real world, and therefore in
> database designs.

Yes, and that should be propagated far and wide, if only to counter the pig-poop being propagated.

> ISTM you overlook mutual dependence.

What in God's name is a "mutual dependence" ??? Sounds like the thing homosexuals do, before one of them slaughters the other.

> There are
> reasons to have tables with 1:1 cardinality with both components
> mandatory. Sometimes the reason is physical, because one part is
> updated or referenced or replicated more than the other. Others are
> logical, as with this securities design. There's perfectly nothing
> wrong with it relationally, and yet the semantics cannot be enforced
> with standard SQL.

I have had the challenge (give me an example of a "very very necessary" circular reference and I will give you the Normalised, Relational solution without it) out for over a decade, and I have proved it at least twenty times in that decade. The issue is often tangled up with misunderstanding of what normalisation is; with implementing a set of rules "because the business said so". Which I didn't believe is the case with you, but now that we have evidence of many non-relational things that you do implement, it may well be.

One thing I will declare, that you yourself have provided evidence that you do understand, circular references are simply, flatly illegal in the RM.

As per the the challenge, I need a real world example, that can be worked, not a theoretical discussion, which is endless. So please give me that, or confirm that (eg.) the Security model you have given is a valid example of a circular reference or "mutual dependence", and I will work that and close it.

> You have wickedly mocked the need for deferred constraint enforcement

(Henceforth DCC for short.)

Yes.

But whoa, wicked is the realm of the devil. The devil is the one who preaches filth, anti-RM, falsities, "deferred constraint checking is very very very necessary".

I don't preach. I don't go looking for it, I just walk around destroying the filth wherever I see it. Therefore it is not I who is "wicked", the one who does good cannot be "wicked". Except when it is the devil speaking.

I have no problem mocking the devil, and his minions, the curse of humanity. Destroy is probably more accurate.

> -- constraints that apply to the transaction, not to the statement --

Yeah, I know all that, I spent three years doing hard labour at the TTM slave labour camp.

There, in that one sentence (ok, two clauses), is the entire falsity stated. If you do not understand that, you are already seduced, a victim, of the pig-poop-eaters, and you are henceforth eating the pig-poop they feed their slaves.

For context, you understand that most of the argument you had against ERwin is dishonest, because as you stated, you cannot fault ERwin for NOT being something that it is NOT declared to be. Good. You understand that most of the argument you had against SQL is dishonest, because as you stated re ERwin, you cannot fault SQL for NOT being something that it is NOT declared to be. Good. Ok, so that is the context for this one.

Now for the specifics. See if you understand and agree with each of these statements, in the sequence given. That will result in a set of agreed facts, and leave a small number to be dealt with, resolved.

1. ACID Transactions are an imperative for OLTP. It has existed in software since 1960, CICS/TCP.
2. Database Consistency (the [C] in ACID) is statement level.
3. DBMS with full ACID transactions existed for a decade before the RM, for two decades before a RDBMS platform arrived
4. SQL Standardised the RDBMS market.
5. SQL in it s entry-level compliance requirement, in its first edition (89?) demands ACID transactions. (The RDBMS vendors provided it long before the SQL Standard demanded it, from 1984 in my experience, but it may be earlier.) 5.1 Constraints are applied at the statement level, same as ACID.
6. [2] is now confirmed for sixty five years, in a variety of platforms.
7. Those who follow the RM and ACID, do not need either circular references, or DCC.
8. We do not need "convincing" of the need, the need is for pathetic non-technical people who are struggling in some technical position.
9. It is for the people who are "convinced" of their very very necessary need, to learn from the people who do not have the need, who have enjoyed the simplicity for sixty five years, what they do, and how they do it.
10. Imbeciles such as C J Date (aka TweedleDee) and Hugh Darwen (aka Andrew Warden aka TweedleDumb), who have consistently demonstrated that they have no technical ability whatsoever, suggest that there might be another way. After Dropping ACID.
11. After violating the RM so horribly, that they end up with circular references.
12. After misunderstanding SQL so terribly, that they end up with circular references.
13. Technical, scientific people [8] couldn't care less.
14. Non-techical or unscientific people, their slaves, follow their non-logic, without question.

So it is a gross contradiction, a disgusting lie, to state "constraints that apply to the transaction", coz there ain't no such thing, sonny boy. Never has been. You EITHER have transactions, which maintain and support constraints (the [C]), which are statement level only, XOR you have strings of SQL that run all over the neighbourhood without being able to find their home.

If those poop-eaters had half a teaspoon of honesty, they would define a new model, and it would be complete, "constraints" that apply to a "transaction, not the statement". As you know, they have produced none such, neither model, nor language, definitively, for twenty years, and they are still arguing about the meaning of TYPE, for the third time. It is all flimsy descriptions that keep changing.

But they are proud sons of the devil, their intent is not to create something, but to destroy something good. So they redefine the universe, for every definition, they have some pathetic exceptional case (like special needs kids who can't wipe their backsides), and for these special needs, instead of fixing their problem, where it exists, they demand the whole universe has to change, so that the whole universe has their problem. There, see, they are special needs retards any more.

Database design is for undamaged humans. Imbeciles have a hard time.

Learn how to normalise data, Relationally, such that you do not have circular references, so that you don't feel "deprived" when your vendor doesn't supply DCC. We live in that "deprivation" and love it.

Anyway, just give me a real world example, or confirm the Security one, and I will resolve it for you.

> on the theory that all such mutuality is illusory.

Not just illusory, simply, you have unfinished, incomplete definition. Don't code a single line (that includes DDL) until the definition has been completed.

> But you are in a
> trap: if it's OK to have two non-null attributes on one row (1:1
> mandatory cardinality), why is it not OK to have the same relationship
> between two attributes on two rows? And if 2 tables can be related
> that way, why not 3 or more?

You are talking nonsense. Since when does a column that is functionally dependent on a key have "cardinality" with the key or "mutual dependence" on it. The notion is ridiculous.

And then you take that ridiculous, false, special-needs notion, and you say you can't apply that "relationship" to another context. Good. The notion was absurd the first time, and it remains absurd the second time.

The trap is yours, of your own making.

And just in case that special-needs notion comes from some abstraction that you guys love, that we real-universe types have no knowledge of, then be advised that you have abstracted yourself into a state of ridicule, and into a trap of your own making.

Since I am ultra-legal, since I comply with the RM and SQL, I don't have the special needs; the circular references, I don't need DCC, and the trap doesn't work on me.

I note that you and Nicola and others are discussing what "Derek claims". Unfortunately, on this thread. I read your posts, but I ignore some others. Please be advised that I am quite able to express myself, by myself, I don't need help from proven imbeciles (I am not talking about you). What they say, and what you participate in when you reply them, has nothing to do with what I have said, let alone what I meant.

I say this in order to ensure that you don't get mixed up when you are replying to my posts, ie. you do not think I stated something that I did not, that some imbecile stated that I stated, etc. The confusion around FDs is bad enough as it is, we do not need more confusion based on "he saids" and "she saids".

Yes, imbeciles don't have the slightest notion of internet etiquette. And they squeal like piglets who have lost their sow when their imbecility is exposed. Special needs indeed.

Cheers
Derek Received on Tue Feb 17 2015 - 12:53:22 CET