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
