The division operation, denoted by ÷ is not included in some RDBMS implementations with SQL as the primary query language. The division operation T R ÷ S can be expressed as a sequence of , x, and - operations as follows (pg 163):
T1 y (R)
T2 y ((S × T1) - R)
T T1 - T2
Consider the following relations:
SAILORS (sid, sname, rating, age)
RESERVES (sid, bid, day)
BOATS (bid, bname, color)
State what the following queries compute. Your answer should express the semantics of the queries with the vocabulary of a person who has no knowledge of relational algebra and SQL.
a) (TEMP1, bid = 103 RESERVES)
(TEMP2, TEMP1 SAILORS)
sname (TEMP2)
b) (TEMP1, sid (( color = \'red\' BOATS) RESERVES))
(TEMP2, sid (( color = \'green\' BOATS) RESERVES))
sname ((TEMP1 TEMP2) SAILORS)
c) sid( age > 20 SAILORS) - sid (( color = \'red\' BOATS) RESERVES SAILORS)
d) (TEMP1, ( sid, bid RESERVES) ÷ ( bid BOATS))
sname (TEMP1 SAILORS)
The division operation, denoted by ÷ is not included in some RDBMS implementations with SQL as the primary query language. The division operation T R ÷ S can be expressed as a sequence of , x, and - operations as follows (pg 163):
T1 y (R)
T2 y ((S × T1) - R)
T T1 - T2
Consider the following relations:
SAILORS (sid, sname, rating, age)
RESERVES (sid, bid, day)
BOATS (bid, bname, color)
State what the following queries compute. Your answer should express the semantics of the queries with the vocabulary of a person who has no knowledge of relational algebra and SQL.
a) (TEMP1, bid = 103 RESERVES)
(TEMP2, TEMP1 SAILORS)
sname (TEMP2)
b) (TEMP1, sid (( color = \'red\' BOATS) RESERVES))
(TEMP2, sid (( color = \'green\' BOATS) RESERVES))
sname ((TEMP1 TEMP2) SAILORS)
c) sid( age > 20 SAILORS) - sid (( color = \'red\' BOATS) RESERVES SAILORS)
d) (TEMP1, ( sid, bid RESERVES) ÷ ( bid BOATS))
sname (TEMP1 SAILORS)
The division operation, denoted by ÷ is not included in some RDBMS implementations with SQL as the primary query language. The division operation T R ÷ S can be expressed as a sequence of , x, and - operations as follows (pg 163):
T1 y (R)
T2 y ((S × T1) - R)
T T1 - T2
Consider the following relations:
SAILORS (sid, sname, rating, age)
RESERVES (sid, bid, day)
BOATS (bid, bname, color)
State what the following queries compute. Your answer should express the semantics of the queries with the vocabulary of a person who has no knowledge of relational algebra and SQL.
a) (TEMP1, bid = 103 RESERVES)
(TEMP2, TEMP1 SAILORS)
sname (TEMP2)
b) (TEMP1, sid (( color = \'red\' BOATS) RESERVES))
(TEMP2, sid (( color = \'green\' BOATS) RESERVES))
sname ((TEMP1 TEMP2) SAILORS)
c) sid( age > 20 SAILORS) - sid (( color = \'red\' BOATS) RESERVES SAILORS)
d) (TEMP1, ( sid, bid RESERVES) ÷ ( bid BOATS))
sname (TEMP1 SAILORS)
a) (TEMP1, bid = 103 RESERVES)
(TEMP2, TEMP1 SAILORS)
sname (TEMP2)
The above query computes the names of sailors who have reserved boat #103.
Given
SAILORS (sid, sname, rating, age)
RESERVES (sid, bid, day)
The above query was written using rename operator
With the statement (TEMP1, bid = 103 RESERVES) the row with bid value 103 in the table RESERVES will be renames with TEMP1
With the statement (TEMP2, TEMP1 SAILORS) there will be a natural join between TEMP1 and SAILORS and that column will be renamed with TEMP2.
Here Natural join means cross product(each row of TEMP1 is paired with each row of SAILORS) + selection on common fields + drop duplicate fields.
With the statement sname (TEMP2) the sname column will be left after the other unwanted columns from TEMP2 being deleted.
c) sid( age > 20 SAILORS) - sid (( color = \'red\' BOATS) RESERVES SAILORS)
The above query computes the sids of sailors with age over 20 who have not reserved a red boat.
With the statement sid( age > 20 SAILORS) we find all sailors (sids) with age over 20
With this sid (( color = \'red\' BOATS) RESERVES SAILORS) we find all sailors (sids) who have reserved a red boat by applying a join on RESERVES and SAILORS.
and finally we find all sailors (sids) who have reserved a red boat Take their set difference.
Homework Sourse
