1 / 18

Normalization

Normalization. Are we Normal. Normalization. Normalization is the process of converting complex data structures into simple, stable data structures It also is the process of removing from a database certain “anomalies”. Anomalies.

tynice
Télécharger la présentation

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 Are we Normal

  2. Normalization • Normalization is the process of converting complex data structures into simple, stable data structures • It also is the process of removing from a database certain “anomalies”

  3. Anomalies • Update anomalies—you have to update a record in a number of different places • Insertion anomalies—Example: in order to insert a new employee a project must be assigned. If there is no project yet a phantom one must be created. • Deletion anomalies—Two types: when you delete a record other vital information is lost, or must delete in several places with the possibility of leaving unattached data islands

  4. Normal Forms • There are many Normal Forms—or stages of normalization possible, but we will only focus on the first three.

  5. First Normal Form • There are no duplicated rows in the table. • Each cell is single-valued (i.e., there are no repeating groups or arrays). • Entries in a column (attribute, field) are of the same kind.

  6. Example Table 1 CDS Table 1

  7. Another Example Table

  8. Normalizing • The sample tables have repeating groups—ie the tracks associated with each CD. • Each column must contain only a single value • You also don’t want to find yourself numbering columns like track1, etc. • The next table puts the sample table into first normal form

  9. First Normal Form Sample

  10. Second Normal Form • A table is in 2NF if it is in 1NF and if all non-key attributes are dependent on all of the key and nothing else. • This is called functional dependency

  11. Normalizing. . . • In our sample table there are really two separate things going on • One is the CD information and one is the track information- • To get all track information creates a lot of redundancy in the CD information • Each should be dependent on their own key

  12. Second Normal Form Sample

  13. Third Normal Form • A table is in 3NF if it is in 2NF and if it has no transitive dependencies. • This means that the non primary key attributes don’t depend on each other. • Look at our second sample table:

  14. Sample Table

  15. Normalizing • There is a transitive dependency here • Artist Country is dependent on Artist, not on TrackID which is the key field of the table • The following tables resolve this:

  16. Better

  17. Summary • Through the process of normalization our original table has become three tables, related by foreign keys: CDs(CDID, CDTitle) ARTISTS(ArtistID, Artist, ArtistCountry) TRACKS(TrackID, TrackTitle, CDID, ArtistID)

  18. MORE… • Boyce Codd Normal Form • Fourth Normal Form • Fifth Normal Form

More Related