Database Systems For each part of this problem you will need to construct a single SQL query which will check whether a
Posted: Tue Jul 05, 2022 10:19 am
Database Systems
For each part of this problem you will need to construct asingle SQL query which will check whether a certain condition holdson a specific instance of a relation, in the following way: yourquery should return an empty result if and only if the conditionholds on the instance. (If the condition doesn’t hold, your queryshould return something non-empty). Note our language here: theconditions that we specify cannot be proved to hold in generalwithout knowing the externally-defined functional dependencies; sowhat we mean is, check whether they could hold in general for therelation, given a specific set of tuples. You may assume that therewill be no NULL values in the tables, and you may assume that therelations are sets rather than multisets, but otherwise your queryshould work for general instances.
1. A is a superkey for a relation T (A, B, C, D).
2. The combinations of two attributes in the relation T (A, B,C, D) are each keys.
3. A tuple-generating dependency (TGD) between two relations Aand B, having some shared attributes X1, ..., Xn, holds if, forevery 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 tuplein A, there must exist a corresponding tuple in B, which has thesame values of shared attributes. Consider three tablesSailors(sid, sname, rating, age), Reserves(sid, bid, sname, bname,date) and Boats(bid, bname, color); Check for a TGD between Sailorsand Reserves.
4. A multivalued dependency (MVD) is defined as follows: let Rbe a schema i.e. a set of attributes, and consider two sets ofattributes X ⊆ R and Y ⊆ R. We say that a multivalued dependency(MVD), written: X Y , holds on R if whenever there are two tuplest1, t2 such that t1[X] = t2[X], there also exists a third tuple t3such 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 notin Y , and that t3 need not be distinct from t1 or t2. Noteespecially that an MVD holds on an entire relation, meaning thatany two tuples (in any order) in the relation should satisfy theabove conditions if the MVD holds. To simply the problem, we assumeR = {A, B, C, D, E}, X = {A, B, C}, Y = {C, D, E}. Hints: You needself-joins, nested SQL queries and Not Exist or Exists.
MUST DO:
Two testing tables with data for each Question (20%) •
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 differentcombinations.
For Q3, you will need four tables.
**PLEASE HELP ME ASAP I WILL GIVE YOU THUMBS UP**
What I need help with is creating the tables! I understand thequeries, I DO NOT NEED THE 4 QUERIES!!! I ONLY NEED THE TABLES!
THANK YOU!
For each part of this problem you will need to construct asingle SQL query which will check whether a certain condition holdson a specific instance of a relation, in the following way: yourquery should return an empty result if and only if the conditionholds on the instance. (If the condition doesn’t hold, your queryshould return something non-empty). Note our language here: theconditions that we specify cannot be proved to hold in generalwithout knowing the externally-defined functional dependencies; sowhat we mean is, check whether they could hold in general for therelation, given a specific set of tuples. You may assume that therewill be no NULL values in the tables, and you may assume that therelations are sets rather than multisets, but otherwise your queryshould work for general instances.
1. A is a superkey for a relation T (A, B, C, D).
2. The combinations of two attributes in the relation T (A, B,C, D) are each keys.
3. A tuple-generating dependency (TGD) between two relations Aand B, having some shared attributes X1, ..., Xn, holds if, forevery 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 tuplein A, there must exist a corresponding tuple in B, which has thesame values of shared attributes. Consider three tablesSailors(sid, sname, rating, age), Reserves(sid, bid, sname, bname,date) and Boats(bid, bname, color); Check for a TGD between Sailorsand Reserves.
4. A multivalued dependency (MVD) is defined as follows: let Rbe a schema i.e. a set of attributes, and consider two sets ofattributes X ⊆ R and Y ⊆ R. We say that a multivalued dependency(MVD), written: X Y , holds on R if whenever there are two tuplest1, t2 such that t1[X] = t2[X], there also exists a third tuple t3such 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 notin Y , and that t3 need not be distinct from t1 or t2. Noteespecially that an MVD holds on an entire relation, meaning thatany two tuples (in any order) in the relation should satisfy theabove conditions if the MVD holds. To simply the problem, we assumeR = {A, B, C, D, E}, X = {A, B, C}, Y = {C, D, E}. Hints: You needself-joins, nested SQL queries and Not Exist or Exists.
MUST DO:
Two testing tables with data for each Question (20%) •
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 differentcombinations.
For Q3, you will need four tables.
**PLEASE HELP ME ASAP I WILL GIVE YOU THUMBS UP**
What I need help with is creating the tables! I understand thequeries, I DO NOT NEED THE 4 QUERIES!!! I ONLY NEED THE TABLES!
THANK YOU!