Database Design Normalisation – An introduction

1. Database DesignNormalisation – An introduction

2. Keys

3. What list of information would you need to buy the correct game?

4. Platform GameID Title PS3 1202 Fallout 3 No other game will have the same ID Version Do these pieces of information uniquely identify this game? How else could we uniquely identify this game? Game of year edition

5. Unique identifiers for the game could be: Key GameID or a combination of Title + Version + Platform Composite Key What do we call the pieces of information that uniquely identify the game ?

6. We can use keys to find game information from different sources: Title + Version + Platform Game reviews £ Game Prices Title + Version + Platform

7. Entity Relationships

8. Gamer and game relationship .. Gamer: Steve Kim Jo How many games could Steve own? How many people could own Modern Warfare”? Games:

9. Gamer to Games relationship: • 1 gamer can have many games • many gamers can have the same game What kind of relationship is this? Gamer Game • many to many relationship (n:n)

10. Normalisation

11. Cars are designed to be .. faster more efficient easy to maintain

12. Normalisation … improves our database design … faster more efficient easy to maintain

13. Steve, Jo and Kim write the list of games they want for xmas: Modern Warfare 2 Modern Warfare 2 Bioshock Tomb Raider Tomb Raider Fable II Kim Steve Jo Using these lists would you be able to buy the correct game & send it to the correct gamer?

14. What additional game data might we need?: Platform X360 Modern Warfare 2 Modern Warfare 2 Bioshock Tomb Raider Tomb Raider Fable II Title GameID Modern Warfare 2 5426 CompanyName Infinity Ward

15. We need more information to identify the correct game: GameID Title Platform Company Name 5426 Modern Warfare 2 X360 Infinity Ward Modern Warfare 2 Modern Warfare 2 Bioshock Tomb Raider Tomb Raider Fable II 5426 Modern Warfare 2 X360 Infinity Ward 7003 Bioshock X360 2K 1110 Tomb Raider X360 EIDOS 1110 Tomb Raider X360 EIDOS 2112 Fable II X360 LionHead Game list has information that uniquely identifies the game e.g. GameID

16. What additional gamer information might we need?: A unique GamerID would be ideal !! Name GamerID Steve Lonely 9089 Kim Steve Jo DateofBirth 23 Jan 1980

17. We need more information to identify the correct gamer: GamerID Name DateofBirth 3120 Kim Newbie 10 Mar 1991 Kim Steve Jo 9089 Steve Lonely 23 Jan 1980 7707 Jo Headshot 31 Dec 1968 Gamer list has information that uniquely identifies the gamer e.g. GamerID

18. Can we now buy the correct game & send it to the correct gamer? GamerID Name DateofBirth GameID Title Platform Company Name 3120 Kim Newbie 10 Mar 1991 5426 Modern Warfare 2 X360 Infinity Ward 9089 Steve Lonely 23 Jan 1980 5426 Modern Warfare 2 X360 Infinity Ward 7003 Bioshock X360 2K 1110 Tomb Raider X360 EIDOS 7707 Jo Headshot 31 Dec 1968 1110 Tomb Raider X360 EIDOS 2112 Fable II X360 LionHead Which gamer wants which game?

19. Steve wants 3 games for xmas How could we rewrite this information on one note? Modern Warfare 2 Bioshock Tomb Raider Steve

20. Steve - Modern Warfare, Bioshock, Tomb Raider Write all games in a line against Steves name …. How do we add games to the list ?

21. We could add games by adding more and more columns … GamerID Name DateofBirth GameID GameID GameID GameID GameID GameID GameID GameID GameID 9089 Steve Lonely 23 Jan 1980 1110 12310 9910 2010 5426 1910 1210 7003 4410 This is called a ‘repeating’ field …

22. Steve - Modern Warfare Steve – Bioshock Steve - Tomb Raider Write Steves name against each game .. How do we add games to the list ?

23. We could add games without adding more columns just data… GamerID Name DateofBirth GameID 9089 Steve Lonely 23 Jan 1980 5426 9089 Steve Lonely 23 Jan 1980 7003 9089 Steve Lonely 23 Jan 1980 1110 9089 Steve Lonely 23 Jan 1980 980 9089 Steve Lonely 23 Jan 1980 7720

24. Which is the best way to join our lists together and why? Steve – Modern Warfare 2, Bioshock, Tomb Raider… OR Steve - Modern Warfare 2 Steve - Bioshock Steve - Tomb Raider

