SlideServe
Télécharger Obtenir Pro
Menu
Accueil Pro
Préférences
ApparenceLight Aide et commentaires
© SlideServe · v2026

Understanding Data Normalization: A Worked Example of Un-Normalized Form

trung avatar
trung
27 diapositives · 408 Views · Sep 27, 2014

This guide provides a comprehensive overview of the normalization process in database design, using a practical worked example. It explains how to identify primary keys, recognize repeating groups, and transform un-normalized data into a properly structured format through First, Second, and Third Normal Forms. You'll learn how to eliminate partial dependencies, create new tables for repeating groups, and ensure that all data is effectively described by its keys. This structured approach simplifies database maintenance and improves data integrity.

Understanding Data Normalization: A Worked Example of Un-Normalized Form - slide 1
01 / 27Understanding Data Normalization: A Worked Example of Un-Normalized Form
1 / 17

Télécharger la présentation

Understanding Data Normalization: A Worked Example of Un-Normalized Form
Une image / un lien ci-dessous est fourni (tel quel) pour télécharger la présentation

Politique de téléchargement : le contenu du site vous est fourni TEL QUEL pour votre information et votre usage personnel ; il ne peut être vendu, concédé sous licence ni partagé sur d’autres sites sans l’accord de son auteur.Le contenu vous est fourni TEL QUEL, pour votre information et votre usage personnel uniquement. Téléchargez la présentation en cliquant sur ce lien. Lors du téléchargement, si vous ne parvenez pas à télécharger une présentation, l’éditeur a peut-être supprimé le fichier de son serveur.Pendant le téléchargement, si vous ne parvenez pas à obtenir une présentation, le fichier a peut-être été supprimé par l’éditeur.

F I N

Plus de contenu associé

Transcription de la présentation

  1. Normalisation A Worked Example

  2. Un-normalised form • Write down every data object in the given source materials.

  3. Like this

  4. Pick a Primary KeyQu1- What’s this all about? • Decide what the data is all about and underline that data item • There may be more than one obvious choice - pick any one! • (unlikely in exam?)

  5. Like this… or

  6. Identify repeating groupsQu 2 - How many? • For each <primary key> is there one or more than one of each of the other data items…?

  7. In this case • For each event number is there one or more than one event name? • For each event number is there one or more than one competitor name? …… • For each event number is there one or more than one school name? … Write 1 or M beside each data item

  8. Like this… or

  9. To make it look pretty…put brackets round all the Ms.. This is the repeating group

  10. Write the repeating group as a new tableTake a copy of the primary key with it position competitor_no performance competitor_name school_name school_address1 school_address2 event_no

  11. Decide on a primary key for the new table – decide what this entity is all about (often a compound key here) • position • competitor_no • performance • competitor_name • school_name • school_address1 • school_address2 • event_no Draw a table of sample data to help identify the key attributes

  12. Tidy things up • Write the primary keys at the top of the table • Mark the copy of the original key as a foreign key competitor_no event_no* position performance competitor_name school_name school_address1 school_address2 event_no competitor_no* event_name position performance or

  13. 1st Normal Form or

  14. Remove partial dependencies • How? • Examine table with more than 1 key • Draw a grid • Number of rows = number of data items • competitor_no + event_no + position + performance + competitor_name + school_name + school_address1 + school_address2 = 8 • Number of columns = number of keys +2 • competitor_no + event_no + 2 = 4

  15. Insert data item names in first column

  16. Fill the second column with some supplied data

  17. Qu3 What can be different? • Change one key and fill in data in third column • Mark data which may change • Circle, underline, different colour…

  18. Like this…

  19. Repeat for next column – changing a different key

  20. Remove all data items marked in both (all) columns along with copies of the keys Mark the (two) keys as foreign keys

  21. Look at column with ‘Must Change or Maybe’ dataWrite the remaining data items in new list(s) along with marked key in that column

  22. 2nd Normal Form Check each table to make sure the key determines all the data items Yes – there is only one Yes – we know from our previous table Better have a closer look at this one!!

  23. Qu4 Does this data describe the key? - yes, it tells us about the competitor yes, it tells us which school the competitor attends no, it tells us about the school no, it tells us about the school

  24. Move to 3NF • Remove non-descriptive items to a new table with the item they describe. Leave a copy of this item as a foreign key

  25. Like this…

  26. 3NF