Normalization

1. Normalization CSC 3800 Fall 2008

2. Database Normalization • Database normalization is the process of removing redundant data from your tables to improve storage efficiency, data integrity, and scalability. • In the relational model, methods exist for quantifying how efficient a database is. These classifications are called normal forms (or NF), and there are algorithms for converting a given database between them. • Normalization generally involves splitting existing tables into multiple ones, which must be re-joined or linked each time a query is issued.

3. History • Edgar F. Codd first proposed the process of normalization and what came to be known as the 1st normal form in his paper A Relational Model of Data for Large Shared Data Banks Codd stated: “There is, in fact, a very simple elimination procedure which we shall call normalization. Through decomposition nonsimple domains are replaced by ‘domains whose elements are atomic (nondecomposable) values.’”

4. Normal Form • Edgar F. Codd originally established three normal forms: 1NF, 2NF and 3NF. There are now others that are generally accepted, but 3NF is widely considered to be sufficient for most applications. Most tables when reaching 3NF are also in BCNF (Boyce-Codd Normal Form).

5. Why Normalize? • Flexibility • Structure supports many ways to look at the data • Data Integrity • “Modification Anomalies” • Deletion • Insertion • Update • Efficiency • Eliminate redundant data and save space

6. Normalization Defined • “In relational database design, the process of organizing data to minimize duplication. • Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. • The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.” - Webopedia, http://webopedia.internet.com/TERM/n/normalization.html

7. The Normal Forms • A series of logical steps to take to normalize data tables • First Normal Form • Second • Third • Boyce Codd • There’s more, but beyond scope of this class

8. Un-normalized Table or

9. First Normal Form • Remove horizontal redundancies • No two columns hold the same information • No single column holds more than a single item • Each row must be unique • Use a primary key • Benefits • Easier to query/sort the data • More scalable • Each row can be identified for updating

10. First Normal Form • All columns (fields) must be atomic • Means : no repeating items in columns Solution: make a separate table for each set of attributes with a primary key (parser, append query) Customers CustomerID Name Orders OrderID Item CustomerID OrderDate

11. First Normal Form Tables Customers Orders

12. Second Normal Form (2NF) • In 1NF and every non-key column is fully dependent on the (entire) primary key • Means : Do(es) the key field(s) imply the rest of the fields? Do we need to know both OrderID and Item to know the Customer and Date? Clue: repeating fields Orders

13. Second Normal Form • Table must be in First Normal Form • Remove vertical redundancy • The same value should not repeat across rows • Composite keys • All columns in a row must refer to BOTH parts of the key • Benefits • Increased storage efficiency • Less data repetition

14. Second Normal Form (2NF) • In 1NF and every non-key column is fully dependent on the (entire) primary key • Means : Do(es) the key field(s) imply the rest of the fields? Do we need to know both OrderID and Item to know the Customer and Date? Clue: repeating fields Solution: Remove to a separate table (Make Table) Orders OrderID CustomerID OrderDate OrderDetails OrderID Item

15. Customers Orders OrderDetails Second Normal Form Tables

16. Third Normal Form (3NF) • In 2NF and every non-key column is mutually independent • means : Calculations

17. Third Normal Form • Table must be in Second Normal Form • If your table is 2NF, there is a good chance it is 3NF • All columns must relate directly to the primary key • Benefits • No extraneous data

18. Third Normal Form (3NF) • In 2NF and every non-key column is mutually independent • means : Calculations • Solution: Put calculations in queries and forms OrderDetails OrderID Item Quantity Price Put expression in text control or in query: =Quantity * Price

19. Third Normal Form (3NF) OrderDetails Put expression in text control or in query: =Quantity * Price SELECT OrderID, Item, Quantity, Price, Price*Quantity FROM OrderDetails

20. Boyce-Codd Form (3NF) - Examples Kumar Madurai: http://www.mgt.buffalo.edu/courses/mgs/404/mfc/lecture4.ppt • A more restricted version of 3NF (known as Boyce-Codd Normal Form) requires that the determinant of every functional dependency in a relation be a key - for every FD: X => Y, X is a key • Consider the following relation:STU-MAJ-ADV (Student-Id, Major, Advisor)Advisor => Major, but Advisor is not a key • Boyce-Codd Normal Form for above:STU-ADV (Student-Id, Advisor)ADV-MAJ (Advisor, Major) 2/16/98 2/16/98 10 10 MGS 404

21. Primary Key • Unique Identifier for every row in the table • Integers vice Text to save memory, increase speed • Can be “composite” • Surrogate is best bet! • Meaningless, numeric column acting as primary key in lieu of something like SSN or phone number - (both can be reissued!)

