Systems Analysis & Design Methods

1. Systems Analysis & Design Methods III Classic normalization rules for relational databases

2. Contents • Introduction • First Normal Form: Columns should not repeat • Second Normal Form: Non-key columns should depend on the whole primary key, not just on a part. • Third Normal Form: Non-key should not depend on other non-key columns. Note: Excellent reading: http://www.cs.uta.fi/~av/tiko/monisteet/db_normal.pdf Systems Analysis III Normalization Rules

3. Introduction • By using normalization rules, we try to • avoid inconsistencies • avoid waste of space. • enhance flexible use of data (easy SQL queries). • minimize the effect of application changes on the database structure. • The normalization rules are to be read accumulatively: E.g. Your database is in 3NF if it is compliant with the rules given by 1NF, 2NF and 3NF Systems Analysis III Normalization Rules

4. Some vocabulary • candidate key = a combination of columns which uniquely determine a table row • the primary key = a chosen minimal combination of columns which uniquely determine a table row • alternate key = candidate key not chosen as primary key • foreign key = primary key of another table. Is used to reference a specific row in this other table • With non-key I mean a key that is not candidate, primary or alternative key Systems Analysis III Normalization Rules

5. First Normal Form Columns should not repeat • This means that you are not allowed to try and store an array or a collection of the same kind of information, in one table row. This attempt can take two forms which result in two subrules: • You cannot have several columns, having similar information: • 3 columns child1, child2, child3 (see also next slide) • Nor can you put multiple values in one column: • 1 column children which contains a string of concatenated first names like ‘David-Ben-Joe’ Systems Analysis III Normalization Rules

6. First Normal FormColumns should not repeat • Violation Example: Systems Analysis III Normalization Rules

7. First Normal Form Columns should not repeat • Problems when violating 1NF: • Every time more repeated fields are needed, the structure of the table changes, and rewriting of existing code/queries is needed. • Explicit naming of different columns necessary when quering of programming. • Rows who do not need many contacts waste space. Systems Analysis III Normalization Rules

8. First Normal Form Columns should not repeat • Solution: Systems Analysis III Normalization Rules

9. Second Normal FormNon-key columns should depend on the whole primary key, not just on a part. A field y depends on a field x, if there is only one possible value for y, given a value for x. E.g. in the next slide: The applicant table on top of the slide has a primary key Appl_id. The applicant/reference table below tells you wich applicants have which references. The primary key is Appl_id + Refphone Within de applicant/reference table we can say this: When you know the value of the Appl_id column, you know which Appl_name goes with it. Clearly, the Appl_Name field is completely dependent on Appl_id. That’s is why we say the database violates 2NF. Systems Analysis III Normalization Rules

10. Second Normal FormNon-key columns should depend on the whole primary key, not just on a part. • Violation Example Systems Analysis III Normalization Rules

11. Second Normal FormNon-key columns should depend on the whole primary key, not just on a part. • Problems when violating 2NF: • The part of the primary key, on which the column is dependent, is mostly a foreign key. This means that the dependent column contains information that is probably already available in the record (in another table) to which this foreign key points. So the dependend column contains copied information (from another table) that needs to be kept consistent with the original. (Danger for anomalies.) Systems Analysis III Normalization Rules

12. Second Normal FormNon-key columns should depend on the whole primary key, not just on a part. • The part of the primary key, on which the column is dependent, probably contains the same values for different rows. This means that the dependent column contains the same values for these same rows. So the dependend column contains copied information (from the same table) that needs to be kept consistent with the original. • Copying (see above) information is a waste of space. Systems Analysis III Normalization Rules

13. Second Normal FormNon-key columns should depend on the whole primary key, not just on a part. • Solution: Systems Analysis III Normalization Rules

14. Third Normal Form Non-key columns should not depend on other non-key columns. • Violation Example: Systems Analysis III Normalization Rules

15. Third Normal Form Non-key columns should not depend on other non-key columns. • Problems when violating 3NF: • The dependent column contains information that is also available in other rows from the same table. So the dependend column contains copied information that needs to be kept consistent with the original. (Danger for update anomalies.) • Copied information wastes space. Systems Analysis III Normalization Rules

16. Third Normal Form Non-key columns should not depend on other non-key columns. • Solution: Systems Analysis III Normalization Rules

17. Third Normal FormRemarks • Columns may be dependent on alternative keys. So they may be dependent on a field or combination of fields that uniquely define a record, but that was not chosen as the primary key. E.g. On the next slide Appl_Name is dependent on the alternate key. This is okay since an alternate key uniquely defines the whole record. (In other words, we could just as well have chosen this alternate key as the primary key) Systems Analysis III Normalization Rules

18. Third Normal FormRemarks alterate key primary key Systems Analysis III Normalization Rules