Normalization

Normalization

• 1.
  Normalization Normalization is adatabase design technique which organizes tables in a manner that reduces redundancy and dependency of data.
• 2.
  Reasons for Normalization InsertAnomaly: In our example we cannot record a new sales office until we also know the sales person. Why? Because in order to create the record, we need provide a primary key. In our case this is the EmployeeID. CSE-311(C) 2
• 3.
  Reasons for Normalization UpdateAnomaly: The same information is recorded in multiple rows. For instance if the office number changes, then there are multiple updates that need to be made. If these updates are not successfully completed across all rows, then an inconsistency occurs. CSE-311(C) 3
• 4.
  Reasons for Normalization DeletionAnomaly: Deletion of a row can cause more than one set of facts to be removed. For instance, if John Hunt retires, then deleting that row cause use to lose information about the New York office. CSE-311(C) 4
• 5.
  Normalization Rule Database NormalForms:  1NF (First Normal Form) Rules oEach table cell should contain single value. oEach record needs to be unique oNo rows of data should have repeating group of column values  2NF (Second Normal Form) Rules oRule 1- Be in 1NF oRule 2- Single Column Primary Key  3NF (Third Normal Form) Rules oRule 1- Be in 2NF oRule 2- Has no transitive functional dependencies CSE-311(C) 5
• 6.
  First Normal Form(1NF) Repeatedgroups CSE-311(C) 6
• 7.
  Second Normal Form(2NF) CSE-311(C)7
• 8.
  Third Normal Form(3NF) Let’s add three more columns - STREET, CITY and ZIP to STUDENT table to explain 3NF  simplest to think of transitive dependence to mean a column’s value relies upon another column through a second intermediate column STUDENT ZIP CITY CSE-311(C) 8
• 9.
  Third Normal Form(3NF)  CSE-311(C)9