What are database normal forms and can you give examples? [closed]

by

1NF is the most basic of normal forms – each cell in a table must contain only one piece of information, and there can be no duplicate rows.

2NF and 3NF are all about being dependent on the primary key. Recall that a primary key can be made up of multiple columns. As Chris said in his response:

The data depends on the key [1NF], the whole key [2NF] and nothing but the key [3NF] (so help me Codd).

2NF

Say you have a table containing courses that are taken in a certain semester, and you have the following data:

|-----Primary Key----|               uh oh |
                                           V
CourseID | SemesterID | #Places  | Course Name  |
------------------------------------------------|
IT101    |   2009-1   | 100      | Programming  |
IT101    |   2009-2   | 100      | Programming  |
IT102    |   2009-1   | 200      | Databases    |
IT102    |   2010-1   | 150      | Databases    |
IT103    |   2009-2   | 120      | Web Design   |

This is not in 2NF, because the fourth column does not rely upon the entire key – but only a part of it. The course name is dependent on the Course’s ID, but has nothing to do with which semester it’s taken in. Thus, as you can see, we have duplicate information – several rows telling us that IT101 is programming, and IT102 is Databases. So we fix that by moving the course name into another table, where CourseID is the ENTIRE key.

Primary Key |

CourseID    |  Course Name |
---------------------------|
IT101       | Programming  |
IT102       | Databases    |
IT103       | Web Design   |

No redundancy!

3NF

Okay, so let’s say we also add the name of the teacher of the course, and some details about them, into the RDBMS:

|-----Primary Key----|                           uh oh |
                                                       V
Course  |  Semester  |  #Places   |  TeacherID  | TeacherName  |
---------------------------------------------------------------|
IT101   |   2009-1   |  100       |  332        |  Mr Jones    |
IT101   |   2009-2   |  100       |  332        |  Mr Jones    |
IT102   |   2009-1   |  200       |  495        |  Mr Bentley  |
IT102   |   2010-1   |  150       |  332        |  Mr Jones    |
IT103   |   2009-2   |  120       |  242        |  Mrs Smith   |

Now hopefully it should be obvious that TeacherName is dependent on TeacherID – so this is not in 3NF. To fix this, we do much the same as we did in 2NF – take the TeacherName field out of this table, and put it in its own, which has TeacherID as the key.

 Primary Key |

 TeacherID   | TeacherName  |
 ---------------------------|
 332         |  Mr Jones    |
 495         |  Mr Bentley  |
 242         |  Mrs Smith   |

No redundancy!!

One important thing to remember is that if something is not in 1NF, it is not in 2NF or 3NF either. So each additional Normal Form requires everything that the lower normal forms had, plus some extra conditions, which must all be fulfilled.

More Related Contents:

  1. Is storing a delimited list in a database column really that bad?
  2. Normalization: What does “repeating groups” mean?
  3. In terms of databases, is “Normalize for correctness, denormalize for performance” a right mantra?
  4. have address columns in each table or an address table that is referenced by the other tables?
  5. Minimum no of tables that exists after decomposing relation R into 1NF?
  6. What’s the better database design: more tables or more columns?
  7. Should each and every table have a primary key?
  8. Database, Table and Column Naming Conventions? [closed]
  9. Can I have multiple primary keys in a single table?
  10. How are super- and subtype relationships in ER diagrams represented as tables?
  11. Difference between scaling horizontally and vertically for databases [closed]
  12. NoSql vs Relational database
  13. Best practices for storing postal addresses in a database (RDBMS)?
  14. Microservices with shared database? using multiple ORM’s? [closed]
  15. Why use a 1-to-1 relationship in database design?
  16. What is the optimal length for an email address in a database?
  17. What are the advantages of using a single database for EACH client?
  18. Database optimization orders
  19. In what way does denormalization improve database performance?
  20. Still Confused About Identifying vs. Non-Identifying Relationships
  21. Normal forms – 2nd vs 3rd – is the difference just composite keys? non trivial dependency?
  22. Alternatives to Entity-Attribute-Value (EAV)?
  23. Is it better to use multiple databases with one schema each, or one database with multiple schemas? [closed]
  24. Is it a good idea to use an integer column for storing US ZIP codes in a database?
  25. Limit an sqlite Table’s Maximum Number of Rows
  26. When we need to use 1-to-1 relationship in database design?
  27. Need help in developing DB logic
  28. What exactly does database normalization do?
  29. Databse architecture (single db vs client specific db) for Building Enterprise Web (RIA) application on cloud
  30. Database – Data Versioning [closed]

Leave a Comment