A: Relational Algebra for Relations Supplier, Supplies and Parts: 61

a. Find the name of all suppliers located in the city "Kathmandu" that supplies part 'P01'.

Relational Algebra

π _{supplier_name} ( σ _{Supplier.city = 'Kathmandu' ^ Supplies.part_id = 'P01'} ( Supplier ⨝ Supplies ) )

SQL


SELECT sr.supplier_name
FROM   Supplier sr
       JOIN Supplies s
         ON sr.supplier_id = s.supplier_id
WHERE  sr.city = 'Kathmandu'
       AND s.part_id = 'P01';

Explanation:
The attribute to find: name of the supplier(supplier_name) from the "Supplier" schema.
Given Conditions:
1. city = "Kathmandu". From the "Supplier" schema.
2. part_id = 'P01'. From the "Supplies" schema.
Thus, we need to join two schemas.

b. Find the name of all parts supplied by "RD Traders".

Relational Algebra

π _{part_name} ( σ _{Supplier.supplier_name = 'RD Traders'} ( Parts ⨝ Supplies ⨝ Supplier ) )

SQL


SELECT p.part_name
FROM   Parts p
       JOIN Supplies s
         ON p.part_id = s.part_id
       JOIN Supplier sr
         ON s.supplier_id = sr.supplier_id
WHERE  p.part_name = 'RD Traders';

Explanation:
The attribute to find: name of the part(part_name). From the "Parts" schema.
Given Condition: Supplier's name = "RD Traders". From the "Supplier" schema.
Thus, we need to join two schemas: Parts and Supplier. Since they don't have a common attribute, we need to include another schema "Supplies" in the join operation.

c. Find the name of all parts that are supplied in quantity greater than 300.

Relational Algebra

π _{part_name} ( σ _{Supplies.quantity > 300} ( Parts ⨝ Supplies ) )

SQL


SELECT p.part_name
FROM   Parts p
       JOIN Supplies s
         ON p.part_id = s.part_id
WHERE  s.quantity > 300;

Explanation:
The attribute to find: name of the part(part_name). From the "Parts" schema.
Given Condition: Supply quantity ( > 300 ). From the "Supplies" schema.
Hence, a join is required.

d. Find the number of parts supplied by "S02".

There could be two meanings of "number of parts".

Case #1: It could be the 'count of unique part-type(part_id)'. Ex: if 10 units(quantity) of part 'P01' and 15 units of part 'P02' is supplied. Then number of parts is 2 (ie; 1 'P01' + 1 'P02').

Relational Algebra

𝓖 _count_{( part_id )} ( σ _{supplier_id = 'S02'} ( Supplies ) )

SQL


SELECT COUNT(DISTINCT part_id)
FROM   Supplies
WHERE  supplier_id = 'S02';

Case #2: It could be 'total quantity of parts'. Ex: if 10 units(quantity) of part 'P01' and 15 units of part 'P02' is supplied. Then the total number of parts is 25 (ie; 10 + 15).

Relational Algebra

𝓖 _{sum ( quantity )} ( σ _{supplier_id = 'S02'} ( Supplies ) )

SQL


SELECT SUM(quantity)
FROM   Supplies
WHERE  supplier_id = 'S02';

e. Find the number of parts supplied by each supplier.

Case #1: Same as above(question d).

Relational Algebra

_{supplier_id} 𝓖 ( _count_{( part_id )} ) ( Supplies )

SQL


SELECT supplier_id,
       COUNT(part_id)
FROM   Supplies
GROUP  BY supplier_id;

Case #2: Same as above(question d).

Relational Algebra

supplier_id 𝓖 ( sum ( quantity ) ) ( Supplies )

SQL


SELECT supplier_id,
       SUM(quantity)
FROM   Supplies
GROUP  BY supplier_id;