1 / 18

Normal Forms for Tables

Normal Forms for Tables. The concept of normal forms: established in the early period of development of DB’s means of detecting and correcting bad table design included in most DB texts as a foundation concept (Bill’s view) superceded by principles of OO design

olisa
Download Presentation

Normal Forms for Tables

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. Normal Forms for Tables

  2. The concept of normal forms: • established in the early period of development of DB’s • means of detecting and correcting bad table design • included in most DB texts as a foundation concept • (Bill’s view) superceded by principles of OO design • e.g. don’t design bad objects => no need to correct tables

  3. First normal form: attributes must be atomic ( not sets of values) eg. Employee( name, {dependent} ) 

  4. First normal form: attributes must be atomic ( not sets of values) eg. Employee( name, {dependent} )  ?why: usually implemented by a string of names: “Bill, Bob, Mary” which does not support easy queries.

  5. First normal form: attributes must be atomic ( not sets of values) eg. Employee( name, {dependent} )  ?why: usually implemented by a string of names: “Bill, Bob, Mary” which does not support easy queries. In OO design: it violates UML aggregation model.

  6. Second & third normal forms based on concept of functional dependency: A -> B means field A acts as a key for field B

  7. Second & third normal forms based on concept of functional dependency: A -> B means field A acts as a key for field B Table: A B C a e f b g w a q f c e f

  8. Second & third normal forms based on concept of functional dependency: A -> B means field A acts as a key for field B Table: A B C a e f b g w a q f c e f ? A -> B ? A,B -> C ? A -> C ? B -> C ? B -> A

  9. Second normal form: no partial dependency A,B -> C,D,E B -> D  ? why: can split tables into separate tables; e.g EmpProj: SSN, Pnum, name, hours, Plocation Emp: SSN, name EmpWork: SSN, Pnum, hours Proj: Pnum, Plocation

  10. Second normal form: no partial dependency A,B -> C,D,E B -> D  ? why: can split tables into separate tables; e.g EmpProj: SSN, Pnum, name, hours, Plocation Emp: SSN, name EmpWork: SSN, Pnum, hours Proj: Pnum, Plocation OO view: violating E-R modeling

  11. Third normal form: no transitive dependency A -> B, B -> C,D,E  ? why: can split tables into separate tables; e.g StudentActivity: SID, Activity, Fee but SID -> Activity, Activity -> Fee to: StudentActivity: SID, Activity Activitiy: Activity, Fee

  12. Third normal form: no transitive dependency A -> B, B -> C,D,E  ? why: can split tables into separate tables; e.g StudentActivity: SID, Activity, Fee but SID -> Activity, Activity -> Fee to: StudentActivity: SID, Activity Activitiy: Activity, Fee Object-oriented view: not put 2 entities in same class !!!

  13. other forms: Boyce-Codd Normal form, Fourth Normal form.

  14. Some books show: Domain-key normal form: it is the “universal” good design equivalent to “do good object model” all constraints depend only on the key and the (domain) types of the attributes.

  15. References: Riccardi, (Blue book), Chapter 5. David Kroenke, Database Processing, Prentice Hall, 2004, Chap. , Chap. 4. R. Fagin, “A normal form for relational databases that is based on domains and keys”, Trans. Database Systems, Sept. 1981.

  16. ???

More Related