Database Design Concepts

1. Database Design Concepts Term 2 Lecture 5 Introduction to Normalisation

2. Objectives • During this lecture we will explain the terms • Column order significance • Row order significance • Repeating groups • Redundant data

3. Normalisation • This is a process to enable us to design a database that works well • This means the finished design should • Give us non confusing information • Allows us to use it efficiently

4. What makes a good Database design • It does not matter what order the rows of the table are in. i.e. no row order significance • It does not matter what order the columns are displayed, i.e. no column order significance • No repeating groups (a field only contains one value). • It does not contain redundant duplicate data

5. Row order significance Example……

6. If we move the rows into another order Whose E-mail is this?

7. Solve this by making each row complete

8. Column order significanceExample……

9. Solution? • Same as before • If we move the order of the columns around we don’t know what the second number is. • We solve this by naming Each column then it does not matter which order we display the columns

10. No repeating groupsin other words only one value in a cell

11. We solve this by expanding the table Doing this actually saves us space because If I left it as before I would need to set the field friend to contain the largest ever likely- Most records would not be this size so would have a lot of empty space

12. Redundancy • This means data that we don’t need it. In other words can we work it out from the information we have • Example

13. ExampleWhich is redundant data and which is not ?

14. We can work out the values in the blanks spaces – so that data was redundant

15. We deal with this by splitting the table up

16. We do this because it helps if we need to change information, so it is stored only once in the database. • For example if the price of item S23 changed in the first version we would need to change it several times- and we would not know how many • If we forgot to change one of the prices we might charge customers the wrong amount • Or I might get two values- how do I know which is the right one? • In the split version I know I need to change one value only.

17. Conclusion • We need to produce a database with no redundant data, no column or row order significance and without repeating groups. • The examples we have been using allow us to work this out from looking at examples of tables and data. • What do we do if we have no example tables and/or Data. • We use normalisation • Tables derived from ERDs are also normalised

18. References • Andy Oppel Databases Demystified • David Howe Data Analysis For Database Design