Normalization Functional dependencies 1 3 NF BCNF Functional

Normalization Functional dependencies 1. -3. NF + BCNF Functional dependencies and normalization 1

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 2

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

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 4

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 5

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 6

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 7

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

1. normal form • A relation is on 1 NF, 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 9

2. normal form • 1 NF + no partial dependency • Partial dependency – An attribute is dependent of a PART of the key. – Only interesting if the key is composite. • Example – Student. Course: • student. ID, course. ID grade • course. ID course. Name • What to do? Divide the relation – Partial dependent attributes must have their own relation. Functional dependencies and normalization 10

3. normal form • 2 NF + 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 11

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 12

BCNF • Boyce-Codd Normal form – Named after its inventors: Boyce and Codd – Stronger than 3 NF, but less strong than 4 NF • 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 13

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 subrelations • No need to go through 1 NF, 2 NF, and 3 NF Functional dependencies and normalization 14

Strengths of the normal forms Functional dependencies and normalization 15

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 16