I am taking college classes in relational databases for half a year now. As an assignment get to normalize data with 1NF, 2NF and 3NF.

I do not find any difficulties making my schema compatible with 1NF. After that I have not managed to find any examples where to apply 2NF and 3NF as well. It feels like I think a bit ahead when I do 1NF.

When I deliver the assignment, all I have done is 1NF to my understanding. And it still is being approved as valid design.

Is this a normal thing to have? At the moment I do not see the use of 2NF and 3NF when all the redundancy is taken away in 1NF already. Your thoughts?

  • This is covered nicely here: stackoverflow.com/questions/723998/… – JNK Jan 27 '15 at 22:52
  • Essentially, you still have duplicate data about attributes in 1nf. – JNK Jan 27 '15 at 22:53
  • Great link you posted. I do understand it more clear now. I think for me it is simply a matter of not come across a good example to apply 2NF and 3NF to. – Lee Jan 27 '15 at 23:12
  • Why don't you tell us the problem and give us your solution... – Neil McGuigan Jan 27 '15 at 23:38

I think that you will understand the needing of normalization thinking to the anomalies that happens when you try to delete, insert or update rows in your tables.

Think to a 1NF tables with students, exam and the teacher of the professor of the exam.

An anomaly that happens when you delete a record is that you will lose every information associated to It. For example if only a student took an exam and you delete the tuple Student-Exam-Professor you will lose every information about that exam and the professor (the information about the professor that teaches that exam is lost because it existed in your database only on that tuple). 1NF allow this anomaly.

An anomaly that happens when you try to insert a new tuple is that you have to know all data associated to that tuple. If you want to insert a new tuple, you have to know the professor that teaches it (for example if you have a Not null constraint). For example, a Not null constraint on Student can't allow you to insert a new exam with its professor if you have no students to associate it. 1NF allow this anomaly.

An anomaly that happens when you try to update a value of a tuple in your a table is that you have to update all copies of that value. For example, if you update some data about the professor, the exam or the student changes you have to update every tuple that have that values or you'll have inconsistency in your DB. 1NF allow this anomaly.

Don't forget the redundancy of a 1NF table that repeats every time the professor associated to that exam even if you need it only one time.

If you change it to a 2NF table, you will have two tables, the students-exams one and the exams-professors one, and you will have no anomalies.

Another NF is the Boyce and Codd Normal Form, BCNF. This one sometimes doesn't preserve some FD.

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