Functional Dependencies and Determinants

1
Functional Dependencies and Determinants
2
Functional Dependencies

• is a relationship between attributes in a
  database
• Functional dependencies and keys are the basis of
  normalization --- the process of grouping
  attributes into well-structured relations

3
Functional Dependencies

• An attribute, B, is functionally dependent on
  another attribute, A (or possibly a collection of
  attributes), if a value for A determines a value
  for B at any one time.

4
Dependency Diagram

• is a graphical tool for representing functional
  dependencies.

5
Functional Dependencies to Dependency Diagram
6
Rules of Functional Dependencies

• Augmentation Rule
• If X -gt Y, then XY -gt Y. That is, if attribute Y
  is functionally dependent on attribute X, then it
  is also functionally dependent on attribute X in
  combination with one (or more) additional
  attributes.
• Ex
• Student_Number -gt Student_Name
• Student_Number, Course_Number -gt Student_Name

7
Rules of Functional Dependencies

• Transitivity Rule
• If X -gt Y and Y -gt Z then X -gt Z.
• Example
• If Student_Number -gt Major and Major
  -gt Adviser then Student_Number -gt
  Adviser

8
Rules of Functional Dependencies

• Pseudo Transitivity Rule
• If X -gt Y and YZ -gt W then XZ -gt W.
• Example
• If Student_Number -gt Major and
  Major, Class -gt Adviser then Student_Number,
  Class -gt Adviser

9
Keys

• Definition
• Attribute A (or a collection of attributes) is
  the primary key for a relation, R, if
• All attributes in R are functionally dependent on
  A.
• No subcollection of the attributes of A (assuming
  A is a collection of attributes and not just a
  single attribute) also has property 1.

10
Candidate Key

• is a collection of attributes that has the same
  properties as a primary key.
• technically, the definition given for primary
  key really defines candidate key. From all the
  candidate keys one is chosen to be the primary
  key. The candidate keys that are not chosen to be
  the primary key are often referred to as
  alternate keys.

11
Example for Candidate Keys
EMPLOYEE (EMP, GSIS, TIN, Name, Address, Salary)
The candidate keys are EMP, GSIS, and TIN
because any one of these attributes can uniquely
identify and EMPLOYEE record (i.e. EMP or GSIS
or Tin singly determines all the other
attributes in the relation).
Since EMP was arbitrarily chosen as the primary
key (for a reason, of course) then GSIS and TIN
are said to be alternate keys.
12
Partial Dependencies

• Definition
• An attribute or group of attributes A is said to
  fully determine an attribute or group of
  attributes B (or B is fully functionally
  dependent on A) if A determines B and no proper
  subset of A determines B.
• The concept of partial dependency is concerned
  with relations with composite determinants.

13
Example of dependency diagram
14
relation
R(BRANCH, CUSTOMER, BRANCH_ADDRESS,
CREDIT_CODE, CREDIT_LIMIT)
Composite primary key BRANCH, CUSTOMER. In
which case, BRANCH_ADDRESS is said to be
partially dependent on the primary key since
BRANCH( which is a proper subset of the primary
key) also determines BRANCH_ADDRESS
15
Transitive Dependencies

• Definition
• If attribute A determines attribute Band B
  determines attribute C then attribute A must also
  determine C. Attribute A is said to transitively
  determine C (or C is transitively dependent on
  A).

16
Example of dependency diagram
17
relation
We say that BRANCH, CUSTOMER determines
CREDIT_CODE and CREDIT_LIMIT. However, the
dependency between BRANCH, CUSTOMER and
CREDIT_LIMIT is transitive since it is only
CREDIT_CODE that fully determines CREDIT_LIMIT.