database-designrelational-databasedatabase-normalization

Does "tuples are not necessarily distinct" imply they are equal? How do I show whether this multivalued dependency holds in the example table?


From the book Fundamentals of Database Systems (7th edition) by Elmasri et al., pages 475-476:

A multivalued dependency [MVD] X ↠ Y specified on relation schema R, where X and Y are both subsets of R, specifies the following constraint on any relation state r of R:
If two tuples t1 and t2 exist in r such that t1[X] = t2[X], then two tuples t3 and t4 should also exist in r with the following properties, where we use Z to denote (R − (X ∪ Y)):
t3[X] = t4[X] = t1[X] = t2[X]
t3[Y] = t1[Y] and t4[Y] = t2[Y]
t3[Z] = t2[Z] and t4[Z] = t1[Z]

(The tuples t1, t2, t3, and t4 are not necessarily distinct.)

Does "tuples t1, t2, t3, and t4 are not necessarily distinct" imply t1 = t2 = t3 = t4?

Example table:

course  teacher    book

physics   tom      general physics
physics   tom      optics
physics   marry    general physics
physics   marry    optics
math      john     mathematical analysis
math      ken      linear algebra

When course = physics,

course  teacher    book

physics   tom      general physics
physics   tom      optics
physics   marry    general physics
physics   marry    optics

Let t1, t2, t3, and t4 be distinct in the definition; course ↠ teacher holds.

When course = math,

course  teacher    book

math      john     mathematical analysis
math      ken      linear algebra

Let t1 = t2 = t3 = t4 in the definition; course ↠ teacher holds too.

How do I show whether multivalued dependency course ↠ teacher holds in the example table?


Solution

  • MVD does not hold in the example table you shared.

    Simply because
    (math, john, mathematical analysis) and (math, ken, linear algebra) exists but

    (math, ken, mathematical analysis) and (math, john, linear algebra) does not exist

    If two tuples t1 and t2 exist in r such that t1[X] = t2[X], then two tuples t3 and t4 should also exist in r with the following properties, where we use Z to denote (R − (X ∪ Y)):
    t3[X] = t4[X] = t1[X] = t2[X]
    t3[Y] = t1[Y] and t4[Y] = t2[Y]
    t3[Z] = t2[Z] and t4[Z] = t1[Z]

    1. Let t1 = (math, john, mathematical analysis)
    2. Let t2 = (math, ken, linear algebra)
    
    3. Since t1[X] = math = t2[X] and t2[Y] != t1[Y] and t1[Z] != t2[Z],
    3.1. For MVD to hold, there must be t3 where,
    3.1.1.      t3[X] = math and t3[Y] = t1[Y] = "john" and t3[Z] = t2[Z] = "linear algebra"
    

    Since t3 = (math, john, linear algebra) does not exists in r, MVD does not hold. Same reasoning for any possible t4

    Does "tuples t1, t2, t3, and t4 are not necessarily distinct" imply t1 = t2 = t3 = t4?

    I think you got confused by this statement. It merely means they don't have to be distinct not necessarily they are the same.

    This statement is useful for the following tables where they exhibit MVD.

    Example 1:

    course  teacher    book
    
    physics   tom      general physics
    physics   tom      optics
    physics   marry    general physics
    physics   marry    optics
    math      john     mathematical analysis
    math      john     linear algebra
    

    Example 2:

    course  teacher    book
    
    physics   tom      general physics
    physics   tom      optics
    physics   marry    general physics
    physics   marry    optics
    math      john     mathematical analysis
    math      ken      mathematical analysis