150 likes | 362 Views
Normalization Normalization intro First normal form (1NF) Second normal form ( 2 NF) Third normal form (3NF) Denormalization Beyond normalization Can be skipped in chapter 8 Steen Jensen, autumn 2013. Normalization - introduction.
E N D
Normalization • Normalization intro • First normal form (1NF) • Second normal form (2NF) • Third normal form (3NF) • Denormalization • Beyond normalization • Can be skipped in chapter 8 • Steen Jensen, autumn 2013
Normalization - introduction • To ensure that the database is constructed appropriately (consistent design), normalization rules are used • 6 normalization rules exist (normal forms) – normally only the 3 first are used, which have a practicalimportance • Normalization first originated along with relational databases – E. F. Codd (IBM) in 1969
First normal form (1NF) – example 1 • All attributes must be dependent of the primary key • No repeating fields or composite fields • Owner1-4 are repeating and are not dependent of the primary • Solution: Owner1-4 are deleted, and a new table (entity) is made (CarUser)
Second normal form (2NF) • By compound primary keys all other attributes must be equally dependent of both parts of the key • ManufactureDate is only dependent of Registration • Solution: ManufactureDate is moved to Car
Third normal form (3NF) – example 1 • No attribute must be more dependent of other attributes than the primary key • Derived data is not allowed
Third normal form (3NF) – example 2 • Derived data is not allowed – just delete the column (attribute)
Denormalization • Sometimes denormalization can actually be a good idea: • If including extra columns (attributes) can dramatically reduce response time for queries • When storing historical data (store data in fewer tables) • The fewer tables that have to be joined, the easier for users to do their own reports
Beyond normalization • Even though normalization is important, it isn’t everything! • Keep it simple – try avoiding complex solutions • Choose the right data types (“wasted space is wasted speed), e.g. To store months (1-12) a tinyint would be ideal (not an int) • “Are we going to need that information later?” – if in doubt, just keep it!
Can be skipped in chapter 8 • Page 270 – 290 + 293bot – 302top: • Other normal forms (beyond third form: academic) • Understanding relationships: already covered • Diagramming databases + Drawing up a quick example: we use Dia (or similar)
Exercise in normalization - 1 Take a look at exercise 1 page 302 in SQL Server Try to make your solution without looking at the answer at the back of the book When you have finished, compare your solution with the answer on page 788bot – 789top
Exercise in normalization - 2 Take a look at your relational model for Amazon Try to run your model through the three normal forms Is it ok, or do you need to change something?