1 / 27

NOrmalization

NOrmalization. By: Shiraj Mohamed M | MIS | SEUSL. Normalization. Basically, normalization is the process of structuring the tables in a database for maximum efficiency

finna
Download Presentation

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. NOrmalization By: Shiraj Mohamed M | MIS | SEUSL SHIRAJ MOHAMED M | MIS | SEUSL

  2. Normalization • Basically, normalization is the process of structuring the tables in a database for maximum efficiency • Normalization involves creating tables and establishing relationships between those tables to make the database more flexible by eliminating redundancy and inconstancies. SHIRAJ MOHAMED M | MIS | SEUSL

  3. Normalization… • Conceptual Modeling is a subjective process • Therefore, the schema after the logical database design phase may not be very good (contain redundant data) • However, there are formalisms to ensure that the schema is good. • This process is called Normalization SHIRAJ MOHAMED M | MIS | SEUSL

  4. Normalization… • Relational database schema = set of relations • Relation = set of attributes • How we group the attributes to relations is very important SHIRAJ MOHAMED M | MIS | SEUSL

  5. Schema refinement…. Discussed SHIRAJ MOHAMED M | MIS | SEUSL

  6. Schema Refinement Steps • Determine Functional dependencies for relation • Find all keys in relation • Normalize the relation SHIRAJ MOHAMED M | MIS | SEUSL

  7. Normalization…. • There are many Normal Forms proposed to reduce redundancies • Some of the well-known ones are: • 1st Normal Form • 2nd Normal Form • 3rd Normal Form • Boyce-Codd Normal Form SHIRAJ MOHAMED M | MIS | SEUSL

  8. Review of some terms… • Superkey: Set of attributes S in relation R that can be used to identify each tuple uniquely. • Key: A key is a superkey with the additional property that removal of any attributes from the key will not satisfy the key condition SHIRAJ MOHAMED M | MIS | SEUSL

  9. Review of some terms… • Candidate Key: Each key of a relation is called a candidate key • Primary Key: A candidate key is chosen to be the primary key • Prime Attribute: an attribute which is a member of a candidate key • Nonprime Attribute: An attribute which is not prime SHIRAJ MOHAMED M | MIS | SEUSL

  10. First Normal Form (1NF) • Identify repeating groups of fields • Remove repeating groups to a separate tables. • Identify the keys for the tables SHIRAJ MOHAMED M | MIS | SEUSL

  11. Second Normal Form (2NF) • Check if all fields are dependent of the whole key • Remove Fields That depend on part of the key • Group Partially dependent fields as a separate table • Name the tables • Identify keys to the tables SHIRAJ MOHAMED M | MIS | SEUSL

  12. Third Normal Form (3NF) • Remove Fields That Depend on other non key attribute • Group interdependent fields as separate tables , identifies the key and name the tables SHIRAJ MOHAMED M | MIS | SEUSL

  13. Example SHIRAJ MOHAMED M | MIS | SEUSL

  14. First Normal Form (1NF) • Eliminate any duplicate or repeating columns or groups in the same table. • Since a Customer can place/have many orders, the above table now can be break into two. Customer Details Table SHIRAJ MOHAMED M | MIS | SEUSL

  15. First Normal Form (1NF) … • Order Details Table SHIRAJ MOHAMED M | MIS | SEUSL

  16. Second Normal Form (2NF) • Eliminate redundant data. • Now the table structure is improved. But you will notice that the Order Date is repeated. So take that out from this table. SHIRAJ MOHAMED M | MIS | SEUSL Improved Order Details Table

  17. Second Normal Form (2NF)… • Order-Item Table SHIRAJ MOHAMED M | MIS | SEUSL

  18. Third Normal Form (3NF) • Eliminate fields that do not depend on the primary key • You can notice that the Item Descriptions, Unit Price are not depend on the Oder No. So easily we can take them out from this Order-Item table. Improved Order-Item Table SHIRAJ MOHAMED M | MIS | SEUSL

  19. Third Normal Form (3NF) … Improved Order-Item Table Item Details Table SHIRAJ MOHAMED M | MIS | SEUSL

  20. Now you will notice that all the details are simplified. So finally you will make four (4) table to store these order details in the database. The tables will be as follows SHIRAJ MOHAMED M | MIS | SEUSL Order Details Table Customer Details Table

  21. Order-Item Table Item Details Table SHIRAJ MOHAMED M | MIS | SEUSL

  22. FD1 FD2 FD3 Example02 EMP_PROJ SHIRAJ MOHAMED M | MIS | SEUSL

  23. Example02… EP1 SHIRAJ MOHAMED M | MIS | SEUSL EP2 EP3

  24. Example02… SHIRAJ MOHAMED M | MIS | SEUSL EMP_DEPT

  25. Example02… ED1 ED2 SHIRAJ MOHAMED M | MIS | SEUSL

  26. DeNormalization • Denormalization… Sometime for performance reasons, database designer may leave the relation in a lower normal form. This process is known as denormalization. SHIRAJ MOHAMED M | MIS | SEUSL

  27. End… SHIRAJ MOHAMED M | MIS | SEUSL

More Related