Database Systems For each part of this problem you will need to construct a single SQL query which will check whether a certain condition holds on a specific instance of a relation, in the following way: your query should return an empty result if and only if the condition holds on the instance. (If the condition doesn’t hold, your query should return something non-empty). Note our language here: the conditions that we specify cannot be proved to hold in general without knowing the externally-defined functional dependencies; so what we mean is, check whether they could hold in general for the relation, given a specific set of tuples. You may assume that there will be no NULL values in the tables, and you may assume that the relations are sets rather than multisets, but otherwise your query should work for general in- stances. A is a superkey for a relation T (A, B, C, D). The combinations of two attributes in the relation T (A, B, C, D) are each keys. A tuple-generating dependency(TGD)between two relations A and B, having some shared attributes X1, ..., Xn, holds if, for every tuple tA in A, there is some tuple tB in B such that tA[Xi] = tB[Xi] for i = 1,...n. In other words, for every distinct tuple in A, there must exist a corresponding tuple in B, which has the same values of shared attributes. Consider three tables Sailors(sid, sname, rating, age), Reserves(sid, bid, sname, bname, date) and Boats(bid, bname, color); Check for a TGD between Sailors and Reserves. A multivalued dependency (MVD) is defined as follows: let R be a schema i.e. a set of attributes, and consider two sets of attributes X ⊆ R and Y ⊆ R. We say that a multivalued dependency (MVD), written: X ? Y , holds on R if whenever there are two tuples t1, t2 such that t1[X] = t2[X], there also exists a third tuple t3 such that: • t3[X] = t1[X] = t2[X] • t3[Y]=t1[Y] • t3[R \Y]=t2[R \Y] Note that R \Y is all the attributes in R that are not in Y , and that t3 need not be distinct from t1 or t2. Note especially that an MVD holds on an entire relation, meaning that any two tuples (in any order) in the relation should satisfy the above conditions if the MVD holds. To simply the problem, we assume R = {A, B, C, D, E}, X = {A, B, C}, Y = {C, D, E}. Hints: You need self-joins, nested SQL queries and Not Exist or Exists. • At least 5 tuples for each table. • One table satisfies the requirement. • One table fails the requirement. • For Q2, you will need four tables to test two different combinations. • For Q3, you will need four tables.

Question

Database Systems

For each part of this problem you will need to construct a single SQL query which will check whether a certain condition holds on a specific instance of a relation, in the following way: your query should return an empty result if and only if the condition holds on the instance. (If the condition doesn’t hold, your query should return something non-empty).

Note our language here: the conditions that we specify cannot be proved to hold in general without knowing the externally-defined functional dependencies; so what we mean is, check whether they could hold in general for the relation, given a specific set of tuples.

You may assume that there will be no NULL values in the tables, and you may assume that the relations are sets rather than multisets, but otherwise your query should work for general in- stances.

A is a superkey for a relation T (A, B, C, D).
The combinations of two attributes in the relation T (A, B, C, D) are each keys.
A tuple-generating dependency(TGD)between two relations A and B, having some shared attributes X1, ..., Xn, holds if, for every tuple tA in A, there is some tuple tB in B such that tA[Xi] = tB[Xi] for i = 1,...n.

In other words, for every distinct tuple in A, there must exist a corresponding tuple in B, which has the same values of shared attributes.

Consider three tables Sailors(sid, sname, rating, age), Reserves(sid, bid, sname, bname, date) and Boats(bid, bname, color); Check for a TGD between Sailors and Reserves.
A multivalued dependency (MVD) is defined as follows: let R be a schema i.e. a set of attributes, and consider two sets of attributes X ⊆ R and Y ⊆ R.

We say that a multivalued dependency (MVD), written: X ? Y , holds on R if whenever there are two tuples t1, t2 such that t1[X] = t2[X], there also exists a third tuple t3 such that:

• t3[X] = t1[X] = t2[X] • t3[Y]=t1[Y]
• t3[R \Y]=t2[R \Y]

Note that R \Y is all the attributes in R that are not in Y , and that t3 need not be distinct from t1 or t2. Note especially that an MVD holds on an entire relation, meaning that any two tuples (in any order) in the relation should satisfy the above conditions if the MVD holds.

To simply the problem, we assume R = {A, B, C, D, E}, X = {A, B, C}, Y = {C, D, E}. Hints: You need self-joins, nested SQL queries and Not Exist or Exists.

• At least 5 tuples for each table.
• One table satisfies the requirement.
• One table fails the requirement.
• For Q2, you will need four tables to test two different combinations.

• For Q3, you will need four tables.