Relational Algebra and SQL for Relations Supplier, Supplies and Parts

Consider the following database, where primary keys are underlined.

  • Supplier(supplier_id, supplier_name, city)
  • Supplies(supplier_id, part_id, quantity)
  • Parts(part_id, part_name, color, weight)

 

Write relation algebra and SQL expressions for each of the following queries.

  1. Find the name of all suppliers located in the city "Kathmandu" that supplies part 'P01'.
  2. Find the name of all parts supplied by "RD Traders".
  3. Find the name of all parts that are supplied in quantity greater than 300.
  4. Find the number of parts supplied by "S02".
  5. Find the number of parts supplied by each supplier.

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;