25. Congratulations !!! You have just witnessed one of the great mysteries of the database universe !! Step 1 of the normalisation process ‘Avoid repeating fields when linking lists/tables’

27. Lets see this work against our data? GamerID Name DateofBirth GameID GameID Title Platform Company Name 9089 Steve Lonely 23 Jan 1980 5426 5426 Modern Warfare 2 X360 Infinity Ward 7003 Bioshock X360 2K 1110 Tomb Raider X360 EIDOS We have a link – is there a problem?

28. Can we improve our ‘repeating field’ design? GamerID Name DateofBirth GameID Repeating field GameID Key field GamerID make the repeating field part of the key … GamerID GameID

29. We could add games without adding more columns just data… GamerID Name DateofBirth GameID Good design No repeating fields !! 9089 Steve Lonely 23 Jan 1980 5426 9089 Steve Lonely 23 Jan 1980 7003 9089 Steve Lonely 23 Jan 1980 1110 9089 Steve Lonely 23 Jan 1980 980 9089 Steve Lonely 23 Jan 1980 7720

30. Are there any other great mysteries ? Step 2 of the normalisation process ‘non key fields should depend upon all parts of the key’

31. non key fields ‘Name’, ‘DateofBirth’ should depend upon all parts of the key ‘GamerID & GameID’ Is ‘Name’ Gamer data or Game data or both? Is ‘DateofBirth’ Gamer data or Game data or both? GamerID Name DateofBirth GameID 9089 Steve Lonely 23 Jan 1980 5426 9089 Steve Lonely 23 Jan 1980 7003 Gamer only ! (GamerID) Gamer only ! (GamerID) 9089 Steve Lonely 23 Jan 1980 1110 9089 Steve Lonely 23 Jan 1980 980 9089 Steve Lonely 23 Jan 1980 7720 non key fields depend on just a bit of the key not all of it !!! .. we can improve this design .. … but whats the point …. ? To remove all that duplicate data !!!!

32. Move ‘Name’ and ‘DateofBirth’ to their own table? Name DateofBirth GameID GameID GamerID Name DateofBirth GamerID Steve Lonely 23 Jan 1980 5426 5426 9089 Steve Lonely 23 Jan 1980 9089 Steve Lonely 23 Jan 1980 7003 7003 9089 Steve Lonely 23 Jan 1980 1110 1110 9089 But what about the GameID field? Steve Lonely 23 Jan 1980 980 980 9089 Steve Lonely 23 Jan 1980 7720 7720 9089 GameID needs to remain with the GamerID to keep the link between games and gamers

33. Non key fields should depend upon all parts of the key .. GamerID Name DateofBirth 9089 Steve Lonely 23 Jan 1980 GamerID Name DateofBirth GameID original table becomes 9089 Steve Lonely 23 Jan 1980 5426 GamerID GameID 9089 Steve Lonely 23 Jan 1980 7003 9089 5426 9089 Steve Lonely 23 Jan 1980 1110 9089 7003 9089 Steve Lonely 23 Jan 1980 980 9089 1110 9089 Steve Lonely 23 Jan 1980 7720 9089 980 9089 7720

34. Is there anything else we can improve… ? GamerID Name DateofBirth 9089 Steve Lonely 23 Jan 1980 GamerID GameID GameID Title Platform Company ID Company Name 9089 5426 5426 Modern Warfare 2 X360 4322 Infinity Ward 9089 7003 7003 Bioshock X360 5422 2K 9089 1110 1110 Tomb Raider X360 1120 EIDOS 9089 980 980 Dead Space X360 0922 EA 9089 7720 7720 GOW X360 8727 EPIC

35. The final frontier … Step 3 of the normalisation process ‘remove non key dependencies’

36. Remove non key dependencies… an example … CompanyName is dependent upon ComanyID not GameID GameID Title Platform Company ID Company Name 5426 Modern Warfare 2 X360 4322 Infinity Ward 7003 Bioshock X360 5422 2K Etc. Move CompanyName to its own table

37. Remove non key dependencies… ? GameID Title Platform Company ID Company Name 5426 Modern Warfare 2 X360 4322 Infinity Ward 7003 Bioshock X360 5422 2K Etc. becomes 2 tables GameID Title Platform Company ID Company ID Company Name 5426 Modern Warfare 2 X360 4322 7003 Bioshock X360 5422 4322 Infinity Ward Etc. 5422 2K

38. To boldly normalise …