5.16
Consider the relational schema
part(part_id, name, cost)
subpart(part_id, subpart_id, count)
where the primary-key attributes are underlined. A tuple \((p_1,p_2,3)\) in the subpart relation denotes that the part with part_id \(p_2\) is a direct subpart of the part with part_id \(p_1\), and \(p_1\) has 3 copies of \(p_2\). Note that \(p_2\) may itself have further subparts. Write a recursive SQL query that outputs the names of all subparts of the part with part-id ‘P-100’.
WITH RECURSIVE all_sub_parts_of_p100(part_id,name) AS (
(SELECT p.part_id,p.name
FROM part p INNER JOIN subpart s
ON p.part_id = s.subpart_id
WHERE s.part_id = 'P-100'
)UNION
(SELECT p.part_id,p.name
FROM
INNER JOIN subpart s
part p ON p.part_id = s.subpart_id
INNER JOIN all_sub_parts_of_p100 a
ON s.part_id = a.part_id
)
)SELECT name FROM all_sub_parts_of_p100;