Due to:
1. Taking care of some health issues that have accumulated (not getting any younger);
2. Concentration on the Israel-Hamas War;
3. Effort to update old papers and write new ones;
4. Much needed rest and the holidays.
I am taking the remaining of the year off and will re-start my contributions in January.
Wishing you and yours season's greetings and happy holidays!
“I have read this article in an effort to boost my academic knowledge on data modeling a bit and still have no idea what this academic author wanted to say. Apparently First Normal Form (1NF) doesn't get enough respect and then proceeds to talk about Non-First Normal Form (NFNF). But what about First Normal Form (1NF) damnit.”
PRACTICAL DATABASE FOUNDATIONS
FIRST NORMAL FORM
A DEFINITIVE GUIDE
(September 2023)
Fabian Pascal
Introduction
1. The Normal Form
2. The First Normal Form
3. Domain Decomposability & Atomicity
4. 1NF & Tables
5. SQL & 1NF
5.1. Repeating Groups & Repeated Attributes
After 23+ years DBDebunk.com has undergone a slight refresh. Here is an outline of the changes.
1. The BOOKS and PAPERS are now unified into one PUBS page from which my papers and self-published books can be ordered. There are two paper series:
Some papers will be brought up to date/re-written and new ones will be published.
2. The recommended books links from the BOOKS page have been moved to the LINKS page.
3. The FUNDAMENTALS page was replaced by a SEARCH page that contains a newly compiled data and relational fundamentals comprehensive dictionary of terms to be maintained on a regular basis. It includes abbreviations/acronyms to be used as labels for searching. Use the page as a reference in conjunction with the labels to compensate partially for a limitation of the Blogger labels facility.
4. Labels now exist only for the search terms that have known abbreviations or acronyms. The SEARCH page should be used to identify which terms have labels and those that do not must be used with the Blogger search facility (see details on ABOUT page).
Any problems, comments, suggestions, questions, or support are welcome--drop me an email.
Note: This a revision of an earlier post
RDM is an application to database management of mathematical relation theory (MRT) consistent with simple set theory (SST) expressible in first order predicate logic (SST/FOPL) that is used to formalize symbolically conceptual models of reality as logical models for database representation.
In RDM a domain can "appear" in multiple relations: the domain represents an abstract property, attributes defined on it represent that property in contexts of specific entity groups that relations represent. For example, attribute SALARY in relation EMPLOYEES represents the property represented by domain MONEY in the context of entities of type Employee and attribute BUDGET in relation DEPARTMENTS represents it in the context of entities of type Department.
Note: "Then & Now" (t&n) is a new version of what used to be the "Oldies but Goodies" (OBG) series. To demonstrate the superiority of a sound theoretical foundation relative to the industry's fad-driven "cookbook" practices, as well as the disregarded evolution/progress of RDM, I am re-visiting my old debunkings, bringing them up to the current state of knowledge. This will enable you to judge how well arguments have held up and realize the increasing gap between industry stagnation -- and scientific progress.
“I am an application developer who is competent in SQL and have designed some small-scale schemas that have gotten the job done. I came to your website seeking enlightment on the issue of whether it is better to use strings to represent attributes (I believe these are called "speaking keys"), or to use integers and key them to a list of strings. I see that you are avidly pro-normalization, but I'm not even sure if this is a normalization issue. Here is a quote from a web site that captures my gut feeling. I hope you can comment or point me to an article that presents your view of the matter.
Many popular database tools make it very easy to create some sort of arbitrary numeric sequence for your primary keys. Be careful not to use these to excess. If you have are designing a music catalog system, it is definitely not useful to create a lot of arbitrary codes for music classification. There is unlikely to be much benefit in designating "1" to stand in for "Jazz", 2 for "Rock", 3 for "Classical" and 4 for "Latin Jazz" in an application. Why not just use the word "Jazz" for "Jazz" and use "Rock" to stand for "Rock"? It is certainly easier to remember. It also means that you won't have to lookup the value of 1 to see that it stands for "Jazz".”
Note: In "Setting Matters Straight" posts I debunk online pronouncements that involve fundamentals which I first post on LinkedIn. The purpose is to induce practitioners to test their foundation knowledge against our debunking, where we explain what is correct and what is fallacious. For in-depth treatments check out the POSTS and our PAPERS, LINKS and BOOKS (or organize one of our on-site/online SEMINARS, which can be customized to specific needs). Questions and comments are welcome here and on LinkedIn.
Q: “Does anyone have an alternative solution to the issue where multiple nulls are allowed in the same column for a unique index containing that column? I have a number of such nullable columns and unique index combinations in my database schema and I'm trying to avoid having to create additional generated as columns for each case.”
A: “null<>null so the problem's built into unique columns, perhaps the columns need to become non-nullable?”
“Some attributes in a tuple are not meant to be part of any relationship so people are safe to populate such relationshipless attributes with a Null when needed. Just an example, how about the famous MailAddressSecondLine attribute describing the optional second line of a mail address? You can populate it if needed or you can leave it with no value a.k.a. Null if you have nothing to put in there.”We have discussed extensively the problems with and solution to the treatment of missing data in relational databases:
“So, when YOU create an address table, do you have a "second address" line or do you normalize it? Enquiring minds want to know.”
“You are asking how I would implement optional parts of an address within the limitations of a fixed set of numbered address line attributes? If an address has fewer lines than there are attributes then I'd populate the extra lines with zero-length strings. Null wouldn't be appropriate because all the parts of the address are known and present and using null would give undesirable results. For example if you compare all the parts of an address in a join or subquery you ought to expect two identical addresses to be returned as equivalent, but that wouldn't work if you start adding nulls into addresses.”
Note: In "Setting Matters Straight" posts I debunk online pronouncements that involve fundamentals which I first post on LinkedIn. The purpose is to induce practitioners to test their foundation knowledge against our debunking, where we explain what is correct and what is fallacious. For in-depth treatments check out the POSTS and our PAPERS, LINKS and BOOKS (or organize one of our on-site/online SEMINARS, which can be customized to specific needs). Questions and comments are welcome here and on LinkedIn.
Assessing such arguments normally requires clarification of what exactly is meant by "the relational model". Ross does refer specifically to the "original" -- which we take to mean that introduced by Codd in 1969-70 -- but given the massive misuse and abuse in the industry, perceptions of it may well be corrupted (Nobody Understands the Relational Model Semantics, Relational Closure and Database Correctness). Moreover, there are many predicate logic (PL) systems and many ways of categorizing them (1st vs n-th order being only one way) -- we assume Ross means RDM is based on none.“As I have said many times, if the original relational model had been based on predicate logic and also the semantics and rules of definitions we'd all be better off now. It wasn't. Full stop.”--Ronald Ross, LinkedIn.com
Note: "Then & Now" (t&n) is a new version of what used to be the "Oldies but Goodies" (OBG) series. To demonstrate the superiority of a sound theoretical foundation relative to the industry's fad-driven "cookbook" practices, as well as the disregarded evolution/progress of RDM, I am re-visiting my old debunkings, bringing them up to the current state of knowledge. This will enable you to judge how well arguments have held up and realize the increasing gap between industry stagnation -- and scientific progress.
(email exchange with a reader originally published September 2002)
“Saw your latest and once again I think you have hit one of the many protruding nails on the head. Understanding one's data is so central and so crucial and yet so often ignored.
All this talk (not from you, I note) of silver bullets. Nothing new and I wonder if the paying customers and the big-ticket so-called technology strategy companies will ever wise up. Edward de Bono wrote of 'porridge words' that distract thought from the matter at hand. When used sparingly, they can facilitate new lines of thought but when, as they are in this field, they are used so casually and often they blur the real issues. All this technicalese of XML etcetera has this effect on me.
During one of the few times an employer allowed me to help people with logical design, I was having difficulty because the customer's IT staff knew very little English and had perhaps even less database background. I hit on the idea of explaining tables as relations and relations as sentences - sentences that must have the same 'size and shape'. Their faces seemed to light up and when they agreed that they had overloaded some of their tables, I was very pleased with myself. I felt vindicated a few weeks later when I read an article about predicates and propositions that Hugh Darwen had written in the now defunct DBPD magazine, put these thoughts much more precisely than I could, . Of course, the changes created new problems because the database product, like so many others, gave precious few ways to map the logical design to the physical one. But I regarded these as preferable problems since the staff was much more interested in the more concrete physical optimization techniques.
Without any disrespect to Dr. Codd (who I once met but was too awe-struck to ask any questions of), I have often thought that the language used by everybody in the field, with words such as "tables", nearly always brings connotations of physical arrangements to the mind of anybody who has done traditional programming. This seems unfortunate to me. Especially after I read Mr. McGoveran's proposals for results that might embody more than one table. (I wonder if these might not be part of the key for much better physical integration of databases with their visualization for users, not to mention smarter engines.)
I came across a site https://www.mcjones.org/System_R/ the other day, where a bunch of the System R people reminisced about its development on the occasion of, I think, the 25th anniversary of one of Codd's early papers. Presumably Mr. Date was absent from this gathering so that he could write his own most interesting history, which I remember reading five or six years ago. Anyway, I was struck again by how often their design decisions were either determined or distorted by physical considerations. And now, when many of the obstacles have been overcome courtesy of Moore's and other laws, some of those clever people seem regretful.
Also, please let me submit an historical, non-technical 'nit' to Mr. Date - I remember him writing that Codd did not coin the database term 'normalization of relations' as a result of R.M. Nixon's foreign policy excursion with China. But I also remember reading what I recall was an original interview with Dr. Codd in the DBMS magazine where he stated that this was the case. It's not really important, perhaps I'm just sensitive to it because I live in a country that established relations with modern China a year earlier!”
Note: Reader mail (rm) posts are my exchanges with readers that raise fundamental issues. I may improve language for clarity and amplify with notes for the benefit of readers.
I've written more than once about Chen's E/RM (see references) and recently a reader emailed me a reaction to one of these writings, offering E/RM as evidence that properties were not necessary in conceptual modeling -- everything could be expressed with just entities and relationships. That is, of course, incorrect, but I was working at the time on my latest series of posts on Relationships and the RDM, which was going to address that issue too and I intended to refer him to it. But knowing that David McGoveran knew Chen in the 80s, I asked him if he had any comments. He thought that something broader and more forceful regarding Chen's work was in order and suggested some text. We had some discussion on the subject and decided that I will post my reply (ON PROPERTIES IN CONCEPTUAL MODELING) and publish his reply to the reader later, which I do below.
Note: This is a multipart re-write of a previous series that, when completed, is intended to replace it.
In Part 1 we documented the differences between mathematical and database relations (see table in Part 1). We attributed the fallacy that the RDM can express only one type of relationship -- between relations using FKs -- to the industry being unaware of the adaptation of math relations for database management. We intimated that some of the additional features of database relations express relationships other than between relations.
In Part 2 we identified the intra-group c-relationships (and the corresponding within-relation l-relationships) in our approach to conceptual modeling:
- general dependencies
- entity uniqueness
- functional dependencies (FD)
- entity supertype-subtypes relationships
and used a simple conceptual model (CM) of six entity groups to illustrate them:
Customers (cID, cname, FICO, discount)
Products (pID, pname, price)
Salesmen (sID, sname, sales, salary, commission)
Orders (oID, pID, cID, sID, date, amount)
Order Items (oID, iID, pID, quantity)
Database design is the use of a data model (DM) (here, RDM) to formalize conceptual models (CM) -- including c-relationships -- as logical models (LM) for database representation, so it must be able to convert the business rules (BR) that express those relationships in specialized natural language at the conceptual level to formal constraints in a FOPL-based data sublanguage at the logical level.
Our intention is to demonstrate that the RDM can express all c-these relationships, but we face a difficulty.
Note: "Then & Now" (t&n) is a new version of what used to be the "Oldies but Goodies" (OBG) series. To demonstrate the superiority of a sound theoretical foundation relative to the industry's fad-driven "cookbook" practices, as well as the disregarded evolution/progress of RDM, I am re-visiting my old debunkings, bringing them up to the current state of knowledge. This will enable you to judge how well arguments have held up and realize the increasing gap between industry stagnation -- and scientific progress.
(Email exchange with reader originally published August 2002)
“I'd like to comment on your other recent articles: on denormalization. Of course you prove that denormalization does not improve performance, because you pay for it by maintaining integrity. But, when people say that de-normalization improves performance, they usually mean just on one side. For example, I can merge DEPT and EMP tables into a third table DE and achieve a better query performance by replacing a join by a simple select from the new table. If this is the most frequent and most important operation in my application (vs. updates, inserts, deletes), then my overall performance will be improved (and that's what usually happens in DW). But if the opposite is true, then performance will suffer. I didn't see these considerations in your articles ...
Many people, yes, but not nobody. I always considered the cost of denormalization. I know many people in this field that do the same; however, I do agree with you that many people, especially those "younger" ones learning from more "modern" books on database design, especially those in the OO field, are not aware, and what's worse, don't even want to be aware.
That's exactly how I always thought and when I had discussions with people, that's what I always said to them (not that it made a big difference in their thinking). However, when I read your articles on this topic, I had another thought. As you always say (and again, I fully agree with you on this), we must always separate logical and physical. I always considered denormalization as one of the things done at the physical level. So, denormalization shouldn't even be your concern, because it has nothing to do with the relational model. The rule I always follow is that whatever I do at the physical level, it should not destroy my logical model, which must stay normalized. If I denormalize to achieve some performance gains for a selected set of functions, then I do pay for it by writing additional logic to preserve the integrity and by creating views that represent the entities on my logical model, which I had to "destroy". So as long as I separate these two levels, I don't think I'm in any conflict with the relational model. Of course if DBMS gave me more options in physical design while protecting the integrity of my logical model, I wouldn't have to do this myself.
Theoretically, I think the way you do, and that's why I enjoy reading your columns. But I also have to deliver practical results to my users. Unfortunately, I can't go to my users and tell them that their response time is slow because of Oracle's technology. And I don't believe screaming at Oracle will do me any good either (and yes I know what you will say to this). So until that mysterious technology you mentioned many times is implemented, I have to do what I can.”
Note: This is a multi-part complete re-write of a previous series which, when completed, is intended to replace it.
In Part 1 we attributed the fallacy that the RDM can express only one type of relationship -- between relations, using FKs -- to practitioners being unaware of the adaptation of math relations to database management and missing the additional features of database relations. We documented the differences in features between math and database relations (see the table in Part 1) and intimated that some of the additional features express relationships other than those between relations using FKs (which we leave out in this discussion).
In this Part 2 we identify the c-relationships and use a simple conceptual model (CM) of six entity groups:
Customers (cID, cname, FICO, discount)
Products (pID, pname, price)
Salesmen (sID, sname, sales, salary, commission)
Orders (oID, pID, cID, sID, date, amount)
Order Items (oID, iID, pID, quantity)
to illustrate them (to recall, we prepend 'relationship' with c- and l- when we use the term at the conceptual and logical levels, respectively).
Note: "Then & Now" (t&n) is a new version of what used to be the "Oldies but Goodies" (OBG) series. To demonstrate the superiority of a sound theoretical foundation relative to the industry's fad-driven "cookbook" practices, as well as the disregarded evolution/progress of RDM, I am re-visiting my old debunkings, bringing them up to the current state of knowledge. This will enable you to judge how well arguments have held up and realize the increasing gap between industry stagnation -- and scientific progress.
(This a revised version of an earlier post with clarity improvements).
Q: “What would you suggest for a datetime field where the value is not known and should therefore be not-applicable?”While searching through records I came across an old consulting project involving the migration of a neo-natal research database from Focus -- an old hierarchic DBMS -- used to record extensive details about hundreds of monthly births at a university hospital for more than 20 years. The person who had designed the Focus database was the only one who knew and understood its complexity sufficiently to maintain it. Each time a researcher needed some subset of data to analyze, he would extract it and serve it upon request. Aside from the inefficiency of the process, the person was retiring at a time when hierarchic DBMSs reached the end of their usefulness, Focus experts were already few and expensive and "relational" (read: SQL) was the dominant fad.
A: ”NULL sounds good to me.”
I just received the following email from Quora:
-----------------------------------------------------------------------------------------
| |||||
|
------------------------------------------------------------------------------------------
What?????????? I had just posted my first answer on Quora:
They don't mention, of course, that they bombard me several times a day with spam, including asking me to answer random questions on the platform!!!!
I left Facebook, I am practically off Twitter -- so why the hell did I bother to engage on any new platform? Serves me right.
UPDATE: I appealed and the post was reinstated with apologies. Aside from the insult and the hassle to complain, corrections will become practically impossible as "AI" algorithms take over everything. You will have to appeal to these algorithms too and that will be talking to the wall.
“... we are not modeling objects/entities/attribute ... at all in the relational model, [but] a bunch of relationships ... hence perhaps Codd was correct in calling it a "relation", a bunch of relationships ... Interesting that most people think of relationships as being the distinguishing characteristic of a relational model and it is not ... [it] has no relationships since Codd decreed that all relationships must be represented by foreign keys, which are exactly the same as "attributes ... What [other] type(s) of relationships can be explicitly and formally defined in a relational data model? Of course there are many other relationships which can be inferred, such as between an attribute and an entity identifier. Please give me a precise reference to where Codd spoke of relationships [differently than i]n his 1985 piece published in ComputerWorld, [where] he said that the only way to represent a relationship (between relations) was through explicitly stored values (i.e., attributes, foreign keys) ... In my personal understanding, a relation is defined as a set of tuples. Then ... "in the relational model every relation represents a relationship". And then a quote from Chen: "each tuple of entities ... is a relationship". If I use the first and the second statements - I can say that a relationship is a set of tuples. The third statement says that a relationship is a tuple. So far, is a relationship a set of an element of a set? (Or may be a set of sets?) ... I argue that there is essentially no difference between relationships between entities of distinct classes and between properties of the same class. They both represent relationships. A property can represent a relationship between entities of distinct classes. If such relationships are represented by foreign keys and the relations representing the classes must be in 1NF, then relational databases can represent only M:1 relationships, a very unnecessary limitation when modeling some reality of interest ... The entity-relationship model is essentially a directed graph model, where relationships are prominent residents. Not so in the relational model (despite the name), where relationships (between relations, mind you) are not visible and in the SQL implementations is reduced to constraints. Relationships are about structure, which is as important as meaning (the semantics of the terms used in the universe being modeled).” --LinkedIn.com
The amount of nonsense squeezed into this rambling is mind boggling. No understanding of the RDM, confusion, abysmal reasoning and misuse of terms -- debunking it in its entirety would be practically impossible (believe me, I tried). Instead, I focus on a critical aspect of the RDM of which there is little grasp in the industry: I convey the fundamentals and leave it to the motivated reader to try their own debunking -- the most effective way I know to learn.
Note: Reader mail (rm) posts are exchanges with my readers that raise fundamental issues. I may improve language for clarity and amplify with Ed. Notes for the benefit of readers.
“Your post Understanding Conceptual vs. Data Modeling Part 1: Data Model - The RDM Is, the E/RM Isn't is well done. However, concepts and relationships can be perceived and modeled without formulating or specifying properties. Chen did that in his ER diagrams. And informally, everyone does it as a mental model every day. I suppose anyone can define conceptual modeling however they wish to. But at its minimum and most abstract, which is what conceptual modelling is usually understood to be, it can be done without formulating or specifying properties.” --GR
Note: "Then & Now" (T&N) is a new version of what used to be the "Oldies but Goodies" (OBG) series. To demonstrate the superiority of a sound theoretical foundation relative to the industry's fad-driven "cookbook" practices, as well as the evolution/progress of RDM, I am re-visiting my 2000-06 debunkings, bringing them up to my with my knowledge and understanding of today. This will enable you to judge how well my arguments have held up and appreciate the increasing gap between scientific progress and the industry’s stagnation, if not outright regress.
“I find [your article in DM Review] to contradict your stated devotion to scientific methods and the value of theory. You present a single example of denormalization, then proceed to draw a conclusion about denormalization in general. In addition, the example chosen is not typical of real world denormalizations.” In order to be half-way consistent with your own ideals, you would need to present at a minimum an exhaustive list of the types of denormalizations used in practice, along with an objective list of the pros and cons of each. I would expect that if this were undertaken, you would end up with a more balanced view, and some exceptions to your black-and-white conclusions. Of course, to prove your point scientifically would require far more effort than this, if indeed it were at all possible to prove or disprove your statements. This brings me to my key point: if your contention is not falsifiable, it does not belong in the realm of true science at all, instead it belongs in the domain of mere opinion and belief. Please tell us how you have proved your propositions, or else refrain from claiming that you are working from a sound scientific foundation and everyone else is somehow misguided. Relational algebra has nothing to say about real-world performance.”
Note: In "Setting Matters Straight" posts I debunk online pronouncements that involve fundamentals which I first post on LinkedIn. The purpose is to induce practitioners to test their foundation knowledge against our debunking, where we explain what is correct and what is fallacious. For in-depth treatments check out the POSTS and our PAPERS, LINKS and BOOKS (or organize one of our on-site/online SEMINARS, which can be customized to specific needs). Questions and comments are welcome here and on LinkedIn.
“A conceptual data model usually just includes the main concepts (entities) required to store information and the relationships that exist between these entities. We don’t usually include any details about each piece of information. We can consider the conceptual stage as an initial model, without all the details required to create a database.
A logical data model is probably the most-used data model. It goes beyond the conceptual model; it includes entities, relationships, details on entities’ different attributes, and unique ways to identify entities (primary keys) and establish the relationships between them (foreign keys).
A physical data model is usually derived from a logical data model for a particular relational database management system (RDBMS), thus taking into account all technology-specific details. One big difference between logical and physical data models is that we now need to use table and column names rather than specifying entity and attribute names. This allows us to adapt to the limits and conventions of the desired database engine. We also provide the actual data types and constraints that allows us to store the desired information.”
--Vertabelo.com
Note: In "Setting Matters Straight" posts I debunk online pronouncements that involve fundamentals which I first post on LinkedIn. The purpose is to induce practitioners to test their foundation knowledge against our debunking, where we explain what is correct and what is fallacious. For in-depth treatments check out the POSTS and our PAPERS, LINKS and BOOKS (or organize one of our on-site/online SEMINARS, which can be customized to specific needs). Questions and comments are welcome here and on LinkedIn.
What's right/wrong about this database picture?
“Other than constraints on cardinality, business rules are not generally represented on data models of either kind. Even in the case of business data models, the models are supposed to represent fundamental structures, while business rules represent variable constraints.”
--TDan.com
Note: "Then & Now" (T&N) is a new version of what used to be the "Oldies but Goodies" (OBG) series. To demonstrate the superiority of a sound theoretical foundation relative to the industry's fad-driven "cookbook" practices, as well as the evolution/progress of RDM, I am re-visiting my 2000-06 debunkings, bringing them up to my with my knowledge and understanding of today. This will enable you to judge how well my arguments have held up and appreciate the increasing gap between scientific progress and the industry’s stagnation, if not outright regress.
This is a re-published series of several DBDebunk 2002 posts on Simon Wlliams' Lazy Software so-called "Associative Model of Data" (AMD), academic claims of its superiority over RDM ("The Associative Data Model Versus the Relational model") and predictions of the demise of the latter ("The decline and eventual demise of the Relational Model of Data").