22. Relationships • One to many to enforce “Referential Integrity” Two “foreign” keys make a composite primary key and “relate” many to many tables A look up table - it doesn’t reference any others

23. Table Prefixes Aid Development • First, we’ll get replace text PK with number • The Items table is a “look up” with tlkp prefix • tlkp “lookup” table (no “foreign keys”) • OrderDetails is renamed “trelOrderItem” a “relational” table • trel “relational” (or junction or linking) • two foreign keys make a primary OrderDetails OrderID Item tblOrders OrderID CustomerID OrderDate trelOrderItem OrderID ItemID tlkpItems ItemID ItemName

24. Referential Integrity • Every piece of “foreign” key data has a primary key on the one site of the relationship • No “orphan” records. Every child has a parent • Can’t delete records from primary table if in related table • Benefits - Data Integrity and Propagation • If update fields in main table, reflected in all queries • Can’t add a record in related table without adding it to main • Cascade Delete: If delete record from primary table, all children deleted - use with care! Better idea to “archive” • Cascade Update: If change the primary key field, will change foreignkey

25. When Not to Normalize • Want to keep tables simple so user can make their own queries • Avoid processing multiple tables • Archiving Records • If no need to perform complex queries or “resurrect” • Flatten and store in one or more tables • Testing shows Normalization has poorer performance • “Sounds Like” field example • Can also try temp tables produced from Make Table queries

26. Real World - School Data Student Student Previous Current Last First Parent 1 Parent 2 Teacher Teacher Smith Renee Ann Jones Theodore Smith Hamil Burke Mills Lucy Barbara Mills Steve Mills Hamil Burke Jones Brendan Jennifer Jones Stephen Jones Hamil Burke …. Street Address City State Postal Code Home Phone 5551 Private Hill Annandale Virginia 22003- (703) 323-0893 4902 Acme Ct Annandale Virginia 22003- (703) 764-5829 5304 Gains Street Fairfax Virginia 22032- (703) 978-1083 …. First Year Last Year Age Program Enrolled Attended Birthday inSept Map Coord Notes PF / 0 0 6/25/93 5 22 A-3 PF 96/97 0 8/14/93 5 21 F-3 PH 96/97 0 6/13/94 4 21 A-4

27. One Possible Design

28. Examples

29. 1. Eliminate Repeating Groups • In the original member list, each member name is followed by any databases that the member has experience with. Some might know many, and others might not know any. To answer the question, "Who knows DB2?" we need to perform an awkward scan of the list looking for references to DB2. This is inefficient and an extremely untidy way to store information. • Moving the known databases into a seperate table helps a lot. Separating the repeating groups of databases from the member information results in first normal form. The MemberID in the database table matches the primary key in the member table, providing a foreign key for relating the two tables with a join operation. Now we can answer the question by looking in the database table for "DB2" and getting the list of members. 1NF Tables Original Table

30. 2. Eliminate Redundant Data • In the Database Table, the primary key is made up of the MemberID and the DatabaseID. This makes sense for other attributes like "Where Learned" and "Skill Level" attributes, since they will be different for every member/database combination. But the database name depends only on the DatabaseID. The same database name will appear redundantly every time its associated ID appears in the Database Table. • Suppose you want to reclassify a database - give it a different DatabaseID. The change has to be made for every member that lists that database! If you miss some, you'll have several members with the same database under different IDs. This is an update anomaly. • Or suppose the last member listing a particular database leaves the group. His records will be removed from the system, and the database will not be stored anywhere! This is a delete anomaly. To avoid these problems, we need second normal form. • To achieve this, separate the attributes depending on both parts of the key from those depending only on the DatabaseID. This results in two tables: "Database" which gives the name for each DatabaseID, and "MemberDatabase" which lists the databases for each member. • Now we can reclassify a database in a single operation: look up the DatabaseID in the "Database" table and change its name. The result will instantly be available throughout the application.

31. 3. Eliminate Columns Not Dependent On Key • The Member table satisfies first normal form - it contains no repeating groups. It satisfies second normal form - since it doesn't have a multivalued key. But the key is MemberID, and the company name and location describe only a company, not a member. To achieve third normal form, they must be moved into a separate table. Since they describe a company, CompanyCode becomes the key of the new "Company" table. • The motivation for this is the same for second normal form: we want to avoid update and delete anomalies. For example, suppose no members from the IBM were currently stored in the database. With the previous design, there would be no record of its existence, even though 20 past members were from IBM!

