 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / General DB Topics / DB Theory / July 2005Implementation of boolean types.
Hi all, I have seen many databases that tables with columns such as IsMale,IsFemale, IsNull,Is... All these were somekind of boolean type with yes/no or true/false or 1/0 values.In DBMSs that supports boolean or bit data type it can be implemented naturaly but in Oracle that does not have boolean or bit,people usually use number(1) or char(1) for this purpose. My question: Is this design OK in your opinion? I personally think that this should be design using a relationship with other tables such as HumanSex or... for scalability reasons and so on (Not in fe/male situation! ) Any other ideas are highly appreciated. -Thanks in advance > I have seen many databases that tables with columns such as IsMale,IsFemale, >IsNull,Is... [quoted text clipped - 7 lines] >for scalability reasons and so on (Not in fe/male >situation! ) What scalability? "Have you ever been convicted of a criminal offence for which you have not received a pardon?" The answer is yes or no. What need of scalability? If there is a real possibility of having more than two choices, then maybe another table, but there are cases of multiple choice that are stable. Marital status is one of these. Sex of individual is not limited to two choices. In a medical database, it might be very important to specify the sex accurately. 1) While rare, hermaphroditism does exist. 2) There is also androgen insensitivity syndrome where the person is genetically male but somatically female. > Any other ideas are highly appreciated. Sincerely, Gene Wirchenko You missed the class on scales and measurements. SQL deliberately left out Booleans and deprecated bits. They are a sign of "punch card" and assembly language programming. >> I personally think that this should be design using a relationship with other tables such as Human << No, you should use a CHECK() on a vlaue that is limited to a particular scale. Your sex code example has an ISO Standard which you would have found if you researched before you coded. 0= Unknown, 1= male, 2= female and 9= lawful person (corporations, organizations, etc.) Get a copy of SQL PROGRAMMING STYLE for some details on how to create encoding schemes -- there is no single magic answer. > You missed the class on scales and measurements. SQL deliberately left > out Booleans and deprecated bits. They are a sign of "punch card" and [quoted text clipped - 12 lines] > Get a copy of SQL PROGRAMMING STYLE for some details on how to create > encoding schemes -- there is no single magic answer. only a committee would come up with values of 1=male and 2=female. If you protest that these are multilingual, I say pick French, pick English, pick anything. but 1=male? nobody will ever remember that, and they'll just use the values for their language.  SignatureKenneth Downs Secure Data Software, Inc. (Ken)nneth@(Sec)ure(Dat)a(.com) They were picked to be language-free. This is a big issue in standards. >> but 1=male? nobody will ever remember that, and they'll just use the values for their language. << Not if they are good programmers who learn the standards of their trade. Not if they have to exchange data with someone else on earth. Are you old enought to remember these popular rants against standards, which fall in my lifetime? 1) ZIP codes? Nobody will remember those! They'll just put down the city and the state when they mail a letter! 2) All-digit dialing?? Are you insane?? Who can remember a number that long! TEN DIGITS!! We are just doing this to make phone numbers language free. It is a Commie plot to destroy America. There was actually a group named "The anti-Digit Dialing League" that protested this change. 3) Americans do not need Metric madness! Coca Cola will never be sold in liters. And surely cigarettes and automobiles will never be made in millimeters. [snip] >only a committee would come up with values of 1=male and 2=female. If you >protest that these are multilingual, I say pick French, pick English, pick >anything. but 1=male? nobody will ever remember that, and they'll just >use the values for their language. A recent example that I saw was in a Scandinavian language for small, medium, and large. Apparently, the initials for the corresponding words in that language respectively start with L, M, and S. The speakers of that language use S, M, and L for sizes, in that order. Sincerely, Gene Wirchenko > You missed the class on scales and measurements. and I just read a good treatment of that in a new book called SQL PROGRAMMING STYLE. I am pleased to say that I can recommend the book, even if not all of the ISO standards :-) > SQL deliberately left > out Booleans and deprecated bits. They are a sign of "punch card" and [quoted text clipped - 4 lines] > No, you should use a CHECK() on a vlaue that is limited to a particular > scale. It is still a shame not to have a boolean type, you've gotta admit, right? > Your sex code example has an ISO Standard which you would have found if > you researched before you coded. 0= Unknown, 1= male, 2= female and 9= > lawful person (corporations, organizations, etc.) It was MUCH better when male was 0 and female was 1 -- at least each of these two genders were valued as an identity ;-) rather than one being SECOND -- ugh! When ISO came up with this standard, what percentage of existing databases provided more than two possible values for gender? If someone is uncertain, show two signs from restrooms and ask which restroom they are most comfortable entering or flip a coin. Define the attribute as being a "best fit" for a value for gender among those available (such as M & F). Then if they indicate they are concerned about having to provide a gender value, flip a bit, I mean give another attribute such as hasGenderConcerns a value of "T" (or true). > Get a copy of SQL PROGRAMMING STYLE for some details on how to create > encoding schemes -- there is no single magic answer. I see you have read the book too ;-) I'm only half way through, but I'll pass you an attaboy on it, Joe. So far, I have found it helpful, clear, readable, and it addresses practical questions people have. I don't know how we are going to get past the underscores in attribute names vs camelCase names issue, however. I'd appreciate more consistency in naming throughout an application and camelCase is here to stay. Also, when you put your data catalog on a web site as hypertext with standard html styling, you don't lose visibiliy of any of the attribute name (the underscore). The fact that some dbms's have upper/lowercase issues in 2005 is not a reason to set the industry standards there. I'll expect that fixed in the next edition of the book, OK? :-) Cheers! --dawn >> When ISO came up with this standard, what percentage of existing databases provided more than two possible values for gender? << The problem was that too many databases were using the local language equivalent of 'M' and 'F' and could not exchange data, nor model a corporation nor show a missing value. This code was designed for normal usage, not medical freaks. >> I don't know how we are going to get past the underscores in attribute names vs camelCase names issue, however. I'd appreciate more consistency in naming throughout an application and camelCase is here to stay. << Get a copy of SQL PROGRAMMING STYLE for some of the details about readable names. For example, camelCase is worse and you can measure it with eye movement studies. The eye is attracted to an uppercase letter because it marks the start of a sentence or a noun. Then the eye has to twitch back left to the start of the word. What makes this really bad is thart the second unit of the name is often a scale or other standardized postfix, as requred by ISO-11179 rules. When you read "fooType" and "barType" you get confused because you processed the second unit first. Effectively, your eye is saying "it's a TYPE, the last thing i read was FOO so I expect a FOO, wait! Nope! it's BAR. now I can move to the next unit of text after twitching this.". > >> When ISO came up with this standard, what percentage of existing databases provided more than two possible values for gender? << > [quoted text clipped - 6 lines] > > Get a copy of SQL PROGRAMMING STYLE I have a copy and paid money for it too -- I HAVE read your entire treatment of this subject. I simply disagree. > for some of the details about > readable names. For example, camelCase is worse and you can measure it > with eye movement studies. and that should, indeed, be a factor in the decision, but not the sole factor. Upper case is much harder to read than lower case, but there are good times to use all upper, none-the-less. > The eye is attracted to an uppercase letter > because it marks the start of a sentence or a noun. Then the eye has > to twitch back left to the start of the word. Yup. But surely this is not the only requirement for the standard. > What makes this really bad is thart the second unit of the name is > often a scale or other standardized postfix, as requred by ISO-11179 > rules. When you read "fooType" and "barType" you get confused because > you processed the second unit first. and if you roll a piano up next to me, I won't just be able to play Bach either. > Effectively, your eye is saying > "it's a TYPE, the last thing i read was FOO so I expect a FOO, wait! > Nope! it's BAR. now I can move to the next unit of text after twitching > this.". You laid out the case for using the underscore. It is a good one, but not sufficient. Others standards have also been thought out. I don't know if UML standards indicate naming conventions, but the diagrams I have seen use the more OOP naming standards. Ours is an industry where screws and screwdrivers insist on different standards, so we always need that screw-to-screwdriver tool in the middle. Then someone pipes up and says that is good because we want to decouple the screw and the screwdriver anyway. Think how easy it would be to build with a legos that were made with such an that approach. Heavy sigh. --dawn >> ... camelCase is worse and you can measure it >>with eye movement studies. > You laid out the case for using the underscore. It is a good one, but > not sufficient. Others standards have also been thought out. I don't > know if UML standards indicate naming conventions, but the diagrams I > have seen use the more OOP naming standards. Unfortunately, most naming convention standards writers (whether international, industry or local) simply do not consider issues such as readability, ease-of-scanning, or similar issues such as ease of typing. Considering how much time we spend reading and (to a lesser extent) writing code, this seems rather short-sighted (sorry :-) > dawn wrote: > > > ... camelCase is worse and you can measure it [quoted text clipped - 10 lines] > Considering how much time we spend reading and (to a lesser extent) > writing code, this seems rather short-sighted (sorry :-) Yes...and this point also extends to the style in which the code is laid out (white space etc). IMHO, absolute adherence (by the entire team) to the mandated (for capitalist readers) or agreed (for communists) style is the main issue in ensuring maximum productivity! Cheers, Frank. > SQL deliberately left out Booleans [...] SQL-1999 and SQL-2003 both have a BOOLEAN type, with recognized values TRUE, FALSE and UNKNOWN. I'm not clear whether NULL is equivalent to UNKNOWN or not; I've not scrutinized the weasel-words carefully enough. SignatureJonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/ > > SQL deliberately left out Booleans [...] > > SQL-1999 and SQL-2003 both have a BOOLEAN type, with recognized values > TRUE, FALSE and UNKNOWN. I'm not clear whether NULL is equivalent to > UNKNOWN or not; I've not scrutinized the weasel-words carefully enough. What would we need UNKNOWN for when we already have NULL? NULL is all we have to mean both "inapplicable" and "missing/unknown" for all other data types in SQL, so why not for BOOLEAN? The differences seem rather subtle to me: UNKNOWN - we know that we don't know whether this is true or false NULL - we don't know whether we know whether this is true or false?! My head is spinning... >>>SQL deliberately left out Booleans [...] >> [quoted text clipped - 11 lines] > > My head is spinning... Consider a table that holds the shoe size of 3 entities. Me (John), George Bush and my keyboard. Description | Shoe Size -------------+------------ John | 10 UK George Bush | unknown My Keyboard | null So we can see that null and unknown mean different things. George has a shoe size, but I don't know it, wheras my keyboard doesn't have a shoe size. Having said that, I believe that NULLs are a bad thing, and would never use them. (The example above could be normalised away by separating it into 2 tables with no "shoe size" entry for "My Keyboard". John NULL is not a logical value and UNKNOWN is. Look up the truth tables in any SQL book. Q: Now what is the first and most important rule of NULLs? A: NULLs propagate in computations! That means we should have these rules for Boolean types: NULL AND TRUE = NULL NULL AND FALSE = NULL NULL AND NULL = NULL NULL OR TRUE = NULL NULL OR FALSE = NULL NULL OR NULL = NULL NOT NULL= NULL -- valid but really weird looking, unh? However, in 3VL we have these rules UNKNOWN AND TRUE = UNKNOWN UNKNOWN AND FALSE = FALSE <== opps! not the same as NULL UNKNOWN AND UNKNOWN = UNKNOWN UNKNOWN OR TRUE = TRUE <== opps! not the same as NULL UNKNOWN OR FALSE = UNKNOWN UNKNOWN OR UNKNOWN = UNKNOWN NOT UNKNOWN = UNKNOWN So, which of the three values does a NULL map to? The results of logical expressions now depend on the order of execution and substitution of UNNOWN and NULL > NULL is not a logical value and UNKNOWN is. Look up the truth tables > in any SQL book. [quoted text clipped - 13 lines] > > NOT NULL= NULL -- valid but really weird looking, unh? Hmmm...the 3VL truth tables I was taught looked like this (with ? meaning null - I hope you're looking at fixed width fonts): AND T ? F T T ? F ? ? ? F F F F F OR T ? F T T T T ? T ? ? F T ? F x NOT x T F ? ? F T This would make your rules for NULL AND FALSE and for NULL OR TRUE incorrect: NULL AND FALSE = FALSE (because regardless of whether TRUE or FALSE is substituted for null, the result will then be false) NULL OR TRUE = TRUE (because, regardless of whether TRUE or FALSE is substituted for null, the overall result will then be TRUE) > However, in 3VL we have these rules > > UNKNOWN AND TRUE = UNKNOWN > UNKNOWN AND FALSE = FALSE <== opps! not the same as NULL > UNKNOWN AND UNKNOWN = UNKNOWN If your initial statement was accurate, then yes; my understanding is that looks the same as the corrected table. > UNKNOWN OR TRUE = TRUE <== opps! not the same as NULL > UNKNOWN OR FALSE = UNKNOWN > UNKNOWN OR UNKNOWN = UNKNOWN Ditto. > NOT UNKNOWN = UNKNOWN > > So, which of the three values does a NULL map to? Apparently, NULL == UNKNOWN, under the premises I was taught. > The results of > logical expressions now depend on the order of execution and > substitution of UNNOWN and NULL Where did I make a mistake in my analysis? SignatureJonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/ > > NULL is not a logical value and UNKNOWN is. Look up the truth tables > > in any SQL book. [quoted text clipped - 72 lines] > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/ And if your font is variable width, but your reader decodes embedded html, this might help: <code> AND T ? F T T ? F ? ? ? F F F F F OR T ? F T T T T ? T ? ? F T ? F x NOT x T F ? ? F T </code> > NULL is not a logical value and UNKNOWN is. Look up the truth tables > in any SQL book. [quoted text clipped - 13 lines] > > NOT NULL= NULL -- valid but really weird looking, unh? That would be a dumb treatment, really. NULLs propagate in computations for numbers, because it makes sense to do so: we really don't know what 1+NULL is equal to. But we DO know that (FALSE AND [any boolean value]) = FALSE, so logically NULL should NOT propagate here. Jonathan's post expresses my expectation of how NULLs should behave. > That would be a dumb treatment, really. NULLs propagate in > computations for numbers, because it makes sense to do so: we really > don't know what 1+NULL is equal to. Not quite: 1+NULL = NULL while sum(1,1,NULL) = 2 No elementary consistency. > > That would be a dumb treatment, really. NULLs propagate in > > computations for numbers, because it makes sense to do so: we really > > don't know what 1+NULL is equal to. > > Not quite: 1+NULL = NULL while sum(1,1,NULL) = 2 > No elementary consistency. yeah, that's really weird. the 1+null case shows null having 'unknown' semantics. the sum(1,1,null) case shows null having 'missing value' semantics. So which is it? Clearly, there is no particular consistency. I would prefer a treatment with better consistency. In particular, I see value in using 'missing value' semantics, because how they should behave is quite clear, whereas unknown introduces 3vl which I find unattractive, from the standpoint that it introduces a lot of complexity for little payback. I also think that unknown semantics are better handled by user-defined special values. Marshall Marshall, Symbolic programming is a good solution for null values. See these trivial evaluations: y = 1+x --> 1+x y - x --> 1 z = (1,1,x) --> 2 + x z - x -->2 False and x --> False True or y --> True a = not b --> not b c = not a --> b Remember elementary algebra: We learned how to calculate with unknowns in elementary school :-)) But CS forgot to build it in computing. I strongly believe CAS [Computer Algebra Systems] are needed for solving the null problem. The practical problem is that expressions with "unknown variables" (symbols) tend to get really big (e.g. z + y --> (2 + x) + (1+x) -->3 + 2x). Greeting from Craotia, Drago Ganic >> > That would be a dumb treatment, really. NULLs propagate in >> > computations for numbers, because it makes sense to do so: we really [quoted text clipped - 18 lines] > > Marshall Hi, there was an error in my posting: > Symbolic programming is a good solution for NULL values.... should be Symbolic programming is a good solution for UNKNOWN values ... NULL-a "as a pollution mechanism" are maybe not so bad as a practical solution when expressions with lot's of unknowns get large. Greeting form Croatia, Drago Ganic > Marshall, > Symbolic programming is a good solution for null values. See these trivial [quoted text clipped - 44 lines] >> >> Marshall > > Symbolic programming is a good solution for NULL values.... > should be > Symbolic programming is a good solution for UNKNOWN values ... I figured that was what you meant. However, I have to say, I question whether unknown values is really the right problem to be working on. I think it it's. I think the better, easier approach is to model the empty set, because it is application-independent, and also to have a special-values mechanism, because sometimes you need them anyway, and special values handle both null and unknown however you want them to. You can also avoid introducing 3VL or 4VL into your system. I *do* think the idea of symbolic computation or algebraic formulas or whatever you call it is quite interesting, but I'm not sure how much value it has towards the field of *data management* per se. Marshall > > > Symbolic programming is a good solution for NULL values.... > > should be [quoted text clipped - 5 lines] > the right problem to be working on. I think it it's. I think > the better, easier approach is to model the empty set, Yup! I've worked with tools that have a null value (yes "value") that models the null set and I've worked with SQL and it is so much easier and more intuitive to work with a 2VL and null value = empty set model. Design, development, trouble-shooting, and maintenance are all significantly easier with this model. This is one of several features where those attempting to implement relational theory took what was working in databases in the 70's (such as 2VL) and mucked it up, contributing to greater cost of ownership for data-centric applications. Taking just this one issue, I suspect the path to getting the industry from here to there is via XML/XQuery, perhaps? However, I did read that SQLServer has an option of switching from 3VL to 2VL. cheers! --dawn > Yup! I've worked with tools that have a null value (yes "value") that > models the null set and I've worked with SQL and it is so much easier [quoted text clipped - 6 lines] > as 2VL) and mucked it up, contributing to greater cost of ownership for > data-centric applications. Hmmm. I detect that perhaps you are trying to identify this issue as being somehow a consequence of RM? If so I would not agree; the issues are independent. > Taking just this one issue, I suspect the > path to getting the industry from here to there is via XML/XQuery, > perhaps? However, I did read that SQLServer has an option of switching > from 3VL to 2VL. I don't think I'd agree with this either. SQL needs to be replaced, not fixed, and the XML-family of technologies are fundamentally flawed. I don't think any "data management" system that doesn't have a type system or a schema in version one is going to go anywhere. You can't retrofit these things; they have to be designed in from the start. (Yes, I know XML now has various ways to specify schema, with at least DTD and XMLSchema. [Does anyone anywhere think they are good?] Reminds me of the old saw about a man with two watches.) Anyway, XML doesn't address data management; it's a document management system. Marshall > > Yup! I've worked with tools that have a null value (yes "value") that > > models the null set and I've worked with SQL and it is so much easier [quoted text clipped - 10 lines] > as being somehow a consequence of RM? If so I would not agree; > the issues are independent. Even if current RM theory accomodates 2VL, is it historically inaccurate for me to suggest that before Oracle and other SQL-DBMS products became popular most developers working with stored data were using 2VL, putting ^000 or low-values or some such in "empty" fields? I admit that I did not research it, I just lived it and certainly not all of it. I think of SQL as bringing the 3VL into popularity, but I could be wrong. Attempts to implement the RM have been almost exclusively with SQL-DBMS's, so practically speaking it is the products that stem from the RM that have brought this on, whether they needed to (and we know they didn't) or not. > > Taking just this one issue, I suspect the > > path to getting the industry from here to there is via XML/XQuery, [quoted text clipped - 3 lines] > I don't think I'd agree with this either. SQL needs to be replaced, > not fixed, Complete agreement on that point. > and the XML-family of technologies are fundamentally > flawed. I agree with that too. In fact, all models and all products are flawed, but some are more useful than others, right? > I don't think any "data management" system that doesn't > have a type system or a schema in version one is going to go anywhere. I think you might have to eat those words, but we shall see. > You can't retrofit these things; they have to be designed in from > the start. Certainly preferred and there will definitely always be flaws, but I don't think we are too far off from significant changes in the database world and XML will surely play I roll. > (Yes, I know XML now has various ways to specify schema, > with at least DTD and XMLSchema. [Does anyone anywhere think they > are good?] Not I, brother. > Reminds me of the old saw about a man with two watches.) I must be too young to know that one, so feel free to fill it in > Anyway, XML doesn't address data management; it's a document > management system. Sure XML is just a format. But is a format that doesn't play nice with SQL-DBMS's (and, yes, I know of everybody and his brothers adapters with one end connected to the SQL-DBMS and the other to XML, but some folks like me will want that unnecessary component out of there). Somewhere between the SQL-RDBMS and the Semantic Web, exclusive, lies a model and implementations thereof that will do a big-bang-for-the-buck job and developers will flock to it -- perhaps a hosted database with an intuitive interface and constraint-handling that permits end-users to add their own new attributes (having just read the other thread on that). Cheers! --dawn > Marshall > > > This is one of several features where those attempting to implement > > > relational theory took what was working in databases in the 70's (such [quoted text clipped - 15 lines] > that stem from the RM that have brought this on, whether they needed to > (and we know they didn't) or not. If you're proposing that SQL is the thing that popularized 3VL, I won't disagree. (I don't have any particular information on this topic.) But if you're proposing that any RM-based system will necessarily have 3VL, then I disagree. It's entirely possible to have a relational system with vanilla 2VL. > > I don't think I'd agree with this either. SQL needs to be replaced, > > not fixed, > > Complete agreement on that point. Whee! > > and the XML-family of technologies are fundamentally > > flawed. > > I agree with that too. In fact, all models and all products are > flawed, but some are more useful than others, right? Sure, I guess. Maybe "limited" is a better word than flawed here. > > I don't think any "data management" system that doesn't > > have a type system or a schema in version one is going to go anywhere. > > I think you might have to eat those words, but we shall see. You're anti-static typing then? Don't tell me you're anti-schema?! (Gasp!) Seriously, though, looking back at my original wording, I suppose I should have said "anywhere useful." I find it hard to imagine how anyone can advocate data management without a type system and without a schema. > > You can't retrofit these things; they have to be designed in from > > the start. > > Certainly preferred and there will definitely always be flaws, but I > don't think we are too far off from significant changes in the database > world and XML will surely play I roll. It'll be sad if it does. But you may be right. > > (Yes, I know XML now has various ways to specify schema, > > with at least DTD and XMLSchema. [Does anyone anywhere think they [quoted text clipped - 5 lines] > > I must be too young to know that one, so feel free to fill it in "A man with one watch knows what time it is; a man with two watches knows nothing." --> XML has two different systems for specifying schema. And despite the fact that XML is the perfect universal format for storing everything, one of them isn't formatted in XML! > > Anyway, XML doesn't address data management; it's a document > > management system. [quoted text clipped - 3 lines] > with one end connected to the SQL-DBMS and the other to XML, but some > folks like me will want that unnecessary component out of there). Somehow I missed the end of that sentence. > Somewhere between the SQL-RDBMS and the Semantic Web, exclusive, lies a > model and implementations thereof that will do a big-bang-for-the-buck > job and developers will flock to it -- perhaps a hosted database with > an intuitive interface and constraint-handling that permits end-users > to add their own new attributes (having just read the other thread on > that). Meh, I dunno. Just because something becomes popular doesn't mean it's good, or even the right tool for doing data management. MySQL is about the worst SQL-DBMS for managing data integrity, and it's also the most popular. Maybe its popularity has more to do with the price than with the suckiness. All the good SQL-DBMSs cost big quatloos. Marshall > > > > This is one of several features where those attempting to implement > > > > relational theory took what was working in databases in the 70's (such [quoted text clipped - 18 lines] > If you're proposing that SQL is the thing that popularized 3VL, > I won't disagree. Yes, that is the point. > (I don't have any particular information on > this topic.) But if you're proposing that any RM-based system > will necessarily have 3VL, then I disagree. I did not intend that. The RM need not be used in connection with a 3VL. > It's entirely > possible to have a relational system with vanilla 2VL. And likely wise too. > > > I don't think I'd agree with this either. SQL needs to be replaced, > > > not fixed, [quoted text clipped - 10 lines] > > Sure, I guess. Maybe "limited" is a better word than flawed here. This might be a matter of defining terms. In that the RM is a mathematical model and the mathematics underlying it have been defined in a formal system, etc, we could call the RM correct. The flaws with the RM are introduced in the mapping from "reality" to the model. These flaws limit the usefulness of the model. A model is a mathematical metaphor, so it doesn't tell the entire story accurately. It is flawed in that regard. And then there is the implementation of the model, which is a model of the model, introducing more flaws. > > > I don't think any "data management" system that doesn't > > > have a type system or a schema in version one is going to go anywhere. > > > > I think you might have to eat those words, but we shall see. > > You're anti-static typing then? Don't tell me you're anti-schema?! Absolutely not. I am definitely a fan of static typing, although perhaps not with the same type of implementations as prevelent today. See next comment. > (Gasp!) Seriously, though, looking back at my original wording, > I suppose I should have said "anywhere useful." I find it hard > to imagine how anyone can advocate data management without a > type system and without a schema. Where and how should types and schema be defined; where/when should problems be identified during the construction and maintenance processes; and when a change is needed, what does it take to make various types of changes and where do such changes need to be made? I think that as an industry we could do much better than we do now. > > > You can't retrofit these things; they have to be designed in from > > > the start. >From a statement like that, which seems so right when you hear it, what would you conclude about the implications and cost of changing requirements? > > Certainly preferred and there will definitely always be flaws, but I > > don't think we are too far off from significant changes in the database > > world and XML will surely play I roll. > > It'll be sad if it does. There are several things about it that are not at all sad. SQL-DBMS's can make for overly costly software development and maintenance. I know that we can do better and this could give us a means to do that. Taking the industry from thinking of data in terms of graphs and paths to making it a no-no to think of data in any form other than sets was a "throw the baby out with the bath water" approach (terrible metaphor that one is). Without taking everything we have done with SQL-DBMS's, but what we have learned about working with sets and set operators, and adding back what we have learned about navigating around data, marking up data that are not best captured in smaller pieces, using a 2VL, working with collection types such as lists, etc, we ought to be able to make significant improvements. Then answer some of the questions I put up front regarding typing (and other constraints) in a different way than we do with the SQL-DBMS's, or at least providing options, and we could get to something much better than what we have today. > But you may be right. of course I am ;-) > > > (Yes, I know XML now has various ways to specify schema, > > > with at least DTD and XMLSchema. [Does anyone anywhere think they [quoted text clipped - 8 lines] > "A man with one watch knows what time it is; a man with two > watches knows nothing." love it > --> XML has two different systems > for specifying schema. And despite the fact that XML is the > perfect universal format for storing everything, one of them > isn't formatted in XML! I just prepared an xhtml page that points to the transitional .dtd and uses a .css for the style. And then that way cool Javascript ... > > > Anyway, XML doesn't address data management; it's a document > > > management system. [quoted text clipped - 5 lines] > > Somehow I missed the end of that sentence. Nope, I did. Because XML and SQL-DBMS's don't play nice, we need to have those way-too-prevalent connectors. If software can be written where the data structures persisted in memory can be the same structures stored on the disk or any other media, we can avoid some costs and gain qualtiy, I suspect. > > Somewhere between the SQL-RDBMS and the Semantic Web, exclusive, lies a > > model and implementations thereof that will do a big-bang-for-the-buck [quoted text clipped - 5 lines] > Meh, I dunno. Just because something becomes popular doesn't mean > it's good, or even the right tool for doing data management. I've been saying that related to another big buzz word in data management that hit hard in the 80's and is still king of the hill today ;-) > MySQL > is about the worst SQL-DBMS for managing data integrity, I'm not a MySQL fan by any stretch, but recognize that it is the entire system as a whole that needs to manage data integrity. There is more than one way to partition a system and placing all constraint logic in the DBMS tool (either in addition to or instead of elsewhere) might not yield the best overall solutions. > and it's > also the most popular. Maybe its popularity has more to do with > the price Price, sure. But I suspect that the fact that considerably less constraint logic is encoded in the DBMS is also a feature. Software developers want systems that can be maintained and the SQL-DBMS implementations have not provided the most flexible sandboxes. > than with the suckiness. All the good SQL-DBMSs cost > big quatloos. Not all. ;-) Cheers! --dawn > Marshall > > It's entirely > > possible to have a relational system with vanilla 2VL. > > And likely wise too. We agree on this point. > > > I agree with that too. In fact, all models and all products are > > > flawed, but some are more useful than others, right? [quoted text clipped - 9 lines] > It is flawed in that regard. And then there is the implementation of > the model, which is a model of the model, introducing more flaws. I would say 'abstraction' rather than 'metaphor'. And I don't think the word 'flawed' is quite right. For example, the mathematical abstraction of addition can be used to make prediction about how many of something you'll have when you combine two groups. It is always accurate; you never put together two apples and two apples and get five apples. It it true that addition doesn't tell the whole story, but since it does not claim to I don't see how you could call it 'flawed.' 'Incomplete' maybe. > Where and how should types and schema be defined; where/when should > problems be identified during the construction and maintenance > processes; and when a change is needed, what does it take to make > various types of changes and where do such changes need to be made? I > think that as an industry we could do much better than we do now. I'm not sure I understand what all you're trying to get at, but I can pick out schema migration issues from you're saying. I agree that that, and software versioning are areas for improvement. > > You can't retrofit these things; they have to be designed in from > > the start. > > From a statement like that, which seems so right when you hear it, what > would you conclude about the implications and cost of changing > requirements? Uh, that it sucks? every project I've ever been on has had requirement changes. Wait, maybe that's what you're saying. We should design systems that accept change well, yes? I'd certainly agree there. But I don't see anything specific to RM here. If anything, I would propose that having a good formal foundation would enable us to define better how change management should take place. > > > Certainly preferred and there will definitely always be flaws, but I > > > don't think we are too far off from significant changes in the database [quoted text clipped - 5 lines] > can make for overly costly software development and maintenance. I > know that we can do better and this could give us a means to do that. I really don't see how. Just because there are limitations of the current best model doesn't mean that any other model we come up with is going to be any better. And in particular the xml model is much worse, lacking even the most basic of data management features. > Taking the industry from thinking of data in terms of graphs and paths > to making it a no-no to think of data in any form other than sets was a > "throw the baby out with the bath water" approach (terrible metaphor > that one is). I don't agree. Manual navigation is an inferior technique to using content-based addressing. > Without taking everything we have done with SQL-DBMS's, > but what we have learned about working with sets and set operators, and [quoted text clipped - 5 lines] > way than we do with the SQL-DBMS's, or at least providing options, and > we could get to something much better than what we have today. Could you be explicit about what these questions are? > > But you may be right. > [quoted text clipped - 38 lines] > structures stored on the disk or any other media, we can avoid some > costs and gain qualtiy, I suspect. If you mean the *logical* structure, then yes. I note that Other than the bare rm, I haven't really seen alyone attempt to come up with a framework for talking about logical data structures. Say, something that accounted for different kinds of logical trees. > I'm not a MySQL fan by any stretch, but recognize that it is the entire > system as a whole that needs to manage data integrity. There is more > than one way to partition a system and placing all constraint logic in > the DBMS tool (either in addition to or instead of elsewhere) might not > yield the best overall solutions. we've had this conversation before! > Price, sure. But I suspect that the fact that considerably less > constraint logic is encoded in the DBMS is also a feature. Ugh. Strongly disagree here. Although it may be true that this approach appeals to novices and those who don't know any better. I am soorry if that sounds harsh, but application-enforced integrity is just not the answer to anything. Marshall > > > > I agree with that too. In fact, all models and all products are > > > > flawed, but some are more useful than others, right? [quoted text clipped - 11 lines] > > I would say 'abstraction' rather than 'metaphor'. I'm OK with "abstraction" but I like "metaphor". > And I don't think > the word 'flawed' is quite right. For example, the mathematical > abstraction of addition can be used to make prediction about how > many of something you'll have when you combine two groups. It is > always accurate; you never put together two apples and two apples > and get five apples. If you have an answer to a question and I have an answer to the same quetion that doesn't mean that we have two answers to the question -- our answers might be the same, so adding one answer to another answer might just give us one answer. I know there are much more fun examples where you would not want to map language to arithmetic the way it sounds, but I'm not coming up with them right now. Similarly, I could take a proposition like this one: I topped the pizza crust with sauce, mozzarella, and pepperoni and model it with a relation PizzaToppings("pizzaId", "topping") and then ask my model the question "What topping do I put on after the mozzarella?" It is not that I am using relations that introduces the flaw -- it is that I opted to model it this way, which was not useful for the question I'm asking. So, it is a flaw to use this particular metaphor and, in this case, the metaphor is flawed. When I modeled this data, I didn't think the order of the ingredients was important, so I did not include that in the model. I could have modeled that with this relation by adding an ordering number for the topping, but when I copy this pizza as a template for a new one and then need to put sausage on the pizza before the pepperoni, I have to (as opposed to the data model auto functions) renumber the pepperoni because I put something before it. That seems like a bit of a flaw in the model. I could have modeled this same proposition with PizzaToppings("pizzaId", "toppings") and declare toppings to be of type "list". SQL-92 might not be happy with that (and we could call that a flaw in this model), but then my model could include an insert-into-list function so I don't have to renumber. > It it true that addition doesn't tell the whole > story, but since it does not claim to I don't see how you could > call it 'flawed.' 'Incomplete' maybe. In my examples above, it is not addition nor relational theory that is flawed, it is that using addition or relational theory to model reality in these cases is not as useful as using a different model. I'll address the rest of the message later, since I've rambled on enough already. cheers! --dawn [snip] >Similarly, I could take a proposition like this one: > [quoted text clipped - 9 lines] >for the question I'm asking. So, it is a flaw to use this particular >metaphor and, in this case, the metaphor is flawed. No, the metaphor is incorrect in this case as it does not do what you want. That, however, does not mean it is flawed or that it is not useful. In another case, you might correctly want that metaphor. Maybe, when making stew? The RM cafe. We can SET you up with a hearty stew, or you can ORDER pizza. Please ignore the arguments you hear: we are still trying to decide whether to call our collective offerings a set or a list. [snip] Sincerely, Gene Wirchenko > [snip] > [quoted text clipped - 13 lines] > > No, the metaphor is incorrect Which seems like a flaw, eh? But I'm thinking you have that 0,1 thing going, where I'm a bit more of a grey person. > in this case as it does not do what > you want. That, however, does not mean it is flawed ? puzzled look > or that it is not > useful. Again, "all models are flawed, but some are useful" (G. Box IIRC) I agree that a flawed data model might still be useful. In the example given, it was not, however. > In another case, you might correctly want that metaphor. > Maybe, when making stew? Yup, agreed. > The RM cafe. We can SET you up with a hearty stew, or you can > ORDER pizza. Please ignore the arguments you hear: we are still > trying to decide whether to call our collective offerings a set or a > list. In the mean time, the dishes are QUEUED up to be brought to the customers at their TABLES and the dirty dishes are STACKED for washing. We better start FUNCTIONING or we will start to have poor RELATIONS, lose customers, stop paying bills and start hearing from COLLECTIONS agencies. smiles. --dawn [snip] >> The RM cafe. We can SET you up with a hearty stew, or you can >> ORDER pizza. Please ignore the arguments you hear: we are still [quoted text clipped - 6 lines] >lose customers, stop paying bills and start hearing from COLLECTIONS >agencies. DROP customers? That would ROLLBACK everything we are trying for. Sincerely, Gene Wirchenko INSERT witty sig here. > Marshall, > Symbolic programming is a good solution for null values. See these trivial [quoted text clipped - 4 lines] > z = (1,1,x) --> 2 + x > z - x -->2 This is a great idea. > Remember elementary algebra: We learned how to calculate with unknowns in > elementary school :-)) But CS forgot to build it in computing. I strongly [quoted text clipped - 4 lines] > tend to get really big > (e.g. z + y --> (2 + x) + (1+x) -->3 + 2x). The expression can be collapsed. Say there are only 3 known values of Bonus in the Emp table (out of 10). Then totalBonus = 1000 + x1 + x2 + 2000 + x3 + 3000 + x5 + x6 + x7 = = 6000 + (x1 + x2 + x3 + x5 + x6 + x7) = 6000 + y An implicit rule is that expressions involving variables can be renamed to other variable. > The expression can be collapsed. Say there are only 3 known values of > Bonus in the Emp table (out of 10). Then [quoted text clipped - 4 lines] > An implicit rule is that expressions involving variables can be renamed > to other variable. Yes .... that's it :-) The variable names/symbol names in a relation are attribute names subscripted by primary keys. For example: emp.bonus is {1000 EUR, bonus[petar], bonus[paul], 2000 EUR, ... } Of course, you could change bonus[petar] to x1, bonus[paul] to x2 etc. When we augment RDBMS with this symbolic staff then we need the funcionalnity of equation solving in the engineand then things get really exicting :-) Geetings, Drago P.S.: Notice the "type" of the bonus attribute. > > The expression can be collapsed. Say there are only 3 known values of > > Bonus in the Emp table (out of 10). Then [quoted text clipped - 9 lines] > > emp.bonus is {1000 EUR, bonus[petar], bonus[paul], 2000 EUR, ... } This is convenient notation, although initially I had some difficulty applying it to an expression which is aggreagation result. After aggregation we have something like this 3000 * EUR + 2000 * $ + unknown where we collapsed bonus[petar], bonus[paul], ... into "unknown" Or, perhaps we should pursue this idea and make it 3000 * EUR + 2000 * $ + unknAggrBonus[0] where index doesn't really matter for a single aggregate. For aggregation with group by we still have a key so that the result is 3000 * EUR + 2000 * $ + unknAggrBonus[department=10,mgr=smith] 5000 * EUR + 6000 * $ + unknAggrBonus[department=10,mgr=adams] Question. How do we handle mess with created by union of unknowns Relation A: id name -- ---- 1 smith 2 unknName[id=2] 3 unknName[id=3] Relation B: id name -- ---- 1 adams 2 unknName[id=2] What is the result of project(name, A) union project(name, B) >>Marshall, >>Symbolic programming is a good solution for null values. See these trivial [quoted text clipped - 6 lines] > > This is a great idea. please forgive my ignorance about the notation. how is the symbol '-->' being used above? thx, p Paul, --> means "results after evaluation in". For example: 1+3 --> 4. Instead of "-->" we could *not* use "=" (equals, because this is a logical operator) or ":=" (assignment, becuase of obvious reasons). drago P.S: I made a typo [as I always do :-( ... ] and forgot the operator SUM in my second example: >>>y = 1+x --> 1+x >>>y - x --> 1 >>> >>>z = SUM (1,1,x) --> 2 + x >>>z - x -->2 >>>Marshall, >>>Symbolic programming is a good solution for null values. See these [quoted text clipped - 13 lines] > thx, > p > Paul, > --> means "results after evaluation in". For example: 1+3 --> 4. [quoted text clipped - 13 lines] > > ... thanks, Drago. now i think i can follow what you're talking about. not sure if i get it yet, but this is the kind of thread in this group that intrigues me. paul c. > > SQL deliberately left out Booleans [...] > > SQL-1999 and SQL-2003 both have a BOOLEAN type, with recognized values > TRUE, FALSE and UNKNOWN. Gotta love committees! Perhaps in the next round, just for fun, the standard could permit the word "mom" as another possible value for an integer type ;-) But for consistency, at least UNKNOWN should then be a recognized value for any numeric types too, don't you think? > I'm not clear whether NULL is equivalent to > UNKNOWN or not; In SQL, NULL is not a value, so these could not be equivalent. This is an implementation question, but if SQL-99 has BOOLEAN types and ODBC is stuck in SQL-92 standards (which I think is the case, but feel free to correct me on that), what happens when someone tries to access a value of this new type through ODBC? Just curious -- I know it is off-topic. --dawn > Gotta love committees! Perhaps in the next round, just for fun, the > standard could permit the word "mom" as another possible value for an > integer type ;-) LOL! Marshall [snip] >Gotta love committees! Perhaps in the next round, just for fun, the I do not feel that need. >standard could permit the word "mom" as another possible value for an >integer type ;-) But for consistency, at least UNKNOWN should then be a >recognized value for any numeric types too, don't you think? Why not give the type the additional name "mom"? Think of C: mom Mom; short mom Susan; long mom Anna; Hmm, make "long" "tall". tall mom Anna; void main(void)? How dare you disrespect your mother like that? Use mom main(void) [snip] Sincerely, Gene Wirchenko > SQL-1999 and SQL-2003 both have a BOOLEAN type, with recognized values > TRUE, FALSE and UNKNOWN. I'm not clear whether NULL is equivalent to > UNKNOWN or not; I've not scrutinized the weasel-words carefully enough. old 3-value logic posting http://www.garlic.com/~lynn/2003g.html#40 How to cope with missing values - NULLS? http://www.garlic.com/~lynn/2004l.html#75 NULL reference to Dec. '92, article by CJ Date SignatureAnne & Lynn Wheeler | http://www.garlic.com/~lynn/ >> I'm not clear whether NULL is equivalent to UNKNOWN or not; I've not scrutinized the weasel-words carefully enough. << Nobody is sure and that is a serious problem with that extension. You have to sudenly change the way that NULLs are handled and how 3VL works no matter what you decide. Nobody thought this out, so nobody wants to implement it. > Nobody is sure and that is a serious problem with that extension. Honestly, I just don't see the problem. You have two nullable 3VL booleans, it's very simple what they do: OP1 OP2 AND OR ----------------------------- TRUE TRUE TRUE TRUE FALSE TRUE FALSE TRUE NULL TRUE NULL NULL UNKNOWN TRUE UNKNOWN TRUE TRUE FALSE FALSE TRUE FALSE FALSE FALSE FALSE NULL FALSE NULL NULL UNKNOWN FALSE FALSE FALSE TRUE NULL NULL NULL FALSE NULL NULL NULL NULL NULL NULL NULL UNKNOWN NULL NULL NULL TRUE UNKNOWN TRUE TRUE FALSE UNKNOWN FALSE UNKNOWN NULL UNKNOWN NULL NULL UNKNOWN UNKNOWN UNKNOWN UNKNOWN It's simple: NULLs always propogate, and UNKNOWN is evanescent. AND is only true if op1 and op2 are true, which they might be if unknown, but won't be if it's null, unless it's Tuesday in which case you should not use AND. OR is true if either operand is true, which about 50% of UNKNOWN values are, so UNKNOWN OR UNKNOWN should return TRUE 75% of the time, from basic probability theory. However, if you're using a quantum computer with 4VL, please remember that UNKNOWN is both TRUE and FALSE at the same time. Also, in the FRENCH standard, UNKNOWN is written "JENESAISPAS" and the value of TRUE OR JENESAISPAS is DACCORD, except in Belgian French which specifies NULL. In Texas, UNKNOWN AND UNKNOWN returns BULLSHIT. Sure, you *could* just limit yourself to 2VL. But honestly, isn't knowing how the boolean operators are going to turn out kind of boring? You wouldn't read the last page a book before you read the beginning, would you? Marshall LOL! Can I steal this for an article? >> Honestly, I just don't see the problem. You have two nullable 3VL booleans, it's very simple what they do: << Now put it in a CHECK() constraint in the DDL and a search condition in the DML.. >> But honestly, isn't knowing how the boolean operators are going to turn out kind of boring? You wouldn't read the last page a book before you read the beginning, would you? << Well it depends. I read the last page of mysteries, math books in sequential order and jump into porno novels at random. --CELKO-- > LOL! Can I steal this for an article? Sure! Let me know where/when it appears if you do. Marshall > Booleans [...] are a sign of "punch card" and > assembly language programming. That's just ridiculous. What do you think connects up all those terms in a WHERE clause? Where do you think the definition of natual join comes from? { (a,b,c) | (a,b) in S AND (b,c) in T } Do you think predicate logic is a sign of assembly language? Edsger Dijkstra wrote an essay in praise of the boolean type in language design. And Boole himself died about a hundred years before the invention of assembly language. A language not having a clean treatment of the boolean type in version 1 is a sign of bad design. Marshall >> Where do you think the definition of natual join comes from? << Getting serious for a minute, Tom Johnston had a paper and a conversation with Dr. Codd about problems with the Natural Join because we cannot be sure which of the two or more columns are or are not in the resulting relation. >> A language not having a clean treatment of the boolean type in version 1 is a sign of bad design. << Procedural languages, not declarative ones. Dijkstra "only" worked with non-deterministic procedural languages. I think that we grew up with the Dijkstra/ Manna/ Gries model of programming that we had a bitch of a time with RM. I think I can say that with authority since my first Master's Thesis was on an algebra of Structured Programming :) > >> Where do you think the definition of natual join comes from? << > > Getting serious for a minute, Tom Johnston had a paper and a > conversation with Dr. Codd about problems with the Natural Join because > we cannot be sure which of the two or more columns are or are not in > the resulting relation. If I understard what you're saying correctly (not sure) then I have to say that I buy into Date's viewpoint: relations are predicates, and attributes are the variables of the predicate. > >> A language not having a clean treatment of the boolean type in version 1 is a sign of bad design. << > [quoted text clipped - 3 lines] > bitch of a time with RM. I think I can say that with authority since my > first Master's Thesis was on an algebra of Structured Programming :) I must respectfully disagree. Haskell, for example, is as declarative as they come, and has the boolean type. (Not that I didn't have a "bitch of a time with RM" at first. But this is something that I think a clear algebra would help with a lot.) Marshall >> I have to say that I buy into Date's viewpoint: relations are predicates, and attributes are the variables of the predicate. << I am more inclined toward thinking if them as facts. Facts are always "true" in the sense of existing. You do not talk about substitution in a set. Predicates can be true or false (or whatever other logical values your deductive system has). As Dave McGovran points out, SQL has no rule of inference, so it is not a logical system. We even call them <search conditions> and not predicates. > >> I have to say that I buy into Date's viewpoint: relations are predicates, and attributes are the variables of the predicate. << > [quoted text clipped - 4 lines] > Predicates can be true or false (or whatever other logical values your > deductive system has). I'm sure you've heard of the closed world hypothesis. I'd say this is exactly what SQL operates under. And it seems to me that calling rows "facts" and calling them "propositions" is entirely compatible. (At least under the CWH.) > As Dave McGovran points out, SQL has no rule of > inference, so it is not a logical system. Natural join meets the definition of inference rule. > We even call them <search > conditions> and not predicates. *Some* people call them predicates. :-) Marshall > >> I have to say that I buy into Date's viewpoint: relations are predicates, and attributes are the variables of the predicate. << > > I am more inclined toward thinking if them as facts. A set of facts, then, instead of each predicate being a fact. Isn't it common to think of the propositions and related tuples as (individual) facts and the predicates with variables (those in the header) as a way to see the set of facts that form the relation (feeding into the predicate logic, for example)? The propositions are instances of the predicates as tuples are elements of the relation. Am I getting my terms confused, are you, or is this another area where there is considerable variation within the discipline? Just curious. > Facts are always > "true" in the sense of existing. You do not talk about substitution in > a set. You do talk about instances, however, or values for a particular attribute. One way to look at it is that the propositions substitute a value for the header variable. > Predicates can be true or false (or whatever other logical values your > deductive system has). As Dave McGovran points out, SQL has no rule of > inference, so it is not a logical system. You're telling me! (smiles) We even call them <search > conditions> and not predicates. But it would be helpful not to have the term predicate used for both the relation and each individual tuple in the relation. Is that what you are suggesting or did I misunderstand? Thanks. --dawn |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.