1 / 21

Database Normalization

Database Normalization. Raymond Mark/Learn iT! Incorporated. What Is Normalization?. Process of modifying a database into different normal forms (stages of normalization) Process of organizing data to eliminate repetition Process of organizing data to make a database more efficient.

ashely-stanley
Download Presentation

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. Database Normalization Raymond Mark/Learn iT! Incorporated

  2. What Is Normalization? • Process of modifying a database into different normal forms (stages of normalization) • Process of organizing data to eliminate repetition • Process of organizing data to make a database more efficient

  3. Normal Forms • First Normal Form • Flat file tables • No field repetition • Second Normal Form • Meets all First Normal Form requirements • Non-key attributes are functionally dependent on primary key

  4. Normal Forms • Third Normal Form(Boyce-Codd Normal Form, BCNF) • Meets all Second Normal Form requirements • Eliminates all fields not fully dependent on the primary key field

  5. Normal Forms • Fourth Normal Form • Meets all Third Normal Form requirements • Given relation may not contain more than one multivalued attribute • Fifth Normal Form • Meets all Third Normal Form requirements • Contains data that enables reconstruction of original data source

  6. Database Design Guidelines • Spend the bulk of your effort on table and relational design • Consider the tradeoff between normalization rules and actual data usage • Establish table objects and relationships before creating other MS Access objects • Use consistent naming conventions • Test your objects and design before fully populating tables

  7. Normalizing Guidelines • Make Your Data Atomic • Avoid Field Repetition • Avoid Data Repetition • Make Primary Keys Short and Stable • Maintain Table Relevance • Maintain Field Independence

  8. Rule 1: Make Your Data Atomic • Break the data into fields that are as small as usably possible • It’s easier to search for information when the data are in their own fields • In MS Access, it’s easier to fuse two pieces of data into one than the other way around

  9. Rule 1: Make Your Data Atomic

  10. Rule 2: Avoid Field Repetition • This rule helps to achieveFirst Normal Form • In most cases of field repetition, the number of fields that may eventually be required is usually unknown.

  11. Rule 2: Avoid Field Repetition • Fields that are repeated may make you set aside memory that you never fill with data • Fields that are repeated have to be searched on individually, increasing the demands on a data search • Separate repeating fields into their own table, unifying the fields in the process

  12. Rule 2: Avoid Field Repetition

  13. Rule 2: Avoid Field Repetition

  14. Rule 2: Avoid Field Repetition

  15. Rule 3: Avoid Data Repetition • This rule helps to achieveSecond Normal Form • When data entry is repeated, it’s an indication that the information should be consolidated into a single reference point • The reference point for the consolidated data is that row’s Primary Key

  16. Rule 3: Avoid Data Repetition

  17. Rule 3: Avoid Data Repetition

  18. Rule 3: Avoid Data Repetition

  19. Rule 4: Make Primary KeysShort and Stable • Primary Keys should be short because they are used for searching through records and identifying data • Primary Keys should be stable because changes made to Primary Key data have to be propagated to matching Foreign Keys

  20. Rule 5: Maintain Table RelevanceRule 6: Maintain Field Independence • These rule helps to achieveThird Normal Form • Each of your tables should represent a single entity, that is, provide information on a single subject • The entity should be represented by a primary key

  21. Rule 5: Maintain Table RelevanceRule 6: Maintain Field Independence

More Related