Chapter Two The Relational Model Functional Dependency Functional

Chapter Two The Relational Model Functional Dependency

Functional Dependency A relationship between attributes in which one attribute (or group of attributes) determines the value of another attribute in the same table n Illustration… n n The price of one cookie can determine the price of a box of 12 cookies (Cookie. Price, Qty) Box. Price 2

Determinants n The attribute (or attributes) that we use as the starting point (the variable on the left side of the equation) is called a determinant (Cookie. Price, Qty) Box. Price Determinant 3

Functional Dependency Example Given different color objects: Red objects – 5 pounds Blue objects – 3 pounds Yellow objects – 7 pounds If you look in a bag and see an object, the weight of the bag is determined by the color of the object. Object. Color Weight 4

Functional Dependency Example Given different color objects: Red objects – Ball shape Blue objects – Cube shape Yellow objects – Cube shape The Object. Color also determines shape. Object. Color Shape 5

Functional Dependency Example So, Object. Color determines both Weight and Shape. Object. Color (Weight, Shape) 6

Functional Dependency Example Represent this data as a Relational Table… Object. Color Weight Shape Red Blue Yellow Ball Cube 5 3 7 OBJECT (Object. Color, Weight, Shape) 7

Candidate/Primary Keys and Functional Dependency By definition… A candidate key of a relation will functionally determine all other attributes in the row n Likewise, by definition… A primary key of a relation will functionally determine all other attributes in the row n 8

Primary Key and Functional Dependency Example (Employee. ID) (Project. ID) (Emp. Last. Name, Emp. Phone) (Project. Name, Start. Date) 9

Normalization is a process of analyzing a relation to ensure that it is well-formed n More specifically, if a relation is normalized (well -formed), rows can be inserted, deleted, or modified without creating update anomalies n 10

Normalization Example Given: ADVISER-LIST (Adviser. ID, Adviser. Name, Dept, Phone, Office, Student. Num, Student. Name) Valid Relational table by the rules defined earlier! So Student. Num (Adviser. ID, Adviser. Name, Dept, Phone, Office, Student. Name) Relation: ADVISER-LIST (Adviser. ID, Adviser. Name, Dept, Phone, Office, Student. Num, Student. Name) n 11

Normalization Example n n An Advisor’s phone number can appear in multiple rows. Take out advisor information. Functional Dependency: Adviser. ID (Adviser. Name, Dept, Phone, Office) This Relation is poorly formed because it has a functional dependency that does NOT involve the primary key! i. e. Adviser. ID is a determinant of a functional dependency but it is NOT a candidate key! 12

Normalization Principles n Relational design principles for normalized relations: To be a well-formed relation, every determinant must be a candidate key n Any relation that is not well formed should be broken into two or more well-formed relations. n 13

Normalization Process Normalization is the process of examining relations and modifying them to make them well -formed. n You can categorize problems into different types called normal forms. n Any relation that has characteristics presented in Fig 2 -1 is called First Normal Form. n Others: Second, Third, Boyce-Codd, Fourth, Fifth and domain/key normal form. n 14

Normalization Process 1. 2. 3. Identify all candidate keys of the relation. Identify all functional dependencies in the relation. Exampine the determinants of the functional dependencies. If any determinant is NOT a candidate key, the relation has normalization problems. In this case… 15

Normalization Process 3 a. Place the columns of the functional dependency in a new relation of their own. 3 b. Make the determinant the functional dependency of the primary key of the new relation. 3 c. Leave a copy of the determinant as a foreign key in the original relation. 3 d. Create a referential integrity constraint between the original relation and the new relation. 16

Normalization Process 4. Repeat step 3 as many times as necessary until every determinant of every relation is a candidate key. 17