NORMALIZATION AND TRANSFORMING AN ERD INTO RELATIONS Functional

NORMALIZATION AND TRANSFORMING AN ERD INTO RELATIONS

Functional Dependence An attribute A is functionally dependent on attribute(s) B if: given a value b for B there is one and only one corresponding value a for A (at a time). b 1 b 2 b 3 a 1

Example: functional dependence All sales representatives in a given pay class have the same commission rate. Sales. Rep. Number Name Pay. Class Commission

Normal Forms A set of conditions on table structure that improves maintenance. Normalization removes processing anomalies: Update Inconsistent Data Addition Deletion

Normal Forms All attributes depend on the key, the whole key and nothing but the key. 1 NF Keys and no repeating groups 2 NF No partial dependencies 3 NF All determinants are candidate keys 4 NF No multivalued dependencies

EXAMPLE PLAYERID POSITION 01 Forward Ronaldo Joe London 02 03 04 05 01 Midfield Forward Goal Forward Midfield Ed Pete Jim Joe Ed Madrid Manchester Barcelona London Madrid Position Player_id PLAYER_NA ME Zidane Rooney Cassilas Fabregas Ronaldo COACH Coach City Player Name Partial Functional Dependencies Transitive Dependency 6

1 st Normal Form Table has a primary key Table has no repeating groups Anomalies in 1 st normal form �Inserting �We cannot enter the fact that a player information until his position is known Solved in 2 nd Normal form �Cannot Enter a Coach and City information with an assigned player and position Solved in 3 rd Normal Form �Deleting �If we Delete Player id 03 we loose information about coach “Pete”. Solved in 3 rd Normal Form �Updating �If coach Joe moves from London to Amsterdam, we have to search for all relevant tuples and update them Will be solved in 3 rd Normal Form

Second Normal Form A relation R is in 2 NF if and only if it is in 1 NF and every non-key attribute is fully dependent on the primary key Partial functional dependency: one or more non-key attributes are functionally dependent on part of the primary key PLAYERI D Player Name PLAYERID POSITION COACH Coach City 01 Forward Joe London 01 Ronaldo 02 Midfield Ed Madrid 02 Zidane 03 Forward Pete Manchester 03 Rooney 04 Goal Jim Barcelona 04 Cassilas 05 Forward Joe London 05 Fabregas 01 Midfield Ed Madrid 8

Anomalies (2 NF) �Inserting �We cannot insert Coach and City Information unless Player id and Position is identified. �Deleting �If we Delete Player id 03 we loose information about coach “Pete”. �Updating �If coach Joe moves from London to Amsterdam, we have to search for all relevant tuples and update them Will be solved in 3 rd Normal Form 9

Third Normal Form A relation is in 3 NF if and only if it is in 2 NF and every non-key attribute is non-transitively dependent on the primary key Transitive Dependency: functional dependency between two or more non-key attributes in a relation PLAYERID Player Name PLAYERID POSITION COACH 01 Forward Joe 01 Ronaldo 02 Midfield Ed 02 Zidane 03 Forward Pete 03 Rooney 04 Goal Jim 04 Cassilas 05 Fabregas 05 Forward Joe 01 Midfield Ed Coach City Joe London Ed Madrid Pete Manchester Jim Barcelona 10

Normalization 1 NF Keys & no repeating groups 2 NF 1 NF & all attributes depend on all key components 3 NF 2 NF & all determinants are candidate keys Pros: Improves maintenance for database changes Cons: Tends to slow down retrieval

Transforming E-R Diagrams into Relations Foreign Key An attribute that appears as a nonprimary key attribute in one relation and as a primary key attribute (or part of a primary key) in another relation Referential Integrity An integrity constraint specifying that the value (or existence) of an attribute in one relation depends on the value (or existence) of the same attribute in another relation 9. 12 Copyright © 2009 Pearson Education, Inc. Publishing as Prentice Hall

Transforming E-R Diagrams into Relations (Continued) It is useful to transform the conceptual data model into a set of normalized relations Steps: 1. 2. 3. 4. Represent entities Represent relationships Normalize the relations Create Tables 9. 13 Copyright © 2009 Pearson Education, Inc. Publishing as Prentice Hall

Transforming E-R Diagrams into Relations (continued) Represent Entities 1. Each regular entity is transformed into a relation The identifier of the entity type becomes the primary key of the corresponding relation The primary key must satisfy the following two conditions a. The value of the key must uniquely identify every row in the relation b. The key should be non-redundant 9. 14 Copyright © 2009 Pearson Education, Inc. Publishing as Prentice Hall

9. 15 Copyright © 2009 Pearson Education, Inc. Publishing as Prentice Hall

2. Transforming E-R Diagrams into Relations (continued) Represent Relationships Binary 1: N Relationships � Add the primary key attribute (or attributes) of the entity on the one side of the relationship as a foreign key in the relation on the right side � The one side migrates to the many side 9. 16 Copyright © 2009 Pearson Education, Inc. Publishing as Prentice Hall

Transforming E-R Diagrams into Relations (continued) 2. Represent Relationships (continued) Binary or Unary 1: 1 � Three possible options: a. Add the primary key of A as a foreign key of B b. Add the primary key of B as a foreign key of A c. Both Binary and higher M: N relationships � Create another relation and include primary keys of all relations as primary key of new relation 9. 17 Copyright © 2009 Pearson Education, Inc. Publishing as Prentice Hall

9. 18 Copyright © 2009 Pearson Education, Inc. Publishing as Prentice Hall

Transforming E-R Diagrams into Relations (continued) Unary 1: N Relationships �Relationship between instances of a single entity type �Utilize a recursive foreign key �A foreign key in a relation that references the primary key values of that same relation Unary M: N Relationships �Create a separate relation �Primary key of new relation is a composite of two attributes that both take their values from the same primary key 9. 19 Copyright © 2009 Pearson Education, Inc. Publishing as Prentice Hall

9. 20 Copyright © 2009 Pearson Education, Inc. Publishing as Prentice Hall