1 / 29

Rules of Database Normalization

Rules of Database Normalization. Overview. What is Normalization ? What is the objective of Normalization ? Why is Normalization in a database important? What are NORMAL FORMS ? How do we accomplish Normalization in our database? Are there times when Normalization is unnecessary?.

tamesis-tanith
Download Presentation

Rules of Database Normalization

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Rules of DatabaseNormalization

  2. Overview What is Normalization? What is the objective of Normalization? Why is Normalization in a database important? What are NORMAL FORMS? How do we accomplish Normalization in our database? Are there times when Normalization is unnecessary?

  3. What is Normalization? Normalization refers to the process of creating an efficient, reliable, flexible, and appropriate “relational” structure for storing information in a “relational” data structure. Normalization usually involves dividing a database into two or more tables and defining the relationship between the tables.

  4. What is the objective of Normalization? 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.

  5. Why is Normalization in a database important? Normalization removes redundant data from your tables in order to improve storage efficiency. Normalization removes redundant data and improves data integrity. Normalization removes redundant data and helps in maintenance problems. Example: If data that exists in more than one place must be changed, the data must be changed in exactly the same way in all locations. Finally, Normalization removes redundant data and improves the scalability of your database.

  6. What are NORMAL FORMS? • A series of logical steps to normalize data tables. • First Normal Form-1NF • Second Normal Form-2NF • Third Normal Form-3NF • Fourth Normal Form-4NF • Fifth Normal Form-5NF • We will learn the First, Second and Third Normal Forms

  7. What is the First Normal Form? The definition of the first Normal Form is as follows • There are no repeating groups. • All of the key attributes are defined. • All attributes are dependent on the primary key. Example on the next slide.

  8. How do we accomplish First Normal Form? Look at this example.

  9. Notice the repeating Groups.

  10. This is our solution to eliminating the repeating groups

  11. How can we set our Primary Key?

  12. Can this be our Primary Key?

  13. Or, can this be our Primary Key?

  14. Solution-We need both as our Primary Keys.

  15. Is our table in First Normal Form?

  16. What is Second Normal Form • A table is in 2nd Normal Form if: • It is in 1st normal form. • It includes no partial dependencies (where an attribute is dependent on only a part of a primary key).

  17. Does it include partial dependencies? Do you notice anything else?

  18. Look at “Project Name” Row 3. Also look at Hourly Rate? What about data integrity?

  19. Step One: Employee-Project Table Project Name is only Dependent on Project Number; so, we create a “Employee Project Table.”

  20. Step Two: Project Table Notice that “Project Name” is dependent on only “Project Number.” We create our “Project Table” With Project Number as our Primary Key. Look at the space we have saved. What else have we done?

  21. Step Three: Employee Table Notice that Employee Name, Rate Category, and Hourly Rate are dependent on Employee Number. We create our “Employee Table” with Employee Number as our Primary Key.

  22. Are we now in Second Normal Form? Project Table Project Table Employee Project Table Employee Table Employee Project Table Is it in First Normal Form and does it includes no partial Dependencies?

  23. Now, What is Third Normal Form? It is in SECOND Normal Form. It contains no transitive dependencies (where a non-key attribute is dependent on another non-key attribute). Let’s see where we begin.

  24. Let’s take another look at what we have.? Project Table Project Table Employee Project Table Employee Table Employee Project Table

  25. Let’s look at the Employee Project Table Employee Project Table Can we do anything else with this table?

  26. Let’s Look at the Project Table. Can we do anything else with this table? Project Table

  27. Let’s look at the Employee table Employee Table Can we further normalize this table?

  28. See what we can do with the employee table. • Let’s make an “Rate” Table Employee Table Rate Table We have now reduced the possibility of Employees being paid Incorrectly. Our database Will have more integrity.

  29. That’s It! • Our data is now in 3NF.. • Ready to be used in a DBMS…

More Related