Relational algebra questions: Using the supplier-part database provided below, a
ID: 2247194 • Letter: R
Question
Relational algebra questions: Using the supplier-part database provided below, answer each of the following relational algebra questions. (Note: Does not require the use of SQL or any database software. The results must be manually generated.)
please show work to help me understand
Use the following data to answer questions:
S Table (for Suppliers)
SNO
SNAME
STATUS
CITY
S1
Smith
20
London
S2
Jones
10
Paris
S3
Blake
30
Paris
S4
Clark
20
London
S5
Adams
30
Athens
P Table (for Parts)
PNO
PNAME
COLOR
WEIGHT
CITY
P1
Nut
Red
12
London
P2
Bolt
Green
17
Paris
P3
Screw
Blue
17
Rome
P4
Screw
Red
14
London
P5
Cam
Blue
12
Paris
P6
Cog
Red
19
London
SP Table
SNO
PNO
QTY
S1
P1
300
S1
P2
200
S1
P3
400
S1
P4
200
S1
P5
100
S1
P6
100
S2
P1
300
S2
P2
400
S3
P2
200
S4
P2
200
S4
P4
300
S4
P5
400
(NOTE: QTY stands for quantity.)
SP table shows which supplier (SNO) supplied how many units (QTY) of which part (PNO).
Questions Below:
6. Show the result of a one-sided outer join between the S (supplier) and P (part) tables using the common CITY column. Preserve the rows of the S table in the result.
7. Show the result of the full outer join between the S (supplier) and P (part) tables using the common CITY column.
8. For each part, calculate the total quantity of the part supplied. That is, show the result of a summarize operation on SP table; the grouping column is PNO and the aggregate calculation is SUM on QTY; display PNO and the total quantity.
9. Show the result of a summarize operation on P. The grouping column is CITY and the aggregate calculations are the minimum and maximum WEIGHT values.
10. Show SNO of supplier(s) who supplied all parts in P table. That is, show the result of the divide operation in which SP table (with SNO and PNO columns) is divided by P table (with PNO column).
SNO
SNAME
STATUS
CITY
S1
Smith
20
London
S2
Jones
10
Paris
S3
Blake
30
Paris
S4
Clark
20
London
S5
Adams
30
Athens
Explanation / Answer
1.) Result of one sided outer join b/w the table S and P using common CITY column is as follows:
The only cities London and Paris are common in the both table S and P. There is too much redundancy in the tables because the same city repeat many times. For e.g. London city in table P repeats three times so it join to London City of table S three times with single entry in the table.
2.) Result of full outer join b/w S and P table by using CITY as common is as follows:
3.) The Total quantity of the part supplied group by PNO is as follows:
The total Quantity as the Sum on QTY group bt PNO is shows in above table by adding the quantity of same PNO's.
4.) Minimum and Maximum Weight Values by Grouping City on table P is as follows:
a) Minimum
The minimum weight group by city in table P is 12 for both cities London and paris.
b) Maximum
5.) SNO of suppliers who supplied all parts in P as follows:
The only Supplier S1 supplied all the parts in table P.
IF THERE IS ANY QUERY PLEASE ASK IN COMMENTS............