Презентация на тему Normalisation. Describe relational databases and their use

Normalisationdescribe relational databases and their use Success criteriaknow what is normalizationknow the purpose of the 3 forms of normalization (1NF, 2NF, 3NF)can Database normalization is the process of removing redundant data from your tables in to improve storage 1NF - Atomic Data TestIf a table has a primary key it is said to be Take the following table.  StudentID is the primary key.Is it 1NF? No. There are repeating groups (subject, subjectcost, grade)How can you make it 1NF? Create new rows so each cell contains only one valueBut now look – is the studentID No – the studentID no longer uniquely identifies each rowYou now need to declare studentID and So. We now have 1NF.Is it 2NF? Studentname and address are dependent on studentID (which is part of the key) This is good.But And 2NF requires…All non-key fields are dependent on the ENTIRE key (studentID + subject) So it’s not 2NFHow can we fix it? Make new tablesMake a new table for each primary key fieldGive each new table its own Step 1STUDENT TABLE (key = StudentID) Step 2STUDENT TABLE (key = StudentID)SUBJECTS TABLE (key = Subject) Step 3STUDENT TABLE (key = StudentID)SUBJECTS TABLE (key = Subject)RESULTS TABLE (key = StudentID+Subject) Step 3STUDENT TABLE (key = StudentID)SUBJECTS TABLE (key = Subject)RESULTS TABLE (key = StudentID+Subject) Step 4 - relationshipsSTUDENT TABLE (key = StudentID)SUBJECTS TABLE (key = Subject)RESULTS TABLE (key = StudentID+Subject) Step 4 - cardinalitySTUDENT TABLE (key = StudentID)SUBJECTS TABLE (key = Subject)RESULTS TABLE (key = StudentID+Subject)1Each Step 4 - cardinalitySTUDENT TABLE (key = StudentID)SUBJECTS TABLE (key = Subject)RESULTS TABLE (key = StudentID+Subject)11Each Step 4 - cardinalitySTUDENT TABLE (key = StudentID)SUBJECTS TABLE (key = Subject)RESULTS TABLE (key = StudentID+Subject)118A Step 4 - cardinalitySTUDENT TABLE (key = StudentID)SUBJECTS TABLE (key = Subject)RESULTS TABLE (key = StudentID+Subject)1188A A 2NF checkSTUDENT TABLE (key = StudentID)SUBJECTS TABLE (key = Subject)RESULTS TABLE (key = StudentID+Subject)1188SubjectCost is A 2NF checkSTUDENT TABLE (key = StudentID)SUBJECTS TABLE (key = Subject)RESULTS TABLE (key = StudentID+Subject)1188Grade is A 2NF checkSTUDENT TABLE (key = StudentID)SUBJECTS TABLE (key = Subject)RESULTS TABLE (key = StudentID+Subject)1188Name, Address But is it 3NF?STUDENT TABLE (key = StudentID)SUBJECTS TABLE (key = Subject)RESULTS TABLE (key = StudentID+Subject)1188So A 3NF checkSTUDENT TABLE (key = StudentID)SUBJECTS TABLE (key = Subject)RESULTS TABLE (key = StudentID+Subject)1188Oh oh…What? A 3NF checkSTUDENT TABLE (key = StudentID)SUBJECTS TABLE (key = Subject)RESULTS TABLE (key = StudentID+Subject)1188HouseName is A 3NF checkSTUDENT TABLE (key = StudentID)SUBJECTS TABLE (key = Subject)RESULTS TABLE (key = StudentID+Subject)1188Or HouseColour A 3NF checkSTUDENT TABLE (key = StudentID)SUBJECTS TABLE (key = Subject)RESULTS TABLE (key = StudentID+Subject)1188But either A 3NF checkSTUDENT TABLE (key = StudentID)SUBJECTS TABLE (key = Subject)RESULTS TABLE (key = StudentID+Subject)1188And 3NF A 3NF checkSTUDENT TABLE (key = StudentID)SUBJECTS TABLE (key = Subject)RESULTS TABLE (key = StudentID+Subject)1188WHAT DO Again, carve off the offending fieldsSUBJECTS TABLE (key = Subject)RESULTS TABLE (key = StudentID+Subject)1188 A 3NF fixSUBJECTS TABLE (key = Subject)RESULTS TABLE (key = StudentID+Subject)1188 A 3NF fixSUBJECTS TABLE (key = Subject)RESULTS TABLE (key = StudentID+Subject)118818 A 3NF win!Or… The RevealBefore…After…RESULTS TABLE (key = StudentID+Subject)118818SUBJECTS TABLE (key = Subject)

