1 / 16

Normalization Functional dependencies 1.-3. NF + BCNF

Learn about functional dependencies and normalization in database design, including terminology, types of dependencies, normalization process, keys, and the benefits of normalization.

ygrego
Télécharger la présentation

Normalization Functional dependencies 1.-3. NF + BCNF

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. NormalizationFunctional dependencies1.-3. NF + BCNF Functional dependencies and normalization

  2. Terminology • Functional dependency • Dependency between attributes in a single relation • Normal form • Rule, which a relation must obey. Higher normal form means more “quality” in the relation. • Normalization • Process that leads the relation to a certain normal form. Functional dependencies and normalization

  3. Bottom-up Starting point: A set of attributes Dependencies among the attributes. Hard to survey all the attributes. Top-down Starting point: Attributes “grouped” into relations from ER-diagram (analysis). 2 types of database-design Functional dependencies and normalization

  4. Normalization • Can be used in 2 ways: • Quality check of relations (from ER) • Top down • Normal procedure • Process, starting with unordered data • Bottom up • Not recommended • Used in many book (in the normalization chapters) Functional dependencies and normalization

  5. Goal of normalization • Reduce redundancy • Redundant data: Data that occurs more than once (or can be inferred from other data) • Price_incl_postage = price + postage • Reduce the number of NULLs • NULLs fill the database without giving any information. Functional dependencies and normalization

  6. Keys • Key • Minimal set of attributes that uniquely defines any row. • Primary key • If the set of attributes consists of ONE attribute • Composite primary key • If the set of attributes consists of more that one attribute. • Prime attribute • Attribute in some candidate key • Secondary key • If we have more than one key. One of the candidates is chosen as the primary key. • Surrogate key (aka. ID) • Invented (not in the real world) attribute. • Advice: Always use surrogate keys. Never use real world data in a key • Real world data might change in the future. Functional dependencies and normalization

  7. Functional dependency • CPR → first name • 1 CPR leads to exactly 1 first name • CPR → phone number • Probably not true: Many people have more than 1 phone number • Must be true in every relational state • in the past and in the future • A relational state may • Exclude certain functional dependencies • Never show any functional dependencies Functional dependencies and normalization

  8. Dependency diagram • A dependency of a relation • All attribute of the relation • The key (bold) • The dependencies (arrows) Functional dependencies and normalization

  9. 1. normal form • A relation is on 1NF, if • Primary key defined • All attributes are atomic • Forbids • Multi valued attributes • Create more tuples in the relation. • Create a new relation with foreign keys to this relation • Composite attributes • Create an attribute for each sub-attribute. Functional dependencies and normalization

  10. 2. normal form • 1NF + no partial dependency • Partial dependency • An attribute is dependent of a PART of the key. • Only interesting if the key is composite. • Example • StudentCourse: • studentID, courseID grade • courseID  courseName • What to do? Divide the relation • Partial dependent attributes must have their own relation. Functional dependencies and normalization

  11. 3. normal form • 2NF + no transitive dependency • Transitive dependency • Person: CPR  post and code  city • What to do? Divide the relation • Transitive dependencies must go into another relation Functional dependencies and normalization

  12. The Relational oath • "I promise to use the key, the whole key, and nothing but the key, so help me Codd." • Codd defined the relational model - and invented the normal forms [1970]. Functional dependencies and normalization

  13. BCNF • Boyce-Codd Normal form • Named after its inventors: Boyce and Codd • Stronger than 3NF, but less strong than 4NF • A relation is in BCNF, if • For every functional dependency A → B • A must be a super key in the relation Functional dependencies and normalization

  14. BCNF “cut-through” • Find all the functional dependencies • Check that the left hand sides of the functional dependencies are (super)keys • If they are not, then split the relation into 2 sub-relations • No need to go through 1NF, 2NF, and 3NF Functional dependencies and normalization

  15. Strengths of the normal forms Functional dependencies and normalization

  16. Denormalization • Normalization produces many (good) tables. • Many joins: Takes extra time at “select” • Sometimes we prefer fewer tables with controlled redundancy. Functional dependencies and normalization

More Related