Normalization Using Multivalued Dependencies

1
Normalization Using Multivalued Dependencies

• CS 157A
• Yan Qing Lei
• Chapter 7

2

• Suppose that in our banking example, we had an
  alternative design including the schema
• BC-schema(loan, cname, street, ccity)
• We can see this is not BCNF, as the
  functional dependency
• cname? street ccity holds on this schema, and
  cname is not a superkey.

3

• If we have customers who have several addresses,
  though, then we no longer wish to enforce this
  functional dependency, and the schema is in BCNF.
  
• However, we now have the repetition of
  information problem. For each address, we must
  repeat the loan numbers for a customer, and vice
  versa.

4

• Multivalued Dependencies
• Functional dependencies rule out certain tuples
  from appearing in a relation. If A?B, then we
  cannot have two tuples with the same A value but
  different B values.
• 2. Multivalued dependencies do not rule out the
  existence of certain tuples. Instead, they
  require that other tuples of a certain form be
  present in the relation.

5

• 3.Let R be a relation schema, and let a?R
  and b?R .
• The multivalued dependency a?b holds on R if
  in any legal relation r(R), for all pairs of
  tuples t1 and t2 in r such that t1a t2a,
  there exist tuples t3 and t3 in r such that
• t1a t2a t3a t4a
• t3b t1b
• t3R-b t2R-b
• t4b t2b
• t4R-b t1R-b

6

• 4. Figure 1 shows a tabular representation of
  this. It looks horrendously complicated, but is
  really rather simple.
• A simple example is a table with the schema
  (name, address, car), as shown in Figure 2.

7

• (name, address, car) where name?address and
  name?car

8

• Intuitively, a?b says that the relation-
• ship between a and b is independent of the
  relationship between a and R-b.
• If the multivalued dependency a?b is satisfied by
  all relations on schema R, then we say it is a
  trivial multivalued dependency on schema R.
• Thus a?b is trivial if b?a or bUaR.

9
5.Look at the example relation bc relation in
Figure 3

• Figure 3  Relation bc, an example
• of redundancy in a BCNF relation

10

• We must repeat the loan number once for each
  address a customer has.
• We must repeat the address once for each loan the
  customer has.
• This repetition is pointless, as the relationship
  between a customer and a loan is independent of
  the relationship between a customer and his or
  her address.
• If a customer, say Smith'', has loan number 23,
  we want all of Smith's addresses to be associated
  with that loan.

11

• Thus the relation of Figure 4 is illegal
• If we look at our definition of multivalued
  dependency, we see that we want the multivalued
  dependency
• cname?street ccity to hold on BC-schema.

12

• 6.Note that if a relation r fails to satisfy a
  given multivalued dependency, we can construct a
  relation r' that does satisfy the multivalued
  dependency by adding tuples to r.

13
Theory of Multivalued Dependencies

• We will need to compute all the multivalued
  dependencies that are logically implied by a
  given set of multivalued dependencies.
• Let D denote a set of functional and multivalued
  dependencies.
• The closure D of D is the set of all functional
  and multivalued dependencies logically implied by
  D.

14

• We can compute from D using the formal
  definitions, but it is easier to use a set of
  inference rules.
• The following set of inference rules is sound and
  complete. The first three rules are Armstrong's
  axioms from Chapter 5

15

• Augmentation rule if a-gtb holds, and c is a set
  of attributes, then ac-gtbc holds.
• Reflexivity rule if a is a set of attributes and
  b belongs to a, then a-gtb holds.
• Transitivity rule if a-gtb holds, and b-gtc
  holds, then a-gtc holds.
• Complementation rule if a-gtb holds, then
  a-gtR-b-a holds.

16

• Multivalued augmentation rule if a-gtb holds,
  and c belongs to R and d belongs to c, then
  ac-gtbd holds.
• Multivalued transitivity rule if a-gtb holds,
  and b-gtc holds, then a-gtc-b holds.
• Replication rule if a-gtb holds, then a-gtgtb.
• Coalescence rule if a-gtgtb holds, and c belongs
  to b, and there is a d such that d belongs to R
  and db0 and d-gtc , then a-gtc holds.

17

• An example of multivalued transitivity rule is as
  follows.
• loan-gtgtcname and cname-gtgtcname,
  caddresss. Thus we have loan-gtgtcaddress, where
  caddress cname, caddress - cname .
• An example of coalescence rule is as follows.
  If we have student_name-gtgt bank, account , and
  student_id -gt bank, then we have student_name -gt
  bank.

18

• Let's do an example
• Let R(A,B,C,G,H,I) be a relation schema.
• Suppose A-gtgtBC holds.
• The definition of multivalued dependencies
  implies that if t1a t2A, then there exists
  tuples t3 and t4 such that

19

• t1At2At3At4A
• t3BCt1BC
• t3GHIt2GHI
• t4GHIt1GHI
• t4BCt2BC
• The complementation rule states that if A-gtgtBC
  then A-gtgtG HI
• Tuples t3 and t4 satisfy A-gtgtGH if we simply
  change the subscripts.

20

• We can simplify calculating D, the closure of D
  by using the following rules, derivable from the
  previous ones
• Multivalued union rule if a-gtgtb holds and a-gtgtc
  holds, then a-gtgtbc holds.
• Intersection rule if a-gtgtb holds and a-gtgtc
  holds, then a-gtgtbc holds.
• Difference rule if a-gtgtb holds and a-gtgtc holds,
  then a-gtgtb-c holds and a-gtgtc-b holds.

21

• An example will help
• Let R(A,B,C,G,H,I) with the set of
  dependencies
• A-gtgtB
• B-gtgtHI
• CG-gtH

22

• We list some members of D
• A-gtgtCGHI since A-gtgtB, complementation rule
  implies that A-gtgtR-A-B, and R - B - A CGHI.
• A-gtgtHI Since A-gtgtB and B-gtgtHI, multivalued
  transitivity rule implies that A-gtgtHI-B.

23

• B-gtH coalescence rule can be applied. B-gtgtHI
  holds, H belongs to HI and CG-gtgtH and CGHI0, so
  we can satisfy the coalescence rule with a being
  B, b being HI, c being CG, and d being H. We
  conclude that B-gtH.
• A-gtgtCG now we know that A-gtgtCGHI and A-gtgtHI. By
  the difference rule, A-gtgtCGHI-HICG .