- Главная
- Разное
- Дизайн
- Бизнес и предпринимательство
- Аналитика
- Образование
- Развлечения
- Красота и здоровье
- Финансы
- Государство
- Путешествия
- Спорт
- Недвижимость
- Армия
- Графика
- Культурология
- Еда и кулинария
- Лингвистика
- Английский язык
- Астрономия
- Алгебра
- Биология
- География
- Детские презентации
- Информатика
- История
- Литература
- Маркетинг
- Математика
- Медицина
- Менеджмент
- Музыка
- МХК
- Немецкий язык
- ОБЖ
- Обществознание
- Окружающий мир
- Педагогика
- Русский язык
- Технология
- Физика
- Философия
- Химия
- Шаблоны, картинки для презентаций
- Экология
- Экономика
- Юриспруденция
Normalisation. Describe relational databases and their use презентация
Содержание
- 1. Normalisation. Describe relational databases and their use
- 2. Success criteria know what is normalization know
- 3. Database normalization is the process of removing
- 4. 1NF - Atomic Data Test If a
- 6. Take the following table. StudentID is the primary key. Is it 1NF?
- 7. No. There are repeating groups (subject, subjectcost, grade) How can you make it 1NF?
- 8. Create new rows so each cell contains
- 9. No – the studentID no longer uniquely
- 10. So. We now have 1NF. Is it 2NF?
- 11. Studentname and address are dependent on studentID
- 12. 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. Make new tables Make a new table
- 15. Step 1 STUDENT TABLE (key = StudentID)
- 16. Step 2 STUDENT TABLE (key = StudentID) SUBJECTS TABLE (key = Subject)
- 17. Step 3 STUDENT TABLE (key = StudentID)
- 18. Step 3 STUDENT TABLE (key = StudentID)
- 19. Step 4 - relationships STUDENT TABLE (key
- 20. Step 4 - cardinality STUDENT TABLE (key
- 21. Step 4 - cardinality STUDENT TABLE (key
- 22. Step 4 - cardinality STUDENT TABLE (key
- 23. Step 4 - cardinality STUDENT TABLE (key
- 24. A 2NF check STUDENT TABLE (key =
- 25. A 2NF check STUDENT TABLE (key =
- 26. A 2NF check STUDENT TABLE (key =
- 27. But is it 3NF? STUDENT TABLE (key
- 28. A 3NF check STUDENT TABLE (key =
- 29. A 3NF check STUDENT TABLE (key =
- 30. A 3NF check STUDENT TABLE (key =
- 31. A 3NF check STUDENT TABLE (key =
- 32. A 3NF check STUDENT TABLE (key =
- 33. A 3NF check STUDENT TABLE (key =
- 34. Again, carve off the offending fields SUBJECTS
- 35. A 3NF fix SUBJECTS TABLE (key =
- 36. A 3NF fix SUBJECTS TABLE (key =
- 37. A 3NF win! Or…
- 38. The Reveal Before… After… RESULTS TABLE (key
Слайд 2Success criteria
know what is normalization
know the purpose of the 3 forms
can create 3 forms of normalization for a table
Слайд 3Database normalization is the process of removing redundant data from your
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.
Слайд 41NF - Atomic Data Test
If a table has a 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.)
Слайд 8Create new rows so each cell contains only one value
But now
Слайд 9No – the studentID no longer uniquely identifies each row
You now
So the new key is StudentID and Subject.
Слайд 11Studentname and address are dependent on studentID (which is part of
But they are not dependent on Subject (the other part of the key)
Слайд 14Make new tables
Make a new table for each primary key field
Give
Move columns from the original table to the new table that matches their primary key…
Слайд 17Step 3
STUDENT TABLE (key = StudentID)
SUBJECTS TABLE (key = Subject)
RESULTS TABLE
Слайд 18Step 3
STUDENT TABLE (key = StudentID)
SUBJECTS TABLE (key = Subject)
RESULTS TABLE
Слайд 19Step 4 - relationships
STUDENT TABLE (key = StudentID)
SUBJECTS TABLE (key =
RESULTS TABLE (key = StudentID+Subject)
Слайд 20Step 4 - cardinality
STUDENT TABLE (key = StudentID)
SUBJECTS TABLE (key =
RESULTS TABLE (key = StudentID+Subject)
1
Each student can only appear ONCE in the student table
Слайд 21Step 4 - cardinality
STUDENT TABLE (key = StudentID)
SUBJECTS TABLE (key =
RESULTS TABLE (key = StudentID+Subject)
1
1
Each subject can only appear ONCE in the subjects table
Слайд 22Step 4 - cardinality
STUDENT TABLE (key = StudentID)
SUBJECTS TABLE (key =
RESULTS TABLE (key = StudentID+Subject)
1
1
8
A subject can be listed MANY times in the results table (for different students)
Слайд 23Step 4 - cardinality
STUDENT TABLE (key = StudentID)
SUBJECTS TABLE (key =
RESULTS TABLE (key = StudentID+Subject)
1
1
8
8
A student can be listed MANY times in the results table (for different subjects)
Слайд 24A 2NF check
STUDENT TABLE (key = StudentID)
SUBJECTS TABLE (key = Subject)
RESULTS
1
1
8
8
SubjectCost is only dependent on the primary key,
Subject
Слайд 25A 2NF check
STUDENT TABLE (key = StudentID)
SUBJECTS TABLE (key = Subject)
RESULTS
1
1
8
8
Grade is only dependent on the primary key (studentID + subject)
Слайд 26A 2NF check
STUDENT TABLE (key = StudentID)
SUBJECTS TABLE (key = Subject)
RESULTS
1
1
8
8
Name, Address are only dependent on the primary key
(StudentID)
Слайд 27But is it 3NF?
STUDENT TABLE (key = StudentID)
SUBJECTS TABLE (key =
RESULTS TABLE (key = StudentID+Subject)
1
1
8
8
So it is 2NF!
Слайд 28A 3NF check
STUDENT TABLE (key = StudentID)
SUBJECTS TABLE (key = Subject)
RESULTS
1
1
8
8
Oh oh…
What?
Слайд 29A 3NF check
STUDENT TABLE (key = StudentID)
SUBJECTS TABLE (key = Subject)
RESULTS
1
1
8
8
HouseName is dependent on both StudentID + HouseColour
Слайд 30A 3NF check
STUDENT TABLE (key = StudentID)
SUBJECTS TABLE (key = Subject)
RESULTS
1
1
8
8
Or HouseColour is dependent on both StudentID + HouseName
Слайд 31A 3NF check
STUDENT TABLE (key = StudentID)
SUBJECTS TABLE (key = Subject)
RESULTS
1
1
8
8
But either way,
non-key fields are dependent on MORE THAN THE PRIMARY KEY (studentID)
Слайд 32A 3NF check
STUDENT TABLE (key = StudentID)
SUBJECTS TABLE (key = Subject)
RESULTS
1
1
8
8
And 3NF says that non-key fields must depend on nothing but the key
Слайд 33A 3NF check
STUDENT TABLE (key = StudentID)
SUBJECTS TABLE (key = Subject)
RESULTS
1
1
8
8
WHAT DO WE DO?
Слайд 34Again, carve off the offending fields
SUBJECTS TABLE (key = Subject)
RESULTS TABLE
1
1
8
8
Слайд 38The Reveal
Before…
After…
RESULTS TABLE (key = StudentID+Subject)
1
1
8
8
1
8
SUBJECTS TABLE (key = Subject)
