1 / 27

Normalisation

Normalisation. A Worked Example. Un-normalised form. Write down every data object in the given source materials. Like this. Pick a Primary Key Qu1- What’s this all about?. Decide what the data is all about and underline that data item

trung
Download Presentation

Normalisation

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. 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

More Related