The Relational Model

1
The Relational Model

• Chapter Two

Functional Dependency
2
Functional Dependency

• A relationship between attributes in which one
  attribute (or group of attributes) determines the
  value of another attribute in the same table
• Illustration
• The price of one cookie can determine the price
  of a box of 12 cookies

3
Determinants

• The attribute (or attributes) that we use as the
  starting point (the variable on the left side of
  the equation) is called a determinant

4
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.
• ObjectColor ? Weight

5
Functional Dependency Example

• Given different color objects
• Red objects Ball shape
• Blue objects Cube shape
• Yellow objects Cube shape
• The ObjectColor also determines shape.
• ObjectColor ? Shape

6
Functional Dependency Example

• So, ObjectColor determines both Weight and Shape.
• ObjectColor ? (Weight, Shape)

7
Functional Dependency Example

• Represent this data as a Relational Table
• OBJECT (ObjectColor, Weight, Shape)

ObjectColor Weight Shape
Red 5 Ball
Blue 3 Cube
Yellow 7 Cube
8
Candidate/Primary Keys and Functional Dependency

• By definitionA candidate key of a relation will
  functionally determine all other attributes in
  the row
• Likewise, by definitionA primary key of a
  relation will functionally determine all other
  attributes in the row

9
Primary Key and Functional Dependency Example
10
Normalization

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

11
Normalization Example

• Given ADVISER-LIST (AdviserID, AdviserName,
  Dept, Phone, Office, StudentNum, StudentName)
• Valid Relational table by the rules defined
  earlier!
• So StudentNum ? (AdviserID, AdviserName, Dept,
  Phone, Office, StudentName)
• Relation ADVISER-LIST (AdviserID, AdviserName,
  Dept, Phone, Office, StudentNum, StudentName)

12
Normalization Example

• An Advisors phone number can appear in multiple
  rows. Take out advisor information.
• Functional Dependency
• AdviserID ? (AdviserName, Dept, Phone, Office)
• This Relation is poorly formed because it has a
  functional dependency that does NOT involve the
  primary key!
• i.e. AdviserID is a determinant of a functional
  dependency but it is NOT a candidate key!

13
Normalization Principles

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

14
Normalization Process

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

15
Normalization Process

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

16
Normalization Process

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

17
Normalization Process

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