Functional Dependencies and Normalization: Building Blocks for Analyzing Data Redundancy, Slides of Database Management Systems (DBMS)

Download Functional Dependencies and Normalization: Building Blocks for Analyzing Data Redundancy and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Functional Dependencies

Definitions

• Functional dependencies

are the building blocks that

enable the analysis of data

redundancy and the

elimination of anomalies

caused by data redundancy

through the process of

normalization.

• Normalization is a

technique that facilitates

systematic validation of

participation of attributes in

a relation schema from a

perspective of data

redundancy.

Notation

• A (the left-side of the FD) is the determinant

and B (the right-side of the FD) is the

dependant.

• If the determinant or the dependant are

composite values then the atomic values are

enclosed in braces.

{Store, Product} -> Quantity

Example

• Stock table. Normalized or unnormalized?

 Unnormalized!

• Let’s take a closer look.

• Is Quantity redundant?

• How about Price?

• What about Location and Discount?

Example

• We want to add a blender and its Price to our stock. We can’t

unless we know the store where they’ll be stocked.

 Insertion anomaly!

 If we close store 17 we have to change multiple lines

and we loose the info on the price of the vacuum

cleaner.

 Deletion anomaly!

 If we want to change the Location of store 11 we have

to change all rows were store 11 appears.

 Modification anomaly!

How do we fix it?

Store Product Quantity Discount 15 Refrigerator 120 5% 15 Dishwasher 150 5% 13 Dishwasher 180 10% 14 Refrigerator 150 5% 14 Television 280 10% 14 Humidifier 30 17 Television 10 17 Vac Cleaner 150 5% 17 Dishwasher 150 5% 11 Computer 180 10% 11 Refrigerator 120 5% 11 Lawn Mower

• We “split” the data into separate tables to eliminate

redundancies.

Inventory

New tables (cont.)

• This new system is less efficient when

retrieving data. That’s the price paid for

eliminating the modification anomalies.

• We draw the line between efficiency and

redundancy.

• Discount is stored redundantly. This is called

controlled redundancy and is done for

efficiency of data retrieval.

Inference rules for FDs

• The set of functional dependencies explicitly

specified on a relational schema is referred a

F.

• Given F it is possible to deduce all other FD’s

in R that are not explicitly defined.

• Closure is the set of all possible functional

dependencies that hold in R. It is also referred

as F +^.

Armstrong’s Axioms (cont.)

Rule Definition

Reflexivity If Y is a subset of X [i.e., if X is (A,B,C,D) and Y is (A,C)], then X->Y. Example: {Store, Product} -> Store

Augmentation If X->Y, then {X,Z} -> {Y,Z}; also {X,Z}->Y

Example: If Store->Location, then {Store,Product} -> {Location,Product} and {Store,Product} ->Location Transitivity If X->Y, and Y->Z, then X->Z Example: If {Store, Product} ->Quantity and Quantity-> Discount, then {Store, Product} -> Discount

Armstrong’s Axioms (cont.)

• Four more rules can be derived from the previous

three.

Rule Definition

Decomposition If X->{Y,Z}, then X->Y and X->Z

Union If X->Y, and X->Z, then X->{Y,Z}

Composition If X->Y, and Z->W, then {X,Z} -> {Y,W}

Pseudo- transitivity

If X->Y, and {Y,W} ->Z, then {X,W} ->Z

Minimal Cover for a set of FDs (cont.)

• F can be its own minimal cover also known as canonical

cover.

• There can be several minimal covers of F.
• Formally G (^) c is the minimal cover of F if:
  • Gc ≡ F
  • The dependant (RHS) in every FD in Gc is a singleton attribute.

This is called standard or canonical form.

• No FD in Gc is redundant. In other words, if any FD in Gc is

discarded, then Gc would be no longer equivalent to F.

• The determinant (LHS) if every FD in Gc is irreducible. In other

words, if any attribute is discarded from the determinant of any

FD in Gc , then Gc would be no longer equivalent to F.

Algorithm to compute the minimal cover

1. Set G to F.
2. Convert all FDs into standard (canonical) form.
3. Remove all redundant attributes from the

determinant (LHS) of the FDs from G

4. Remove all redundant FDs from G.

Two Notes:

• This algorithm might produce different results

based on the order of candidates removal.

• Steps 3 and 4 aren’t interchangeable.

Examples (cont.)

• Based on fd4b, A in fd2 is redundant. We

remove it. Now we remove fd4b because is

identical to fd2.

• We are left with the minimal cover of F ( Gc):

fd1: A->B fd2: B->C

fd3: C->A

Examples (cont.)

• Consider the set of attributes {Student,Advisor,Subject,Grade}

and a set of FDs F:

fd1:{Student,Advisor}->{Grade,Subject}

fd2: Advisor->Subject

fd3: {Student, Subject}->{Grade,Advisor}

• Rewrite in standard form:

fd1a: {Student,Advisor}->Grade

fd1b: {Student,Advisor}->Subject

fd2: Advisor->Subject

fd3a: {Student,Subject}->Grade

fd3b: {{Student,Subject}->Advisor