Understanding Functional Dependencies and Lossless Decompositions in Database Design - Pro, Study notes of Principles of Database Management

Download Understanding Functional Dependencies and Lossless Decompositions in Database Design - Pro and more Study notes Principles of Database Management in PDF only on Docsity!

CMSC 424 – Database design

Lecture 10

Normalization

Mihai Pop

Midterm...

• Graded A & B – disappointing

• Outline of what I expected for A & B

Combine Schemas?

●

Suppose we combine borrower and loan to get

bor_loan = ( customer_id , loan_number , amount )

• Result is possible repetition of information (L-100 in

example below)

A Lossy Decomposition

FDs continued

• trivial dependencies:
• closure
  • need all FDs
  • (^) some logically implied by others e.g. if A → B & B → C then A → C is implied
• given F = set of FDs, find F+ (the closure) of all logically implied by F
• Why?
• See: http://www.schneier.com/blog/archives/2007/12/anonymity_and_t_2.html
• Given a user’s public IMDb ratings, which the user posted voluntarily to selectively reveal some of his (or her; but we’ll use the male pronoun without loss of generality) movie likes and dislikes, we discover all the ratings that he entered privately into the Netflix system, presumably expecting that they will remain private.

   if ⊆

FD formalism

Amstrong’s axioms

• reflexivity:
• (^) augmentation:
• (^) transitivity: More rules (can be inferred from Amstrong's axioms)
• (^) union rule:
• (^) decomposition rule:
• (^) pseudotransitivity rule:

if ⊆ then    (trivial FD)

if    then     

if   ∧   then   

if   ∧   then    

if     then    and   

if   ∧    then   

Closure of Attribute Sets

• (^) useful to find if a set of attributes is a superkey
• (^) the closure α+ of a set of attributes α under F is the set of all attributes that are functionally determined by α
• (^) there is an algorithm that computes the closure Example: Note that since G is not on any right hand side, no subset of the attributes can be a superkey unless it contains G for there is no FD to generate it. R(A,B,C,G,H,I) F={ A → B, A → C, CG → H, CG → I, B → H } Algorithm to Compute (AG)+ start with result=(AG) A → B expands result=(AGB) A → C expands result=(AGBC) CG → H “-” result=(AGBCH) CG → I “-” result=(AGBCHI) B → H no more expansion

Uses of Attribute Closure

There are several uses of the attribute closure algorithm:

• Testing for superkey:
  • (^) To test if α is a superkey, we compute α+,^ and check if α+ contains all attributes of R.
• (^) Testing functional dependencies
  • (^) To check if a functional dependency α → β holds (or, in other words, is in F+ ), just check if β ⊆ α+.
  • (^) That is, we compute α+ by using attribute closure, and then check if it contains β.
  • (^) Is a simple and cheap test, and very useful

Dependency Preservation

In a decomposition

• (^) dependencies are preserved if
  • (^) we do not need to join in order to enforce FDs
  • (^) all FDs remain intra-relational and do not become inter-relational constraints
• (^) to check if a decomposition is dependency preserving
  • (^) we need to examine all FDs in F+
• (^) there is an algorithm for testing dependency preservation
  • (^) requires the computation of F+

The Normal Forms

• 1NF: every attribute has an atomic value (not a set value)
• 2NF: we will not be concerned in this course
• (^) 3NF: if for each FD X→Y either
  • it is trivial or
  • X is a superkey
  • Y-X is a proper subset of a candidate key
• (^) BCNF: if for each FD X→Y either
  • it is trivial or
  • X is a superkey
• 4NF,…: we are not concerned in this course. 1NF 2NF 3NF BCNF 4NF,..