Презентацию Normalisation. Describe relational databases and their use, из раздела: Информатика,  в формате PowerPoint (pptx) можно скачать внизу страницы, поделившись ссылкой в социальных сетях! Презентации взяты из открытого доступа или загружены их авторами, администрация сайта не отвечает за достоверность информации в них. Все права принадлежат авторам материалов: Политика защиты авторских прав

Слайды и текст этой презентации

Слайд 1

Normalisation

describe relational databases and their use


Слайд 2

normalization (1NF, 2NF, 3NF)can create 3 forms of normalization for a table

Success criteria

know what is normalization
know the purpose of the 3 forms of normalization (1NF, 2NF, 3NF)
can create 3 forms of normalization for a table


Слайд 3

in to improve storage efficiency, data integrity, and scalability. In the relational model, methods exist

Database normalization is the process of removing redundant data from your tables in to improve storage efficiency, data integrity, and scalability.

In the relational model, methods exist for quantifying how efficient a database is. These classifications are called normal forms (or NF), and there are algorithms for converting a given database between them.


Слайд 4

is said to be in First Normal form if the table does not have repeating

1NF - Atomic Data Test
If a table has a primary key it is said to be in First Normal form if the table does not have repeating groups of attributes. All attributes within the table need to be dependent only on the primary key.
2NF - Partial Dependence Test
For a table to be in Second Normal form it must first be in First Normal (1NF) Form and then contain no data that is dependent on only part of the Primary Key
3NF - Non-Key Dependence Test
For a table to be in Third Normal Form(3NF) it must be in Second Normal form and contain No data that is not dependent on the primary Key e.g. (Remove columns that are not dependent upon the primary key.)


Слайд 6

Take the following table. StudentID is the primary key.

Is it 1NF?


Слайд 7

1NF?

No. There are repeating groups (subject, subjectcost, grade)

How can you make it 1NF?


Слайд 8

– is the studentID primary key still valid?

Create new rows so each cell contains only one value

But now look – is the studentID primary key still valid?



Слайд 9

to declare studentID and subject together to uniquely identify each row.So the new key is

No – the studentID no longer uniquely identifies each row

You now need to declare studentID and subject together to uniquely identify each row.

So the new key is StudentID and Subject.


Слайд 10

So. We now have 1NF.

Is it 2NF?


Слайд 11

key) This is good.But they are not dependent on Subject (the other part of the

Studentname and address are dependent on studentID (which is part of the key) This is good.

But they are not dependent on Subject (the other part of the key)


Слайд 12

+ subject)

And 2NF requires…

All non-key fields are dependent on the ENTIRE key (studentID + subject)


Слайд 13

So it’s not 2NF

How can we fix it?


Слайд 14

new table its own primary keyMove columns from the original table to the new table

Make new tables

Make a new table for each primary key field
Give each new table its own primary key
Move columns from the original table to the new table that matches their primary key…


Слайд 15

Step 1

STUDENT TABLE (key = StudentID)


Слайд 16

Step 2

STUDENT TABLE (key = StudentID)

SUBJECTS TABLE (key = Subject)


Слайд 17

= StudentID+Subject)

Step 3

STUDENT TABLE (key = StudentID)

SUBJECTS TABLE (key = Subject)

RESULTS TABLE (key = StudentID+Subject)


Слайд 18

= StudentID+Subject)

Step 3

STUDENT TABLE (key = StudentID)

SUBJECTS TABLE (key = Subject)

RESULTS TABLE (key = StudentID+Subject)


Слайд 19

TABLE (key = StudentID+Subject)

Step 4 - relationships

STUDENT TABLE (key = StudentID)

SUBJECTS TABLE (key = Subject)

RESULTS TABLE (key = StudentID+Subject)


Слайд 20

TABLE (key = StudentID+Subject)1Each student can only appear ONCE in the student table

Step 4 - cardinality

STUDENT TABLE (key = StudentID)

SUBJECTS TABLE (key = Subject)

RESULTS TABLE (key = StudentID+Subject)

1

Each student can only appear ONCE in the student table


Слайд 21

TABLE (key = StudentID+Subject)11Each subject can only appear ONCE in the subjects table

Step 4 - cardinality

STUDENT TABLE (key = StudentID)

SUBJECTS TABLE (key = Subject)

RESULTS TABLE (key = StudentID+Subject)

1

1

Each subject can only appear ONCE in the subjects table


Слайд 22