32. BCNF. Boyce-Codd Normal Form • Boyce-Codd Normal Form states mathematically that:A relation R is said to be in BCNF if whenever X -> A holds in R, and A is not in X, then X is a candidate key for R.BCNF covers very specific situations where 3NF misses inter-dependencies between non-key (but candidate key) attributes. Typically, any relation that is in 3NF is also in BCNF. However, a 3NF relation won't be in BCNF if (a) there are multiple candidate keys, (b) the keys are composed of multiple attributes, and (c) there are common attributes between the keys. • Basically, a humorous way to remember BCNF is that all functional dependencies are:"The key, the whole key, and nothing but the key, so help me Codd."

33. Example 2

34. Example 2 The First Normal Form For a table to be in first normal form, data must be broken up into the smallest units possible. For example, the following table is not in first normal form.

35. Example 2 To conform to first normal form, this table would require additional fields. The name field should be divided into first and last name and the address should be divided by street, city state, and zip like this.

36. Example 2 In addition to breaking data up into the smallest meaningful values, tables in first normal form should not contain repetitions groups of fields such as in the following table.

37. Example 2 The problem here is that each representative can have multiple clients not all will have three. Some may have less as is the case in the second record, tying up storage space in your database that is not being used, and some may have more, in which case there are not enough fields. The solution to this is to add a record for each new piece of information. Notice the splitting of the first and last name fields again.

38. Example 2 This table is now in first normal form. Note that by avoiding repeating groups of fields, we have created a new problem in that there are identical values in the primary key field, violating the rules of the primary key. In order to remedy this, we need to have some other way of identifying each record. This can be done with the creation of a new key called client ID. This new field can now be used in conjunction with the Rep ID field to create a multiple field primary key. This will prevent confusion if ever more than one Representative were to serve a single client.

39. Example 2 Second Normal Form The second normal form applies only to tables with multiple field primary keys.  Take the following table for example. This table is already in first normal form.  It has a primary key consisting of Rep ID and Client ID since neither alone can be considered a unique value.  

40. Example 2 • The second normal form states that each field in a multiple field primary key table must be directly related to the entire primary key. Or in other words, each non-key field should be a fact about all the fields in the primary key. Only fields that are absolutely necessary should show up in our table, all other fields should reside in different tables.  In order to find out which fields are necessary we should ask a few questions of our database.  In our preceding example, I should ask the question "What information is this table meant to store?" Currently, the answer is not obvious. It may be meant to store information about individual clients,  or it could be holding data for employees time cards.  As a further example, if my database is going to contain records of employees I may want a table of demographics and a table for payroll.  The demographics will have all the employees personal information and will assign them an ID number.  I should not have to enter the data twice, the payroll table on the other hand should refer to each employee only by their ID number. I can then link the two tables by a relationship and will then have access to all the necessary data.  

41. Example 2 In the table of the preceding example we are devoting three field to the identification of the employee and two to the identification of the client.  I could identify them with only one field each -- the primary key.  I can then take out the extraneous fields and put them in their own table.  For example,  my database would then look like the following. The above table contains time card information.

42. Example 2 The above table contains Employee Information.

43. Example 2 The above table contains Client Information These tables are now in normal form.  By splitting off the unnecessary information and putting it in its own tables, we have eliminated redundancy and put our first table in second normal form.  These tables are now ready to be linked through relationship to each other. 

44. Example 2 Third Normal Form • Third normal form is the same as second normal form except that it only refers to tables that have a single field as their primary key.  In other words, each non-key field in the table should be a fact about the primary key. Either of the preceding two tables act as an example of third normal form since all the fields in each table are necessary to describe the primary key.  • Once all the tables in a database have been taken through the third normal form, we can begin to set up relationships. 

45. Example 3

46. Repeating Groups And Normalization To First Normal Form (1nf) SALES-INFORMATION Invoice# Date Customer# Salesperson Region Item# Description Price Quantity 100110021003 7/1/927/1/927/1/92 456329897 JohnMaryAl WestEastWest 121348540 WidgetGearBolt $2.25$3.70$0.40 45105 INVOICE-ITEMS (1NF) Invoice# Item# Description Price Quantity INVOICES (2NF) Invoice# Date Customer# Salesperson Region

47. What Is The Problem With Description/Price? • Insert anomalies • Delete anomalies • Update anomalies

48. Decomposition Of A First-normal-form (1nf) Table INVOICE-ITEMS (1NF) Invoice# Item# Description Price Quantity ITEMS (2NF) INVOICE-ITEMS-QTY (2NF) Item# Description Price Invoice# Item# Quantity You can only have a 2nd Normal Form problem if there is a composite primary Key

49. Database Normalization • Functional dependency is key in understanding the process of normalization. Functional dependency means that if there is only one possible value of Y for every value of X, then Y is functionally dependent on X.

50. Database Normalization • Think of an invoice table. Two fields would be invoice # and date. Which field is functionally dependent on the other? INVOICE # DATE Date is functionally dependent on invoice number.