1 / 12

Normalization Normalization intro First normal form (1NF) Second normal form ( 2 NF)

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.

benson
Télécharger la présentation

Normalization Normalization intro First normal form (1NF) Second normal form ( 2 NF)

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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

  2. 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

  3. 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)

  4. First normal form (1NF) – example 2

  5. 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

  6. Third normal form (3NF) – example 1 • No attribute must be more dependent of other attributes than the primary key • Derived data is not allowed

  7. Third normal form (3NF) – example 2 • Derived data is not allowed – just delete the column (attribute)

  8. 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

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

  10. 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)

  11. 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

  12. 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?

More Related