1 / 14

DATABASE PROGRAMMING Lecture on 04 – 05 – 2005

DATABASE PROGRAMMING Lecture on 04 – 05 – 2005. PREVIOUS LECTURE. 1. Introduction to Database Design - 6 Steps Database Design 2. Normalization. - 1NF, 2NF and 3NF and example of normalization. - BCNF, 4NF and 5NF. NEWS. 1. All lecture materials are available on

katima
Download Presentation

DATABASE PROGRAMMING Lecture on 04 – 05 – 2005

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. DATABASE PROGRAMMING Lecture on 04 – 05 – 2005

  2. PREVIOUS LECTURE 1. Introduction to Database Design - 6 Steps Database Design 2. Normalization. - 1NF, 2NF and 3NF and example of normalization. - BCNF, 4NF and 5NF

  3. NEWS 1. All lecture materials are available on http://192.168.1.1/teachers/adinugro/dp 2. Next Lecture will be QUIZ (06/05/2005) - About Normalization - OPEN BOOK - DO NOT CHEAT!!!!!!!!!! or You will get 0. - Exercise for exam, as the question is a typical exam questions, - OK?

  4. 1NF -> eliminate repeating attributes 1. Identify repeating attributes. 2. All the key attributes are defined. 3. All attributes are dependent on the primary key. 2NF 1. It's in first normal form (1NF) 2. It includes no partial dependencies (where an attribute is dependent on only a part of a primary key). 3NF 1. It's in second normal form (2NF) 2. It contains no transitive dependencies (where a non-key attribute is dependent on another non-key attribute REVIEW OF NORMAL FORMS

  5. 1st NORMAL FORM

  6. 2nd NORMAL FORM Employee Table

  7. 2nd NORMAL FORM Project Table Employee Project Table

  8. 3rd NORMAL FORM Employee Table Rate Table

  9. 3rd NORMAL FORM Project Table Employee Project Table

  10. DENORMALIZATION Normalization is: • Storing one fact in one place • Storing related facts about single entity together • every column of each entity refers non-transitively to only the unique identifier for that entity. Denormalization: is the reversal process of Normalization, which is a process to store a fact in numerous places.

  11. DENORMALIZATION(2) Sometimes we need to do denormalization to accomplish quick retrieval capability for data stored in relational database. Of course we should (whenever possible) normalize our design to provide optimum environment . However, in real world, denormalization is necessary. Some issues need to be considered before denormalization: • can the system achieve acceptable performance without denormalizating? • will the performance of the system after denormalizating still unacceptable? • will the system be less reliable due to denormalization? If there is one 'yes' answer, we should avoid denormalization!

  12. DENORMALIZATION (EXAMPLE) In order to justify denormalization we need to have a business reason for the alteration form 3rd NF.  This example design is for a mail order company that has 120,000,000 customers to whom they must send catalogs.  The top design is in 3rd normal form because the attributes for State, City, and Country are codependent on the PostalCode field.  To fix that we created the PostalCode entity and related it back to the Customer records on the PostalCode foreign key in the Customer table. However, during system testing it was discovered that the time required to produce 120,000,000 mailing labels through a two-table join was much longer than if the labels could be produced from a single table.  The table was then denormalized by reintroducing the City, State, and Country attributes to the Customer table. After doing this it would be heavily documented including the normal form that is violated, the reason for the violation, and the consequences of the violation.  Any violation of a fully normalized design carries with it potential problems in the area of updates.

  13. Craig, S. Mullin, Denormalization, http://www.objectarchitects.de/ObjectArchitects/orpatterns/Performance/Denormalization/CraigMullinsGuidelines/i001fe02.htm Ian Gilfillan, Database Normalization, http://www.databasejournal.com/sqletc/article.php/1428511 Mike Hillyer, An Introduction to Database Normalization, http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html David Faour, Database Normalization, http://www.serverwatch.com/tutorials/article.php/1549781 REFERENCES

More Related