TABLE (key = StudentID+Subject)118A subject can be listed MANY times in the results table (for

Step 4 - cardinality

STUDENT TABLE (key = StudentID)

SUBJECTS TABLE (key = Subject)

RESULTS TABLE (key = StudentID+Subject)

1

1

8

A subject can be listed MANY times in the results table (for different students)


Слайд 23

TABLE (key = StudentID+Subject)1188A student can be listed MANY times in the results table (for

Step 4 - cardinality

STUDENT TABLE (key = StudentID)

SUBJECTS TABLE (key = Subject)

RESULTS TABLE (key = StudentID+Subject)

1

1

8

8

A student can be listed MANY times in the results table (for different subjects)


Слайд 24

(key = StudentID+Subject)1188SubjectCost is only dependent on the primary key, Subject

A 2NF check

STUDENT TABLE (key = StudentID)

SUBJECTS TABLE (key = Subject)

RESULTS TABLE (key = StudentID+Subject)

1

1

8

8

SubjectCost is only dependent on the primary key,
Subject



Слайд 25

(key = StudentID+Subject)1188Grade is only dependent on the primary key (studentID + subject)

A 2NF check

STUDENT TABLE (key = StudentID)

SUBJECTS TABLE (key = Subject)

RESULTS TABLE (key = StudentID+Subject)

1

1

8

8

Grade is only dependent on the primary key (studentID + subject)



Слайд 26

(key = StudentID+Subject)1188Name, Address are only dependent on the primary key(StudentID)

A 2NF check

STUDENT TABLE (key = StudentID)

SUBJECTS TABLE (key = Subject)

RESULTS TABLE (key = StudentID+Subject)

1

1

8

8

Name, Address are only dependent on the primary key
(StudentID)



Слайд 27

TABLE (key = StudentID+Subject)1188So it is 2NF!

But is it 3NF?

STUDENT TABLE (key = StudentID)

SUBJECTS TABLE (key = Subject)

RESULTS TABLE (key = StudentID+Subject)

1

1

8

8

So it is 2NF!


Слайд 28

(key = StudentID+Subject)1188Oh oh…What?

A 3NF check

STUDENT TABLE (key = StudentID)

SUBJECTS TABLE (key = Subject)

RESULTS TABLE (key = StudentID+Subject)

1

1

8

8

Oh oh…
What?


Слайд 29

(key = StudentID+Subject)1188HouseName is dependent on both StudentID + HouseColour

A 3NF check

STUDENT TABLE (key = StudentID)

SUBJECTS TABLE (key = Subject)

RESULTS TABLE (key = StudentID+Subject)

1

1

8

8

HouseName is dependent on both StudentID + HouseColour


Слайд 30

(key = StudentID+Subject)1188Or HouseColour is dependent on both StudentID + HouseName

A 3NF check

STUDENT TABLE (key = StudentID)

SUBJECTS TABLE (key = Subject)

RESULTS TABLE (key = StudentID+Subject)

1

1

8

8

Or HouseColour is dependent on both StudentID + HouseName


Слайд 31

(key = StudentID+Subject)1188But either way,non-key fields are dependent on MORE THAN THE PRIMARY KEY (studentID)

A 3NF check

STUDENT TABLE (key = StudentID)

SUBJECTS TABLE (key = Subject)

RESULTS TABLE (key = StudentID+Subject)

1

1

8

8

But either way,
non-key fields are dependent on MORE THAN THE PRIMARY KEY (studentID)


Слайд 32

(key = StudentID+Subject)1188And 3NF says that non-key fields must depend on nothing but the key

A 3NF check

STUDENT TABLE (key = StudentID)

SUBJECTS TABLE (key = Subject)

RESULTS TABLE (key = StudentID+Subject)

1

1

8

8

And 3NF says that non-key fields must depend on nothing but the key


Слайд 33

(key = StudentID+Subject)1188WHAT DO WE DO?

A 3NF check

STUDENT TABLE (key = StudentID)

SUBJECTS TABLE (key = Subject)

RESULTS TABLE (key = StudentID+Subject)

1

1

8

8

WHAT DO WE DO?


Слайд 34

= StudentID+Subject)1188

Again, carve off the offending fields

SUBJECTS TABLE (key = Subject)

RESULTS TABLE (key = StudentID+Subject)

1

1

8

8


Слайд 35

A 3NF fix

SUBJECTS TABLE (key = Subject)

RESULTS TABLE (key = StudentID+Subject)

1

1

8

8


Слайд 36

A 3NF fix

SUBJECTS TABLE (key = Subject)

RESULTS TABLE (key = StudentID+Subject)

1

1

8

8

1

8


Слайд 37

A 3NF win!

Or…


Слайд 38

The Reveal

Before…

After…

RESULTS TABLE (key = StudentID+Subject)

1

1

8

8

1

8

SUBJECTS TABLE (key = Subject)


  • Имя файла: normalisation-describe-relational-databases-and-their-use.pptx
  • Количество просмотров: 10
  • Количество скачиваний: 0