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 
            part p INNER JOIN subpart s
                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;