Talk:Database normalization

This article was tagged, a few months ago, as being too technical, but apparently without any suggestions for improvement or explanation of what is not understandable. This article describes a specialist activity. I think the objectives of normalization need to be explained in terms the non-specialist can understand, but I think this is adequately done. I'm not sure that it is possible to describe the concepts that are central to database normalization in less technical terms. I believe they are introduced in a way that a non-specialist can grasp at a level appropriate to their level of knowledge. Is it possble to express the concepts in less technical terms without introducing serious errors or misunderstandings? If the inadequacies cannot be decribed in more detail, I propose to remove the tag. --Boson (talk) 16:25, 21 July 2012 (UTC)

I think the first paragraph can be improved further. I don't think of 'dependency' and information being 'propagated through the database' are the clearest way to express the intention. I'd rather say that normalization strives to represent each atomic piece of information as a single value, such that when information changes, that change can always be expressed as a change to the corresponding values in the database and nothing else.

First normal form is not allowing multiple values in a single table cell. I don't think it is associated with a particular normalization operation; a candidate would be the unnest operation of nested relational algebra, but that only covers cases where a column represents sets of values, and first normal form is usually understood in a broader sense.

The other normal forms are very different, and very similar to each other: they are various degrees of vertical decomposition of tables into smaller tables, such that joined together these tables produce the original table. Here, the goal is complementary to that of first normal form, namely avoiding duplication of the same values across different rows in the same table. Any explanation of these normal forms should start with the project and join operations of relational algebra. By skipping this connection the present article jumps from a very general and imprecise introduction to excessive detail. Rp (talk) 17:10, 21 July 2012 (UTC)

I agree about the first paragraph, though I'm not sure about "strives to represent each atomic piece of information as a single value . . .". That could be thought of as a definition of 6NF (which few people strive for) - at least if we equate atomic with irreducible. On reflection, I agree that more is needed to bridge the gap between the general/imprecise introduction and the more detailed technical stuff.--Boson (talk) 19:26, 21 July 2012 (UTC)

Addressing the "too technical" tag, I propose to add this sentence somewhere in the "lead" section:

For instance, a unique ID is stored everywhere in the system and it's name is just held in just one table. The name can be updated more easily in one row of one table.

adding an example too...

A typical update would be the R.I.M. company changing its name to Blackberry.^[1] That update would be done in one place and immediately the correct "Blackberry" name would be displayed throughout the system.

CITE REF USED ABOVE: http://bgr.com/2013/01/30/rblackberry-z10-q10-announce-311920/

Mediation4u ^(chat) _{nb: editing is fun} 13:22, 3 June 2014 (UTC)

I have now added this to the article, after 2 weeks allowed for any feedback. Mediation4u ^(chat) _{nb: editing is fun} 12:20, 19 June 2014 (UTC)

"Even by the removal of {Employee ID} functional dependency still holds between {Employee Address} and {Skill}."

How can there be a functional dependency between {Skill} and {Employee Address}?, consider two people living in the same house. — Preceding unsigned comment added by Prathik Rajendran M (talk • contribs) 13:21, 29 December 2012 (UTC)

I agree that the quoted claim in the above comment seems incorrect, although I think think that the above argument makes a case for why {Employee Address} -> {Skill} is not true, while I think it is more pertinent to demonstrate that {Skill} -> {Employee Address} is not true (which is what I think the example in the article argues in its explanation). This argument, though, is even easier to disprove than the one that Prathik debunked, as it is quite possible for someone living at address A to possess the skill of typing and someone else at address B to also possess the skill of typing. But regardless of which claim must be disproved ({Skill} -> {Employee Address} or {Employee Address} -> {Skill}), they both seem to be incorrect. Perhaps the example should argue that {Employee Address} does not have a full functional dependency on {Employee ID, Skill} because {Employee Address} has a functional dependency on the proper subset {Employee ID}, although even that is debatable as some people have more than one residence, but it was something that seemed to be enforced in the mention of the update anomaly. Brennere (talk) 19:08, 30 January 2013 (UTC)brennere

