Chapter 6 database normalisation

Chapter 6 database normalisation

• 1.
  Introduction to MySQL DatabaseNormalization Day 2
• 2.
  Relevance of normalization •We could have store all the data in a single table like • But there are several issues arise here Emp_id Emp_name Emp_age Emp_email Fk_int_designation Vchr_Desig_roles 1000 Deepak 45 dk@gmail.co m Are Manager Handles all the district level operations 1001 Aneesh 23 an@gmail.com Sales person Will increase sales and ensure customer satisfaction 1002 Naveen 25 nn@gmail.com Sales person Will increase sales and ensure customer satisfaction 1003 Jacob 25 dpk@gmail.com Administrator Handles all the district level operations
• 3.
  Relevance of normalization •We could have store all the data in a single table like Emp_id Emp_name Emp_age Emp_email Fk_int_designation Vchr_Desig_roles 1000 Deepak 45 dk@gmail.com Are Manager Handles all the district level operations 1001 Aneesh 23 an@gmail.com Sales person Will increase sales and ensure customer satisfaction 1002 Naveen 25 nn@gmail.com Sales person Will increase sales and ensure customer satisfaction 1003 Jacob 25 dpk@gmail.com Administrator Handles all the district level operations Here designation is repeated for all the rows and where ever it repeats the designation role also has to repeat. This redundancy takes a lot of space to store and is inefficient
• 4.
  Relevance of normalization •We could have store all the data in a single table like Emp_id Emp_name Emp_age Emp_email Fk_int_designation Vchr_Desig_roles 1000 Deepak 45 dk@gmail.com Are Manager Handles all the district level operations 1001 Aneesh 23 an@gmail.com Sales person Will increase sales and ensure customer satisfaction 1002 Naveen 25 nn@gmail.com Sales person Will increase sales and ensure customer satisfaction 1003 Jacob 25 dpk@gmail.com Administrator Handles all the district level operations Suppose if i want to modify the roles of any designation, i will have to do the same for all records in the table. Its time consuming and not an effective way to follow
• 5.
  Normalization • Normalization isthe process of moving data into related tables • Will save data dependency and redundancy • Save typing of repetitive data • Reduce disk space
• 6.
  Types of Normalization •There are several levels of normalization – 1st Normalization – 2nd Normalization – 3rd Normalization
• 7.
  First Normal Form
• 8.
  First Normal Form –Each field contains the smallest meaningful value – The table does not contain repeating groups of fields or repeating data within the same field To attain 1st normalized form what you have to do is •Create a separate field/table for each set of related data. •Identify each set of related data with a primary key
• 9.
  Table violating 1stNormal Form Company (pk) Branches Baabte Cochin, Calicut baabtra Calicut,Trivandrum It really is a bad setup that we are storing multiple values in a single column. For a table to be in 1st normalised form it should only have atomic values
• 10.
  Table violating 1stNormal Form Company (Pk) Branche1 Branch2 Baabte Cochin Calicut baabtra Calicut Trivandrum It still in a bad set up , as we should not have multiple columns describing the same property of an entity.
• 11.
  Table conforming to1st Normal Form Company (pk) Branch (pk) Baabte Cochin Baabte Calicut baabtra Calicut baabtra Trivandrum We have resulted in a 1st normalised table with primary key consists of two columns
• 12.
  Second Normalisation
• 13.
  Second Normalisation – usuallyused in tables with a multiple-field primary key (composite key) – each non-key field relates to the entire primary key – any field that does not relate to the primary key is placed in a separate table • To attain 1st normalized form what you have to do is •eliminate redundant data in a table •Create separate tables for sets of values that apply to multiple records
• 14.
  Table violating 2ndNormal Form Employee (pk) Skill (pk) WorkLocation John IOS programmer Calicut Mathew Java programmer Cochin Thomas Android programmer Trivandrum Alex PHP programmer Banglore Ram Java programmer Mumbai John PHP programmer Delhi Soozan doNet programmer Calicut Primary key= { Employee,Skill }
• 15.
  Table violating 2ndNormal Form Employee (pk) Skill (pk) WorkLocation John IOS programmer Calicut Mathew Java programmer Cochin Thomas Android programmer Trivandrum Alex PHP programmer Banglore Ram Java programmer Mumbai John PHP programmer Delhi Soozan doNet programmer Calicut Here non key attribute do not fully depends on compete primary key. Ie WorkLocation depends only on Employee and doesn’t depend on Skill (partial dependency) Primary key= { Employee,Skill }
• 16.
  Table conforming to2nd Normal Form Employee (pk) Skill (pk) John IOS programmer Mathew Java programmer Thomas Android programmer Alex PHP programmer Ram Java programmer John PHP programmer Soozan doNet programmer Employee (pk) WorkLocation John Calicut Mathew Cochin Thomas Trivandrum Alex Banglore Ram Mumbai John Delhi Soozan Calicut
• 17.
  Third Normalisation
• 18.
  Third Normal Form •Usually used in tables with a single field primary key --records do not depend on anything other than a table's primary key – each non-key field is a fact about the key – Values in a record that are not part of that record's key do not belong in the table. In general, any time the contents of a group of fields may apply to more than a single record in the table, consider placing those fields in a separate table.
• 19.
  EMPNO (Primary Key) FIRSTNAME LASTNAMEWORKDEPTNO DEPTNAME 000290 John Parker OP11 Operations 000320 Ramlal Mehta SE21 Software Support 000310 Maude Setright OP11 Operations Table violating 3rd Normal Form
• 20.
  EMPNO (Primary Key) FIRSTNAME LASTNAMEWORKDEPTNO DEPTNAME 000290 John Parker OP11 Operations 000320 Ramlal Mehta SE21 Software Support 000310 Maude Setright OP11 Operations Table violating 3rd Normal Form Here all the fields are not completely depend on Primary key. Ie Dept Name column only depends on DeptNo.And deptno depends on the EMPNO. Ie its a transitive dependency. Which should be avoided
• 21.
  Table conforming to3rd Normal Form EMPNO (Primary Key) FIRSTNAME LASTNAME WORKDEPTNO 000290 John Parker OP11 000320 Ramlal Mehta SE21 000310 Maude Setright OP11 WORKDEPTNO DEPTNAME OP11 Operations SE21 Software Support OP11 Operations Here all the fields are completely depend on Primary key.
• 22.
  End of Day2