4

I have been reading 'Database Systems: A practical approach to design, implementation and management' by Connolly and Begg in order to understand database normalization (chapter 14). I now better understand the three database normalization forms:

  • First normal form (1NF)
  • Second normal form (2NF)
  • Third normal form (3NF)

I also understand the three update anomalies:

  • Insertion anomaly
  • Deletion anomaly
  • Update / Modification anomaly

What I am struggling to understand now is how to link the two together. For example, which of the different forms help(s) to fix the insertion anomaly? Similarly for the other anomalies. I would ideally like to know the mapping relationship between the two groups and why those forms fix the certain anomaly.

This is my first question on here and I searched on Google and this site to try and find an answer first, but to no avail.

Thank you.

  • 1
    give us sample tables that are not in 2nd normal form or 3rd normal form so that we can show the anomalies. – miracle173 Jan 4 '18 at 15:51
  • You might find this explanation by Mark Rettig helpful - it lightly covers the anomalies with good examples. – LowlyDBA Jan 4 '18 at 17:48
  • First Normal Form, originally called Normal Form, does not address either update anomalies or harmful redundancy. It addresses keyed access to all data. Keyed access to data, coupled with appropriate index design, a good query optimizer, and well formed queries are the way to get decent performance out of a relational database. But this has little to do with update anomalies. – Walter Mitty Jan 7 '18 at 16:11
5

1NF is basically just "don't keep too much data in a single column", so I think that 2NF and 3NF are the primary fix for all 3 database anomalies, since both 2NF and 3NF involve breaking out items into their own tables:

  1. Insertion anomaly: If you have one big enrollment table that includes both "class" and "student" data (neither of which exists elsewhere), then you can't enter a new (empty) course without at least one corresponding student (because the table is a record of your enrollments). So, apply 2NF and create separate tables for classes, students, and make your original enrollment table link to both by ClassID and StudentID. Now you can enter new classes with no students, and new students with no classes.

  2. Deletion anomaly: Same as above, if each row of your original enrollment table contains the full details of the student and the full details of the class they are enrolled in, then removing the last enrolled student for a class removes the last bit of information about that class. The solution is the same, apply 2NF to make separate tables, so that students can be enrolled or unenrolled without losing class information.

  3. Update anomaly: Same as above, using the single-table method, updating information (say, the room number) for a class with multiple students enrolled might lead to a situation where some rows have been new information and other rows have the old. Applying 2NF as above is again the solution, so that class data is changed in only one place (the classes table).

Note that 1NF still plays some role in your process, as it means you can't try to solve the problem by cramming an entire list of enrolled students into a single field, or add student1, student2, student3 fields or something like that.

We could come up with similar examples where the factor in play is 3NF instead of 2NF: If each "student" has a faculty advisor (and some advisors are assigned to multiple students), that might not be part of the key for our student table, but it is "dependent attribute", and could lead to some of the same problems as above. So faculty advisor could be broken out into its own table.

A few resources I found helpful:

  • Thank you for your answer. So assuming 1NF already, am I right in saying both 2NF and 3NF can fix all three anomalies, but it just depends if the table has attributes with partial dependencies (2NF) or transitive dependencies (3NF)? – the_butler Jan 6 '18 at 9:01
  • @the_butler Yes, that is the way I would describe it. – BradC Jan 6 '18 at 23:04
  • @the_butler Fagin's original definitions of insert anomaly don't involve multi-table consistency or nulls or delete anomalies dropping all info about something. ("Normal forms and relational database operators" 1979) That's a ubiquitous misinterpretation/generalization of the terms and has nothing to do with normalization. We might notice that a design is thus wrong (not just bad) while normalizing. "Normalize" is also frequently misinterpreted/generalized to include eliminating such "anomalies". For either sense of either term, "normalization" gets rid of some "anomalies"), not all. – philipxy Jan 9 '18 at 1:44
2

I'll come up with the example I've learnt. 1Nf is all about separating the repeating groups into different tables and all it's attributes should be atomic.

Let's assume we have a table that contains the following attributes. Purchase_order_No, Purchase_date, Emp_code, Supplier_Name,Supplier_No, Part_No Part_description, Part_Quantity

  • 1NF: Here within a single purchase order we may have several parts ordered, so the related same data will be repeated. so we can separate the part_no, part_description, part_quantity and make part_no,purchase_order_no as primary key. but we will have anomalies here..

    1. insert anomaly: as there are 2 tables now. we can't insert a part that is not purchased.
    2. delete anomaly: if there was a single purchase for a particular part and if that order was deleted then we will not have that part in our table at all.
    3. update anomaly: to change the description of a certain part we need to update it in all places where it has been purchased.
  • 2NF: for a table to be in second form all its attributes should be totally dependent on it's primary key. for example to know the part quantity we need to know part no and purchase order. but for part description part no is just sufficient so we separate them into tables. supplier name does not d

    1. insert anomaly: if we had to insert a supplier who has no order then it is not possible.
    2. delete anomaly: if we delete the last single order of a supplier the data of the supplier will also be lost.
    3. update anomaly: to update a supplier need to update in each and every purchase order of the supplier.
  • 3Nf: for a table to be in 3NF there should be no attribute that is also dependent in another non-key attribute. here we have supplier name which is also dependent in sup-no so we put them into a different table. where sup_no is the primary key.

now the tables cannot be decomposed further but the anomalies can continue to exist. we cannot form perfectly normalized data tables. hope this clears you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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