The Mike Chapple article on About.com mentioned at the bottom is Ghostwritten, it's copied verbatim from the Microsoft Access Manuals it seems.. If you look in the 2NF reference, there is a a reference to "Sea Cliff, NY 11579" and "Miami, FL 33157" which does not have a corresponding figure on about.com, to locate the figure I did a search on google and found a number of documents, including a PDF of a technicians manual at AIMS.. Which leads me to to believe Mike is either outright plagiarizing, or its ghostwritten by a foreign outsourcer.. There probably isn't a copyright infringement as it seems Microsoft owns 5% of IAC which is the parent company of About.com . It should go noted.. 172.0.207.115 (talk) 02:43, 13 February 2014 (UTC)

The article reads "By definition, a table always meets the requirements of its HNF and of all normal forms lower than its HNF; also by definition, a table fails to meet the requirements of any normal form higher than its HNF" but then goes on to say that a database in 3NF is usually also in higher normal forms. I feel like this is sort of confusing. So 3NF isn't usually the HNF for a 3NF table? Some guy (talk) 19:38, 10 June 2014 (UTC)

I think this just means that the "highest normal form" of a table is defined as the highest NF that the table meets.

So if you say that the higest normal form of a table is 2NF, this means that it is not 3NF and that you can also say it is 1NF (because 2NF meets all lower requirements.)

On the other hand most 3NF tables meet the requirements of, say, 4NF, so 3NF is not the HNF for most 3NF tables.

--Boson (talk) 21:15, 10 June 2014 (UTC)

But if a table meets the requirements of 4NF why would one say that it is 3NF? Is it not standard practice to state the table's HNF as its NF? Andrew S. (talk) 14:52, 24 July 2015 (UTC)

It depends on context. If you say all tables must be in 3NF, you would not exclude tables that are in 4NF, so if someone asked if a table was in 3NF, you would say yes, though that is not its HNF. Similarly, an even number remains an even number, even if it is divisible by 10. --Boson (talk) 17:34, 24 July 2015 (UTC).

The problem I see here is 1NF. 1NF should be excepted here. It is fundamentally different from the other normal forms, and the article should explain this better:

• 1NF is a property of the individual values that appear in a database, the value domains; it doesn't say anything about the structure of a database or the relationships between values within a database. There is no standard way to mathematically formalize 1NF: if you want to do it, you have to do it in terms of constraints on values in domains, or in terms of how values in the database may be used by queries performed on the database.
• 2NF and higher, on the other hand, are completely independent of the particular values in the database, and are all about dependencies, i.e. a particular kind of cardinality constraint on the relations formed by the values in tables.

Hence, being in 1NF and being in 2NF are independent properties. Even if you provide a mathematical definition of 1NF, being in 1NF will be independent from being in 2NF. The quote from the article is wrong if 1NF is included.

2NF and higher are defined mathematically, and these definitions are such that for each i > j > 1, every database in iNF is also in jNF. Hence, for all NFs above 1, the quote is correct. Rp (talk) 16:11, 12 June 2014 (UTC)

But there is no NF below 1, so doesn't the statement "a table always meets the requirements of its HNF and of all normal forms lower than its HNF" apply? --Boson (talk) 21:07, 12 June 2014 (UTC)

?? The distinction required is between 1NF and >1NF. Rp (talk) 12:54, 13 June 2014 (UTC)

I'm still not sure I get the distinction being made. I can see that you may want to express the rule for 1NF and >1NF differently in simple "mathematical" terms, but for 1NF the condition "there exists at least 1 lower NF" is false; so in normal language, surely, if the highest normal form of a database is 1NF, then the condition

• "meets the requirements of 1NF and all (= any existing) normal forms lower than 1NF.

is always true. --Boson (talk) 15:29, 13 June 2014 (UTC)

One problem is that a table can be in 2NF without being in 1NF, and the text doesn't say what its 'HNF' is in that case. Another is that once you fix this, the statement that a table is always in its highest normal form (by definition of 'HNF') becomes trivial and I think trivial statements only serve to confuse the reader. Yet another is that the statement is confusingly similar to two statements often made about normalization that do make sense, namely,

• that each table in (n+1)NF is always in nNF for all n > 1 (by definition of those normal forms); this does not include 1NF;
• that as a general rule of database design, the lower the normal form, the more important it is for databases to be in that form; this includes 1NF. Rp (talk) 10:28, 20 June 2014 (UTC)

If a table "can be in 2NF without being in 1NF, we neeed to change the text of Second normal form, which currently states "a table is in 2NF if and only if it is in 1NF and . . ." (which seems to come straight from Date). Could you suggest an example? --Boson (talk) 00:52, 21 June 2014 (UTC)

