database design normalisation an introduction n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Database Design Normalisation – An introduction PowerPoint Presentation
Download Presentation
Database Design Normalisation – An introduction

Loading in 2 Seconds...

play fullscreen
1 / 38

Database Design Normalisation – An introduction - PowerPoint PPT Presentation


  • 63 Views
  • Uploaded on

Database Design Normalisation – An introduction. Keys. What list of information would you need to buy the correct game?. 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?.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Database Design Normalisation – An introduction' - lee


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide4

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

slide5

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 ?

slide6

We can use keys to find game information from different sources:

Title

+ Version

+ Platform

Game

reviews

£

Game

Prices

Title

+ Version

+ Platform

slide8

Gamer and game relationship ..

Gamer:

Steve

Kim

Jo

How many games could Steve own?

How many people could own Modern Warfare”?

Games:

slide9

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)
cars are designed to be

Cars are designed to be ..

faster

more efficient

easy to maintain

normalisation improves our database design

Normalisation … improves our database design …

faster

more efficient

easy to maintain

slide13

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?

slide14

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

slide15

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

slide16

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

slide17

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

slide18

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?

slide19

Steve wants 3 games for xmas

How could we rewrite this information on one note?

Modern Warfare 2

Bioshock

Tomb Raider

Steve

slide20

Steve - Modern Warfare, Bioshock, Tomb Raider

Write all games in a line against Steves name ….

How do we add games to the list ?

slide21

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 …

slide22

Steve - Modern Warfare

Steve – Bioshock

Steve - Tomb Raider

Write Steves name against each game ..

How do we add games to the list ?

slide23

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

slide24

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

slide25

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’

slide27

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?

slide28

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

slide29

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

slide30

Are there any other great mysteries ?

Step 2 of the normalisation process

‘non key fields should depend upon all parts of the key’

slide31

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

slide32

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

slide33

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

slide34

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

slide35

The final frontier …

Step 3 of the normalisation process

‘remove non key dependencies’

slide36

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

slide37

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