26

I learned about normalization recently, and understand how important it is when implementing a new schema.

How can I check if my database is 2NF or 3NF compliant ?

Manual review is a sure option, but I'm looking for an automated tool here.

I'm not looking for a point-and-click tool, more something that would highlight possible optimizations to make a table 3NF compliant. I guess it might use statistics based on good sample data and/or semantic analysis of columns names.

Improve this question
4
  • 1
    I hope such a tool doesn't exist. 2NF/3NF doesn't need to be enforced. Designing a schema/model often has to take practicalities and performance considerations into account, which usually chucks xNF out of the window
    – Philᵀᴹ
    Sep 22, 2012 at 23:06
  • 2
    Does it mean normalization is not used in real ? I planned to use it as a starting point, and will consider denormalizing when it slows performance down.
    – ack__
    Sep 22, 2012 at 23:42
  • 3
    Schema design is more of an art than a science. Sure there are basic rules to follow, but once you do it enough you start to get a good feel of what works and what doesn't. Craftmanship.
    – datagod
    Sep 23, 2012 at 1:42
  • 2
    I don't agree with Phil. Normalization is sometimes the way to go. Other times star schema is better. It depends on what you are doing. You wuld need an awful lot of tables before automation would be worthwhile.
    – Walter Mitty
    Mar 22, 2018 at 17:24

3 Answers 3

Reset to default
27

Normalization absolutely is used in the real world... and hopefully you know that 3NF is only the third one of... what is is now, 8? But 3NF should be an easy target.

However... I would venture to say that there could not be such a tool.

Normalization, technically, is an attribute of each table. Within a given database, different tables may have different levels of normalization.

Each table represents facts... facts about instances of a certain type of thing (person, account, order, shipment, item, location) including, sometimes, foreign keys which lead you to other kinds of facts about that thing.

Normalization has to do with how accurately and efficiently facts are represented in the tables as well as the ability of table's design to prevent ambiguous and redundant data patterns.

Thus, an understanding of the actual facts is required... which is outside the scope of automated tools.

Q: Is a table with { student, subject, instructor } in 3NF?
A: What are students, subjects and instructors?

In a world where all instructors taught all subjects and each student could take any combination but not more than one course on each subject from each instructor, this table could indeed be said to be in 3NF. In the real world, making the claim of 3NF for this table is absurd.

To understand that is isn't in 3NF requires an understanding of the nature of the facts it represents. In our reality, this table is not going to be 3NF since (among other reasons) the subject and the instructor are associated together in ways that have nothing to do with the student. If we have the courses where instructors teach subjects stored elsewhere in our database, why would we copy both values here instead of a foreign key from the other table indicating that the student was signed up for the course? If the instructor is replaced, we have to change multiple records in multiple places.

The more normalized a database is, the more intrinsically consistent it is with the real world and with itself, and the more difficult it is for the database's facts to be inadvertently untrue. Database design is an art, but it is most definitely a science as well.

Even though I do not see eye-to-eye with everything he writes, I would recommend Chris Date's book, Database Design and Relational Theory: Normal Forms and All That Jazz which goes into excruciating detail about the underlying theory of the relational model.

Improve this answer
6
  • 2
    Thanks for this detailed answer. However, following your example, it should be possible for a tool that analyze sample data to detect that "instructor" values are repeated a lot, and also appears in another table, hence suggesting it might be candidate for foreign key or any other change that would make this table 3nf compliant, or am I missing something ?
    – ack__
    Sep 23, 2012 at 20:35
  • 2
    True, but the key word is still "suggest." A tool could suggest structural changes by looking for apparent redundancy, but I would still maintain that genuine redundancy and a coincidental appearance of correlation would be difficult for such a tool to differentiate between. Knowledge of the real-world nature of the data is required.
    – Michael - sqlbot
    Sep 23, 2012 at 23:54
  • @ack__: to be in 3rd normal form or not is a property of a relation not of a pair or a set of relation. So I can't see that establishing a foreign key relation has anything to do with 3rd normal form.
    – miracle173
    Sep 24, 2012 at 2:37
  • I think the bigger issue is that determining multi-column functional dependencies is problematic esp. if values may be the same and yet represent different entities (i.e. where composite keys are involved). Consider a table: (address_text, city, state_province, mail_code, country). Now this is not normalized. (city, state_province) -> (country). So to fully normalize we have to break this off into at least three tables, maybe four (mail_code handling!) but the composite keys make this problematic to detect on the fly. Also comp. keys are key to the idea of nf's so no ignoring them!
    – Chris Travers
    Sep 27, 2012 at 10:12
  • Could someone explain why "In a world where a student could only take a course in one subject and all instructors taught all subjects, this table could indeed be said to be in 3NF" That seems to establish that Students -> subject and nothing more. Sense the key for this table would then be (students, instructor) wouldn't that break 3NF? What does every teacher teaching every subject help us with?
    – Drew Verlee
    Apr 9, 2014 at 22:06
6

Part of the point of using formal methods in developing relational theory was to develop procedures that could be automated. I'm pretty sure C.J. Date comes right out and says that in his Introduction to Database Systems.

Several CASE tools released in the late 1980s or early 1990s could derive every possible 5NF schema either by analyzing ER diagrams or by statistical analysis of sample data.

Visio Enterprise Architect (I think) is a relatively recent tool along those lines.

Improve this answer
1
  • 3
    Analysis of tables with sample data and suggestions for normalization was a feature of Access, and in early versions, if I remember well.
    – ypercubeᵀᴹ
    Sep 23, 2012 at 21:15
3

I think that if you could write a tool to do it, it would still take manual review to make work. normal forms are defined on the basis of functional dependencies and these can't be determined from mere inspection of the data. In other words, it is a matter of inductive rather than deductive logic in order to determine whether a functional dependency exists.

Improve this answer
4
  • Indeed there will be some manual review to do, I don't expect a tool to do it fully automatically. But I'm pretty sure most of the work can be automated by analyzing existing data in the DB (at least, samples of it), and/or fields name/type .
    – ack__
    Sep 23, 2012 at 12:20
  • 1
    How? Normalization is defined on join dependencies and functional dependencies. A functional dependency is basically easily defined as: for every possible value of a superkey, there is exactly one value in the functional dependency. You can't get there by automating the review of the data because just because you have, for your current set, what appears to be a functional dependency, that doesn't mean that a new key will conform. It is very much like the old correlation vs causation division. You can't induce a functional dependency by observing what is currently in the database.
    – Chris Travers
    Sep 23, 2012 at 13:42
  • 2
    at least a tool can suggest that a relation maybe exists. if quality and size of the data sample is large enough the accuracy of such a guess will be high enough for practical purpose.
    – miracle173
    Sep 23, 2012 at 17:03
  • Maybe. But let's take a real example which is pretty hellish relational-design wise: addresses. How should mailing addresses be normalized? How many tables should be suggested? By my understanding of 3NF, you need tables for countries, states/provinces, and cities. I don't know whether postal code/city breakdowns make sense but they probably do. But how do you analyze existing data and determine that (city, state) -> country? How do you determine if/if not postal codes globally relate to these constructs?
    – Chris Travers
    Sep 24, 2012 at 1:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.