Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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 –  FreshPhilOfSO Sep 22 '12 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 '12 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 '12 at 1:42
add comment

4 Answers

up vote 12 down vote accepted

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 precisely the tables represent the facts and now well the table's design can prevent misrepresentation or inconsistent representation of the facts.

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 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. 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.

share|improve this answer
    
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 '12 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 '12 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 '12 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 '12 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? –  DrewV Apr 9 at 22:06
add comment

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.

share|improve this answer
    
Nice. I was indeed looking for both ways to analyze a schema (ER diagram and sample data analysis). Do you know of any other recent software than Visio ? I don't have (yet) a licence for it. –  ack__ Sep 23 '12 at 12:13
1  
But your ER diagrams are essentially object-model diagrams. That's not quite the same thing as taking an existing database and determining if it is normalized from it. –  Chris Travers Sep 23 '12 at 13:35
2  
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 '12 at 21:15
    
@Chris: yes, I was referring to ER diagrams for semantic analysis only. –  ack__ Sep 23 '12 at 21:32
    
@ypercube: Thanks for the tip, i'll take a look at Access. –  ack__ Sep 23 '12 at 21:33
show 1 more comment

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.

share|improve this answer
    
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 '12 at 12:20
    
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 '12 at 13:42
1  
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 '12 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 '12 at 1:04
add comment

I doubt very much that you are still in need of an answer, but in case you or someone else might benefit, you may wish to give this tool a try: http://uisacad5.uis.edu/cgi-bin/mcrem2/database_design_tool.cgi

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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