I don't think everybody who defines 2NF requires this. If 2NF requires 1NF by definition, then things become much simpler; the present text can then be clarified by saying that all normal forms are defined such that (n+1)NF always implies nNF. Rp (talk) 22:01, 21 June 2014 (UTC), edited 25 June

I realize that some links are duplicates since they exist in the body of some preceding paragraphs but I thought it would be better to duplicate them in a complete list instead of calling the section "Other Normal Forms" and only listing forms not discussed in the rest of the article. Andrew S. (talk) 15:15, 24 July 2015 (UTC)

By way of disclaimer, I am not a database expert myself — far from it. I'm simply a novice web developer seeking to advance his understanding of databases and relational theory. I've made the following changes; in all cases, please correct me if I'm mistaken.

1. My understanding is that normal forms are, by definition, the formal end-goal of database normalization. As such, I have edited the lead section to redefine normalization in terms of normal forms, and extracted the list of internal links to various normal forms out of the "See also" section into its own "Normal forms" section.
2. I've also removed bits that appeared redundant to me, as well as the bit about "achieving the optimal structure composed of atomic elements" (which, in my understanding, relates only to 1NF).

More importantly, I'm requesting the following changes from an expert:

1. a broad overview of what normal forms are exactly; e.g., the property that each normal form requires all prior normal forms to be met (currently, it only features a brief history of when they were proposed and a list of links to each one); and
2. a modest reorganization to ensure that no terms are used before they are clearly defined or linked to corresponding articles (currently, "Objectives" mentions normal forms, which aren't defined anywhere, and "Normal forms" mentions update/insertion/deletion anomalies, which are defined in "Objectives").

Pennbrook (talk) 03:42, 10 January 2018 (UTC)

I've added a step by step normalization example illustrating each normal form. I've ceased to think of an example for EKNF though... I'm by all means no expert on databases, so I welcome any corrections... Honzikec (talk) 21:29, 23 January 2019 (UTC)

Thanks Honzikec I think that's super helpful. I just went over it and fixed two problems, hopefully I didn't do anything wrong. I think one remaining problem is that right now it's not clear what's the difference between ETNF and 5NF as the example seems to be essentially saying the same things. --a3nm (talk) 20:25, 21 April 2019 (UTC)

Not sure of how to fix it, but the step-by-step example seems to have another problem: after the 3NF step, the schema is not actually in 3NF because the dependency removed in the BCNF case also violates 3NF. Pinging Honzikec, in case you have an idea of how to fix the example? --a3nm (talk) 22:20, 27 May 2019 (UTC)

A3nm, we could probably replace the BCNF example with some reservations inspired by the Wiki article on BCNF (https://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_form) ... I currently don't have time to address this in detail though :( Honzikec (talk) 14:11, 29 May 2019 (UTC)

Honzikec, thanks for your input. No worries if you can't do it, but just to be sure, you agree that there's currently a problem, right? Also, if you see a way to integrate the examples from the BCNF article in a way that fits with the running example of this article, this would be interesting. (As for actually doing it, maybe I could take care of it.) --a3nm (talk) 15:09, 29 May 2019 (UTC)

Yes, this is extremely confusing and had me scratching my head for an hour until I visited this talk section. Something would seem to be in 3NF but not BCNF if information is missing from the table? That what the example on https://en.wikipedia.org/wiki/Boyce–Codd_normal_form seems to imply.

As stated above, the example involving the anomaly Author => Author country is wrong since this anomaly is required to be solved in order to have a 3NF table. This is true since we are having a transitive dependency, with a non key attribute (Author), thus 3NF does not hold. It is the same case as with Genre id => Genre. Once we solve the Author => Author country anomaly, the table becomes 3NF but also BCNF. In general, as soon as we are 3NF and without at least two overlapping key, the table will be BCNF too. This because a non-key attribute should depend on whole key without transitive dependencies (2NF and 3NF holding). Key attributes have no dependencies, since there is only one superkey candidate: if this was true, we would have a redundat key (say PK is {A,B,C}. if AB=>C, then a minimal super key is {A,B}) I would suggest the following modifications:

1. We modify Book table PK from Title alone to {Title, Author} (this is someway more realistic, since there are many different books with same title, think about something like "Linear Algebra")
2. We add one more column "Publisher Author ID" which is defined as a unique identifier of an author, within a publisher (ok this is less real world, but in my opinion easy to understand). In this way we have a new dependency {Publisher ID, Publisher Author ID} => Author, and a second super key {Publisher id, Publisher Author ID, Title}
3. Removing the {Author} => {Author Title} in 3NF paragraph and choosing {Title,Author} PK, we now have a 3NF table but not a BCNF compliant one. Infact BCNF requires that if we have A => B, A should be a super key, but now we have {Publisher ID, Publisher Author ID} => Author with {Publisher ID, Publisher Author ID} which is not a key.
4. To become a BCNF table, we add a many to many table Publishers_Authors with columns PublisherId (FK to Publisher), Author (FK to Authors table), Publisher Author ID. Then we can remove "Publisher Author ID" from Book table and have a BCNF

What do you think? Pluttero (talk) 14:31, 16 May 2020 (UTC)

@Pluttero: I have realized the same issue while reading the article. So your contribution is very welcome. Please go ahead. — Maggyero (talk) 11:30, 21 June 2020 (UTC)

Having First and Last name in the same column isn't atomic. If you want to sort by last name, you can't do it without parsing. — Preceding unsigned comment added by Grr (talk • contribs) 00:41, 9 July 2020 (UTC)

Whether a particular field is atomic or not depends on how it will be used. Having first name and last name seperated may be neccessary if sorting by last name is something you want to do, but this may not be neccessary in the given scenario. I think the example is acceptable as is. AntPraxis (talk) 10:48, 17 February 2021 (UTC)

"Atomic" is really a simplification of 1NF. The definition is to eliminate non-simple domains, which means collection types. A string type is considered a simple domain, while a column which allowed an array or set of strings as value would would be non-simple. Deciding on one or two columns for a name depends on business requirements, but is unrelated to the issue of normalization, since in either case the attribute domains are the same: a string. --80.62.117.218 (talk) 11:00, 9 June 2021 (UTC)

This table has a transitive dependency {Title} → {Author} → {Author Nationality}, which violates 3NF.

Unfortunately, fixing this is not trivial since later in the section about BCNF this same dependency is described to violate BCNF and is decomposed to illustrate the process of normalizing a database from 3NF up to BCNF. But, as I said, the table is not in 3NF in the first place!

Therefore, the sections: #Satisfying_3NF and #Satisfying_BCNF are incorrect and misleading.

Also see this reddit thread that discusses this Wikipedia page. — Preceding unsigned comment added by 5.173.128.7 (talk) 10:03, 12 August 2020 (UTC)

EDIT: Working with the assumption that no content is better than wrong content, I removed this wrong content for now, but in principle better examples should be provided here instead. 5.173.128.7 (talk) 10:10, 12 August 2020 (UTC)

The incorrect example you removed appears to have been re-added. Also the BCNF example is problematic because author name is unlikely to be unique. Author-Nationality (which should just be called Author) should have a surrogate key, because there could be two authors with the same name from two different countries. In fact if we consider duplicate author names, author name does not even functionally determine author nationality. AntPraxis (talk) 11:09, 17 February 2021 (UTC)

The problem is about the subject column isn't atomic. Giving the subject a own table is to normalize this 1NF example, but why is for the publisher column also given a own table? There is even no word about the publisher in the problem description at all. 80.208.142.162 (talk) 16:12, 25 April 2021 (UTC)

In the normalization grid, the first step is UNF (Unnormalized form) which is described as requiring primary keys. I think this is potentially confusing. "Unnormalized form" have no exact definition AFAIK, but can be used to refer to "free-form" data like an Excel sheet which allow duplicate rows and inconsistent columns. But Codd defines normalization as starting with relations, which means it is already assumed there is no duplicates, no intrinsic order, a well defined number of attributes and domains and so forth. The terms 0NF (zeroth normal form) and NFNF (non-first normal form) is often used to describe data which is in relational form but does not conform to first normal form (i.e may have nested relations). Therefore I suggest we create a separate page (perhaps called 0NF or NFNF) which describes the constraints of a relational form of data as separate from unnormalized data. --80.62.117.218 (talk) 10:47, 9 June 2021 (UTC)

"Until the new faculty member, Dr. Newsome, is assigned to teach at least one course, their details cannot be recorded."

"Their"? Who are they? At first I was confused, and then I thought that it probably is the new "inclusive" language. Who cares about the sex of an imaginary example doctor other than some crazy zealots? Use a singular pronoun. I suggest using "his", but this is only because that is the tradition. Again, I do not mind the sex of an imaginary doctor. If you think female representation is needed in fictional examples, use "her". Sin Jeong-hun (talk) 12:11, 11 June 2022 (UTC)

The singular "they" is a commonly accepted usage in modern English. In this case, the sex of the doctor is completely ambiguous and the language can reflect that. Jtbwikiman (talk) 19:58, 8 July 2024 (UTC)

After looking at the definition of "primary key" I find it surprising that the term would be involved in the definitions and descriptions of the normal forms at all. The "primary key" concept seems to be an implementation detail of RDBMSs that doesn't even necessarily have to exist on a given implementation.

The part that states "As a prerequisite to conform to the relational model, a table must have a primary key, which uniquely identifies a row." doesn't seem true.

The Relational Model article states "Usually one candidate key is chosen to be called the primary key and used in preference over the other candidate keys, which are then called alternate keys." which implies that it is not strictly necessary to choose a primary key.

It seems like the formal and informal definitions in "Every non-prime attribute has a full functional dependency on a candidate key (attributes depend on the complete primary key)" disagree. If any candidate key can be chosen as the primary key and the informal definition is assumed to be correct, then that implies that the formal definition should say "on each candidate key" rather than "on a candidate key" because we don't know which candidate key will be chosen as the primary key.

I don't know how the canonical texts on normalization define these, but the definitions in this article seem problematic. If "primary key" is not used in the formal definitions, perhaps the use of the term should be relegated to one section that clearly explains that the informal definitions aren't strictly correct and should only be used as an convenience to help guide intuition. Since the large example section accounts for most of the explanation of the normal forms and heavily make use of the term "primary key" it makes it seem as though the term is part of the real definition. 172.10.118.68 (talk) 17:25, 2 April 2023 (UTC)

You are right. The use of the phrase primary key is wrong and inconsistent. Candidate keys are what matter. Oradium (talk) 22:09, 8 April 2023 (UTC)

I can't recall a time I've ever left Wikipedia to go and learn the basics of a topic elsewhere, because the Wikipedia article was too complicated. But I just did that, for this topic. I'm a software engineer and even for me, this article is very difficult to follow. Due the fact that I don't yet understand the topic, I can't really fix it. But I (and presumably others) would be grateful if someone could dumb this one down some. At least the introduction and early parts of it. This article seems to assume a high degree of familiarity with a lot of material, and knowledge of a lot of specific jargon. That people coming here may not have. As this is covering a pretty high level and general topic. JaHolo (talk) 21:35, 1 August 2023 (UTC)

This is where I wound up and 15 seconds in, I felt I had a much better understanding of what Database Noramlization is, than I did in several headache inducing minutes trying to figure this one out. https://www.lifewire.com/database-normalization-basics-1019735 JaHolo (talk) 21:39, 1 August 2023 (UTC)

Ok, with a tiny bit of knowledge one thing I can say is that the 'Objectives' section seems to assume an understanding of what the Normal Forms are. When that isn't even covered until the next section. And there, it's covered in a pretty technical way. A paragraph of introduction on the normal forms - at the end of the introduction or before 'Objectives' would be extremely helpful. JaHolo (talk) 22:50, 1 August 2023 (UTC)

That article isn't a very reliable technical source. It appears to be a blog. "Dumb it down" isn't one of the goals of Wikipedia. Andre🚐 21:50, 23 November 2023 (UTC)

Agreed. I can recall hundreds of times I had to go learn the basics of a topic elsewhere, but I suspect I spend more time on math-related pages than JaHolo.

This page is not a beginner's tutorial, like it or not. It does not need to be immediately digestible to all software engineers. Jtbwikiman (talk) 20:00, 8 July 2024 (UTC)

Hello! As I was reading through this article, I noticed a small inconsistency in the 1NF example that could trip up readers. The example introduces a composite candidate key {Title, Format} for the Book table in order to satisfy the relational model and support normalization. This makes sense — especially since Price depends on Format (as shown later in the 2NF section). However, the associated Title–Subject table shown as the result of 1NF normalization only includes Title. For consistency with the key structure at this stage, I suggest updating that table to include Format as well. Happy to help draft a patch or make the change directly if others agree. Thanks for the great resource!

--User:opzesty Opzesty (talk) 18:27, 14 May 2025 (UTC)