Suppose we have a relational database with five tables. table key Attributes S(sid, A) Sid T(tid, B) Tid U(uid, C) Uid R(sid, tid, D) sid, tid Q(tid, uid, E) tid, uid Here R implements a many-to-many relationship between the entities implemented with tables S and T, and Q implements a many-to-many relationship between the entities implemented with tables T and U. A. Write an SQL query that returns all records of the form sid, uid where sid is the key of an Srecord and uid is the key of a U-record and these two records are related through the relations R and Q. Use SELECT and not SELECT DISTINCT in your query. B. Write an SQL query that returns records of the form A, C where the A-value is from an Srecord and the C-value is from a U-record and these two records are related through the relations R and Q. Use SELECT and not SELECT DISTINCT in your query. C. Could one of your queries from parts (a) and (b) return more records than the other? If so, which one? Justify your answer.
Suppose we have a relational
table key Attributes
S(sid, A) Sid
T(tid, B) Tid
U(uid, C) Uid
R(sid, tid, D) sid, tid
Q(tid, uid, E) tid, uid
Here R implements a many-to-many relationship between the entities implemented with tables S
and T, and Q implements a many-to-many relationship between the entities implemented with
tables T and U.
A. Write an SQL query that returns all records of the form sid, uid where sid is the key of an Srecord and uid is the key of a U-record and these two records are related through the relations
R and Q. Use SELECT and not SELECT DISTINCT in your query.
B. Write an SQL query that returns records of the form A, C where the A-value is from an Srecord and the C-value is from a U-record and these two records are related through the
relations R and Q. Use SELECT and not SELECT DISTINCT in your query.
C. Could one of your queries from parts (a) and (b) return more records than the other? If so,
which one? Justify your answer.
D. Suppose you replaced SELECT with SELECT DISTINCT in your queries from parts (a) and
Could one of these modified queries return more records than the other? If so, which one?
Justify your answer.
E. Consider again your query from part (a). If pair sid, uid is returned by this query then there
must exist at least one “path” that goes from from table S to table T (via relation R) and then
from table T to table U (via relation Q).
Note that there can be many such paths for a given pair sid, uid. Write an SQL query that
returns records of the form tid, total where tid is a key of a record from table T and total
indicates the total number of such paths that “go through” that record.
Step by step
Solved in 3 steps