Database Normalization Lecture PDF

DATABASE Prepared by: Joey Arellano, MAEd, MSIT, MSCS-CAR, DHum(hc), NORMALIZATION DPed(hc)  Lesson Objectives  Fundamentals  Database Normalization Process  What is Normalization?  Normalization...

DATABASE Prepared by: Joey Arellano, MAEd, MSIT, MSCS-CAR, DHum(hc), NORMALIZATION DPed(hc)  Lesson Objectives  Fundamentals  Database Normalization Process  What is Normalization?  Normalization is the process of organizing data in a database, in order to reduce data redundancy and improve data integrity.  It refers to the specific process that allows database architects to turn unstructured data into a properly designed set of tables and data elements. Normalization is a bottom-up technique where we examine the relationship between attributes and reconfigure the tables accordingly. It is a multi-step process that puts data into tabular form, removing duplicated data from the relation tables. First proposed by Edgar F. Codd as part of his relational model.  Importance? 1. To make sure that there is a clear representation of the data , it’s relationship and constraints. 2. To group attributes according to relations to minimize or eliminate Data Redundancy. 3. To take up minimal storage space on the computer 4. maintenance problems 5. Inconsistent dependency. Example of Unnormalized Data Order Details table  What must be in a Normal Form? 1.Each table/relation represents a single subject. 2.Each row/column intersection contains only one value and not a group of values. 3.No data item will be unnecessarily stored in more than one table.  Three Common Normal Forms First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form ( 3NF) – Considered the highest level necessary  Conversion To First Normal Form  A repeating group derives its name from the fact that a group of multiple entries of the same type can exist for any single key attribute occurrence. The table does not contain repeating groups of fields or repeating data within the same field. *a repeating group is a series of fields/attributes that are repeated throughout a database table. It Contains only atomic values, An atomic value is a value that cannot be divided Each field contains the smallest meaningful value  Process of Conversion to 1st NF 1. Eliminate repeating groups in individual tables. 2. Create a separate table for each set of related data and identify the primary key. 3. Identify each set of related data dependencies on the primary key. Tables Violating First Normal Form Solution  Conversion to 2nd Normal Form  It is in 1NF.  All partial key dependencies are removed, where an attribute in a table depends on only a part of the primary key and not on the whole key.  Process of Conversion to 2nd NF 1. Create new/separate tables for sets of values that apply to multiple records. (Eliminate Partial Dependencies) 2. Relate these tables with a foreign key. Table Violating Second Normal Form  Conversion to 3rd Normal Form A table is in third normal form (3NF) when: It is in 2NF. Non-Primary key columns shouldn’t depend on the other non-Primary key columns. Usually used in tables with a single- field primary key Records do not depend on anything other than a table's primary key. Another Example  DREAM HOME CUSTOMER RENTAL  The Dream Home Customer Rental Details form holds details about property rented by a given customer.  assume that a renter rents a given property once and only one property at a time.  ORIGINAL TABLE CustNo Cname PropNo PAddr RntSt RntFnsh Rent OwnerNo OName John 6 Lawrence St, 7/1/10 8/31/06 700 Tina CR76 Kay PG4 Elmont 5 Nova CO40 Murphy Dr, 9/1/06 9/1/08 900 Tony Shaw PG16 East Meadow CO93 PG4 6 Lawrence 9/1/02 6/10/04 700 CO40 Tina Aline CR56 Stewart St, Elmont Murphy PG36 2 Manor Rd 8/1/04 12/1/05 750 CO93 Tony Scarsdale Shaw PG16 5 Nova Dr, 8/1/06 9/1/10 900 CO93 Tony East Meadow Shaw  Table in 1st Normal Form CustNo CName PropNo PAddr RntSt RntFnsh Rent OwnerN o OName CR76 John PG4 6 Lawrence 7/1/10 8/31/06 700 CO40 Tina Kay St, Elmont Murphy CR76 John PG16 5 Nova Dr, East 9/1/06 9/1/08 900 CO93 Tony Kay Meadow Shaw CR56 Aline PG4 6 Lawrence 9/1/02 6/10/04 700 CO40 Tina Stewart St, Elmont Murphy CR56 Aline PG36 2 Manor Rd 1/1/04 12/1/05 750 CO93 Tony Stewart Scarsdale Shaw CR56 Aline PG16 5 Nova Dr, East 8/1/06 9/1/08 900 CO93 Tony Stewart Meadow Shaw  Tables in 2nd Normal Form Customer Relation Rental Relation Property-Owner Relation CustNo CName CustNo PropNo RentStart RentFinish PropNo PAddr Rent OwnerNo OName PG4 6 Lawrence St, 700 CO40 Tina CR76 John Kay CR76 PG4 7/1/10 8/31/06 Elmont Murphy PG16 5 Nova Dr, East 900 CO93 Tony Shaw CR76 PG16 9/1/06 9/1/08 Meadow CR56 Aline Stewart PG4 6 Lawrence St, 700 CO40 Tina CR56 PG4 9/1/02 6/10/04 Elmont Murphy CR56 PG36 1/1/04 12/1/05 PG36 2 Manor Rd 750 CO93 Tony Shaw Scarsdale CR56 PG16 8/1/06 9/1/10 PG16 5 Nova Dr, East 900 CO93 Tony Shaw Meadow Functional Dependencies in 2NF Customer – CustNo → Cname Rental – CustNo, PropNo → RentStart, RentFinish – PropNo, RentStart → CustNo, RentFinish PropertyOwner – PropNo → Paddr, Rent, OwnerNo, OName – OwnerNo → Oname (Oname is not f.d. on PropNo)  Tables in 3rd Normal Form Table Relations in 3NF 4 relations: Customer(CustNo, Cname) Rental(CustNo, PropNo, RentStart, RentFinish) Property_For_Rent(PropNo, Paddress, Rent, OwnerNo) Owner(OwnerNo, OName)  Customer Relation Rental Relation CustNo PropNo RentStart RentFinish CustNo CName CR76 PG4 7/1/10 8/31/06 CR76 John Kay CR76 PG16 9/1/06 9/1/08 CR56 PG4 9/1/02 6/10/04 CR56 Aline Stewart CR56 PG36 1/1/04 12/1/05 CR56 PG16 8/1/06 9/1/10 Property for Rental Relation Owner Relation PropNo PAddr Rent OwnerNo OName PG4 6 Lawrence St, 700 CO40 Tina OwnerNo OName Elmont Murphy CO40 Tina Murphy CO93 Tony Shaw PG16 5 Nova Dr, East 900 CO93 Tony Shaw Meadow PG36 2 Manor Rd 750 CO93 Tony Shaw Scarsdale  Summary  Normalization is a technique used to design tables in which data redundancies are minimized. The first three normal forms (1NF, 2NF, and 3NF) are the most common.  A table is in 1NF when all key attributes are defined and all remaining attributes are dependent on the primary key.  A table is in 2NF when it is in 1NF and contains no partial dependencies. Therefore, a 1NF table is automatically in 2NF when its primary key is based on only a single attribute. A table in 2NF may still contain transitive dependencies.  A table is in 3NF when it is in 2NF and no nonkey attribute is functionally dependent on another nonkey attribute; that is, it cannot include transitive dependencies. End-of-lesson3